oracle检查数据是否错位,检查点和oracle数据库的恢复（一）SCN

最新推荐文章于 2022-08-21 10:32:31 发布

高崖内

最新推荐文章于 2022-08-21 10:32:31 发布

阅读量239

点赞数

文章标签： oracle检查数据是否错位

scn号研究

什么是scn(system change number)号

scn号是ORACLE数据库一个内部时钟，是数据库的历史时间记录，用来衡量ORACLE事务(transaction)发生的顺序，是一个时间概念。 scn号不断增加，可以理解为直至无穷大。

什么时候scn号会增加

scn号会实时增加的，而且只会增加不会减少，反映数据库的更新历史。

怎样查询目前系统最新的scn号

SQL>select dbms_flashback.get_system_change_number from dual; (9i以前)

SQL>select current_scn# from v$database;(10g以后)

scn号的存在什么地方

1. 控制文件中当：

Database checkpoint SCN(系统常规检查点checkpoint对应的scn号，Oracle就把系统检查点的scn存储到控制文件中)SQL> select checkpoint_change# from v$database;

Controlfile checkpoint SCN(增量检查点更新的scn)

Redo checkpoint scn(redo进程记录的检查点信息和Database checkpoint SCN 是一致的)

Resetlogs scn(系统在执行resetlogs操作时的scn号，当数据库系统做resetlogs操作完成之后会把这时scn号记录到控制文件)

SQL>select RESETLOGS_CHANGE# from v$database；Datafile Checkpoint scn(一个检查点动作完成后，Oracle就把每个数据文件的scn单独存放在控制文件中,每个数据文件一个)

SQL> select name, CHECKPOINT_CHANGE# from v$datafile where name ＝'FILENAME';End scn号(ORACLE将End scn号存放在控制文件中，这个scn号用于检查数据库启动过程是否需要做instance recovery,每个数据文件一个)

SQL> select name, LAST_CHANGE# from v$datafile where name ＝'FILENAME';First scn、Next scn(控制文件中包含每个日志文件的low scn和next scn)

在线重做日志select group#,thread#,sequence#,first_change# from v$log;

归档重做日志

select NAME,THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log;

2. 数据文件中(头部)：Start scn(ORACLE将Start scn号存放在数据文件头中，这个scn用于检查数据库启动过程是否需要做media recovery,每个数据文件一个)

SQL> Select name,checkpoint_change# from v$datafile_header where name ＝'FILENAME';

3. 数据文件

4. 日志文件(日志文件头中包含了low scn,next scn,表示该日志文件包含有从low scn到next scn的redo record)

接下来我们实例演示一下

Dump控制文件

Alter session set events 'immediate trace name controlf level 8';

打开一个新的session

Dump数据文件头部Alter session set events 'immediate trace name file_hdrs level 10';

获得处于current状态的日子文件SQL> select l.STATUS,lf.member from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#;

STATUS ---------------- MEMBER -------------------------------------------------------------------------------- INACTIVE +DISKGROUP1/ora/onlinelog/group_3.263.683896045 CURRENT +DISKGROUP1/ora/onlinelog/group_2.262.683896043 INACTIVE +DISKGROUP1/ora/onlinelog/group_1.261.683896043

打开一个新的session

Dump日志文件Alter system dump logfile '+DISKGROUP1/ora/onlinelog/group_2.262.683896043';

注意:

在每次dump文件后都要重新打开一个session才能重新dump下一个文件，在dump文件后用SELECT d.VALUE

|| '/'

|| LOWER (RTRIM (i.INSTANCE, CHR (0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

FROM (SELECT p.spid

FROM v$mystat m, v$session s, v$process p

WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

(SELECT t.INSTANCE

FROM v$thread t, v$parameter v

WHERE v.NAME = 'thread'

AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

(SELECT VALUE

FROM v$parameter

WHERE NAME = 'user_dump_dest') d

/获得dump文件的路径。

控制dump文件节选

第一段

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

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/11/2009 11:07:20

DB Name "ORA"

Database flags = 0x00404001 0x00001000

Controlfile Creation Timestamp 04/11/2009 11:07:20

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0000.00089d08 Resetlogs Timestamp 04/11/2009 11:07:22

此处为Resetlogs scn

Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 12/19/2006 04:16:53

Redo Version: compatible=0xa200300

#Data files = 4, #Online files = 4

Database checkpoint: Thread=1 scn: 0x0000.000a326c

Database checkpoint SCN,系统常规检查点checkpoint对应的scn号，Oracle就把系统检查点的scn存储到控制文件中

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

enabled threads: 01000000 00000000 00000000 00000000

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

Max log members = 3, Max data members = 1

Arch list: Head=2, Tail=2, Force scn: 0x0000.00096e74scn: 0x0000.000a326b

Activation ID: 1302138531

Controlfile Checkpointed at scn: 0x0000.000a32ca 04/17/2009 15:59:53

增量检查点更新的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:11

dirty buffer的数量

low cache rba:(0x8.b85.0) on disk rba:(0x8.bfb.0)

on disk scn: 0x0000.000a340b 04/17/2009 16:10:26

增量检查点会更新low cache rbaresetlogs scn: 0x0000.00089d08 04/11/2009 11:07:22

heartbeat: 684440041 mount id: 1302634282

心跳检验没三秒检查一次，用于验证实例的存活性。

THREAD #2 - status:0x0 flags:0x0 dirty:0

low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)

on disk scn: 0x0000.00000000 01/01/1988 00:00:00

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

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

第二段

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

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.00089d08 04/11/2009 11:07:22

disabled at scn: 0x0000.00000000 01/01/1988 00:00:00

opened at 04/17/2009 15:59:49 by instance ora

Checkpointed at scn: 0x0000.000a326c 04/17/2009 15:59:49

redo进程检查点，这个检查点和Database的检查点信息一致。

thread:1 rba:(0x8.2.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

第三段

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

LOG FILE RECORDS

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

(size = 72, compat size = 72, section max = 16, section in-use = 3,

last-recid= 6, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 10, numrecs = 16)

LOG FILE #1:

(name #3) +DISKGROUP1/ora/onlinelog/group_1.261.683896043

Thread 1 redo log links: forward: 2 backward: 0

siz: 0x19000 seq: 0x00000006 hws: 0x6 bsz: 512 nab: 0x92aa flg: 0x1 dup: 1

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0008e394

Low scn: 0x0000.00096e74 04/14/2009 12:57:09

Next scn: 0x0000.0009de44 04/17/2009 15:17:12

LOG FILE #2:

(name #2) +DISKGROUP1/ora/onlinelog/group_2.262.683896043

Thread 1 redo log links: forward: 3 backward: 1

siz: 0x19000 seq: 0x00000008 hws: 0x2 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0009de44

Low scn: 0x0000.000a326b 04/17/2009 15:59:48

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

日志文件中的first scn和next scn，当前文件的next scn为无穷大

LOG FILE #3:

(name #1) +DISKGROUP1/ora/onlinelog/group_3.263.683896045

Thread 1 redo log links: forward: 0 backward: 2

siz: 0x19000 seq: 0x00000007 hws: 0x5 bsz: 512 nab: 0xa8c flg: 0x1 dup: 1

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00096e74

Low scn: 0x0000.0009de44 04/17/2009 15:17:12

Next scn: 0x0000.000a326b 04/17/2009 15:59:48

第四段

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

DATA FILE RECORDS

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

(size = 428, compat size = 428, section max = 100, section in-use = 4,

last-recid= 7, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 11, numrecs = 100)

DATA FILE #1:

(name #7) +DISKGROUP1/ora/datafile/system.256.683895993

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:63 scn: 0x0000.000a326c 04/17/2009 15:59:49

Stop scn: 0xffff.ffffffff 04/17/2009 15:57:34

checkpoint cnt号：checkpoint的次数

scn:为Datafile Checkpoint scn

stop scn当数据库正常打开时

这个值被设为无穷大。

Creation Checkpointed at scn: 0x0000.00000008 12/19/2006 04:17:12

thread:0 rba:(0x0.0.0)

数据文件dump

DATA FILE #1:

(name #7) +DISKGROUP1/ora/datafile/system.256.683895993

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:63 scn: 0x0000.000a326c 04/17/2009 15:59:49

Stop scn: 0xffff.ffffffff 04/17/2009 15:57:34

Creation Checkpointed at scn: 0x0000.00000008 12/19/2006 04:17:12

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

00000000 00000000 00000000 00000000 00000000 00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

V10 STYLE. FILE HEADER (header前面的信息来自控制文件，后面的来自数据文件头部)Compatibility Vsn = 169870080=0xa200300

Db ID=1302124456=0x4d9cd7a8, Db Name='ORA'

Activation ID=0=0x0

Control Seq=457=0x1c9, File size=61440=0xf000

File Number=1, Blksiz=8192, File Type=3 DATA

Tablespace #0 - SYSTEM rel_fn:1

Creation at scn: 0x0000.00000008 12/19/2006 04:17:12

Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0

reset logs count:0x28c36cea scn: 0x0000.00089d08 reset logs terminal rcv data:0x0 scn: 0x0000.00000000

prev reset logs count:0x245542b5 scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000

recovered at 04/17/2009 15:59:47

status:0x2004 root dba:0x00400179 chkpt cnt: 63 ctl cnt:62

datafile cnt号和control file cnt号begin-hot-backup file size: 0

Checkpointed at scn: 0x0000.000a326c 04/17/2009 15:59:49begin scn号 thread:1 rba:(0x8.2.10)

日志文件dump文件DUMP OF REDO FROM FILE '+DISKGROUP1/ora/onlinelog/group_2.262.683896043'

Opcodes *.*

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

SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff

Times: creation thru eternity

FILE HEADER:

Compatibility Vsn = 169870080=0xa200300

Db ID=1302124456=0x4d9cd7a8, Db Name='ORA'

Activation ID=1302138531=0x4d9d0ea3

Control Seq=457=0x1c9, File size=102400=0x19000

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

descrip:"Thread 0001, Seq# 0000000008, SCN 0x0000000a326b-0xffffffffffff"

thread: 1 nab: 0xffffffff seq: 0x00000008 hws: 0x2 eot: 1 dis: 0

resetlogs count: 0x28c36cea scn: 0x0000.00089d08 (564488)

resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

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

prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

Low scn: 0x0000.000a326b (668267) 04/17/2009 15:59:48

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

与控制文件中对应的low scn和next scn Enabled scn: 0x0000.00089d08 (564488) 04/11/2009 11:07:22

Thread closed scn: 0x0000.000a326b (668267) 04/17/2009 15:59:48

Disk cksum: 0x5f9b Calc cksum: 0x5f9b

Terminal recovery stop scn: 0x0000.00000000

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

高崖内

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫