Step By Step Guide On How To Configure And Test Client-Failover For Dataguard

D 740029.1

In this Document
  Goal
Solution

---

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition – Version: 10.1.0.2 to 11.2.0.2 – Release: 10.1 to 11.2
Information in this document applies to any platform.

Goal

Step By Step Guide On How To Configure And Test Client-Failover For Dataguard Switchover And Failover.

Solution

Configure the Client Failover
1. Create and Start a new service on primary which will be used to connect to the database.

SQL> exec dbms_service.create_service('prod_db','prod_db');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.start_service('prod_db');
PL/SQL procedure successfully completed.


2. Create trigger to start the service on primary during startup.

CREATE OR REPLACE TRIGGER manage_DGservice after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('prod_db');
END IF;
END;
/


3. Configure client TNSNAMES.ORA entry to connect to the database using prod_db service.

PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod.world.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby.world.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod_db )
)
)


Testing the Client Failover:

1. Connect to database using “SYSTEM” user

$sqlplus system/@PROD


2. Check the db_unique_name

SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
prod10g

3. Perform. Switchover
4. Connect again to database using “SYSTEM” user:

$sqlplus system/@PROD


5. Check the db_unique_name

SQL> select db_unique_name from v$database;
DB_UNIQUE_NAME
------------------------------
stdby10g




Note:
1. Make sure that service name (prod_db) used to connect the database is not included in the service_names parameter neither in primary nor in standby database.
2. Please change the hostname of the primary and standby in the TNSNAMES.ORA shown in this note as per your setup.

---

Note:
In 11.2 we greatly simplified the client failover setup and configuration over previous releases.
For more information please see fallowing White Paper:
Client Failover Best Practices for Highly Available Oracle Databases:
Oracle Database 11g Release 2
Oracle Maximum Availability Architecture White Paper
available on

http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf

Switchover from Primary to Physical Standby Database

#########Switchover to Physical Standby Database#########

–In Primary–

SELECT database_role FROM v$database;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
or
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN WAIT;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
or
startup nomount
alter database mount standby database;
SELECT database_role FROM v$database;

–In Standby–

select SWITCHOVER_STATUS from v$database; —switchover status should show “SWITCHOVER PENDING”
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;

–In New Standby–

alter database recover managed standby database disconnect from session;

–In New Primary–

ALTER SYSTEM SWITCH LOGFILE;

Note: If feel any issue just give me a buzz....

Failover from Primary to Physical Standby Database

#########Failover to Physical Standby Database#########

–In Standby–
SELECT thread#, low_sequence#, high_sequence#
FROM v$archive_gap;

If possible, copy all of the identified missing archived redo log files to the target standby
database from the primary database and register them. Execute the following command to
register the redo log files:

ALTER DATABASE REGISTER PHYSICAL LOGFILE ‘filespec1′;

–Perform this step for each thread. Repeat step until all gaps are resolved.

SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
OVER (PARTITION BY thread#) AS LAST
FROM V$ARCHIVED_LOG;

/*Copy to the target standby database any available primary database archived redo log files
that contain sequence numbers higher than the highest sequence number that is available on
the target standby database. Then register those redo log files by issuing the following SQL
statement:*/

ALTER DATABASE REGISTER PHYSICAL LOGFILE ‘filespec1′;

–Perform this step for each thread. Repeat step until all gaps are resolved.

–Execute the following SQL statement to initiate the failover:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

–Transition the physical standby database to the primary databaserole by issuing the following SQL statement

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

/*After issuing this SQL statement, you can no longer use this database as a standby database.
Any subsequent redo that is received from the original primary database cannot be applied.
During the failover process, the standby redo log files are automatically archived and
recovered on all other standby databases that are derived from the original primary database
if the standby destinations are correctly defined on the new primary database*/

–in New Primary Database
ALTER DATABASE OPEN;

....Cheer....