ORA-600[2662]與[2252] 以及修改系統SCN

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONEORA-600[2662]與[2252] 以及修改系統SCN

除了處理ORA-600[2662]的那個EVENT，其實還可以通過修改控制檔案來增加SCN

老大告訴我，他以前處理2662時，沒有用那個EVENT，而是通過不斷重啟資料庫解決，每次重啟DB，SCN會大幅度增加一段。如果需要增加的SCN很大，那通過重啟DB來增加SCN太累。這時可以通過修改控制檔案中記錄的SCN來達到激進SCN的目的。今天試驗了下，果然，SCN的最原始的儲存地址是在控制檔案中

測試環境10.2.0.4

l 在ORACLE 10g中，控制檔案塊大小貌似固定為16384，而不是像以前一樣，和資料庫標準塊大小相同

l SCN儲存在控制檔案的DATABASE區域，也就是控制檔案的第1號塊（第2個塊，還有個0號塊是檔案頭）

l DATABASE區域雖然也是成對出現的（1，2號塊），但是其只使用1號塊。2號塊一直是空著

SCN的位置

[root@centos ~]# dd if=/oradata/SOURCE10/control01.ctl bs=16k count=1 skip=1 |od -xv|head -n 20

1+0 records in

1+0 records out

16384 bytes (16 kB) copied, 0.000172962 seconds, 94.7 MB/s

0000000 c215 0000 0001 0000 0000 0000 0000 0401

0000020 bc12 0000 0000 0000 0300 0a20 0b0f 3bc6

0000040 4f53 5255 4543 3031 0f1f 0000 01b4 0000

0000060 4000 0000 0000 0001 0000 0000 0000 0000

0000100 0000 0000 0000 0000 0000 0000 0000 0000

0000120 0000 0000 0000 0000 0000 0000 0000 0000

0000140 7d31 3d60 2491 2a35 5d80 0035 0001 0000

0000160 48b7 2bcf 0000 0000 0000 0000 0000 0000

0001 為 SCN WRAP

5d80 0035 為SCN BASE，這裡是LINUX平臺，正確的順序應該為 00355D80

計算當前SCN為：2^32 * 1 + 0x355D80 = 4294967296 + 3497344 = 4298464640

SQL> SELECT dbms_flashback.get_system_change_number FROM dual;

GET_SYSTEM_CHANGE_NUMBER

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

4298465688

兩個值相近，因為ORACLE也不是SCN一變就寫控制檔案

修改SCN

修改的目標是將SCN WRAP 增加1，也就是SCN增加4294967296左右

[root@centos ~]# hexedit /oradata/SOURCE10/control01.ctl

00000000 00 C2 00 00 00 00 C0 FF 00 00 00 00 00 00 00 00 D2 FB 00 00 ....................

00000014 00 40 00 00 B4 01 00 00 7D 7C 7B 7A A0 81 00 00 00 00 00 00 .@......}|{z........

00000028 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

0000003C 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

00000064 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

00000078 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

0000008C 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

000000A0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

SCN的位置是為1號塊，該塊的起始偏移為16384 = 0x4000，定位到偏移0x4000

00003FFC 00 00 00 00 15 C2 00 00 01 00 00 00 00 00 00 00 00 00 01 04 ....................

00004010 12 BC 00 00 00 00 00 00 00 03 20 0A 0F 0B C6 3B 53 4F 55 52 .......... ....;SOUR

00004024 43 45 31 30 1F 0F 00 00 B4 01 00 00 00 40 00 00 00 00 01 00 CE10.........@......

在往下尋找到SCN的位置

0000404C 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

00004060 31 7D 60 3D 91 24 35 2A 80 5D 35 00 01 00 00 00 B7 48 CF 2B 1}`=.$5*.]5......H.+

00004074 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

01 00 就是SCN WRAP的值，由於平臺的關係，這裡的順序是反的，真實值為0001

修改SCN WRAP為02 00

0000404C 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

00004060 31 7D 60 3D 91 24 35 2A 80 5D 35 00 02 00 00 00 B7 48 CF 2B 1}`=.$5*.]5......H.+

00004074 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ....................

PS:這裡記錄下SCN WRAP的偏移位置：0x406C

同時修改CHECKSUM為0000,以得到正確的CHECKSUM

00003FFC 00 00 00 00 15 C2 00 00 01 00 00 00 00 00 00 00 00 00 01 04 ....................

00004010 00 00 00 00 00 00 00 00 00 03 20 0A 0F 0B C6 3B 53 4F 55 52 .......... ....;SOUR

00004024 43 45 31 30 1F 0F 00 00 B4 01 00 00 00 40 00 00 00 00 01 00 CE10.........@......

現在先不管控制檔案副本，先嚐試啟動DB

SQL> startup

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1266632 bytes

Variable Size 134220856 bytes

Database Buffers 71303168 bytes

Redo Buffers 2924544 bytes

ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)

ORA-00202: control file: '/oradata/SOURCE10/control01.ctl'

控制檔案也有CHECKSUM，並且控制檔案的CHECKSUM不能像資料檔案，置0跳過，所以必須從ALERT日誌中去得到正確的CHECKSUM值

Sun Nov 14 00:45:19 2010

Hex dump of (file 0, block 1) in trace file /oracle10/admin/SOURCE10/udump/source10_ora_28994.trc

Corrupt block relative dba: 0x00000001 (file 0, block 1)

Bad check value found during control file header read

Data in bad block:

type: 21 format: 2 rdba: 0x00000001

last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x00001501

check value in block header: 0x0

computed block checksum: 0xbc11

Sun Nov 14 00:45:19 2010

Errors in file /oracle10/admin/SOURCE10/udump/source10_ora_28994.trc:

ORA-00202: control file: '/oradata/SOURCE10/control01.ctl'

ORA-227 signalled during: ALTER DATABASE MOUNT...

修改CHECKSUM的值，從0x0000 改為 0xbc11

00003FFC 00 00 00 00 15 C2 00 00 01 00 00 00 00 00 00 00 00 00 01 04 ....................

00004010 00 00 00 00 00 00 00 00 00 03 20 0A 0F 0B C6 3B 53 4F 55 52 .......... ....;SOUR

00004024 43 45 31 30 1F 0F 00 00 B4 01 00 00 00 40 00 00 00 00 01 00 CE10.........@......

->

00003FFC 00 00 00 00 15 C2 00 00 01 00 00 00 00 00 00 00 00 00 01 04 ....................

00004010 11 bc 00 00 00 00 00 00 00 03 20 0A 0F 0B C6 3B 53 4F 55 52 .......... ....;SOUR

00004024 43 45 31 30 1F 0F 00 00 B4 01 00 00 00 40 00 00 00 00 01 00 CE10.........@......

儲存退出後，把該控制檔案拷貝來覆蓋其他副本

[oracle10@centos ~]$ cp /oradata/SOURCE10/control01.ctl /oradata/SOURCE10/control02.ctl

[oracle10@centos ~]$ cp /oradata/SOURCE10/control01.ctl /oradata/SOURCE10/control03.ctl

啟動DB

SQL> startup

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1266632 bytes

Variable Size 134220856 bytes

Database Buffers 71303168 bytes

Redo Buffers 2924544 bytes

Database mounted.

Database opened.

驗證：

SQL> SELECT dbms_flashback.get_system_change_number FROM dual;

GET_SYSTEM_CHANGE_NUMBER

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

8593432099

相比以前的4298464640，SCN已經激進了至少2^32

注意，別把SCN改太大，否則會拋ORA-600 [2252]，ORACLE只允許SCN每秒最多增加XX（具體值未知）,從1970年開始算，到現在。在啟動時ORACLE會計劃出允許的SCN的最大值，如果從DB中得到的該值超過了這個最大值，就拋這個錯誤

ORA-600 [2252] Occurs During Alter Database Open [ID 253977.1]

While opening the database, Oracle compares the given SCN value with the reasonable upper limit value calculated based on the system date. If Oracle detects the provided scn is too large, ORA-600[2252] would be raised.

SQL> startup

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1266632 bytes

Variable Size 134220856 bytes

Database Buffers 71303168 bytes

Redo Buffers 2924544 bytes

Database mounted.

ORA-00600: internal error code, arguments: [2252], [39321], [3497422], [], [],

[], [], []

第一個引數：39321 為現在系統的SCN WRAP，第二個沒什麼好說的，就是BASE

將SCN修改小，就能啟動資料庫

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-678184/，如需轉載，請註明出處，否則將追究法律責任。

ORA-600[2662]與[2252] 以及 修改系統SCN

相關文章

ORA-600[2662]與[2252] 以及修改系統SCN