Tnsping OK不代表listener正常，近日遇到一个问题，环境是11Gr2 RAC，tnsping正常，但是实际访问数据库报错
- ORA-12545: 因目标主机或对象不存在, 连接失败
代码如下
- [grid@grida ~]$ srvctl config scan
- SCAN name: scan-grid, Network: 1/172.16.200.0/255.255.255.0/eth0
- SCAN VIP name: scan1, IP: /scan-grid/172.16.200.219
- tnsname file:
- XYZ =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.200.219)(PORT = 1521))
- (LOAD_BALANCE = yes)
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = XYZ)
- )
- )
- scanip:172.16.200.219
- C:\Users\B010910>tnsping xyz
- TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-5月 -
- 2012 09:02:43
- Copyright (c) 1997, 2010, Oracle. All rights reserved.
- 已使用的参数文件:
- d:\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
- 已使用 TNSNAMES 适配器来解析别名
- 尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.200.219)(PORT
- = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME
- = XYZ)))
- OK (60 毫秒)
- C:\Users\B010910>sqlplus /nolog
- SQL*Plus: Release 11.2.0.1.0 Production on 星期一 5月 7 09:03:06 2012
- Copyright (c) 1982, 2010, Oracle. All rights reserved.
- SQL> conn system/0racle@xyz;
- ERROR:
- ORA-12545: 因目标主机或对象不存在, 连接失败
经过分析，tnsping正常只能说明，该IP、1521端口、以及listener status是正常的。但是在实际需要访问instance的时候，并没有能够正常连接，主要问题就是instance并没有正常的注册到listener中，需要进一步分析没有注册的原因。
查看CRS资源
- [oracle@qr01db01 ~]$ crsctl status res -t
- --------------------------------------------------------------------------------
- NAME TARGET STATE SERVER STATE_DETAILS
- --------------------------------------------------------------------------------
- Local Resources
- --------------------------------------------------------------------------------
- ora.DATA_QR01.dg
- ONLINE ONLINE qr01db01
- ONLINE ONLINE qr01db02
- ora.DBFS_DG.dg
- ONLINE ONLINE qr01db01
- ONLINE ONLINE qr01db02
- ora.LISTENER.lsnr
- ONLINE ONLINE qr01db01
- ONLINE ONLINE qr01db02
- ora.RECO_QR01.dg
- ONLINE ONLINE qr01db01
- ONLINE ONLINE qr01db02
- ora.asm
- ONLINE ONLINE qr01db01 Started
- ONLINE ONLINE qr01db02
- ora.gsd
- OFFLINE OFFLINE qr01db01
- OFFLINE OFFLINE qr01db02
- ora.net1.network
- ONLINE ONLINE qr01db01
- ONLINE ONLINE qr01db02
- ora.ons
- ONLINE ONLINE qr01db01
- ONLINE ONLINE qr01db02
- ora.registry.acfs
- ONLINE ONLINE qr01db01
- ONLINE ONLINE qr01db02
- --------------------------------------------------------------------------------
- Cluster Resources
- --------------------------------------------------------------------------------
- ora.LISTENER_SCAN1.lsnr
- 1 ONLINE ONLINE qr01db01
- ora.cvu
- 1 ONLINE ONLINE qr01db01
- ora.dbm.db
- 1 ONLINE ONLINE qr01db02 Open
- 2 ONLINE ONLINE qr01db01 Open
- ora.oc4j
- 1 ONLINE ONLINE qr01db01
- ora.qr01db01.vip
- 1 ONLINE ONLINE qr01db01
- ora.qr01db02.vip
- 1 ONLINE ONLINE qr01db02
- ora.scan1.vip
- 1 ONLINE ONLINE qr01db01
查看instance是否正常注册
- [oracle@qr01db01 ~]$ ps -ef | grep tns
- oracle 3049 1 0 May02 ? 00:00:02 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
- oracle 3057 1 0 May02 ? 00:00:02 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
- oracle 5677 5606 0 10:33 pts/0 00:00:00 grep tns
- [oracle@qr01db01 ~]$ lsnrctl service LISTENER_SCAN1
- LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-MAY-2012 10:33:27
- Copyright (c) 1991, 2010, Oracle. All rights reserved.
- Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
- Services Summary...
- Service "dbm.example.com" has 2 instance(s). <<<<<<<<<<<<<<<<<这里显示已经将实例注册
- Instance "dbm1", status READY, has 1 handler(s) for this service...
- Handler(s):
- "DEDICATED" established:0 refused:0 state:ready
- REMOTE SERVER
- (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.0.2.24)(PORT=1521)))
- Instance "dbm2", status READY, has 1 handler(s) for this service...
- Handler(s):
- "DEDICATED" established:0 refused:0 state:ready
- REMOTE SERVER
- (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.0.2.25)(PORT=1521)))
- Service "dbmXDB.example.com" has 2 instance(s).
- Instance "dbm1", status READY, has 1 handler(s) for this service...
- Handler(s):
- "D000" established:0 refused:0 current:0 max:1022 state:ready
- DISPATCHER
- (ADDRESS=(PROTOCOL=tcp)(HOST=qr01db01.example.com)(PORT=61048))
- Instance "dbm2", status READY, has 1 handler(s) for this service...
- Handler(s):
- "D000" established:0 refused:0 current:0 max:1022 state:ready
- DISPATCHER
- (ADDRESS=(PROTOCOL=tcp)(HOST=qr01db02.example.com)(PORT=46543))
- The command completed successfully
查看parameter参数
- [oracle@qr01db01 ~]$ sqlplus / as sysdba
- SQL> show parameter remote_listener
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- remote_listener string qr01-scan:1521
- SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=192.
0.2.24)(PORT=1521))))
参考hosts文件设置
- [oracle@qr01db01 ~]$ cat /etc/hosts
- 127.0.0.1 localhost.localdomain localhost
- 192.0.2.11 qr01cel01.example.com qr01cel01
- 192.0.2.12 qr01cel02.example.com qr01cel02
- 192.0.2.13 qr01cel03.example.com qr01cel03
- 192.0.2.14 qr01db01.example.com qr01db01
- 192.0.2.15 qr01db02.example.com qr01db02
- 192.0.2.21 qr01-scan.example.com qr01-scan
- 192.0.2.24 qr01db01-vip.example.com qr01db01-vip
- 192.0.2.25 qr01db02-vip.example.com qr01db02-vip
- 192.168.1.11 qr01cel01-priv.example.com qr01cel01-priv
- 192.168.1.12 qr01cel02-priv.example.com qr01cel02-priv
- 192.168.1.13 qr01cel03-priv.example.com qr01cel03-priv
- 192.168.1.14 qr01db01-priv.example.com qr01db01-priv
- 192.168.1.15 qr01db02-priv.example.com qr01db02-priv
以上信息都确认正常，基本就能将listener的问题解决了。
本文转自 hsbxxl 51CTO博客，原文链接：http://blog.51cto.com/hsbxxl/857475，如需转载请自行联系原作者
