[20150624]提升scn.txt
--有时候修复数据库,要避免一些ora-00600错误,要提升数据库的scn。自己花了1点时间google许多资料,做1个总结:
--安全起见,我做了一个冷备份。便于重复测试。
--拿比较常见的ORA-600 [2662]错误:
ORA-600 [2662] "Block SCN is ahead of Current SCN",说明当前数据库的数据块的SCN早于当前的SCN,主要是和存储在UGA变量中的
dependent SCN进行比较,如果当前的SCN小于它,数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数,分别代表不同的含义
ORA-600 [2662] [a] [b] {c} [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg {c} dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
1.一般常用的方法:
一种是在open的状态下:用alter session set events 'IMMEDIATE trace name adjust_scn level n';
一种是在mount状态下:用alter session set events '10015 trace name adjust_scn level n';
--说明以下level的计算,实际上level的单位是1g = 1024*1024*1024 =2^30.
--而1 wrap = 2^32 ,这样1wrap 必须乘以4,相当于 4 level。
--另外我的测试在10.2.0.4的情况下:以上两者方法都没有成功,不知道哪里出错了。
--这样ORA-600 [2662] "Block SCN is ahead of Current SCN"的错误,根据提示计算level的公式如下:
Arg {c}* 4得出一个数值,假设为V_Wrap
如果Arg [d]=0,则V_Wrap值为需要的level
Arg [d] < 1073741824(2^30*1),V_Wrap+1为需要的level
Arg [d] < 2147483648(2^30*2),V_Wrap+2为需要的level
Arg [d] < 3221225472(2^30*3),V_Wrap+3为需要的level
2.修改隐含参数_minimum_giga_scn:
--很简单按照上面计算出来的level。修改参数加入:
*._minimum_giga_scn=level
--顺便做一个测试:
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@test> select dbms_flashback.get_system_change_number ,current_scn from v$database ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ ------------
12884944681 12884944681
select 12884944681,trunc(12884944681/power(2,32)) scn_wrap,mod(12884944681,power(2,32)) scn_base from dual
12884944681 SCN_WRAP SCN_BASE
------------ ------------ ------------
12884944681 3 42793
SYS@test> create pfile='/tmp/inittestx.ora' from spfile ;
File created.
--计算level 3*4+1=13.修改/tmp/inittestx.ora加入
*._minimum_giga_scn=13
--使用这个参数文件启动数据库:
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount pfile=/tmp/inittestx.ora
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> alter database open ;
Database altered.
SYS@test> select dbms_flashback.get_system_change_number ,current_scn from v$database ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ ------------
13958643803 13958643803
select 13958643803,trunc(13958643803/power(2,32)) scn_wrap,mod(13958643803,power(2,32)) scn_base from dual
13958643803 SCN_WRAP SCN_BASE
------------ ------------ ------------
13958643803 3 1073741915
--注意看scn_base 提高了不少.1073741915-42793=1073699122,可以推算加了1G。
3.通过oradebug手工修改SCN:
SYS@test> oradebug setmypid
Statement processed.
SYS@test> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0600122B0, 0600122E0) = 40000097 00000003 00000000 00000000 0000005D 00000000 00000000 00000000 00000000 00000000 60011F90 00000000
~~~~~~~~~~~~~~~~~
--注意看前面的~表示的就是scn。在线提升也是可以的,注意我这里是测试环境,不要在生产系统做这样的动作!
select 13958643905,trunc(13958643905/power(2,32)) scn_wrap,mod(13958643905,power(2,32)) scn_base from dual
13958643905 SCN_WRAP SCN_BASE
------------ ------------ ------------
13958643905 3 1073742017
SYS@test> @&r/10to16 1073742017
10 to 16 HEX REVERSE16
-------------- ------------------
00000400000c1 0xc1000040
SYS@test> oradebug poke 0x0600122B0 4 0x50000097
BEFORE: [0600122B0, 0600122B4) = 40004F6C
AFTER: [0600122B0, 0600122B4) = 50000097
SYS@test> select dbms_flashback.get_system_change_number ,current_scn from v$database ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ ------------
14227079323 14227079323
--注意50000097前要加0x,表示16进制。
--我开始少写了0x
SYS@test> oradebug poke 0x0600122B0 4 50000097
BEFORE: [0600122B0, 0600122B4) = 400000F6
AFTER: [0600122B0, 0600122B4) = 02FAF0E1
SYS@test> select dbms_flashback.get_system_change_number ,current_scn from v$database ;
select dbms_flashback.get_system_change_number ,current_scn from v$database
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2662], [3], [50000102], [3], [1073742025], [0], [], []
--看after的值比前面还小。
SYS@test> select dbms_flashback.get_system_change_number ,current_scn from v$database ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ ------------
14227079323 14227079323
--对比前面提升不少。14227079323 > 13958643905.
4.当然一般在mount状态修改:
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> oradebug setmypid
Statement processed.
SYS@test> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0600122B0, 0600122E0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60011F90 00000000
--这时看到的是scn是0.
SYS@test> select current_scn from v$database ;
CURRENT_SCN
------------
0
--可以通过v$datafile,v$datafile_header来大致确定。我这里直接修改wrap从3->4. 注意地址0x0600122B0+0x4.
SYS@test> oradebug poke 0x0600122B4 4 0x4
BEFORE: [0600122B4, 0600122B8) = 00000000
AFTER: [0600122B4, 0600122B8) = 00000004
SYS@test> alter database open read only ;
Database altered.
SYS@test> select dbms_flashback.get_system_change_number ,current_scn from v$database ;
GET_SYSTEM_CHANGE_NUMBER CURRENT_SCN
------------------------ ------------
17179869191 0
select 17179869191,trunc(17179869191/power(2,32)) scn_wrap,mod(17179869191,power(2,32)) scn_base from dual
17179869191 SCN_WRAP SCN_BASE
------------ ------------ ------------
17179869191 4 7
--我现在在mount状态,看视图v$database的CURRENT_SCN=0,但是通过dbms_flashback.get_system_change_number获得的scn_wrap=4.
5.终极测试:
--测试scn耗尽会出现什么情况:
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> oradebug setmypid
Statement processed.
SYS@test> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0600122B0, 0600122E0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60011F90 00000000
SYS@test> oradebug poke 0x0600122B0 4 0xffffff00
BEFORE: [0600122B0, 0600122B4) = 00000000
AFTER: [0600122B0, 0600122B4) = FFFFFF00
SYS@test> oradebug poke 0x0600122B4 4 0xffffffff
BEFORE: [0600122B4, 0600122B8) = 00000000
AFTER: [0600122B4, 0600122B8) = FFFFFFFF
SYS@test> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0600122B0, 0600122E0) = FFFFFF00 FFFFFFFF 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60011F90 00000000
--这样还差0xff就用完,看看会出现什么情况:
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [2251], [65535], [4294963201], [], [], [], [], []
SYS@test> @&r/10to16 65535
10 to 16 HEX REVERSE16
-------------- ------------------
000000000ffff 0xffff0000
--估计改大了wrap 仅仅占4位。重来。
SYS@test> oradebug setmypid
Statement processed.
SYS@test> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0600122B0, 0600122E0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60011F90 00000000
SYS@test> oradebug poke 0x0600122B0 4 0xffffff00
BEFORE: [0600122B0, 0600122B4) = 00000000
AFTER: [0600122B0, 0600122B4) = FFFFFF00
SYS@test> oradebug poke 0x0600122B4 4 0xffff
BEFORE: [0600122B4, 0600122B8) = 00000000
AFTER: [0600122B4, 0600122B8) = 0000FFFF
SYS@test> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0600122B0, 0600122E0) = FFFFFF00 0000FFFF 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60011F90 00000000
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
--估计不能一下子提高太多,放弃测试。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1710054/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1710054/