#PDB级闪回（Flashback PDB）
#PDB闪回有几个基本的前提条件：
1，enable local undo
2，enable archivelog mode
3，enable flashback database;
#停快速故障轉移功能
DGMGRL> stop observer
DGMGRL> disable fast_start failover;
#主库停止日志传到从库（可选操作）
SQL>alter system set log_archive_dest_state_2=defer scope=both; （可選擇）
[oracle@XAG126 ~]$ dgmgrl sys/123456
Connected to "DG126"
Connected as SYSDG.
DGMGRL> show configuration;
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 3 seconds ago)
[oracle@XAG125 ~]$ sqlplus / as sysdba
SQL> select database_role,flashback_on from v$database;
PRIMARY YES
SQL> show parameter db_recovery_file_dest
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area/ORACLE12CDG
db_recovery_file_dest_size big integer 8016M
SQL> show parameter flashback
db_flashback_retention_target integer 1440
SQL> select flashback_on from v$database;
YES
SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
1. 在 PDB 级别创建 restore points：
连接到特定的 PDB 然后运行 CREATE RESTORE POINT 命令：
SQL> alter session set container=PDB1;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi') from dual;
2018-10-04 13:44
SQL> create restore point pdb1_10041344; #正常还原点 （和如下2选1）
SQL> create restore point pdb1_10041344 guarantee flashback database; #担保还原点
或
连接到 CDB，并使用 CREATE RESTORE POINT 命令的 FOR PLUGGABLE DATABASE 子句：
SQL> alter session set container=CDB$ROOT;
#正常还原点 （和如下2选1）
SQL> create restore point pdb1_10041344 for pluggable database PDB1;
SQL> create restore point pdb1_10041344 for pluggable database PDB1 guarantee flashback database;
#第一次测试输入插入 CDB 及 PDB 都插入测试数据
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi') from dual;
2018-10-04 13:45
#pdb
#CDB
#第二次测试输入插入 CDB 及 PDB 都插入测试数据
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi') from dual;
2018-10-04 13:50
#PDB
#CDB
##開始還原
#还原到第一次（13:45）插入后，第二次（13:50）插入前时间 即13:47
flashback backup在12.2中有rman和SQL两种方式闪回， 当使用shared undo里需要使用rman，前提需要在pdb close immediate后创建clean resotre point, 过程中会自动创建辅助实例CDB和PDB PITR；
使用local undo时,就可以使用SQL命令更佳快速，而且只是pdb 数据文件原位置闪回，并应用undo，在做之前创建任意一种restore point都可以，也不需要辅助实例。
SQL> show pdbs;
3 PDB1 READ WRITE NO
SQL> shutdown immediate; or alter pluggable database PDB1 close;
SQL> show pdbs;
3 PDB1 MOUNTED
SQL> FLASHBACK PLUGGABLE DATABASE PDB1 TO TIMESTAMP to_timestamp('2018-10-04 13:47:00','yyyy-mm-dd hh24:mi:ss');
SQL> alter pluggable database PDB1 open resetlogs;
#備庫日誌如下：
#ADG 狀態如下
DGMGRL> show configuration;
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 37 seconds ago)
DGMGRL> show database 'DG126' statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
* WARNING ORA-16853: apply lag has exceeded specified threshold
* ERROR ORA-16766: Redo Apply is stopped
#主庫狀態如下
DGMGRL> show database 'DG125';
Database - DG125
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
DG
Database Status:
SUCCESS
但是備庫存在錯誤
DGMGRL> show database 'DG126';
Database - DG126
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 7 minutes 28 seconds (computed 0 seconds ago)
Average Apply Rate: 5.00 KByte/s
Real Time Query: OFF
Instance(s):
DG
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
Database Status:
ERROR
#alert log of the standby dataset we can find following errors
---------------------------------------------------------------------
2018-10-04T13:59:55.300500+08:00
(3):Recovery of pluggable database PDB1 aborted due to pluggable database open resetlog marker.
(3):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 2820243, or timestamp before 10/04/2018 13:47:02, and restart recovery
MRP0: Background Media Recovery terminated with error 39874
2018-10-04T13:59:55.301469+08:00
Errors in file /u01/app/oracle/diag/rdbms/dg126/DG/trace/DG_pr00_1448.trc:
ORA-39874: Pluggable Database PDB1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 2820243.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2822396
2018-10-04T13:59:55.425604+08:00
Errors in file /u01/app/oracle/diag/rdbms/dg126/DG/trace/DG_pr00_1448.trc:
ORA-39874: Pluggable Database PDB1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 2820243.
2018-10-04T13:59:55.526502+08:00
MRP0: Background Media Recovery process shutdown (DG)
---------------------------------------------------------------------------------------------
On the primary PDB, we can can query the current INCARNATION_SCN in the v$pdb_incarnation view. And we can remark that the current SCN is the same that the one specified in the standby alert log 2820243
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select status,INCARNATION_SCN from v$pdb_incarnation;
STATUS INCARNATION_SCN
--------------------- ---------------
CURRENT 2820243
PARENT 2756861
PARENT 2756236
PARENT 2741913
PARENT 2741034
PARENT 1408558
#And then as specified in the alert log we have to flashback the standby pdb to a SCN lower than 2820243
First let’s stop the redo apply on the standby
DGMGRL> edit database 'DG126' set state='APPLY-OFF';
#備庫
And then let’s flashback to 2820241( i.e 2820243-2 ) for example
Let’s shutdown the standby container and startup it in a mount state
SQL> select name,open_mode from v$pdbs;
PDB$SEED READ ONLY
PDB1 READ ONLY
PDB8 READ ONLY
SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB8 READ ONLY NO
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback pluggable database PDB1 to SCN 2820241;
#啟用備庫應用
DGMGRL> edit database 'DG126' set state='APPLY-ON';
#檢查ADG狀態
DGMGRL> show configuration
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 4 seconds ago)
#OPEN DB
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB8 READ ONLY NO
#还原到还原点(restore point) pdb1_10041344
#主庫
SQL> show pdbs;
3 PDB1 READ WRITE NO
SQL> shutdown immediate; or alter pluggable database PDB1 close;
SQL> show pdbs;
3 PDB1 MOUNTED
SQL> FLASHBACK PLUGGABLE DATABASE PDB1 TO RESTORE POINT pdb1_10041344;
SQL> alter pluggable database PDB1 open resetlogs;
#檢查CDB數據未見回滾，但是PDB 數據回滾到 pdb1_10041344 點時刻
#ADG 狀態如下
DGMGRL> show configuration;
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 37 seconds ago)
DGMGRL> show database 'DG126' statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
* WARNING ORA-16853: apply lag has exceeded specified threshold
* ERROR ORA-16766: Redo Apply is stopped
#主庫狀態如下
DGMGRL> show database 'DG125';
Database - DG125
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
DG
Database Status:
SUCCESS
但是備庫存在錯誤
DGMGRL> show database 'DG126';
Database - DG126
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 7 minutes 28 seconds (computed 0 seconds ago)
Average Apply Rate: 5.00 KByte/s
Real Time Query: OFF
Instance(s):
DG
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
Database Status:
ERROR
#alert log of the standby dataset we can find following errors
---------------------------------------------------------------------
2018-10-04T14:34:03.978752+08:00
Errors in file /u01/app/oracle/diag/rdbms/dg126/DG/trace/DG_pr00_2701.trc:
ORA-39874: Pluggable Database PDB1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 2820013.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2828721
2018-10-04T14:34:04.096906+08:00
Errors in file /u01/app/oracle/diag/rdbms/dg126/DG/trace/DG_pr00_2701.trc:
ORA-39874: Pluggable Database PDB1 recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 2820013.
2018-10-04T14:34:04.197542+08:00
MRP0: Background Media Recovery process shutdown (DG)
#On the primary PDB, we can can query the current INCARNATION_SCN in the v$pdb_incarnation view. And we can remark that the current SCN is the same that the one specified in the standby alert log 2820013
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> select status,INCARNATION_SCN from v$pdb_incarnation;
STATUS INCARNATION_SCN
--------------------- ---------------
CURRENT 2820013
PARENT 2756861
PARENT 2756236
PARENT 2741913
PARENT 2741034
PARENT 1408558
ORPHAN 2820243
7 rows selected.
#And then as specified in the alert log we have to flashback the standby pdb to a SCN lower than 2820013
First let’s stop the redo apply on the standby
DGMGRL> edit database 'DG126' set state='APPLY-OFF';
#備庫
And then let’s flashback to 2820011( i.e 2820013-2 ) for example
Let’s shutdown the standby container and startup it in a mount state
SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB8 READ ONLY NO
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback pluggable database PDB1 to SCN 2820011;
#啟用備庫應用
DGMGRL> edit database 'DG126' set state='APPLY-ON';
#檢查ADG狀態
DGMGRL> show configuration
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
DG125 - Primary database
DG126 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 4 seconds ago)
#OPEN DB
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB8 READ ONLY NO
#啟動快速故障轉移功能
DGMGRL> enable fast_start failover;
DGMGRL> start observer
参考 http://blog.dbi-services.com/oracle-12-2-dataguard-pdb-flashback-on-the-primary/
