redhat 6.4 oracle11g dataguard 主备切换客户端连接配置

问题:当oracle dataguard主备切换后,客户端连接时需要对客户端的tns文件进行修改后连接,那么能否使用另外一种方法来解决连接问题呢?我的设想是在能否在主备连接添加服务名,然后在客户端tns中进行failover的配置使得主备切换后无需更改tns文件只需重新连接就ok呢?


实验:

修改客户端tns文件配置为:

[[email protected] admin]$ vi tnsnames.ora 

CUBE=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521))
   (CONNECT_DATA=
   (SERVER=dedicated)
     (SERVICE_NAME=CUBE)))
JAKKI=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521))
   (CONNECT_DATA=
   (SERVER=dedicated)
     (SERVICE_NAME=JAKKI)))


dgserver=        
 (DESCRIPTION=
  (ADDRESS_LIST=
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521)))
  (CONNECT_DATA=
   (SERVICE_NAME=dgserver)))


主备两边都添加dgserver服务:

[[email protected] admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 8 21:05:00 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> show parameter service

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
service_names                        string
CUBE

SQL>  alter system set service_names='CUBE','dgserver';

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JAN-2018 21:09:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-JAN-2018 20:52:23
Uptime                    0 days 0 hr. 16 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/cube/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "CUBE" has 2 instance(s).
  Instance "CUBE", status UNKNOWN, has 1 handler(s) for this service...
  Instance "CUBE", status READY, has 1 handler(s) for this service...
Service "CUBEXDB" has 1 instance(s).
  Instance "CUBE", status READY, has 1 handler(s) for this service...
Service "CUBE_DGMGRL" has 1 instance(s).
  Instance "CUBE", status UNKNOWN, has 1 handler(s) for this service...
Service "dgserver" has 1 instance(s).
  Instance "CUBE", status READY, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 8 21:07:12 2018


Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter service

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
service_names                        string
JAKKI
SQL> alter system set service_names='JAKKI','dgserver';

System altered.

[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JAN-2018 21:09:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-JAN-2018 20:52:16
Uptime                    0 days 0 hr. 16 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/jakki/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "JAKKI" has 2 instance(s).
  Instance "JAKKI", status UNKNOWN, has 1 handler(s) for this service...
  Instance "JAKKI", status READY, has 1 handler(s) for this service...
Service "JAKKIXDB" has 1 instance(s).
  Instance "JAKKI", status READY, has 1 handler(s) for this service...
Service "JAKKI_DGMGRL" has 1 instance(s).
  Instance "JAKKI", status UNKNOWN, has 1 handler(s) for this service...
Service "dgserver" has 1 instance(s).
  Instance "JAKKI", status READY, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


测试客户端连接:

SQL> conn sys/[email protected] as sysdba
Connected.
SQL> select name from v$database;

NAME
------------------
CUBE

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
CUBE


主备切换:请参考http://blog.csdn.net/hzcyhujw/article/details/78962361

 关闭原先的主库;

测试连接:

SQL> conn sys/[email protected] as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
JAKKI

SQL> select name from v$database;

NAME
------------------
CUBE

测试结果:当主库关闭后,服务切换至备库,tns文件无需改动就可连接至现有的主库提供工作;

那如果当原主库修复后开启至mount状态或者open read only with apply 时呢?

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2257880 bytes
Variable Size             545262632 bytes
Database Buffers          289406976 bytes
Redo Buffers                2355200 bytes
Database mounted.
Database opened.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select name,database_role,protection_mode, SWITCHOVER_STATUS,open_mode from v$database;


NAME               DATABASE_ROLE                    PROTECTION_MODE                          SWITCHOVER_STATUS
------------------ -------------------------------- ---------------------------------------- ----------------------------------------
OPEN_MODE
----------------------------------------
CUBE               PHYSICAL STANDBY                 MAXIMUM AVAILABILITY                     NOT ALLOWED
READ ONLY WITH APPLY


测试:

SQL> conn sys/[email protected] as sysdba
Connected.
SQL> select open_mode,name from v$database;


OPEN_MODE                                NAME
---------------------------------------- ------------------
READ ONLY WITH APPLY                     CUBE

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
CUBE

此时不修改tns文件的时候讲会连接到备库的数据中,如果业务为此连接的话讲只能够是只读的模式影响业务正常运行,那将怎么处理呢? 这里最简单方法还是修改tns文件的ip顺序来解决,其他的方案解决请各位进行测试吧。

[[email protected] admin]$ vi tnsnames.ora 
CUBE=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521))
   (CONNECT_DATA=
   (SERVER=dedicated)
     (SERVICE_NAME=CUBE)))
JAKKI=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521))
   (CONNECT_DATA=
   (SERVER=dedicated)
     (SERVICE_NAME=JAKKI)))


dgserver=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))

  (CONNECT_DATA=
   (SERVICE_NAME=dgserver)))


SQL> conn sys/[email protected] as sysdba
Connected.
SQL> select open_mode,name from v$database;

OPEN_MODE                                NAME
---------------------------------------- ------------------
READ WRITE                               CUBE

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
JAKKI


总结:此tns文件配置只能够当主库完全不可以用后才使得客户端能够连接至新主库中,一旦原主库修复开启后,要么切换回原来的主备模式,要么就修改tns文件ip的顺序得以解决;还有其他的自动配置请各位提提方案喽,一起学习

;