溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細(xì)講解有關(guān)Oracle12C如何實現(xiàn)閃回技術(shù)，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
1.Oracle Flashback Query --閃回查詢
ORACLE根據(jù)undo信息，利用undo數(shù)據(jù)，類似一致性讀取方法，可以把表置于一個刪除前的時間點(或SCN)，從而將數(shù)據(jù)找回。
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，設(shè)置自動管理
Undo_retention = n(秒),設(shè)置決定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
獲取數(shù)據(jù)刪除前的一個時間點或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）的數(shù)據(jù)，如下：
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;
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
恢復(fù)
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天以前的數(shù)據(jù)。
| 閃回查詢無法恢復(fù)到表結(jié)構(gòu)改變之前，因為閃回查詢使用的是當(dāng)前的數(shù)據(jù)字典。
| 受到undo_retention參數(shù)的影響，對于undo_retention之前的數(shù)據(jù)，F(xiàn)lashback不保證能Flashback成功。
| 對drop,truncate等不記錄回滾的操作，不能恢復(fù)。
| 普通用戶使用dbms_flashback包，必須通過管理員授權(quán)。
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/按表名閃回，后進(jìn)先出
按RECYCLEBIN NAME可以任意恢復(fù)
可以重命名
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;
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;
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;
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擴(kuò)空間
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);
從閃回數(shù)據(jù)歸檔FLA1中清除了一天前的所有歷史記錄數(shù)據(jù)。通常會在保留時間到期后的第一天執(zhí)行自動清除。也可以覆蓋此設(shè)置以進(jìn)行臨時清除。
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 表 表能恢復(fù)
flashback archive administer //授予用戶創(chuàng)建，修改或刪除閃回回檔 flashback archive //授予用戶對表進(jìn)行歸檔。
可以刪除列，truncate 表，rename 表名 與11g R1不同
不能drop表
插入不能閃回，刪除，更新可以
ORA-55617 直接resize不行 建議用autoextend on next 32M maxsize 2048M;
5.Oracle Flashback Version Query 閃回版本查詢
Flashback Version Query引入了一些數(shù)據(jù)表“偽列”，可以提供對數(shù)據(jù)版本的操作和檢索。
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表示數(shù)據(jù)修改后的版本數(shù)據(jù)。如果刪除數(shù)據(jù)，如下操作：
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;
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.閃回數(shù)據(jù)庫
flashback log 快照
+
歸檔日志（或當(dāng)前日志)
邏輯恢復(fù)
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.配置閃回恢復(fù)區(qū)
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# 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
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
sys@newtestCDB> startup mount
ORACLE 例程已經(jīng)啟動。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
數(shù)據(jù)庫裝載完畢。
sys@newtestCDB> flashback database to scn 6730082;
閃回完成。
已用時間: 00: 00: 13.49
sys@newtestCDB> alter database open read only;
數(shù)據(jù)庫已更改。
已用時間: 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 例程已經(jīng)啟動。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
數(shù)據(jù)庫裝載完畢。
sys@newtestCDB> alter database open resetlogs;
數(shù)據(jù)庫已更改。
已用時間: 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
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
sys@newtestCDB> startup UPGRADE
ORACLE 例程已經(jīng)啟動。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
數(shù)據(jù)庫裝載完畢。
數(shù)據(jù)庫已經(jīng)打開。
sys@newtestCDB> alter database local undo on;
數(shù)據(jù)庫已更改。
已用時間: 00: 00: 00.34
sys@newtestCDB> shutdown immediate
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
sys@newtestCDB> startup
ORACLE 例程已經(jīng)啟動。
Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
數(shù)據(jù)庫裝載完畢。
數(shù)據(jù)庫已經(jīng)打開。
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
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級別的閃回，并沒有改變整個數(shù)據(jù)庫的INCARNATION， 從新增加的v$pdb_incarnation視圖可以確認(rèn)剛才的閃回操作只是在PDB（con_id is 3）增加了對應(yīng)的2條記錄。
C:\app\Administrator\virtual\oradata\newtest\clonepdb_plug>rman target sys/zncg3
008_ZNCG@clonepdb_plug
恢復(fù)管理器: 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.
已連接到目標(biāo)數(shù)據(jù)庫: NEWTEST:CLONEPDB_PLUG (DBID=50957894, 未打開)
RMAN> flashback pluggable database CLONEPDB_PLUG to scn 6749827;
從位于 22-1月 -18 的 flashback 開始
使用目標(biāo)數(shù)據(jù)庫控制文件替代恢復(fù)目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=28 設(shè)備類型=DISK
正在開始介質(zhì)的恢復(fù)
介質(zhì)恢復(fù)完成, 用時: 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兩種方式閃回， 當(dāng)使用shared undo里需要使用rman，前提需要在pdb close immediate后創(chuàng)建clean resotre point, 過程中會自動創(chuàng)建輔助實例CDB和PDB PITR； 使用local undo時,就可以使用SQL命令更佳快速，而且只是pdb 數(shù)據(jù)文件原位置閃回，并應(yīng)用undo，在做之前創(chuàng)建任意一種restore point都可以，也不需要輔助實例。
關(guān)于“Oracle12C如何實現(xiàn)閃回技術(shù)”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
