跟踪数据在数据库中的整个移动过程

建立测试环境

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后进行。当然在当前日志没有被覆盖之前，插入的数据在日志文件中还是有记录的。