数据库数据跟踪记录

今天群里有个朋友提问如下：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后进行。当然在当前日志没有被覆盖之前，插入的数据在日志文件中还是有记录的。

 

希望能帮到那位朋友！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26143577/viewspace-717533/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-717533/