除了处理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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/8242091/viewspace-678184/
扫一扫
2603
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
