Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


当前位置: 首页 » 技术文章 » 内部原理

How to Increase 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)

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状态下可通过以下方法
alter session set events 'immediate trace name adjust_scn level n';
mount状态下可通过
alter session set events '10015 trace name adjust_scn level n';
n是指把SCN推进到n*1024*1024*1024这个值

2.通过_minimum_giga_scn
_minimum_giga_scn(Minimum SCN to start with in 2^30 units),意思是把SCN往前推进到n*1024*1024*1024,在10g可用,11g大部分版本可用
$ 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
SQL> oradebug help peek
PEEK           <addr> <len> [level]      Print/Dump memory
SQL> oradebug help poke
POKE           <addr> <len> <value>      Modify memory
SQL>

oradebug poke <address>    <length>      <value>

                                       \|/                 \|/                    \|/

                             起始地址        长度      需要设置的值

address:内存地址

length长度可为1,2,4,8

value可为10进制,也可以为16进制


**************************************

Oradebug on Linux

*********************************
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>
SQL>

*******************************
Oradebug on AIX
*******************************
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都可以用下列方式修改,这样修改最安全
        oradebug poke 0x7000000100120C0  8  64425561947
                                                  \|/         \|/    \|/  

                                    SCN变量开始地址  长度   需要设置的SCN值,这个值最好用10进制表示, 当然用16进制

                                                                                                  也可以,不过用10进制不易混淆更安全

                                                                                                

                   

Reference

http://www.orafaq.com/papers/oradebug.pdf



关键词:scn 

相关文章

How to Increase SCN
Oracle SCN(一)
Top