oracle service 解决ADG 切换后自动连接

How To Configure Client Failover For Data Guard Connections Using Database Services (Doc ID 1429223.1)

APPLIES TO: Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. GOAL In a Data Guard environment  primary database  is open in read write mode and the standby database in read only mode for reporting purpose. This document describes how to setup clients to connect to Data Guard databases (primary and standby) and configure automatic client failover such that in case there is role change due to switchover or failover, the client connections should still be valid i.e. the clients that need to connect to read only standby should always connect to the current standby irrespective of which database in the Data Guard configuration is currently in standby role and same for primary connections. This goal is achieved via database services. In 11gR2, we have the concept of role-based database services. For Data Guard environments running older release, this is achieved via a database startup trigger. Pre 11.2 Configuration 11.2 or later Configuration Note: Starting from 12cR1 (12.1), you can consider using Global Data Services to seamlessly manage client connections for dataguard or Golden Gate replicated database Also check below technical brief for latest information on this topic: Client Failover Best Practices for Highly Available Oracle Databases http://www.oracle.com/technetwork/database/availability/client-failover-2280805.pdf Application Continuity with Oracle Database12c Release 2 http://www.oracle.com/technetwork/database/database-cloud/private/application-continuity-wp-12c-1966213.pdf SOLUTION Pre 11.2 Configuration: + On the current primary, create 2 services, one to connect to the primary (prim_db) and another (stby_db) to connect to the read only standby: SQL> exec dbms_service.create_service('prim_db','prim_db'); SQL> exec dbms_service.create_service('stby_db','stby_db'); + On the current primary, start the service that is needed to connect to primary: SQL> exec dbms_service.start_service('prim_db'); + Now, on the current primary, create the trigger to manage these services accordingly: CREATE OR REPLACE TRIGGER startDgServices after startup on database DECLARE db_role VARCHAR(30); db_open_mode VARCHAR(30); BEGIN SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE; IF db_role = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('prim_db'); END IF; IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN DBMS_SERVICE.START_SERVICE('stby_db'); END IF; END; / + Note down the current online redo log sequence on primary and switch the current logfile: SQL> select thread#, sequence# from v$log where status = 'CURRENT'; SQL> alter system archive log current; Ensure that the archive with sequence# which was shown as current is shipped and applied on standby. This ensures that the redo from CREATE TRIGGER command is applied on standby. Now, shutdown and startup the standby database to make the trigger take effect: SQL> shut immediate; SQL> startup; . + Configure client TNSNAMES.ORA entry. In below example PRIM_DB should be used by clients that need to connect to primary database. STBY_DB should be used by clients that need to access the read only standby database: PRIM_DB = (DESCRIPTION = (ADDRESS_LIST = (FAILOVER = ON) (LOAD_BALANCE = OFF) (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = prim_db) ) ) STBY_DB = (DESCRIPTION = (ADDRESS_LIST = (FAILOVER = ON) (LOAD_BALANCE = OFF) (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = stby_db) ) )   Configuration in 11.2 or later release: We will be using role-based database services introduced in 11.2. To use role-based services, Oracle Clusterware must be installed and active on the primary and standby sites for both single instance (using Oracle Restart) and Oracle RAC databases. + On the primary and standby hosts create the service (prim_db) that the clients will use to connect to the primary database. The service should be created such that it is associated with and runs on the database when it is in the ‘PRIMARY’ database role: On primary: [oracle@vmOraLinux6 ~]$ srvctl add service -d ora11gR2 -s prim_db -l PRIMARY -e SESSION -m BASIC -w 10 -z 10 On standby: [oracle@vmOraLinux6 ~]$ srvctl add service -d sby11gR2 -s prim_db -l PRIMARY -e SESSION -m BASIC -w 10 -z 10 + Start the primary database service (prim_db) on the current primary: [oracle@vmOraLinux6 admin]$ srvctl start service -d ora11gR2 -s prim_db + Next, on the primary and standby hosts create the service (stby_db) that the clients will use to connect to the read only standby database. The service should be created such that it is associated with and runs on the database when it is in the ‘PHYSICAL_STANDBY’ database role: On primary: [oracle@vmOraLinux6 ~]$ srvctl add service -d ora11gR2 -s stby_db -l PHYSICAL_STANDBY -e SESSION -m BASIC -w 10 -z 10 On standby: [oracle@vmOraLinux6 ~]$ srvctl add service -d sby11gR2 -s stby_db -l PHYSICAL_STANDBY -e SESSION -m BASIC -w 10 -z 10 + Now, start the standby service (stby_db) on standby host: [oracle@vmOraLinux6 ~]$ srvctl start service -d sby11gR2 -s stby_db If you get below error when trying to start the service on standby: PRCD-1084 : Failed to start service stby_db PRCR-1079 : Failed to start resource ora.sby11gR2.stby_db.svc CRS-5017: The resource action "ora.sby11gR2.stby_db.svc start" encountered the following error: ORA-44317: database open read-only ORA-06512: at "SYS.DBMS_SERVICE", line 478 ORA-06512: at "SYS.DBMS_SERVICE", line 229 ORA-06512: at line 1 Then as workaroud, follow below steps: a. Start the service on primary instead: [oracle@vmOraLinux6 ~]$ srvctl start service -d ora11gR2 -s stby_db b. Perform a few log switches on primary and allow standby to catch up. c. Now, the service should start on standby: [oracle@vmOraLinux6 ~]$ srvctl start service -d sby11gR2 -s stby_db d. Stop the service on primary: [oracle@vmOraLinux6 ~]$ srvctl stop service -d ora11gR2 -s stby_db + Configure client TNSNAMES.ORA entry. In below example PRIM_DB should be used by clients that need to connect to primary database. STBY_DB should be used by clients that need to access the read only standby database: PRIM_DB =   (DESCRIPTION =      (ADDRESS_LIST =        (FAILOVER = ON)        (LOAD_BALANCE = OFF)        (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))        (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))      )      (CONNECT_DATA =        (SERVICE_NAME = prim_db)      )   ) STBY_DB =   (DESCRIPTION =      (ADDRESS_LIST =        (FAILOVER = ON)        (LOAD_BALANCE = OFF)        (ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))        (ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))      )     (CONNECT_DATA =        (SERVICE_NAME = stby_db)     )   ) Also see the MAA Technical Brief "Client Failover Best Practices for Data Guard 11g Release 2" for further Details and Reference.