通过控制文件修改scn，适合11g,12c版本

 

SQL> set line 132

SQL> set wrap off

SQL> l1

  1* select * from v$version

SQL> r

  1* select * from v$version

BANNER                                                                               CON_I

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

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE    12.1.0.2.0      Production

TNS for 64-bit Windows: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A

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

         8113679

         8113679

         8113679

         8113679

         8113679

SQL> select   checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

           8113679

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> oradebug setmypid

已处理的语句

SQL> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [14BCC4270, 14BCC42A0) = 00000000 00000000 00000000 00000000 00000000 00000

00 00000000 00000000 00000000 4BCC3CF0 00000001

SQL> select to_char('8113800','xxxxxxxxx') from dual;

TO_CHAR('8

----------

   7bce88

SQL> shutdown immediate

ORA-01109: 数据库未打开

已经卸载数据库。

--开始修改控制文件，先修改control01.ctl，将control02.ctl重命名为control02bak.ctl

等control01.ctl修改完成后拷贝一份为control02.ctl，然后启动数据库检查。

通过UE打开：先切换文字模式，查找数据库名字“ZXM”，然后切换为16进制，快速找到位置00004000h开始，将04CA0C右键替换为000000

将71CD7B右键替换为想要的值：88CE7B。 

 

修改结果如下：

 

然后拷贝控制文件。

 

此时可以启动数据库。

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 1677721600 bytes

Fixed Size                  3046368 bytes

Variable Size            1006634016 bytes

Database Buffers          654311424 bytes

Redo Buffers               13729792 bytes

数据库装载完毕。

数据库已经打开。

SQL> oradebug setmypid

已处理的语句

SQL> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [14BCC4270, 14BCC42A0) = 007BCF48 00000000 00000000 00000000 00000000 00000000 000000

00 00000000 00000000 00000000 4BCC3CF0 00000001

SQL> select   checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

           8113803

SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A

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

         8113803

         8113803

         8113803

         8113803

         8113803

SQL> oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [14BCC4270, 14BCC42A0) = 007BD15E 00000000 00000000 00000000 00000000 00000000 000000

00 00000000 00000000 00000000 4BCC3CF0 00000001

SQL> select   checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

           8113803

SQL> select to_char('8113803','xxxxxxxxx') from dual;

TO_CHAR('8

----------

    7bce8b

其它可参考文档：

http://www.xifenfei.com/2014/04/通过修改控制文件scn推进数据库scn.html

http://www.dbsnake.net/how_to_dirty_adjust_scn.html

以正常库控制文件操作的，只改了一处。

[oracle@localhost.localdomain:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 29 18:39:22 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

A
----------
972173
972173
972173
972173

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
972173

使用ue工具打开控制文件，

[root@localhost ecard]# mv control01.ctl control01.ctl.bak
[root@localhost ecard]# rz -E
rz waiting to receive.
[root@localhost ecard]# ls -l
total 1530516
-rw-r--r-- 1 root root 9748480 Sep 29 18:37 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 Sep 29 18:05 control01.ctl.bak
-rw-r-----. 1 oracle oinstall 52429312 Sep 29 06:46 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 Sep 29 18:05 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 Sep 29 06:46 redo03.log
-rw-r-----. 1 oracle oinstall 534781952 Sep 29 18:05 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 775954432 Sep 29 18:05 system01.dbf
-rw-r-----. 1 oracle oinstall 30416896 Sep 27 19:42 temp01.dbf
-rw-r-----. 1 oracle oinstall 73408512 Sep 29 18:05 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Sep 29 18:05 users01.dbf
[root@localhost ecard]# chown oracle.oinstall control01.ctl
[root@localhost ecard]# cd /u01/app/oracle/fast_recovery_area/
[root@localhost fast_recovery_area]# cd ecard/
[root@localhost ecard]# ls
control02.ctl
[root@localhost ecard]# mv control02.ctl control02.ctl.bak
[root@localhost ecard]# cp /u01/app/oracle/oradata/ecard/control01.ctl ./control02.ctl
[root@localhost ecard]# ls -l
total 19040
-rw-r--r-- 1 root root 9748480 Sep 29 18:38 control02.ctl
-rw-r-----. 1 oracle oinstall 9748480 Sep 29 18:05 control02.ctl.bak
[root@localhost ecard]# chown oracle.oinstall control02.ctl
[root@localhost ecard]# su - oracle
Last login: Tue Sep 29 18:05:21 PDT 2020 on pts/1
[oracle@localhost.localdomain:/home/oracle]$ slqplus / as sysdba
bash: slqplus: command not found...
[oracle@localhost.localdomain:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 29 18:39:22 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4259082240 bytes
Fixed Size 2260040 bytes
Variable Size 872416184 bytes
Database Buffers 3372220416 bytes
Redo Buffers 12185600 bytes
Database mounted.
Database opened.