闲着无事看到几篇文章介绍了使用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数据库可能推进方式有一定的区别，操作前最好在系统平台位数上进行测试，否则有可能导致恢复后果更加麻烦
加我QQ(107644445)
