其他部門負責的兩臺資料庫伺服器最近都碰到了這個問題，於是幫他們檢查一下問題。

資料庫都是10g for Linux X86-64，一個是10201，另一個是10203。由於錯誤發生的時候我不在，這個錯誤在系統重啟後消失，且不定時的會重現，因此現在只能透過分析日誌來檢查問題了。

首先檢查了一下alert檔案，裡面除了資料庫重啟資訊外，沒有發現任何有價值的資訊。不過想想也對，問題發生在連線資料庫的時候，顯然不可能寫到資料庫的alert檔案中，於是檢查監聽對應的日誌$ORACLE_HOME/network/log/listener.ora，果然在出現問題對應的時間段找到了錯誤資訊：

10-SEP-2008 00:56:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl10g)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=28909)) * establish * orcl10g * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
10-SEP-2008 00:56:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl10g)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=4392)) * establish * orcl10g * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
10-SEP-2008 00:56:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl10g)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14494)) * establish * orcl10g * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
10-SEP-2008 00:56:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl10g)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=13123)) * establish * orcl10g * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

另外一個出錯的資料庫中則包含了類似的錯誤資訊。

查詢了一下Oracle的錯誤文件，對於ORA-12514錯誤的描述如下：

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action:
- Wait a moment and try to connect a second time.

- Check which services are currently known by the listener by executing: lsnrctl services

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.

在metalink上找到有關這個錯誤的文章Doc ID: 365314.1，雖然當前的問題和這篇文章描述的並不一樣，但是這篇文章對這個錯誤產生的原因描述的還是比較清晰的。

檢查這兩個出錯的伺服器環境，發現二者都配置了兩個以上的IP地址，而且這兩個伺服器中/etc/hosts檔案中都是僅設定了一個IP，而其中一個伺服器甚至連唯一設定的IP還是錯誤的。

[root@bakdata2 ~]# more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               bakdata2        localhost
172.0.4.30              bakdata2        localhost
[root@bakdata2 ~]# ifconfig -a
eth0      Link encap:Ethernet HWaddr 00:1E:4F:41:07:E1
          inet addr:172.25.13.137 Bcast:172.25.255.255 Mask:255.255.0.0
          inet6 addr: fe80::21e:4fff:fe41:7e1/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
          RX packets:890702 errors:0 dropped:0 overruns:0 frame.:0
          TX packets:247307 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:81324043 (77.5 MiB) TX bytes:253306628 (241.5 MiB)
          Interrupt:169 Memory:f8000000-f8011100

eth1      Link encap:Ethernet HWaddr 00:1E:4F:41:07:E3
          inet addr:192.168.11.5 Bcast:192.168.11.255 Mask:255.255.255.0
          UP BROADCAST MULTICAST MTU:1500 Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame.:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)
          Interrupt:169 Memory:f4000000-f4011100

lo        Link encap:Local Loopback
          inet addr:127.0.0.1 Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING MTU:16436 Metric:1
          RX packets:339667 errors:0 dropped:0 overruns:0 frame.:0
          TX packets:339667 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:49086165 (46.8 MiB) TX bytes:49086165 (46.8 MiB)

sit0      Link encap:IPv6-in-IPv4
          NOARP MTU:1480 Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame.:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)

修改/etc/hosts檔案，使得主機對應的IP地址都包含在這個檔案中。

除此之外，還有一個一勞永逸的方法解決這個檔案，就是不使用動態註冊，而採用靜態的方式在listener.ora中手工配置目標資料庫的資訊。

連線資料庫時出現ORA-12514錯誤

相關文章