oracle日常作業--透過flashback table復原資料－Eason [資料科學//Python學習/資料庫] & [拍片&剪片]

如果使用者不小心commit一筆資料，可透過flashback table還原

使用不完整復原也可以(大規模邏輯破壞用比較適當)

在此使用flashback table，他的復原機制與undo有關，因此涉及undo_retention。

介紹undo_retention

當Oracle使用者對Oracle資料庫進行新增、修改、刪除等異動類型的操作指令(DML)時，Oracle會先把異動前的樣子保留在還原表格空間(Undo Tablespace)下的還原區段(Undo Segment)，這樣的資料我們稱之為還原資訊(Undo Data)。

保留還原資訊(Undo Data)的好處，原本的想法只是方便Oracle使用者做交易的倒回(Transaction Rollback)及達到讀取一致性(Consistent Read)的需求，所以當交易完成時，還原資訊(Undo Data)就可以不需要，還原資訊(Undo Data)所佔用的空間就可以被新的交易覆蓋(重複使用)。然而，為了避免經常性發生ORA-01555: snapshot too old的錯誤及能順利完成Oracle瞬間復原(Flashback)的相關操作，可以藉由設定參數UNDO_RETENTION，讓還原資料在交易結束後能再保留一陣子，雖然這個參數最大值可設定2³¹ – 1(秒)，但設定太大，容易造成新的交易可能沒有可重複使用的還原空間而交易失敗，所以就增加還原表格空間(Undo Tablespace)的空間管理工作負載。

跟flashback有關的參數:

select name,flashback_on from v$database; --查看相關資料

alter database flashback on; --開啟

*注意: flashback table 不需要開啟 flashback on， flashback database 才需要

測試 flashback table

**************************

透過時間點恢復:

create table flashback_table (id number); --建立測試table

insert into flashback_table values(2); --建立測試資料

insert into flashback_table values(1); --建立測試資料

commit;

select * from flashback_table;

這時!!!

使用者誤commit一筆資料...

insert into flashback_table values(100);
commit;

失誤的時間

**2015-08-05 14:36:48**

這時使用者馬上告訴DBA是在哪個時候錯誤commit的

DBA馬上執行flashback table 復原到失誤時間之前

再執行flashback table前須執行以下指令:

alter table flashback_table enable row movement;

再執行

flashback table flashback_table to timestamp
to_timestamp('2015-08-05 14:36:00','yyyy-mm-dd hh24:mi:ss');

成功!!

****************

透過SCN恢復:

*********************

create table flashback_table1 (id number); --建立測試table

insert into flashback_table1 values(3); --建立測試資料

commit;

這時!!!

使用者誤commit一筆資料...

insert into flashback_table1 values(100);
commit;

select ROWID,f.* from flashback_table1 f;

AAANILAAEAAAAtNAAD 為失誤的ROWID

趕快通知DBA，DBA由flashback_transaction_query 資料表找出使用者誤commit資料的SCN

SQL> show user

'SYS'

select start_scn,
to_char(start_timestamp,'yyyy-mm-dd hh24:mi:ss') as s_time,
commit_scn,
to_char(commit_timestamp,'yyyy-mm-dd hh24:mi:ss') as c_time,
table_name,
undo_sql
from flashback_transaction_query
where logon_user='HR' AND table_name='FLASHBACK_TABLE1'
order by commit_timestamp;