oradebug 推进SCN

朱雀 2022-08-13 00:49 145阅读 0赞

1 linux 32位

[oracle@lx01 ~]$ uname -a
    Linux lx01 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:43 EDT 2010 i686 i686 i386 GNU/Linux
    SYS@fyl>select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SYS@fyl>select to_char(current_scn,'XXXXXXXXXXXX') from v$database;
    TO_CHAR(CURRE
    -------------
        10000765F
    
    SYS@fyl>select to_number('10000765F','XXXXXXXXXXXX') from dual;
    TO_NUMBER('10000765F','XXXXXXXXXXXX')
    -------------------------------------
                               4294997599
    
    SYS@fyl>oradebug setmypid
    Statement processed.
    SYS@fyl>oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [2000D260, 2000D280) = 00000001 00007651 000009EB 00000000 00000000 00000000 00000000 2000D06C
    SYS@fyl>
    SYS@fyl>oradebug poke 0x2000D260 4 0x000000002
    BEFORE: [2000D260, 2000D264) = 00000001
    AFTER:  [2000D260, 2000D264) = 00000002
    SYS@fyl>oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [2000D260, 2000D280) = 00000002 00007677 00000A0C 00000000 00000000 00000000 00000000 2000D06C
    
    SYS@fyl>select to_char(current_scn,'XXXXXXXXXXXX') from v$database;
    TO_CHAR(CURRE
    -------------
        20000767C
    
    SYS@fyl>select current_scn from v$database;
    CURRENT_SCN
    -----------
     8589964953
    
    我这里把SCN wrap推高了一位，即把SCN提高了2的32次方=2*2*1024*1024*1024=4294967296
    修改后相差
    SYS@fyl>select 8589964953-4294997599 from dual;
    8589964953-4294997599
    ---------------------
               4294967354
    
    修改SCN base数值
    SYS@fyl>select to_char(checkpoint_change#,'XXXXXXXXXXXXXXXX') from v$database;
    TO_CHAR(CHECKPOIN
    -----------------
            41000538C
    SYS@fyl>oradebug setmypid
    Statement processed.
    SYS@fyl>oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [2000D260, 2000D280) = 00000004 10005429 00000049 00000000 00000000 00000000 00000000 2000D06C
    SYS@fyl>oradebug poke 0x2000D264 4 0x10010000
    BEFORE: [2000D264, 2000D268) = 10005453
    AFTER:  [2000D264, 2000D268) = 10010000
    SYS@fyl>oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [2000D260, 2000D280) = 00000004 10010004 00000074 00000000 00000000 00000000 00000000 2000D06C
    
    SYS@fyl>select to_char(checkpoint_change#,'XXXXXXXXXXXXXXXX') from v$database;
    TO_CHAR(CHECKPOIN
    -----------------
            41001000F

大小端可以参考oracle视图

SYS@fyl>select PLATFORM_ID,PLATFORM_NAME from v$database;
    
    PLATFORM_ID PLATFORM_NAME
    ----------- -----------------------------------------------------------------------------------------------------
             10 Linux IA (32-bit)
    
    SYS@fyl>select * from v$transportable_platform;
    
    PLATFORM_ID PLATFORM_NAME                                                                                         ENDIAN_FORMAT
    ----------- ----------------------------------------------------------------------------------------------------- --------------
              1 Solaris[tm] OE (32-bit)                                                                               Big
              2 Solaris[tm] OE (64-bit)                                                                               Big
              7 Microsoft Windows IA (32-bit)                                                                         Little
             10 Linux IA (32-bit)                                                                                     Little
              6 AIX-Based Systems (64-bit)                                                                            Big
              3 HP-UX (64-bit)                                                                                        Big
              5 HP Tru64 UNIX                                                                                         Little
              4 HP-UX IA (64-bit)                                                                                     Big
             11 Linux IA (64-bit)                                                                                     Little
             15 HP Open VMS                                                                                           Little
              8 Microsoft Windows IA (64-bit)                                                                         Little
              9 IBM zSeries Based Linux                                                                               Big
             13 Linux x86 64-bit                                                                                      Little
             16 Apple Mac OS                                                                                          Big
             12 Microsoft Windows x86 64-bit                                                                          Little
             17 Solaris Operating System (x86)                                                                        Little
             18 IBM Power Based Linux                                                                                 Big
             19 HP IA Open VMS                                                                                        Little
             20 Solaris Operating System (x86-64)                                                                     Little
             21 Apple Mac OS (x86-64)                                                                                 Little
    
    20 rows selected.

其他测试  
测试环境1：  
solaris 10 sparc  
大端序  
前4字节表示SCN WRAP:00000000   
5～8字节表示SCN BASE:0002FB37

SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [380016528, 380016558) = 00000000 0002FB37 00000000 00000000 0000D5E9 00000000 00000000 00000000 00000000 00000000 00000003 80016208
    SQL> select to_char(checkpoint_change#,'XXXXXXXX') from v$database;
    TO_CHAR(C
    ---------
        3C264
    SQL> select checkpoint_change#  from v$database;
    CHECKPOINT_CHANGE#
    ------------------
                246372
    
    1)设置SCN BASE值
    SQL> oradebug poke 0x380016528 8 0x3C264  8表示修改8个字节的值
    BEFORE: [380016528, 380016530) = 00000000 00000000  （mount数据库时kcsgscn_=0）
    AFTER:  [380016528, 380016530) = 00000000 0003C264   -->更改成功
    
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [380016528, 380016558) = 00000000 0003C264 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000003 80016208
    SQL> oradebug poke 0x380016528 4 0x00000001   --> 4表示修改前4个字节SCN_WRAP，改成1
    BEFORE: [380016528, 38001652C) = 00000000
    AFTER:  [380016528, 38001652C) = 00000001
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [380016528, 380016558) = 00000001 0003C264 --> 最终SCN_WRAP和SCN_BASE都修改成功
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000003 80016208
    
    SQL> alter database open;
    SQL>  select dbms_flashback.get_system_change_number from dual;
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                  4295213826 
    SQL> select to_char(dbms_flashback.get_system_change_number,'XXXXXXXXXXXXXXXX') from dual;
    TO_CHAR(DBMS_FLAS
    -----------------
            10003C30B   --> SCN WRAP跳了一位
    
    实际上用poke 地址 8 0x000000010003C264直接修改也可以
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [380016528, 380016558) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000003 80016208
    SQL> select to_char(checkpoint_change#,'XXXXXXXXXXXXXXXX') from v$database;
    TO_CHAR(CHECKPOIN
    -----------------
            10003C482 
    SQL> oradebug poke 0x380016528 8 0x10003C482
    BEFORE: [380016528, 380016530) = 00000000 00000000
    AFTER:  [380016528, 380016530) = 00000001 0003C482
    本测试说明大端序64位系统，语法
    oradebug poke 地址  8  SCN号

测试环境2：  
linux x86-64  
小端序

SQL> select to_char(current_scn,'XXXXXXXX') from v$database;
    TO_CHAR(CURRENT_SCN,'XXXXXX
    ---------------------------
        B49D9
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [060017008, 060017038) = 000B49DD 00000000 00000000 00000000 000169BF 00000000 00000000 00000000 00000000 00000000 60016CE8 00000000
    
    前4字节是SCN BASE:000B49DD 
    5~8字节是SCN WRAP：00000000 
    
    SQL> oradebug poke 0x060017008 8 0x000B56C400000001   -->值设为SCN_BASE SCN_WRAP
    
    BEFORE: [060017008, 060017010) = 00000000 00000000
    AFTER:  [060017008, 060017010) = 00000001 000B56C4  
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [060017008, 060017038) = 00000001 000B56C4   -->修改的值不对，前4字节成了SCN_WRAP，后4字节是SCN_BASE
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60016CE8 00000000
    SQL> oradebug poke 0x060017008 8 0x00000001000B56C4   -->重新修改，不考虑大小端，直接写SCN号
    BEFORE: [060017008, 060017010) = 00000001 000B56C4
    AFTER:  [060017008, 060017010) = 000B56C4 00000001
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [060017008, 060017038) = 000B56C4 00000001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60016CE8 00000000
    
    SQL> alter database open;
    Database altered.
    SQL> select to_char(current_scn,'XXXXXXXXXXXXXXXX') from v$database;
    TO_CHAR(CURRENT_SCN,'XXXXXXXXXXXXXXXX')
    ---------------------------------------------------
            1000B579F  -->SCN_WRAP跳了1位
    
    测试说明小端序64位系统，修改SCN的方法也是
    oradebug poke 地址  8  SCN号

测试环境3:  
windows 7 32bit  
小端序  
前4字节是SCN\_WRAP  
后4字节是SCN\_BASE

SQL> oradebug setmypid
    已处理的语句
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [59852C8, 59852E8) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 059850C0
    SQL> select to_char(checkpoint_change#,'XXXXXXXXXXXXXXXX') from v$database;
    TO_CHAR(CHECKPOIN
    -----------------
                3378B
    SQL> oradebug poke 0x59852C8 8 0x000000010003378B  --> 32位系统寻址只能是4字节，不能填8
    ORA-00082: 8 的内存大小不在有效集合 [1], [2], [4] 之内SQL>
    SQL> oradebug poke 0x59852C8 4 0x01  -->修改SCN_WRAP值
    BEFORE: [59852C8, 59852CC) = 00000000
    AFTER:  [59852C8, 59852CC) = 00000001
    SQL> oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [59852C8, 59852E8) = 00000001 00000000 00000000 00000000 00000000 00000000 00000000 059850C0
    SQL> oradebug poke 0x59852CC 4 0x0003378B  --->修改SCN_WRAP值，需要把内存地址+4字节
    BEFORE: [59852CC, 59852D0) = 00000000
    AFTER:  [59852CC, 59852D0) = 0003378B
    SQL>  oradebug dumpvar sga kcsgscn_
    kcslf kcsgscn_ [59852C8, 59852E8) = 00000001 0003378B 00000000 00000000 00000000 00000000 00000000 059850C0
    
    SQL> alter database open;
    数据库已更改。
    SQL> select to_char(current_scn,'XXXXXXXXXXXXXXXX') from v$database;
    TO_CHAR(CURRENT_S
    -----------------
            10003381A  --> SCN_WRAP跳了1位。
    
    测试表明对于32位系统，需要分别改SCN_WRAP和SCN_BASE来修改内存中的SCN

参考：

http://www.laoxiong.net/scn-ora-19706-\_external\_scn\_rejection\_threshold\_hours-parameter.html  
http://www.askmaclean.com/archives/how-to-increase-system-change-number-by-manual.html  
http://www.dbsnake.net/how\_to\_dirty\_adjust\_scn.html