[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] Arg [d] Arg [d]

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

--估计不能一下子提高太多，放弃测试。