Automatic Flashback Of Standby Database in Oracle 19c

Posted in: Oracle, Technical Track

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.

email

Authors

Interested in working with Rene? Schedule a tech call.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

No comments

Leave a Reply

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