oracle怎么推进scn?

很多时候，数据库有故障打不开，需要用到推进scn的技术，这里介绍下12c及以上版本的oracle怎么推进数据库的scn。

经测试发现，数据库mount和open状态下都可以通过此方法推进SCN。

Session1：

查询当前SCN

SQL> select current_scn from v$database;               

CURRENT_SCN

-----------

 2910718245

查询当前SCN转成16进制后的值

SQL> select to_char(2910718245,'xxxxxxxxxxxx') from dual;

TO_CHAR(29107

-------------

     ad7e0925

查询预修改的SCN转换成16进制后的值

SQL> select to_char(3910718245,'xxxxxxxxxxxx') from dual;

TO_CHAR(39107

-------------

     e918d325

SQL>

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dumpvar sga kcsgscn_

kscn8 kcsgscn_ [060017E98, 060017EA0) = AD7E093B 00000000

060017E98是SCN BASE值，我们待会修改的就是他，修改成多少，数据库SCN就是多少

AD7E093B是当前的SCN值，可以理解为060017E98是一个代号x，当前的x等于AD7E093B

Session2:

[oracle@redhat19c11 ~]$ ps -ef|grep LOCAL=YES

oracle    9824  9730  0 Feb22 ?        00:00:01 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle   18621  8636  0 01:18 pts/1    00:00:00 grep --color=auto LOCAL=YES

oracle   20109 20105  0 Feb15 ?        00:00:13 oracletestdb19c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

本次测试库是orcl，因此选9824

[oracle@redhat19c11 ~]$ gdb $ORACLE_HOME/bin/oracle 9824

GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-114.el7

Copyright (C) 2013 Free Software Foundation, Inc.

License GPLv3+: GNU GPL version 3 or later <The GNU General Public License v3.0- GNU Project - Free Software Foundation>

This is free software: you are free to change and redistribute it.

There is NO WARRANTY, to the extent permitted by law.  Type "show copying"

and "show warranty" for details.

This GDB was configured as "x86_64-redhat-linux-gnu".

For bug reporting instructions, please see:

。。。。。。(中间省略)

(gdb) set *((int *) 0x060017E98) = 0xe918d325        --->将SCN BASE修改为刚才查出来的值

(gdb) quit

A debugging session is active.

        Inferior 1 [process 9824] will be detached.

Quit anyway? (y or n) y

Detaching from program: /oracle/app/product/19.3.0/db_1/bin/oracle, process 9824

返回session1查询，修改成功：

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

 3910718287

重启数据库，也正常：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2466250400 bytes

Fixed Size                  9137824 bytes

Variable Size             603979776 bytes

Database Buffers         1845493760 bytes

Redo Buffers                7639040 bytes

Database mounted.

Database opened.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

 3910719415