联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
闲着无事看到几篇文章介绍了使用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数据库可能推进方式有一定的区别,操作前最好在系统平台位数上进行测试,否则有可能导致恢复后果更加麻烦
1. scn有两个部分组成wrap scn和base scn,所以第一个问题,你去了解scn的组成
2. 关于a是一个假设值
–求出WRAP SCN值
SQL> select 34360240739/4294967296 from dual;
34360240739/4294967296
———————-
8.00011697
–修改内存中scn值(十六进制)
SQL> oradebug poke 0×20009228 4 0x0000000a
BEFORE: [20009228, 2000922C) = 00000000
AFTER: [20009228, 2000922C) = 0000000A
此处算warp scn是要做什么?另外,修改的scn 0x0000000a 这个值怎么算出来的?
ORADEBUG POKE使用说明