how to modify scn with oradebug(windows)?

原创 Roger 2014-10-14

2059

一个朋友测试说windows 7 x64平台无法利用oradebug poke来推进scn。我这里简单测试了一下，是ok的。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

上述信息是我的Oracle 版本，OS为win 7 x64. 下面是我的几个简单测试，供参考！请勿在生产环境测试！

+++修改前8位，包含scn_base和scn_wrap
SQL> startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  2067040 bytes
Variable Size             310379936 bytes
Database Buffers          205520896 bytes
Redo Buffers                6320128 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1957838
         2            1957838
         3            1957838
         4            1957838
         5            1957838
         6            1957838

6 rows selected.

SQL> select to_char('1957888','xxxxxxxx') from dual;

TO_CHAR('
---------
   1de000

SQL> oradebug poke 0x0049c0950 8 0x1de000
BEFORE: [0049C0950, 0049C0958) = 00000000 00000000
AFTER:  [0049C0950, 0049C0958) = 001DE000 00000000
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 001DE000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> alter database open;

Database altered.

SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 001DE0BE 00000000 00000000 00000000 00000028 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1957889
         2            1957889
         3            1957889
         4            1957889
         5            1957889
         6            1957889

6 rows selected.

可以看到，成功利用oradebug poke推进了scn。 SCN值从之前的1957838 增加到1957889.

+++++修改scn_wrap来推进scn

SQL> alter system switch logfile;

System altered.

SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1957889
         2            1957889
         3            1957889
         4            1957889
         5            1957889
         6            1957889

6 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1958109
         2            1958109
         3            1958109
         4            1958109
         5            1958109
         6            1958109

6 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  2067040 bytes
Variable Size             310379936 bytes
Database Buffers          205520896 bytes
Redo Buffers                6320128 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1958282
         2            1958282
         3            1958282
         4            1958282
         5            1958282
         6            1958282

6 rows selected.

SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> oradebug poke 0x0049c0950 4 1
BEFORE: [0049C0950, 0049C0954) = 00000000
AFTER:  [0049C0950, 0049C0954) = 00000001
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 00000001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> alter database open;

Database altered.

SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1958283
         2            1958283
         3            1958283
         4            1958283
         5            1958283
         6            1958283

6 rows selected.

SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 001DE249 00000000 00000000 00000000 0000002A 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000

我们可以看到，虽然前面的scn_wrap修改成功了，但是其实scn 并没有推进成功。看来windows 64位环境不能利用这种方式来推进SCN。

+++++能否使用32位平台的修改方法来直接修改scn_base呢？

SQL> startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  2067040 bytes
Variable Size             310379936 bytes
Database Buffers          205520896 bytes
Redo Buffers                6320128 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1959094
         2            1959094
         3            1959094
         4            1959094
         5            1959094
         6            1959094

6 rows selected.

SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> oradebug poke 0x0049c0950 4 0x0001
BEFORE: [0049C0950, 0049C0954) = 00000000
AFTER:  [0049C0950, 0049C0954) = 00000001
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 00000001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> select to_char(checkpoint_change#,'XXXXXXXXXXXXXXXX') from v$database;

TO_CHAR(CHECKPOIN
-----------------
           1DE4B6

SQL> oradebug poke 0x0049c0954 4 0x001de4b9
BEFORE: [0049C0954, 0049C0958) = 00000000
AFTER:  [0049C0954, 0049C0958) = 001DE4B9
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [0049C0950, 0049C0980) = 00000001 001DE4B9 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 049C0630 0
0000000
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

我们可以看到，利用修改32位平台的方式在这里是行不通的，报错了。我们来看下alert log的错误是什么？

Tue Oct 14 15:46:29 2014
alter database open
Tue Oct 14 15:46:30 2014
Repairing half complete open of thread 1
Tue Oct 14 15:46:30 2014
Errors in file g:\oracle_test\product\10.2.0\admin\roger\bdump\roger_lgwr_6176.trc:
ORA-00600: ??????, ??: [2252], [58553], [1], [], [], [], [], []

Tue Oct 14 15:46:31 2014
Errors in file g:\oracle_test\product\10.2.0\admin\roger\bdump\roger_lgwr_6176.trc:
ORA-00600: ??????, ??: [2252], [58553], [1], [], [], [], [], []

LGWR: terminating instance due to error 470
Tue Oct 14 15:46:31 2014
Errors in file g:\oracle_test\product\10.2.0\admin\roger\bdump\roger_dbw0_2540.trc:
ORA-00470: LGWR ????????

大家非常熟悉的错误，这里不多说了。 测试而已，供参考，偶尔数据恢复时可以利用，请勿在生产环境随意测试！

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Roger's Database Notes（共77篇）

非知名DBA、数据库爱好者、专注于Oracle/MySQL/PostgreSQL/NewSQL/国产数据库等

how to modify scn with oradebug(windows)?

文章被以下合辑收录

评论