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;

COUNT(*)

    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;

COUNT(*)

     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;

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

  7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
  7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
  7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
  7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
  7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
  7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
  7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
  7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
  7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
  7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
  7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

  7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

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;

COUNT(*)

    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;

COUNT(*)

     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;

COUNT(*)

     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;

COUNT(*)

     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;

COUNT(*)

     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;

 EMPNO ENAME             SAL

  7369 SMITH             800
  7499 ALLEN            1600
  7521 WARD             1250
  7566 JONES            2975
  7654 MARTIN           1250
  7698 BLAKE            2850
  7782 CLARK            2450
  7839 KING             5000
  7844 TURNER           1500
  7900 JAMES             950
  7902 FORD             3000

 EMPNO ENAME             SAL

  7934 MILLER           1300

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;

COUNT(*)

     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;

COUNT(*)

    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;

COUNT(*)

    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;

NAME

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;

 EMPNO ENAME             SAL

  7521 WARD             1250
  7566 JONES            2975
  7654 MARTIN           1250
  7698 BLAKE            2850
  7782 CLARK            2450
  7839 KING             5000
  7844 TURNER           1500
  7900 JAMES             950
  7902 FORD             3000
  7934 MILLER           1300

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;

 EMPNO ENAME

  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7839 KING
  7844 TURNER
  7900 JAMES
  7902 FORD
  7934 MILLER

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;

COUNT(*)

    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');

COUNT(*)

     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');

COUNT(*)

    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;

 EMPNO ENAME

  7369 SMITH

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');

 EMPNO ENAME

  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7839 KING
  7844 TURNER
  7900 JAMES
  7902 FORD

 EMPNO ENAME

  7934 MILLER

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;

 EMPNO ENAME             SAL

  7369 SMITH             800
  7499 ALLEN            1600

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

                                           7369
                                           7499

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

                                           7369
                                           7499

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;

COUNT(*)

    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;

 EMPNO ENAME             SAL

  7369 SMITH             800
  7499 ALLEN            1600

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

                                           7369
                                           7499

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

                                           7369
                                           7499

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;

 EMPNO ENAME             SAL

  7369 SMITH             200
  7499 ALLEN            1600

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;

FLASHBACK_ON

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# NAME                           INC BIG FLA ENC     CON_ID

     1 SYSAUX                         YES NO  YES              1
     0 SYSTEM                         YES NO  YES              1
     2 UNDOTBS1                       YES NO  YES              1
     4 USERS                          YES NO  YES              1
     3 TEMP                           NO  NO  YES              1
     0 SYSTEM                         YES NO  YES              2
     1 SYSAUX                         YES NO  YES              2
     2 UNDOTBS1                       YES NO  YES              2
     3 TEMP                           NO  NO  YES              2
     0 SYSTEM                         YES NO  YES              3
     1 SYSAUX                         YES NO  YES              3

   TS# NAME                           INC BIG FLA ENC     CON_ID

     2 UNDOTBS1                       YES NO  YES              3
     3 TEMP                           NO  NO  YES              3
     5 USERS                          YES NO  YES              3
     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
             sys@newtest[CDB](10.8.5.204-12.2)> 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 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# NAME                           INC BIG FLA ENC     CON_ID

     4 USERS                          YES NO  YES              1
     2 UNDOTBS1                       YES NO  YES              1
     0 SYSTEM                         YES NO  YES              1
     5 ABCE                           YES NO  YES              1
     1 SYSAUX                         YES NO  YES              1
     3 TEMP                           NO  NO  YES              1
     1 SYSAUX                         YES NO  YES              2
     2 UNDOTBS1                       YES NO  YES              2
     3 TEMP                           NO  NO  YES              2
     0 SYSTEM                         YES NO  YES              2
     5 USERS                          YES NO  YES              3

   TS# NAME                           INC BIG FLA ENC     CON_ID

     3 TEMP                           NO  NO  YES              3
     2 UNDOTBS1                       YES NO  YES              3
     0 SYSTEM                         YES NO  YES              3
     1 SYSAUX                         YES NO  YES              3
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     0 SYSTEM                         YES NO  YES              4

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# NAME                           INC BIG FLA ENC     CON_ID

     1 SYSAUX                         YES NO  YES              1
     0 SYSTEM                         YES NO  YES              1
     2 UNDOTBS1                       YES NO  YES              1
     4 USERS                          YES NO  YES              1
     3 TEMP                           NO  NO  YES              1
     0 SYSTEM                         YES NO  YES              2
     1 SYSAUX                         YES NO  YES              2
     2 UNDOTBS1                       YES NO  YES              2
     3 TEMP                           NO  NO  YES              2
     0 SYSTEM                         YES NO  YES              3
     5 USERS                          YES NO  YES              3

   TS# NAME                           INC BIG FLA ENC     CON_ID

     3 TEMP                           NO  NO  YES              3
     2 UNDOTBS1                       YES NO  YES              3
     1 SYSAUX                         YES NO  YES              3
     0 SYSTEM                         YES NO  YES              4
     5 USERS                          YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     6 FDA                            YES NO  YES              4

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

     1            6730083          1
     3            6730083          1
     5            6730083          1
     7            6730083          1
     2            1525489          2
     4            1525489          2
     6            1525489          2
     8            6730083          3
    11            6730083          3
    10            6730083          3
     9            6730083          3
    40            6730083          4
    43            6730083          4
    42            6730083          4
    41            6730083          4
    45            6730083          4

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_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 PDBTEST                        MOUNTED
     4 CLONEPDB_PLUG                  MOUNTED

sys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;

Pluggable database altered.

Elapsed: 00:00:06.46
sys@newtestCDB> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 PDBTEST                        MOUNTED
     4 CLONEPDB_PLUG                  READ WRITE NO

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

     1 UNDOTBS1

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# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

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;

   SCN TIME        NAME                           IS_     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# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

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# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

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# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4

6 rows selected.

Elapsed: 00:00:00.08
看來pdb不能用readonly 來檢查
sys@newtestCDB>  select INCARNATION#,RESETLOGS_TIME from v$database_incarnation;

INCARNATION# RESETLOGS_TIME

       1 2017-03-08 15:57:31
       2 2017-12-13 17:22:26
       3 2018-01-22 10: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

          2                0 2017-12-13 17:22:26          4
          3                0 2018-01-22 10:24:24          4
          3                1 2018-01-22 11:03:02          4

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# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

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# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4

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如何實現閃回技術”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。