News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 19c New Feature Automatic Flashback of Standby Database

  • Posted by Gavin Soorma
  • On July 2, 2019
  • 0 Comments
  • 18c, 19c, data guard, flashback, new features

One of the new features in Oracle 19c is that either when a flashback or point-in-time recovery is performed on the primary database in an Oracle Data Guard configuration, the same operation is also performed on the standby database as well.

Following a flashback or PITR operation, the primary database is then opened with the RESETLOGS option.

The RESETLOGS leads to a new incarnation of the primary or the PDB in the primary.

What’s new then in Oracle 19c?

The MRP process on the standby detects the new incarnation and moves the standby database to the new ‘branch’ of redo and then flashes back the standby or the pluggable database on the standby to the same point in time as that of the primary or the PDB on the primary.

In earlier releases, we had to obtain the RESETLOGS SCN# on the primary and then manually issue a FLASHBACK DATABASE command on the standby database to enable managed recovery and continue with the redo apply process.

Further, another new Oracle 19c feature is that when we create a Restore Point on the primary database, it will automatically create a restore point as well on the standby database.

These restore points are called Replicated Restore Points and have the restore point name suffixed with a “_PRIMARY”.

Let us have a look at this feature in action.

On the primary database we will create a guaranteed restore point.
 

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> create table hr.myobjects as select * from all_objects;

Table created.

SQL> select count(*) from hr.myobjects;

  COUNT(*)
----------
     71296


SQL> create restore point orcl_grp guarantee flashback database;

Restore point created.

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
ORCL_GRP

 
Note that on the standby database, the restore point has been automatically created and the name has the suffix _PRIMARY
 

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>  select count(*) from hr.myobjects;

  COUNT(*)
----------
     71296

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
ORCL_GRP_PRIMARY

 
On the primary database we can see that the REPLICATED column has the value NO for the restore point while on the standby database the value is YES
 

Primary 

SQL> select NAME,REPLICATED from v$restore_point;

NAME			       REP
------------------------------ ---
ORCL_GRP	       		NO


Standby

SQL> select NAME,REPLICATED from v$restore_point;

NAME			       REP
------------------------------ ---
ORCL_GRP_PRIMARY	       YES

 
We now simulate a case where a human error has been made and we now need to perform a flashback operation on the primary to resolve the human error.

Flashback is performed to the restore point created earlier and we then open the database with the RESETLOGS option.
 

SQL> truncate table hr.myobjects;

Table truncated.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1241513488 bytes
Fixed Size		    8896016 bytes
Variable Size		  335544320 bytes
Database Buffers	  889192448 bytes
Redo Buffers		    7880704 bytes
Database mounted.

SQL> flashback database to restore point orcl_grp;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.


 
The standby database is placed in MOUNT mode and we will see that the MRP process on the standby database will start and perform the automatic flashback operation on the standby database as well.
 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1241513488 bytes
Fixed Size		    8896016 bytes
Variable Size		  318767104 bytes
Database Buffers	  905969664 bytes
Redo Buffers		    7880704 bytes
Database mounted.

 

...
...
 rfs (PID:27484): Primary database is in MAXIMUM PERFORMANCE mode
2019-07-01T23:00:30.671381+08:00
 rfs (PID:27484): Selected LNO:5 for T-1.S-3 dbid 1540291890 branch 1012517366
2019-07-01T23:00:34.853803+08:00
ARC0 (PID:27457): Archived Log entry 9 added for T-1.S-3 ID 0x5bcedc53 LAD:1
2019-07-01T23:00:34.930249+08:00
 rfs (PID:27500): Primary database is in MAXIMUM PERFORMANCE mode
2019-07-01T23:00:35.073538+08:00
 rfs (PID:27500): Selected LNO:5 for T-1.S-4 dbid 1540291890 branch 1012517366
2019-07-01T23:00:35.909833+08:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2019-07-01T23:00:35.910522+08:00
Attempt to start background Managed Standby Recovery process (orcl)
Starting background process MRP0
2019-07-01T23:00:35.932806+08:00
MRP0 started with pid=49, OS id=27505 
2019-07-01T23:00:35.935882+08:00
Background Managed Standby Recovery process started (orcl)
2019-07-01T23:00:40.940224+08:00
Serial Media Recovery started

...
...

MRP0 (PID:27505): Recovery coordinator performing automatic flashback of database to SCN:0x00000000001fcce5 (2084069)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
2019-07-01T23:01:01.852650+08:00
Setting recovery target incarnation to 2
2019-07-01T23:01:01.852994+08:00
Serial Media Recovery started
stopping change tracking
2019-07-01T23:01:01.976765+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORCL_SB/archivelog/2019_07_01/o1_mf_1_9_gkn5pkx9_.arc
2019-07-01T23:01:02.142326+08:00
Resize operation completed for file# 3, old size 522240K, new size 552960K
Restore point ORCL_GRP_PRIMARY propagated from primary already exists
2019-07-01T23:01:02.215320+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORCL_SB/archivelog/2019_07_01/o1_mf_1_10_gkn5pnf4_.arc
2019-07-01T23:01:02.657960+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORCL_SB/archivelog/2019_07_01/o1_mf_1_11_gkn6s6g1_.arc
2019-07-01T23:01:02.795226+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORCL_SB/archivelog/2019_07_01/o1_mf_1_12_gkn7cxvl_.arc
2019-07-01T23:01:02.956167+08:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2019-07-01T23:01:04.624321+08:00
Incomplete Recovery applied until change 2084069 time 07/01/2019 22:47:05
Flashback Media Recovery Complete

 
When we see the message “Flashback Media Recovery Complete” in the standby database alert log, we can now open the standby database.

Note that the Data Guard Broker configuration is not showing any error and we did not have to perform any manual steps on the standby database to enable the configuration following the flashback of the primary database.
 

SQL> ALTER DATABASE OPEN;

Database altered.

DGMGRL> show configuration;

Configuration - orcl_dg

  Protection Mode: MaxPerformance
  Members:
  orcl    - Primary database
    orcl_sb - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 54 seconds ago)
 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *