How to setup transparent Switchover / Failover to application and clients

If there is a failover or switchover and if user(s) are trying to connect to database with same connect string, end up with below error message

C:\>sqlplus system/password@trg1
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 17 12:29:25 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Clients transparently can connect to whichever node/instance is currently running the primary database in Data Guard environment.

1. Set the below entry to tnsname.ora on both Data Guard nodes

TRG is a Database (show parameter db_name) – Replace TRG with your database name.
Change the IP address of both nodes in below tnsnames.ora entry

ALL_LISTENERS_TRG =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.XX)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.YY)(PORT = 1521))
)

2. Set below entry to client’s tnsname.ora file.

TRG_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.XX)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.YY)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TRG_PRIMARY)
) )

3. Setup for Application server.

If the application is a java program that uses the thin jdbc driver the following connect string can be used instead of the TNS alias.
Service Name =TRG_primary (change have any server name)
1st host ip address 10.0.0.XX
2nd host ip address 10.0.0.YY

TRG_primary.db.url="jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=TRUE)(ADDRESS_LIST=(ADDRESS=(PROTO=TCP)(HOST=10.0.0.XX)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.YY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TRG_primary)))"

4. Login as sysdba and create abelow trigger on Primary Database and it will be automatically shipped to the Standby Database with the redo log stream.

declare
db_name v$database.name%type;
service_name varchar2(64);
net_name varchar2(64);
begin
select name into db_name from v$database;
service_name := db_name '_PRIMARY';
net_name := db_name '_PRIMARY';
DBMS_SERVICE.CREATE_SERVICE(service_name,net_name);
end;
/

CREATE OR REPLACE TRIGGER enable_primary_service
after startup ON database
declare
db_name v$database.name%type;
db_role v$database.database_role%type;
service_name varchar2(64);
begin
select name,database_role into db_name,db_role from v$database;
if db_role = 'PRIMARY'
then
service_name := db_name '_PRIMARY';
DBMS_SERVICE.START_SERVICE(service_name,NULL);
end if;
END;
/

5. Shutdown and restart Primary Database to activate the login trigger.

6. After restart of database, Check for service at primary database

SQL> Show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string trg1, TRG_PRIMARY

7. Connect to database from client machine with connect string “trg_primary”
sqlplus system/xxxx@trg_primary

8. Do the switchover either through Data Guard (dgmgrl) or manually

#dgmgrl sys/password
DGMGRL> switchover to 'trg1';

9. after successful switchover, Primary Database is now TRG1 but Client should be successfully connect with same connect string without making any change in client tnsnames.ora file.

sqlplus system/password@trg_primary