多種TNS報錯總結--監聽器及tnsnames.ora配置檔案多種出錯總結

還不算暈發表於2013-11-08
原文網址 : https://blog.csdn.net/haibusuanyun/article/details/14517211

注意：本文內容基本無sqlnet.ora的情況。關於sqlnet.ora，詳見：監聽中sqlnet.ora的作用

本地命名的配置：.本地名可以用簡單的名稱訪問到所需的資料庫或伺服器所需的資訊,而這些資訊儲存到了tnsnames.ora中.

LOCALNAME: 本地名稱
ADDRESS: 伺服器地址
PROTOCOL: 使用協議
HOST: IP地址
PORT: 監聽埠
SERVICE_NAME: 資料庫服務名稱
首先要保證tnsnames.ora檔案中的格式正確，如=號、空格、括號之類基本格式不出錯
這個可以參考我的一篇tnsnames.ora各種模板：http://blog.csdn.net/q947817003/article/details/11180027

當我們發出一條命令：sqlplus bys/bys@test時，連線到資料庫大致經過以下步驟：

1.首先在sqlplus程式的相關目錄中查詢tnsnames.ora

2.在tnsnames.ora中查詢test=開頭的字串

3.根據tnsnames.ora中查詢test=字串中的：PROTOCOL = TCP)(HOST = 192.168.1.211) 這一句，查詢相應的HOST，如是IP，則直接訪問；如是域名，需要解析為IP

4.當可以與HOST =中描述的主機通訊後，再根據(PORT = 1521)這一句中的描述，在主機的1521這一埠使用TCP協議進行連線。

5.如果以上連線成功，則已經連線到資料庫監聽器。此時tnsnames.ora中查詢test=字串中的：SERVICE_NAME = bys1，這一句則再次指定了要連線到監聽器的bys1服務上

6.如果以上連線成功，已經連線到監聽器的bys1服務，則會根據監聽器中服務所對應的例項，來連線到具體的例項。（在tnsnames.ora的test=字串中也可以在SERVICE_NAME =這一句下面再指定具體例項--我實驗中未指定；如不指定，則由監聽器來分配；如監聽器中同一服務下有多個例項（如RAC），由監聽器來進行動態均衡分配連線）。

7.如果以上連線成功，則已經連線到例項。接下來就由資料庫來驗證使用者名稱、密碼的正確性了。

本篇的實驗即使遵循以上的sqlplus bys/bys@test連線到資料庫的步驟來對每一步進行錯誤演示

關於連線資料庫的語句：sqlplus bys/bys@192.168.1.211：1521/bys1  這種是不使用tnsnames.ora檔案。連線到192.168.1.211主機的1521埠的bys1服務上。 關於SQLPLUS連線資料庫的寫法，更詳細見：http://blog.csdn.net/q947817003/article/details/11180137

實驗環境：LINUX--OLE 5.8；ORACLE -Oracle Database 11g Enterprise Edition Release 11.2.0.1.

##################################################################################

1.tnsnames.ora檔案不存在或檔名寫錯，總之就是找不到這個檔案。此時使用本地名來連線是不行的。

測試結果：

TNSPING報錯為：TNS-03505: Failed to resolve name

SQLPLUS報錯為：ORA-12154: TNS:could not resolve the connect identifier specified

實驗資料

[oracle@bys001 admin]$ ls
listener.bak  samples     tnsnames.ora
listener.ora  shrept.lst
[oracle@bys001 admin]$ mv tnsnames.ora tnsnames.oraa
[oracle@bys001 admin]$ tnsping bys1
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-NOV-2013 09:24:18
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name
[oracle@bys001 admin]$ sqlplus bys/bys@bys1
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 8 09:25:12 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

##################################################################################

2.tnsnames.ora 中test本地名對應的欄位描述格式正確，資料庫監聽開啟。但是使用TNSPING或SQLPLUS時所用的本地名不對。可能是test本地名前有空格。

（本地名指的是tnsnames.ora 中的描述欄位：

test =

(DESCRIPTION =  這裡；也即SQLPLUS BYS/BYS@TEST這裡的TEST）

測試時：

TNSPING報錯為：TNS-03505: Failed to resolve name

SQLPLUS報錯為：ORA-12154: TNS:could not resolve the connect identifier specified

如果是test本地名前有空格，WIN下客戶端報錯可能如上，而LINUX下test本地名有空格時，TNSPING正常。

SQLPLUS報錯：ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA  

 --如tnsnames.ora 的本地名中這一段：(SERVICE_NAME = bys1)單詞拼錯，如SERVER_NAME  BYS9等也會報這個錯

實驗資料：
[oracle@bys001 admin]$ tnsping hello
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:41:29
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

TNS-03505: Failed to resolve name
[oracle@bys001 admin]$ sqlplus bys/bys@hello
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:42:36 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
#########################################################################################################

3.tnsnames.ora 中test本地名對應的欄位描述格式正確，資料庫監聽開啟。但是tnsnames.ora 中HOST = 欄位指定的IP無法PING通。

測試結果：

TNSPING和SQLPLUS報錯均為：    TNS-12543: TNS:destination host unreachable

實驗資料：

[oracle@bys001 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
bys1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys1)
    )
  )
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))   手動將HOST中的IP改為一個不存在的IP即可
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys1)
    )
  )
[oracle@bys001 ~]$ ping 192.168.1.222
PING 192.168.1.222 (192.168.1.222) 56(84) bytes of data.
From 192.168.1.211 icmp_seq=2 Destination Host Unreachable
From 192.168.1.211 icmp_seq=3 Destination Host Unreachable
[oracle@bys001 admin]$ tnsping test
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:08:12
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1)))
TNS-12543: TNS:destination host unreachable
[oracle@bys001 admin]$ sqlplus bys/bys@test
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:08:21 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12543: TNS:destination host unreachable
#########################################################################################################

4.資料庫監聽正常。tnsnames.ora 中HOST = 欄位指定的主機名無法解析（涉及/etc/hosts或DNS），總之主機名無法解析成IP。

測試結果：

TNSPING和SQLPLUS報錯均為：   ORA-12545: Connect failed because target host or object does not exist

實驗資料：

[oracle@bys001 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
bys1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys1)
    )
  )
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bucunzai)(PORT = 1521))   手動將HOST指定的主機改為一個不存在的域名/主機名
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys1)
    )
  )
[oracle@bys001 admin]$ ping bucunzai
ping: unknown host bucunzai
[oracle@bys001 admin]$ tnsping test
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:24:47
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bucunzai)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1)))
TNS-12545: Connect failed because target host or object does not exist       ------這裡需要等待一段時間。
[oracle@bys001 admin]$ sqlplus bys/bys@test
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:26:08 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.    ------這裡需要等待一段時間。
ERROR:
ORA-12545: Connect failed because target host or object does not exist
#########################################################################################################

5.資料庫監聽正常。tnsnames.ora 中HOST = 欄位指定的IP或主機可以連線。但是PORT =欄位指定的埠沒有開啟。

埠沒開啟：LINUX中即埠沒有對應的程式或者叫程式不是在此埠開啟。

測試結果：

TNSPING和SQLPLUS報錯均為：   ORA-12541: TNS:no listener

從報錯中也可以理解出：能連線到主機，但是相應的埠沒有對應的程式，所以報錯：沒有監聽器。
這種情況下，如果關閉監聽器，使用TNSPING和SQLPLUS測試，報的也是：ORA-12541: TNS:no listener  --見實驗7。
實驗資料：
[oracle@bys001 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
bys1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys1)
    )
  )
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1522))   手動將此埠改為一個不存在的埠--即沒對應的程式的埠
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys1)
    )
  )
[oracle@bys001 admin]$ tnsping test
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:30:49
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1)))
TNS-12541: TNS:no listener
[oracle@bys001 admin]$ sqlplus bys/bys@test
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:30:56 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12541: TNS:no listener
#########################################################################################################

6.tnsnames.ora 中配置正確，監聽正常，資料庫伺服器所在主機防火牆未開放監聽所在的1521埠或者tnsnames.ora指定的埠與監聽中不符，遠端連線時報TIME OUT。

除了防火牆外，網路不穩定(包括網線質量問題)、ARP問題導致網路時通時不通、路由問題導致時通時不通等等問題都可能引起此錯誤。

測試結果：   --tnsnames.ora中的埠與監聽中埠不符也是報TIME OUT.

TNSPING報錯為：TNS-12535: TNS:operation timed out

SQLPLUS報錯為：ORA-12170: TNS:Connect timeout occurred

實驗資料：
[root@bys001 ~]# service iptables start    ---啟動防火牆
Flushing firewall rules:                                   [  OK  ]
Setting chains to policy ACCEPT: filter                    [  OK  ]
Unloading iptables modules:                                [  OK  ]
[root@bys001 ~]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         
[root@bys001 ~]# iptables -A INPUT -p tcp --dport 1521 -j DROP    --新增加一個策略，禁止連線1521埠
[root@bys001 ~]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
DROP       tcp  --  anywhere             anywhere            tcp dpt:ncube-lm
Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         
[root@bys001 ~]# su - oracle
[oracle@bys001 ~]$ tnsping bys1       ---bys1是我正常使用的，這裡未貼出tnsnames.ora，可以參考實驗5
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-NOV-2013 00:02:14
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1)))
TNS-12535: TNS:operation timed out
[oracle@bys001 ~]$ sqlplus bys/bys@bys1
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 8 00:00:07 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12170: TNS:Connect timeout occurred
[oracle@bys001 ~]$ su -
Password:
[root@bys001 ~]# service iptables stop                  ----及時關閉防火牆，以免影響後面實驗
Flushing firewall rules:                                   [  OK  ]
Setting chains to policy ACCEPT: filter                    [  OK  ]
Unloading iptables modules:                                [  OK  ]
在其它主機上測試：
Last login: Thu Nov  7 23:56:04 2013
[oracle@dg1 ~]$ ping 192.168.1.211
PING 192.168.1.211 (192.168.1.211) 56(84) bytes of data.
64 bytes from 192.168.1.211: icmp_seq=1 ttl=64 time=2.47 ms
^C
--- 192.168.1.211 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 2.476/2.476/2.476/0.000 ms
[oracle@dg1 ~]$ telnet 192.168.1.211 1521
Trying 192.168.1.211...
telnet: connect to address 192.168.1.211: Connection timed out
telnet: Unable to connect to remote host: Connection timed out
[oracle@dg1 ~]$ sqlplus bys/bys@192.168.1.211:1521/bys1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:57:50 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12170: TNS:Connect timeout occurred
#########################################################################################################

7.資料庫監聽正常。tnsnames.ora 中HOST = 欄位指定的IP/主機連通，PORT =欄位指定的埠已開啟。SERVICE_NAME指定服務名與監聽中不符。

即tnsnames.ora 中test本地名對應的欄位描述格式正確，監聽開啟，但是監聽中不包括TNS中SERVICE_NAME =描述的服務名。也可能是動態監聽時例項沒註冊到監聽。  ---還有一種可能是，RAC伺服器時，伺服器使用的是SCANIP或VIP，客戶端配置的HOST地址中IP與主機名對應有問題或未配置等。
測試結果：

TNSPING可以通。

SQLPLUS連線報錯：ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

實驗資料：
[oracle@bys001 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
bys1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys1)
    )
  )
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys999)   手動改為一個不存在的服務名bys999
    )
  )
[oracle@bys001 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:10:38
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys001.oel.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                05-NOV-2013 16:10:23
Uptime                    2 days 7 hr. 0 min. 15 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/diag/tnslsnr/bys001/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys001.oel.com)(PORT=1521)))
Services Summary...
Service "bys1" has 1 instance(s).
  Instance "bys1", status READY, has 1 handler(s) for this service...
Service "bys1XDB" has 1 instance(s).
  Instance "bys1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@bys001 admin]$ tnsping bys1
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:10:43
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1)))
OK (10 msec)
[oracle@bys001 admin]$ tnsping test  ----一個不存在的服務名，只要主機和埠正確，也是可以用TNSPING測通的。
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 07-NOV-2013 23:10:46
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = bys999)))
OK (10 msec)
[oracle@bys001 admin]$ sqlplus bys/bys@bys1
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:10:53 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
BYS@bys1>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bys001 admin]$ sqlplus bys/bys@test   服務名不對，測試時TNSPING可能測通，SQLPLUS不能連線
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 7 23:10:59 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
#########################################################################################################

8.tnsnames.ora 中本地名及相關主機名、埠、服務名描述正常，資料庫中監聽未開啟。--這步原理同實驗4相同。

測試結果：

TNSPING和SQLPLUS報錯均為：   ORA-12541: TNS:no listener

從報錯中也可以理解出：能連線到主機，但是相應的埠沒有對應的程式，所以報錯為：沒有監聽器。
實驗資料：
[oracle@bys001 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-NOV-2013 09:10:00
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys001.oel.com)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@bys001 ~]$ tnsping bys1
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-NOV-2013 09:10:26
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys1)))
TNS-12541: TNS:no listener
[oracle@bys001 ~]$ sqlplus bys/bys@bys1
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 8 09:10:30 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

#########################################################################################################

9.在客戶端連線RAC或資料鏈連線其它庫時，報錯：ORA-12545:Connect failed because target host or object does not exist

有兩種解決方法情況：

參考mos文件:Client Connection to RAC Intermittently Fails-ORA-12545 TNS: Host or Object Does not Exist (文件 ID 364855.1)

1.客戶端使用SCANIP連線RAC資料庫時的報錯：ORA-12545: Connect failed because target host or object does not exist
解決方法是：修改RAC的local_listener引數，將引數值中HOST=的值改為當前節點的VIP或者scanip--注意是IP而不是hostname，客戶端可以通過SCANIP或者VIP都可以連線到RAC資料庫庫
詳見：http://blog.csdn.net/haibusuanyun/article/details/17737553
2.資料鏈連線其它庫時，/etc/hosts中沒有tnsnames.ora中的主機名與IP的對應，也無法通過DNS解析tnsnames.ora中的主機名。

詳見：http://blog.csdn.net/haibusuanyun/article/details/17738631

10.ORA-12523: TNS:listener could not find instance appropriate for the client  TNS共享伺服器模式連到到靜態監聽

我遇到一次，tnsnames.ora裡寫的是連線模式共享伺服器，連到的監聽是靜態的，報此錯誤。詳情如下：

1.tnsnames.ora裡共享伺服器模式，連到到靜態監聽，報錯：ORA-12523: TNS:listener could not find instance appropriate for the client

prod_s=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=ocm1)(PORT=1521))
  (CONNECT_DATA=
   (SERVER=shared)
   (SERVICE_NAME=PROD)))

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.bys.com)(PORT=1521)))
Services Summary...
Service "PROD" has 1 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
[oracle@ocm1 admin]$ sqlplus sys/oracle@prod_s as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 27 12:20:07 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection

2.tnsnames.ora裡共享伺服器模式，連到到動態監聽，正常

prod_s=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=ocm1)(PORT=1526))
  (CONNECT_DATA=
   (SERVER=shared)
   (SERVICE_NAME=PROD)))

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.bys.com)(PORT=1526)))
Services Summary...
Service "PROD" has 1 instance(s).
  Instance "PROD", status READY, has 4 handler(s) for this service...

[oracle@ocm1 admin]$ sqlplus sys/oracle@prod_s as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 27 12:21:54 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

11.tnsnames.ora 中本地名及相關主機名、埠、服務名描述正常。監聽開啟，可能因監聽的配置檔案出錯或程式異常等，服務無法註冊到監聽或其它異常。

這個情況沒遇到過，也不知道要怎麼來演示了。

說下監聽配置檔案的注意事項：

1.監聽分動態與靜態監聽，詳細見：http://blog.csdn.net/q947817003/article/details/11136477
2.監聽器的配置檔案listener.ora：和tnsnames.ora同樣在在：$ORACLE_HOME/network/admin 資料夾下
3.listener.ora內配置資訊格式見：http://blog.csdn.net/q947817003/article/details/11180027
4.靜態監聽的一個用處是可以用來遠端啟動資料庫。即例項未啟動時，仍可以通過遠端連線到空閒例項，發出STARTUP命令來啟動資料庫。
至於這種方式的安全問題，可以通過配置多個監聽器，如L1配置為靜態監聽，L2配置為動態監聽。同時在tnsnames.ora中通過配置不同本地名來使不同的業務連線不同的本地名，就最終連到不同監聽來解決安全隔離問題。（以上個人理解）

相關文章