oracle推进SCN方法

评论

oracle推进SCN方法

System change number (SCN) is a logical, internal timestamp used by the Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction.
SCN是Oracle数据库内部逻辑上的时钟，是一直持续增长的。数据库中SCN事件，主要是为了满足事务的ACID特性(包括分布式事务)、数据库的恢复等。有时候我们可能需要推进SCN的到一个合适的值去解决一些常见的错误，例如ORA-600 [2662]，ORA-600 [4000]，不同版本有不同的方法，下面是常用的方法：

ACID：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

1
2
3
4
5
ORA-600 [2662]
A data block SCN is ahead of the current SCN,occurs when an SCN is compared to the dependent SCN stored in a UGA variable.If the SCN is less than the dependent SCN then we signal the ORA-600 [2662] internal error.
ORA-600 [4000]
It means that Oracle has tried to find an undo segment number in the dictionary cache and failed.

1.通过adjust_scn

有2种方式修改，此方法在9i数据库可用，10g之后版本不可用
Open状态下可通过以下方法

1
alter session set events 'immediate trace name adjust_scn level n';

mount状态下可通过

1
alter session set events '10015 trace name adjust_scn level n';

n是指把SCN推进到n10241024*1024这个值

2.通过_minimum_giga_scn

_minimum_giga_scn(Minimum SCN to start with in 2^30 units),意思是把SCN往前推进到n10241024*1024，在10g可用，11g大部分版本可用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 30 13:11:55 2016
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile='/tmp/pfile';
ORACLE instance started.
Total System Global Area  612368384 bytes
Fixed Size                  2022800 bytes
Variable Size             239075952 bytes
Database Buffers          364904448 bytes
Redo Buffers                6365184 bytes
Database mounted.
SQL> show parameter scn

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
_minimum_giga_scn                    integer     1 
SQL> select checkpoint_change# from v$database;  
CHECKPOINT_CHANGE#  
------------------             
736493  
SQL> alter database open;        
Database altered.  
SQL> select checkpoint_change# from v$database;  
CHECKPOINT_CHANGE#  
------------------         
1073741825 
SQL>

3.通过oracdebug

1
2
3
4
5
6
7
SQL> oradebug help peek
PEEK           <addr> <len> [level]      Print/Dump memory
SQL> oradebug help poke<br>
POKE           <addr> <len> <value>      Modify memory
SQL>oradebug poke   address length  value 
                   \|/       \|/     \|/
                  起始地址    长度    需要设置的值

address：内存地址
length：长度可为1,2,4,8
value：可为10进制，也可以为16进制

Oradebug on Linux

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SQL> startup mount
ORACLE instance started.
Total System Global Area  313159680 bytes
Fixed Size                  2252824 bytes
Variable Size             167776232 bytes
Database Buffers          138412032 bytes
Redo Buffers                4718592 bytes
Database mounted.
SQL> select checkpoint_change#,current_scn from v$database;
CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
           1052507           0

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL> select to_char(1052507,'xxxxxxxx') from dual;

TO_CHAR(1
---------
   100f5b

SQL> oradebug peek 0x06001AE70 8
[06001AE70, 06001AE78) = 00000000 00000000
SQL> oradebug poke 0x06001AE70 8 2052507        -->修改SCN为2052507，可以是10进制，也可以是16进制
BEFORE: [06001AE70, 06001AE78) = 00000000 00000000
AFTER:  [06001AE70, 06001AE78) = 001F519B 00000000

SQL> select to_char(64425561947,'xxxxxxxxxxxxxxxx') from dual;
TO_CHAR(644255619
-----------------
        f00100f5b
SQL> oradebug poke 0x06001AE70 8 64425561947    -->修改SCN为64425561947，0x000f.00100f5b       
BEFORE: [06001AE70, 06001AE78) = 001F519B 00000000
AFTER:  [06001AE70, 06001AE78) = 00100F5B 0000000F -->Little Endian存储的方式为00100F5B 0000000F，可以看到scn_wrap在后面，scn_base在前

SQL> oradebug peek 0x06001AE70 4 -->查看scn_base的值,地址从0x06001AE70开始
[06001AE70, 06001AE74) = 00100F5B
SQL> oradebug peek 0x06001AE74 4 -->查看scn_wrap的值,地址从0x06001AE74开始
[06001AE74, 06001AE78) = 0000000F
SQL>

Oradebug on AIX

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> startup mount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  2022800 bytes
Variable Size             234881648 bytes
Database Buffers          369098752 bytes
Redo Buffers                6365184 bytes
Database mounted.
SQL>  select checkpoint_change#,current_scn from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
            735435           0

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn
kcslf kcsgscn_ [7000000100120C0, 7000000100120F0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 07000000 ...
SQL> oradebug peek 0x7000000100120C0 8
[7000000100120C0, 7000000100120C8) = 00000000 00000000
SQL> oradebug poke 0x7000000100120C0 8 835435  -->修改SCN为835435
BEFORE: [7000000100120C0, 7000000100120C8) = 00000000 00000000
AFTER:  [7000000100120C0, 7000000100120C8) = 00000000 000CBF6B

SQL> oradebug poke 0x7000000100120C0 8 64425561947 -->修改SCN为64425561947
BEFORE: [7000000100120C0, 7000000100120C8) = 00000000 000CBF6B
AFTER:  [7000000100120C0, 7000000100120C8) = 0000000F 00100F5B  -->Big Endian存储的方式为0000000F 00100F5B，可以看到scn_wrap在前面，scn_base在后
SQL> select to_char(64425561947,'xxxxxxxxxxxx') from dual;

TO_CHAR(64425
-------------
    f00100f5b

SQL>
SQL> oradebug peek 0x7000000100120C0 4    -->查看scn_wrap的值,地址从0x7000000100120C0开始
[7000000100120C0, 7000000100120C4) = 0000000F
SQL> oradebug peek 0x7000000100120C4 4    -->查看scn_base的值,地址从0x7000000100120C4开始
[7000000100120C4, 7000000100120C8) = 00100F5B
SQL>

总结：不论AIX,LINUX,HP,Solaris平台，修改SCN都可以用下列方式修改，这样修改最安全

1
2
3
oradebug   poke   0x7000000100120C0  8     64425561947 
                    \|/              \|/     \|/ 
                SCN变量开始地址      长度   需要设置的SCN值，这个值最好用10进制表示， 当然用16进制也可以，不过用10进制不易混淆更安全

gladlyDBA

个人简介。