Oracle11gR2 RAC 使用scan IP無法連線資料庫(ORA-12545)問題解決
環境:
[grid@rac1 ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
# Public
192.168.2.101 rac1
192.168.2.102 rac2
# Private
192.168.0.101 rac1-priv
192.168.0.102 rac2-priv
# Virtual
192.168.2.111 rac1-vip
192.168.2.112 rac2-vip
# SCAN
192.168.2.200 rac-scan
現象:
在windows客戶端sqlplus工具使用scan的IP無法連線,報錯如下:
C:\Users\WJW>sqlplus system/qweasd@192.168.2.200:1521/orcl
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 12:35:28 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
檢查各元件狀態,正常:
[grid@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type ONLINE ONLINE rac1
ora.asm ora.asm.type ONLINE ONLINE rac1
ora.eons ora.eons.type ONLINE ONLINE rac1
ora.gsd ora.gsd.type ONLINE ONLINE rac1
ora....network ora....rk.type ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type ONLINE ONLINE rac2
ora.ons ora.ons.type ONLINE ONLINE rac1
ora.orcl.db ora....se.type ONLINE ONLINE rac1
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type ONLINE ONLINE rac1
檢查監聽器狀態,正常:
[grid@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2012 13:27:40
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-MAY-2012 12:19:51
Uptime 0 days 1 hr. 7 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.111)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2012 13:28:23
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-MAY-2012 12:19:51
Uptime 0 days 1 hr. 8 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.112)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.102)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@rac1 ~]$ lsnrctl status listener_scan1
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2012 13:29:09
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 Linux: Version 11.2.0.1.0 - Production
Start Date 17-MAY-2012 12:21:31
Uptime 0 days 1 hr. 7 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/log/diag/tnslsnr/rac1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.200)(PORT=1521)))
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 2 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully
原因:
metalink說明: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地址即可
解決辦法:
rac1:
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=rac1
-vip)(PORT=1521))))
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.111)(PORT=1521))))' scope=both sid='orcl1';
SQL> alter system register;
rac2:
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=rac2
-vip)(PORT=1521))))
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.112)(PORT=1521))))' scope=both sid='orcl2';
SQL> alter system register;
隨後使用windows客戶端連線成功:
C:\Users\WJW>sqlplus system/qweasd@192.168.2.200:1521/orcl
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 13:14:39 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29067253/viewspace-2148268/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 11g RAC通過SCAN IP連線資料庫 - JDBC連線串問題Oracle資料庫JDBC
- ORACLE DG為RAC時使用scan-ip連線資料庫會報 ORA-12545錯誤Oracle資料庫
- 使用 localhost 無法連線 MySQL 資料庫的解決方案localhostMySql資料庫
- 連線rac資料庫報ORA-12545資料庫
- scan-ip連線出現“ORA-12545”
- RAC連線的問題ORA-12545
- 連線scan ip出現ORA-12545錯誤
- 客戶端通過SCAN TNS無法連線ORA-12545客戶端
- ORACLE RAC中連線ScanIP報錯ORA-12545的問題解決Oracle
- HGAdmin無法連線本地資料庫解決方式資料庫
- 客戶端使用SCNAIP連線11G RAC資料庫報錯ORA-12545解決客戶端AI資料庫
- RAC連線的問題ORA-12545(二)
- 解決IBM DATA STUDIO無法連線資料庫問題,JDBC驅動不顯示問題。IBM資料庫JDBC
- 客戶端無法連線資料庫的小問題客戶端資料庫
- 解決無法連線SQL Server資料庫的方法BWSQLServer資料庫
- Windows無法配置此無線連線這個問題的解決辦法Windows
- IDEA無法連線docker中的資料庫的問題IdeaDocker資料庫
- 掉電無法啟動資料庫問題解決資料庫
- 11g RAC ORA-12545 解決方案 +11g scan IP新特性
- 應用使用JNDI,資料庫無法連線,導致的程序無法啟動問題處理資料庫
- 11.2.0.4 通過 scan ip 連線資料庫報TNS-12537 連線關閉問題總結資料庫
- picc某rac資料庫無法連線,資料庫處於開啟狀態。資料庫
- 解決Oracle 11gR2 RAC 無法在客戶端通過scanIP連線資料庫Oracle客戶端資料庫
- 寶塔皮膚無法遠端連線資料庫的解決方法資料庫
- sql server資料庫連線失敗/無法附加解決過程SQLServer資料庫
- WAMP無法連線mysql資料庫MySql資料庫
- MMNL absent ,資料庫無法連線資料庫
- 為什麼有些客戶沒有用11gR2 RAC中scan ip連線資料庫資料庫
- jdbc連線oracle rac資料庫的寫法JDBCOracle資料庫
- 解決sqlserver資料庫單一使用者無法刪除的問題SQLServer資料庫
- Oracle備庫無法連線主庫的問題分析Oracle
- 資料庫突然當機無法open的問題及解決資料庫
- 在RAC建立資料庫報無法建立“/etc/oratab"解決辦法資料庫
- 解決Oracle 11g R2 RAC 無法在客戶端通過scanIP連線資料庫Oracle客戶端資料庫
- 解決無法使用VI的問題
- 關於資料庫連線問題的一般解決方法資料庫
- 換ip軟體無法連線如何解決
- ajax資料無法更新問題原因及解決