作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。
前面介绍了7种推进SCN方法
（1）event 10015 来增加 scn 的值
（2）隐含参数_minimum_giga_scn 来增加 scn 的值
（3）gdb/dbx 来直接修改内存中的值
（4）使用oradebug poke 直接修改内存中的值
（5）通过修改控制文件来修改 scn 的值
（6）通过修改数据文件头来修改 scn 的值
（7）通过adjust_scn方式来增加SCN
现在来说一下oracle推进SCN方法8：使用event 21307096推进scn
在12.2中，Oracle多了一个新的EVENT 21307096，Oracle 官方文档描述
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 21307096，请勿在生产环境中操作；如果需要在生产环境中操作，提前做好备份。
1、查看当前 SCN 的值
–19.22测试要设置_allow_resetlogs_corruption参数为true，否则不成功
–参考资料：Force Open Database after applying Patch 21307096 ( Doc ID 2674196.1 )
alter system set "_allow_resetlogs_corruption"=TRUE scope=spfile;
--这里查询当前 SCN 的值，主要用于与后面修改后的值做对比。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5100732
2、 修改方法
event=‘21307096 trace name context forever,level N’
计算方式
Lowest_scn+N * 1000000
假设我们要推进1百万，设置level 为1
可以通过 PFILE 文件来增加 EVENT:
cat /tmp/init.ora
event="21307096 trace name context forever, level 1"
或者
alter system set event='21307096 trace name context forever,level 1' scope=spfile;
--把scn推进5百万
SQL> alter system set event='21307096 trace name context forever,level 5' scope=spfile;
System altered.
--event推进是以百万位单位推进，这里level 5即SCN推进500W
--19.22测试要设置_allow_resetlogs_corruption参数为true，否则不成功
alter system set "_allow_resetlogs_corruption"=TRUE scope=spfile;
3、 重启数据库
--重启数据库，使参数生效
SQL> shutdown immediate;
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3707764736 bytes
Fixed Size 2930944 bytes
Variable Size 855639808 bytes
Database Buffers 2835349504 bytes
Redo Buffers 13844480 bytes
Database mounted.
--并用resetlogs方式open
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1789147 generated at 06/03/2024 16:22:21 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORA121/archivelog/2024_06_03/o1_mf_1_9_%u_.ar
c
ORA-00280: change 1789147 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
--OPEN过程会耗时比较长，level的级别越高，耗时约长，具体计算方法见文末。
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
10106627
--推进了约500W
根据LEVEL值不一样，数据库OPEN耗时也不一样，计算公式：
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 !.
col name for a60;
set linesize 400;
select a.file#,
a.name,
(select checkpoint_change# from v$database) system_ckpt_scn,
a.checkpoint_change# df_ckpt_scn,
a.last_change# end_scn,
b.checkpoint_change# start_scn,
b.recover,
a.status
from v$datafile a, v$datafile_header b
where a.file# = b.file#;
FILE# NAME SYSTEM_CKPT_SCN DF_CKPT_SCN END_SCN START_SCN REC STATUS
---------- ------------------------------------------------------------ --------------- ----------- ---------- ---------- --- -------
1 /u01/oradata/BBED19C/system01.dbf 2262818 2262818 2262818 NO SYSTEM
3 /u01/oradata/BBED19C/sysaux01.dbf 2262818 2262818 2262818 NO ONLINE
4 /u01/oradata/BBED19C/undotbs01.dbf 2262818 2262818 2262818 NO ONLINE
7 /u01/oradata/BBED19C/users01.dbf 2262818 2262818 2262818 NO ONLINE
4、 查看 alert 日志
查看 ALERT 日志，可以发现数据库在 OPEN 过程中，消耗了几分钟的时间。此时间可以通过 EVENT 描述中的计算公式来计算。
2024-06-07T15:00:51.307635+08:00
.... (PID:1233): Clearing online redo logfile 1 complete
.... (PID:1233): Clearing online redo logfile 2 complete
Resetting resetlogs activation ID 4100944891 (0xf46f73fb)
Online log /u01/oradata/BBED19C/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oradata/BBED19C/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oradata/BBED19C/redo03.log: Thread 1 Group 3 was previously cleared
2024-06-07T15:00:51.322568+08:00
Setting recovery target incarnation to 7
2024-06-07T15:00:51.328799+08:00
Smart fusion block transfer is disabled:
instance mounted in exclusive mode. <<<<会在这里 HANG 住，根据 LEVEL 的值不同，这个 HANG 的时候也不同。目前这里 LEVEL 值是多少，在这里消耗的时间就是多
2024-06-07T15:00:58.540192+08:00
Endian type of dictionary set to little
2024-06-07T15:00:58.542179+08:00
Assigning activation ID 4100985645 (0xf470132d)
Redo log for group 1, sequence 1 is not located on DAX storage
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/oradata/BBED19C/redo01.log
Successful open of redo thread 1
2024-06-07T15:00:58.561496+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
5、 去掉添加的参数
--打开数据库后，要去掉添加的相关参数
alter system reset event scope=spfile;
alter system reset "_allow_resetlogs_corruption" scope=spfile;
--重启数据库
链接：
史上最全，呕心沥血总结oracle推进SCN方法（一）
史上最全，呕心沥血总结oracle推进SCN方法（二）
史上最全，呕心沥血总结oracle推进SCN方法（三）
史上最全，呕心沥血总结oracle推进SCN方法（四）
史上最全，呕心沥血总结oracle推进SCN方法（五）
史上最全，呕心沥血总结oracle推进SCN方法（六）
史上最全，呕心沥血总结oracle推进SCN方法（七）
