深入解析oracle——控制文件与数据库初始化（2）！

来源：互联网发布：python 消息提醒编辑：程序博客网时间：2024/06/05 07:43

1、SCN

scn的获取方式：

SQL> select current_scn from v$database;CURRENT_SCN-----------    1558091

数据库中有一张表记录了scn和时间的对应关系：

SQL> select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from smon_scn_time where rownum < 10;       SCN TO_CHAR(TIME_DP,'YY---------- -------------------         4 2008-02-17 01:50:43     10725 2008-02-17 01:51:16    114369 2008-02-17 01:56:49    160566 2008-02-17 02:01:21    238143 2008-02-17 02:06:31    296479 2008-02-17 02:11:17    340068 2008-02-17 02:17:22    380836 2008-02-17 02:21:18    428625 2008-02-17 02:26:36

把时间转换为scn：

SQL> select timestamp_to_scn(to_date('2011-12-02 09:15:49','yyyy-mm-dd hh24:mi:ss')) scn from dual;       SCN----------   1532716

把scn转换为时间：

SQL> select to_char(scn_to_timestamp(1532716),'yyyy-mm-dd hh24:mi:ss') time from dual;TIME-------------------2011-12-02 09:15:49

把十六进制的scn转换为十进制：

SQL> select to_number('174e4f','xxxxxx') scn from dual;       SCN----------   1527375

2、数据文件

数据文件头中包含了该数据文件的checkpoint scn，表示该数据文件最近一次执行检查点操作时的scn。（下面的内容是转储数据文件头信息中的一段）

DATA FILE #1:   (name #7) /u01/app/oracle/oradata/orcl/system01.dbfcreation size=0 block size=8192 status=0xe head=7 tail=7 dup=1 tablespace 0, index=1 krfil=1 prev_file=0 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:400 scn: 0x0000.00177b57 12/04/2011 08:18:49                     --1 Stop scn: 0xffff.ffffffff 12/02/2011 12:38:27                                   --2 Creation Checkpointed at scn:  0x0000.00000007 02/17/2008 01:50:54 thread:0 rba:(0x0.0.0) enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000  ... ... 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.000716f6 prev_range: 0 Online Checkpointed at scn:  0x0000.000716f7 11/01/2011 03:32:41 thread:1 rba:(0x1.2.0) enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000  ... ... 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED  V10 STYLE FILE HEADER:                                                          --3Compatibility Vsn = 169870080=0xa200300Db ID=1293815896=0x4d1e1058, Db Name='ORCL'Activation ID=0=0x0Control Seq=3338=0xd0a, File size=64000=0xfa00File Number=1, Blksiz=8192, File Type=3 DATATablespace #0 - SYSTEM  rel_fn:1 Creation   at   scn: 0x0000.00000007 02/17/2008 01:50:54                         --4Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 reset logs count:0x2da8c4d9 scn: 0x0000.000716f7 reset logs terminal rcv data:0x0 scn: 0x0000.00000000 prev reset logs count:0x268ea86b scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000 recovered at 11/26/2011 18:10:13 status:0x2004 root dba:0x00400179 chkpt cnt: 400 ctl cnt:399                    --9begin-hot-backup file size: 0Checkpointed at scn:  0x0000.00177b57 12/04/2011 08:18:49                        --5 thread:1 rba:(0x127.7cbc.10) enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000  ... .... 00000000 00000000 00000000 00000000 00000000 00000000Backup Checkpointed at scn:  0x0000.00000000  thread:0 rba:(0x0.0.0) enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000  ... ... 00000000 00000000 00000000 00000000 00000000 00000000External cache id: 0x0 0x0 0x0 0x0Absolute fuzzy scn: 0x0000.00000000Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00Terminal Recovery Stamp  01/01/1988 00:00:00Platform Information: Creation Platform ID: 10Current Platform ID: 10Last Platform ID: 10

在（3）FILE HEADER之前的信息来自控制文件，之后的信息来自数据文件头，在数据库启动过程中，需要依赖两部分信息进行比对判断，从而确保数据库的一致性和判断是否需要进行恢复。

3、日志文件（下面的内容是转储控制文件信息中的一段）

***************************************************************************LOG FILE RECORDS*************************************************************************** (size = 72, compat size = 72, section max = 16, section in-use = 3,  last-recid= 6, old-recno = 0, last-recno = 0) (extent = 1, blkno = 10, numrecs = 16)LOG FILE #1:   (name #3) /u01/app/oracle/oradata/orcl/redo01.log  (name #11) /u01/app/oracle/oradata/orcl/redo011.log Thread 1 redo log links: forward: 2 backward: 0 siz: 0x19000 seq: 0x00000127 hws: 0xa bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0016a691 Low scn: 0x0000.0017473d 12/01/2011 18:00:44 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00LOG FILE #2:   (name #2) /u01/app/oracle/oradata/orcl/redo02.log  (name #12) /u01/app/oracle/oradata/orcl/redo012.log Thread 1 redo log links: forward: 3 backward: 1 siz: 0x19000 seq: 0x00000125 hws: 0xc bsz: 512 nab: 0x13fca flg: 0x1 dup: 2 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00154d93 Low scn: 0x0000.001629aa 11/25/2011 23:49:41 Next scn: 0x0000.0016a691 11/26/2011 18:10:14LOG FILE #3:   (name #1) /u01/app/oracle/oradata/orcl/redo03.log  (name #13) /u01/app/oracle/oradata/orcl/redo013.log Thread 1 redo log links: forward: 0 backward: 2 siz: 0x19000 seq: 0x00000126 hws: 0x12 bsz: 512 nab: 0x18ffb flg: 0x1 dup: 2 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.001629aa Low scn: 0x0000.0016a691 11/26/2011 18:10:14 Next scn: 0x0000.0017473d 12/01/2011 18:00:44

日志文件头中包含了Low scn和Next scn。这两个scn标示该日志文件包含有介于Low scn到Next scn的重做信息。对于current的日志文件，其最终scn不可知，所以Next scn被置为无穷大，也就是ffffffff。

可以看见上面LOG FILE #1即（redo01.log）是current日志文件组：

SQL> select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------         1          1        295   52428800          2 NO  CURRENT                1525565 01-12月-11         2          1        293   52428800          2 YES INACTIVE               1452458 25-11月-11         3          1        294   52428800          2 YES INACTIVE               1484433 26-11月-11