The purpose of this blog is to guide you through configuring Oracle Data Guard Fast-Start Failover (FSFO) using Data guard broker with physical standby database.

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.

The lab Environment

My environment consists of the following entities:

  • I have 3 VM with version of REHL 6 installed.
  • Both Primary and standby database version is 12.1.
  • Primary database is already created and ready for use and on standby server, the oracle software binaries has been installed.
  • Primary database/Unique name is DGTST/DGTST_PRIMY on host DGTST01
  • Standby database/Unique name is DGTST/DGTST_STBY on host DGTST02
  • The Data Guard Observer server name is DGTST03.

Note: The configuration of standby database on Oracle 12c with data guard is similar to Oracle 11g except few changes in log_archive_dest_2 and dgmgrl utility user access.

Primary Server set up

Logging

Check database is in archive log mode. If not, please put the database in archive log mode.

CDB$ROOT@DGTST> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora_archive/DGTST
Oldest online log sequence 2
Next log sequence to archive 5
Current log sequence 5

Check force database logging option is enabled.

CDB$ROOT@DGTST> select force_logging FROM v$database;
FORCE_LOGGING
-----------------------------------------------------------------
NO
CDB$ROOT@DGTST> ALTER DATABASE FORCE LOGGING;
Database altered.

Create standby redologs on primary database

Create standby redologs on primary database. Make sure standby log file size should be same as existing online logfile size.

CDB$ROOT@DGTST> alter database add standby logfile ('/redolog/DGTST/standby_redo1.log') size 100M;
Database altered.
CDB$ROOT@DGTST> alter database add standby logfile ('/redolog/DGTST/standby_redo2.log') size 100M;
Database altered.
CDB$ROOT@DGTST> alter database add standby logfile ('/redolog/DGTST/standby_redo3.log') size 100M;
Database altered.
CDB$ROOT@DGTST> alter database add standby logfile ('/redolog/DGTST/standby_redo4.log') size 100M;
Database altered.

Enable Flashback database

Put the primary database in flashback mode. Reset the recovery related parameters and restart the primary database on DGTST01.

CDB$ROOT@DGTST> alter system set db_recovery_file_dest='/ora_fra/'  scope=spfile;
System altered.
CDB$ROOT@DGTST> alter system set db_recovery_file_dest_size=50g scope=both;
System altered.
CDB$ROOT@DGTST> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
CDB$ROOT@DGTST> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size             553650296 bytes
Database Buffers         1577058304 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
CDB$ROOT@DGTST> ALTER DATABASE FLASHBACK ON;
Database altered.
CDB$ROOT@DGTST> select flashback_on from v$database;
FLASHBACK_ON
--------------------------------------------
YES

Add parameters on primary database

Set below parameters to DGTST database at primary side.

CDB$ROOT@DGTST> alter system set db_unique_name='DGTST_PRIMY' scope=spfile;
System altered.
CDB$ROOT@DGTST> alter system set fal_client='DGTST_PRIMY' scope=both;
System altered.
CDB$ROOT@DGTST> alter system set fal_server='DGTST_STBY' scope=both;
System altered.
CDB$ROOT@DGTST> alter system set log_archive_config='dg_config=(DGTST_PRIMY, DGTST_STBY)' scope=both;
System altered.
CDB$ROOT@DGTST> alter system set standby_file_management='AUTO' scope=both;
System altered.
CDB$ROOT@DGTST> alter system set dg_broker_start=TRUE scope=both;
System altered.

Oracle Net configuration

Add TNS  entries as below on both primary and standby servers under location $ORACLE_HOME/network/admin/tnsnames.ora

DGTST_PRIMY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DGTST01)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = DGTST)
 )
 )

DGTST_STBY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DGTST02)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = DGTST)
 )
 )

Now add the static Listener entry to Primary as below.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=DGTST_PRIMY_DGMGRL)
(SID_NAME=DGTST)
(ORACLE_HOME=/oracle/product/12.1.0.2)
)
)

For Standby database, listener entry should look like as below..Restart listener on both servers.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_DESC=
(GLOBAL_DBNAME=DGTST_STBY_DGMGRL)
(SID_NAME=DGTST)
(ORACLE_HOME=/oracle/product/12.1.0.2)
)
)

Standby Server Setup

Add standby instance names to standby nodes /etc/oratab.
DGTST:/oracle/product/12.1.0.2:N

Create a parameter file for standby database and create the necessary directories.

mkdir /oracle/admin/DGTST/adump
mkdir /d06/oradata/DGTST
mkdir /d06/oradata/DGTST/pdbseed
mkdir /ora_archive/DGTST
mkdir /d02/ora_fra/
mkdir /redolog/DGTST

Create a password file, with the SYS password matching that of the primary database.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=

Create Standby Using RMAN DUPLICATE

# First mount the standby database. 
idle> create spfile from pfile;
File created.
idle> startup nomount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 3712904 bytes
Variable Size 570427512 bytes
Database Buffers 1560281088 bytes
Redo Buffers 13062144 bytes
RMAN> connect target sys/password@DGTST_PRIMY
connected to target database: DGTST (DBID=789452781)
RMAN> connect auxiliary sys/password@DGTST_STBY
connected to auxiliary database: DGTST (not mounted)
RMAN> run {
allocate channel dx1 type disk;
allocate channel dx2 type disk;
allocate auxiliary channel ax1 type disk;
allocate auxiliary channel ax2 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
 }

Now parameter settings at standby database side. Restart database to pick up parameter changes.

CDB$ROOT@DGTST> alter system set db_unique_name='DGTST_STBY' scope=spfile;
System altered.
CDB$ROOT@DGTST> alter system set fal_client='DGTST_STBY' scope=spfile;
System altered.
CDB$ROOT@DGTST> alter system set fal_server='DGTST_PRIMY' scope=spfile;
System altered.
CDB$ROOT@DGTST> alter system set log_archive_config='dg_config=(DGTST_PRIMY, DGTST_STBY)' scope=spfile;
System altered.
CDB$ROOT@DGTST> alter system set standby_file_management='AUTO' scope=spfile;
System altered.
CDB$ROOT@DGTST> alter system set dg_broker_start=TRUE scope=spfile;
System altered.
# Check Database Flashback
CDB$ROOT@DGTST> select flashback_on from v$database;
FLASHBACK_ON
---------------------------------------------------
YES
CDB$ROOT@DGTST> select force_logging from v$database;
FORCE_LOGGING
--------------------------------------------------
YES

Enable Broker

Add DB broker configuration from primary database. I already set the Db_broker_start= true.

set ORACLE_SID=DGTST
dgmgrl
DGMGRL> connect sys/password
Connected as SYSDG.
# Add Primary database
DGMGRL> create configuration 'DGTST_HA' as  Primary database is 'DGTST_PRIMY' connect identifier is DGTST_PRIMY;
Configuration "DGTST_HA" created with primary database "DGTST_PRIMY"
# Add standby database
DGMGRL> add database 'DGTST_STBY' as connect identifier is DGTST_STBY  maintained as physical;
Database "DGTST_STBY" added
# Enable database configuration
DGMGRL> enable configuration;
Enabled.
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 40 seconds ago)
DGMGRL> show database 'DGTST_PRIMY'
Database - DGTST_PRIMY
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
DGTST
Database Status:
SUCCESS
DGMGRL> show database 'DGTST_STBY'
Database - DGTST_STBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 12.00 KByte/s
Real Time Query: ON
Instance(s):
DGTST
Database Status:
SUCCESS

In 12c, as soon as you enable the dg broker configuration the manage recovery will start.

CDB$ROOT@DGTST> show parameter log_archive_dest_2
NAME TYPE VALUE
-------------------------------------------- ------------------------------
log_archive_dest_2  string   service="dgtst_stby", ASYNC NO
                             AFFIRM delay=0 optional compre
                             ssion=disable max_failure=0 ma
                             x_connections=1 reopen=300 db_
                             unique_name="DGTST_STBY" net_t
                            imeout=30, valid_for=(online_logfile,all_roles)

Switchover operation

Note:  I am using GI for standalone database. So Clusterware restart the database. You need to register database with GI on both primary and standby side. Otherwise, swicthover will stuck with clusterware start up.

DGMGRL> switchover to 'DGTST_STBY'
Performing switchover NOW, please wait...
Operation requires a connection to instance "DGTST" on database "DGTST_STBY"
Connecting to instance "DGTST"...
Connected as SYSDBA.
New primary database "DGTST_STBY" is opening...
Oracle Clusterware is restarting database "DGTST_PRIMY" ...
Switchover succeeded, new primary is "DGTST_STBY"

Register database with GI

# Register the PRIMARY database:
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
# Register the 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

Failover operation

Now it’s time to test the failover operation.

DGMGRL> failover to 'DGTST_STBY'
Performing failover NOW, please wait...
Failover succeeded, new primary is "DGTST_STBY"
DGMGRL> show configuration
Configuration - DGTST_HA
Protection Mode: MaxPerformance
 Members:
 DGTST_STBY - Primary database
 DGTST_PRIMY - (*) Physical standby database (disabled)
 ORA-16661: the standby database needs to be reinstated
 Fast-Start Failover: DISABLED

Reinstate the aborted primary as a standby

Next task is to reinstate the primary. Fix the problem with primary database server and reinstate the database as below. if flash back is not enabled then you have yo manually re-create the standby database.

DGMGRL> show Configuration 
Configuration - DGTST_HA
Protection Mode: MaxPerformance
Members:
DGTST_PRIMY - Primary database
DGTST_STBY - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 52 seconds ago)
# Reinstate standby database
DGMGRL> reinstate database 'DGTST_STBY'
Reinstating database "DGTST_STBY", please wait...
Oracle Clusterware is restarting database "DGTST_STBY" ...
Continuing to reinstate database "DGTST_STBY" ...
Reinstatement of database "DGTST_STBY" succeeded.

Enable FSFO (Fast Start Fail Over)

FSFO can be enabled as below from data guard broker. Note that enabling FSFO does not make the configuration ready for automatic failover – that requires an observer, which we’ll get to next.

DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> DGMGRL> show configuration
Configuration - DGTST_HA
Protection Mode: MaxPerformance
Members:
DGTST_PRIMY - Primary database
Warning: ORA-16819: fast-start failover observer not started
DGTST_STBY  - (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-Start Failover: ENABLED
Configuration Status:
WARNING   (status updated 1 second ago)

With FSFO enabled, Broker expects to find an observer, which we haven’t started yet, so if you verify the configuration at this point with ‘show configuration’, Broker will report a warning (if it doesn’t, give it a minute to discover that the observer isn’t there).

Configure Observer

To maximize the benefits of FSFO, the observer should run on a different host than the primary and standby databases. Ideally the primary, standby, and observer will be in geographically separate areas. The observer is very lightweight, requiring few system resources. Since the observer is a specialized instance of a dgmgrl session, the observer host should be installed with either the Oracle Client Administrator software or the full Oracle Database software stack.Use below script to start-up the observer. If you startup the observer without this script through sys account directly the terminal session will appear to hang and will stop the observer if you close the session.

Cd /d01/observer12c/DGTST
Vi start_DGTST.sh
nohup dgmgrl -logfile /d01/observer12c/DGTST/DGTST_observer.log sys/password@DGTST_PRIMY "start observer file='/d01/observer12c/DGTST/DGTST_fsfo.dat'" &
# RUn the script
 ./ start_DGTST.sh
 nohup: appending output to `nohup.out'

Verify the configuration and can see all errors are now gone.

DGMGRL> show configuration verbose
Configuration - DGTST_HA
Protection Mode: MaxPerformance
Members:
DGTST_PRIMY - Primary database
DGTST_STBY - (*) Physical standby database
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: DGTST_STBY
Observer: DGTST03
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configuration Status:
SUCCESS

See observer logs as well.

tail -f DGTST_observer.log
Observer started
[W000 10/27 13:51:14.27] Observer started.

 

Enjoy 🙂

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