通过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