ITPub博客

首页 > 数据库 > 数据库开发技术 > 数据库open过程内部分析

数据库open过程内部分析

原创 数据库开发技术 作者:exitgogo 时间:2006-08-11 09:27:44 0 删除 编辑

数据库从mountopen的过程,是个详细的检查过程,oracleopen过程中会进行很多的检测,数据文件的存在,以及scn一致性等等,其中非常重要的并且大家熟知的有下面两个信息检测:

[@more@]

1:第一次检查数据文件头的检查点计数(chenkpoint cnt)是否和控制文件中的检查点计数(chenkpoint cnt)一致,此步骤主要是用以确认数据文件是否来自同一版本。

2:第二次检查数据文件头的开始scncheckpoint scn)和控制文件中记录的每个文件的结束scnstop scn)是否一致,如果两者相同,不需要进行恢复,如果不同,则数据库需要进行恢复。

以上两个检测,总结起来,第一次是版本一致性检测,第二次是是否需要恢复性的检测。

下面通过具体的操作,来演示上面两个过程的具体实现。

[oracle@www cicro]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 10 17:17:16 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 - Production

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>quit

Disconnected from Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 - Production

[oracle@www cicro]$ ls

cicrodb.dbf control03.ctl example01.dbf redo02.log test undotbstemp.dbf cicrodb.dbf.bak cwmlite01.dbf indx01.dbf redo03.log tools01.dbf users01.dbf control01.ctl cws33db.dbf odm01.dbf system01.dbf undotbs01.dbf xdb01.dbf control02.ctl drsys01.dbf redo01.log temp01.dbf undotbs02.dbf

[oracle@www cicro]$ cp cicrodb.dbf cicrodb.dbf.old

[oracle@www cicro]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 10 15:36:32 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 235999352 bytes

Fixed Size 450680 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

此时,/opt/oracle/oradata/cicro/cicrodb.dbf数据文件头信息如下:

**********************************************************

DATA FILE #11:

(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf

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

tablespace 12, index=12 krfil=11 prev_file=0

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

Checkpoint cnt:137 scn: 0x0800.0195c4d2 08/10/2006 15:36:04

Stop scn: 0x0800.0195c4d2 08/10/2006 15:36:04

Creation Checkpointed at scn: 0x0000.01213333 12/28/2005 15:28:58

thread:1 rba:(0x60.baf.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0800.017b052f prev_range: 0

Online Checkpointed at scn: 0x0800.017b0530 08/02/2006 02:29:36

thread:1 rba:(0x1.2.0)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

以上是控制文件的信息,也就是说信息是从控制文件得到的。

以下信息是读取数据文件头得到的。

aux_file is NOT DEFINED

FILE HEADER:

Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000

Db ID=44014079=0x29f99ff, Db Name='CICRO'

Activation ID=0=0x0

Control Seq=1295=0x50f, File size=7680=0x1e00

File Number=11, Blksiz=8192, File Type=3 DATA

Tablespace #12 - CICRO rel_fn:11

Creation at scn: 0x0000.01213333 12/28/2005 15:28:58

Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0

reset logs count:0x239b4610 scn: 0x0800.017b0530 recovered at 08/10/2006 15:18:39

status:0x0 root dba:0x00000000 chkpt cnt: 137 ctl cnt:136

begin-hot-backup file size: 0

Checkpointed at scn: 0x0800.0195c4d2 08/10/2006 15:36:04

thread:1 rba:(0x1a.2ac.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Backup Checkpointed at scn: 0x0000.00000000

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

External cache id: 0x0 0x0 0x0 0x0

Absolute fuzzy scn: 0x0000.00000000

Recovery fuzzy scn: 0x0000.00000000 08/10/2006 14:18:35

Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00

从以上可以看出,此时数据库数据文件头Checkpoint cnt和控制文件Checkpoint cnt相同。数据文件头的Checkpointed at scn和控制文件中的Checkpointed at scn也相同。

然后在dump出控制文件信息,如下

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

Session altered.

也同理找出/opt/oracle/oradata/cicro/cicrodb.dbf文件在控制文件中记录的信息。

***********************************************************

DATA FILE #11:

(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf

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

tablespace 12, index=12 krfil=11 prev_file=0

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

Checkpoint cnt:137 scn: 0x0800.0195c4d2 08/10/2006 15:36:04

Stop scn: 0x0800.0195c4d2 08/10/2006 15:36:04

Creation Checkpointed at scn: 0x0000.01213333 12/28/2005 15:28:58

thread:1 rba:(0x60.baf.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0800.017b052f prev_range: 0

Online Checkpointed at scn: 0x0800.017b0530 08/02/2006 02:29:36

thread:1 rba:(0x1.2.0)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

以上可以看到,在正常情况下,控制文件中dump出来的数据文件的scn,和dump数据文件头得到的数据文件scn是一样的,在dump数据文件头得到的信息中,一部分来自与控制文件,一部分来自与数据文件头,

如上说明。

继续往下测试:

SQL> alter database open;

Database altered.

SQL> quit

Disconnected from Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 - Production

[oracle@www cicro]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 10 15:54:11 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 - Production

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

执行日志切换,然后触发完全检查点,这样checkpoint scn将被更新。

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

FIRST_CHANGE# FIRST_TIM

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

1 1 26 104857600 1 YES INACTIVE

8.7961E+12 10-AUG-06

2 1 27 104857600 1 YES INACTIVE

8.7961E+12 10-AUG-06

3 1 28 104857600 1 NO CURRENT

8.7961E+12 10-AUG-06

然后关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> quit

Disconnected from Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 – Production

接下来把原来备份的cicrodb.dbf替换过来。

[oracle@www cicro]$ ls

cicrodb.dbf control02.ctl drsys01.dbf redo01.log temp01.dbf undotbs02.dbf cicrodb.dbf.bak control03.ctl example01.dbf redo02.log test undotbstemp.dbf cicrodb.dbf.old cwmlite01.dbf indx01.dbf redo03.log tools01.dbf users01.dbf control01.ctl cws33db.dbf odm01.dbf system01.dbf undotbs01.dbf xdb01.dbf

[oracle@www cicro]$ mv cicrodb.dbf cicrodb.dbf.new

[oracle@www cicro]$ mv cicrodb.dbf.old cicrodb.dbf

[oracle@www cicro]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 10 15:57:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 235999352 bytes

Fixed Size 450680 bytes

Variable Size 201326592 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

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

Sessionaltered.

以下是dump出来的控制文件中cicrodb.dbf文件的信息。

***********************************************************

DATABASE ENTRY

*********************************************************** (blkno = 0x1, size = 192, max = 1, in-use = 1, last-recid= 0)

DF Version: creation=0x9200000 compatible=0x8000000, Date 09/14/2005 14:01:35

DB Name "CICRO"

Database flags = 0x00404001

Controlfile Creation Timestamp 09/14/2005 14:01:35

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0800.017b0530 Resetlogs Timestamp 08/02/2006 02:29:36

Prior resetlogs scn: 0x0000.0123d11d Prior resetlogs Timestamp 12/29/2005 11:52:32

Redo Version: creation=0x9200000 compatable=0x9200000

#Data files = 14, #Online files = 14

Database checkpoint: Thread=1 scn: 0x0800.0195c773

Threads: #Enabled=1, #Open=0, Head=0, Tail=0

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

Max log members = 5, Max data members = 1

Arch list: Head=3, Tail=3, Force scn: 0x0800.0195c20fscn: 0x0800.0195c761

Controlfile Checkpointed at scn: 0x0800.0195c761 08/10/2006 15:56:27

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

***********************************************************

CHECKPOINT PROGRESS RECORDS

***********************************************************

(blkno = 0x4, size = 104, max = 1, in-use = 1, last-recid= 0)

THREAD #1 - status:0x1 flags:0x0 dirty:0

low cache rba:(0xffffffff.ffffffff.ffff) on disk rba:(0x1c.2.0)

on disk scn: 0x0800.0195c761 08/10/2006 15:56:27

resetlogs scn: 0x0800.017b0530 08/02/2006 02:29:36

heartbeat: 598125514 mount id: 73105079

MTTR statistics status: 0

***********************************************************

DATA FILE RECORDS

***********************************************************DATA FILE #11:

(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf

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

tablespace 12, index=12 krfil=11 prev_file=0

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

Checkpoint cnt:140 scn: 0x0800.0195c773 08/10/2006 15:57:01

Stop scn: 0x0800.0195c773 08/10/2006 15:57:01

Creation Checkpointed at scn: 0x0000.01213333 12/28/2005 15:28:58

thread:1 rba:(0x60.baf.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0800.017b052f prev_range: 0

Online Checkpointed at scn: 0x0800.017b0530 08/02/2006 02:29:36

thread:1 rba:(0x1.2.0)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

然后在dump出数据文件头信息

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

***********************************************************

DATA FILE #11:

(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf

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

tablespace 12, index=12 krfil=11 prev_file=0

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

Checkpoint cnt:140 scn: 0x0800.0195c773 08/10/2006 15:57:01

Stop scn: 0x0800.0195c773 08/10/2006 15:57:01

Creation Checkpointed at scn: 0x0000.01213333 12/28/2005 15:28:58

thread:1 rba:(0x60.baf.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0800.017b052f prev_range: 0

Online Checkpointed at scn: 0x0800.017b0530 08/02/2006 02:29:36

thread:1 rba:(0x1.2.0)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

以上信息是来自与控制文件,所以与控制文件信息相同。

以下信息是来自于当前的数据文件头,此时由于cicrodb.dbf是旧的数据文件,就出现问题了。

FILE HEADER:

Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000

Db ID=44014079=0x29f99ff, Db Name='CICRO'

Activation ID=0=0x0

Control Seq=1295=0x50f, File size=7680=0x1e00

File Number=11, Blksiz=8192, File Type=3 DATA

Tablespace #12 - CICRO rel_fn:11

Creation at scn: 0x0000.01213333 12/28/2005 15:28:58

Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0

reset logs count:0x239b4610 scn: 0x0800.017b0530 recovered at 08/10/2006 15:18:39

status:0x0 root dba:0x00000000 chkpt cnt: 137 ctl cnt:136

chkpt cnt: 137是记录checkpoint的执行次数,由于是旧的数据文件,chkpt cnt的值还是原来没有替换cicrodb.dbf以前的状态,所以与dump出来的控制文件中记录的chkpt cnt就出现了不同,此时检测就不能通过。)

begin-hot-backup file size: 0

Checkpointed at scn: 0x0800.0195c4d2 08/10/2006 15:36:04

(此时文件的Checkpointed at scn是在没有替换cicrodb.dbf以前的Checkpointed at scn,与dump出来的控制文件中记录的Checkpointed at scn不相同,因此,open时肯定需要恢复)

thread:1 rba:(0x1a.2ac.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

Backup Checkpointed at scn: 0x0000.00000000

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

External cache id: 0x0 0x0 0x0 0x0

Absolute fuzzy scn: 0x0000.00000000

Recovery fuzzy scn: 0x0000.00000000 08/10/2006 14:18:35

Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00

尝试删除cicrodb.dbf文件,然后dump出来数据文件头信息,看看又是什么情况

SQL> quit

Disconnected from Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 - Production

[oracle@www cicro]$ ls

cicrodb.dbf control02.ctl drsys01.dbf redo01.log temp01.dbf undotbs02.dbf cicrodb.dbf.bak control03.ctl example01.dbf redo02.log test undotbstemp.dbf cicrodb.dbf.new cwmlite01.dbf indx01.dbf redo03.log tools01.dbf users01.dbf control01.ctl cws33db.dbf odm01.dbf system01.dbf undotbs01.dbf xdb01.dbf

[oracle@www cicro]$ mv cicrodb.dbf cicrodb.dbf.nn

[oracle@www cicro]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 10 16:25:13 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Release 9.2.0.1.0 - Production

JServer Release 9.2.0.1.0 – Production

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL>

此时Dump出数据文件头信息,如下:

***********************************************************

DATA FILE #11:

(name #15) /opt/oracle/oradata/cicro/cicrodb.dbf

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

tablespace 12, index=12 krfil=11 prev_file=0

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

Checkpoint cnt:140 scn: 0x0800.0195c773 08/10/2006 15:57:01

Stop scn: 0x0800.0195c773 08/10/2006 15:57:01

Creation Checkpointed at scn: 0x0000.01213333 12/28/2005 15:28:58

thread:1 rba:(0x60.baf.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Offline scn: 0x0800.017b052f prev_range: 0

Online Checkpointed at scn: 0x0800.017b0530 08/02/2006 02:29:36

thread:1 rba:(0x1.2.0)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

以上仍就是转自控制文件的信息。

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file

ORA-01110: data file 11: '/opt/oracle/oradata/cicro/cicrodb.dbf'

*** Error 1157 in open/read file # 11 ***

此时就提示ORA-01157错误,也就是数据库open时要报的错误。

继续下面的测试,恢复刚才删除的数据文件,尝试打开数据库:

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

上一篇: Linux 15周岁庆典
请登录后发表评论 登录
全部评论

注册时间:2007-12-14

  • 博文量
    81
  • 访问量
    411690