Automatic Flashback Of Standby Database in Oracle 19c

Automatic Flashback Of Standby Database in Oracle 19c

Oracle 19c DataGuard introduced a very cool feature which is the ability to flashback your standby automatically if you flashback your primary. 

The first thing I do in this test scenario is create 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

Once I have created the table, I will now 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 will see that the restore point is present in both databases, the only difference is that in the standby it is not as a guaranteed restore point, it is what is called 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 in the standby database log of the standby the creation of the restore point. Here I am just doing a verification in the log to see if it was created or not.

## 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

What I will do next is to proceed 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 will validate that everything is ok with my DataGuard 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 will now 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 do the following 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 would have been only in 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.

What I will see now in my standby database log, is that once I had initiated the flashback in my primary and opened it resetlogs, it will now proceed to 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 had done that, I will now see 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 know, is that flashback has to be enabled in the standby database, or else you will get the following errors if it is not enabled and the MRP 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 that ARCHIVELOG mode is enabled
  3. Set the following 2 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.

Hope this new feature will help you when you are using Oracle DataGuard, I know that if I would have had this, it would have saved me from hours of work in the past 🙂 

Comments

comments

Tags:
Rene Antunez
antunez.rene@gmail.com
No Comments

Leave a Reply