資料庫VIP地址無法訪問（一）
客戶的資料庫出現VIP地址無法訪問的情況。
這一篇描述問題的診斷。
客戶的RAC環境重建後，發現兩個節點的VIP都無法訪問，開始認為是網路問題，但是隨後發現整個RAC重啟後，其中一個節點的VIP可以訪問，而另一個節點的VIP仍然無法訪問，因此判斷可能是RAC本身的問題。
聽到問題描述後，第一個判斷是否VIP沒有啟動，登入資料庫伺服器後進行了檢查：
oracle@racdb1 $ lsnrctl status
LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 18-APR-2012 10:22:02
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RACDB1
Version TNSLSNR for
Solaris: Version 10.2.0.4.0 - Production
Start Date 25-MAR-2012
17:16:47
Uptime 23 days 17 hr.
5 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
/u01/oracle/product/10.2/database/network/admin/listener.ora
Listener Log File
/u01/oracle/product/10.2/database/network/log/listener_racdb1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.8.60.201)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.8.60.1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status
BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status
BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status
UNKNOWN, has 1 handler(s) for this service...
Service "racdb" has 2 instance(s).
Instance "racdb1", status
READY, has 2 handler(s) for this service...
Instance "racdb2", status
READY, has 1 handler(s) for this service...
Service "racdbXDB" has 2 instance(s).
Instance "racdb1", status
READY, has 1 handler(s) for this service...
Instance "racdb2", status
READY, has 1 handler(s) for this service...
Service "racdb_XPT" has 2 instance(s).
Instance "racdb1", status
READY, has 2 handler(s) for this service...
Instance "racdb2", status
READY, has 1 handler(s) for this service...
The command completed successfully
節點2：
oracle@racdb2 $ lsnrctl status
LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 18-APR-2012 10:19:00
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_RACDB2
Version TNSLSNR for
Solaris: Version 10.2.0.4.0 - Production
Start Date 25-MAR-2012
17:14:28
Uptime 23 days 17 hr. 4 min. 31 sec
Trace Level off
Security ON: Local OS
Authentication
SNMP OFF
Listener Parameter File
/u01/oracle/product/10.2/database/network/admin/listener.ora
Listener Log File /u01/oracle/product/10.2/database/network/log/listener_racdb2.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.8.60.202)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.8.60.3)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status
BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status
BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status
UNKNOWN, has 1 handler(s) for this service...
Service "racdb" has 2 instance(s).
Instance "racdb1", status
READY, has 1 handler(s) for this service...
Instance "racdb2", status
READY, has 2 handler(s) for this service...
Service "racdbXDB" has 2 instance(s).
Instance "racdb1", status
READY, has 1 handler(s) for this service...
Instance "racdb2", status
READY, has 1 handler(s) for this service...
Service "racdb_XPT" has 2 instance(s).
Instance "racdb1", status
READY, has 1 handler(s) for this service...
Instance "racdb2", status
READY, has 2 handler(s) for this service...
The command completed successfully
兩個節點的監聽都是正常的：
oracle@racdb1 $ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 18 10:20:06 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names
string racdb
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener
string
remote_listener
string LISTENERS_RACDB
oracle@racdb1 $ cd $ORACLE_HOME/network/admin
oracle@racdb1 $ more listener.ora
# listener.ora.racdb1 Network Configuration File:
/u01/oracle/product/10.2/database/network/admin/listener.ora.racdb1
# Generated by Oracle configuration tools.
LISTENER_RACDB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb1-vip)(PORT
= 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST =
10.8.60.1)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_RACDB1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =
/u01/oracle/product/10.2/database)
(PROGRAM = extproc)
)
)
oracle@racdb1 $ more tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/oracle/product/10.2/database/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENERS_RACDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb1-vip)(PORT
= 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb2-vip)(PORT
= 1521))
)
RACDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb2-vip)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
RACDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb1-vip)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb1-vip)(PORT
= 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = racdb2-vip)(PORT
= 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
oracle@racdb1 $ tnsping racdb
TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 18-APR-2012 10:24:08
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdb1-vip)(PORT
= 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = racdb2-vip)(PORT = 1521))
(LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb)))
OK (0 msec)
oracle@racdb1 $ tnsping racdb1
TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 18-APR-2012 10:24:11
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdb1-vip)(PORT
= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)))
OK (10 msec)
oracle@racdb1 $ tnsping racdb2
TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 18-APR-2012 10:24:12
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racdb2-vip)(PORT
= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)))
OK (0 msec)
資料庫中設定了REMOTE_LISTENERS引數，而且TNSNAMES.ORA中的REMOTE_LISTENERS的配置也沒有異常。透過tnsping命令檢查配置同樣沒有發現異常。
oracle@racdb2 $ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 18 10:19:18 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn a/a@10.8.60.3/racdb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn a/a@10.8.60.1/racdb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn a/a@10.8.60.202/racdb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn a/a@10.8.60.201/racdb
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> exit
節點2上透過PUBLIC IP和VIP分別連線兩個例項，發現服務名配置沒有問題，透過VIP也可以訪問資料庫，並沒有出現VIP不同的情況。
oracle@racdb1 $ more /etc/hosts
#
# Internet host table
#
::1 localhost
127.0.0.1 localhost
10.8.60.1 racdb1 racdb1.com loghost
10.8.60.201 racdb1-vip
192.168.60.201 racdb1-priv
10.8.60.3 racdb2
10.8.60.202 racdb2-vip
192.168.60.202 racdb2-priv
#BackupServer
10.8.48.26 gz-bak
oracle@racdb2 $ more /etc/hosts
#
# Internet host table
#
::1 localhost
127.0.0.1 localhost
10.8.60.1 racdb1
10.8.60.201 racdb1-vip
192.168.60.201 racdb1-priv
10.8.60.3 racdb2 racdb2.com loghost
10.8.60.202 racdb2-vip
192.168.60.202 racdb2-priv
#BackupServer
10.8.48.26 gz-bak
分別在兩個節點上檢查hosts檔案的配置，結果同樣正常。
root@racdb1 # ./crs_stat -t
Name Type Target State
Host
------------------------------------------------------------
ora....b1.inst application ONLINE ONLINE
racdb1
ora....b2.inst application ONLINE ONLINE
racdb2
ora.racdb.db application ONLINE
ONLINE racdb2
ora....SM1.asm application ONLINE ONLINE
racdb1
ora....B1.lsnr application ONLINE ONLINE
racdb1
ora.racdb1.gsd application ONLINE ONLINE
racdb1
ora.racdb1.ons application ONLINE ONLINE
racdb1
ora.racdb1.vip application ONLINE ONLINE
racdb1
ora....SM2.asm application ONLINE ONLINE
racdb2
ora....B2.lsnr application ONLINE ONLINE
racdb2
ora.racdb2.gsd application ONLINE ONLINE
racdb2
ora.racdb2.ons application ONLINE ONLINE
racdb2
ora.racdb2.vip application ONLINE ONLINE
racdb2
RAC的相同服務都正常啟動。
root@racdb1 # ./oifcfg getif
aggr1 192.168.60.0 global
cluster_interconnect
aggr2 10.8.60.0 global
public
root@racdb2 # ./oifcfg getif
aggr1 192.168.60.0 global
cluster_interconnect
aggr2 10.8.60.0 global
public
使用oifcfg檢查網路卡配置也未發現異常。
root@racdb1 # ifconfig -a
lo0: flags=2001000849
inet 127.0.0.1 netmask ff000000
aggr1: flags=1000843
inet 192.168.60.201 netmask
ffffff00 broadcast 192.168.60.255
ether 0:21:28:1a:89:43
aggr1:1: flags=1040843
inet 10.8.60.201 netmask ffffff00
broadcast 10.8.60.255
aggr2: flags=1000843
inet 10.8.60.1 netmask ffffff00
broadcast 10.8.60.255
ether 0:21:28:1a:89:42
root@racdb2 # ifconfig -a
lo0: flags=2001000849
inet 127.0.0.1 netmask ff000000
aggr1: flags=1000843
inet 192.168.60.202 netmask
ffffff00 broadcast 192.168.60.255
ether 0:21:28:1a:89:6b
aggr2: flags=1000843
inet 10.8.60.3 netmask ffffff00
broadcast 10.8.60.255
ether 0:21:28:1a:89:6a
aggr2:1: flags=1040843
inet 10.8.60.202 netmask ffffff00
broadcast 10.8.60.255
透過檢查當前的IP地址資訊，終於發現了問題所在，節點2上的VIP是正常的，但是節點1的VIP漂到了PRIVATE IP對應的網路卡上。
對於這種情況，當前RAC的各個節點都可以正常訪問VIP，因為無論是PUBLIC網路卡還是PRIVATE網路卡，兩個節點都可以訪問。但是對於資料庫外的其他伺服器而言，是不可能訪問RAC的PRIVATE網路的，因此造成了節點2的VIP可以訪問，而節點1的VIP不可訪問。
那麼為什麼Oracle的VIP會漂到PRIVATE網路卡上呢：
root@racdb1 # exit
oracle@racdb1 $ srvctl config nodeapps -n racdb1
racdb1 racdb1 /u01/oracle/product/10.2/database
oracle@racdb1 $ srvctl config nodeapps -n racdb1 -a
VIP exists.: /racdb1-vip/10.8.60.201/255.255.255.0/aggr1:aggr2
oracle@racdb1 $ srvctl config nodeapps -n racdb2 -a
VIP exists.: /racdb2-vip/10.8.60.202/255.255.255.0/aggr1:aggr2
顯然是RAC安裝過程中配置錯誤導致的，在設定VIP使用的網路卡資訊時，應該選擇PUBLIC網路，而當前將兩個網路卡都選擇上了。這正好說明了為什麼有時VIP是不能訪問的，而重啟RAC節點後，一個節點的VIP就可以訪問了。
如果VIP啟動的時候選擇了PUBLIC網路卡，那麼VIP地址對外就是可見的，而如果VIP選擇了PRIVATE網路卡，那麼VIP地址對外就不可見了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-722519/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫VIP地址無法訪問（二）資料庫
- 本機資料庫資料庫鏈無法訪問遠端資料庫資料庫
- discuz資料庫搬家，改密碼後無法訪問解決辦法資料庫密碼
- PUBLIC資料庫鏈無法刪除的問題（一）資料庫
- github無法訪問Github
- 用JDBC訪問一個資料庫JDBC資料庫
- JDBC資料庫訪問JDBC資料庫
- win10QQ無法訪問個人資料夾解決辦法Win10
- 資料庫無法update資料庫
- 用JDBC訪問一個資料庫(轉)JDBC資料庫
- 一次資料庫無法登陸的問題及排查資料庫
- 一次資料庫無法登陸的"問題"及排查資料庫
- 資料庫無法建立資料庫檢視資料庫
- git hub 無法訪問 訪問速度慢Git
- GitHub無法訪問或訪問緩慢解決辦法Github
- Oracle資料庫訪問控制Oracle資料庫
- 異構資料庫訪問資料庫
- 釋出一個.NET資料庫訪問類資料庫
- Mysq無法建立資料庫資料庫
- 無法訪問GitHub網站及無法正常操作倉庫的處理方法Github網站
- linux 無法訪問域名Linux
- 資料夾損壞無法訪問. 不知道如何找到呢？
- 無法根據TZ_OFFSET的值進行資料訪問
- jboss訪問資料庫的問題資料庫
- 訪問github出現無法訪問此網站Github網站
- 控制資料訪問（一）
- IIS無法訪問動態連結庫DLL的原因
- Oracle日常問題處理-資料庫無法啟動Oracle資料庫
- Oracle日常問題-資料庫無法啟動(案例二)Oracle資料庫
- 資料庫shutdown之後無法啟動的問題資料庫
- Windows 下處理資料庫無法啟動問題Windows資料庫
- PUBLIC資料庫鏈無法刪除的問題（二）資料庫
- 掉電無法啟動資料庫問題解決資料庫
- 資料庫訪問最佳化法則詳解之返回更少的資料資料庫
- 【資料庫優化】面向程式設計師的資料庫訪問效能優化法則資料庫優化程式設計師
- 外網訪問MySQL資料庫MySql資料庫
- Oracle資料庫限制訪問IPOracle資料庫
- C#訪問MySQL資料庫C#MySql資料庫