1.创建测试表
SQL> create table shall as select * from dba_objects;
Table created.
2.控制文件转储
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
3.获取跟踪文件
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9332.trc
4.模拟故障异常关闭
SQL> shut abort
ORACLE instance shut down.
5.分析控制文件转储文件
SQL> ho vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_9332.trc
***************************************************************************
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)
04/23/2016 18:33:43
DB Name "ORCL"
Database flags = 0x00404001 0x00001200
Controlfile Creation Timestamp 04/23/2016 18:33:44
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.000d64fe Resetlogs Timestamp 05/15/2016 12:00:34
Prior resetlogs scn: 0x0000.000d638e Prior resetlogs Timestamp 05/15/2016 11:51:11
Redo Version: compatible=0xb200000
#Data files = 5, #Online files = 5
Database checkpoint: Thread=1 scn: 0x0000.000e9485
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
此时记录数据库的检查点SCN是e9485，这是16进制，10进制是955525
继续检查，在检查点进程记录部分，获得如下信息，这里包含low cache RBA和on disk RBA的信息，也记录了dirty buffer的数量是140
***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:140
low cache rba:(0x5.a26c.0) on disk rba:(0x5.e93d.0)
on disk scn: 0x0000.000e98d8 05/22/2016 10:57:48
resetlogs scn: 0x0000.000d64fe 05/15/2016 12:00:34
heartbeat: 912501701 mount id: 1440238633
Flashback log tail log# 8 thread# 1 seq 9 block 1207 byte 0
把这里的RBA信息简单分析一下：
|
|
RBA信息 |
Log Sequence |
Block Number |
|
Low Cache RBA |
0x5.a26c.0 |
0x5 =5 |
a26c = 41580 |
|
On Disk RBA |
0x5.e93d.0 |
0x5 =5 |
e93d = 59709 |
此时on disk scn为e98d8，10进制为956632
6.再次启动数据库
经过上面的分析，可以得到，数据库在启动恢复时，恢从5号日志文件的第41580块恢复至59709块，恢复点由SCN 955525恢复到SCN 956632，下面验证：
SQL> startup
ORACLE instance started.
Total System Global Area 1272213504 bytes
Fixed Size 1336260 bytes
Variable Size 922750012 bytes
Database Buffers 335544320 bytes
Redo Buffers 12582912 bytes
Database mounted.
Database opened.
SQL> select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10338.trc
SQL> ho vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10338.trc
Thread 1 checkpoint: logseq 5, block 41057, scn 955525
cache-low rba: logseq 5, block 41580
on-disk rba: logseq 5, block 59709, scn 956632
start recovery at logseq 5, block 41580, scn 0
*** 2016-05-22 11:21:24.681
Started writing zeroblks thread 1 seq 5 blocks 59709-59716
*** 2016-05-22 11:21:24.682
Completed writing zeroblks thread 1 seq 5
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 12288Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 9064Kb in 0.21s => 42.15 Mb/sec
Longest record: 8Kb, moves: 0/2984 (0%)
Change moves: 3/36 (8%), moved: 0Mb
Longest LWN: 1045Kb, moves: 2/237 (0%), moved: 2Mb
Last redo scn: 0x0000.000e98d7 (956631)
----------------------------------------------
在启动数据库时，进行恢复产生了一个跟踪文件，记录了恢复的过程，恢复从5号日志文件的第41580块恢复至59709块，正是以上数据库关闭之前的RBA地址范围。
数据库恢复的检查点起点是SCN 955525，也就是控制文件中记录的数据库最后完成的检查点，On-Disk RBA的SCN是956632，也和控制文件中记录的On-Disk RBA的SCN完全符合。
启动数据库后，查看一下日志信息，可以看到5号日志文件正是执行恢复的日志文件，其恢复的SCN范围为955525~956632，一个日志就满足了之前恢复的SCN范围，恢复完成之后日志切换，当前使用6号日志：
SQL> select GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------- ------------- ------------
1 4 INACTIVE 927943 949190
2 5 INACTIVE 949190 976634
3 6 CURRENT 976634 2.8147E+14
------------------------------------------------------------------------------------------------------------------------------------------------------- from DBA手记2