在Oracle 11g Data Guard中實現Connect Time Failover & Transparent Application Failover(TAF)____Oracle

背景介紹：

在switchover或failover時主庫進行切換後，用戶端獲得自己重連主庫的能力。

環境修改：
1.修改$ORACLE_HOME/network/admin/tnsnames.ora

PRIOCM=  (DESCRIPTION =    (ADDRESS_LIST=      (ADDRESS = (PROTOCOL = TCP)(HOST = ocm1.example.com)(PORT = 1521))  --主庫      (ADDRESS = (PROTOCOL = TCP)(HOST = ocm2.example.com)(PORT = 1521))  --備庫    )    (CONNECT_DATA =      (SERVICE_NAME = priocm)  --指定service名字    )  )

2.在主庫中建立並啟動service

begin dbms_service.create_service('priocm','priocm');  --service_name,network_nameend;/begin DBMS_SERVICE.START_SERVICE('priocm');end;/

3.在主庫中建立啟動觸發器，當database_role為非主庫時關閉service

create or replace trigger priocmtrigg after startup on databasedeclare v_role varchar(30);begin select database_role into v_role from v$database; if v_role = 'PRIMARY' then DBMS_SERVICE.START_SERVICE('priocm'); else DBMS_SERVICE.STOP_SERVICE('priocm'); end if;end;/

4.修改servicefailover參數

begin dbms_service.modify_service ('priocm', FAILOVER_METHOD => 'BASIC', --BASIC：是指在感知到節點故障時才建立到其他執行個體的串連。PRECONNECT: 是在最初建立串連時就同時建立到所有執行個體的串連，當發生故障時，立刻就可以切換到其他鏈路 FAILOVER_TYPE => 'SELECT',  --session和select，這2種方式對於未提交的事務都會自動復原，區別在於對select 語句的處理，對於select，使用者正在執行的select語句會被轉移到新的執行個體上，在新的節點上繼續返回後續結果集，而已經返回的記錄集則拋棄。 FAILOVER_RETRIES => 200,    --重試次數 FAILOVER_DELAY => 1);     --稍候再試時間end;/

切換實驗：

類比在用戶端通過service(priocm)串連

[oracle@ocm1 admin]$ sqlplus sys/oracle@priocm as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 25 16:07:33 2017Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@priocm> select db_unique_name,name,database_role,switchover_status from v$database;  --串連到當前主庫DB_UNIQUE_NAME       NAME DATABASE_ROLE  SWITCHOVER_STATUS------------------------------ --------- ---------------- --------------------aux       PROD3 PRIMARY  TO STANDBY----------------------------------------------------------------------------------------------------------------省略switchover過程，http://blog.csdn.net/u013169075/article/details/73555409----------------------------------------------------------------------------------------------------------------

switchover後，再次類比在用戶端通過service(priocm)串連

SYS@priocm> /select db_unique_name,name,database_role,switchover_status from v$database*ERROR at line 1:ORA-03113: end-of-file on communication channel   --嘗試操作主庫拋出錯誤Process ID: 4810Session ID: 28 Serial number: 7ERROR:ORA-03114: not connected to ORACLESYS@priocm> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options--嘗試重連主庫[oracle@ocm1 ~]$ sqlplus sys/oracle@priocm as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 25 16:49:45 2017Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@priocm> select db_unique_name,name,database_role,switchover_status from v$database;  --已經串連到新主庫DB_UNIQUE_NAME       NAME DATABASE_ROLE  SWITCHOVER_STATUS------------------------------ --------- ---------------- --------------------PROD3       PROD3 PRIMARY  FAILED DESTINATION

參考資料：
http://www.oracle.com/technetwork/cn/articles/database-performance/oracle-rac-connection-mgmt-1650424-zhs.html
https://uhesse.com/2009/08/19/connect-time-failover-transparent-application-failover-for-data-guard/