开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> flashback全库和pdb -> 正文阅读

[大数据]flashback全库和pdb

1、flashback 全库

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------
YES
?
SQL> show parameter flashback

NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE ? ? ? ? ? ? ? ? ? VALUE
------------------------------------ ---------------------- ------------------------------
db_flashback_retention_target ? ? ? ?integer ? ? ? ? ? ? ? ?1440

（1）flashback 还原点

SQL> create table test as select * from dba_objects;

Table created.

SQL> create restore point rp guarantee flashback database;

Restore point created.

SQL> drop table test purge;

Table dropped.

SQL> shutdown immediate

SQL> startup mount

SQL> flashback database to restore point rp;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from test;

? COUNT(*)
----------
? ? ?72385
?? ??
SQL> shutdown immediate?? ??

SQL> startup mount?? ??

SQL> alter database open resetlogs;

SQL> select count(*) from test;

? COUNT(*)
----------
? ? ?72385

（2）flashback scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
? ? 2253002

SQL> drop table test purge;

SQL> shutdown immediate

SQL> startup mount?? ?

SQL> flashback database to scn 2253002;

SQL> alter database open resetlogs;

SQL> select count(*) from test;

? COUNT(*)
----------
? ? ?72385

（3）flashback timestamp

SQL> select sysdate from dual;

SYSDATE
-------------------
2022-10-24 14:32:32

SQL> drop table test purge;
SQL> startup mount

SQL> flashback ?database ?to timestamp to_timestamp('2022-10-24 14:32:32','yyyy-mm-dd hh24:mi:ss');

SQL> alter database open resetlogs;

SQL> select count(*) from test;

? COUNT(*)
----------
? ? ?72385

2、flashback pdb

SQL> show pdbs

? ? CON_ID CON_NAME ? ? ? ? ? ? ? ? ? ? ? OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
? ? ? ? ?2 PDB$SEED ? ? ? ? ? ? ? ? ? ? ? READ ONLY ?NO
? ? ? ? ?3 PDB1 ? ? ? ? ? ? ? ? ? ? ? ? ? READ WRITE NO
? ? ? ? ?4 PDB2 ? ? ? ? ? ? ? ? ? ? ? ? ? READ WRITE NO
? ? ? ? ?5 PDB3 ? ? ? ? ? ? ? ? ? ? ? ? ? READ WRITE NO
SQL> alter session set container=pdb2;

Session altered.?? ?

（1）flashback cdb 还原点

SQL> create table pdb2 as select * from dba_objects;

Table created.?? ??

SQL> select count(*) from dba_objects;

? COUNT(*)
----------
? ? ?72357
?? ??
SQL> shutdown immediate
Pluggable Database closed.

SQL> show pdbs

? ? CON_ID CON_NAME ? ? ? ? ? ? ? ? ? ? ? OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
? ? ? ? ?4 PDB2 ? ? ? ? ? ? ? ? ? ? ? ? ? MOUNTED
?? ??? ??
SQL> flashback pluggable database pdb2 to restore point rp;

Flashback complete.?? ??? ??
?? ??? ??
SQL> alter pluggable database pdb2 open resetlogs;

SQL> select count(*) from pdb2;
select count(*) from pdb2
? ? ? ? ? ? ? ? ? ? ?*
ERROR at line 1:
ORA-00942: table or view does not exist

（2）flashback pdb 还原点

SQL> create table pdb2 as select * from dba_objects;

Table created.

SQL> select count(*) from dba_objects;

? COUNT(*)
----------
? ? ?72357
?? ??
SQL> create restore point pdb;

Restore point created.

SQL> drop table pdb2 purge;

SQL> shutdown immediate

SQL> flashback pluggable database pdb2 to restore point pdb;

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 16 needs media recovery
ORA-01110: data file 16: '/u01/app/oracle/oradata/ORCL/pdb2/users01.dbf'

SQL> alter pluggable database pdb2 open resetlogs;

SQL> select count(*) from pdb2;

? COUNT(*)
----------
? ? ?72356