ORACLE 19.3 (ADG 环境下 Flashback 测试)

一、CDB级(有创建还原点)

主库CDB & PDB 插入测试数据

SQL> show pdbs;
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 MPREPORT           READ WRITE NO

SQL> create table test_c as select 1 as a from dual;

SQL> alter session set container=MPREPORT;

SQL> show pdbs;
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 MPREPORT           READ WRITE NO
SQL> create table test_p as select 11 as a from dual;

备库检查主库插入的数据

SQL> select *from test_c;
     A
----------
     1
SQL> alter session set container=MPREPORT;

SQL> select * from test_p;
     A
----------
    11

主库创建还原点

SQL> show pdbs;
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 MPREPORT           READ WRITE NO
SQL> create restore point cbd_2229;

[oracle@XAG191 ~]$ sql / as sysdba

SQL> set sqlformat ansiconsole

SQL> select name,restore_point_time,replicated from v$restore_point;
NAME                      RESTORE_POINT_TIME   REPLICATED   
GUAR_RESTORE_01_PRIMARY                        YES          
CBD_2229                                       NO

备库检查还原点

[oracle@XAG192 ~]$ sql / as sysdba

SQL> set sqlformat ansiconsole

SQL> select name,restore_point_time,replicated from v$restore_point;
NAME                      RESTORE_POINT_TIME   REPLICATED   
GUAR_RESTORE_01_PRIMARY                        YES          
CBD_2229_PRIMARY                               YES

主库再次插入测试数据

SQL> insert into test_c values(2);
SQL> commit;

SQL> alter session set container=MPREPORT;
SQL> insert into test_p values(12);
SQL> commit;

备库检查主库第2次插入的数据

SQL> select * from test_c;
  A 
  1 
  2 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11 
  12

主库 & 备库切换到 mount 模式下

#主库
SQL> show pdbs;
  CON_ID CON_NAME   OPEN MODE    RESTRICTED   
       2 PDB$SEED   READ ONLY    NO           
       3 MPREPORT   READ WRITE   NO           

SQL> shutdown immediate;

SQL> startup mount;

#备库
SQL> alter session set container=CDB$ROOT;

SQL> show pdbs;
  CON_ID CON_NAME   OPEN MODE   RESTRICTED   
       2 PDB$SEED   READ ONLY   NO           
       3 MPREPORT   READ ONLY   NO           

SQL> shutdown immediate;

SQL> startup mount;

主库闪回到 cbd_2229

SQL> FLASHBACK DATABASE TO RESTORE POINT CBD_2229;

resetlogs方式open 主库

SQL> alter database open resetlogs;

SQL> show pdbs;
  CON_ID CON_NAME   OPEN MODE    RESTRICTED   
       2 PDB$SEED   READ ONLY    NO           
       3 MPREPORT   READ WRITE   NO           

SQL> select * from test_c;
  A 
  1 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11

open 备库

SQL> alter database open;

SQL> show pdbs;
  CON_ID CON_NAME   OPEN MODE   RESTRICTED   
       2 PDB$SEED   READ ONLY   NO           
       3 MPREPORT   READ ONLY   NO           

SQL> select * from test_c;
  A 
  1 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11

检查ADG状态

[oracle@XAG192 ~]$ dgmgrl sys/123456

DGMGRL> show configuration;

Configuration - ADGbroker

  Protection Mode: MaxAvailability
  Members:
  MYCDB191 - Primary database
    MYCDB192 - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 58 seconds ago)

二、CDB级(未创建还原点)

主库CDB & PDB 插入测试数据

[oracle@XAG192 ~]$ sql / as sysdba
SQL> set sqlformat ansiconsole
SQL> show pdbs;
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 MPREPORT           READ WRITE NO

SQL> create table test_c as select 1 as a from dual;

SQL> alter session set container=MPREPORT;

SQL> create table test_p as select 11 as a from dual;

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as now from dual; 
  NOW                   
2019-07-12 22:38:00

备库检查主库插入的数据

SQL> select *from test_c;
     A
----------
     1
SQL> alter session set container=MPREPORT;

SQL> select * from test_p;
     A
----------
    11

主库再次插入测试数据

SQL> alter session set container=CDB$ROOT;
SQL> insert into test_c values(2);
SQL> commit;

SQL> alter session set container=MPREPORT;
SQL> insert into test_p values(12);
SQL> commit;

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as now from dual;
  NOW                   
2019-07-12 22:41:25

备库检查主库第2次插入的数据

SQL> alter session set container=CDB$ROOT;
SQL> select * from test_c;
  A 
  1 
  2 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11 
  12

主库 & 备库切换到 mount 模式下

#主库
SQL> alter session set container=CDB$ROOT;
SQL> shutdown immediate;
SQL> startup mount;

#备库
SQL> alter session set container=CDB$ROOT;
SQL> shutdown immediate;
SQL> startup mount;

主库闪回到第1次插入后，二次插入前

SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2019-07-12 22:38:00','yyyy-mm-dd hh24:mi:ss');

resetlogs方式open 主库

SQL> alter database open resetlogs;

SQL> show pdbs;
  CON_ID CON_NAME   OPEN MODE    RESTRICTED   
       2 PDB$SEED   READ ONLY    NO           
       3 MPREPORT   READ WRITE   NO           

SQL> select * from test_c;
  A 
  1 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11

open 备库

SQL> alter database open;

SQL> show pdbs;
  CON_ID CON_NAME   OPEN MODE   RESTRICTED   
       2 PDB$SEED   READ ONLY   NO           
       3 MPREPORT   READ ONLY   NO           

SQL> select * from test_c;
  A 
  1 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11

检查ADG状态

[oracle@XAG192 ~]$ dgmgrl sys/123456

DGMGRL> show configuration;

Configuration - ADGbroker

  Protection Mode: MaxAvailability
  Members:
  MYCDB191 - Primary database
    MYCDB192 - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 58 seconds ago)

三、PDB级

主库插入数据

SQL> alter session set container=CDB$ROOT;
SQL> insert into test_c values(2);
SQL> commit;

SQL> alter session set container=MPREPORT;
SQL>  insert into test_p values(12);
SQL> commit;

SQL>  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as now from dual;
  NOW                   
2019-07-12 22:59:23

SQL> create restore point MPREPORT_2319 for pluggable database MPREPORT;

SQL> select name,restore_point_time,replicated from v$restore_point;
NAME                      RESTORE_POINT_TIME   REPLICATED   
GUAR_RESTORE_01_PRIMARY                        YES          
CBD_2229                                       NO           
MPREPORT_2319                                  NO 


SQL> alter session set container=CDB$ROOT;
SQL> insert into test_c values(3);
SQL> commit;

SQL> alter session set container=MPREPORT;
SQL>  insert into test_p values(13);
SQL> commit;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as now from dual;
  NOW                   
2019-07-12 23:00:43

备库检查主库插入的数据

[oracle@XAG192 ~]$ sql / as sysdba

SQL> set sqlformat ansiconsole;
SQL> select * from test_c;
  A 
  1 
  2 
  3 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11 
  12 
  13

主备库的 pdb 切换到 mount模式下

#主库
SQL> alter session set container=CDB$ROOT;     

SQL> alter pluggable database MPREPORT close;

#备库
SQL> alter session set container=CDB$ROOT;

SQL> shutdown immediate;
SQL> startup mount;

主库pdb 还原到闪回点

SQL> FLASHBACK PLUGGABLE DATABASE MPREPORT TO TIMESTAMP to_timestamp('2019-07-12 22:59:23','yyyy-mm-dd hh24:mi:ss');

SQL> FLASHBACK PLUGGABLE DATABASE MPREPORT TO RESTORE POINT MPREPORT_2319;

SQL> alter pluggable database MPREPORT  open resetlogs;

SQL> select * from test_c;
  A 
  1 
  2 
  3 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11 
  12

备库alert日志(可选步骤)

2019-07-12T23:05:45.005392+08:00
(3):Recovery of pluggable database MPREPORT aborted due to pluggable database open resetlog marker.
(3):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 2706533, or timestamp before 07/12/2019 22:59:24, and restart recovery
PR00 (PID:3869): MRP0: Detected orphaned datafiles!
PR00 (PID:3869): Recovery will possibly be retried after pluggable database flashback...
2019-07-12T23:05:45.012061+08:00
Errors in file /u01/app/oracle/diag/rdbms/mycdb192/MYCDB/trace/MYCDB_pr00_3869.trc:
ORA-39874: Pluggable Database MPREPORT recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 2706533.
PR00 (PID:3869): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2707839
stopping change tracking
2019-07-12T23:05:45.502781+08:00
Errors in file /u01/app/oracle/diag/rdbms/mycdb192/MYCDB/trace/MYCDB_pr00_3869.trc:
ORA-39874: Pluggable Database MPREPORT recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 2706533.
2019-07-12T23:06:05.626954+08:00
MRP0 (PID:3859): Recovery coordinator encountered one or more errors during automatic flashback on standby
2019-07-12T23:06:05.627314+08:00
Background Media Recovery process shutdown (MYCDB)

检查备库问题

#(可选步骤)
SQL> alter session set container=MPREPORT;
SQL> select status,INCARNATION_SCN from v$pdb_incarnation;
STATUS  INCARNATION_SCN
------- ---------------
CURRENT        2706533
PARENT         2704069
PARENT         2651349
PARENT         2641819
PARENT         2637629
PARENT         1920977

SQL> alter session set container=CDB$ROOT;

#【此处检查 ADG状态如ok则open 否则 闪回 (12C 则必须要用如下步骤闪回) 】
#闪回到 SCN  2706532=2706533-1
##SQL> flashback pluggable database MPREPORT to SCN 2706532;

SQL> alter database open;

SQL> select * from test_c;
  A 
  1 
  2 
  3 

SQL> alter session set container=MPREPORT;
SQL> select * from test_p;
   A 
  11 
  12

检查 ADG状态

[oracle@XAG192 ~]$ dgmgrl sys/123456

DGMGRL> show configuration;

Configuration - ADGbroker

  Protection Mode: MaxAvailability
  Members:
  MYCDB191 - Primary database
    MYCDB192 - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 45 seconds ago)

ORACLE 19.3 (ADG 环境下 Flashback 测试)

推荐阅读更多精彩内容