Oracle12C如何实现闪回技术

这篇文章将为大家详细讲解有关Oracle12C如何实现闪回技术，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。1.Oracle Flashback Query –闪回查询
ORACLE根据undo信息，利用undo数据，类似一致性读取方法，可以把表置于一个删除前的时间点(或SCN)，从而将数据找回。
Flashback query(闪回查询)前提：
sys@CLONEPDB_PLUGPDB> show parameter undoNAME TYPE VALUEtemp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
其中undo_management = auto，设置自动管理
Undo_retention = n(秒),设置决定undo最多的保存时间，其值越大，就需要越多的undo表空间的支持。修改undo_retention的命令如下：
sys@CLONEPDB_PLUGPDB> alter system set undo_retention = 3600;System altered.Elapsed: 00:00:00.06
sys@CLONEPDB_PLUGPDB> show parameter undoNAME TYPE VALUEtemp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
获取数据删除前的一个时间点或scn，如下：
sys@CLONEPDB_PLUGPDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-17 14:16:00 5409876Elapsed: 00:00:00.11
sys@CLONEPDB_PLUGPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@CLONEPDB_PLUGPDB> select count(*) from emp;Elapsed: 00:00:00.03
scott@CLONEPDB_PLUGPDB> delete from emp;12 rows deleted.Elapsed: 00:00:00.04
scott@CLONEPDB_PLUGPDB> commit;Commit complete.Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;
select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual
*
ERROR at line 1:
ORA-00904: DBMS_FLASHBACK: invalid identifierElapsed: 00:00:00.02
查询该时间点（或scn）的数据，如下：
scott@CLONEPDB_PLUGPDB> select count(*) from emp;Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select from emp as of timestamp to_timestamp(‘2018-01-17 14:16:00’, ‘yyyy-mm-dd hh34:mi:ss’);–或select from emp as of scn 5409876;12 rows selected.Elapsed: 00:00:00.15
恢复
scott@CLONEPDB_PLUGPDB> insert into emp (select * from emp as of scn 5409876);–也可以用时间12 rows created.Elapsed: 00:00:00.04
scott@CLONEPDB_PLUGPDB> commit;Commit complete.Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select count(*) from emp;局限：| 不能Falshback到5天以前的数据。| 闪回查询无法恢复到表结构改变之前，因为闪回查询使用的是当前的数据字典。| 受到undo_retention参数的影响，对于undo_retention之前的数据，Flashback不保证能Flashback成功。| 对drop,truncate等不记录回滚的操作，不能恢复。| 普通用户使用dbms_flashback包，必须通过管理员授权。2.Oracle Flashback Drop Table 闪回Drop掉表
scott@clonepdb_plugPDB> show recyclebin
scott@clonepdb_plugPDB> select * from test;no rows selectedElapsed: 00:00:00.00
scott@clonepdb_plugPDB> drop table test;Table dropped.Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIMETEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> create table test as select * from emp where rownum
Table created.Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> drop table test;Table dropped.Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIMETEST BIN$Kqeo3hZ/RA+w3PdIGKlt6Q==$0 TABLE 2018-01-18:15:27:11
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table test to before drop;Flashback complete.Elapsed: 00:00:00.06
scott@clonepdb_plugPDB> select count(*) from test;Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIMETEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> drop table test;Table dropped.Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIMETEST BIN$WZphkGyLQjqqgTNlaFN6jA==$0 TABLE 2018-01-18:15:29:52
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table “BIN$WZphkGyLQjqqgTNlaFN6jA==$0” to before drop;Flashback complete.Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> select count(*) from test;Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> drop table test;Table dropped.Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIMETEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18:15:30:43
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> flashback table “BIN$lHb2N8coS86p8/1o8xr29A==$0” to before drop;Flashback complete.Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIMETEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18:15:30:43
scott@clonepdb_plugPDB> select count(*) from test;scott@clonepdb_plugPDB> flashback table “BIN$Avlh8rB/Q22J0WciRhx58g==$0” to before drop rename to test_new;Flashback complete.Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> show recyclebinscott@clonepdb_plug[PDB](10.8.5.204-12.2)>
br/>scott@clonepdb_plug[PDB](10.8.5.204-12.2)>
按RECYCLEBIN NAME可以任意恢复
可以重命名
sys表不能闪回
闪回后和drop回收站约束在但乱码（外键约束除外），索引不见了
没用undo，其他的都用了

3.Oracle Flashback Table 闪回表
scott@clonepdb_plugPDB> drop table test purge;Table dropped.Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;Table created.Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> alter table test enable row movement;Table altered.Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-18 16:08:56 5535328Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> drop table test purge
2 ;Table dropped.Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> flashback table test to scn 5535328;
flashback table test to scn 5535328
*
ERROR at line 1:
ORA-00942: table or view does not existElapsed: 00:00:00.01
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;Table created.Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test enable row movement;Table altered.Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> select * from test;12 rows selected.Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-18 16:13:45 5536324Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum
2 rows deleted.Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit
2 ;Commit complete.Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-18 16:14:57 5536483Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum
2 rows deleted.Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;Commit complete.Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-18 16:15:14 5536518Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test;8 rows deleted.Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;Commit complete.Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536518;Flashback complete.Elapsed: 00:00:00.55
scott@clonepdb_plugPDB> select count(*) from test;Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536324;Flashback complete.Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536483;Flashback complete.Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test;Elapsed: 00:00:00.01purge表不能闪回
闪回不分先后顺序
需要启动行移动alter table test enable row movement;
system表不能闪回
4.FLASHBACK ARCHIVE 闪回归档
sys@clonepdb_plugPDB> create tablespace fda datafile ‘C:APPADMINISTRATORVIRTUALORADATANEWTESTCLONEPDB_PLUGfda01.dbf’ size 5m;Tablespace created.Elapsed: 00:00:00.56
sys@clonepdb_plugPDB> select name from v$datafile;C:APPADMINISTRATORVIRTUALORADATANEWTESTCLONEPDB_PLUGSYSTEM01.DBF
C:APPADMINISTRATORVIRTUALORADATANEWTESTCLONEPDB_PLUGSYSAUX01.DBF
C:APPADMINISTRATORVIRTUALORADATANEWTESTCLONEPDB_PLUGUNDOTBS01.DBF
C:APPADMINISTRATORVIRTUALORADATANEWTESTCLONEPDB_PLUGUSERS01.DBF
C:APPADMINISTRATORVIRTUALORADATANEWTESTCLONEPDB_PLUGFDA01.DBFElapsed: 00:00:00.04
sys@clonepdb_plugPDB> create flashback archive fla1 tablespace fda retention 3 year;Flashback archive created.Elapsed: 00:00:00.19sys@clonepdb_plugPDB> grant flashback archive on fla1 to scott;Grant succeeded.
scott@clonepdb_plugPDB> select * from test;10 rows selected.Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test flashback archive fla1;Table altered.Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test drop column SAL;Table altered.Elapsed: 00:00:06.97
scott@clonepdb_plugPDB> select * from test;10 rows selected.Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> desc test
Name Null? TypeEMPNO NUMBER(4)
ENAME VARCHAR2(10)scott@clonepdb_plugPDB> truncate table test;Table truncated.Elapsed: 00:00:08.37
scott@clonepdb_plugPDB> drop table test;
drop table test
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked tableElapsed: 00:00:00.04
scott@clonepdb_plugPDB> insert into test select empno,ename from emp;12 rows created.Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> commit
2 ;Commit complete.Elapsed: 00:00:00.23
scott@clonepdb_plugPDB> select count(*) from test;Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp(‘2018-01-19 07:00:00′,’YYYY-MM-DD HH24:MI:SS’);scott@clonepdb_plugPDB> delete from test;12 rows deleted.Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;Commit complete.Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp(‘2018-01-19 09:40:00′,’YYYY-MM-DD HH24:MI:SS’);scott@clonepdb_plugPDB> insert into test select empno,ename from test as of timestamp to_timestamp(‘2018-01-19 09:40:00′,’YYYY-MM-DD HH24:MI:SS’);12 rows created.Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> commit;Commit complete.Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> update test set empno=7969 where empno=7369;
update test set empno=7969 where empno=7369
*
ERROR at line 1:
ORA-55617: Flashback Archive “FLA1” runs out of space and tracking on “TEST” is suspended
SYS扩空间
SQL> alter database datafile ‘C:APPADMINISTRATORVIRTUALORADATANEWTESTCLONEPDB_PLUGfda01.dbf’ autoextend on next 32M maxsize 2048M;Database altered.
scott@clonepdb_plugPDB> update test set ename=7969 where empno=7369;1 row updated.Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> commit;Commit complete.Elapsed: 00:00:00.14
scott@clonepdb_plugPDB> update test set ENAME =(select ename from test as of timestamp to_timestamp(‘2018-01-19 10:40:00′,’YYYY-MM-DD HH24:MI:SS’) where empno=7369) where empno=7369;1 row updated.Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> commit;Commit complete.Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> select * from test where empno=7369;Elapsed: 00:00:00.01sys@newtestCDB> select * from v$flash_recovery_area_usage;FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_IDCONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 98.54 0 5 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 08 rows selected.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZENAME TYPE VALUEdb_recovery_file_dest_size big integer 300M
sys@newtestCDB> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G;System altered.Elapsed: 00:00:00.06
SQL> ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR; –更改保留时间Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE
2 TIMESTAMP(SYSTIMESTAMP – INTERVAL ‘1’ day);
从闪回数据归档FLA1中清除了一天前的所有历史记录数据。通常会在保留时间到期后的第一天执行自动清除。也可以覆盖此设置以进行临时清除。
Flashback archive altered.
scott@clonepdb_plugPDB> col table_name format A10
scott@clonepdb_plugPDB> col owner_name format A10
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;TABLE_NAME OWNER_NAME STATUSTEST SCOTT ENABLEDElapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test no flashback archive;
alter table test no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL> grant flashback archive administer to scott;Grant succeeded.
scott@clonepdb_plugPDB> alter table test no flashback archive;Table altered.Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;TABLE_NAME OWNER_NAME STATUSTEST SCOTT DISABLED
scott@clonepdb_plugPDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-19 14:16:51 5757544Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> truncate table test;Table truncated.Elapsed: 00:00:08.32
scott@clonepdb_plugPDB> select * from test as of timestamp to_timestamp(‘2018-01-19 14:16:51′,’yyyy-mm-dd hh34:mi:ss’);12 rows selected.Elapsed: 00:00:00.12
scott@clonepdb_plugPDB> insert into table test (select from test as of timestamp to_timestamp(‘2018-01-19 14:16:51′,’yyyy-mm-dd hh34:mi:ss’));
insert into table test (select from test as of timestamp to_timestamp(‘2018-01-19 14:16:51′,’yyyy-mm-dd hh34:mi:ss’))
*
ERROR at line 1:
ORA-00903: invalid table nameElapsed: 00:00:00.01
scott@clonepdb_plugPDB> insert into test (select * from test as of timestamp to_timestamp(‘2018-01-19 14:16:51′,’yyyy-mm-dd hh34:mi:ss’));12 rows created.Elapsed: 00:00:00.06
scott@clonepdb_plugPDB&g免费主机域名t; select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-19 14:23:58 5759385Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test rename to test_01;Table altered.Elapsed: 00:00:08.36
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;TABLE_NAME OWNER_NAME STATUSTEST_01 SCOTT ENABLEDElapsed: 00:00:00.01
scott@clonepdb_plugPDB> drop flashback archive fla1;Flashback archive dropped.Elapsed: 00:00:00.04
truncate 表 表能恢复
flashback archive administer //授予用户创建，修改或删除闪回回档 flashback archive //授予用户对表进行归档。
可以删除列，truncate 表，rename 表名 与11g R1不同
不能drop表
插入不能闪回，删除，更新可以ORA-55617 直接resize不行 建议用autoextend on next 32M maxsize 2048M;5.Oracle Flashback Version Query 闪回版本查询
Flashback Version Query引入了一些数据表“伪列”，可以提供对数据版本的操作和检索。
scott@CLONEPDB_PLUGPDB> create table test as select empno, ename, sal from emp where rownum
Table created.Elapsed: 00:00:00.17
scott@CLONEPDB_PLUGPDB> select * from test;Elapsed: 00:00:00.07
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNOElapsed: 00:00:00.08
scott@CLONEPDB_PLUGPDB> update test set sal=200 where empno=7369;1 row updated.Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNOElapsed: 00:00:00.03
scott@CLONEPDB_PLUGPDB> commit;Commit complete.Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNO07001E000D070000 5415641 U 7369
5415641 7369
7499Elapsed: 00:00:00.02
U表示数据修改后的版本数据。如果删除数据，如下操作：
scott@CLONEPDB_PLUGPDB> delete test where empno=7499;1 row deleted.Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> commit;Commit complete.Elapsed: 00:00:00.00
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNO09000800EE060000 5415674 D 7499
07001E000D070000 5415641 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> delete test;1 rows deleted.scott@clonepdb_plugPDB> commit;scott@clonepdb_plugPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNO01001C005D070000 5415969 D 7369
09000800EE060000 5415674 D 7499
07001E000D070000 5415641 5415969 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW(‘07001E000D070000’)
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant select on flashback_transaction_query to scott;Grant succeeded.Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW(‘07001E000D070000’)
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant execute on dbms_flashback to scott;Grant succeeded.Elapsed: 00:00:00.05
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> exec dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array(‘01001C005D070000’));
BEGIN dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array(‘01001C005D070000’)); END;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “SYS.DBMS_FLASHBACK”, line 37
ORA-06512: at “SYS.DBMS_FLASHBACK”, line 70
ORA-06512: at line 1
也失败
在cdb建c##scott用户
c##scott@newtestCDB> select count() from emp;Elapsed: 00:00:00.02
c##scott@newtestCDB> create table test as select empno, ename, sal from emp where rownum
Table created.Elapsed: 00:00:00.25
c##scott@newtestCDB> select * from test;Elapsed: 00:00:00.05
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNOElapsed: 00:00:00.01
c##scott@newtestCDB> update test set sal=200 where empno=7369;1 row updated.Elapsed: 00:00:00.02
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNOElapsed: 00:00:00.01
c##scott@newtestCDB> commit;Commit complete.Elapsed: 00:00:00.01
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;XID START_SCN ENDSCN O EMPNO080010009B0E0000 5502537 U 7369
5502537 7369
7499
c##scott@newtestCDB> select * from test;6.闪回数据库
flashback log 快照
+
归档日志（或当前日志)

逻辑恢复
a.配置归档方式
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:appAdministratorvirtualarchivelog
Oldest online log sequence 125
Next log sequence to archive 127
Current log sequence 127
b.配置闪回恢复区
sys@newtestCDB> show parameter db_recoveryNAME TYPE VALUEdb_recovery_file_dest string C:appAdministratorvirtualF
lashRecovery
db_recovery_file_dest_size big integer 2G
c.配置闪回保留时间
sys@newtestCDB> show parameter db_flashback_retention_targetNAME TYPE VALUEdb_flashback_retention_target integer 1440
1440 单位分钟
d.查询是否启用
sys@newtestCDB> select flashback_on from v$database;YESElapsed: 00:00:00.02
select from v$process where pname=’RVWR’;
或select from v$bgprocess where name=upper(‘rvwr’);例子：
增加一个表空间，然后闪回
sys@newtestCDB> select * from v$tablespace;TIME SCN2018-01-22 10:07:05 6730082
sys@newtestCDB> create tablespace abce datafile ‘C:appAdministratorvirtualoradatanewtestabcd01.dbf’ size 50m;Tablespace created.Elapsed: 00:00:01.06
sys@newtestCDB> select * from v$tablespace order by con_id;21 rows selected.Elapsed: 00:00:00.05
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup mount
ORACLE 例程已经启动。Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
sys@newtestCDB> flashback database to scn 6730082;闪回完成。已用时间: 00: 00: 13.49sys@newtestCDB> alter database open read only;数据库已更改。已用时间: 00: 00: 08.89sys@newtestCDB> select * from v$tablespace order by con_id;20 rows selected.Elapsed: 00:00:00.05
SQL> select file#,checkpoint_change#,con_id from v$datafile order by con_id;16 rows selected
sys@newtestCDB> startup force mount
ORACLE 例程已经启动。Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
sys@newtestCDB> alter database open resetlogs;数据库已更改。已用时间: 00: 02: 15.47PDB级闪回
PDB闪回有几个基本的前提条件：
a.enable local undo
b.enable archivelog mode
c.enable flashback database;
sys@newtestCDB> show pdbssys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;Pluggable database altered.Elapsed: 00:00:06.46
sys@newtestCDB> show pdbssys@newtestCDB> alter database local undo on;
alter database local undo on
*
ERROR at line 1:
ORA-65192: database must be in UPGRADE mode for this operationElapsed: 00:00:00.03
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup UPGRADE
ORACLE 例程已经启动。Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
数据库已经打开。
sys@newtestCDB> alter database local undo on;数据库已更改。已用时间: 00: 00: 00.34
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup
ORACLE 例程已经启动。Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
数据库已经打开。
sys@newtestCDB> col PROPERTY_NAME for a25;
sys@newtestCDB> col PROPERTY_VALUE for a25;
sys@newtestCDB> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name=’LOCAL_UNDO_ENABLED’;PROPERTY_NAME PROPERTY_VALUELOCAL_UNDO_ENABLED TRUEElapsed: 00:00:00.02
select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME
from cdb_tablespaces a,CDB_DATA_FILES b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS=’UNDO’;
CON_ID TABLESPACE_NAMEC:APPADMINISTRATORVIRTUALORADATANEWTESTUNDOTBS01.DBFsys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;Pluggable database altered.Elapsed: 00:00:12.05
sys@newtestCDB> alter session set container=CLONEPDB_PLUG;Session altered.Elapsed: 00:00:00.12
sys@newtestCDB> select to_char(sysdate, ‘yyyy-mm-dd hh34:mi:ss’) time, to_char(dbms_flashback.get_system_change_number) scn from dual;TIME SCN2018-01-22 11:02:17 6736984Elapsed: 00:00:00.38
sys@newtestCDB> create restore point CLONEPDB_PLUG_20180122 GUARANTEE FLASHBACK DATABASE;Restore point created.Elapsed: 00:00:00.12
sys@newtestCDB> create tablespace abce datafile ‘C:appAdministratorvirtualoradatanewtestCLONEPDB_PLUGabcd01.dbf’ size 50m;Tablespace created.Elapsed: 00:00:01.39

sys@newtestCDB> select * from v$tablespace;7 rows selected.Elapsed: 00:00:00.05
sys@newtestCDB> col name for a30
sys@newtestCDB> select SCN,to_char(time,’mm-dd hh34:mi’) time,NAME,CLEAN_PDB_RESTORE_POINT IS_CLEAN,CON_ID from v$restore_point;6737077 01-22 11:03 CLONEPDB_PLUG_20180122 NO 4Elapsed: 00:00:00.01sys@newtestCDB> flashback pluggable database clonepdb_plug to scn 6736984;Flashback complete.Elapsed: 00:00:03.18
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;Pluggable database altered.Elapsed: 00:00:04.06
sys@newtestCDB> select * from v$tablespace;7 rows selected.Elapsed: 00:00:00.07
sys@newtestCDB> alter pluggable database CLONEPDB_PLUG close;Pluggable database altered.Elapsed: 00:00:00.16
sys@newtestCDB> flashback pluggable database clonepdb_plug TO RESTORE POINT CLONEPDB_PLUG_20180122;Flashback complete.Elapsed: 00:00:01.41
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;Pluggable database altered.Elapsed: 00:00:04.02
sys@newtestCDB> select * from v$tablespace;7 rows selected.Elapsed: 00:00:00.06
sys@newtestCDB> alter pluggable database clonepdb_plug close;Pluggable database altered.Elapsed: 00:00:00.18
sys@newtestCDB> alter pluggable database clonepdb_plug open resetlogs;Pluggable database altered.Elapsed: 00:00:11.50
sys@newtestCDB> select * from v$tablespace;6 rows selected.Elapsed: 00:00:00.08
看来pdb不能用readonly 来检查
sys@newtestCDB> select INCARNATION#,RESETLOGS_TIME from v$database_incarnation;INCARNATION# RESETLOGS_TIMEElapsed: 00:00:00.06
sys@newtestCDB> select DB_INCARNATION#,PDB_INCARNATION#,INCARNATION_TIME,con_id from v$pdb_incarnation order by 3;DB_INCARNATION# PDB_INCARNATION# INCARNATION_TIME CON_IDElapsed: 00:00:00.04
PDB级别的闪回，并没有改变整个数据库的INCARNATION， 从新增加的v$pdb_incarnation视图可以确认刚才的闪回操作只是在PDB（con_id is 3）增加了对应的2条记录。

C:appAdministratorvirtualoradatanewtestclonepdb_plug>rman target sys/zncg3
008_ZNCG@clonepdb_plug恢复管理器: Release 12.2.0.1.0 – Production on 星期一 1月 22 12:33:42 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.已连接到目标数据库: NEWTEST:CLONEPDB_PLUG (DBID=50957894, 未打开)RMAN> flashback pluggable database CLONEPDB_PLUG to scn 6749827;从位于 22-1月 -18 的 flashback 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=28 设备类型=DISK正在开始介质的恢复
介质恢复完成, 用时: 00:00:03在 22-1月 -18 完成了 flashback
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;Pluggable database altered.Elapsed: 00:00:04.33
sys@newtestCDB> select * from v$tablespace;7 rows selected.Elapsed: 00:00:00.06
sys@newtestCDB> alter pluggable database clonepdb_plug close;Pluggable database altered.
sys@newtestCDB> alter pluggable database clonepdb_plug open resetlogs;Pluggable database altered.Elapsed: 00:00:10.53
sys@newtestCDB> select * from v$tablespace;6 rows selected.Elapsed: 00:00:00.09
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都可以，也不需要辅助实例。关于“Oracle12C如何实现闪回技术”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。

相关推荐: citus中DDL操作规范有哪些

本篇内容主要讲解“citus中DDL操作规范有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“citus中DDL操作规范有哪些”吧!citus是PG的一个插件，插件主要针对普通SQL（非UTILITY）加HOOK…