In the event of a planned/unplanned outage, how efficiently can user sessions be directed to a secondary site/ Database with minimal disruption is called seamless application failover. This can be achieved when database failover is facilitated by oracle data guard.
Oracle Data Guard is one of the software solutions provided by Oracle Corporation to maximize high availability of Oracle databases. Oracle Data Guard maintains one or many secondary databases as alternatives to the primary production database.

How to set up Data guard via DG broker with Fast start failover, please go through following article.
Oracle Data Guard 12cR1 with Data Guard Broker

And for Oracle GI for single instance with Oracle restart, read this blog.
Oracle 12c R2 (12.2.0.1) – GI installation for Single Instance Without & With ASM

Role Specific Database services

Starting with 11gR2, with the advent of the new Grid Infrastructure software, we have the ability to set up services in our databases that can now automatically be tied to the specific role that a database is playing in our Data Guard configuration. Data Guard Broker must be configured and running to use this new feature. This can be used for both RAC and single instance databases, just so long as Grid Infrastructure had been installed. The services would have to be set up using SRVCTL rather than DBMS_SERVICE.Data Guard Broker interacts with Oracle Clusterware or Oracle Restart to ensure appropriate services are active after a role transition: no need to write triggers on system startup events. Here is demo how to register database with GI with existing environment.

Server DB_NAME DB_UNIQUE_NAME Install Type GI ver DB ver
Primary

DGSTST01

DGTST DGTST_PRIMY  Single Instance 12.2 12.1
Satndby

DGSTST01

DGTST DGTST_STBY  Single Instance 12.2 12.1

Register the PRIMARY  database and Service

After Data guard va dg broker, register primary database with GI.
NOTE:- The database need to be bounce at this stage to bring the state of database to ONLINE, either using shutdown immediate or below “srvctl start database” command. Now register the HA (High availability) service and then start-up service on primary side. Here  I register the service with PDB.

DGTST01:DGTST:/oracle/product/12.1.0.2/bin:>./srvctl add database -d DGTST_PRIMY -oraclehome /oracle/product/12.1.0.2 -r PRIMARY -dbname DGTST -i DGTST -spfile /oracle/product/12.1.0.2/dbs
DGTST01:DGTST:/oracle/product/12.1.0.2/bin:>./srvctl add service -d DGTST_PRIMY -pdb DGTSTPDB -s DGTSTPDB_HA -l PRIMARY -q TRUE -e SELECT -m BASIC -z 150 -w 1
DGTST01:DGTST:/oracle/product/12.1.0.2/bin:>./srvctl start service -d DGTST_PRIMY -pdb DGTSTPDB  -s DGTSTPDB_HA
# Check service with database at PDB level as I register and started the service at PDB.
DGTSTPDB@DGTST> select name from v$active_services;
NAME
-------------------
DGTSTPDB_HA

Can see the configuration from GI home.

/oracle/product/grid12.2/bin:>./crsctl status resource -t
 ------------------------------------------------------------------------------------------------------------
 Name               Target  State              Server     State details  Local Resources
 -------------------------------------------------------------------------------------------------------------
 ora.dgtst_primy.db  1     ONLINE  ONLINE       DGTST01    Open,HOME=/oracle /product/12.1.0.2,STABLE                        
 ora.dgtst_primy.dgtstpdb_ha.svc 1      ONLINE  ONLINE       DGSTST01       STABLE

Register the Standby  database

Similarly register standby database with GI and then service.
Note:- No need to start service on STANDBY Database

DGTST01:DGTST:/oracle/product/12.1.0.2/bin:>./srvctl add database -d DGTST_STBY -oraclehome /oracle/product/12.1.0.2 -r PHYSICAL_STANDBY -dbname DGTST -i DGTST -spfile /oracle/product/12.1.0.2/dbs
DGTST:/oracle/product/12.1.0.2/bin:>./srvctl add service -d DGTST_STBY -pdb DGTSTPDB -s DGTSTPDB_HA -l PRIMARY -q TRUE -e SELECT -m BASIC -z 150 -w 10

Can see the configuration from GI home and Service is not started at Standby side.

/oracle/product/grid12.2/bin:>./crsctl status resource -t
---------------------------------------------------------------------------------
Name           Target  State        Server            State details Local Resources
----------------------------------------------------------------------------------
ora.dgtst_stby.db 1        ONLINE  ONLINE       DGTST02      Open,Readonly,HOME=/oracle/product/12.1.0.2,STABLE
ora.dgtst_stby.dgtstpdb_ha.svc 1        OFFLINE OFFLINE       STABLE

Reference for Options:
srvctl add database -db -oraclehome [-domain ] [-spfile ] [-pwfile ] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY | FAR_SYNC}] [-startoption ] [-stopoption ] [-dbname ] [-instance ] [-policy {AUTOMATIC | MANUAL | NORESTART}] [-diskgroup “”]

srvctl add service -db -service [-role [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-policy {AUTOMATIC | MANUAL}][-notification {TRUE|FALSE}] [-clbgoal {SHORT|LONG}] [-rlbgoal {NONE|SERVICE_TIME|THROUGHPUT}][-failovertype {NONE|SESSION|SELECT|TRANSACTION}] [-failovermethod {NONE|BASIC}][-failoverretry ] [-failoverdelay ] [-edition ] [-pdb ] [-global ] [-maxlag ] [-sql_translation_profile ] [-commit_outcome {TRUE|FALSE}] [-retention ] [replay_init_time ] [-session_state {STATIC|DYNAMIC}] [-force]

Automatic FAN Event Publication by Broker

Following a Data Guard Failover (manual or Fast-Start Failover), Data Guard Broker now automatically publishes a FAN (Fast Application Notification) event to clean up connections to the failed primary database. Upon receipt of that event, FAN client subscribers can automatically reconnect to the service started on the new primary database. Oracle Clusterware must be installed and active on the primary and standby sites for both single instance (using Oracle Restart). ONS gets installed and configured automatically with Oracle Clusterware as part of a RAC/Single instance install.So Integrated Broker/FAN automation removes the need of an external program to publish these FAN events and Manual triggers to invoke external program.

 Seamless Application failure

Application are redirected to secondary site during database failover (switchover ) by data guard and application notification is done via Fast application notification.Service should be created on both primary and standby site as explained above.

  • Oracle Grid Infrastructure HA and other Oracle 12c APIs/drivers/adapters are supported inbuilt mechanism for FAN
  • TAF – Transparent Application Failover (OCI)
  • FCF – Fast Connection Failover (JDBC)
  • OCI applications with TAF enabled should use FAN high availability events for fast connection failover. Example PeopleSoft Version 8:50.09 and higher support FAN.

On JDBC side, enable FCF ( Fast Start Failover) to receives FAN events. JDBC thin clients are capable to receive FAN events by default. No need to configure anything.

Client connectivity

OCI:
Oracle Net alias Defined on both Primary and standby database servers as below.

DGTST_HA=
 (DESCRIPTION_LIST=
 (LOAD_BALANCE=off)
 (FAILOVER=on)
 (DESCRIPTION =
 (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
 (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=DGTST01)(PORT=1521)))
 (CONNECT_DATA=(SERVICE_NAME = DGTSTPDB_HA))
 )
 (DESCRIPTION=
 (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
 (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(HOST=DGTST02)(PORT=1521)))
 (CONNECT_DATA= (SERVICE_NAME = DGTSTPDB_HA))
 )
 )
  • A new Connection spends 10 sec + 30 sec (3X10-retries) = 40sec to failover to secondary site.
 JDBC:
jdbc:oracle:thin:@(DESCRIPTION_LIST=(FAILOVER=on)(DESCRIPTION=(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=dgtst01)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=dgtst02)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME = DGTSTPDB_ha))))

Testing:

Perform the failover/switchover to test the service jumping over to standby site or not. For easy testing, make sqlplus connection to primary database with HA TNS entry.
Failover

 DGMGRL> connect sys/password
 Connected as SYSDG.
 DGMGRL> show configuration
 Configuration - DGTST_HA
 Protection Mode: MaxPerformance
 Members:
 DGTST_PRIMY - Primary database
 DGTST_STBY  - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS   (status updated 44 seconds ago)
 DGMGRL> failover to 'DGTST_STBY'
 New primary database "DGTST_STBY" is opening...
 Failover succeeded, new primary is "DGTST_STBY"
# Check new Primary server and HA service.
 DGTSTPDB@DGTST> select name from v$active_services;
 NAME
 -------------------------
 DGTSTPDB_HA
 DGTST:/oracle/product/grid12.2/bin:>./crsctl status resource -t
 --------------------------------------------------------------------------
 Name           Target  State        Server                   State details Local Resource
 --------------------------------------------------------------------------------
 ora.dgtst_stby.db 1        ONLINE  ONLINE       DGTST02              Open,HOME=/oracle/product/12.1.0.2,STABLE
 ora.dgtst_stby.dgtstpdb_ha.svc 1        ONLINE  ONLINE       DGTST02              STABLE
 # Service is on-line at Standby side and also connection came over to new primary.
 DGTSTPDB@DGTST> select username, machine  from v$session where username like 'SYSTEM';
 no rows selected
 DGTSTPDB@DGTST> /
 USERNAME  MACHINE
 ------------------------------
 SYSTEM DGTST02

Switchover:

You get client failover for switchover free if you have followed the steps as explained earlier.
  • Clients disconnected as primary is converted to a standby
  • Clients go through TAF retry logic (OCI) or application retry logic (JDBC)
  • Once both databases come up in new roles, services start and clients reconnect

Mandy!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s