背景

在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最大值是什麼，哈哈

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

背景

結論

測試

相關文章