Automatic Flashback Of Standby Database in Oracle 19c

Oracle 19c Data Guard introduced a very cool feature — you can now automatically flashback your standby if you flashback your primary.

I'll demonstrate this in the following test scenario by creating a table called rene.test.

############################################################################################################## 
 ## Primary Database
 ############################################################################################################## 
 
 SQL> create table rene.test( name varchar2(100), num number) ;
 
 Table created.
 
 SQL> Begin
 for x in 1..3
 loop
 for x in 1..100000
 Loop
 insert into rene.test values('sdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', x);
 End Loop;
 commit;
 end loop;
 END;
 /
 
 PL/SQL procedure successfully completed.
 
 SQL> select count(1) from rene.test;
 
  COUNT(1)
 ----------
  300000
 
 ##############################################################################################################
 ## Standby Database
 ############################################################################################################## 
 SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
 
  INST_ID NAME OPEN_MODE  DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
 ---------- --------- -------------------- ---------------- -------------------- --------
 1 TESTDB READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
 
 SQL> select count(1) from rene.test;
 
  COUNT(1)
 ----------
  300000

Now that I've created the table, I create a guaranteed restore point (GRP) in the primary database.

SQL> create restore point TEST_GRP guarantee flashback database;
 
 Restore point created.

If I check my primary and standby database, I can see the restore point is present in both databases. The only difference is that in the standby it is not a guaranteed restore point; it is a replicated restore point, and it adds the suffix "_PRIMARY" to the GRP name.

############################################################################################################## 
 ## Primary Database
 ############################################################################################################## 
 
 SET PAGESIZE 60
 SET LINESIZE 300
 SET VERIFY OFF
 
 COLUMN scn FOR 999999999999999
 COLUMN Incar FOR 99
 COLUMN name FOR A25
 COLUMN storage_size FOR 999,999,999,999
 COLUMN guarantee_flashback_database FOR A3
 
 SELECT 
  database_incarnation# as Incar,
  scn,
  name,
  time,
  storage_size,
  guarantee_flashback_database
 FROM 
  v$restore_point
 ORDER BY 4;
 
 INCAR  SCN NAME  TIME  STORAGE_SIZE GUA
 ----- -------------------- ------------------------- --------------------------------------------------------------------------- ---------------- ---
  2  2142920 TEST_GRP  25-OCT-20 10.50.59.000000000 AM  52428800 YES
 
 ##############################################################################################################
 ## Standby Database
 ############################################################################################################## 
 INCAR  SCN NAME  TIME  STORAGE_SIZE GUA
 ----- -------------------- ------------------------- --------------------------------------------------------------------------- ---------------- ---
  2  2142920 TEST_GRP_PRIMARY  25-OCT-20 10.50.59.000000000 AM 0 NO

Unlike in the primary database log, you won't see the creation of the restore point in the standby database log. Below I'm conducting a verification in the log to see whether the restore point was created.

##############################################################################################################
 ## Primary Database log
 ############################################################################################################## 
 
 
 [oracle@primary trace]$ cat alert_TESTDB.log | grep TEST_GR
 Created guaranteed restore point TEST_GRP
 
 ##############################################################################################################
 ## Standby Database log
 ############################################################################################################## 
 [oracle@standby trace]$ cat alert_TESTDB.log | grep TEST_GRP | wc -l
 0

The next step is to drop the table as if it were the disaster that I want to flashback my database from.

############################################################################################################## 
 ## Primary Database
 ############################################################################################################## 
 
 SQL> drop table rene.test;
 
 Table dropped.
 
 SQL> select count(1) from rene.test;
 select count(1) from rene.test
  *
 ERROR at line 1:
 ORA-00942: table or view does not exist
 
 ##############################################################################################################
 ## Standby Database
 ############################################################################################################## 
 SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
 
  INST_ID NAME OPEN_MODE  DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
 ---------- --------- -------------------- ---------------- -------------------- --------
 1 TESTDB READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
 
 SQL> select count(1) from rene.test;
 select count(1) from rene.test
  *
 ERROR at line 1:
 ORA-00942: table or view does not exist

Before I proceed to doing a flashback of the primary DB, I'll validate that everything is OK with my Data Guard environment.

DGMGRL> validate database testdb_stdby;
 
  Database Role: Physical standby database
  Primary Database: testdb
 
  Ready for Switchover: Yes
  Ready for Failover: Yes (Primary Running)
 ...
 
 DGMGRL> show database testdb_stdby;
 
 Database - testdb_stdby
 
  Role: PHYSICAL STANDBY
  Intended State: APPLY-ON
  Transport Lag: 0 seconds (computed 0 seconds ago)
  Apply Lag: 0 seconds (computed 0 seconds ago)
  Average Apply Rate: 8.00 KByte/s
  Real Time Query: ON
  Instance(s):
  TESTDB
 
 Database Status:
 SUCCESS

Once I have done that, I'll flashback my primary database to the GRP created above.

SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.
 
 Total System Global Area 2415917880 bytes
 Fixed Size  8899384 bytes
 Variable Size  520093696 bytes
 Database Buffers 1879048192 bytes
 Redo Buffers  7876608 bytes
 Database mounted.
 SQL> flashback database to restore point TEST_GRP;
 
 Flashback complete.
 
 SQL> alter database open resetlogs;
 
 Database altered.
 
 ############################################################################################################## 
 ## From the Primary Database log
 ############################################################################################################## 
 2020-10-25T19:59:22.147780-06:00
 Incomplete Recovery applied until change 2142921 time 10/25/2020 19:54:34
 2020-10-25T19:59:22.154955-06:00
 Flashback Media Recovery Complete
 Completed: flashback database to restore point TEST_GRP
 2020-10-25T19:59:38.047040-06:00
 alter database open resetlogs
 2020-10-25T19:59:38.048798-06:00
 Data Guard Broker initializing...
 Data Guard Broker initialization complete
 Data Guard: verifying database primary role...
 Data Guard: broker startup completed
 Data Guard: primary database controlfile verified
 RESETLOGS after incomplete recovery UNTIL CHANGE 2142921 time 10/25/2020 19:54:34
 2020-10-25T19:59:38.124157-06:00
 NET (PID:23187): Archived Log entry 18 added for T-1.S-31 ID 0x903b45dd LAD:1
 2020-10-25T19:59:38.147340-06:00
 NET (PID:23187): Archived Log entry 19 added for T-1.S-29 ID 0x903b45dd LAD:1
 2020-10-25T19:59:38.169097-06:00
 NET (PID:23187): Archived Log entry 20 added for T-1.S-30 ID 0x903b45dd LAD:1

I have to take the following steps as the standby database is in READ ONLY WITH APPLY.

1. Shut down the standby database.
2. Bring up the standby in mount mode.
3. Set APPLY-OFF for the standby.
4. Set APPLY-ON for the standby.
5. Open the standby read only.

If the standby database had been in the MOUNTED state I wouldn't have to do anything else.

############################################################################################################## 
 ##a. Standby Database
 ############################################################################################################## 
 
 SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.
 
 Total System Global Area 2415917880 bytes
 Fixed Size  8899384 bytes
 Variable Size  520093696 bytes
 Database Buffers 1879048192 bytes
 Redo Buffers  7876608 bytes
 Database mounted.
 
 SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
 
  INST_ID NAME OPEN_MODE  DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
 ---------- --------- -------------------- ---------------- -------------------- --------
 1 TESTDB MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
 
 ############################################################################################################## 
 ##b. Primary Database
 ##############################################################################################################
 
 [oracle@primary ~]$ dgmgrl /
 DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Oct 26 09:01:30 2020
 Version 19.3.0.0.0
 
 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
 
 Welcome to DGMGRL, type "help" for information.
 Connected to "TESTDB"
 Connected as SYSDG.
 DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-OFF'; 
 Succeeded.
 DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-ON'; 
 Succeeded.
 
 ############################################################################################################## 
 ##c. Standby Database
 ##############################################################################################################
 
 SQL> alter database open read only;
 
 Database altered.
 

In my standby database log, I can see that once I initiate the flashback in my primary, and open it with the resetlogs option, it will automatically flashback my standby database.

2020-10-25T19:59:41.084190-06:00
  rfs (PID:19991): Standby in the future of new recovery destination branch(resetlogs_id) 1054756778
  rfs (PID:19991): Incomplete Recovery SCN:0x000000000020b3f7
  rfs (PID:19991): Resetlogs SCN:0x000000000020b2ca
  rfs (PID:19991): Flashback database to SCN:0x000000000020b2c9 (2142921) to follow new branch
  rfs (PID:19991): New Archival REDO Branch(resetlogs_id): 1054756778 Prior: 1054754847
  rfs (PID:19991): Archival Activation ID: 0x903b4441 Current: 0x903b45dd
  rfs (PID:19991): Effect of primary database OPEN RESETLOGS
  rfs (PID:19991): Managed Standby Recovery process is active
 2020-10-25T19:59:41.086259-06:00
 Incarnation entry added for Branch(resetlogs_id): 1054756778 (TESTDB)
 2020-10-25T19:59:41.115292-06:00
 Setting recovery target incarnation to 3
 2020-10-25T19:59:41.116577-06:00
 PR00 (PID:19541): MRP0: Incarnation has changed! Retry recovery...
 ...
 2020-10-25T19:59:42.767092-06:00
 Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_mrp0_19535.trc:
 ORA-19909: datafile 1 belongs to an orphan incarnation
 ORA-01110: data file 1: '/u02/oradata/TESTDB_STDBY/datafile/o1_mf_system_hsdb684s_.dbf'
 2020-10-25T20:00:02.784506-06:00
 MRP0 (PID:19535): Recovery coordinator performing automatic flashback of database to SCN:0x000000000020b2c8 (2142920)
 Flashback Restore Start
 Flashback Restore Complete
 Flashback Media Recovery Start
 2020-10-25T20:00:03.076773-06:00
 Setting recovery target incarnation to 2
 2020-10-25T20:00:03.088461-06:00
  Started logmerger process
 2020-10-25T20:00:03.151319-06:00
 Parallel Media Recovery started with 2 slaves
 2020-10-25T20:00:03.275940-06:00
 stopping change tracking
 2020-10-25T20:00:03.372578-06:00
 Media Recovery Log /u01/app/oracle/TESTDB_STDBY/archivelog/2020_10_25/o1_mf_1_26_hsdbzxkt_.arc
 ...
 2020-10-25T20:00:06.743546-06:00
 Media Recovery Log /u01/app/oracle/TESTDB_STDBY/archivelog/2020_10_25/o1_mf_1_29_hsdc38v6_.arc
 2020-10-25T20:00:07.052081-06:00
 Incomplete Recovery applied until change 2142920 time 10/25/2020 19:54:34
 2020-10-25T20:00:07.060043-06:00
 Flashback Media Recovery Complete
 2020-10-25T20:00:07.129168-06:00
 stopping change tracking
 2020-10-25T20:00:07.152900-06:00
 Setting recovery target incarnation to 3
 2020-10-25T20:00:07.174093-06:00
  Started logmerger process
 2020-10-25T20:00:07.183156-06:00
 PR00 (PID:20040): Managed Standby Recovery starting Real Time Apply
 2020-10-25T20:00:07.242139-06:00
 Parallel Media Recovery started with 2 slaves
 2020-10-25T20:00:07.368253-06:00

Once I've done that, the table is back in both primary and standby.

SQL> select count(1) from rene.test;
 
  COUNT(1)
 ----------
  300000
 
 ##############################################################################################################
 ## Standby Database
 ##############################################################################################################
 
 SQL> select INST_ID, name , open_mode , database_role ,switchover_status, DATAGUARD_BROKER from gv$database;
 
  INST_ID NAME OPEN_MODE  DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
 ---------- --------- -------------------- ---------------- -------------------- --------
 1 TESTDB READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
 
 SQL> select count(1) from rene.test;
 
  COUNT(1)
 ----------
  300000

One thing to remember is to enable flashback in the standby database. If you don't enable it you'll get the errors below, and the MRP (managed recovery process) will never start.

##############################################################################################################
 ## Standby Database
 ############################################################################################################## 
 
 SQL> select flashback_on from v$database;
 
 FLASHBACK_ON
 ------------------
 NO
 
 ############################################################################################################## 
 ## From the Standby Database log
 ##############################################################################################################
 2020-10-25T18:58:01.338567-06:00
  rfs (PID:4675): Standby in the future of new recovery destination branch(resetlogs_id) 1054753080
  rfs (PID:4675): Incomplete Recovery SCN:0x00000000002123dc
  rfs (PID:4675): Resetlogs SCN:0x000000000020b027
  rfs (PID:4675): New Archival REDO Branch(resetlogs_id): 1054753080 Prior: 1054722537
  rfs (PID:4675): Archival Activation ID: 0x903bd59c Current: 0x903a9da7
  rfs (PID:4675): Effect of primary database OPEN RESETLOGS
  rfs (PID:4675): Managed Standby Recovery process is active
 2020-10-25T18:58:58.128051-06:00
 ARC2 (PID:18468): Archived Log entry 5 added for T-1.S-2 ID 0x903bd59c LAD:1
 2020-10-25T18:59:38.299028-06:00
 PR00 (PID:18518): MRP0: Incarnation has changed! Retry recovery...
 2020-10-25T18:59:38.300079-06:00
 Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_pr00_18518.trc:
 ORA-19906: recovery target incarnation changed during recovery
 PR00 (PID:18518): Managed Standby Recovery not using Real Time Apply
 Recovery interrupted!
 Recovered data files to a consistent state at change 2172426
 stopping change tracking
 2020-10-25T18:59:38.381319-06:00
 Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_pr00_18518.trc:
 ORA-19906: recovery target incarnation changed during recovery
 2020-10-25T18:59:38.520995-06:00
  Started logmerger process
 ...
 Recovery Slave PR00 previously exited with exception 19909
 2020-10-25T19:11:09.118089-06:00
 Errors in file /u01/app/oracle/diag/rdbms/testdb_stdby/TESTDB/trace/TESTDB_mrp0_5511.trc:
 ORA-19909: datafile 1 belongs to an orphan incarnation
 ORA-01110: data file 1: '/u02/oradata/TESTDB_STDBY/datafile/o1_mf_system_hscbmkj0_.dbf'
 2020-10-25T19:11:29.131828-06:00
 MRP0 (PID:5511): Recovery coordinator encountered one or more errors during automatic flashback on standby
 2020-10-25T19:11:29.132082-06:00
 Background Media Recovery process shutdown (TESTDB)

You would enable flashback in a standby database as below:

1. Disable apply in standby database.
2. Make sure ARCHIVELOG mode is enabled.
3. Set the following two parameters:
   db_recovery_file_dest
   db_recovery_file_dest_size
4. Enable flashback.
5. Enable apply in standby database.

[oracle@standby ~]$ dgmgrl /
 Connected to "TESTDB_STDBY"
 Connected as SYSDG.
 DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-OFF'; 
 Succeeded.
 DGMGRL> exit
 
 [oracle@standby ~]$ sqlplus / as sysdba
 
 SQL> archive log list;
 Database log mode  Archive Mode
 Automatic archival  Enabled
 Archive destination  USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 0
 Next log sequence to archive 0
 Current log sequence  0
 SQL> show parameter db_recovery_file_dest
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_recovery_file_dest  string /u01/app/oracle
 db_recovery_file_dest_size  big integer 20G
 SQL> alter database flashback ON;
 
 Database altered.
 
 SQL> exit
 
 [oracle@standby ~]$ dgmgrl /
 Connected to "TESTDB_STDBY"
 Connected as SYSDG.
 DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-ON'; 
 Succeeded.
 

I hope this new feature will help when you're using Oracle Data Guard. I know if I'd had this earlier, it would have saved me hours of work :)

Note: This was originally published on rene-ace.com.