串連scan ip出現ORA-12545錯誤

GRID和資料庫版本：11.2.0.1

OS：Solaris 5.10

現象：在用戶端串連scan ip時報錯

sqlplus system/Oracle@210.10.4.5:1521/pdpt

ORA-12545: 因目標主機或對象不存在，串連失敗

但兩台資料庫伺服器之間連scan ip則沒問題

# cat /etc/hosts

::1    localhost     

127.0.0.1      localhost     

210.10.4.5      taian-pt-d1    loghost

210.10.4.6  taian-pt-d2

210.10.4.9  taian-pt-d1-vip

210.10.4.10  taian-pt-d2-vip

210.10.4.7  pdpt-cluster-scan

192.168.2.1  taian-pt-d1-priv

192.168.2.2  taian-pt-d2-priv

 

經檢查scan ip監聽正常，用戶端能ping通scan ip

-bash-3.2$ crs_stat -trac資源服務正常

Name          Type          Target    State    Host       

------------------------------------------------------------

ora.ARCHDG.dg  ora....up.type ONLINE    ONLINE    taian-pt-d1

ora.DATADG.dg  ora....up.type ONLINE    ONLINE    taian-pt-d1

ora....ER.lsnr ora....er.type ONLINE    ONLINE    taian-pt-d1

ora....N1.lsnr ora....er.type ONLINE    ONLINE    taian-pt-d2

ora.OCRVT.dg  ora....up.type ONLINE    ONLINE    taian-pt-d1

ora.asm        ora.asm.type  ONLINE    ONLINE    taian-pt-d1

ora.eons      ora.eons.type  ONLINE    ONLINE    taian-pt-d1

ora.gsd        ora.gsd.type  OFFLINE  OFFLINE             

ora....network ora....rk.type ONLINE    ONLINE    taian-pt-d1

ora.oc4j      ora.oc4j.type  OFFLINE  OFFLINE             

ora.ons        ora.ons.type  ONLINE    ONLINE    taian-pt-d1

ora.pdpt.db    ora....se.type ONLINE    ONLINE    taian-pt-d1

ora.scan1.vip  ora....ip.type ONLINE    ONLINE    taian-pt-d2

ora....SM1.asm application    ONLINE    ONLINE    taian-pt-d1

ora....D1.lsnr application    ONLINE    ONLINE    taian-pt-d1

ora....-d1.gsd application    OFFLINE  OFFLINE             

ora....-d1.ons application    ONLINE    ONLINE    taian-pt-d1

ora....-d1.vip ora....t1.type ONLINE    ONLINE    taian-pt-d1

ora....SM2.asm application    ONLINE    ONLINE    taian-pt-d2

ora....D2.lsnr application    ONLINE    ONLINE    taian-pt-d2

ora....-d2.gsd application    OFFLINE  OFFLINE             

ora....-d2.ons application    ONLINE    ONLINE    taian-pt-d2

ora....-d2.vip ora....t1.type ONLINE    ONLINE    taian-pt-d2

 

-bash-3.2$ lsnrctl status LISTENER_SCAN1scan IP監聽正常

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 06-MAR-2012 09:36:50

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))

STATUS of the LISTENER

------------------------

Alias                    LISTENER_SCAN1

Version                  TNSLSNR for Solaris: Version 11.2.0.1.0 - Production

Start Date                29-FEB-2012 13:42:48

Uptime                    5 days 19 hr. 54 min. 1 sec

Trace Level              off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File  /home/app/grid_home/network/admin/listener.ora

Listener Log File        /home/app/grid_home/log/diag/tnslsnr/taian-pt-d2/listener_scan1/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=210.10.4.7)(PORT=1521)))

Services Summary...

Service "PDPT" has 2 instance(s).

  Instance "PDPT1", status READY, has 1 handler(s) for this service...

  Instance "PDPT2", status READY, has 1 handler(s) for this service...

The command completed successfully

 

SQL> show parameter listener資料庫執行個體的local_listener參數內容為vip的主機名稱

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

listener_networks                    string

local_listener                      string      (DESCRIPTION=(ADDRESS_LIST=(AD

                                                DRESS=(PROTOCOL=TCP)(HOST=taia

                                                n-pt-d2-vip)(PORT=1521))))

remote_listener                      string      pdpt-cluster-scan:1521

 

 

 

經Metalink上尋找，發現這是11.2.0.1的小bug：“ORA-12545 or ORA-12537 while connecting to RAC through SCAN name[ID 970619.1]”，11.2.0.2及之後的版本就修複好了

Client is able to resolve all Fully Qualified Domain Name (FQDN) SCAN and VIP name but not short ones(without domain name) as its in different domain; even though FQDN names were specified during Grid Infrastructure setup, due to bug 9150053 by default DBCA set database parameter local_listener to short node VIP name while database is created.

因此只要把資料庫執行個體參數local_listener內的host改為vip的IP地址即可。

NODE1：

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=210.10.4.9)(PORT=1521))))' scope=both sid='PDPT1';

System altered.

SQL> alter system register;

System altered.

NODE2：

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=210.10.4.10)(PORT=1521))))' scope=both sid='PDPT2';

System altered.

SQL> alter system register;

System altered.

 

參考：How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name [ID 975457.1]

ORA-12545 or ORA-12537 while connecting to RAC through SCAN name[ID 970619.1]

11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1]