Dataguard - 12c Physical standby database failover steps example

Oracle Database server Details:

 

Server Details		DB Details
Host Name	IP Address	DB Version	Instance Name	Mode
		Oracle 12c		Stand By
		Oracle 12c		Primary

  

Introduction

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.

Data Guard Architecture

Oracle Data Guard supports both physical standby and logical standby sites.

Physical Standby: When the primary database transactions generate redo entries, a redo apply process keeps up the secondary databases with the exact block copies of the primary database.

Logical Standby: SQL apply processes read the redo and convert it to SQL transactions. These are then applied to the secondary database.

Data Guard Modes

Oracle Data Guard can operate in 3 different modes:

Maximum Protection: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will shutdown to ensure maximum protection mode.

Maximum Performance: Transactions are not allowed to commit as soon as the redo are written to the online redo logs. The redo stream is asynchronously propagated to the secondary databases to ensure maximum performance mode.

Maximum Availability: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.

Glossary

Role transition: Changing the role of each database component from primary database to the secondary database or from secondary database to the primary database.

Switchover: Planned role transition for testing. Manual intervention.

Fail Over: Unplanned failure. Manual or Automatic intervention. Automatic role transition is the recommended.

Primary database: Where the users are connected to access to the database.

Standby database: Exists in the the disaster recovery (DR) site. Where the users are connected in the case of planned role transition (Switchover) or in the case of unplanned failure (Fail Over).

Failover Operation Steps

A failover operation is a true disaster recovery operation. A failover operation should only be considered when all of the alternative options for primary database recovery are not feasible. A failover is a unplanned event when something has happened to hardware, networking, etc.

Below are the steps for opening standby database when the primary database is lost:

Considering Primary and standby database is in sync with each other.

In order to initiate the failover operation, the target physical Oracle instance should be placed in MAXIMUM PERFORMANCE data protection mode using the following statement:

ALTER DATABASE SET Oracle instance TO MAXIMIZE PERFORMANCE.

As in SMRPRODB database already in maximum performance mode by default so ALTER command not required.

 

1 .  Check the status of archived logs in existing standby database server. Verify a Standby Redo Log is in use for Primary current Online Redo Log.

SQL > SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#;

 

 

2. Using query Check database status as database name, INSTANCE NAME, OPEN MODE, DATABASE ROLE, DB UNIQUE NAME

select name, instance_name, open_mode, database_role, flashback_on , current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance;

 

3. Verify a Standby Redo Log is in use for Primary current Online Redo Log.  Stoping the MRP Process on standby database server.

Alter database recover managed standby database cancel;

 

4. To get the Standby Redo Log Information is still required.

5 converting standby role to the primary role

6.  Open database for read write mode

 

 

 

#########################################################################

NOTE : Since the standby database is now the primary database it should be backed up immediately.

 

 Bring  back old primary database in standby mode.

 

The Old Primary

The data center has now been fixed and the old primary is back online and you need to do this quick to make sure that you are protected again. If you are not using failback database then you have to delete the original database and re-create it using the RMAN Backup procedure

.If you have enabled flashback database then we can easily bring back the primary

 

## Since redo is applied by SCN we need he failover SCN from the new primary

select to_char(standby_became_primary_scn) failover_scn from v$database;

FAILOVER_SCN
-----------------------------------------------
7658841

## Now flashback the old primary to this SCN and start in mount mode

startup mount;

flashback database to scn 7658841;

alter database convert to physical standby;

shutdown immediate;

startup mount;

## hopefully the old primary will start to resolve any gap issues at the next log switch, which means we can start the MRP
## process to get this standby going to catchup as fast as possible

alter database recover managed standby database using current logfile disconnect;

## eventually the missing redos will be sent to the standby and applied, bring us back to synchronization again.

 

 

 

 

#####################################################################

Recreate Old Primary Database Using existing Rman backup.

RMAN Backup script

run

{

allocate channel c1 device type disk format '/u04/MasterDB/rmanbkp_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';

allocate channel c2 device type disk format '/u04/MasterDB/rmanbkp_SMRPRODB/Backup_%d_DB_%u_%s_%p_%T';

backup full database format '/u04/MasterDB/rmanbkp_SMRPRODB/%d_%U.bckp' plus archivelog;

copy current controlfile to '/u04/MasterDB/rmanbkp_SMRPRODB/control01.bak';

backup current controlfile for standby format '/u04/MasterDB/rmanbkp_SMRPRODB/standbycontrol_%d_%T_%U.ctl';

release channel c1;

release channel c2;

}