از اوراکل 19c با اجرای دستور flashback database و یا انجام عملیات point in time recovery در محیط دیتابیس اصلی(primary)؛ دیتاگارد هم به صورت خودکار flashback خواهد شد و از حالت sync با primary خارج نمی شود(البته در صورت فعال بودن قابلیت flashback). این قابلیت با کمک پارامتر مخفی standby_auto_flashback_ قابل مدیریت است.

در ادامه متن زیر، با اجرای دستور flashback database و همچنین انجام PITR در محیط primary، رفتار دیتاگارد را در دو نسخه 18c و 19c مقایسه می کنیم.

تاثیر عملیات flashback در استندبای(اوراکل 18c)

–primary 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)

همانطور که در پیامهای alert log قابل مشاهده است، دیتاگارد از مود ریکاوری خارج شده و با قرار دادن آن در وضعیت mount، کماکان این خطا تکرار خواهد شد:

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’

برای حل این مشکل در اوراکل 18c می توانیم از دستور زیر استفاده کنیم:

FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# – 2;

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 پیامهای زیر را می بینیم:

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)

 

تاثیر عملیات flashback در استندبای(اوراکل 19c)

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jul 5 03:28:44 2020

–primary:

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)

 

برگشت خودکار دیتاگارد در زمان PITR

همانطور که مشاهده شد، در اوراکل 19ه، flashback دستی primary منجر به flashback خودکار دیتاگارد خواهد شد حال قصد داریم این کار را برای point in time recovery تکرار کنیم:

On primary(oracle 19c):

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

 

On 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)