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