Oracle 閃回技術

一、配置閃回數據庫

1、數據庫處于歸檔日志模式

SQL> select log_mode from v$database;

LOG_MODE                                                                        

------------                                                                    

ARCHIVELOG  

2、創建閃回恢復區

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> alter system set db_recovery_file_dest='D:\oracle\product\11.2.0\dbhome_1\RDBMS';

System altered.

3、設置閃回保留目標時間

SQL> alter system set db_flashback_retention_target=240;

System altered.

--DB_FLASHBACK_RETENTION_TARGET參數控制保留時間，單位是分鐘，默認值是1天。閃回日志空間以循環的方式重用，更新的數據將覆蓋舊的數據。(本例為保留4小時)

4、關閉數據庫并啟動到MOUNT狀態

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3373858816 bytes                                       

Fixed Size                  2180424 bytes                                       

Variable Size            1845496504 bytes                                       

Database Buffers         1509949440 bytes                                       

Redo Buffers               16232448 bytes                                       

Database mounted.

5、啟用閃回日志記錄并打開數據庫

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

6、查看是否啟用閃回日志記錄

SQL> select flashback_on from v$database;

FLASHBACK_ON                                                                    

------------------                                                              

YES                                                                             

二、使用sqlplus閃回數據庫 

1、創建一個臨時表

SQL> create table t as select * from dba_objects;

Table created.

2、查詢當前系統時間表

SQL> select to_char(sysdate,'yyyy-mm-dd hh34:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY                                                             

-------------------                                                             

2016-03-15 11:34:21                                                             

3、對t表做一些DML操作

SQL> delete from t;

72464 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into t select * from dba_objects where rownum<=100;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)                                                                      

----------                                                                      

       100                                                                      

4、把數據庫閃回到步驟2查詢出的時間

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3373858816 bytes                                       

Fixed Size                  2180424 bytes                                       

Variable Size            1845496504 bytes                                       

Database Buffers         1509949440 bytes                                       

Redo Buffers               16232448 bytes                                       

Database mounted.

SQL> flashback database to timestamp to_timestamp('2016-03-15 11:34:21','yyyy-mm-dd hh34:mi:ss');--可以接受時間戳或系統變更號(SCN)參數，不接受日期或日志世界的序列號

Flashback complete.

5、以只讀方式打開數據庫查詢是否閃回成功 --注意使用read only方式打開數據庫，驗證閃回情況。

SQL> alter database open read only;

Database altered.

SQL> select count(*) from t;

  COUNT(*)                                                                      

----------                                                                      

     72464                                                                      

6、閃回成功后，以resetlogs方式打開數據庫

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3373858816 bytes                                       

Fixed Size                  2180424 bytes                                       

Variable Size            1845496504 bytes                                       

Database Buffers         1509949440 bytes                                       

Redo Buffers               16232448 bytes                                       

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from t;

  COUNT(*)                                                                      

----------                                                                      

     72464                                                                      

數據庫閃回成功

注：如果flashback閃回的數據庫時間太早，可以使用RECOVER DATABASE UNTILE TIME times恢復到后邊的時間點。

--也可以使用RMAN進行閃回

RMAN>flashback database to time = to_date('2016-03-15 11:34:21','yyyy-mm-dd hh34:mi:ss');

RMAN>flashback database to scn = 2728665;

RMAN>flash backup to sequence = 2123 thread = 1;

三、限制生成的閃回數據量

1、關閉表空間閃回屬性

ALTER TABLESPACE tablespace_name FLASHBACK OFF;

2、開啟表空間閃回屬性

ALTER TABLESPACE tablespace_name FLASHBACK ON;--只能在mount狀態下執行

3、查看表空間的閃回屬性

select name,flashback_on from v$tablespace;

四、設置sqlplus提示符

SQL> set sqlprompt "_user'@'_connect_identifier>"

SYS@ocp>

--為了對所有的sqlplus會話自動設置sqlprompt。將上面的命令放置在ORACLE_HOME/sqlplus/admin目錄中的glogin.sql文件中

五、閃回刪除

1、創建一個練習用戶

SYS@ocp>create user dropper identified by dropper;

User created.

SYS@ocp>grant create session,resource to dropper;

Grant succeeded.

SYS@ocp>connect dropper/dropper;

Connected.

2、創建一個帶有索引和約束的表，并插入一行

DROPPER@ocp>create table names(name varchar2(10));

Table created.

DROPPER@ocp>create index name_idx on names(name);

Index created.

DROPPER@ocp>alter table names add constraint name_u unique (name);

Table altered.

DROPPER@ocp>insert into names values('John');

1 row created.

DROPPER@ocp>commit;

Commit complete.

3、確認模式的內容

DROPPER@ocp>select object_name,object_type from user_objects;

OBJECT_NAME  OBJECT_TYPE                                                                    

-------------------------------------

      NAME_IDX      INDEX                                                        

  NAMES         TABLE                                                  

DROPPER@ocp>select constraint_name,constraint_type,table_name from user_constraints;

CONSTRAINT_NAME                C TABLE_NAME                                     

------------------------------ - ------------------------------                 

NAME_U                         U NAMES                                          

4、刪除該表

DROPPER@ocp>drop table names;

Table dropped.

5、查詢回收站查看原始名稱到回收站名稱的映射

DROPPER@ocp>select object_name,original_name,type from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME        TYPE                                    

------------------------------ ------------------   -------------------------               

 BIN$q+6VgWdBRGOykqOJfCyZNg==$0 NAME_IDX             INDEX                           

 BIN$u1TeIIlLS3isIPDvpSTblQ==$0 NAMES                TABLE                        

 --注：視圖并沒有顯示約束                                                                             

6、可以使用回收站的對象名進行查詢，但不可做DML語句

DROPPER@ocp>select * from "BIN$u1TeIIlLS3isIPDvpSTblQ==$0";

NAME                                                                            

----------                                                                      

John                                                                            

DROPPER@ocp>insert into "BIN$u1TeIIlLS3isIPDvpSTblQ==$0" values('Root');

insert into "BIN$u1TeIIlLS3isIPDvpSTblQ==$0" values('Root')

            *

ERROR at line 1:

ORA-38301: can not perform DDL/DML over objects in Recycle Bin 

7、使用FLASHBACK drop恢復表

DROPPER@ocp>flashback table names to before drop;

Flashback complete.

8、查詢模式中的內容

DROPPER@ocp>select * from names;

NAME                                                                            

----------                                                                      

John                                                                            

DROPPER@ocp>select object_name,original_name,type from user_recyclebin;

no rows selected

DROPPER@ocp>select object_name,object_type from user_objects;

OBJECT_NAME                      OBJECT_TYPE        

-------------------------------------------------

BIN$q+6VgWdBRGOykqOJfCyZNg==$0   INDEX                                                          

NAMES                            TABLE                                 

DROPPER@ocp>select constraint_name,constraint_type,table_name from user_constraints;

CONSTRAINT_NAME                C TABLE_NAME                                     

------------------------------ - ------------------------------                 

BIN$cLAR1tu9Toi3u5qKdAbvIw==$0 U NAMES                                          

9、將索引和約束重命名回原先的名稱

DROPPER@ocp>alter index "BIN$q+6VgWdBRGOykqOJfCyZNg==$0" rename to name_idx;

Index altered.

DROPPER@ocp>alter table names rename constraint "BIN$cLAR1tu9Toi3u5qKdAbvIw==$0" to name_u;

Table altered.

DROPPER@ocp>select object_name,object_type from user_objects;

OBJECT_NAME    OBJECT_TYPE         

----------------------------------                                                             

NAME_IDX         INDEX                                                                                                                                               

NAMES            TABLE                                                                                      

DROPPER@ocp>select constraint_name,constraint_type,table_name from user_constraints;

CONSTRAINT_NAME                C TABLE_NAME                                     

------------------------------ - ------------------------------                 

NAME_U                         U NAMES                                          

10、使用SYS用戶刪除DROPPER模式

SYS@ocp>drop user dropper cascade;

User dropped.

11、查詢DBA_RECYCLEBIN視圖來證實確實刪除了用戶DROPPER擁有的所有對象。

SYS@ocp>select count(*) from dba_recyclebin where owner='DROPPER';

  COUNT(*)                                                                      

----------                                                                      

         0                                                                      

注：如果回收站中兩兩個表的原始名稱相同，默認情況下，Flashback Drop命令總是恢復最新版本的表，但如果不是想要的版本，可以指定希望恢復的版本的回收站名稱，而不是原先的名稱。

SQL>flashback table "BIN$q+6VgWdBRGOykqOJfCyZNg==$0" to before drop;

六、管理回收站

SQL>show recyclebin;

user_recyclebin

dba_recyclebin

注：Flashback Drop不適用于存儲在SYSTEM表空間中的表，直接將它們刪掉并清除了

永久清除刪掉的對象

DROP TABLE table_name PURGE;--刪除表并不將它轉移到回收站

PURGE TABLE table_name;--從回收站中清除表。如果存在多個具有相同原始名稱的對象，清除時間最久的對象。也可以通過指定回收站名稱來避免這種混淆。

PURGE INDEX index_name;--從回收站中消除索引。同樣可以指定原始名稱或回收站名稱。

PURGE TABLESPACE tablespace_name;--從表空間中清除所有刪除的對象。

PURGE TABLESPACE tablespace_name USER user_name;--從表空間中清除屬于一個用戶的所有刪除的對象。

PURGE USER_RECYCLEBIN;--清除用戶刪除文件的所有對象。

PURGE DBA_RECYCLEBIN;--清除所有刪除的對象，需要dba權限。

七、閃回查詢

--所有形式的閃回查詢依賴撤銷數據來重構它在過去某個時間點的數據。

1、基本的閃回查詢

1.1創建測試表并插入測試數據

USER1@mydb>create table regions (region_id number,region_name varchar2(20));

Table created.

USER1@mydb>insert into regions values(1,'Europe');

1 row created.

USER1@mydb>insert into regions values(2,'Americas');

1 row created.

USER1@mydb>insert into regions values(3,'Asia');

1 row created.

USER1@mydb>insert into regions values(4,'Middle East');

1 row created.

USER1@mydb>commit;

Commit complete.

1.2查詢系統當前時間，刪除部分數據并確認

USER1@mydb>select sysdate from dual;

SYSDATE                                                                         

-------------------                                                             

2016-03-15 19:08:16                                                             

USER1@mydb>delete from regions where region_name like 'A%';

2 rows deleted.

USER1@mydb>commit;

Commit complete.

USER1@mydb>select * from regions;

 REGION_ID REGION_NAME                                                          

---------- --------------------                                                 

         1 Europe                                                               

         4 Middle East                                                          

1.3進行閃回查詢

USER1@mydb>select * from regions as of timestamp to_timestamp('2016-03-15 19:08:16','yyyy-mm-dd hh34:mi:ss');

 REGION_ID REGION_NAME                                                          

---------- --------------------                                                 

         1 Europe                                                               

         2 Americas                                                             

         3 Asia                                                                 

         4 Middle East                                                          

1.4查詢閃回時間點到現在的差異

USER1@mydb>select * from regions as of timestamp to_timestamp('2016-03-15 19:08:16','yyyy-mm-dd hh34:mi:ss') minus select * from regions;

 REGION_ID REGION_NAME                                                          

---------- --------------------                                                 

         2 Americas                                                             

         3 Asia                                                                 

可以使用DBMS_FLASHBACK程序包將整個會話回退到過去某個時間，對其他會話沒有影響。處于閃回模式中不支持DMS語句。

USER1@mydb>execute dbms_flashback.enable_at_time(to_timestamp('2016-03-15 19:08:16','yyyy-mm-dd hh34:mi:ss'));

PL/SQL procedure successfully completed.

USER1@mydb>select * from regions;

 REGION_ID REGION_NAME

---------- --------------------

         1 Europe

         2 Americas

         3 Asia

         4 Middle East

USER1@mydb>execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

select * from table_name as of timestamp(systimestamp-interval '30' minute);

select * from table_name as of timestamp(systimestamp-interval '1' hour);

2、閃回表查詢  --啟用表閃回的第一步是在表上支持行移動。

2.1創建測試表插入測試數據

HR@mydb>create table dept (dept_id number,dept_name varchar2(20));

Table created.

HR@mydb>alter table dept add constraint pk_dept primary key (dept_id);

Table altered.

HR@mydb>create table emp (emp_id number,name varchar2(20),dept_id number);

Table created.

HR@mydb>alter table emp add constraint fk_emp foreign key (dept_id) references dept(dept_id);

Table altered.

HR@mydb>insert into dept values(1,'SUPPORT');

1 row created.

HR@mydb>select * from dept;

   DEPT_ID DEPT_NAME                                                            

---------- --------------------                                                 

         1 SUPPORT                                                              

HR@mydb>commit;

Commit complete.

HR@mydb>insert into emp values(101,'John',1);

1 row created.

HR@mydb>commit;

Commit complete.

HR@mydb>select * from emp;

    EMP_ID NAME                    DEPT_ID                                      

---------- -------------------- ----------                                      

       101 John                          1    

2.2、查看當前系統時間

HR@mydb>select sysdate from dual;

SYSDATE                                                                         

-------------------                                                             

2016-03-15 21:37:36   

2.3、刪除測試表數據

HR@mydb>delete from emp where emp_id=101;

1 row deleted.

HR@mydb>delete from dept where dept_id=1;

1 row deleted.

HR@mydb>commit;

Commit complete.

2.4、開始閃回表

flashback table table_name to timestamp to_timestamp(systimestamp-interval '30' minute);

HR@mydb>flashback table emp to timestamp to_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh34:mi:ss');

flashback table emp to timestamp to_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh34:mi:ss')

                *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled 

--提示需要打開表的row movement

HR@mydb>alter table emp enable row movement;

Table altered.

HR@mydb>alter table dept enable row movement;

Table altered.

HR@mydb>flashback table emp to timestamp to_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh34:mi:ss');

flashback table emp to timestamp to_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh34:mi:ss')

*

ERROR at line 1:

ORA-02091: transaction rolled back 

ORA-02291: integrity constraint (HR.FK_EMP) violated - parent key not found 

--提示有外鍵約束

--兩個表同時閃回避免約束問題

HR@mydb>flashback table emp,dept to timestamp to_timestamp('2016-03-15 21:37:36','yyyy-mm-dd hh34:mi:ss');

Flashback complete.

2.5、檢查閃回結果

HR@mydb>select * from dept;

   DEPT_ID DEPT_NAME                                                            

---------- --------------------                                                 

         1 SUPPORT             

HR@mydb>select * from emp;

    EMP_ID NAME                    DEPT_ID                                      

---------- -------------------- ----------                                      

       101 John                          1    

--語法的變型允許閃回到一個系統變更號并在操作期間激活DML觸發器。

flashback table emp,dept to scn 6539425 enable triggers;

3、閃回版本查詢

--使用VERSIONS BETWEEN關鍵字

--根據scn的返回版本

select emp_id, versions_xid,versions_startscn,versions_endscn,versions_operation from emp versions between scn minvalue and maxvalue where emp_id=101;

--根據時間戳的返回版本

select emp_id, versions_xid,versions_starttime,versions_endtime,versions_operation from emp versions between timestamp (systimestamp - 25/1440) and systimestamp where emp_id=101;

4、閃回事務

--使用閃回事務需要開啟庫的最小附加日志 alter database add supplemental log data;

4.1創建測試表并插入數據

USER1@mydb>create table countries(name varchar2(10));

Table created.

USER1@mydb>alter table countries enable row movement;

Table altered.

USER1@mydb>insert into countries values('Zambia');

1 row created.

USER1@mydb>insert into countries values('Zimbabwe');

1 row created.

USER1@mydb>insert into countries values('Zamibia');

1 row created.

USER1@mydb>commit;

Commit complete.

USER1@mydb>select * from countries;

NAME                                                                            

----------                                                                      

Zambia                                                                          

Zimbabwe                                                                        

Zamibia    

4.2對測試表進行更新                                                                     

USER1@mydb>update countries set name='Namibia';

3 rows updated.

USER1@mydb>commit;

Commit complete.

USER1@mydb>select * from countries;

NAME                                                                            

----------                                                                      

Namibia                                                                         

Namibia                                                                         

Namibia   

4.3查詢行的所有版本，確定錯誤的事務ID                                                                      

USER1@mydb>select name,versions_xid,versions_operation from countries versions between scn minvalue and maxvalue ;

NAME       VERSIONS_XID     V                                                   

---------- ---------------- -                                                   

Namibia    080007000F040000 U                                                   

Namibia    080007000F040000 U                                                   

Namibia    080007000F040000 U                                                   

Zamibia    040009003C030000 I                                                   

Zimbabwe   040009003C030000 I                                                   

Zambia     040009003C030000 I                                                   

6 rows selected.

4.4查詢FLASHBACK_TARNSACTION_QUERY視圖看到該事務影響的行，并給出如何取消影響的SQL語句。

--XID列是RAW類型的，VERSIONS_XID偽列是十六進制的，需要使用類型強制轉換函數。

SYS@mydb>select operation,undo_sql from flashback_transaction_query where xid=hextoraw('080007000F040000');

OPERATIO UNDO_SQL

-------- ------------------------------------------------------------------------------------------------------------------------

UPDATE   update "USER1"."COUNTRIES" set "NAME" = 'Zamibia' where ROWID = 'AAASjKAAEAAAACVAAC';

UPDATE   update "USER1"."COUNTRIES" set "NAME" = 'Zimbabwe' where ROWID = 'AAASjKAAEAAAACVAAB';

UPDATE   update "USER1"."COUNTRIES" set "NAME" = 'Zambia' where ROWID = 'AAASjKAAEAAAACVAAA';

--還可以使用DBMS_FLASHBACK包進行閃回事務

execute sys.dbms_flashback.transaction_backout(numtxns=>2,xids=>sys.xid_array('080007000F040000'),options=>dbms_flashback.cascade);

5、閃回數據歸檔  --保證將表閃回到過去任何時間。

可以在預先存在的表空間中創建歸檔，但在新表空間中更加明智。

create flashback archive default hrarch tablespace fbda1 quota 10g retention 5 year;

--default關鍵字表示除非另行說明將用作所有表的歸檔。

alter flashback archive hrarch set default;

--quota顯示歸檔在表空間中占用的空間。可以在原有的表空間或另一個表空間中添加更多的空間。

alter flashback archive hrarch add tablespace fbda2 quota 10g;

--可以調整保留時間

alter flashback archive hrarch modify retention 7 year;

--數據超過了指定的保留期限，FBDA進程自動從歸檔中刪除。在過期之前也可以手動進行刪除

alter flashback archive hrarch purge before timestamp to_timestamp('01-01-2009','dd-mm-yyyy');

--只有FLASHBACK ARCHIVE ADMINISTER系統權限能夠創建、修改或刪除歸檔以及控制歸檔的保留和清除。

grant flashback archive administer to fbdaadmin;

--必須授予用戶歸檔的FLASHBACK ARCHIVE權限以便能夠對表進行歸檔。

grant flashback archive on hrarch to hr;

--啟用表的歸檔保護

alter table hr.employees flashback archive hrarch;

--刪除表的歸檔保護

alter table hr.employees no flashback archive;

--刪除閃回數據歸檔

drop flashback archive hrarch;

參考《OCP_OCA認證考試指南全冊__ORACLE_DATABASE_11G》