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