问题解疑：关于Oracle隐式offline文件的研究

[oracle@localhost gaojf]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Oct 21 20:41:29 2006

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> conn gaojf/gaojf

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

IXDBA.NET技术社区

------------------------------ ------- ----------

GAOJF TABLE

T TABLE

TEST TABLE

SQL> insert into gaojf values('3333333333');

1 row created.

SQL> insert into gaojf values('3333333333');

1 row created.

SQL> show user

USER is "GAOJF"

[oracle@localhost gaojf]$ mv gaojfdb.dbf  gaojfdb.dbf.nn  ＃物理删除gaojfdb.dbf文件
[oracle@localhost gaojf]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Sun Oct 22 15:46:55 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> conn gaojf/gaojf
Connected.

SQL> insert into gaojf values('3333333333');

1 row created.

继续添加数据，仍然没有问题，试试create语句！

SQL> create table gaojf1 (a char(222));
create table gaojf1 (a char(222))
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/opt/oracle/oradata/gaojf/gaojfdb.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
＃不能执行操作create，看来此时只能执行insert操作了

强制redo归档

SQL> alter system switch logfile;

System altered.

可以看到这个alter操作很快结束，那么再次执行强制归档

再次执行！

SQL> /

这次这个alter语句执行了很长时间，此时我们观察alter日志，信息如下：

Sat Oct 21 20:46:39 2006

Thread 1 advanced to log sequence 33

Current log# 3 seq# 33 mem# 0: /opt/oracle/oradata/gaojf/redo03.log

Sat Oct 21 20:46:39 2006

ARC0: Evaluating archive log 1 thread 1 sequence 32

ARC0: Beginning to archive log 1 thread 1 sequence 32

Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/product/9.2.0/dbs/archlinux/T0001S0000000032.ARC'

ARC0: Completed archiving log 1 thread 1 sequence 32

Sat Oct 21 20:46:52 2006

Errors in file /opt/oracle/admin/gaojf/bdump/linux_ckpt_2783.trc:

ORA-01110: data file 6: '/opt/oracle/oradata/gaojf/gaojfdb.dbf'

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/opt/oracle/oradata/gaojf/gaojfdb.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Sat Oct 21 20:47:02 2006

Errors in file /opt/oracle/admin/gaojf/bdump/linux_ckpt_2783.trc:

ORA-01171: datafile 6 going offline due to error advancing checkpoint

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/opt/oracle/oradata/gaojf/gaojfdb.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Sat Oct 21 20:47:02 2006

Thread 1 advanced to log sequence 34

Current log# 2 seq# 34 mem# 0: /opt/oracle/oradata/gaojf/redo02.log

Sat Oct 21 20:47:12 2006

Errors in file /opt/oracle/admin/gaojf/bdump/linux_ckpt_2783.trc:

ORA-01110: data file 6: '/opt/oracle/oradata/gaojf/gaojfdb.dbf'

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/opt/oracle/oradata/gaojf/gaojfdb.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Sat Oct 21 20:47:12 2006

ARC1: Evaluating archive log 3 thread 1 sequence 33

ARC1: Beginning to archive log 3 thread 1 sequence 33

Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/product/9.2.0/dbs/archlinux/T0001S0000000033.ARC'

ARC1: Completed archiving log 3 thread 1 sequence 33

Sat Oct 21 20:48:55 2006

通过这个信息，已经基本知道是什么原因了，详述如下：

当第一次日志切换时刻，归档进程首先把redo日志归档到指定的位置，同时触发检查点，检查点发生后，dbwr进程将被触发，CKPT获取发生检查点时对应的SCN，通知DBWn要写到这个SCN为止，接下来，dbwr执行写脏列表数据到数据文件。

同时oracle后台跟踪进程在写日志过程中会发现dbwr要写入的数据文件不存在，因此，将在后台提示报错，但日志切换本身不会去检查这个数据文件知否存在。

当第二次执行日志切换时，检查点发生，检查点将推进，也就是要将上次检查点完成时刻对应的scn写入数据文件头和控制文件，此时，检查点进程回去寻找需要写入的数据文件，但是无法找到这个数据文件，ORA-01171错误将发生。

ORA-01171错误的发生将隐式的使这个数据文件offline，也就是oracle隐式的执行了offline这个操作，

这也是为什么第二次执行日志切换很慢的原因。

为了证明oracle执行了隐式的操作，此时dump出来控制文件信息，可以很明显的看到/opt/oracle/oradata/gaojf/gaojfdb.dbf的checkpoint scn与其他数据文件不同，究其原因，是由于offline 数据文件不能触发检查点发生，因此，checkpoint scn无法更新。

当然以上过程redo log中全部记录。

具体操作如下：

SQL> alter system set events 'immediate trace name CONTROLF level 10';

System altered.

Dump信息如下：

DATA FILE #4:

(name #6) /opt/oracle/oradata/gaojf/tools01.dbf

creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1

tablespace 4, index=4 krfil=4 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:113 scn: 0x0000.0027a14c 10/21/2006 20:46:39

Stop scn: 0xffff.ffffffff 10/21/2006 20:28:15

Creation Checkpointed at scn: 0x0000.00001e1c 07/24/2006 16:15:03

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

Offline scn: 0x0000.00000000 prev_range: 0

Online Checkpointed at scn: 0x0000.00000000

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

DATA FILE #5:

(name #5) /opt/oracle/oradata/gaojf/indx01.dbf

creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1

tablespace 5, index=5 krfil=5 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:125 scn: 0x0000.0027a14c 10/21/2006 20:46:39

Stop scn: 0xffff.ffffffff 10/21/2006 20:28:15

Creation Checkpointed at scn: 0x0000.00001e2f 07/24/2006 16:15:03

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0000.00000000 prev_range: 0

Online Checkpointed at scn: 0x0000.00000000

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

DATA FILE #6:

(name #4) /opt/oracle/oradata/gaojf/gaojfdb.dbf