Oracle 19c Automatic Flashback Of Standby Database.
Description:-
In this article we are going to see another 19c new feature of dataguard option. Automatic Flashback Of Standby Database.
Click Here Restore Point Replication From Primary To Standby
- Oracle Database Release 19c New Features.
- The process of flashing back a physical standby to a point in time that was captured on the primary is
simplified by automatically replicating restore points from primary to the standby. - These restore points are called replicated restore points.
- Irrespective of whether a restore point on the primary database is a guaranteed restore point or a normal restore point, the corresponding replicated restore point is
always a normal restore point.
Let’s Start Demo:-
On Primary Side:-
SQL> select status,instance_name,database_role,protection_mode ,flashback_on from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE FLASHBACK_ON
———— —————- ————- ————— ——————
OPEN chennai PRIMARY MAXIMUM AVAILABILITY YES
Testing Table
SQL> create table oracledbwr as select * from all_objects;
Table created.
SQL> select count(*) from oracledbwr;
COUNT(*)
———-
71344
SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from v$restore_point;
no rows selected
Create Restore Point:-
SQL> create restore point oracledbwr_Test1 guarantee flashback database;
Restore point created.
SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from v$restore_point;
SCN GUARANTEE_FLASHBACK_DATABASE TIME NAME REPLICATED
——————————————————————————————————
2461800 YES 20-OCT-19 06.48.44.000000000 PM ORACLEDBWR_TEST1 NO
On Standby Side
SQL> SELECT database_role, open_mode FROM v$database ;
DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY MOUNTED
SQL>select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from v$restore_point;
SCN GUARANTEE_FLASHBACK_DATABASE TIME NAME REPLICATED
————————————————————————————– —————-
2461800 NO 20-OCT-19 06.48.44.000000000 PM ORACLEDBWR_TEST1_PRIMARY YES
SQL> select status,instance_name,database_role,protection_mode ,flashback_on from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE FLASHBACK_ON
———— —————- —————- ——————– ——————
MOUNTED delhi PHYSICAL STANDBY MAXIMUM AVAILABILITY YES
Automatically replicated guarantee restore point from primary to the standby.These restore points can be Identified by “_PRIMARY” at the end of the original name and are displayed in V$RESTORE_POINT. This view has been updated and has new column ‘REPLICATED’
Test case 1:- Mount State
Note:- First testing is our standby database is in MOUNT State.If our database is in mount state MRP process on the standby database will start and perform the automatic flashback operation on the standby database as well
On Primary Side:-
SQL> truncate table oracledbwr;
Table truncated.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1048575776 bytes
Fixed Size 8904480 bytes
Variable Size 339738624 bytes
Database Buffers 696254464 bytes
Redo Buffers 3678208 bytes
Database mounted.
SQL> flashback database to restore point ORACLEDBWR_TEST1;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from oracledbwr;
COUNT(*)
———-
71344
On standby side
Alert log :-
Incarnation entry added for Branch(resetlogs_id): 1022181013 (delhi)
2019-10-20T19:10:32.769135+05:30
Setting recovery target incarnation to 3
2019-10-20T19:10:32.969946+05:30
MRP0 (PID:28594): MRP0: Incarnation has changed! Retry recovery…
2019-10-20T19:10:32.972017+05:30
ARC2 (PID:28575): Archived Log entry 40 added for T-1.S-2 ID 0x6d45faa LAD:1
2019-10-20T19:10:33.091913+05:30
Errors in file /u01/app/oracle/diag/rdbms/delhi/delhi/trace/delhi_mrp0_28594.trc:
ORA-19906: recovery target incarnation changed during recovery
2019-10-20T19:10:33.092061+05:30
MRP0 (PID:28594): Managed Standby Recovery not using Real Time Apply
2019-10-20T19:10:33.110062+05:30
Errors in file /u01/app/oracle/diag/rdbms/delhi/delhi/trace/delhi_rfs_29900.trc:
ORA-00316: log 4 of thread 1, type 0 in header is not log file
ORA-00312: online log 4 thread 1: ‘/u01/app/oracle/oradata/DELHI/redo04.log’
Recovery interrupted!
stopping change tracking
2019-10-20T19:10:33.135139+05:30
Errors in file /u01/app/oracle/diag/rdbms/delhi/delhi/trace/delhi_mrp0_28594.trc:
ORA-19906: recovery target incarnation changed during recovery
2019-10-20T19:10:33.137949+05:30
Serial Media Recovery started
MRP0 (PID:28594): Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 2464876) is orphaned on incarnation#=2
MRP0 (PID:28594): MRP0: Detected orphaned datafiles!
2019-10-20T19:10:33.157680+05:30
Errors in file /u01/app/oracle/diag/rdbms/delhi/delhi/trace/delhi_mrp0_28594.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/DELHI/system01.dbf’
MRP0 (PID:28594): Managed Standby Recovery not using Real Time Apply
stopping change tracking
2019-10-20T19:10:33.164765+05:30
Errors in file /u01/app/oracle/diag/rdbms/delhi/delhi/trace/delhi_mrp0_28594.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/DELHI/system01.dbf’
2019-10-20T19:10:33.252527+05:30
rfs (PID:29914): Opened log for T-1.S-1 dbid 114377971 branch 1022181013
2019-10-20T19:10:33.335676+05:30
rfs (PID:29914): Archived Log entry 41 added for B-1022181013.T-1.S-1 ID 0x6d45faa LAD:2
2019-10-20T19:10:35.134717+05:30
rfs (PID:29904): Changing standby controlfile to MAXIMUM AVAILABILITY level
2019-10-20T19:10:35.141150+05:30
rfs (PID:29904): Selected LNO:6 for T-1.S-4 dbid 114377971 branch 1022181013
2019-10-20T19:10:35.149388+05:30
ARC3 (PID:28577): Archived Log entry 42 added for T-1.S-3 ID 0x6d45faa LAD:1
2019-10-20T19:10:36.164383+05:30
Clearing online log 5 of thread 1 sequence number 0
2019-10-20T19:10:53.166672+05:30
MRP0 (PID:28594): Recovery coordinator performing automatic flashback of database to SCN:0x0000000000259068 (2461800)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
2019-10-20T19:10:53.484232+05:30
Setting recovery target incarnation to 2
2019-10-20T19:10:53.485138+05:30
Serial Media Recovery started
stopping change tracking
2019-10-20T19:10:53.509313+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_45_gtrpg8dz_.arc
2019-10-20T19:10:53.511647+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_46_gtrpg8bh_.arc
2019-10-20T19:10:53.518409+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_47_gtrpgc9g_.arc
2019-10-20T19:10:53.523456+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_48_gtrqyhg3_.arc
2019-10-20T19:10:53.764921+05:30
Incomplete Recovery applied until change 2461800 time 10/20/2019 18:48:44
Flashback Media Recovery Complete
stopping change tracking
2019-10-20T19:10:53.784173+05:30
Setting recovery target incarnation to 3
2019-10-20T19:10:53.788024+05:30
Serial Media Recovery started
MRP0 (PID:28594): Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 2461801
stopping change tracking
2019-10-20T19:10:53.809510+05:30
MRP0 (PID:28594): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_48_gtrqyhg3_.arc
2019-10-20T19:10:53.849574+05:30
MRP0 (PID:28594): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_1_gtrryk7n_.arc
2019-10-20T19:10:53.855285+05:30
MRP0 (PID:28594): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_2_gtrryjr4_.arc
2019-10-20T19:10:53.969752+05:30
MRP0 (PID:28594): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_3_gtrrym4g_.arc
MRP0 (PID:28594): Media Recovery Waiting for T-1.S-4 (in transit)
2019-10-20T19:10:53.973073+05:30
Recovery of Online Redo Log: Thread 1 Group 6 Seq 4 Reading mem 0
Check standby Database level
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 503316480 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
SQL> SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select count(*) from oracledbwr;
COUNT(*)
———-
71344
Test case 2:- READ ONLY WITH APPLY State
On Primary side
SQL> create table dbwr as select * from dba_objects;
Table created.
SQL> select count(*) from dbwr;
COUNT(*)
———-
72475
SQL> create restore point dbwr_test guarantee flashback database;
Restore point created.
on standby side
SQL> SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select count(*) from dbwr;
COUNT(*)
———-
72475
SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from v$restore_point;
SCN GUARANTEE_FLASHBACK_DATABASE TIME NAME REPLICATED
——————————————————————————————————
2465326 NO 20-OCT-19 07.28.54.000000000 PM DBWR_TEST_PRIMARY YES
On Primary side
SQL> truncate table dbwr;
Table truncated.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1048575776 bytes
Fixed Size 8904480 bytes
Variable Size 339738624 bytes
Database Buffers 696254464 bytes
Redo Buffers 3678208 bytes
Database mounted.
SQL> flashback database to restore point dbwr_test;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from dbwr;
COUNT(*)
———-
72475
On standby side
Note:-We need shutdown and startup in mount state
MRP0 (PID:30758): Recovery coordinator encountered one or more errors during automatic flashback on standby
2019-10-20T19:33:48.093780+05:30
Background Media Recovery process shutdown (delhi)
SQL> startup mount
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 503316480 bytes
Database Buffers 738197504 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
MRP0 (PID:32324): Recovery coordinator performing automatic flashback of database to SCN:0x0000000000259e2e (2465326)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
2019-10-20T19:39:40.506309+05:30
Setting recovery target incarnation to 3
2019-10-20T19:39:40.506700+05:30
Serial Media Recovery started
stopping change tracking
2019-10-20T19:39:40.534468+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_4_gtrskwwh_.arc
2019-10-20T19:39:40.637737+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_5_gtrskxj3_.arc
2019-10-20T19:39:40.721867+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_6_gtrsl3jb_.arc
2019-10-20T19:39:40.740930+05:30
Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_7_gtrt7bb7_.arc
Resize operation completed for file# 1, old size 1003520K, new size 1013760K
2019-10-20T19:39:41.059897+05:30
Incomplete Recovery applied until change 2465326 time 10/20/2019 19:28:54
Flashback Media Recovery Complete
stopping change tracking
2019-10-20T19:39:41.089703+05:30
Setting recovery target incarnation to 4
2019-10-20T19:39:41.097712+05:30
Serial Media Recovery started
MRP0 (PID:32324): Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 2465327
stopping change tracking
2019-10-20T19:39:41.145087+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_7_gtrt7bb7_.arc
2019-10-20T19:39:41.161992+05:30
Completed: alter database recover managed standby database disconnect nodelay
2019-10-20T19:39:41.214027+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_1_gtrt9gvn_.arc
2019-10-20T19:39:41.222760+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_2_gtrt9qp6_.arc
2019-10-20T19:39:41.258176+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_3_gtrt9r10_.arc
2019-10-20T19:39:41.272260+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_4_gtrtk4ry_.arc
2019-10-20T19:39:41.414368+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_5_gtrtk8fo_.arc
2019-10-20T19:39:41.449275+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_6_gtrtn09q_.arc
2019-10-20T19:39:41.453860+05:30
MRP0 (PID:32324): Media Recovery Log /u01/app/oracle/fast_recovery_area/DELHI/archivelog/2019_10_20/o1_mf_1_7_gtrtn5l2_.arc
MRP0 (PID:32324): Media Recovery Waiting for T-1.S-8 (in transit)
2019-10-20T19:39:41.460069+05:30
Cheers……
Connect to me:-
Telegram App:https://t.me/oracledbwr
LinkedIn: https://www.linkedin.com/in/hariprasathdba
LinkedIN Page:https://www.linkedin.com/company/orcldbwr/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/oracledbwr/
FB Page : https://www.facebook.com/dbahariprasath/
Twitter : https://twitter.com/hariprasathdba
