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 ############################################################################################################## [[email protected] trace]$ cat alert_TESTDB.log | grep TEST_GR Created guaranteed restore point TEST_GRP ############################################################################################################## ## Standby Database log ############################################################################################################## [[email protected] 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.
- 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 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 ############################################################################################################## [[email protected] ~]$ 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:
- Disable apply in standby database.
- Make sure ARCHIVELOG mode is enabled.
- Set the following two parameters:
db_recovery_file_dest
db_recovery_file_dest_size - Enable flashback.
- Enable apply in standby database.
[[email protected] ~]$ dgmgrl / Connected to "TESTDB_STDBY" Connected as SYSDG. DGMGRL> EDIT DATABASE 'testdb_stdby' SET STATE='APPLY-OFF'; Succeeded. DGMGRL> exit [[email protected] ~]$ 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 [[email protected] ~]$ 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.
No comments