問題：當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的順序得以解決；還有其他的自動配置請各位提提方案喽，一起學習
![Oracle OCP 1Z0-050（61-70題）解析[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)