Best practice for configuring Data Guard Switchover / Failover HA service between primary and DR sites.

Best practice for configuring Data Guard Switchover / Failover HA service between primary and DR sites.
--------------------------------------------------------------------------------------------------------------------

Assumptions:
a. Manual interventions required to start switchover or failover
b. During the failover or switchover there should be no changes in application connection string, however first time only, it is a MUST to update application connection string before the
DR exercise
c. Dataguard broker should be installed and used to failover procedure, to install DG broker please follow MOS# 1583588.1
d. Its highly recommend to use oracle restart for single instance to be able to create role aware service, if not we will be using different approach as explain below.
e. There is no read only service will be created.

1- Application connection string.Below connection string should be used in all application,

a. Single instance

Database=
(DESCRIPTION_LIST=
(LOAD_BALANCE=OFF)
(FAILOVER=ON)
(DESCRIPTION=
(CONNECT_TIMEOUT=8)(TRANSPORT_CONNECT_TIMEOUT=4)(RETRY_COUNT=0)
(ADDRESS_LIST= (LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=primary-hostname)(PORT=5555))
)
(CONNECT_DATA=
(SERVICE_NAME=dbname_primary)
)
)
(DESCRIPTION=
(CONNECT_TIMEOUT=8)(TRANSPORT_CONNECT_TIMEOUT=4)(RETRY_COUNT=0)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=DR-hostname)(PORT=5555))
)
(CONNECT_DATA=
(SERVICE_NAME=dbname_parimary)
)
)
)

b. Cluster instance

Database=
(DESCRIPTION_LIST=
(LOAD_BALANCE=OFF)
(FAILOVER=ON)
(DESCRIPTION=
(FAILOVER=OFF)
(CONNECT_TIMEOUT=8)(TRANSPORT_CONNECT_TIMEOUT=4)(RETRY_COUNT=0)
(ADDRESS_LIST= (LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=primary-scan)(PORT=5555))
)
(CONNECT_DATA=
(SERVICE_NAME=dbname_ha)
)
)
(DESCRIPTION=
(FAILOVER=OFF)
(CONNECT_TIMEOUT=8)(TRANSPORT_CONNECT_TIMEOUT=4)(RETRY_COUNT=0)
(ADDRESS_LIST= (LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=DR-scan)(PORT=5555))
)
(CONNECT_DATA=
(SERVICE_NAME=dbname_ha)
)
)
)

2- Service creation

a. Single instance (without oracle restart), if oracle restart is not installed, please use below creation script to create service

exec dbms_service.create_service('dbname_ha','dbname_ha');
exec dbms_service.start_service('dbname_ha ');
create database trigger to start the service if the primary role changed
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;
END;
/
b. For cluster database and single instance with oracle restart

srvctl add service -database dbaname -service dbname_ha -role primary

cluster will take care of starting the service if role changed.

For a RAC database, you can use the following command to create a service for a CDB

-------------------------------------------------------------------------------------

srvctl add service -database CDBDBA -service CDBDBA_HA -role primary -preferred CDBDBA1,CDBDBA2

For a RAC database, you can use the following command to create a service for a PDB.

-------------------------------------------------------------------------------------

srvctl add service -d CDBDBA -s ERPDBA_HA -pdb ERPDBA -role primary -preferred CDBDBA1,CDBDBA2

srvctl start service -d CDBDBA -s ERPDBA_HA

srvctl status service -d CDBDBA -s ERPDBA_HA

srvctl config service -d CDBDBA

srvctl stop service -d CDBDBA -s ERPDBA_HA

srvctl remove service -d CDBDBA -s ERPDBA_HA