Oracle 閃回技術(shù)

一、配置閃回?cái)?shù)據(jù)庫

1、數(shù)據(jù)庫處于歸檔日志模式

SQL> select log_mode from v$database;

LOG_MODE                                                                        

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

ARCHIVELOG  

2、創(chuàng)建閃回恢復(fù)區(qū)

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、設(shè)置閃回保留目標(biāo)時(shí)間

SQL> alter system set db_flashback_retention_target=240;

System altered.

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

4、關(guān)閉數(shù)據(jù)庫并啟動(dòng)到MOUNT狀態(tài)

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、啟用閃回日志記錄并打開數(shù)據(jù)庫

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閃回?cái)?shù)據(jù)庫 

1、創(chuàng)建一個(gè)臨時(shí)表

SQL> create table t as select * from dba_objects;

Table created.

2、查詢當(dāng)前系統(tǒng)時(shí)間表

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

TO_CHAR(SYSDATE,'YY                                                             

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

2016-03-15 11:34:21                                                             

3、對(duì)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、把數(shù)據(jù)庫閃回到步驟2查詢出的時(shí)間

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');--可以接受時(shí)間戳或系統(tǒng)變更號(hào)(SCN)參數(shù)，不接受日期或日志世界的序列號(hào)

Flashback complete.

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

SQL> alter database open read only;

Database altered.

SQL> select count(*) from t;

  COUNT(*)                                                                      

----------                                                                      

     72464                                                                      

6、閃回成功后，以resetlogs方式打開數(shù)據(jù)庫

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                                                                      

數(shù)據(jù)庫閃回成功

注：如果flashback閃回的數(shù)據(jù)庫時(shí)間太早，可以使用RECOVER DATABASE UNTILE TIME times恢復(fù)到后邊的時(shí)間點(diǎn)。

--也可以使用RMAN進(jìn)行閃回

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;

三、限制生成的閃回?cái)?shù)據(jù)量

1、關(guān)閉表空間閃回屬性

ALTER TABLESPACE tablespace_name FLASHBACK OFF;

2、開啟表空間閃回屬性

ALTER TABLESPACE tablespace_name FLASHBACK ON;--只能在mount狀態(tài)下執(zhí)行

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

select name,flashback_on from v$tablespace;

四、設(shè)置sqlplus提示符

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

SYS@ocp>

--為了對(duì)所有的sqlplus會(huì)話自動(dòng)設(shè)置sqlprompt。將上面的命令放置在ORACLE_HOME/sqlplus/admin目錄中的glogin.sql文件中

五、閃回刪除

1、創(chuàng)建一個(gè)練習(xí)用戶

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、創(chuàng)建一個(gè)帶有索引和約束的表，并插入一行

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、確認(rèn)模式的內(nèi)容

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、可以使用回收站的對(duì)象名進(jìn)行查詢，但不可做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恢復(fù)表

DROPPER@ocp>flashback table names to before drop;

Flashback complete.

8、查詢模式中的內(nèi)容

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視圖來證實(shí)確實(shí)刪除了用戶DROPPER擁有的所有對(duì)象。

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

  COUNT(*)                                                                      

----------                                                                      

         0                                                                      

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

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

六、管理回收站

SQL>show recyclebin;

user_recyclebin

dba_recyclebin

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

永久清除刪掉的對(duì)象

DROP TABLE table_name PURGE;--刪除表并不將它轉(zhuǎn)移到回收站

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

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

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

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

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

PURGE DBA_RECYCLEBIN;--清除所有刪除的對(duì)象，需要dba權(quán)限。

七、閃回查詢

--所有形式的閃回查詢依賴撤銷數(shù)據(jù)來重構(gòu)它在過去某個(gè)時(shí)間點(diǎn)的數(shù)據(jù)。

1、基本的閃回查詢

1.1創(chuàng)建測(cè)試表并插入測(cè)試數(shù)據(jù)

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查詢系統(tǒng)當(dāng)前時(shí)間，刪除部分?jǐn)?shù)據(jù)并確認(rèn)

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進(jìn)行閃回查詢

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查詢閃回時(shí)間點(diǎn)到現(xiàn)在的差異

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程序包將整個(gè)會(huì)話回退到過去某個(gè)時(shí)間，對(duì)其他會(huì)話沒有影響。處于閃回模式中不支持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、閃回表查詢  --啟用表閃回的第一步是在表上支持行移動(dòng)。

2.1創(chuàng)建測(cè)試表插入測(cè)試數(shù)據(jù)

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、查看當(dāng)前系統(tǒng)時(shí)間

HR@mydb>select sysdate from dual;

SYSDATE                                                                         

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

2016-03-15 21:37:36   

2.3、刪除測(cè)試表數(shù)據(jù)

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 

--提示有外鍵約束

--兩個(gè)表同時(shí)閃回避免約束問題

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、檢查閃回結(jié)果

HR@mydb>select * from dept;

   DEPT_ID DEPT_NAME                                                            

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

         1 SUPPORT             

HR@mydb>select * from emp;

    EMP_ID NAME                    DEPT_ID                                      

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

       101 John                          1    

--語法的變型允許閃回到一個(gè)系統(tǒng)變更號(hào)并在操作期間激活DML觸發(fā)器。

flashback table emp,dept to scn 6539425 enable triggers;

3、閃回版本查詢

--使用VERSIONS BETWEEN關(guān)鍵字

--根據(jù)scn的返回版本

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

--根據(jù)時(shí)間戳的返回版本

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、閃回事務(wù)

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

4.1創(chuàng)建測(cè)試表并插入數(shù)據(jù)

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對(duì)測(cè)試表進(jìn)行更新                                                                     

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查詢行的所有版本，確定錯(cuò)誤的事務(wù)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視圖看到該事務(wù)影響的行，并給出如何取消影響的SQL語句。

--XID列是RAW類型的，VERSIONS_XID偽列是十六進(jìn)制的，需要使用類型強(qiáng)制轉(zhuǎn)換函數(shù)。

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包進(jìn)行閃回事務(wù)

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

5、閃回?cái)?shù)據(jù)歸檔  --保證將表閃回到過去任何時(shí)間。

可以在預(yù)先存在的表空間中創(chuàng)建歸檔，但在新表空間中更加明智。

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

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

alter flashback archive hrarch set default;

--quota顯示歸檔在表空間中占用的空間?？梢栽谠械谋砜臻g或另一個(gè)表空間中添加更多的空間。

alter flashback archive hrarch add tablespace fbda2 quota 10g;

--可以調(diào)整保留時(shí)間

alter flashback archive hrarch modify retention 7 year;

--數(shù)據(jù)超過了指定的保留期限，FBDA進(jìn)程自動(dòng)從歸檔中刪除。在過期之前也可以手動(dòng)進(jìn)行刪除

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

--只有FLASHBACK ARCHIVE ADMINISTER系統(tǒng)權(quán)限能夠創(chuàng)建、修改或刪除歸檔以及控制歸檔的保留和清除。

grant flashback archive administer to fbdaadmin;

--必須授予用戶歸檔的FLASHBACK ARCHIVE權(quán)限以便能夠?qū)Ρ磉M(jìn)行歸檔。

grant flashback archive on hrarch to hr;

--啟用表的歸檔保護(hù)

alter table hr.employees flashback archive hrarch;

--刪除表的歸檔保護(hù)

alter table hr.employees no flashback archive;

--刪除閃回?cái)?shù)據(jù)歸檔

drop flashback archive hrarch;

參考《OCP_OCA認(rèn)證考試指南全冊(cè)__ORACLE_DATABASE_11G》