2018-06-07 KK日记，ORA-12545故障处理

一、案例

某dba反馈前几天刚安装好的rac，让一个小白碰过后，就变得不能用scan-ip访问了，报如下错误“ora-12545 Connect failed because target host or object does not exist”;但是用vip是可以访问的。

二、问题

为什么使用scan-vip连接DB时会报“ora-12545 Connect failed because target host or object does not exist”,用VIP就不会?

三、数据收集与分析

3.1 网络检查

根据经验，这时最有可能是网络问题，于是执行如下检查：

• 客户端上执行telnet , tnsping都没有发现异常。
• 服务器端上检查没有实施iptables ,selinux等网络限制。

3.2 监听服务检查

scan与local listener服务检查:

su - grid
grid> lsnrctl status
............服务正常
grid> lsnrctl status LISTENER_SCAN1
............服务正常

分析：监听服务正常。

3.3 日志检查

• 实例、集群日志没有发现异常。
• scan的监听日志发现客户端已经与scan ip 建立连接。
• local listener的日志没有发现连接失败与成功的记录。

分析：根据oracle的原理，scan listener接到客户端连接请求后会转交local listener提供服务，但是从日志上看，local listener就没有收到scan listener的移交请求。

3.4 案例收集

根据错误号在support.oracle.com下搜查相关的案例，结果如下：[已过滤无效结果] Client Connection to RAC Intermittently Fails-ORA-12545 TNS: Host or Object Does not Exist (文档 ID 364855.1)

CAUSE

Whe LOCAL_LISTENER is not set at all or is not set correctly, PMON can register both the VIP hostname and the physical hostname with the REMOTE_LISTENER.  This is incorrect in RAC but if it does occur, the client can intermittently be routed to a listener endpoint on either of these addresses.  If the client is unable to resolve the physical hostname or even the vip hostname, the connection will fail with ORA-12545.

Additionall, if the VIP hostname cannot be resolved, but the VIP numerical address is reachable; then this VIP numerical address should be used as the HOST value for LOCAL_LISTENER instead.

And important first set is to check the output for :  lsnrctl services <listener_name>

You should not see the physical hostname in the handler information for your RAC instances.   Here's an example of an incorrect lsnrctl services <listener_name> output for a 2-node RAC cluster:

lsnrctl services oracle_rac_listener 
Service "oracle.oracle.com" has 2 instance(s). 
Instance "orcl1", status READY, has 2 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:0 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myphysicalhost1)(PORT=1521)) 
"DEDICATED" established:326 refused:0 state:ready 
LOCAL SERVER 

Instance "orcl2", status READY, has 1 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:394371 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myphysicalhost2)(PORT=1521))
 

Here's an example of what it ought to look like when LOCAL_LISTENER is configured correctly:

lsnrctl services oracle_rac_listener 
Service "oracle.oracle.com" has 2 instance(s). 
Instance "orcl1", status READY, has 2 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:0 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myVIP_host1)(PORT=1521)) 
"DEDICATED" established:326 refused:0 state:ready 
LOCAL SERVER 

Instance "orcl2", status READY, has 1 handler(s) for this service... 
Handler(s): 
"DEDICATED" established:394371 refused:0 state:ready 
REMOTE SERVER 
(ADDRESS=(PROTOCOL=TCP)(HOST=myVIP_host2)(PORT=1521))

 

SOLUTION

Log in with privileges to the instance and issue the following commands so that LOCAL_LISTENER is set correctly:

 

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node1>))" scope=both sid='INSTANCE_NAME1';

Do the same for the 2nd instance where host is set to the <vip_host_node2> and the sid is set to the 2nd instance name.

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node2>))" scope=both sid='INSTANCE_NAME2'; 



Now the correct address using the VIP hostname will be registered against the listeners in the cluster.

The client should be able to resolve all forms of the VIP host via their /etc/hosts file.  i.e. short name and fully qualified.

 

In the case that a VIP hostname cannot be resolved by the client, the following syntax can be used with LOCAL_LISTENER to tell the client to connect using an IP address instead of the VIP hostname

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=1.2.3.4))"
... where "1.2.3.4" is the IP address of your VIP interface

分析：大概的意思就是说local_listener没有设置或设置不正确，remote_listener就会把vip及主机名都注册为对接的借口，如果客户端不能解释主机名或 vip时就会报“ora-12545"错误。

3.5 检查参数

show parameter listener
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=rac1                                               -vip)(PORT=1521))))
remote_listener                      string      rac-scan:1521

分析：rac1-vip没有在DNS上解释，也没有添加在客户端的hosts上。

3.6 修复

修改local_listener以VIP连接:

su - oracle
sqlplus / as sysdba
alter system set local_listener="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.1)(PORT=1521))))" scope=both sid='rac1';
alter system set local_listener="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.2)(PORT=1521))))" scope=both sid='rac2';

su - grid
srvctl stop listener
srvctl stop scan_listener


srvctl start listener
srvctl start scan_listener

lsnrctl status 
lsnrctl status LISTENER_SCAN1

重试，故障解决。

四、结论

• 估计小白是修改过loacl_listener这个参数，因为与其他库对比发现，默认就是使用vip地址的。
• scan listener接收到请求后，验证通过后就会把请求移交给local_listener处理，这时客户端就会收到一个答复“请连接local_listener参数所指定值”，此时客户端就会连接host所指定的值，如解释不了，就会报错“ora-12545”
• 3.6方法验证有效

五、延伸阅读

PRCR-1006 : Failed to add resource ora.lhrdg.db for lhrdg
PRCR-1071 : Failed to register or update resource ora.lhrdg.db
CRS-2566: User 'oracle' does not have sufficient permissions to operate on resource 'ora.LISTENER_LHRDG.lsnr', which is part of the dependency specification.
DBCA_PROGRESS : 100%
 
[ZFXDESKDB3:oracle]:/oracle>dbca -silent -createDatabase -templateName General_Purpose.dbc \
> -gdbname LHRDG  -sid LHRDGZK \
> -sysPassword lhr -systemPassword lhr \
> -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination '/arch' \
> -storageType ASM -asmsnmpPassword lhr  -diskGroupName 'DATA' -responseFile NO_VALUE \
> -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -automaticMemoryManagement true \
> -totalMemory 1024  -nodeinfo ZFXDESKDB3,ZFXDESKDB4
 
 
 
Copying database files
100% complete
Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/LHRDG/LHRDG.log" for further details.
[ZFXDESKDB3:oracle]:/oracle>
[ZFXDESKDB3:oracle]:/oracle>
[ZFXDESKDB3:oracle]:/oracle>
[ZFXDESKDB3:oracle]:/oracle>more /oracle/app/oracle/cfgtoollogs/dbca/LHRDG/LHRDG.log"
>
>
[ZFXDESKDB3:oracle]:/oracle>more /oracle/app/oracle/cfgtoollogs/dbca/LHRDG/LHRDG.log
Copying database files
PRCR-1006 : Failed to add resource ora.lhrdg.db for lhrdg
PRCR-1071 : Failed to register or update resource ora.lhrdg.db
CRS-2566: User 'oracle' does not have sufficient permissions to operate on resource 'ora.LISTENER_LHRDG.lsnr', which is part of the dependency specification.
DBCA_PROGRESS : 100%
 
 
[ZFXDESKDB3:oracle]:/oracle>oerr crs 2566
2566, 1, "User '%s' does not have sufficient permissions to operate on resource '%s', which is part of the dependency specification."
// *Cause: User does not have permissions to operate on the resource as it will prevent the current resource from starting or staying online in future.
// *Action: The user performing the operation must have access
// privileges to operate on the entire resource dependency tree. The
// user must either be given those privileges by modifying the dependent
// resources' access rights or another user having permissions should
// perform this operation.
 
《《《《---------------------简单点还是没有权限，且和 resource 'ora.LISTENER_LHRDG.lsnr'有关，这个资源是我创建dg的时候单独开的监听，且看看该资源的配置情况
 
[ZFXDESKDB3:grid]:/home/grid>srvctl config listener -l LISTENER_LHRDG -a
Name: LISTENER_LHRDG
Network: 1, Owner: root《《《《----------很明显的错误，owner是root，
Home: <CRS home>
  /oracle/app/11.2.0/grid on node(s) zfxdeskdb3,zfxdeskdb4
End points: TCP:1523
 
 
《《《《-----下边删除该资源重新添加
[ZFXDESKDB3:grid]:/home/grid>srvctl remove listener -l LISTENER_LHRDG
PRCR-1025 : Resource ora.LISTENER_LHRDG.lsnr is still running
[ZFXDESKDB3:grid]:/home/grid>crsctl  stop resource ora.LISTENER_LHRDG.lsnr
CRS-0245:  User doesn't have enough privilege to perform the operation
CRS-4000: Command Stop failed, or completed with errors.
[ZFXDESKDB3:grid]:/home/grid>exit
[ZFXDESKDB3:root]:/>
[ZFXDESKDB3:root]:/>
[ZFXDESKDB3:root]:/>crsctl  stop resource ora.LISTENER_LHRDG.lsnr
CRS-2673: Attempting to stop 'ora.LISTENER_LHRDG.lsnr' on 'zfxdeskdb4'
CRS-2673: Attempting to stop 'ora.LISTENER_LHRDG.lsnr' on 'zfxdeskdb3'
CRS-2677: Stop of 'ora.LISTENER_LHRDG.lsnr' on 'zfxdeskdb4' succeeded
CRS-2677: Stop of 'ora.LISTENER_LHRDG.lsnr' on 'zfxdeskdb3' succeeded
[ZFXDESKDB3:root]:/>
[ZFXDESKDB3:root]:/>
[ZFXDESKDB3:root]:/>srvctl remove listener -l LISTENER_LHRDG
[ZFXDESKDB3:root]:/>
[ZFXDESKDB3:root]:/>
[ZFXDESKDB3:root]:/>su - grid
[ZFXDESKDB3:grid]:/home/grid>
[ZFXDESKDB3:grid]:/home/grid>
[ZFXDESKDB3:grid]:/home/grid>srvctl add listener -l LISTENER_LHRDG -p 1523 -o $ORACLE_HOME
[ZFXDESKDB3:grid]:/home/grid>
[ZFXDESKDB3:grid]:/home/grid>srvctl config listener -l LISTENER_LHRDG -a
Name: LISTENER_LHRDG
Network: 1, Owner: grid
Home: <CRS home>
  /oracle/app/11.2.0/grid on node(s) zfxdeskdb3,zfxdeskdb4
End points: TCP:1523