背景

在v$transaction会看到scn,其中又分为scn base及scn wrap,这到底怎么回事呢？而且很多ORA报错与SCN有关，如果多了解一些SCN相关的知识，也便于我们
分析解决问题。

结论

1,oradebug poke可以推进SCN，分为在数据库OPEN及MOUNT皆可以
oradebug setmypid

oradebug DUMPvar SGA kcsgscn_

oradebug poke 0x060012658 4 1000

2,关于kcsgscn_变量我是在BAIDU上面查的，而关于SCN到底是对应内存是哪块区域，我是采用多次运行oradebug DUMPvar SGA kcsgscn_，看哪些内存的值在变化，基本就是哪块

3，oradebug poke 0x060012658 4 1000 就是推进SCN的命令，具体含义如下：
oradebug poke 内存地址长度要修改的内容，注意这个要修改的内容必须是十进制，如果是16进制会报上述的错（这里我采用了反向对比思维）,且这里长度是前4个字节

4,如果是在OPEN状态下推进SCN,oradebug DUMPvar SGA kcsgscn_是有值的，而在MOUNT因为数据库没有打开，所在是空的，全是0，那么如何调整SCN，可以基于每个文件头的BLOCK 1的数据结构kscnbas及kscnwrp进行调整
这个数据结构对应select file#,name,checkpoint_change# from v$datafile;
BBED> map
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Dba:0x00000000
------------------------------------------------------------
Data File Header

struct kcvfh, 676 bytes @0

ub4 tailchk @8188

BBED> p kcvfh
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x028f5c58 --scn base
ub2 kscnwrp @488 0x0000 --scn wrap

5,select current_scn from v$database，这个SCN是一直在变化，可以叫作内存SCN

6,scn是由scn base及scn wrap构成的，当scn base达到一定程度，scn wrap则会递增，一般情况下scn wrap是0，不会变化

7,scn base及scn wrap也是数据块中的数据结构，可见scn base是4个字节，而scn wrap是2个字节
也就是说scn base要用4个字节用完，scn wrap就会递增
依理推理，4个字节为 power(2,32),也就是达到这个数据时，scn base就会归0，scn wrap递增1

8，基于 select file#,name,checkpoint_change# from v$datafile;和数据结构的scn base及scn wrap可知scn计算公式为

scn=scn wrap * power(2,32)+scn base

9,进一步引申，也可以计算出为scn最大值，因为scn base及scn wrap是由4个字节及4个字节构成的，而这些字节表示的数据范围是有限的

10,如果过小调整scn远小于checkpoint_change#，会引发ora-600 2662,当然解决也很容易，基于checkpoint_change#调大scn即可
否则2662会引发数据库强制关闭

测试

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

---可见事务也有scn base及scn wrap的概念
SQL> desc v$transaction;
Name Null? Type
----------------- -------- ------------
ADDR RAW(8)
XIDUSN NUMBER
XIDSLOT NUMBER
XIDSQN NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBASQN NUMBER
UBAREC NUMBER
STATUS VARCHAR2(16)
START_TIME VARCHAR2(20)
START_SCNB NUMBER --scn base
START_SCNW NUMBER --scn wrap
START_UEXT NUMBER
START_UBAFIL NUMBER
START_UBABLK NUMBER
START_UBASQN NUMBER
START_UBAREC NUMBER
SES_ADDR RAW(8)
FLAG NUMBER
SPACE VARCHAR2(3)
RECURSIVE VARCHAR2(3)
NOUNDO VARCHAR2(3)
PTX VARCHAR2(3)
NAME VARCHAR2(256
)
PRV_XIDUSN NUMBER
PRV_XIDSLT NUMBER
PRV_XIDSQN NUMBER
PTX_XIDUSN NUMBER
PTX_XIDSLT NUMBER
PTX_XIDSQN NUMBER
DSCN-B NUMBER --scn base
DSCN-W NUMBER --scn wrap
USED_UBLK NUMBER
USED_UREC NUMBER
LOG_IO NUMBER
PHY_IO NUMBER
CR_GET NUMBER
CR_CHANGE NUMBER
START_DATE DATE
DSCN_BASE NUMBER --scn base
DSCN_WRAP NUMBER --scn wrap
START_SCN NUMBER
DEPENDENT_SCN NUMBER
XID RAW(8)
PRV_XID RAW(8)
PTX_XID RAW(8)

---普通数据块也有scn base及scn wrap的概念,且scn base为4个字节，scn wrap为2个字节
BBED> map
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 12 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 72 bytes @20

struct kdbh, 14 bytes @100

struct kdbt[1], 4 bytes @114

sb2 kdbr[32] @118

ub1 freespace[7814] @182

ub1 rowdata[192] @7996

ub4 tailchk @8188

BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x0100000c
ub4 bas_kcbh @8 0x00048e91 --scn base
ub2 wrp_kcbh @12 0x0000 --scn wrap
ub1 seq_kcbh @14 0x02
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0x7e45
ub2 spare3_kcbh @18 0x0000

BBED>

---数据文件头也有scn base及scn wrap的概念
BBED> map
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Dba:0x00000000
------------------------------------------------------------
Data File Header

struct kcvfh, 676 bytes @0

ub4 tailchk @8188

BBED> p kcvfh
struct kcvfh, 676 bytes @0
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01000001
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000

从上述我们发现基本scn wrap全是0，那么何时scn会变成非0呢，我想到了推进SCN,然后对比DUMP数据块，可以了其原理了

SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
311981 296387

SQL> alter session set events 'immediate trace name adjust_scn level 1';

Session altered.

SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
311996 296387

可见上述推进SCN太慢了
SQL> select 311996-311981 from dual;

311996-311981
-------------
15

---且ALERT会报权限不足的错误信息
Mon Nov 30 23:46:36 EST 2015
Errors in file /home/ora10g/admin/asia/udump/asia_ora_23849.trc:
ORA-01031: insufficient privileges

---上述推进SCN方法太慢，我们尝试另一种快速推进SCN的方法，不过要重启库到MOUNT状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 901775360 bytes
Fixed Size 2100424 bytes
Variable Size 226493240 bytes
Database Buffers 666894336 bytes
Redo Buffers 6287360 bytes
Database mounted.

---可见MOUNT状态下CURRENT_SCN为0，其它SCN列皆有值
SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
0 312204

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

FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /home/ora10g/asia/asia/system01.dbf 312204
2 /home/ora10g/asia/asia/undotbs01.dbf 312204
3 /home/ora10g/asia/asia/sysaux01.dbf 312204
4 /home/ora10g/asia/asia/users01.dbf 312204

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 312204
2 312204
3 312204
4 312204

SQL> select to_char('312204','xxxxxxx') from dual;

TO_CHAR(
--------
4c38c

---bbed查看数据文件的SCN，可知是4c38c

struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0004c38c
ub2 kscnwrp @488 0x0000

---用10015事件推进SCN发现没有变化,可见在MOUNT状态下不生效推进SCN
SQL> alter session set events '10015 trace name adjust_scn level 1';

Session altered.

SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
0 312204

打开数据库看看有无生效，也是没有效果
SQL> alter database open;

Database altered.

SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
312409 312207

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

FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /home/ora10g/asia/asia/system01.dbf 312207
2 /home/ora10g/asia/asia/undotbs01.dbf 312207
3 /home/ora10g/asia/asia/sysaux01.dbf 312207
4 /home/ora10g/asia/asia/users01.dbf 312207

---换另一种方法，用ORADEBUG POKE推进SCN
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 901775360 bytes
Fixed Size 2100424 bytes
Variable Size 226493240 bytes
Database Buffers 666894336 bytes
Redo Buffers 6287360 bytes
Database mounted.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [060012658, 060012688) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60012338 00000000
SQL>

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

FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /home/ora10g/asia/asia/system01.dbf 312714
2 /home/ora10g/asia/asia/undotbs01.dbf 312714
3 /home/ora10g/asia/asia/sysaux01.dbf 312714
4 /home/ora10g/asia/asia/users01.dbf 312714

SQL> select to_char('312714','xxxxxx') from dual;

TO_CHAR
-------
4c58a

SQL> oradebug poke 0x060012658 8 0x0004c58a
BEFORE: [060012658, 060012660) = 00000000 00000000
AFTER: [060012658, 060012660) = 0004C58A 00000000
SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 0004C58A 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60012338 00000000
SQL>

SQL> alter database open;

Database altered.

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

FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /home/ora10g/asia/asia/system01.dbf 312717
2 /home/ora10g/asia/asia/undotbs01.dbf 312717
3 /home/ora10g/asia/asia/sysaux01.dbf 312717
4 /home/ora10g/asia/asia/users01.dbf 312717

好像没起作用
SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
312891 312717

---换个思路，试试在数据库打开时推进SCN
SQL> select file#,name,checkpoint_change# from v$datafile;

FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /home/ora10g/asia/asia/system01.dbf 313429
2 /home/ora10g/asia/asia/undotbs01.dbf 313429
3 /home/ora10g/asia/asia/sysaux01.dbf 313429
4 /home/ora10g/asia/asia/users01.dbf 313429

SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
313917 313429

SQL> select to_char('313917','xxxxxxxxxx') from dual;

TO_CHAR('31
-----------
4ca3d

---经间隔多次运行如下ORADEBUG命令，我分析CURRENT_SCN就是内存中的第1部分（因为就这部分信息在变化，其它信息是固定的）
SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [060012658, 060012688) = 0004CAD4 00000000 00000000 00000000 00000113 00000000 00000000 00000000 00000000 00000000 60012338 00000000
SQL>
SQL>
SQL>
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [060012658, 060012688) = 0004CAD6 00000000 00000000 00000000 00000114 00000000 00000000 00000000 00000000 00000000 60012338 00000000
SQL>
SQL>
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [060012658, 060012688) = 0004CAD7 00000000 00000000 00000000 00000115 00000000 00000000 00000000 00000000 00000000 60012338 00000000
SQL>

---所以我们只要改第1部分信息的内容即可
SQL> oradebug poke 0x060012658 4 FFFFA3523
ORA-01858: a non-numeric character was found where a numeric was expected
SQL>

可见oradeug poke 的含义是oradebug poke 内存地址长度要修改的内容，注意这个要修改的内容必须是十进制，如果是16进制会报上述的错（这里我采用了反向对比思维）,且这里长度是前4个字节
SQL> oradebug poke 0x060012658 4 1000
BEFORE: [060012658, 06001265C) = 0004CB41
AFTER: [060012658, 06001265C) = 000003E8
SQL> select to_number('3E8','xxxxxxxxx') from dual;

TO_NUMBER('3E8','XXXXXXXXX')
----------------------------
1000

---而且上述由于把SCN改得过小，小于数据文件及控制文件的SCN,会报ORA-600 的2662错误，处理很简单，快速基于checkpoint_change#把SCN变大即可，否则过会数据库就会DOWN机
SQL> select current_scn,checkpoint_change# from v$database;
select current_scn,checkpoint_change# from v$database
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2662], [0], [71], [0], [333548],
[0], [], []

---基于上述分析，因为原来SCN是313917，我们加到19999999999

SQL> oradebug setmypid
Statement processed.
SQL> select power(2,32) from dual;

POWER(2,32)
-----------
4294967296

如果把SCN调整为上述的值，马上ALAERT会报错
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [060012658, 060012688) = 000565AE 00000000 00000000 00000000 00000075 00000000 00000000 00000000 00000000 00000000 60012338 00000000
SQL> oradebug poke 0x060012658 4 4294967296
BEFORE: [060012658, 06001265C) = 000565B3
AFTER: [060012658, 06001265C) = 00000000

Tue Dec 01 01:27:33 EST 2015
Errors in file /home/ora10g/admin/asia/bdump/asia_cjq0_27711.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08176: consistent read failure; rollback data not available

--所以可见SCN也不能随意去调太大，ORACLE内部肯定有个控制算法的，否则会把库搞DOWN掉
SQL> oradebug poke 0x060012658 4 42949670
BEFORE: [060012658, 06001265C) = 00000003
AFTER: [060012658, 06001265C) = 028F5C26

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

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

FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /home/ora10g/asia/asia/system01.dbf 42949720
2 /home/ora10g/asia/asia/undotbs01.dbf 42949720
3 /home/ora10g/asia/asia/sysaux01.dbf 42949720
4 /home/ora10g/asia/asia/users01.dbf 42949720

---可见SCN已经调整为指定的SCN了
SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
42949724 42949720

---换个思路继糿研究SCN BASE及SCN WRAP，如果我一直增加SCN BASE，SCN WRAP会有变化呢，我理解肯定是SCN BASE大到一定程度，SCN WRAP就会有变化
BBED> map
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Dba:0x00000000
------------------------------------------------------------
Data File Header

struct kcvfh, 676 bytes @0

ub4 tailchk @8188

BBED> p kcvfh
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x028f5c58 --scn base
ub2 kscnwrp @488 0x0000

SQL> select to_number('&x','xxxxxxxx') from dual;
Enter value for x: 28f5c58
old 1: select to_number('&x','xxxxxxxx') from dual
new 1: select to_number('28f5c58','xxxxxxxx') from dual

TO_NUMBER('28F5C58','XXXXXXXX')
-------------------------------
42949720

BBED> set offset 484
OFFSET 484

BBED> dump count 5
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Offsets: 484 to 488 Dba:0x00000000
------------------------------------------------------------------------
585c8f02 00

<32 bytes="" per="" line="">

SQL> select power(2,31) from dual;

POWER(2,31)
-----------
2147483648

SQL> select to_char('2147483648','xxxxxxxxx') from dual;

TO_CHAR('2
----------
80000000

BBED> modify /x 00000080
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Offsets: 484 to 488 Dba:0x00000000
------------------------------------------------------------------------
00000080 00

<32 bytes="" per="" line="">

--还要调整tailchk
可以看到tailchk校验由ub4 bas_kcbh的低4位+ub1 type_kcbh+ub1 seq_kcbh

BBED> p tailchk
ub4 tailchk @8188 0x00000b01

ub4 bas_kcbh @8 0x00000000

ub1 type_kcbh @0 0x0b

ub1 seq_kcbh @14 0x01

BBED> sum apply
Check value for File 0, Block 1:
current = 0x2dac, required = 0x2dac

---调整后
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x80000000
ub2 kscnwrp @488 0x0000

BBED> p tailchk
ub4 tailchk @8188 0x00000b01

SQL> conn scott/system
Connected.
SQL> create table t_modafter(a int);

Table created.

SQL> insert into t_modafter values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> conn /as sysdba
Connected.
SQL> alter system checkpoint;

System altered.

SQL> alter system flush buffer_cache;

System altered.

---调整scn base到最大值-1
SQL> select power(2,32)-1 from dual;

POWER(2,32)-1
-------------
4294967295

SQL> select to_char('4294967295','xxxxxxxxxx') from dual;

TO_CHAR('42
-----------
ffffffff

---报错可以分2步进行，即先2个字节进行，然后再把余下的2个字节修改完
BBED> modify /x ffffffff
BBED-00209: invalid number (ffffffff)

BBED> modify /x ffff
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Offsets: 484 to 488 Dba:0x00000000
------------------------------------------------------------------------
ffff0080 00

<32 bytes="" per="" line="">

BBED> set offset 486
OFFSET 486

BBED> dump
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Offsets: 486 to 488 Dba:0x00000000
------------------------------------------------------------------------
008000

<32 bytes="" per="" line="">

BBED> modify /x ffff
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Offsets: 486 to 488 Dba:0x00000000
------------------------------------------------------------------------
ffff00

<32 bytes="" per="" line="">

BBED> dump
File: /home/ora10g/asia/asia/users01.dbf (0)
Block: 1 Offsets: 484 to 486 Dba:0x00000000
------------------------------------------------------------------------
ffffff

<32 bytes="" per="" line="">

BBED> p kcvfh
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0xffffffff scn_base已经调整为 power(2,32)-1 ,其中32代表4个字节，每个字节byte即8bit,所以就是32个bit,可以表示的数据即power(2,32)
ub2 kscnwrp @488 0x0000

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system checkpoint;
System altered.

BBED> p kcvfh

struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00000002
ub2 kscnwrp @488 0x0001 ---看到没，SCN WRAP有值了，哈哈说明，SCN BASE大到一定程度，它就有值了

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

FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 /home/ora10g/asia/asia/system01.dbf 4294967298
2 /home/ora10g/asia/asia/undotbs01.dbf 4294967298
3 /home/ora10g/asia/asia/sysaux01.dbf 4294967298
4 /home/ora10g/asia/asia/users01.dbf 4294967298

可见scn=scn wrap * power(2,32)+scn base
SQL> select 1*4294967296+2 from dual;

1*4294967296+2
--------------
4294967298

也就是说scn wrap有值时，scn又开始从0开始计数，由此可见ORACLE设计的精妙所在，再深入一些，也可以知道SCN最大值是什么，哈哈

ITPub博客

由oradebug poke推进scn理解scn base及scn wrap系列一

背景

结论

测试

最新文章