Standby Automatic Flashback [19c feature]

One of the new Database 19c Features is the possibility of automatically Flashback the standby database if the Primary has been Flashback.

There are couple of requisites though, the Standby has to have Flashback enabled, and in order for the Automatic flashback to start, the standby has to be mounted

Lets do a quick test for our 19c Standby (st19) doing the Flashback in Primary (db19):

DB_UNIQUE INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE            STATUS
--------- ---------------- ---------------- -------------------- ------------
st19      st191            PHYSICAL STANDBY READ ONLY WITH APPLY OPEN
st19      st192            PHYSICAL STANDBY READ ONLY WITH APPLY OPEN

STANDBY - SQL>

DB_UNIQUE INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE            STATUS
--------- ---------------- ---------------- -------------------- ------------
db19      db191            PRIMARY          READ WRITE           OPEN
db19      db192            PRIMARY          READ WRITE           OPEN

PRIMARY - SQL>

Lets create a Guarantee Restore Point (GRP) in our Primary Database

PRIMARY - SQL> create restore point DB19 guarantee flashback database;

Restore point created.

PRIMARY - SQL>

Now we can check the Standby and confirm that the GRP has been replicated:

STANDBY - SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, NAME, REPLICATED from v$restore_point;

       SCN GUA NAME                 REPLICATED
---------- --- -------------------- ----------
   4184494 NO  DB19_PRIMARY         YES

STANDBY - SQL>

Lets do flashback of the system after creating a simple table as a test:

PRIMARY - SQL>create table P42 as select distinct(table_name) from dba_tables;

Table created.

PRIMARY - SQL>select count(*) from P42;

  COUNT(*)
----------
      2172

PRIMARY - SQL>

STANDBY - SQL>select count(*) from P42;

  COUNT(*)
----------
      2172

STANDBY - SQL>

[oracle@rac1-node1 ~]$ srvctl stop database -d db19 
[oracle@rac1-node1 ~]$ srvctl start instance -i db191 -d db19 -o mount
[oracle@rac1-node1 ~]$ sqlplus / as sysdba

PRIMARY - SQL>flashback database to restore point DB19;

Flashback complete.

PRIMARY - SQL>alter database open resetlogs;

Database altered.

PRIMARY - SQL> select count(*) from P42;
select count(*) from P42
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

PRIMARY - SQL>

Something to be aware of, if the Standby is OPEN, the standby will acknowledge the primary Flashback operation, but won’t do more than that, and we can see how the P42 table still exist:

2020-12-17T11:33:05.516913+00:00
 rfs (PID:29075): New archival redo branch: 1059391930 current: 1022248506
2020-12-17T11:33:05.624007+00:00
 rfs (PID:29077): New archival redo branch: 1059391930 current: 1022248506
 rfs (PID:29077): Primary database is in MAXIMUM PERFORMANCE mode
2020-12-17T11:33:06.017191+00:00
 rfs (PID:29075): Selected LNO:113 for T-1.S-1 dbid 753464436 branch 1059391930
2020-12-17T11:33:06.308893+00:00
 rfs (PID:29077): Selected LNO:114 for T-1.S-2 dbid 753464436 branch 1059391930
2020-12-17T11:33:06.603423+00:00
 rfs (PID:29075): A new recovery destination branch has been registered
 rfs (PID:29075): Standby in the future of new recovery destination branch(resetlogs_id) 1059391930
 rfs (PID:29075): Incomplete Recovery SCN:0x00000000003f6e8d
 rfs (PID:29075): Resetlogs SCN:0x00000000003f6a37
 rfs (PID:29075): SBPS:0x00000000003b7ed8
 rfs (PID:29075): New Archival REDO Branch(resetlogs_id): 1059391930  Prior: 1022248506
 rfs (PID:29075): Archival Activation ID: 0x2f200010 Current: 0x2efb8e04
 rfs (PID:29075): Effect of primary database OPEN RESETLOGS

STANDBY - SQL> select inst_id, process, status MRP_stat, 
thread#, sequence#, block#, BLOCKS "Total Blocks"
from gv$managed_standby
where process like 'MRP%' or process like 'RFS%' and status != 'IDLE'
order by inst_id,process,thread# ; 

no rows selected

STANDBY - SQL>select count(*) from P42;

  COUNT(*)
----------
      2172

STANDBY - SQL>

Now, in order to have Standby flashback, we just need to restart it as mounted and once the Recovery process tries to start, the system will automatically flashback to same point the primary did, doing an automatic flashback of the database

[oracle@rac2-node1 ~]$ srvctl stop database -d st19 
[oracle@rac2-node1 ~]$ srvctl start database -d st19 -o mount

2020-12-17T13:31:43.570568+00:00
Recovery Slave PR00 previously exited with exception 19909
2020-12-17T13:31:43.619669+00:00
Errors in file /u01/app/oracle/diag/rdbms/st19/st191/trace/st191_mrp0_17530.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA/ST19/DATAFILE/system.280.1059395945'
2020-12-17T13:31:53.934900+00:00
Decreasing number of high priority LMS from 2 to 0

2020-12-17T13:32:03.635729+00:00
MRP0 (PID:17530): Recovery coordinator performing automatic flashback of database to SCN:0x00000000003fd9ae (4184494)   <<<<<<<<

Flashback Restore Start
2020-12-17T13:32:03.911538+00:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2020-12-17T13:32:06.719660+00:00
Flashback Restore Complete
Flashback Media Recovery Start
2020-12-17T13:32:06.806321+00:00
Setting recovery target incarnation to 3
2020-12-17T13:32:06.839646+00:00
 Started logmerger process

And voila!, Flashback completed 🙂