oracle事务操作例子,ORACLE事务和实例恢复过程梳理

最近与一个前辈讨论起关于oracle实例恢复的问题，其实以前都研究过，无奈近一年没怎么研究原理性东西，基本大部分都忘光了，所以场面极度尴尬，现在只能好好再来梳理实验一把，并且把mysql的实例恢复也梳理一下。

从oracle 的一个事务说起：

例如当我们发起一个update 语句更改某一行数据，例如更改zabbix.cwdtest 的 table_name='ICOL$' 为'aaxx'。该行记录在14号文件，1835491块

1.首先会经过在share pool中的sql语句的解析过程，这一过程只要是针对sql语法，执行计划这些进行处理，这一部分不细讲。

2.接着，到了sql执行后，数据库从物理文件读出数据行相应的数据库到 buffer cache中(假设此时内存不存在相应的数据块同时不讨论锁的过程)，这一过程也涉及到数据块写到dirty list,并写脏块，为新读取的数据块寻找空闲空间的过程

。

3.同时会分配回滚段并在undo段再保留一份修改前的数据块映像。

以下通过DUMP UNDO 相关 信息来查看。

看到index

是

0x09

的事务槽的

state

为

10

代表事务正在活动，而其他槽是

9

代表事务不活动，

scn

表示务事启动、提交、回滚的

SCN

，事务槽

0x09

的

scn

是

0x0009.01e25a30,

转换之后是

38686317104

。

dba

表示

uba:

第一部分的

undo

块地址，这个

DBA

是

(rollback)

回滚的起始点，也就是说是记录事务修改的最后一条记录所在

UNDO

块的地址。

事物表中0x19

槽的

dba

为

0x0a400495

即

41

号文件的

1173

号块块号这与(与

v$transaction

视图中一致)。

我们在看一下这个前镜像到底是什么？

转储数据块

这里scn表示 最近写入磁盘的SCN号，此时数据块中的scn是

0x0009.01e25beb

，转化之后是

38686317547

。

对于DBWR，每次刷新脏块后，会去维护这个block的SCN号，代表这个block的数据版本。

接着往下看，有本UNDO

块中有

51

条记录：

我们找到第51行记录的对象号是130736，这个正是我们本次事务更改的表。

这里字段值是 49 43 4f 4c 24，通过转换是ICOL$，这正是update前的值。

SQL> select utl_raw.cast_to_varchar2(replace('49 43 4f 4c 24',' ')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(REPLACE('49434F4C24',''))

-------------------------------------------------------------------------------------

ICOL$

而bdba: 0x039c01e3 是该记录对应的数据块地址，该行记录正是在14号文件，1835491块

SQL> select to_number('039c01e3','xxxxxxxxxxx')from dual;

TO_NUMBER('039C01E3','XXXXXXXXXXX')

-----------------------------------

60555747

SQL> select dbms_utility.data_block_address_file(60555747)file#, dbms_utility.data_block_address_block(60555747) block  from dual;

FILE#          BLOCK

------------- -------------

14        1835491

整个与UNDO相关的有几个SCN，我们重新整理下：

undo header 中的SCN:

表示务事启动、提交、回滚的SCN

，事务槽

0x09

的

scn

是

0x0009.01e25a30,

转换之后是

38686317104

。

undo block中scn:

表示

最近写入磁盘的SCN

号，此时数据块中的

scn

是

0x0009.01e25beb

，转化之后是

38686317547

undo block

修改记录行的

SCN:

ctl max scn: 0x0009.01e25b73 转化之后是38686317427

prv tx scn: 0x0009.01e25b75 ，转化之后是38686317429

txn start scn: scn: 0x0000.00000000 ，这里是0 。

而此时redo记录的信息如下：

alter system dump logfile '/opt/app/oracle/oradata/tlvdb/redo1.log';

4..为事务修改数据块，并

在执行更改完成后，针对此数据块的修改也生成redo信息。这里是将该语句所影响的并被读入db buffer中的这些行数据的rowid及要更新的原值和新值及scn等信息从PGA逐条的写入redo log buffer中，

以下分别UPDATE 前后执行alter system dump datafile 14 block 1835491;来dump 出数据块。

执行UPDATE之前：

tab 0, row 0, @0x1e87

tl: 249 fb: --H-FL-- lb: 0x0 cc: 55

col 0: [ 3] 53 59 53

col 1: [ 5] 49 43 4f 4c 24<<<<<

Block dump from disk:

buffer tsn: 7 rdba: 0x039c01e3 (14/1835491)

scn: 0x0009.01e25a2b seq: 0x02 flg: 0x04 tail: 0x5a2b0602

frmt: 0x02 chkval: 0xf28b type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

这里scn: 0x0009.01e25a2b 转换之后是38686317099

执行UPDATE 之后：

tl: 248 fb: --H-FL-- lb: 0x2 cc: 55

col 0: [ 3] 53 59 53

col 1: [ 4] 61 61 78 78<<<<<<<<

Block dump from disk:

buffer tsn: 7 rdba: 0x039c01e3 (14/1835491)

scn: 0x0009.01e25beb seq: 0x01 flg: 0x04 tail: 0x5beb0601

frmt: 0x02 chkval: 0xad91 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

这里scn: 0x0009.01e25beb 转换之后是38686317547

这里说明，当事务中修改了数据块，不管事务有无提交，数据脏块会随着 dbw进程的机制写入数据文件中。

而在 REDO日志中会记录操作的记录，并

记录本次undo操作的信息，即修改前的信息：

REDO RECORD - Thread:1 RBA: 0x000412.00335b66.0010 LEN: 0x01a0 VLD: 0x0d

SCN: 0x0009.01e25beb SUBSCN:  1 07/22/2019 17:53:43

(LWN RBA: 0x000412.00335b66.0010 LEN: 0001 NST: 0001 SCN: 0x0009.01e25beb)

CHANGE #1 TYP:0 CLS:1 AFN:14 DBA:0x039c01e3 OBJ:130736 SCN:0x0009.01e25a2b SEQ:2 OP:11.5 ENC:0 RBL:0

KTB Redo

op: 0x01  ver: 0x01

compat bit: 4 (post-11) padding: 1

op: F  xid:  0x000b.009.0001ba87    uba: 0x0a400495.639a.51

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000  bdba: 0x039c01e3  hdba: 0x039c01e2

itli: 2  ispac: 0  maxfr: 4858

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0

ncol: 55 nnew: 1 size: -1

col  1: [ 4]  61 61 78 78

CHANGE #2 TYP:0 CLS:37 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0009.01e25bb3 SEQ:1 OP:5.2 ENC:0 RBL:0

ktudh redo: slt: 0x0009 sqn: 0x0001ba87 flg: 0x0012 siz: 140 fbi: 0

uba: 0x0a400495.639a.51    pxid:  0x0000.000.00000000

CHANGE #3 TYP:0 CLS:38 AFN:41 DBA:0x0a400495 OBJ:4294967295 SCN:0x0009.01e25bb2 SEQ:1 OP:5.1 ENC:0 RBL:0

ktudb redo: siz: 140 spc: 646 flg: 0x0012 seq: 0x639a rec: 0x51

xid:  0x000b.009.0001ba87

ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 130736 objd: 130736 tsn: 7]

Undo type:  Regular undo        Begin trans    Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

0x00000000  prev ctl uba: 0x0a400495.639a.50

prev ctl max cmt scn:  0x0009.01e25b73  prev tx cmt scn:  0x0009.01e25b75

txn start scn:  0x0000.00000000  logon user: 0  prev brb: 171967629  prev bcl: 0 BuExt idx: 0 flg2: 0

KDO undo record:

KTB Redo

op: 0x03  ver: 0x01

compat bit: 4 (post-11) padding: 1

op: Z

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000  bdba: 0x039c01e3  hdba: 0x039c01e2

itli: 2  ispac: 0  maxfr: 4858

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0

ncol: 55 nnew: 1 size: 1

col  1: [ 5]  49 43 4f 4c 24 《〈〈更改前镜像数据

5.执行commit命令，声明redo log buffer 中的redo处于commit状态，然后修改事务表相应slot，声明事务已提交，最后通知lgwr进程将redo log buffer中的数据写入redo log file。待lgwr进程通知已经写入完成后，向用户发出commit完成的信息。

在事务的过程中涉及到三个进程，CKPT,DBWn,LGWR进程 ：

CKPT：检查点进程(Checkpoint Process)只是更新数据文件的文件首部，以辅助建立检查点的进程

ckpt 触发条件：

什么时候发生normal checkpoint

下面这些操作将会触发checkpoint事件：

·

日志切换，通过ALTER SYSTEM SWITCH LOGFILE。

·

DBA发出checkpoint命令，通过ALTER SYSTEM checkpoint。

·

对数据文件进行热备时，针对该数据文件的checkpoint也会进行，ALTER TABLESPACE TS_NAME BEGIN BACKUP/END BACKUP。

·

当运行ALTER TABLESPACE/DATAFILE READ ONLY的时候。

·

SHUTDOWN命令发出时。

因为每次完全的checkpoint都需要把buffer cache所有的脏块都写入到数据文件中，这样就是产生一个很大的IO消耗，频繁的完全checkpoint操作很对系统的性能有很大的影响，为此Oracle引入的增量checkpoint的概念，buffer cache中的脏块将会按照BCQ队列的顺序持续不断的被写入到磁盘当中，同时CKPT进程将会每3秒中检查DBWn的写入进度并将相应的RBA信息记录到控制文件中。

有了增量checkpoint之后在进行实例恢复的时候就不需要再从崩溃前的那个完全checkpoint开始应用重做日志了，只需要从控制文件中记录的RBA开始进行恢复操作，这样能节省恢复的时间。

发生增量checkpoint的先决条件

·

恢复需求设定

(FAST_START_IO_TARGET/FAST_START_MTTR_TARGET)

·

LOG_checkpoint_INTERVAL参数值

·

LOG_checkpoint_TIMEOUT参数值

·

最小的日志文件大小

·

buffer cache中的脏块的数量

DBWn：数据库块写入器(Database Block Writer)负责将脏块写入磁盘的后台进程。

触发DBWR进程的条件有：

1.DBWR超时，大约3秒

2.系统中没有多余的空缓冲区来存放数据

3.CKPT 进程触发DBWR

4.free buffer waits 40% 触发 dbwr吧lruw脏块写入磁盘

5.关机会触发dbwr写

6.alter system checkpoint 触发dbwr写

7.redo 日志切换 触发dbwr写

8. 表空间 offline /online 触发dbwr写

LGWR：日志写入器(Log Writer)负责将SGA中重做日志缓冲区的内容刷新输出到磁盘。

触发LGWn工作的时点有几个：

1. 用户提交 ，用户提交时必须写LOGFILE.

2. 有1/3重做日志缓冲区未被写入磁盘

3. 有大于1M的重做日志缓冲区未被写入磁盘

4. 3秒超时

5. DBWR 需要写入的数据的SCN大于LGWR记录的SCN，DBWR 触发LGWR写入。

这三个进程都是为了更好地完成一件事：安全高效地实现内存数据块写入数据文件，就是将内存中修改的数据反映到硬盘的数据文件上。

我在事务未提交时做一个刷新buffer cache,此时发现数据块已经被刷新到磁盘文件中，通过以上三个进程的触发条件也可以看出刷新数据块与事务是否提交关系不大。

首先，日志文件的写入是很频繁的。LGWn会不断将日志信息从Log Buffer中写入Online Redo Log；

其次，在日志文件上，可以有三个类型的事务事件。

1、事务结束，已经被commit，之后打过checkpoint检查点。

2、事务结束，已经被commit，之后没有打入checkpint检查点。

3、事务未结束，没有commit。

那么当我有一个事务一直未提交，此时发生断电，数据库直接crash，在重启后ORACLE是如何保证数据一致性呢？

启动数据库时，如果发现有datafile header的START SCN 不等于储存于CONTROLFILE的DATAFILE SCN，表示需要进行介质恢复

。

启动数据库时，如果发现STOP SCN = NULL，表示需要进行crash recovery。

oracle 的实例恢复过程

实例恢复主要经历三个阶段

： cache recovery、open database、transaction recovery

1.

首先

oracle

对比控制文件中检查点

scn

与数据文件头部

scn

，发现不一致

但我开启一个事务，并不作提交时，首先来看控制文件中记录的 SCN

情况：

最近一次完全检查点checkpoint change scn:

DATABASE ENTRY

***************************************************************************

。。。。

Controlfile Creation Timestamp  08/21/2017 11:01:49

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0000.000e2006 Resetlogs Timestamp  08/21/2017 11:01:51

Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp  08/24/2013 11:37:30

Redo Version: compatible=0xb200400

#Data files = 53, #Online files = 53

Database checkpoint: Thread=1 scn: 0x0009.01e35ecd

Threads: #Enabled=1, #Open=1, Head=1, Tail=1

这里是 Database checkpoint: Thread=1 scn: 0x0009.01e35ecd

，转换之后是

38686383821

。

而增量检查点SCN

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:15

low cache rba:(0x413.18481.0)〉〉〉起点  on disk rba:(0x413.1849b.0)〉〉〉终点

on disk scn: 0x0009.01e374ef 07/24/2019 10:23:54

resetlogs scn: 0x0000.000e2006 08/21/2017 11:01:51

heartbeat: 996528373 mount id: 1186014334

low cache rba就是CKPT记录的DBWR写脏块的进度是最近一次完全checkpoint scn 的位置, on disk rba就是LGWR的写进度，

是 lgwr 写日志文件的最末位置的地址

low cache rba 以前的更新的脏块已经写入数据文件，不需要重做, on disk rba以后的日志还没写入到online logfile.所以不需要恢复.

而此时有15个脏块。

on disk scn

表示当前系统最新的

rba

对应的

scn,

由

CKPT

进程每

3

秒跟新一次。如果数据库异常宕机，那么

CRASH RECOVER

时服务器至少要应用到该

SCN

为止。

这里的增量检查点SCN

是

38686389487

，这个

scn

会比全量检查点后的

SCN

大。

控制文件中记录的数据文件 SCN：

DATA FILE #14:

name #18: /opt/app/oracle/oradata/xxxx/xxx

creation size=2097152 block size=8192 status=0xe head=18 tail=18 dup=1

tablespace 7, index=7 krfil=14 prev_file=13

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:1061 scn: 0x0009.01e35ecd 07/24/2019 07:30:51

Stop scn: 0xffff.ffffffff 09/27/2018 09:20:13

Creation Checkpointed at scn:  0x0000.000f4212 08/21/2017 12:03:57

thread:1 rba:(0x8.3f45.10)

这里记录的数据文件的scn

也同样是

38686383821

，而

Stop scn

是无穷大，表示当前数据库是正常打开状态，或者异常关闭状态。

再看数据文件头中SCN：

oradebug dump file_hdrs 1

DATA FILE #14:

name #18: /opt/app/oracle/oradata/xxxx/xxx

creation size=2097152 block size=8192 status=0xe head=18 tail=18 dup=1

tablespace 7, index=7 krfil=14 prev_file=13

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:1061 scn: 0x0009.01e35ecd 07/24/2019 07:30:51

这里记录的数据文件头SCN

与控制文件中记录的一致。

以上这种情况当发现控制文件中数据文件的SCN与数据文件头中记录 的SCN一致，即不会发生介质恢复。

当启动时发现

Stop scn

是无穷大

,

则表示数据库是异常关闭了，需要进行

crash recovery，即回滚。而其中增量检查点的作用就是记录了该从哪个地方回滚到那个地方。

此时，我们

模式

数据库crash ，然后重启：

alter database open

Beginning crash recovery of 1 threads

parallel recovery started with 32 processes

Started redo scan

Completed redo scan

read 16 KB redo, 14 data blocks need recovery

Started redo application at

Thread 1: logseq 1043, block 185196

Recovery of Online Redo Log: Thread 1 Group 2 Seq 1043 Reading mem 0

Mem# 0: /opt/app/oracle/oradata/tlvdb/redo2.log

Completed redo application of 0.01MB

Completed crash recovery at

Thread 1: logseq 1043, block 185228, scn 38686432573《〈〈〈〈〈〈〈〈

14 data blocks read, 14 data blocks written, 16 redo k-bytes read

Wed Jul 24 23:44:50 2019

Thread 1 advanced to log sequence 1044 (thread open)

Thread 1 opened at log sequence 1044

Current log# 3 seq# 1044 mem# 0: /opt/app/oracle/oradata/tlvdb/redo3.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Wed Jul 24 23:44:50 2019

SMON: enabling cache recovery

[39962] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:193329648 end:193329738 diff:90 (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

2.

从最后检查点之后到日志文件尾部将被重新应用到数据文件，同时产生

undo

信息

(

回滚

)

，此阶段也称为

cache recovery

当数据库中有已经COMMIT

但没有写入磁盘的数据块，当数据库

CRASH

后重启，会从控制文件中最近一次完全检查点位置到最后一次增量检查点位置，例如上面从

38686383821

到

38686389487

。

然后到redo日志文件中找到该检查点位置，然后从该检查点位置开始往下到增量检查点位置，应用所有的redo，而其寻找redo条目是从low cache rba到on disk rba，从而在buffer cache里又恢复了实例崩溃那个时间点的状态。这个过程叫做前滚，前滚完毕以后，

buffer cache里既有崩溃时已经提交还没有写入数据文件的脏数据块，也还有事务被突然终止，而导致的既没有提交又没有回滚的事务所弄脏的数据块。

可以把这个过程的redo给dump出来：

SQL> ALTER SYSTEM DUMP LOGFILE '/opt/app/oracle/oradata/tlvdb/redo1.log'  SCN MIN 38686383821  SCN MAX 38686389487;

System altered.

Opcodes *.*

RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff

SCNs: scn: 0x0009.01e35ecd (38686383821) thru scn: 0x0009.01e374ef (38686389487)

Times: creation thru eternity

FILE HEADER:

。。。。

Control Seq=459718=0x703c6, File size=4194304=0x400000

File Number=1, Blksiz=512, File Type=2 LOG

descrip:"Thread 0001, Seq# 0000001042, SCN 0x000901d8f38d-0x000901e35ecd"

thread: 1 nab: 0x384d91 seq: 0x00000412 hws: 0x3 eot: 0 dis: 0

resetlogs count: 0x38c7849f scn: 0x0000.000e2006 (925702)

prev resetlogs count: 0x3121c97a scn: 0x0000.00000001 (1)

Low  scn: 0x0009.01d8f38d (38685701005) 07/08/2019 09:24:12

Next scn: 0x0009.01e35ecd (38686383821) 07/24/2019 07:30:51

Enabled scn: 0x0000.000e2006 (925702) 08/21/2017 11:01:51

Thread closed scn: 0x0009.01d8f38d (38685701005) 07/08/2019 09:24:12

Disk cksum: 0x56ca Calc cksum: 0x56ca

Terminal recovery stop scn: 0x0000.00000000

Terminal recovery  01/01/1988 00:00:00

Most recent redo scn: 0x0000.00000000

Largest LWN: 2395 blocks

3.

数据文件中包含已提交或未提交的数据，尽管存在未提交的数据，此时数据库已经被打开，允许用户连接

,

此时针对未提交的事务被进行回滚

前滚一旦完毕，SMON进程立即打开数据库。但是，这时的数据库中还含有那些中间状态的、既没有提交又没有回滚的脏块，这种脏块是不能存在于数据库中的，因为它们并没有被提交，必须被回滚。打开数据库以后，SMON进程会在后台进行回滚，此时会从Undo空间中寻找到旧版本SCN的数据块信息，来进行SGA中Buffer Cache数据块恢复。

几个疑问：

一，当log buffer 中的数据还没来得及写入redo file,此时数据库crash掉，那这部分丢失redo怎么办？

Oracle 采取在事务提交的时候将和这个事务相关的REDO LOG 数据，包括COMMIT 记录，都必须从LOG BUFFER 中写入REDO LOG 文件，此时事务提交成功的信号才能发送给用户进程。这样便可以确保当已经提交的事务中的部分BUFFER CACHE 还没有被写入数据文件时发生了实例故障，在重启后做实例恢复的时候，也可以通过REDO LOG 的信息，将不一致的数据前滚。

如果某事务未提交，此时产生的REDO仍在LOG  BUFFER中，当数据库突然CRASH,log buffer数据也丢失了，我们可以确认这部分数据是没有提交的，将会被回滚，但是，redo信息没有写入磁盘，不存在了。也没办法通过redo 构造数据块，怎么办？

其实这里是理解错了，未写入redo_file,也未写入db_file,那么发生数据库失败恢复时,数据库将直接丢弃该DML操作,反正该操作尚未commit,丢掉了也没关系，并且因为未写入数据文件，没必要进行构造UNDO回滚。

二、UNDO有没有写缓存情况，如果有UNDO丢失怎么办？

undo 有undo buffer ,从以上可以看出，redo 日志会记录了事务的 redo 和undo信息。所以不用担心

三、实例恢复过程主要使用的四个SCN：

Control file三个地方为：

1、System checkpoint SCN

select checkpoint_change# from v$database;

这里应该有完整检查点的SCN，完整检查点的SCN会更新数据文件及数据文件头的SCN， 增量检查点SCN只在控制文件中更新。

2、Datafile checkpoint SCN

set linesize 400

col name for a50

select name, checkpoint_change# from v$datafile where file#=14;

3、 Stop SCN

select name,last_change# from v$datafile where file#=14;

正常datafile在read-write mode运作下，last_change#一定是null

还有一个SCN在datafile header内

4、 Start SCN

select name,checkpoint_change# from v$datafile_header where file#=14;