特殊恢复：Oracle 12C通过EVENT来手动增加SCN值

huangtingzhong 发布于 2025-06-28 19:54备份与恢复, 故障处理, 特殊恢复抢沙发

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

引言

在Oracle数据库运维过程中，我们经常会遇到需要增加SCN（System Change Number，系统变更号）的场景。SCN是Oracle数据库中用于标识事务顺序和时间戳的重要机制。当数据库出现某些故障时，比如ORA-01555错误、延迟块清除等问题，我们可能需要手动增加SCN来解决这些问题。

本文将详细介绍12C以后通过Event来增加SCN的常用方式，帮助DBA更好地理解和处理相关故障。

什么是SCN？

SCN（System Change Number）是Oracle数据库中的一个重要概念，它是一个单调递增的数字，用于：

标识数据库中的事务顺序
提供数据库的时间戳功能
确保数据的一致性和可恢复性
支持数据库的读一致性

查看数据库当前的SCN值

SQL> startup
ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size                  8619936 bytes
Variable Size             436209760 bytes
Database Buffers          704643072 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL> 
SQL> 
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    4422688

修改EVENT

这里这个EVENT是一个新的EVENT,在低于12.2平台下面需要打补丁，

event="21307096 trace name context forever, level 3"

下面2个参数是可以不需要的。只有在特殊情况下面才需要的。

_allow_error_simulation=true

下面是关于此event的说明
下面是关于EVENT的说明

Install patch  21307096 and enable the fix with Event 21307096 at level SCN delta.
The SCN delta in million units is with the range of values from 1 to 4095 which increases 
the scn by:
 
    lowest_scn + event level * 1000000
 
Example:  if the lowest datafile checkpoint scn in the database is 990396
and the highest is 992660 then SCN delta is 1; given by (992660 - 990396) / 1000000
 
  event="21307096 trace name context forever, level 1

 Here are some tests in 12.1.0.2 using each level for alter database open resetlogs:
 
  level 1 Elapsed: 00:01:02.35
  level 2 Elapsed: 00:02:16.23
  level 6 Elapsed: 00:06:08.05
 
In general:  based on a 16k per second scn rate (16K/sec) , the open resetlogs time 
would be at least (event level * 1000000 / 16000) seconds. Then level 1 would be at least 
62+ seconds and level 4095 would be 71+ hours !.
****************************************************************************************************************

此EVENT不是直接修改SCN的值，是通过数据库自动增加的机制来实现SCN的增加的，每秒16K，如果LEVEL 很大，那么这里OPEN的事件就会很长。场面红色部分有计算SCN增加需要的事件。

3，启动数据库

这里以新参数文件启动数据库。

SQL> startup mount pfile='/tmp/1234.ora';
ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size                  8619936 bytes
Variable Size             436209760 bytes
Database Buffers          704643072 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4422836 generated at 03/18/2017 07:58:31 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/arch1_1_938937035.dbf
ORA-00280: change 4422836 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7423640   这里看到数据库的SCN增量了300W。

下面查看ALERT日志内容

Completed: ALTER DATABASE   MOUNT
2017-03-18T07:59:40.236101+08:00
ALTER DATABASE RECOVER  database using backup controlfile until cancel  
2017-03-18T07:59:40.236186+08:00
Media Recovery Start
2017-03-18T07:59:40.236823+08:00
Serial Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile until cancel  ...
2017-03-18T07:59:42.034160+08:00
ALTER DATABASE RECOVER    CANCEL  
2017-03-18T07:59:42.040794+08:00
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER    CANCEL  
2017-03-18T07:59:48.188727+08:00
alter database open resetlogs
2017-03-18T07:59:48.189864+08:00
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 4422836 time 
Clearing online redo logfile 1 /oracle/app/oracle/oradata/htz/redo01.log

Clearing online log 1 of thread 1 sequence number 1
Clearing online redo logfile 1 complete
Resetting resetlogs activation ID 1993705771 (0x76d5892b)
Online log /oracle/app/oracle/oradata/htz/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oracle/app/oracle/oradata/htz/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oracle/app/oracle/oradata/htz/redo03.log: Thread 1 Group 3 was previously cleared
2017-03-18T07:59:48.690166+08:00
Setting recovery target incarnation to 4
2017-03-18T07:59:48.693973+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.    
会在这里HANG住，根据LEVEL的值不同，这个HANG的时候也不同。目前这里LEVEL值是多少，在这里消耗的时间就是多少。具体需要多少事件 ，请参考上面event的说明。
2017-03-18T08:02:51.712840+08:00
Endian type of dictionary set to little
2017-03-18T08:02:51.716445+08:00
Assigning activation ID 1993737045 (0x76d60355)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oracle/app/oracle/oradata/htz/redo01.log
Successful open of redo thread 1
2017-03-18T08:02:51.734620+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
2017-03-18T08:02:51.758900+08:00
TT00: Gap Manager starting (PID:9735)
2017-03-18T08:02:51.846445+08:00
Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2017-03-18T08:02:51.887673+08:00
[9714] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:5652874 end:5652906 diff:32 ms (0.0 seconds)
Dictionary check beginning
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
2017-03-18T08:02:52.207519+08:00
AQPC started with pid=38, OS id=9743 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process CJQ0
Completed: alter database open resetlogs
2017-03-18T08:02:52.618086+08:00
CJQ0 started with pid=42, OS id=9751 
2017-03-18T08:02:54.893495+08:00
Shared IO Pool defaulting to 64MB. Trying to get it from Buffer Cache for process 9698.
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================

这里需要注意此方法只适合于能安装21307096补丁的版本，如果是其它的版本，请使用其它的方式修改，我们将在后续的文章中详细的说明。

——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)