Automatic Flashback (or PITR) Standby

 Beginning with Oracle 19c, when executing FLASHBACK DATABASE command or performing a Point-In-Time Recovery (PITR) on the primary database, the Data Guard standby will also be automatically flashed back, ensuring it does not fall out of sync — provided that Flashback Database is enabled.

This feature is controlled by the hidden parameter _standby_auto_flashback.

In the following text, we compare the Data Guard behavior in Oracle 18c vs 19c when performing a flashback or PITR on the primary database.

Effect of Flashback Operations on the Standby (Oracle 18c)

Primary — Oracle 18c

SQL*Plus: Release 18.0.0.0.0 – Production on Thu Jul 2 08:56:22 2020

SQL> select current_scn from v$database;

             CURRENT_SCN

————————

   784527897542

SQL> create table mt1 as select * from v$datafile;

Table created.

SQL> startup force;

SQL> startup mount force;

SQL> flashback database to scn 784527897542;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

–Data Guard 18c:

rfs (PID:15413): Opened log for T-1.S-2 dbid 998481737 branch 1044945942

2020-07-05T02:45:43.677410-04:00

 rfs (PID:15411): A new recovery destination branch has been registered

 rfs (PID:15411): Standby in the future of new recovery destination branch(resetlogs_id) 1044945942

 rfs (PID:15411): Incomplete Recovery SCN: 784528084868

 rfs (PID:15411): Resetlogs SCN: 784527782003

 rfs (PID:15411): Standby Became Primary SCN: 784526779211

 rfs (PID:15411): New Archival REDO Branch(resetlogs_id): 1044945942  Prior: 1044696841

 rfs (PID:15411): Archival Activation ID: 0x3da0b826 Current: 0x3d9d153c

 rfs (PID:15411): Effect of primary database OPEN RESETLOGS

 rfs (PID:15411): Managed Standby Recovery process is active

Deleted Oracle managed file /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_1_hj2xrqk9_.arc

2020-07-05T02:45:59.240446-04:00

 rfs (PID:15413): Archived Log entry 76 added for T-1.S-2 rlc 1044945942 ID 0x3da0b826 LAD:2

2020-07-05T02:46:03.692442-04:00

Deleted file /18c/home/dbs/archreact_test_3b839f49.arc

2020-07-05T02:46:10.335173-04:00

PR00 (PID:6211): MRP0: Incarnation has changed! Retry recovery…

2020-07-05T02:46:10.335386-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_pr00_6211.trc:

ORA-19906: recovery target incarnation changed during recovery

Recovery interrupted!

stopping change tracking

2020-07-05T02:46:10.482972-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_pr00_6211.trc:

ORA-19906: recovery target incarnation changed during recovery

2020-07-05T02:46:10.652829-04:00

 Started logmerger process

2020-07-05T02:46:10.687194-04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

PR00 (PID:15444): Managed Standby Recovery not using 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 784528084868) is orphaned on incarnation#=5

PR00 (PID:15444): MRP0: Detected orphaned datafiles!

PR00 (PID:15444): Recovery will possibly be retried after flashback…

2020-07-05T02:46:10.701103-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_pr00_15444.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/18c/base/oradata/DB18C/system01.dbf’

stopping change tracking

2020-07-05T02:46:11.854465-04:00

Recovery Slave PR00 previously exited with exception 19909

2020-07-05T02:46:11.854984-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_mrp0_6205.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/18c/base/oradata/DB18C/system01.dbf’

2020-07-05T02:46:31.860563-04:00

Background Media Recovery process shutdown (stb18c)

As shown in the alert log messages, the Data Guard standby exits managed recovery mode.
Even after mounting the standby database, the following errors continue:

SQL> startup mount force;

ORACLE instance started.

Total System Global Area 3741316368 bytes

Fixed Size                  8664336 bytes

Variable Size            1291845632 bytes

Database Buffers         2432696320 bytes

Redo Buffers                8110080 bytes

Database mounted.

SQL> alter database recover managed standby database ;         

ORA-00283: recovery session canceled due to errors

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/18c/base/oradata/DB18C/system01.dbf’

Fixing the issue in Oracle 18c

To resolve this problem in Oracle 18c, you must manually flash back the standby:

FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# – 2;

Example:

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# – 2) FROM V$DATABASE;

TO_CHAR(RESETLOGS_CHANGE#-2)

—————————————-

20477777

SQL> flashback database to scn 784527897542;

Flashback complete.

SQL>  alter database recover managed standby database disconnect from session;

Database altered.

Alert log messages confirm successful flashback and restart of managed recovery:

flashback database to scn 784527897542

2020-07-05T04:45:33.783039-04:00

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

2020-07-05T04:45:33.947976-04:00

Setting recovery target incarnation to 7

2020-07-05T04:45:33.972208-04:00

 Started logmerger process

2020-07-05T04:45:34.195973-04:00

Parallel Media Recovery started with 8 slaves

2020-07-05T04:45:34.220994-04:00

stopping change tracking

2020-07-05T04:45:34.274863-04:00

Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_9_hj34jf5o_.arc

2020-07-05T04:45:34.365747-04:00

Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_10_hj34m0v8_.arc

2020-07-05T04:45:34.453660-04:00

Incomplete Recovery applied until change 784527897543 time 07/05/2020 08:41:29

2020-07-05T04:45:34.462613-04:00

Flashback Media Recovery Complete

2020-07-05T04:45:34.594961-04:00

stopping change tracking

2020-07-05T04:45:34.621168-04:00

Setting recovery target incarnation to 8

Completed: flashback database to scn 784527897542

2020-07-05T04:45:44.101033-04:00

 alter database recover managed standby database disconnect from session

2020-07-05T04:45:44.101643-04:00

WARNING: There are no standby redo logs.

Standby redo logs should be configured for real time apply. Real time apply will be ignored.

2020-07-05T04:45:44.102366-04:00

Attempt to start background Managed Standby Recovery process (stb18c)

Starting background process MRP0

2020-07-05T04:45:44.124276-04:00

MRP0 started with pid=8, OS id=27178

2020-07-05T04:45:44.126274-04:00

Background Managed Standby Recovery process started (stb18c)

2020-07-05T04:45:49.151924-04:00

 Started logmerger process

2020-07-05T04:45:49.186567-04:00

PR00 (PID:27187): Managed Standby Recovery not using Real Time Apply

2020-07-05T04:45:49.428452-04:00

Parallel Media Recovery started with 8 slaves

2020-07-05T04:45:49.457972-04:00

stopping change tracking

2020-07-05T04:45:49.487418-04:00

TT02 (PID:27205): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs

2020-07-05T04:45:49.539145-04:00

PR00 (PID:27187): Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_1_hj34n8sx_.arc

PR00 (PID:27187): Media Recovery Waiting for T-1.S-2 (in transit)

2020-07-05T04:45:50.130729-04:00

Completed:  alter database recover managed standby database disconnect from session

2020-07-05T04:46:23.560180-04:00

 rfs (PID:26959): Archived Log entry 7 added for T-1.S-2 rlc 1044952963 ID 0x3da119b1 LAD:2

 rfs (PID:26959): No SRLs created

2020-07-05T04:46:23.610183-04:00

 rfs (PID:26959): Opened log for T-1.S-3 dbid 998481737 branch 1044952963

2020-07-05T04:46:24.124546-04:00

PR00 (PID:27187): Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_2_hj34n8px_.arc

PR00 (PID:27187): Media Recovery Waiting for T-1.S-3 (in transit)

Effect of Flashback Operations on the Standby (Oracle 19c)

Primary — Oracle 19c

SQL> select current_scn from v$database;

CURRENT_SCN

———–

   20070527

SQL> create table mt1 as select * from v$datafile;

Table created.

SQL> startup force;

SQL> startup mount force;

Database mounted.

SQL> flashback database to scn 20070527;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

–Data Guard

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_tt00_25662.trc:

ORA-16009: invalid redo transport destination

2020-07-05T07:32:43.711466+00:00

TT00 (PID:25662): krsg_check_connection: Error 16009 connecting to standby ‘db19c’

2020-07-05T07:32:44.435917+00:00

 rfs (PID:25677): Primary database is in MAXIMUM PERFORMANCE mode

 rfs (PID:25677): No SRLs available for T-1

2020-07-05T07:32:44.509354+00:00

 rfs (PID:25677): Opened log for T-1.S-4 dbid 2168919747 branch 1044934295

2020-07-05T07:32:47.706889+00:00

alter database recover managed standby database disconnect from session

2020-07-05T07:32:47.729300+00:00

Attempt to start background Managed Standby Recovery process (stb)

Starting background process MRP0

2020-07-05T07:32:47.765227+00:00

MRP0 started with pid=47, OS id=25679

2020-07-05T07:32:47.767568+00:00

Background Managed Standby Recovery process started (stb)

2020-07-05T07:32:52.813125+00:00

 Started logmerger process

2020-07-05T07:32:52.834543+00:00

PR00 (PID:25681): 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 20271807) is orphaned on incarnation#=3

PR00 (PID:25681): MRP0: Detected orphaned datafiles!

2020-07-05T07:32:52.864948+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_25681.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

PR00 (PID:25681): Managed Standby Recovery not using Real Time Apply

stopping change tracking

2020-07-05T07:32:54.027457+00:00

Recovery Slave PR00 previously exited with exception 19909

2020-07-05T07:32:54.083593+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_mrp0_25679.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

2020-07-05T07:33:14.089966+00:00

MRP0 (PID:25679): Recovery coordinator performing automatic flashback of database to SCN:0x000000000132407f (20070527)

Flashback Restore Start

2020-07-05T07:33:14.832244+00:00

Completed: alter database recover managed standby database disconnect from session

2020-07-05T07:33:15.320937+00:00

Flashback Restore Complete

Flashback Media Recovery Start

2020-07-05T07:33:15.327426+00:00

Setting recovery target incarnation to 3

2020-07-05T07:33:15.360874+00:00

 Started logmerger process

2020-07-05T07:33:15.638776+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T07:33:15.670302+00:00

stopping change tracking

2020-07-05T07:33:15.743018+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_5_hj30b2vr_.arc

2020-07-05T07:33:15.849711+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_6_hj30b2wv_.arc

2020-07-05T07:33:16.048294+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_7_hj30b2y1_.arc

2020-07-05T07:33:16.328287+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_8_hj30b30d_.arc

2020-07-05T07:33:16.450049+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_9_hj30b30q_.arc

2020-07-05T07:33:16.860249+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_10_hj30dwh1_.arc

2020-07-05T07:33:17.499131+00:00

Incomplete Recovery applied until change 20070527 time 07/05/2020 03:29:40

2020-07-05T07:33:17.504452+00:00

Flashback Media Recovery Complete

2020-07-05T07:33:17.651017+00:00

stopping change tracking

2020-07-05T07:33:17.785469+00:00

Setting recovery target incarnation to 4

2020-07-05T07:33:17.929586+00:00

 Started logmerger process

2020-07-05T07:33:17.953255+00:00

PR00 (PID:25713): Managed Standby Recovery starting Real Time Apply

2020-07-05T07:33:18.390448+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T07:33:18.412086+00:00

Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 20070528

stopping change tracking

2020-07-05T07:33:18.516305+00:00

TT02 (PID:25731): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs

2020-07-05T07:33:18.531164+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_10_hj30dwh1_.arc

2020-07-05T07:33:18.822402+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_1_hj30h4t7_.arc

2020-07-05T07:33:18.934168+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_2_hj30h4st_.arc

2020-07-05T07:33:19.539777+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_3_hj30hzx4_.arc

PR00 (PID:25713): Media Recovery Waiting for T-1.S-4 (in transit)

Data Guard — Oracle 19c

Here, the behavior is different from Oracle 18c.
After detecting an incarnation mismatch, the Data Guard instance automatically performs:

✔ Automatic Flashback of the Standby to the appropriate SCN
✔ Automatic restart of managed recovery
✔ Return to synchronized mode

Automatic Data Guard Rollback During PITR

As demonstrated earlier, in Oracle 19c, performing a manual flashback on the primary automatically triggers a flashback on the standby.
Now let’s perform PITR and observe the results.

Primary (Oracle 19c) — PITR Test

SQL>startup force

RMAN> backup database format ‘/19c/bkp/%U’;

SQL> create table km as select * from v$datafile;

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN

———–

   20477778

SQL> alter system switch logfile;

System altered.

SQL> drop table km;

Table dropped.

SQL> alter system switch logfile;

 

[oracle@ol7 ~]$ rm -rf /19c/base/oradata/DB19C

[oracle@ol7 ~]$ rman target /

RMAN> restore database ;

RMAN> run{

2> set until scn 20477778;

3> recover database;

4> }

RMAN> alter database open resetlogs;

Statement processed

RMAN>  select count(*) from km;

  COUNT(*)

———-

         4

Data Guard(19c):

2020-07-05T10:46:07.047435+00:00

 rfs (PID:4316): New archival redo branch: 1044945955 current: 1044935053

 rfs (PID:4316): No SRLs available for T-1

2020-07-05T10:46:07.061615+00:00

 rfs (PID:4318): New archival redo branch: 1044945955 current: 1044935053

 rfs (PID:4318): Primary database is in MAXIMUM PERFORMANCE mode

2020-07-05T10:46:07.062180+00:00

 rfs (PID:4316): Opened log for T-1.S-1 dbid 2168919747 branch 1044945955

2020-07-05T10:46:07.070481+00:00

 rfs (PID:4318): No SRLs available for T-1

2020-07-05T10:46:07.079204+00:00

 rfs (PID:4318): Opened log for T-1.S-2 dbid 2168919747 branch 1044945955

2020-07-05T10:46:07.083697+00:00

 rfs (PID:4316): Standby in the future of new recovery destination branch(resetlogs_id) 1044945955

 rfs (PID:4316): Incomplete Recovery SCN:0x00000000013b8c7d

 rfs (PID:4316): Resetlogs SCN:0x0000000001387753

 rfs (PID:4316): Flashback database to SCN:0x0000000001387752 (20477778) to follow new branch

 rfs (PID:4316): New Archival REDO Branch(resetlogs_id): 1044945955  Prior: 1044935053

 rfs (PID:4316): Archival Activation ID: 0x81853ba3 Current: 0x81847647

 rfs (PID:4316): Effect of primary database OPEN RESETLOGS

 rfs (PID:4316): Managed Standby Recovery process is active

2020-07-05T10:46:07.085686+00:00

Incarnation entry added for Branch(resetlogs_id): 1044945955 (stb)

2020-07-05T10:46:07.090581+00:00

Setting recovery target incarnation to 6

2020-07-05T10:46:07.091086+00:00

 rfs (PID:4316): Archived Log entry 52 added for B-1044945955.T-1.S-1 ID 0x81853ba3 LAD:2

2020-07-05T10:46:07.915870+00:00

PR00 (PID:4086): MRP0: Incarnation has changed! Retry recovery…

2020-07-05T10:46:07.916194+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_4086.trc:

ORA-19906: recovery target incarnation changed during recovery

PR00 (PID:4086): Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

stopping change tracking

2020-07-05T10:46:08.048651+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_4086.trc:

ORA-19906: recovery target incarnation changed during recovery

2020-07-05T10:46:08.225883+00:00

 Started logmerger process

2020-07-05T10:46:08.245531+00:00

PR00 (PID:4324): 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 20679805) is orphaned on incarnation#=5

PR00 (PID:4324): MRP0: Detected orphaned datafiles!

2020-07-05T10:46:08.260033+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_4324.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

PR00 (PID:4324): Managed Standby Recovery not using Real Time Apply

stopping change tracking

2020-07-05T10:46:09.417192+00:00

Recovery Slave PR00 previously exited with exception 19909

2020-07-05T10:46:09.418012+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_mrp0_4084.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

2020-07-05T10:46:29.424152+00:00

MRP0 (PID:4084): Recovery coordinator performing automatic flashback of database to SCN:0x0000000001387751 (20477777)

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

2020-07-05T10:46:29.751325+00:00

Setting recovery target incarnation to 5

2020-07-05T10:46:29.780986+00:00

 Started logmerger process

2020-07-05T10:46:30.092377+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T10:46:30.117892+00:00

stopping change tracking

2020-07-05T10:46:30.172750+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_7_hj3cj1rg_.arc

2020-07-05T10:46:30.262732+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_8_hj3cj31s_.arc

2020-07-05T10:46:30.355557+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_9_hj3cl30z_.arc

2020-07-05T10:46:30.447894+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_10_hj3cl3s8_.arc

2020-07-05T10:46:30.540674+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_11_hj3cn284_.arc

2020-07-05T10:46:30.629217+00:00

Incomplete Recovery applied until change 20477777 time 07/05/2020 06:42:31

2020-07-05T10:46:30.633468+00:00

Flashback Media Recovery Complete

2020-07-05T10:46:30.764651+00:00

stopping change tracking

2020-07-05T10:46:30.800043+00:00

Setting recovery target incarnation to 6

2020-07-05T10:46:30.838072+00:00

 Started logmerger process

2020-07-05T10:46:30.856095+00:00

PR00 (PID:4348): Managed Standby Recovery starting Real Time Apply

2020-07-05T10:46:31.123891+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T10:46:31.139204+00:00

Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 20477778

stopping change tracking

2020-07-05T10:46:31.188394+00:00

TT02 (PID:4366): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs

2020-07-05T10:46:31.218128+00:00

PR00 (PID:4348): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_11_hj3cn284_.arc

2020-07-05T10:46:31.340878+00:00

PR00 (PID:4348): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_1_hj3cvh1w_.arc

PR00 (PID:4348): Media Recovery Waiting for T-1.S-2 (in transit)

Data Guard (19c) Behavior During PITR

Unlike Oracle 18c, the standby database in Oracle 19c automatically:

✔ Detects the new RESETLOGS branch
✔ Performs an automatic flashback to the correct SCN
✔ Resolves orphaned incarnations
✔ Restarts real-time apply
✔ Returns to full synchronization

Written by Vahid Yousefzadeh

I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com