数据库open过程内部分析

最新推荐文章于 2022-01-28 14:38:24 发布

conggu1898

最新推荐文章于 2022-01-28 14:38:24 发布

阅读量151

点赞数

文章标签：数据库

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

[@more@]

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

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

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

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

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

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

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

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

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

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/509190/viewspace-855019/

conggu1898

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库open过程内部分析

数据库从mount到open的过程，是个详细的检查过程，oracle在open过程中会进行很多的检测，数据文件的存在，以及scn一致性等等，其中非常重要的并且大家熟知的有下面两个信息检测：[@more@]1：第一次检查数据文件头...
复制链接

扫一扫