Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


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

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(一)

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle Database Upgrade

Oracle

eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com