26 Oct 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.
- shut down the standby database
- bring up the standby in mount mode
- set APPLY-OFF for the standby
- set APPLY-ON for the standby
- 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:
- Disable Apply in standby database
- Make sure that ARCHIVELOG mode is enabled
- Set the following 2 parameters
db_recovery_file_dest
db_recovery_file_dest_size - Enable flashback
- 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 🙂
Sorry, the comment form is closed at this time.