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

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.

1. 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; /

1. 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/<password>@PROD

1. Check the db_unique_name

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME —————————— prod10g

1. Perform Switchover

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

$sqlplus system/<password>@PROD

1. 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.