Oracle不同物理文件上的SCN信息

Oracle SCN详解

最新推荐文章于 2025-06-25 19:55:31 发布

转载最新推荐文章于 2025-06-25 19:55:31 发布 · 187 阅读

本文深入解析Oracle数据库中的系统变更号（SCN），涵盖其在不同文件（如在线重做日志、数据文件及控制文件）中的作用与含义。通过具体实例展示了SCN在数据库恢复过程中的关键作用。

SCN-System change number或者又叫做System commit number在Oracle中是扮演很重要的角色。语句的读一致性，数据库的恢复执行都离不开它。

关于Oracle的语句读一致性，在这里不讲解，主要讲解在数据库恢复相关的知识点。

在数据文件头部，控制文件，在线重做日志文件中都包含了SCN的信息。下面说明各个文件中不同的SCN信息-

在线重做日志文件

在logfile中包含了高SCN和低SCN，每个在线重做日志文件的包含的第一个重做对应的SCN就是低SCN，包含的最后一个重做就是高SCN，对于状态是current的日志，高SCN是无穷大的

SQL> col member for a40

SQL> select lf.member,l.first_change#,l.next_change#,l.status from v$log l, v$logfile lf

2 where l.group#=lf.group# ;

MEMBER FIRST_CHANGE# NEXT_CHANGE# STATUS

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

+DATA1/hztest1/redo01.log 9585135 2.8147E+14 CURRENT

+DATA1/hztest1/redo02.log 9476726 9513843 INACTIVE

+DATA1/hztest1/redo03.log 9513843 9585135 INACTIVE

在上面的查询中当前重做日志文件是redo01，可以看到next_change#(高SCN)是无穷，可以通过to_number('fffffff','xxxxxxxxx')得出无穷大就是 2.8147E+14

接下去我们对在线日志进行dump，首先是状态为current的redo01

SQL> oradebug setmypid

Statement processed.

SQL> ALTER SYSTEM DUMP LOGFILE '+DATA1/hztest1/redo01.log';

System altered.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/hztest1/HZTEST11/trace/HZTEST11_ora_8208.trc

*** 2013-05-28 01:37:51.963

Initial buffer sizes: read 1024K, overflow 832K, change 805K

DUMP OF REDO FROM FILE '+DATA1/hztest1/redo01.log'

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 = 186646528=0xb200000

Db ID=3547504780=0xd372a08c, Db Name='HZTEST1'

Activation ID=3558784749=0xd41ebeed

Control Seq=90556=0x161bc, File size=204800=0x32000

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

descrip:"Thread 0001, Seq# 0000000103, SCN 0x0000009241ef-0xffffffffffff"

thread: 1 nab: 0xffffffff seq: 0x00000067 hws: 0x1 eot: 1 dis: 0

resetlogs count: 0x3044f04c scn: 0x0000.005b2fc0 (5976000)

prev resetlogs count: 0x3026c06f scn: 0x0000.00518f2b (5345067)

Low scn: 0x0000.009241ef (9585135) 05/27/2013 22:00:45 --低SCN=9585135

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 --高SCN是无穷大

Enabled scn: 0x0000.005b2fc0 (5976000) 03/11/2013 23:12:12

Thread closed scn: 0x0000.009241ef (9585135) 05/27/2013 22:00:45

Disk cksum: 0x9ed8 Calc cksum: 0x9ed8

Terminal recovery stop scn: 0x0000.00000000

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

Most recent redo scn: 0x0000.00000000

Largest LWN: 0 blocks

End-of-redo stream : No

Unprotected mode

Miscellaneous flags: 0x800000

Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

Zero blocks: 0

Format ID is 2

redo log key is 1d4449a2f618a5cefa9363e8812383d4

redo log key flag is 5

Enabled redo threads: 1

然后是状态为非current的在线日志文件

DUMP OF REDO FROM FILE '+DATA1/hztest1/redo02.log'

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 = 186646528=0xb200000

Db ID=3547504780=0xd372a08c, Db Name='HZTEST1'

Activation ID=3558784749=0xd41ebeed

Control Seq=90131=0x16013, File size=204800=0x32000

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

descrip:"Thread 0001, Seq# 0000000101, SCN 0x000000909a76-0x000000912b73"

thread: 1 nab: 0x2e1dd seq: 0x00000065 hws: 0x2 eot: 0 dis: 0

resetlogs count: 0x3044f04c scn: 0x0000.005b2fc0 (5976000)

prev resetlogs count: 0x3026c06f scn: 0x0000.00518f2b (5345067)

Low scn: 0x0000.00909a76 (9476726) 05/26/2013 06:00:15--低SCN

Next scn: 0x0000.00912b73 (9513843) 05/27/2013 01:26:52--高SCN

Enabled scn: 0x0000.005b2fc0 (5976000) 03/11/2013 23:12:12

Thread closed scn: 0x0000.00909a76 (9476726) 05/26/2013 06:00:15

Disk cksum: 0x14b8 Calc cksum: 0x14b8

Terminal recovery stop scn: 0x0000.00000000

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

Most recent redo scn: 0x0000.00000000

Largest LWN: 599 blocks

End-of-redo stream : No

Unprotected mode

Miscellaneous flags: 0x800000

Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

Zero blocks: 8

Format ID is 2

redo log key is 1de8acf1263d54e07ac6be662ef91d32

redo log key flag is 5

数据文件

数据文件中包含脱机常规SCN和检查点SCN

SQL> select ts#,name, scnwrp,scnbas from ts$;

TS# NAME SCNWRP SCNBAS

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

0 SYSTEM 0 0

1 SYSAUX 0 0

2 UNDOTBS1 0 0

3 TEMP 0 0

4 USERS 0 0

5 TEST 0 0

6 TEST1 0 0

7 TEST2 0 0

8 PAMDATA 0 0

9 JARSDATA 0 0

10 PAMINDEX 0 0

TS# NAME SCNWRP SCNBAS

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

11 JARSINDEX 0 0

12 SECURITY_D 0 0

13 SECURITY_I 0 0

14 TOOLS 0 0

15 SOE2 0 0

16 SOE1 0 0

17 SOE3 0 0

18 TEST3 0 0

19 RMAN_TBS 0 0

20 UTL_TBS 0 0

21 rows selected.

其中SCNBAS就是指的脱机常规SCN，这个时候我们对表空间rman_tbs进行offline

SQL> alter tablespace rman_tbs offline normal;

Tablespace altered.

SQL> select ts#,name, scnwrp,scnbas from ts$;

TS# NAME SCNWRP SCNBAS

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

0 SYSTEM 0 0

1 SYSAUX 0 0

2 UNDOTBS1 0 0

3 TEMP 0 0

4 USERS 0 0

5 TEST 0 0

6 TEST1 0 0

7 TEST2 0 0

8 PAMDATA 0 0

9 JARSDATA 0 0

10 PAMINDEX 0 0

TS# NAME SCNWRP SCNBAS

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

11 JARSINDEX 0 0

12 SECURITY_D 0 0

13 SECURITY_I 0 0

14 TOOLS 0 0

15 SOE2 0 0

16 SOE1 0 0

17 SOE3 0 0

18 TEST3 0 0

19 RMAN_TBS 0 9594322

20 UTL_TBS 0 0

21 rows selected.

我们可以看到当数据文件是online状态的时候，脱机常规SCN是0，当相关的数据文件offline了后，会给他当前的数据库最新的SCN，意味着在该SCN之后，该数据文件不再产生重做。

注意这里的offline选项是normal,如果选项是immediate或者temporary的，脱机常规SCN依旧是0。

检查点SCN代表的是该数据文件最后一次检查点产生的SCN。这个值被记录在数据文件头部，在v$datafile数据字典视图中是字段checkpoint_change#

接下去对数据文件头部进行dump

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump file_hdrs 3;

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/hztest1/HZTEST11/trace/HZTEST11_ora_32337.trc

DATA FILE #16:

name #21: +DATA1/hztest1/datafile/rman01.dbf

creation size=12800 block size=8192 status=0xe head=21 tail=21 dup=1

tablespace 19, index=15 krfil=16 prev_file=0

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

Checkpoint cnt:236 scn: 0x0000.00926789 05/28/2013 02:06:47

Stop scn: 0xffff.ffffffff 05/28/2013 01:58:10

Creation Checkpointed at scn: 0x0000.004f2b46 02/18/2013 01:01:38

thread:1 rba:(0x18d.15085.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000

Offline scn: 0x0000.009265d2 prev_range: 0

Online Checkpointed at scn: 0x0000.00926789 05/28/2013 02:06:47 --检查点SCN

thread:1 rba:(0x67.1a353.10)

查看v$datafile:

SQL> col name for a40

SQL> select file#,name,CHECKPOINT_CHANGE# from v$datafile;

FILE# NAME CHECKPOINT_CHANGE#

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

1 +DATA1/hztest1/datafile/system01.dbf 9594517

2 +DATA1/hztest1/datafile/sysaux01.dbf 9594517

3 +DATA1/hztest1/datafile/undotbs01.dbf 9594517

4 +DATA1/hztest1/datafile/users01.dbf 9594517

5 +DATA1/hztest1/datafile/sysaux02.dbf 9594517

6 +DATA1/hztest1/datafile/test01.dbf 9594517

7 +DATA1/hztest1/datafile/test02.dbf 9594517

8 +DATA1/hztest1/datafile/pamdata01.dbf 9594517

9 +DATA1/hztest1/datafile/jarsdata01.dbf 9594517

10 +DATA1/hztest1/datafile/pamindex01.dbf 9594517

11 +DATA1/hztest1/datafile/jarsindex01.dbf 9594517

FILE# NAME CHECKPOINT_CHANGE#

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

12 +DATA1/hztest1/datafile/security_i01.dbf 9594517

13 +DATA1/hztest1/datafile/security_d01.dbf 9594517

14 +DATA1/hztest1/datafile/users02.dbf 9594517

15 +DATA1/hztest1/datafile/test03.dbf 9594736

16 +DATA1/hztest1/datafile/rman01.dbf 9594761

17 +DATA1/hztest1/datafile/utl01.dbf 9594517

TO_NUMBER('00926789','XXXXXXXXXXXXXXXXXXXXXXXX')

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

9594761

可以看到正好对应

控制文件

控制文件包含结束SCN（stop SCN）。如果数据文件offline，那么会把相应的SCN记录在控制文件中，这就是结束SCN，标志着在此之后该数据文件不再产生重做。该SCN也就是数据文件头部中的脱机常规SCN：

SQL> conn /as sysdba

Connected.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump controlf 12

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/hztest1/HZTEST11/trace/HZTEST11_ora_22076.trc

SQL> !vi /u01/app/oracle/diag/rdbms/hztest1/HZTEST11/trace/HZTEST11_ora_22076.trc

online的时候，数据文件相对应的结束SCN是无穷大：

DATA FILE #16:

name #21: +DATA1/hztest1/datafile/rman01.dbf

creation size=12800 block size=8192 status=0xe head=21 tail=21 dup=1

tablespace 19, index=15 krfil=16 prev_file=0

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

Checkpoint cnt:236 scn: 0x0000.00926789 05/28/2013 02:06:47

Stop scn: 0xffff.ffffffff 05/28/2013 01:58:10 --结束SCN

Creation Checkpointed at scn: 0x0000.004f2b46 02/18/2013 01:01:38

thread:1 rba:(0x18d.15085.10)

如果文件脱机后，变成脱机常规SCN：

SQL> alter tablespace rman_tbs offline normal;

DATA FILE #16:

name #21: +DATA1/hztest1/datafile/rman01.dbf

creation size=12800 block size=8192 status=0x80 head=21 tail=21 dup=1

tablespace 19, index=15 krfil=16 prev_file=0

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

Checkpoint cnt:237 scn: 0x0000.00926d14 05/28/2013 02:38:06

Stop scn: 0x0000.00926d14 05/28/2013 02:38:06

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25525563/viewspace-762544/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25525563/viewspace-762544/