10G RAC節點2宕機通過修改listener.ora實現用戶端通過節點2VIP串連到資料庫

根據周亮ORACLE DBA實戰裡的一個實驗做的。
環境描述：兩節點10G RAC環境，節點2宕機。此時用戶端通過原節點2 VIP地址無法串連至資料庫。用戶端較多修改不便需要在伺服器上進行修改。


1.檢查節點1監聽狀態、/etc/hosts 、IP資訊[oracle@rac1 admin]$ ifconfig
eth0      Link encap:Ethernet  HWaddr 08:00:27:93:AD:F2  
          inet addr:192.168.57.219  Bcast:192.168.57.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:112453 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2496 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:68881461 (65.6 MiB)  TX bytes:332010 (324.2 KiB)

eth0:1    Link encap:Ethernet  HWaddr 08:00:27:93:AD:F2  
          inet addr:192.168.57.221  Bcast:192.168.57.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth0:2    Link encap:Ethernet  HWaddr 08:00:27:93:AD:F2  
          inet addr:192.168.57.222  Bcast:192.168.57.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1      Link encap:Ethernet  HWaddr 08:00:27:33:81:45  
          inet addr:192.168.58.1  Bcast:192.168.58.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1750 errors:0 dropped:0 overruns:0 frame:0
          TX packets:150571 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:150427 (146.9 KiB)  TX bytes:130002468 (123.9 MiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:82328 errors:0 dropped:0 overruns:0 frame:0
          TX packets:82328 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:13965995 (13.3 MiB)  TX bytes:13965995 (13.3 MiB)
[oracle@rac1 admin]$ 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
192.168.57.219   rac1.bys.com   rac1
192.168.57.220   rac2.bys.com   rac2
192.168.57.221    rac1-vip
192.168.57.222    rac2-vip
192.168.58.1      rac1-priv
192.168.58.2      rac2-priv
[oracle@rac1 admin]$ lsnrctl status LISTENER_RAC1

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-APR-2014 23:22:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RAC1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                09-APR-2014 23:19:51
Uptime                    0 days 0 hr. 2 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/dbhome_1/network/log/listener_rac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.57.221)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.57.219)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
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 "RAC" has 1 instance(s).
  Instance "RAC1", status READY, has 2 handler(s) for this service...
Service "RAC_XPT" has 1 instance(s).
  Instance "RAC1", status READY, has 2 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....C1.inst application    ONLINE    ONLINE    rac1        
ora....C2.inst application    ONLINE    OFFLINE               
ora.RAC.db     application    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   application    ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    OFFLINE               
ora....C2.lsnr application    ONLINE    OFFLINE               
ora.rac2.gsd   application    ONLINE    OFFLINE               
ora.rac2.ons   application    ONLINE    OFFLINE               
ora.rac2.vip   application    ONLINE    ONLINE    rac1        

################################################################
2.修改節點1的監聽設定檔listener.ora----修改前要備份下原監聽設定檔：
[oracle@rac1 admin]$ cat listener.orabak
# listener.ora.rac1 Network Configuration File: /u01/app/oracle/product/dbhome_1/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
LISTENER_RAC1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.219)(PORT = 1521)(IP = FIRST))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
######修改後：--修改的內容就是把(IP = FIRST)去掉，這樣監聽會運行在主機的所有IP上（這裡是192.168.57.219、兩個VIP：192.168.57.221、192.168.57.222）。
[oracle@rac1 admin]$ cat listener.ora
# listener.ora.rac1 Network Configuration File: /u01/app/oracle/product/dbhome_1/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.

LISTENER_RAC1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
      )
      (ADDRESS_LIST =
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

################
查看tnsnames.ora檔案：
[oracle@rac1 admin]$ cat tnsnames.ora
RAC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC)
    )
  )
RAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC)
    )
  )
查看監聽狀態：
lsnrctl stop LISTENER_RAC1
lsnrctl start LISTENER_RAC1
[oracle@rac1 admin]$ lsnrctl status LISTENER_RAC1
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 09-APR-2014 22:48:11
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RAC1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                09-APR-2014 22:46:50
Uptime                    0 days 0 hr. 1 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/dbhome_1/network/log/listener_rac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.bys.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
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 "RAC" has 1 instance(s).
  Instance "RAC1", status READY, has 2 handler(s) for this service...
Service "RAC_XPT" has 1 instance(s).
  Instance "RAC1", status READY, has 2 handler(s) for this service...
The command completed successfully
#串連測試[oracle@rac1 admin]$ sqlplus system/system@rac2
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 9 22:43:32 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
RAC1

SQL> exit

使用192.168.57.219、192.168.57.221、192.168.57.222、192.168.58.1這四個IP均可以登陸：--也驗證了listener.ora中(HOST = 主機名稱會運行在主機的所有IP上。
[oracle@rac1 admin]$ sqlplus system/system@192.168.57.221:1521/rac
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 9 22:53:30 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac1 admin]$ sqlplus system/system@192.168.57.222:1521/rac
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 9 22:53:37 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> ei^H
SP2-0042: unknown command "e" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac1 admin]$ sqlplus system/system@192.168.57.219:1521/rac
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 9 22:53:46 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>

[oracle@rac1 admin]$ sqlplus system/system@192.168.58.1:1521/rac
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 9 22:55:31 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> exit