【ORACLE】异常关闭时ORACLE如何根据SCN对数据库进行一致性判断

72 阅读 0 评论 48 点赞

我是靠谱客的博主阳光万宝路，最近开发中收集的这篇文章主要介绍【ORACLE】异常关闭时ORACLE如何根据SCN对数据库进行一致性判断，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

通过shutdown abort方式关闭数据库，可以模拟类似于突然断电导致的宕机故障，使用这种方式，ORACLE会立即中断所有事务，关闭当前所有数据库连接，不执行Checkpoint，立即关闭数据库，在下一次启动时必须进行实例恢复，才能够打开数据库。
实验如下：
1、查看当前SCN号以及日志组信息

SYS@oradb3> select current_scn from v$database;

CURRENT_SCN
-----------
    1018327

SYS@oradb3> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1      1          7   52428800    512      1 NO  INACTIVE           1012449 16-SEP-17      1017695 16-SEP-17
     2      1          8   52428800    512      1 NO  CURRENT            1017695 16-SEP-17   2.8147E+14
     3      1          6   52428800    512      1 NO  INACTIVE            995974 15-SEP-17      1012449 16-SEP-17

2、模拟数据库异常关闭，启动到mount，转储控制文件

SYS@oradb3> shutdown abort;
ORACLE instance shut down.
SYS@oradb3> startup mount;
ORACLE instance started.

Total System Global Area 1553305600 bytes
Fixed Size          2253544 bytes
Variable Size         469765400 bytes
Database Buffers     1073741824 bytes
Redo Buffers            7544832 bytes
Database mounted.
SYS@oradb3> alter session set events 'immediate trace name controlf level 12';

Session altered.

SYS@oradb3> select VALUE from v$diag_info where NAME='Default Trace File';

VALUE
----------------
/u01/app/oracle/diag/rdbms/oradb3/oradb3/trace/oradb3_ora_115711.trc

3、打开转储的控制文件查看详细内容

数据库的SCN信息

***************************************************************************
DATABASE ENTRY
***************************************************************************
 (size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 1, numrecs = 1)
 09/15/2017 14:42:25
 DB Name "ORADB3"
 Database flags = 0x00404000 0x00001000
 Controlfile Creation Timestamp  09/15/2017 14:42:25
 Incmplt recovery scn: 0x0000.00000000
 Resetlogs scn: 0x0000.000e2006 Resetlogs Timestamp  09/15/2017 14:42:27
 Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp  08/24/2013 11:37:30
 Redo Version: compatible=0xb200400
 #Data files = 5, #Online files = 5
 Database checkpoint: Thread=1 scn: 0x0000.000f875f  ——>此处为Checkpoint SCN
 Threads: #Enabled=1, #Open=1, Head=1, Tail=1

Database checkpoint: Thread=1 scn: 0x0000.000f875f ——>此处为Checkpoint SCN

REDO SCN信息

***************************************************************************
REDO THREAD RECORDS
***************************************************************************
 (size = 256, compat size = 256, section max = 8, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 9, numrecs = 8)
THREAD #1 - status:0xf thread links forward:0 back:0
 #logs:3 first:1 last:3 current:2 last used seq#:0x8
 enabled at scn: 0x0000.000e2006 09/15/2017 14:42:27
 disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
 opened at 09/16/2017 08:21:33 by instance oradb3
Checkpointed at scn:  0x0000.000f875f 09/16/2017 08:23:15 ——>此处为Checkpoint SCN
 thread:1 rba:(0x8.2.10)

Checkpointed at scn: 0x0000.000f875f 09/16/2017 08:23:15 ——>此处为Checkpoint SCN

日志文件SCN信息

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 3, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #3: /u01/app/oracle/oradata/oradb3/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000007 hws: 0x2 bsz: 512 nab: 0x1635b flg: 0x0 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f3286
 Low scn: 0x0000.000f72e1 09/16/2017 08:22:00
 Next scn: 0x0000.000f875f 09/16/2017 08:23:15         ——>此处为日志组1检查点SCN
LOG FILE #2:
  name #2: /u01/app/oracle/oradata/oradb3/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000008 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f72e1
 Low scn: 0x0000.000f875f 09/16/2017 08:23:15          ——>此处为日志组2起始点SCN
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
  name #1: /u01/app/oracle/oradata/oradb3/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000006 hws: 0x5 bsz: 512 nab: 0x16ad8 flg: 0x0 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f1829
 Low scn: 0x0000.000f3286 09/15/2017 14:45:54
 Next scn: 0x0000.000f72e1 09/16/2017 08:22:00

数据文件SCN信息

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 5,
  last-recid= 52, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  name #7: /u01/app/oracle/oradata/oradb3/system01.dbf
creation 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:98 scn: 0x0000.000f875f 09/16/2017 08:23:15 ——>此处为Checkpoint SCN
 Stop scn: 0xffff.ffffffff 09/15/2017 21:24:36              ——>此处为Stop SCN

 DATA FILE #2:
  name #6: /u01/app/oracle/oradata/oradb3/sysaux01.dbf
creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1
 tablespace 1, index=2 krfil=2 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:98 scn: 0x0000.000f875f 09/16/2017 08:23:15 ——>此处为Checkpoint SCN
 Stop scn: 0xffff.ffffffff 09/15/2017 21:24:36              ——>此处为Stop SCN

DATA FILE #3:
  name #5: /u01/app/oracle/oradata/oradb3/undotbs01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 2, index=3 krfil=3 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:19 scn: 0x0000.000f875f 09/16/2017 08:23:15 ——>此处为Checkpoint SCN
 Stop scn: 0xffff.ffffffff 09/15/2017 21:24:36              ——>此处为Stop SCN

DATA FILE #4:
  name #4: /u01/app/oracle/oradata/oradb3/users01.dbf
creation size=0 block size=8192 status=0xe head=4 tail=4 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:97 scn: 0x0000.000f875f 09/16/2017 08:23:15 ——>此处为Checkpoint SCN
 Stop scn: 0xffff.ffffffff 09/15/2017 21:24:36              ——>此处为Stop SCN

Checkpoint cnt:97 scn: 0x0000.000f875f 09/16/2017 08:23:15 ——>此处为Checkpoint SCN

Stop scn: 0xffff.ffffffff 09/15/2017 21:24:36 ——>此处为Stop SCN

4、从上面可以看到，由于数据库非正常关闭，导致没有完成最后的检查点，所有数据文件的Stop SCN均指向无穷大（Stop scn: 0xffff.ffffffff）
从十六进制转换到十进制可以看到：

十六进制	十进制
000f875f	1017695

这个Checkpoint SCN，正好就是我们之前查询的日志组2的起始检查点FIRST_CHANGE#（Low scn）

SYS@oradb3> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1      1          7   52428800    512      1 NO  INACTIVE           1012449 16-SEP-17      1017695 16-SEP-17
     2      1          8   52428800    512      1 NO  CURRENT            1017695 16-SEP-17   2.8147E+14
     3      1          6   52428800    512      1 NO  INACTIVE            995974 15-SEP-17      1012449 16-SEP-17

以上各部分文件的Checkpoint SCN都是一致的（Checkpoint scn: 0x0000.000f875f），但是数据文件的Stop SCN均指向无穷大（Stop scn: 0xffff.ffffffff），不等于Checkpoint SCN，这种情况意味着数据库上一次关闭没有执行完全检查点，属于非正常关闭，如果此时启动数据库，将需要进行实例恢复。

5、数据库实例恢复
再次启动数据库时，ORACLE的SMON进程（系统监控进程）会自动执行实例恢复（Instance recovery），包含两个步骤：

前滚（Rolling Forward）——Cache Recovery
在前滚阶段，ORACLE读取日志，从最后完成的Checkpoint 开始，应用所有重做日志。
回滚（Rolling Back）——Transaction Recovery
在第二阶段，所有未被提交的事务被回滚，用以保证事务ACID特性中的原子性（Atomicity）
可以查看alert（sid）.log文件中的相关信息：

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 10632 KB redo, 1394 data blocks need recovery
Started redo application at
 Thread 1: logseq 8, block 6183
Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/oradb3/redo02.log
Completed redo application of 9.53MB
Completed crash recovery at
 Thread 1: logseq 8, block 27447, scn 1038409
 1394 data blocks read, 1394 data blocks written, 10632 redo k-bytes read
Sat Sep 16 10:01:27 2017
Thread 1 advanced to log sequence 9 (thread open)
Thread 1 opened at log sequence 9
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/oradb3/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Sep 16 10:01:27 2017
SMON: enabling cache recovery
[115711] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:184650104 end:184650144 diff:40 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Sep 16 10:01:28 2017
QMNC started with pid=23, OS id=119577
Completed: alter database open