Oracle闪回——闪回数据库

原创董宏伟云和恩墨 2023-02-13

2349

闪回的种类

Oracle闪回分类及对应使用的技术如下：

Flashback Database （flashback logs）
Flashback Drop （Recycle Bin）
Flashback Table （undo）
Flashback Query（undo）
Flashback Version（undo）
Flashback Transaction（undo）

闪回数据库

Oracle闪回数据库可以让你快速地把你的数据库转回到以前的时间点，通过撤消从那以后发生的所有的变化。该操作速度较快，不需要恢复备份。这使得数据损坏或人为错误后的停机时间大大减少。
它最适合作为完整数据库的不完全恢复的替代品。与不完全数据库恢复相比，Oracle Flashback数据库的主要好处是Flashback数据库更快更有效。闪回数据库不是基于undo数据，而是基于闪回日志。

闪回数据库的前提

必须具有SYSDBA系统权限。
已为数据库准备了flash recovery area。
必须使用ALTER database FLASHBACK ON语句将数据库置于FLASHBACK模式，除非您要将数据库闪回有保证的还原点。
数据库必须挂载但不能打开。

闪回数据库的限制

由于Flashback数据库是通过在运行命令时，撤消存在的数据文件的更改来工作的，因此它有以下限制:

闪回数据库只能撤销Oracle数据库对数据文件的修改。它不能用于修复media故障，或从意外删除的数据文件中恢复。
不能使用闪回数据库撤消收缩数据文件操作。
如果从备份中恢复或重新创建数据库控制文件，则所有累积的闪回日志信息将被丢弃。不能使用FLASHBACK DATABASE返回到恢复或重新创建控制文件之前的时间点。
当Flashback数据库的目标时间是NOLOGGING操作正在进行时，受NOLOGGING操作影响的数据库对象和数据文件中可能会出现块损坏。例如，在NOLOGGING模式下执行直接路径INSERT操作，该操作时间为9:00 - 9:15，稍后需要使用Flashback Database返回到该日期的目标时间09:07，则Flashback Database操作完成后，直接路径INSERT更新的对象和数据文件可能会出现块损坏。
收缩数据文件或删除表空间将阻止数据库闪回。

闪回数据库测试

闪回数据库到还原点

配置闪回区

SQL> show parameter recover 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/flashback
db_recovery_file_dest_size           big integer 10G
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL> 
复制

准备测试数据，创建还原点

SQL> create table t (id number);

Table created.

SQL> 
SQL> insert into t values(1);

1 row created.

SQL> commit ;

Commit complete.
10:45:51 SQL> select * from t;

        ID
----------
         1

Elapsed: 00:00:00.00
10:45:55 SQL> 
10:45:55 SQL> CREATE RESTORE POINT cdb_point1  GUARANTEE FLASHBACK DATABASE;

Restore point created.

Elapsed: 00:00:06.14
10:46:20 SQL> select oldest_flashback_scn, to_char(oldest_flashback_time,'yyyy-mm-dd HH24:mi:ss') oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
            23720778 2023-02-08 10:46:20
set lines 200
col name for a20
col RESTORE_POINT_TIME for a40
col TIME for a40
select * from v$restore_point;

10:49:15 SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRE NAME                 PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ---------------------------------------- ---------------------------------------- --- -------------------- --- --- ---------------- --- ----------
  23720778                     8 YES    209715200 08-FEB-23 10.46.13.000000000 AM                                                   YES CDB_POINT1           NO  NO                 0 NO           0

Elapsed: 00:00:00.01

10:49:57 SQL> insert into t values(2);

1 row created.

Elapsed: 00:00:00.00
10:50:52 SQL> commit ;

Commit complete.

Elapsed: 00:00:00.00
10:50:54 SQL> select * from t;

        ID
----------
         1
         2

Elapsed: 00:00:00.00
10:50:57 SQL> 
复制

PDB创建还原点

10:50:57 SQL> alter session set container=s_pdb;

Session altered.

Elapsed: 00:00:00.05
10:51:39 SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRE NAME                 PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ---------------------------------------- ---------------------------------------- --- -------------------- --- --- ---------------- --- ----------
  23720778                     8 YES    209715200 08-FEB-23 10.46.13.000000000 AM                                                   YES CDB_POINT1           NO  NO                 0 NO           0

Elapsed: 00:00:00.03
10:51:49 SQL> create table t (id number);

Table created.

Elapsed: 00:00:00.06
10:52:00 SQL> insert into t values(1);

1 row created.

Elapsed: 00:00:00.00
10:52:05 SQL> commit ;

Commit complete.

Elapsed: 00:00:00.00
10:52:06 SQL> select * from t;

        ID
----------
         1

Elapsed: 00:00:00.01
10:52:19 SQL> 
10:52:19 SQL> conn / as sysdba
Connected.
10:53:51 SQL> CREATE RESTORE POINT cdb_point2  GUARANTEE FLASHBACK DATABASE;

Restore point created.

Elapsed: 00:00:00.08
10:54:05 SQL> create restore point pdb_point1 for pluggable database s_pdb guarantee flashback database;

Restore point created.

Elapsed: 00:00:00.09
复制

还原点如下

2个CDB级别的，1个PDB级别的

10:54:46 SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRE NAME                 PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ---------------------------------------- ---------------------------------------- --- -------------------- --- --- ---------------- --- ----------
  23720778                     8 YES            0 08-FEB-23 10.46.13.000000000 AM                                                   YES CDB_POINT1           NO  NO                 0 NO           0
  23721055                     8 YES            0 08-FEB-23 10.54.05.000000000 AM                                                   YES CDB_POINT2           NO  NO                 0 NO           0
  23721279                     8 YES    209715200 08-FEB-23 10.54.32.000000000 AM                                                   YES PDB_POINT1           YES NO                 0 NO           3
复制

闪回CDB到还原点1

闪回CDB到还原点1，所有的PDB同时闪回到还原点1

10:58:10 SQL> flashback database to RESTORE POINT CDB_POINT1 ;  

Flashback complete.

Elapsed: 00:00:02.14
10:59:38 SQL>

10:59:54 SQL> alter database open RESETLOGS;

Database altered.

Elapsed: 00:00:32.39
11:00:32 SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 S_PDB                          READ WRITE NO
11:00:34 SQL> 

11:00:34 SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRE NAME                 PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ---------------------------------------- ---------------------------------------- --- -------------------- --- --- ---------------- --- ----------
  23720778                     8 YES            0 08-FEB-23 10.46.13.000000000 AM                                                   YES CDB_POINT1           NO  NO                 0 NO           0
  23721055                     8 YES            0 08-FEB-23 10.54.05.000000000 AM                                                   YES CDB_POINT2           NO  NO                 0 NO           0
  23721279                     8 YES    209715200 08-FEB-23 10.54.32.000000000 AM                                                   YES PDB_POINT1           YES NO                 0 NO           3

Elapsed: 00:00:00.13
11:00:44 SQL> select * from t;

        ID
----------
         1

Elapsed: 00:00:00.01
11:01:40 SQL> 

11:01:59 SQL> alter session set container=s_pdb;

Session altered.

Elapsed: 00:00:00.01
11:02:04 SQL>  select * from t;
 select * from t
               *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00
11:02:06 SQL> 

复制

PDB还原点失效

因为CDB闪回，resetlogs了，pdb的还原点就不能用了

11:06:59 SQL>  alter pluggable database S_PDB close;

Pluggable database altered.

11:07:06 SQL> flashback pluggable database S_PDB to restore point PDB_POINT1;
flashback pluggable database S_PDB to restore point PDB_POINT1
*
ERROR at line 1:
ORA-39867: Clean PDB restore point 'PDB_POINT1' is on an orphan incarnation of the pluggable database.


Elapsed: 00:00:00.01
11:07:10 SQL>  !oerr ora 39867
39867, 1, "Clean PDB restore point '%s' is on an orphan incarnation of the pluggable database."
// *Cause: The specified clean PDB restore point was on an orphan incarnation 
//         of the pluggable database. One cannot flashback a pluggable database
//         to an orphan incarnation.
// *Action: Check the restore point.

复制

闪回CDB到还原点2

11:08:58 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11:12:41 SQL> startup mount 
ORACLE instance started.

Total System Global Area 2147482376 bytes
Fixed Size                 37448456 bytes
Variable Size             788529152 bytes
Database Buffers         1308622848 bytes
Redo Buffers               12881920 bytes
Database mounted.
11:13:35 SQL> flashback database to RESTORE POINT CDB_POINT2;

Flashback complete.

Elapsed: 00:00:02.86
11:13:57 SQL> alter database open resetlogs;

Database altered.

Elapsed: 00:00:22.47
11:14:54 SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRE NAME                 PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ---------------------------------------- ---------------------------------------- --- -------------------- --- --- ---------------- --- ----------
  23720778                     8 YES            0 08-FEB-23 10.46.13.000000000 AM                                                   YES CDB_POINT1           NO  NO                 0 NO           0
  23721055                     8 YES            0 08-FEB-23 10.54.05.000000000 AM                                                   YES CDB_POINT2           NO  NO                 0 NO           0
  23721279                     8 YES    209715200 08-FEB-23 10.54.32.000000000 AM                                                   YES PDB_POINT1           YES NO                 0 NO           3

Elapsed: 00:00:00.11
11:15:31 SQL> select * from t;

        ID
----------
         1
         2

Elapsed: 00:00:00.01
11:15:39 SQL> alter session set container=s_pdb;

Session altered.

Elapsed: 00:00:00.03
11:15:52 SQL> select * from t;

        ID
----------
         1

Elapsed: 00:00:00.01
11:15:56 SQL> 
复制

pdb闪回测试

11:30:43 SQL> alter session set container=s_pdb;

Session altered.

Elapsed: 00:00:00.00
11:31:00 SQL> select * from t;

        ID
----------
         1
11:31:04 SQL> 
11:31:04 SQL> conn  / as sysdba
Connected.
11:31:37 SQL>  create restore point pdb_point1 for pluggable database s_pdb guarantee flashback database;

Restore point created.

Elapsed: 00:00:06.17
11:31:57 SQL> alter session set container=s_pdb;

Session altered.

Elapsed: 00:00:00.00
11:32:00 SQL> insert into t values(2);

1 row created.

Elapsed: 00:00:00.01
11:32:09 SQL> commit ;

Commit complete.

Elapsed: 00:00:00.00
11:32:10 SQL> select * from t;

        ID
----------
         1
         2

Elapsed: 00:00:00.01
11:32:13 SQL> conn / as sysdba
Connected.
11:32:19 SQL>  alter pluggable database S_PDB close;

Pluggable database altered.

Elapsed: 00:00:00.73
11:32:27 SQL> flashback pluggable database S_PDB to restore point PDB_POINT1;

Flashback complete.

Elapsed: 00:00:00.46
11:32:54 SQL> alter pluggable database S_PDB open resetlogs;

Pluggable database altered.

Elapsed: 00:00:04.94
11:33:07 SQL> 

11:33:07 SQL> alter session set container=s_pdb;

Session altered.

Elapsed: 00:00:00.00
11:33:35 SQL>  select * from t;

        ID
----------
         1

Elapsed: 00:00:00.01
11:33:39 SQL>

复制

闪回恢复unplug测试

pdb还原点闪回不能恢复unplug

pdb还原点闪回不能恢复unplug,并且闪回后scn也不一致

11:33:39 SQL>  select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRE NAME                 PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ---------------------------------------- ---------------------------------------- --- -------------------- --- --- ---------------- --- ----------
  23725717                    10 YES    209715200 08-FEB-23 11.31.51.000000000 AM                                                   YES PDB_POINT1           YES NO                 0 NO           3


11:37:37 SQL> alter pluggable database S_PDB close immediate ;

Pluggable database altered.

11:38:13 SQL>  alter pluggable database S_PDB  unplug into '/home/oracle/S_PDB.xml';

Pluggable database altered.


Elapsed: 00:00:08.75
11:38:30 SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 S_PDB                          MOUNTED
11:39:21 SQL> select pdb_name, status from cdb_pdbs ;                       

PDB_NAME            STATUS
------------------- ----------
PDB$SEED            NORMAL
S_PDB               UNPLUGGED

Elapsed: 00:00:00.03
11:39:33 SQL>  flashback pluggable database S_PDB to restore point PDB_POINT1;

Flashback complete.

Elapsed: 00:00:01.02
11:39:50 SQL> select pdb_name, status from cdb_pdbs ;     

PDB_NAME            STATUS
------------------- ----------
PDB$SEED            NORMAL
S_PDB               UNPLUGGED

Elapsed: 00:00:00.00
11:39:55 SQL> alter pluggable database S_PDB open;
alter pluggable database S_PDB open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

11:43:05 SQL> create pluggable database S_PDB using '/home/oracle/S_PDB.xml' NOCOPY TEMPFILE REUSE;
create pluggable database S_PDB using '/home/oracle/S_PDB.xml' NOCOPY TEMPFILE REUSE
*
ERROR at line 1:
ORA-65139: Mismatch between XML metadata file and data file +DATA1/NEWDB/EBE8A723C6CFD8A3E053C81FA8C05B9D/DATAFILE/system.306.1119094259 for value of fcpsb (23726947 in the plug XML file, 23725718 in the data file)


Elapsed: 00:00:00.08
11:43:31 SQL> select oldest_flashback_scn, to_char(oldest_flashback_time,'yyyy-mm-dd HH24:mi:ss') oldest_flashback_time from v$flashback_database_log;

no rows selected


[oracle@rac01 ~]$ grep 23726947 S_PDB.xml
      <fcpsb>23726947</fcpsb>
      <fcpsb>23726947</fcpsb>
      <fcpsb>23726947</fcpsb>
      <fcpsb>23726947</fcpsb>
[oracle@rac01 ~]$ sed 's/23726947/23725718/g' S_PDB.xml
[oracle@rac01 ~]$ grep 23725718 S_PDB.xml      
      <fcpsb>23725718</fcpsb>
      <fcpsb>23725718</fcpsb>
      <fcpsb>23725718</fcpsb>
      <fcpsb>23725718</fcpsb>
[oracle@rac01 ~]$ 

SQL>  create pluggable database S_PDB using '/home/oracle/S_PDB.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> 
SQL> alter pluggable database S_PDB open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 S_PDB                          READ WRITE NO
SQL> 

复制

cdb还原点闪回能恢复unplug

SQL> CREATE RESTORE POINT cdb_point1  GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRE NAME                 PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ---------------------------------------- ---------------------------------------- --- -------------------- --- --- ---------------- --- ----------
  23728134                    10 YES    209715200 08-FEB-23 11.52.13.000000000 AM                                                   YES CDB_POINT1           NO  NO                 0 NO           0

SQL> 

SQL> alter pluggable database S_PDB close immediate ;

Pluggable database altered.

SQL> alter pluggable database S_PDB  unplug into '/home/oracle/S_PDB.xml';

Pluggable database altered.

SQL>  shutdown immediate
 startup mount ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
ORACLE instance started.

Total System Global Area 2147482376 bytes
Fixed Size                 37448456 bytes
Variable Size             788529152 bytes
Database Buffers         1308622848 bytes
Redo Buffers               12881920 bytes
Database mounted.
SQL>

SQL> SQL> flashback database to RESTORE POINT CDB_POINT1 ; 

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 S_PDB                          READ WRITE NO
SQL> 

复制

DG环境闪回

结论：
如果在备库上启用了FLASHBACK，并且备库应用的redo数据超过了新的resetlogs SCN，则将备库闪回到与PRIMARY相同的SCN，并在备库中重新启动mrp进程。
如果备库上没有启用FLASHBACK，并且备库应用的redo数据超过了新的resetlogs SCN，则重新创建备库。

主库执行闪回对备库影响

SQL> flashback database to RESTORE POINT CDB_POINT1 ;  

Flashback complete.


SQL>  alter database open  RESETLOGS;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/gbkarch
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL> alter system switch logfile;

System altered.

SQL> 
复制

备库日志报错，mrp终止

Mon Feb 13 14:53:52 2023
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 4568599558) is orphaned on incarnation#=2
MRP0: Detected orphaned datafiles! 
Recovery will possibly be retried after flashback...
Errors in file /u01/app/oracle/diag/rdbms/gbkdbdg/gbkdb/trace/gbkdb_pr00_6603.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/app/oracle/oradata/gbkdbdg/system01.dbf'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
Mon Feb 13 14:54:13 2023
MRP0: Background Media Recovery process shutdown (gbkdb)
Mon Feb 13 14:54:13 2023
Completed: alter database recover managed standby database using current logfile disconnect from session
复制

主库情况

SQL> select current_scn from v$database ;

CURRENT_SCN
-----------
 4568600341

SQL> select current_scn from v$database ;

CURRENT_SCN
-----------
 4568600354

SQL>  select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE TIME                                     RESTORE_POINT_TIME                       PRESERVED NAME
---------- --------------------- --------- ------------ ---------------------------------------- ---------------------------------------- --------- --------------------
4568599457                     2 YES                  0 13-FEB-23 02.50.56.000000000 PM                                                   YES       CDB_POINT1
4568599514                     2 YES           52428800 13-FEB-23 02.51.46.000000000 PM                                                   YES       CDB_POINT2

复制

备库也需闪回恢复

将备库闪回到与PRIMARY相同的SCN，并在备库中重新启动mrp进程。

SQL> select current_scn from v$database ;

CURRENT_SCN
-----------
 4568599557

SQL> /

CURRENT_SCN
-----------
 4568599557

SQL>  flashback database to scn 4568599457;

Flashback complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


Mon Feb 13 15:02:28 2023
 flashback database to scn 4568599457
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Mon Feb 13 15:02:28 2023
SMON: disabling cache recovery
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Mon Feb 13 15:02:28 2023
Setting recovery target incarnation to 2
 started logmerger process
Parallel Media Recovery started with 2 slaves
Mon Feb 13 15:02:29 2023
Media Recovery Log /u01/app/oracle/gbkarch/1_264_1092608952.dbf
Incomplete Recovery applied until change 4568599458 time 02/13/2023 14:50:56
Flashback Media Recovery Complete
Setting recovery target incarnation to 3
Completed:  flashback database to scn 4568599457
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (gbkdb)
Mon Feb 13 15:02:36 2023
MRP0 started with pid=18, OS id=6698 
MRP0: Background Managed Standby Recovery process started (gbkdb)
 started logmerger process
Mon Feb 13 15:02:41 2023
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/gbkarch/1_1_1128696781.dbf
Media Recovery Log /u01/app/oracle/gbkarch/1_2_1128696781.dbf
Media Recovery Log /u01/app/oracle/gbkarch/1_3_1128696781.dbf
Media Recovery Waiting for thread 1 sequence 4 (in transit)
Recovery of Online Redo Log: Thread 1 Group 14 Seq 4 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/gbkdbdg/sredo14.log
Mon Feb 13 15:02:42 2023
Completed: alter database recover managed standby database using current logfile disconnect from session
Mon Feb 13 15:02:49 2023
RFS[8]: Possible network disconnect with primary database
Mon Feb 13 15:03:24 2023
idle dispatcher 'D000' terminated, pid = (17, 1)
Mon Feb 13 15:04:14 2023
RFS[9]: Assigned to RFS process 6726
RFS[9]: Selected log 14 for thread 1 sequence 4 dbid 845494326 branch 1128696781
Mon Feb 13 15:04:14 2023
Archived Log entry 10 added for thread 1 sequence 4 ID 0x348b5630 dest 1:
Mon Feb 13 15:04:14 2023
Media Recovery Waiting for thread 1 sequence 5
RFS[9]: Selected log 13 for thread 1 sequence 5 dbid 845494326 branch 1128696781
Mon Feb 13 15:04:17 2023
Archived Log entry 11 added for thread 1 sequence 5 ID 0x348b5630 dest 1:
Mon Feb 13 15:04:17 2023
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Assigned to RFS process 6728
RFS[10]: Selected log 13 for thread 1 sequence 6 dbid 845494326 branch 1128696781
Media Recovery Log /u01/app/oracle/gbkarch/1_5_1128696781.dbf
Media Recovery Waiting for thread 1 sequence 6 (in transit)
Recovery of Online Redo Log: Thread 1 Group 13 Seq 6 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/gbkdbdg/sredo13.log
复制

备库执行闪回，不影响后续应用

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> flashback database to scn 4568599514;

Flashback complete.

SQL> 
SQL>  alter database recover managed standby database using current logfile disconnect from session;


Database altered.

flashback database to scn 4568599514
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Mon Feb 13 16:42:19 2023
Setting recovery target incarnation to 2
 started logmerger process
Parallel Media Recovery started with 2 slaves
Mon Feb 13 16:42:19 2023
Media Recovery Log /u01/app/oracle/gbkarch/1_264_1092608952.dbf
Incomplete Recovery applied until change 4568599515 time 02/13/2023 14:51:46
Flashback Media Recovery Complete
Setting recovery target incarnation to 4
Completed: flashback database to scn 4568599514
 alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (gbkdb)
Mon Feb 13 16:42:25 2023
MRP0 started with pid=17, OS id=7747 
MRP0: Background Managed Standby Recovery process started (gbkdb)
 started logmerger process
Mon Feb 13 16:42:31 2023
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/gbkarch/1_1_1128697513.dbf
Media Recovery Log /u01/app/oracle/gbkarch/1_2_1128697513.dbf
Media Recovery Log /u01/app/oracle/gbkarch/1_3_1128697513.dbf
Media Recovery Log /u01/app/oracle/gbkarch/1_4_1128697513.dbf
Recovery of Online Redo Log: Thread 1 Group 11 Seq 5 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/gbkdbdg/sredo11.log
Mon Feb 13 16:42:31 2023
Completed:  alter database recover managed standby database using current logfile disconnect from session
Mon Feb 13 16:42:45 2023
idle dispatcher 'D000' terminated, pid = (23, 10)

复制

备库停止mrp时，闪回主库

备库停止mrp时，闪回主库，无影响

备库情况

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> alter database open ;

Database altered.

SQL>  select current_scn from v$database ;

CURRENT_SCN
-----------
 4568628170

SQL> /

CURRENT_SCN
-----------
 4568628170

复制

主库闪回

 SQL>  select current_scn from v$database ;

CURRENT_SCN
-----------
 4568628220

SQL> 
SQL> /

CURRENT_SCN
-----------
 4568628225

SQL> /

CURRENT_SCN
-----------
 4568628226

SQL> startup mount force 
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             234882088 bytes
Database Buffers          381681664 bytes
Redo Buffers                7507968 bytes
Database mounted.
SQL>  flashback database to scn 4568628220;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> 
复制

备库启动mrp后表现

SQL>  alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database ;

OPEN_MODE
------------------------------------------------------------
READ ONLY WITH APPLY

Mon Feb 13 17:06:28 2023
 alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (gbkdb)
Mon Feb 13 17:06:28 2023
MRP0 started with pid=25, OS id=8121 
MRP0: Background Managed Standby Recovery process started (gbkdb)
 started logmerger process
Mon Feb 13 17:06:33 2023
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Media Recovery start incarnation depth : 1, target inc# : 8, irscn : 4568628221
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/gbkarch/1_3_1128704591.dbf
Media Recovery Log /u01/app/oracle/gbkarch/1_1_1128704778.dbf
Media Recovery Log /u01/app/oracle/gbkarch/1_2_1128704778.dbf
Media Recovery Waiting for thread 1 sequence 3 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 3 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/gbkdbdg/sredo12.log
Completed:  alter database recover managed standby database using current logfile disconnect from session
复制