ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库数据跟踪记录

数据库数据跟踪记录

原创 Linux操作系统 作者:YallonKing 时间:2012-03-02 15:38:45 0 删除 编辑
今天群里有个朋友提问如下: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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-07

  • 博文量
    72
  • 访问量
    248733