Oracle 12c R2新特性: PDB级闪回(Flashback PDB)

后端存储 ANBOB

闪回数据库在10G版本时引入,用于 闪回数据库到指定时间点
,在12C 多租户结构中增加了PDB概念,在恢复时通常为了节约时间和一致性做最小粒度的恢复,在12.1版本如果对CDB中的某个PDB做闪回时需要创建辅助实例和CDB,有些像 表空间的基于时间点的恢复
,在12C r2版本新特性引入了PDB级别的flashback,不再需要辅助实例。

PDB闪回有几个基本的前提条件:

1,enable local undo

2,enable archivelog mode

3,enable flashback database;

QL> startup upgrade

SQL> show parameter db_reco
PARAMETER_NAME                     TYPE        VALUE
---------------------------------- ----------- --------------
db_recovery_file_dest              string
db_recovery_file_dest_size         big integer 0

SQL> show parameter flashback
PARAMETER_NAME                     TYPE        VALUE
---------------------------------- ----------- --------------
db_flashback_retention_target      integer     1440

SQL> alter system set db_recovery_file_dest_size=1G;
System altered.

SQL> alter system set db_recovery_file_dest='/u01/arch_anbob';
System altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database local undo on;
Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence           17

SQL> shutdown immediate;
SQL> startup

Note:

flashback database, local undo ,archivelog这些属性都是CDB级别的, 所以不允许在部分PDB级修改。

Create restore point

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBANBOB                       MOUNTED
         5 PDBTEST2_PROXY                 MOUNTED
         6 PDBWEEJAR                      MOUNTED
		 
SQL> alter pluggable database pdbanbob open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBANBOB                       READ WRITE NO
         5 PDBTEST2_PROXY                 MOUNTED
         6 PDBWEEJAR                      MOUNTED
		 
SQL> alter session set container=pdbanbob;
Session altered.

SQL> select sysdate from dual;
SYSDATE
-------------------
2017-03-21 15:58:13

SQL> create restore point pdbanbob_201703211800;
Restore point created.

Note:

首先创建还原点,如果恢复到之前创建的还原点,前提是保证还原点之后的闪回日志的完整性。还原点可以在CDB或PDB级创建只是PDB作用域不同, 以上是创建在PDB级。 还原点也可以创建为GUARANTEE,作用与undo GUARANTEE属性类似,语法如下

create restore point pdbanbob_201703211800 GUARANTEE FLASHBACK DATABASE;

创建测试数据

SQL> alter session set container=pdbanbob;

SQL> create table anbob.t16(id int, v date);
Table created.

SQL> insert into anbob.t16 values(1,sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from anbob.t16;
        ID V
---------- -------------------
         1 2017-03-21 16:12:12

SQL> insert into anbob.t16 values(1,sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from anbob.t16;
        ID V
---------- -------------------
         1 2017-03-21 16:12:12
         1 2017-03-21 16:13:37

SQL> col name for a30
SQL> select SCN,to_char(time,'mm-dd hh24:mi') time,NAME,CLEAN_PDB_RESTORE_POINT IS_CLEAN,CON_ID from v$restore_point;
       SCN TIME        NAME                           IS_     CON_ID
---------- ----------- ------------------------------ --- ----------
   3469597 03-21 15:59 PDBANBOB_201703211800          NO           3

还原到第一条记录insert后的时间

SQL> alter pluggable database pdbanbob close;
Pluggable database altered.

SQL> FLASHBACK PLUGGABLE DATABASE pdbanbob TO TIMESTAMP to_timestamp('2017-03-21 16:13:01','yyyy-mm-dd hh24:mi:ss');
Flashback complete.

SQL> alter pluggable database pdbanbob open resetlogs;
Pluggable database altered.

SQL>  select * from anbob.t16;
        ID V
---------- -------------------
         1 2017-03-21 16:12:12

还原到还原点(restore point)

SQL> alter pluggable database pdbanbob close;
Pluggable database altered.

SQL> FLASHBACK PLUGGABLE DATABASE pdbanbob TO RESTORE POINT PDBANBOB_201703211800;
Flashback complete.

SQL> alter pluggable database pdbanbob open resetlogs;
Pluggable database altered.

SQL> select * from anbob.t16;
select * from anbob.t16
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

NOTE:

只能还原到还原点和open resetlogs之间的时间,也就是只能向前还原。如果向后还原同样会提示下面的错误

SQL>  alter pluggable database pdbanbob close;

Pluggable database altered.

SQL> FLASHBACK PLUGGABLE DATABASE pdbanbob TO TIMESTAMP to_timestamp('2017-03-21 16:14:01','yyyy-mm-dd hh24:mi:ss');
FLASHBACK PLUGGABLE DATABASE pdbanbob TO TIMESTAMP to_timestamp('2017-03-21 16:14:01','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-39889: Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation.

check db incarnation

SQL> select INCARNATION#,RESETLOGS_TIME from v$database_incarnation;

INCARNATION# RESETLOGS_TIME
------------ -------------------
           1 2016-12-09 20:42:51
           2 2017-01-18 21:27:59

SQL>  select DB_INCARNATION#,PDB_INCARNATION#,INCARNATION_TIME,con_id from v$pdb_incarnation order by 3;

DB_INCARNATION# PDB_INCARNATION# INCARNATION_TIME        CON_ID
--------------- ---------------- ------------------- ----------
              1                0 2016-12-09 20:42:51          1
              1                0 2016-12-09 20:42:51          5
              1                0 2016-12-09 20:42:51          6
              1                0 2016-12-09 20:42:51          2
              2                0 2017-01-18 21:27:59          5
              2                0 2017-01-18 21:27:59          2
              2                0 2017-01-18 21:27:59          1
              2                0 2017-01-18 21:27:59          3
              2                0 2017-01-18 21:27:59          6
              2                2 2017-03-21 15:59:13          3
              2                1 2017-03-21 16:13:06          3

11 rows selected.

Note:

PDB级别的闪回,并没有改变整个数据库的INCARNATION, 从新增加的v$pdb_incarnation视图可以确认刚才的闪回操作只是在PDB(con_id is 3)增加了对应的2条记录。

ANBOB稿源:ANBOB (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Oracle 12c R2新特性: PDB级闪回(Flashback PDB)

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录