oradebug poke ORA-32521/ORA-32519故障解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：oradebug poke ORA-32521/ORA-32519故障解决

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近有不少朋友咨询12.1.0.2及其以后的版本使用oradebug去修改scn失败,这里做了一个测试正常情况下确实无法修改(oradebug poke报 ORA-32519 或者 ORA-32521) ,这里进行了一系列修改测试最后修改成功.
数据库版本

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

oradebug poke测试

SQL> oradebug setmypid
已处理的语句
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [14C8D6270, 14C8D62A0) = 009EA333 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 4C8D5CF0 00000001
SQL> oradebug poke 0x14C8D6274 4 0x00000001
ORA-32521: 对 ORADEBUG 命令  进行语法分析时出错
--或者该提示
SQL> oradebug setmypid
已处理的语句
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [14C8D6270, 14C8D62A0) = 009EAE3D 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 4C8D5CF0 00000001
SQL> oradebug poke 0x14C8D6274 4 0x0000000a
ORA-32519: 权限不足, 无法执行 ORADEBUG 命令: execution of ORADEBUG commands is disabled for this instance

通过测试确定oradebug正常情况无法执行poke,不是提示ORA-32521就是提示ORA-32519错误导致scn无法修改.

通过一些修改之后oradebug 修改scn

SQL> select dbid, name,open_mode,
  2         created created,
  3         open_mode, log_mode,
  4         checkpoint_change# as checkpoint_change#,
  5         controlfile_type ctl_type,
  6         controlfile_created ctl_created,
  7         controlfile_change# as ctl_change#,
  8         controlfile_time ctl_time,
  9         resetlogs_change# as resetlogs_change#,
 10         resetlogs_time resetlogs_time
 11  from v$database;
      DBID NAME                                                 OPEN_MODE            CREATED        OPEN_MODE
 LOG_MODE
---------- ---------------------------------------------------- -------------------- -------------- ------------
 ------------
CHECKPOINT_CHANGE# CTL_TYP CTL_CREATED    CTL_CHANGE# CTL_TIME       RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ------- -------------- ----------- -------------- ----------------- --------------
1504692401 XIFENFEI                                             READ WRITE           16-8月 -15     READ WRITE
 ARCHIVELOG
          10407853 CURRENT 16-8月 -15        10408361 07-7月 -16                     1 16-8月 -15
SQL> select con_id,file#,checkpoint_change# from v$datafile_header;
    CON_ID      FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
         1          1           10407853
         2          2            9457324
         1          3           10407853
         2          4            9457324
         1          5           10407853
         1          6           10407853
         3          7           10407853
         3          8           10407853
         3          9           10407853
         4         10            9559964
         4         11            9559964
         4         12            9559964
         3         13           10407853
已选择 13 行。
SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 3221225472 bytes
Fixed Size                  3837232 bytes
Variable Size             838861520 bytes
Database Buffers         2365587456 bytes
Redo Buffers               12939264 bytes
数据库装载完毕。
SQL> oradebug setmypid
已处理的语句
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [14BE16270, 14BE162A0) = 009ED5C6 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 4BE15CF0 00000001
SQL> oradebug poke 0x14BE16274 4 0x0001
BEFORE: [14BE16274, 14BE16276) = 0000
AFTER: [14BE16274, 14BE16276) = 0001
SQL> alter database open;
数据库已更改。
SQL> select dbid, name,open_mode,
  2         created created,
  3         open_mode, log_mode,
  4         checkpoint_change# as checkpoint_change#,
  5         controlfile_type ctl_type,
  6         controlfile_created ctl_created,
  7         controlfile_change# as ctl_change#,
  8         controlfile_time ctl_time,
  9         resetlogs_change# as resetlogs_change#,
 10         resetlogs_time resetlogs_time
 11  from v$database;
      DBID NAME                                                 OPEN_MODE            CREATED        OPEN_MODE
 LOG_MODE
---------- ---------------------------------------------------- -------------------- -------------- -----------
 ------------
CHECKPOINT_CHANGE# CTL_TYP CTL_CREATED    CTL_CHANGE# CTL_TIME       RESETLOGS_CHANGE# RESETLOGS_TIME
------------------ ------- -------------- ----------- -------------- ----------------- --------------
1504692401 XIFENFEI                                             READ WRITE           16-8月 -15     READ WRITE
 ARCHIVELOG
        4305478053 CURRENT 16-8月 -15      4305478245 07-7月 -16                     1 16-8月 -15
SQL> select con_id,file#,checkpoint_change# from v$datafile_header;
    CON_ID      FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
         1          1         4305478053
         2          2            9457324
         1          3         4305478053
         2          4            9457324
         1          5         4305478053
         1          6         4305478053
         3          7         4305478053
         3          8         4305478053
         3          9         4305478053
         4         10            9559964
         4         11            9559964
         4         12            9559964
         3         13         4305478053
已选择 13 行。
SQL> select con_id,file#,checkpoint_change# from v$datafile;
    CON_ID      FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
         1          1         4305478053
         2          2            9457324
         1          3         4305478053
         2          4            9457324
         1          5         4305478053
         1          6         4305478053
         3          7         4305478053
         3          8         4305478053
         3          9         4305478053
         4         10            9559964
         4         11            9559964
         4         12            9559964
         3         13         4305478053
已选择 13 行。

通过上述测试证明scn已经被完美修改.证明我们已经具备了不使用bbed的情况下推进12.1.0.2版本的scn问题,为12c的一系列需要推scn的恢复提供完美技术支持.

使用oradebug修改数据库scn

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：使用oradebug修改数据库scn

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

闲着无事看到几篇文章介绍了使用oradebug修改数据库scn的案例,这里也做了两个测试,发现该功能确实很巧妙,通过修改内存中的scn值,然后写入控制文件和数据文件,实现修改scn的方法,不过同样该方法的危害性极大,这里仅供测试使用,生产环境切不可乱使用,可能引起很严重后果
数据库版本信息

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select '惜分飞' XIFENFEI FROM DUAL;
XIFENF
------
惜分飞

在open库中修改scn

SQL> oradebug setmypid
Statement processed.
--查看当前scn
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 0007A09F 00000019 00000000 00000000 00000000 00000000 20009034
SQL>  select CHECKPOINT_CHANGE# a from v$datafile;
         A
----------
    499314
    499314
    499314
    499314
SQL> select dbms_flashback.get_system_change_number a from dual;
         A
----------
    499877
SQL> select to_number('7A09F','xxxxxxxxx') from dual;
TO_NUMBER('7A09F','XXXXXXXXX')
------------------------------
                        499871
--修改内存中scn值(十进制)
SQL>  oradebug poke 0x20009228 4 8
BEFORE: [20009228, 2000922C) = 00000000
AFTER:  [20009228, 2000922C) = 00000008
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000008 0007A0D8 00000052 00000000 00000000 00000000 00000000 20009034
SQL> col a for 999999999999999
SQL> select dbms_flashback.get_system_change_number a from dual;
         A
------------
34360238301
SQL> select to_number('8','xx')*4294967296+to_number('0007A0D8','xxxxxxxx') a from dual;
               A
----------------
     34360238296
--做一个checkpoint为了内存中的scn值写入控制文件和数据文件
SQL>  alter system checkpoint;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile;
               A
----------------
     34360238496
     34360238496
     34360238496
     34360238496
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
               A
----------------
     34360238496
     34360238496
     34360238496
     34360238496

在mount库中修改scn

SQL> startup mount
ORACLE instance started.
Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
--因为数据库是mount状态不能看到scn值
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 20009034
SQL>  col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
               A
----------------
     34360240739
     34360240739
     34360240739
     34360240739
--求出WRAP SCN值
SQL> select 34360240739/4294967296 from dual;
34360240739/4294967296
----------------------
            8.00011697
--修改内存中scn值(十六进制)
SQL> oradebug poke 0x20009228 4 0x0000000a
BEFORE: [20009228, 2000922C) = 00000000
AFTER:  [20009228, 2000922C) = 0000000A
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000000 00000000 00000000 00000000 00000000 00000000 20009034
SQL> alter database open;
Database altered.
SQL> select dbms_flashback.get_system_change_number a from dual
  2  ;
               A
----------------
     42949673074
--注意:使用此种方法修改BASE SCN如果不指定,会从0开始计数
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000077 0000001C 00000000 00000000 00000000 00000000 20009034
SQL> select to_number('A','xx')*4294967296+to_number('00000077','xxxxxxxx') a from dual;
               A
----------------
     42949673079
SQL> alter system checkpoint;
System altered.
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
               A
----------------
     42949673095
     42949673095
     42949673095
     42949673095
SQL> select CHECKPOINT_CHANGE# a from v$datafile;
               A
----------------
     42949673095
     42949673095
     42949673095
     42949673095
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;
               A
----------------
     42949673231
     42949673231
     42949673231
     42949673231

在oradebug推进scn的过程中，需要注意不同平台，不同位数的ORACLE数据库可能推进方式有一定的区别，操作前最好在系统平台位数上进行测试，否则有可能导致恢复后果更加麻烦