Flashback閃回技術

raysuen發表於2017-09-07
##開啟flasbback
SQL> alter system set db_recovery_file_dest_size=80g scope=spfile;
設定閃回區位置：
SQL> alter system set db_recovery_file_dest='/workdb/account_flashback_area' scope=spfile;
設定閃回目標為5天，以分鐘為單位，每天為1440分鐘：
SQL> alter system set db_flashback_retention_target=7200 scope=spfile;


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1056411648 bytes
Fixed Size                  2257584 bytes
Variable Size             612371792 bytes
Database Buffers          436207616 bytes
Redo Buffers                5574656 bytes
Database mounted.

SQL> alter database flashback on;  #關閉alter database flashback off;

Database altered.

SQL> alter database open;


##查詢當前的scn號
SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
     724633
     

##查詢當前的log sequence號
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCH1
Oldest online log sequence     30
Next log sequence to archive   32
Current log sequence           32


####透過flashback恢復資料庫
startup mount
##恢復到指定的時間點
flashback database to timestamp(to_date('2010-01-01 20:00:00','YYYY-MM-DD HH24:MI:SS'));
flashback database to timestamp(sysdate-1/24); ##恢復到一小時前

##恢復到指定的SCN
flashback database to scn ********;

##恢復到某個log sequence
flashback database to sequence=**** thread=1;

alter database open resetlogs;


####透過flashback恢復表

##透過flashback恢復被刪除的表,如果cyclebin已經被開啟
flashback table <table_name> to before drop [rename to <net_name>];

##回退到一小時前
flashback table <table_name> to timestamp(system-1/24);

##多表同時回退到指定的時間點
flashback table <table_name,table_name> to timestamp(system-1/24/60);  ##回退到1分鐘前


##回退到指定的scn
flashback table <table_name> to scn ******;

##回退是啟動出發起
flashback table <table_name> to scn ****** enable triggers;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2144620/，如需轉載，請註明出處，否則將追究法律責任。

相關文章