How To Configure Client Failover For Data Guard Connections Using Database Services (Doc ID 1429223.1) | 正在上传…重新上传取消To Bottom |
APPLIES TO:Oracle Database Cloud Schema Service - Version N/A and laterOracle 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. GOALIn a Data Guard environment primary database is open in read write mode and the standby database in read only mode for reporting purpose. 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 Application Continuity with Oracle Database12c Release 2 SOLUTION+ 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');
SQL> exec dbms_service.start_service('prim_db');
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; /
SQL> select thread#, sequence# from v$log where status = 'CURRENT'; SQL> alter system archive log current;
SQL> shut immediate; SQL> startup; . 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 primary: --------------------建立了一样的名字, 如果同步后建不上的 ----------------------------------------------- 1. Create the service resource in primary cluster, for example, add service1 and service2 $ $ORACLE_HOME/bin/srvctl add service -d <RAC DB_PRIMARY> -s <service1> -l physical_standby -r "<RAC DB_PRIMARY1>,<RAC DB_PRIMARY2>" ------所以不能早起服务 4. Wait until the last log sequence is applied to the standby DB. 5. Create the service resource in the standby site:------------------19C失败了 已经同步了 $ $ORACLE_HOME/bin/srvctl add service -d <RAC DB_STBY> -s <service1> -l physical_standby -r "<RAC DB_STBY1>,<RAC DB_STBY2>" -----------------------------------------------
[oracle@vmOraLinux6 admin]$ srvctl start service -d ora11gR2 -s prim_db
On primary:
[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
PRIM_DB = |