資料庫資料跟蹤記錄

今天群裡有個朋友提問如下：insert into t values(9)，commit;再刪除所有聯機日誌，之後用recover database until cancel，alter database open

resetlogs開啟，這是的9的記錄還不在？

 

以下測試過程將說明資料整個歷史過程：

建立測試環境

SQL> create tablespace test datafile '/opt/M1HFData/oradata/m1hf/test.dbf' size 10m;

Tablespace created.

建立測試表

SQL> create table test(id number,name varchar2(10)) tablespace test;

Table created.

檢視當前日誌組

SQL> select b.status,a.group#,member from v$logfile a,v$log b where a.group#=b.group#;

STATUS               GROUP# MEMBER
---------------- ---------- ------------------------------
CURRENT                   3 /opt/M1HFData/m1hf/redo03b.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01.log
INACTIVE                  4 /opt/M1HFData/m1hf/redo04.log
INACTIVE                  5 /opt/M1HFData/m1hf/redo05.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02.log
CURRENT                   3 /opt/M1HFData/m1hf/redo03.log
INACTIVE                  4 /opt/M1HFData/m1hf/redo04b.log
INACTIVE                  5 /opt/M1HFData/m1hf/redo05b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01b.log

10 rows selected.

檢視當前日誌檔案中是否有待插入記錄資訊

[oracle@m1hfdata ~]$ strings /opt/M1HFData/m1hf/redo03.log | grep yallonking | wc -l
0

檢視資料檔案中是否有待插入記錄資訊

[oracle@m1hfdata ~]$ strings /opt/M1HFData/oradata/m1hf/test.dbf | grep yallonking | wc -l
0

 

插入測試資料

SQL> insert into test values (1,'yallonking');

1 row created.

 

檢視當前日誌檔案是否有測試資料記錄（未提交狀態）

[oracle@m1hfdata ~]$ strings /opt/M1HFData/m1hf/redo03.log | grep yallonking | wc -l
1

 

檢視資料檔案是否有測試資料

[oracle@m1hfdata ~]$ strings /opt/M1HFData/oradata/m1hf/test.dbf | grep yallonking | wc -l
0

提交記錄

SQL> commit;

Commit complete.

 

檢視資料檔案是否有測試資料

[oracle@m1hfdata ~]$ strings /opt/M1HFData/oradata/m1hf/test.dbf | grep yallonking | wc -l
0

 

檢視日誌檔案是否有測試資料記錄（已提交且當前日誌檔案）

[oracle@m1hfdata ~]$ strings /opt/M1HFData/m1hf/redo01.log | grep yallonking | wc -l
1

 

切換日誌組

SQL> alter system switch logfile;

System altered.

檢視當前日誌組資訊

SQL> select b.status,a.group#,member from v$logfile a,v$log b where a.group#=b.group#;

STATUS               GROUP# MEMBER
---------------- ---------- ------------------------------
ACTIVE                    3 /opt/M1HFData/m1hf/redo03b.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01.log
CURRENT                   4 /opt/M1HFData/m1hf/redo04.log
INACTIVE                  5 /opt/M1HFData/m1hf/redo05.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02.log
ACTIVE                    3 /opt/M1HFData/m1hf/redo03.log
CURRENT                   4 /opt/M1HFData/m1hf/redo04b.log
INACTIVE                  5 /opt/M1HFData/m1hf/redo05b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01b.log

10 rows selected.

檢視日誌檔案是否有測試資料記錄（active狀態）

[oracle@m1hfdata ~]$ strings /opt/M1HFData/m1hf/redo03.log | grep yallonking | wc -l
1

 

檢視資料檔案是否有測試資料存在

[oracle@m1hfdata ~]$ strings /opt/M1HFData/oradata/m1hf/test.dbf | grep yallonking | wc -l
0

 

歸檔日誌檔案

SQL> alter system archive log current;

System altered.

 

檢視日誌檔案狀態

SQL> select b.status,a.group#,member from v$logfile a,v$log b where a.group#=b.group#;

STATUS               GROUP# MEMBER
---------------- ---------- ------------------------------
ACTIVE                    3 /opt/M1HFData/m1hf/redo03b.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01.log
ACTIVE                    4 /opt/M1HFData/m1hf/redo04.log
CURRENT                   5 /opt/M1HFData/m1hf/redo05.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02.log
ACTIVE                    3 /opt/M1HFData/m1hf/redo03.log
ACTIVE                    4 /opt/M1HFData/m1hf/redo04b.log
CURRENT                   5 /opt/M1HFData/m1hf/redo05b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01b.log

10 rows selected.

 

檢視資料檔案是否存在測試資料

[oracle@m1hfdata ~]$ strings /opt/M1HFData/oradata/m1hf/test.dbf | grep yallonking | wc -l
0

 

依然處於active狀態下日誌檔案資訊存在測試資料

[oracle@m1hfdata ~]$ strings /opt/M1HFData/m1hf/redo03.log | grep yallonking | wc -l
1

 

繼續檢視資料檔案是否存在測試資料

[oracle@m1hfdata ~]$ strings /opt/M1HFData/oradata/m1hf/test.dbf | grep yallonking | wc -l
1

 

等待n分鐘後... ...

 

檢視當前日誌組資訊

SQL> select b.status,a.group#,member from v$logfile a,v$log b where a.group#=b.group#;

STATUS               GROUP# MEMBER
---------------- ---------- ------------------------------
INACTIVE                  3 /opt/M1HFData/m1hf/redo03b.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01.log
ACTIVE                    4 /opt/M1HFData/m1hf/redo04.log
CURRENT                   5 /opt/M1HFData/m1hf/redo05.log
INACTIVE                  2 /opt/M1HFData/m1hf/redo02.log
INACTIVE                  3 /opt/M1HFData/m1hf/redo03.log
ACTIVE                    4 /opt/M1HFData/m1hf/redo04b.log
CURRENT                   5 /opt/M1HFData/m1hf/redo05b.log
INACTIVE                  1 /opt/M1HFData/m1hf/redo01b.log

10 rows selected.

檢視日誌檔案中測試資料是否存在

[oracle@m1hfdata ~]$ strings /opt/M1HFData/m1hf/redo03.log | grep yallonking | wc -l
1

 

結論：

     在插入的資料commit後，資料沒有立即寫進資料。即使是發生日誌切換和歸檔，DBWRn也有可能還沒有及時將資料寫進資料檔案，所以，要獲取插入資料資料檔案，應該最好在當前日誌處於inactive後進行。當然在當前日誌沒有被覆蓋之前，插入的資料在日誌檔案中還是有記錄的。

 

希望能幫到那位朋友！