Oracle12C如何实现闪回技术
这篇文章将为大家详细讲解有关Oracle12C如何实现闪回技术，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
1.Oracle Flashback Query --闪回查询
ORACLE根据undo信息，利用undo数据，类似一致性读取方法，可以把表置于一个删除前的时间点(或SCN)，从而将数据找回。
Flashback query(闪回查询)前提：
sys@CLONEPDB_PLUGPDB> show parameter undo
NAME TYPE VALUE
temp_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 undo
NAME TYPE VALUE
temp_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 SCN
2018-01-17 14:16:00 5409876
Elapsed: 00:00:00.11
sys@CLONEPDB_PLUGPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@CLONEPDB_PLUGPDB> select count(*) from emp;
12
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 identifier
Elapsed: 00:00:00.02
查询该时间点（或scn）的数据，如下：
scott@CLONEPDB_PLUGPDB> select count(*) from emp;
0
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;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980-12-1700:00:00800207499ALLENSALESMAN76981981-02-2000:00:001600300307521WARDSALESMAN76981981-02-2200:00:001250500307566JONESMANAGER78391981-04-0200:00:002975207654MARTINSALESMAN76981981-09-2800:00:0012501400307698BLAKEMANAGER78391981-05-0100:00:002850307782CLARKMANAGER78391981-06-0900:00:002450107839KINGPRESIDENT1981-11-1700:00:005000107844TURNERSALESMAN76981981-09-0800:00:0015000307900JAMESCLERK76981981-12-0300:00:00950307902FORDANALYST75661981-12-0300:00:00300020EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7934MILLERCLERK77821982-01-2300:00:00130010
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;
12
局限：
| 不能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 selected
Elapsed: 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 TIME
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26:16
scott@clonepdb_plugPDB> create table test as select * from emp where rownum<2;
Table created.
Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;
1
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 TIME
TEST 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;
1
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
TEST 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 TIME
TEST 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;
1
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 TIME
TEST 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 TIME
TEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18:15:30:43
scott@clonepdb_plugPDB> select count(*) from test;
0
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/" rel="nofollow">br/>scott@clonepdb_plug[PDB](10.8.5.204-12.2)><br/按表名闪回，后进先出
按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 SCN
2018-01-18 16:08:56 5535328
Elapsed: 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 exist
Elapsed: 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;
EMPNOENAMESAL
7369SMITH8007499ALLEN16007521WARD12507566JONES29757654MARTIN12507698BLAKE28507782CLARK24507839KING50007844TURNER15007900JAMES9507902FORD3000EMPNOENAMESAL
7934MILLER1300
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 SCN
2018-01-18 16:13:45 5536324
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;
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 SCN
2018-01-18 16:14:57 5536483
Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;
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 SCN
2018-01-18 16:15:14 5536518
Elapsed: 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;
8
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;
12
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;
10
Elapsed: 00:00:00.01
purge表不能闪回
闪回不分先后顺序
需要启动行移动alter table test enable row movement;
system表不能闪回
4.FLASHBACK ARCHIVE 闪回归档
sys@clonepdb_plugPDB> create tablespace fda datafile 'C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.dbf' size 5m;
Tablespace created.
Elapsed: 00:00:00.56
sys@clonepdb_plugPDB> select name from v$datafile;
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\USERS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\FDA01.DBF
Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> create flashback archive fla1 tablespace fda retention 3 year;
Flashback archive created.
Elapsed: 00:00:00.19
sys@clonepdb_plugPDB> grant flashback archive on fla1 to scott;
Grant succeeded.
scott@clonepdb_plugPDB> select * from test;
EMPNOENAMESAL
7521WARD12507566JONES29757654MARTIN12507698BLAKE28507782CLARK24507839KING50007844TURNER15007900JAMES9507902FORD30007934MILLER1300
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;
EMPNOENAME
7521WARD7566JONES7654MARTIN7698BLAKE7782CLARK7839KING7844TURNER7900JAMES7902FORD7934MILLER
10 rows selected.
Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> desc test
Name Null? Type
EMPNO 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 table
Elapsed: 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;
12
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');
0
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');
12
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:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.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;
EMPNOENAME
7369SMITH
Elapsed: 00:00:00.01
sys@newtestCDB> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
CONTROL 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 0
8 rows selected.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
NAME TYPE VALUE
db_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 STATUS
TEST SCOTT ENABLED
Elapsed: 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 STATUS
TEST 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 SCN
2018-01-19 14:16:51 5757544
Elapsed: 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');
EMPNOENAME
7369SMITH7499ALLEN7521WARD7566JONES7654MARTIN7698BLAKE7782CLARK7839KING7844TURNER7900JAMES7902FORDEMPNOENAME
7934MILLER
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 name
Elapsed: 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> select to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME SCN
2018-01-19 14:23:58 5759385
Elapsed: 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 STATUS
TEST_01 SCOTT ENABLED
Elapsed: 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<3;
Table created.
Elapsed: 00:00:00.17
scott@CLONEPDB_PLUGPDB> select * from test;
EMPNOENAMESAL
7369SMITH8007499ALLEN1600
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 EMPNO
73697499
Elapsed: 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 EMPNO
73697499
Elapsed: 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 EMPNO
07001E000D070000 5415641 U 7369
5415641 7369
7499
Elapsed: 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 EMPNO
09000800EE060000 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 EMPNO
01001C005D070000 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;
12
Elapsed: 00:00:00.02
c##scott@newtestCDB> create table test as select empno, ename, sal from emp where rownum<3;
Table created.
Elapsed: 00:00:00.25
c##scott@newtestCDB> select * from test;
EMPNOENAMESAL
7369SMITH8007499ALLEN1600
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 EMPNO
73697499
Elapsed: 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 EMPNO
73697499
Elapsed: 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 EMPNO
080010009B0E0000 5502537 U 7369
5502537 7369
7499
c##scott@newtestCDB> select * from test;
EMPNOENAMESAL
7369SMITH2007499ALLEN1600
6.闪回数据库
flashback log 快照
+
归档日志（或当前日志)
逻辑恢复
a.配置归档方式
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\app\Administrator\virtual\archivelog
Oldest online log sequence 125
Next log sequence to archive 127
Current log sequence 127
b.配置闪回恢复区
sys@newtestCDB> show parameter db_recovery
NAME TYPE VALUE
db_recovery_file_dest string C:\app\Administrator\virtual\F
lashRecovery
db_recovery_file_dest_size big integer 2G
c.配置闪回保留时间
sys@newtestCDB> show parameter db_flashback_retention_target
NAME TYPE VALUE
db_flashback_retention_target integer 1440
1440 单位分钟
d.查询是否启用
sys@newtestCDB> select flashback_on from v$database;
YES
Elapsed: 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;
TS#NAMEINCBIGFLAENCCON_ID
1SYSAUXYESNOYES10SYSTEMYESNOYES12UNDOTBS1YESNOYES14USERSYESNOYES13TEMPNONOYES10SYSTEMYESNOYES21SYSAUXYESNOYES22UNDOTBS1YESNOYES23TEMPNONOYES20SYSTEMYESNOYES31SYSAUXYESNOYES3TS#NAMEINCBIGFLAENCCON_ID
2UNDOTBS1YESNOYES33TEMPNONOYES35USERSYESNOYES30SYSTEMYESNOYES41SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES4sys@newtest[CDB](10.8.5.204-12.2)>selectto_char(sysdate,'yyyy-mm-ddhh34:mi:ss')time,to_char(dbms_flashback.get_system_change_number)scnfromdual;
TIME SCN
2018-01-22 10:07:05 6730082
sys@newtestCDB> create tablespace abce datafile 'C:\app\Administrator\virtual\oradata\newtest\abcd01.dbf' size 50m;
Tablespace created.
Elapsed: 00:00:01.06
sys@newtestCDB> select * from v$tablespace order by con_id;
TS#NAMEINCBIGFLAENCCON_ID
4USERSYESNOYES12UNDOTBS1YESNOYES10SYSTEMYESNOYES15ABCEYESNOYES11SYSAUXYESNOYES13TEMPNONOYES11SYSAUXYESNOYES22UNDOTBS1YESNOYES23TEMPNONOYES20SYSTEMYESNOYES25USERSYESNOYES3TS#NAMEINCBIGFLAENCCON_ID
3TEMPNONOYES32UNDOTBS1YESNOYES30SYSTEMYESNOYES31SYSAUXYESNOYES31SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES40SYSTEMYESNOYES4
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.49
sys@newtestCDB> alter database open read only;
数据库已更改。
已用时间: 00: 00: 08.89
sys@newtestCDB> select * from v$tablespace order by con_id;
TS#NAMEINCBIGFLAENCCON_ID
1SYSAUXYESNOYES10SYSTEMYESNOYES12UNDOTBS1YESNOYES14USERSYESNOYES13TEMPNONOYES10SYSTEMYESNOYES21SYSAUXYESNOYES22UNDOTBS1YESNOYES23TEMPNONOYES20SYSTEMYESNOYES35USERSYESNOYES3TS#NAMEINCBIGFLAENCCON_ID
3TEMPNONOYES32UNDOTBS1YESNOYES31SYSAUXYESNOYES30SYSTEMYESNOYES45USERSYESNOYES43TEMPNONOYES42UNDOTBS1YESNOYES41SYSAUXYESNOYES46FDAYESNOYES4
20 rows selected.
Elapsed: 00:00:00.05
SQL> select file#,checkpoint_change#,con_id from v$datafile order by con_id;
FILE#CHECKPOINT_CHANGE#CON_ID
1673008313673008315673008317673008312152548924152548926152548928673008331167300833106730083396730083340673008344367300834426730083441673008344567300834
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.47
PDB级闪回
PDB闪回有几个基本的前提条件：
a.enable local undo
b.enable archivelog mode
c.enable flashback database;
sys@newtestCDB> show pdbs
CON_IDCON_NAMEOPENMODERESTRICTED
2PDB$SEEDREADONLYNO3PDBTESTMOUNTED4CLONEPDB_PLUGMOUNTED
sys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;
Pluggable database altered.
Elapsed: 00:00:06.46
sys@newtestCDB> show pdbs
CON_IDCON_NAMEOPENMODERESTRICTED
2PDB$SEEDREADONLYNO3PDBTESTMOUNTED4CLONEPDB_PLUGREADWRITENO
sys@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 operation
Elapsed: 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_VALUE
LOCAL_UNDO_ENABLED TRUE
Elapsed: 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_NAME
FILE_NAME
1UNDOTBS1
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01.DBF
sys@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 SCN
2018-01-22 11:02:17 6736984
Elapsed: 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:\app\Administrator\virtual\oradata\newtest\CLONEPDB_PLUG\abcd01.dbf' size 50m;
Tablespace created.
Elapsed: 00:00:01.39
sys@newtestCDB> select * from v$tablespace;
TS#NAMEINCBIGFLAENCCON_ID
0SYSTEMYESNOYES41SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES47ABCEYESNOYES4
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;
SCNTIMENAMEIS_CON_ID
6737077 01-22 11:03 CLONEPDB_PLUG_20180122 NO 4
Elapsed: 00:00:00.01
sys@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;
TS#NAMEINCBIGFLAENCCON_ID
0SYSTEMYESNOYES41SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES47ABCEYESNOYES4
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;
TS#NAMEINCBIGFLAENCCON_ID
0SYSTEMYESNOYES41SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES47ABCEYESNOYES4
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;
TS#NAMEINCBIGFLAENCCON_ID
0SYSTEMYESNOYES41SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES4
6 rows selected.
Elapsed: 00:00:00.08
看来pdb不能用readonly 来检查
sys@newtestCDB> select INCARNATION#,RESETLOGS_TIME from v$database_incarnation;
INCARNATION# RESETLOGS_TIME
12017-03-0815:57:3122017-12-1317:22:2632018-01-2210:24:24
Elapsed: 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_ID
202017-12-1317:22:264302018-01-2210:24:244312018-01-2211:03:024
Elapsed: 00:00:00.04
PDB级别的闪回，并没有改变整个数据库的INCARNATION， 从新增加的v$pdb_incarnation视图可以确认刚才的闪回操作只是在PDB（con_id is 3）增加了对应的2条记录。
C:\app\Administrator\virtual\oradata\newtest\clonepdb_plug>rman target sys/zncg3
008_ZNCG@clonepdb_plug
恢复管理器: Release 12.2.0.1.0 - Production on 星期一 1月 22 12:33:42 2018
Copyright (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;
TS#NAMEINCBIGFLAENCCON_ID
0SYSTEMYESNOYES41SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES47ABCEYESNOYES4
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;
TS#NAMEINCBIGFLAENCCON_ID
0SYSTEMYESNOYES41SYSAUXYESNOYES42UNDOTBS1YESNOYES43TEMPNONOYES45USERSYESNOYES46FDAYESNOYES4
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如何实现闪回技术”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
