Oracle实例恢复

Oracle实例恢复原理

 首先从事物说起，当执行update开启一个事物的时候，首先需要在buffer cache中找到可用的块（block）更新数据，然后构造cr块，将update之前的数据放入到undo中，同时会在log buffer内写日志，log buffer内数据每隔3秒通过lgwr进程将往redo log写日志，在这个过程更改的数据还在内存中，产生脏数据，直到dbwr进程将脏数据写入到磁盘，如果脏数据还未写入磁盘，脏数据中包括提交或未提交的，这个时候由于掉掉或其他原因导致数据库意外宕机，主机恢复后重启数据库，Oracle会由SMON进程自动进行实例恢复

 为什么会发生实例恢复，这得提到oracle的SCN，在oracle中存在4个scn，system SCN，datafile SCN，结束SCN和datafile header SCN，在Oracle正常关闭的情况下，数据库会产生完全检查点，会通过DBWR进程将脏块写到磁盘，更新着4个SCN值相同，在非正常关闭的情况下，当数据库启动到mount状态时，oracle会发现last scn并不是等于其它3个SCN， 而是等于NULL，这表示Oracle在shutdown时没有进行checkpoint，下次开机必须进行crash recovery（实例恢复）。

 oracle的SCN什么时候会发生变化，上述在log buffer内写日志的时候，会产生增量检查点，增量检查点并不会去更新数据文件头，以及控制文件中数据库SCN以及数据文件条目的SCN信息，而只是每3秒由CKPT进程去更新控制文件中的low cache rba信息（RBA含义redo block adress），也就是检查点的位置。系统SCN、数据文件SCN、数据文件头部SCN的值真正发生变化的时候是在redo文件由ACTIVE变为INACTIVE时，才会更新，这3个值等于ACTIVE, CURRENT中最老的一个日志文件的头部的first SCN，（redo日志文件各状态解释：CURRENT表示当前正在使用的日志文件，ACTIVE表示日志文件中对应的脏块还没有写到磁盘中，而INACTIVE则表示日志文件中对应的所有脏块都写到了磁盘）

 Oracle实例恢复的起点与终点，以实验为例说明：

1、首先在测试数据库创建测试数据提交，在buffer cache产生脏块
create table test（id number,name varchar2(22)）

insert into test values(1,hh)
commit

2、做一次控制文件转储
SQL alter session set events immediate trace name controlf level 12;

Session altered.

3、非正常关闭数据库
shutdown abort

4、启动数据库，做一次控制文件转储
SQL alter session set events immediate trace name controlf level 12;

Session altered.

5，检查对比两次转储文件内容
第一次转储信息：
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/06/2017 23:27:11
DB Name "TSS"
Database flags = 0x50404001 0x00001200
Controlfile Creation Timestamp 04/06/2017 23:27:11
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00000001 Resetlogs Timestamp 04/06/2017 23:27:11
Prior resetlogs scn: 0x0000.00000000 Prior resetlogs Timestamp 01/01/1988 00:00:00
Redo Version: compatible=0xb200400
#Data files = 8, #Online files = 8
Database checkpoint: Thread=1 scn: 0x0000.0022eb8b
Threads: #Enabled=1, #Open=1, Head=1, Tail=1

可知这时数据库的检查点scn： 0x0000.0022eb8b，转化为10进制：2288523
SQL select to_number(22eb8b,XXXXXXXX) from dual;

TO_NUMBER(22EB8B,XXXXXXXX)

 2288523

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:65
low cache rba:(0x76.ab2.0) on disk rba:(0x76.b43.0)
on disk scn: 0x0000.0022f1cd 10/27/2017 15:17:00
resetlogs scn: 0x0000.00000001 04/06/2017 23:27:11
heartbeat: 958525302 mount id: 2083188020

检查点进程信息记录了low cache rba及on disk rba的值
low cache rba含义解释：0x76表示sequence号，ab2表示块号，on disk rba内值含义相同

所以此时low cache rba对应的sequence为118 ，block号为2738，on disk rba对应的sequence为118 ，block号为2883

数据库启动后转储的控制文件信息
* 2017-10-27 15:18:08.577
* SESSION ID:(16.3) 2017-10-27 15:18:08.577
* CLIENT ID:() 2017-10-27 15:18:08.577
* SERVICE NAME:() 2017-10-27 15:18:08.577
* MODULE NAME:(sqlplus@single (TNS V1-V3)) 2017-10-27 15:18:08.577
* ACTION NAME:() 2017-10-27 15:18:08.577

Thread 1 checkpoint: logseq 118, block 2, scn 2288523
cache-low rba: logseq 118, block 2738

on-disk rba: logseq 118, block 2884, scn 2290131

start recovery at logseq 118, block 2738, scn 0

数据库的alert信息
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 73 KB redo, 65 data blocks need recovery
**Started redo application at
Thread 1: logseq 118, block 2738**
Recovery of Online Redo Log: Thread 1 Group 1 Seq 118 Reading mem 0
Mem# 0: /home/oracle/oradata/tss/redo01.log
Completed redo application of 0.05MB
**Completed crash recovery at
Thread 1: logseq 118, block 2884, scn 2310131**
65 data blocks read, 65 data blocks written, 73 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
Fri Oct 27 15:18:08 2017
ARC0 started with pid=18, OS id=2400
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Oct 27 15:18:09 2017
ARC1 started with pid=19, OS id=2402
Thread 1 advanced to log sequence 119 (thread open)
Fri Oct 27 15:18:09 2017
ARC2 started with pid=20, OS id=2404
Thread 1 opened at log sequence 119
Current log# 2 seq# 119 mem# 0: /home/oracle/oradata/tss/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the no FAL ARCH
ARC1: Becoming the no SRL ARCH
ARC2: Becoming the heartbeat ARCH
Fri Oct 27 15:18:09 2017
ARC3 started with pid=21, OS id=2406
Archived Log entry 98 added for thread 1 sequence 118 ID 0x7b19ed8f dest 1:
[2398] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2048784 end:2048814 diff:30 (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 ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Oct 27 15:18:10 2017
QMNC started with pid=22, OS id=2408
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Completed: ALTER DATABASE OPEN
Fri Oct 27 15:28:10 2017
Starting background process SMCO
Fri Oct 27 15:28:10 2017
SMCO started with pid=26, OS id=2509

 从上述alert log我们可以知道，Oracle做Instance Recovery的起点是logseq 118, block 2738；终点是logseq 118, block 2884

 从第一次控制文件的dump文件我们可以看到控制文件里记录的Low Cache RBA是(0x76.ab2.0，转换过来就是Low Cache RBA的logfile sequence是118，logfile block number是2738。”这和alert log里记录的Instance Recovery的起点一致，即Instance Recovery的起点就是Low Cache RBA；

 数据库启动后控制文件的dump文件信息记录的on-disk rba的 logseq 118, block 2884，与alert日志Completed crash recovery恢复记录的logseq 118, block 2884一致，即Instance Recovery的终点就是on-disk rba

白海生 擅长Oracle，Mysql，DataGuard，GoldenGate，目前在国内某金融支付公司负责Oracle，Mysql，DataGuard，GoldenGate管理运维。