非归档数据库异常恢复一例

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:非归档数据库异常恢复一例

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

由于存储故障,数据库为非归档模式,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)收集信息确认数据库redo异常
2


Thu Mar 28 11:36:13 2019
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Thu Mar 28 11:36:13 2019
Errors with log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_17611.trc:
ORA-00308:cannot open archived log
    '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Errors with log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pr00_17611.trc:
ORA-00308:cannot open archived log
   '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397869_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...

Thu Mar 28 11:38:44 2019
ALTER DATABASE RECOVER  datafile 5,6  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 5397870 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 5,6  ...
Thu Mar 28 11:39:08 2019
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397870_%u_.arc
Errors with log /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2019_03_28/o1_mf_1_5397870_%u_.arc
ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL 
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17605.trc  (incident=365041):
ORA-00600: internal error code, arguments: [3051], [82], [], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE RECOVER CANCEL ...

通过屏蔽一致性,强制打开库,报kgegpa错误,实例启动失败

Database Characterset is ZHS16GBK
No Resource Manager plan active
Exception[type:SIGSEGV, Address not mapped to object][ADDR:0x319C0CF3] [PC:0x2297740, kgegpa()+40] [flags: 0x0, count:1]
Exception[type:SIGSEGV, Address not mapped to object][ADDR:0x319C0CF3] [PC:0x229596B, kgebse()+279][flags: 0x2, count:2]
Exception[type:SIGSEGV, Address not mapped to object][ADDR:0x319C0CF3] [PC:0x229596B, kgebse()+279][flags: 0x2, count:2]
Thu Mar 28 11:43:15 2019
PMON (ospid: 17939): terminating the instance due to error 397
Instance terminated by PMON, pid = 17939

处理上述错误相关undo,启动数据库报ORA-00600 4193,ORA-00600 4137, ORA-00600 6006

Thu Mar 28 11:50:37 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_p001_18267.trc  (incident=373059):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_373059/orcl_p001_18267_i373059.trc
Stopping background process MMON
Trace dumping is performing id=[cdmp_20190328115038]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_18247.trc  (incident=372995):
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_372995/orcl_smon_18247_i372995.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_18247.trc  (incident=372996):
ORA-00600: internal error code, arguments: [4137], [34.22.4206895], [0], [0], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_372996/orcl_smon_18247_i372996.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18263.trc  (incident=373044):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_373044/orcl_ora_18263_i373044.trc
ORACLE Instance orcl (pid = 16) - Error 600 encountered while recovering transaction (34, 22).
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_18247.trc:
ORA-00600: internal error code, arguments: [4137], [34.22.4206895], [0], [0], [], [], [], [], [], [], [], []

通过重建undo,相关报错消失,安排数据导出重建库

ORA-00322 ORA-00312恢复

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:ORA-00322 ORA-00312恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库启动ORA-00322 ORA-00312错误,无法正常启动

Fri Mar 29 17:44:20 2019
ALTER DATABASE RECOVER  datafile 1  
Media Recovery Start
Serial Media Recovery started
Media Recovery failed with error 19909
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 1  ...
Fri Mar 29 17:44:20 2019
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_m000_5392.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\xff\REDO01.LOG'
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_m000_5392.trc:
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\xff\REDO02.LOG'
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_m000_5392.trc:
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\xff\REDO03.LOG'

人工指定redo应用,报ORA-00600 3051错误

Fri Mar 29 17:56:33 2019
ALTER DATABASE RECOVER  datafile 1  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 27542 Reading mem 0
  Mem# 0: D:\XIFENFEI\REDO02.LOG
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 1  ...
Fri Mar 29 17:56:49 2019
ALTER DATABASE RECOVER    LOGFILE 'D:\xifenfei\REDO02.log'  
Media Recovery Log D:\xifenfei\REDO02.log
Errors with log D:\xifenfei\REDO02.log
ORA-363 signalled during: ALTER DATABASE RECOVER    LOGFILE 'D:\xifenfei\REDO02.log'  ...
ALTER DATABASE RECOVER CANCEL 
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_ora_8532.trc  (incident=147928):
ORA-00600: ??????, ??: [3051], [82], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\administrator\diag\rdbms\xff\xff\incident\incdir_147928\xff_ora_8532_i147928.trc

比较明显redo无法正常应用,通过屏蔽数据库一致性,强制拉库

Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: D:\XIFENFEI\REDO01.LOG
Block recovery stopped at EOT rba 1.76.16
Block recovery completed at rba 1.76.16, scn 0.1073742057
Doing block recovery for file 3 block 272
Resuming block recovery (PMON) for file 3 block 272
Block recovery from logseq 1, block 72 to scn 1073742051
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: D:\XIFENFEI\REDO01.LOG
Block recovery completed at rba 1.72.16, scn 0.1073742052
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_smon_5144.trc:
ORA-01595: error freeing extent (16) of rollback segment (10))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Fri Mar 29 17:59:12 2019
Errors in file c:\app\administrator\diag\rdbms\xff\xff\trace\xff_mmon_13928.trc  (incident=149097):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: c:\app\administrator\diag\rdbms\xff\xff\incident\incdir_149097\xff_mmon_13928_i149097.trc
Fri Mar 29 17:59:12 2019
Trace dumping is performing id=[cdmp_20190329175912]
Completed: alter database open resetlogs

通过重建undo,数据库open正常,安排导出数据导入数据,恢复完成

利用VNCR修复CVE-2012-1675漏洞

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:利用VNCR修复CVE-2012-1675漏洞

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

随着对安全的重视,TNS Listener远程数据投毒漏洞(CVE-2012-1675)被很多单位要求进行整改,而且级别是高危
1


如果是11.2.0.4之前版本,特别是在rac环境中,如果要整改该问题相对比较麻烦,需要通过配置Oracle wallet来实现,配置比较复杂,而且还要重启实例,影响比较大,具体参考:Using Class of Secure Transport (COST) to Restrict Instance Registration in Oracle RAC (Doc ID 1340831.1),对于单机环境直接参考以前文章:Oracle Database Server ‘TNS Listener’远程数据投毒漏洞(CVE-2012-1675)的解决方案.从11.2.0.4开始Oracle引入了Valid Node Checking For Registration (VNCR)新特性,可以通过简单的配置即可完成该漏洞修复
在listener.ora文件中增加(grid/oracle用户)

VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=ON
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(hisdb1,hisdb2)

重启监听

LSNRCTL>set current_listener listener_name
LSNRCTL>stop
LSNRCTL>start

验证是否生效
1.在远程机器尝试远程注册

alter system set remote_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.115.15)(PORT=1521))' scope=memory;

2.观察监听日志
VNCR


类似这样证明已经生效.

补充说明

VALID_NODE_CHECKING_REGISTRATION_listener_name
Values:

OFF/0 - Disable VNCR//禁用VNCR,此功能不会对注册过来的service进行check;

ON/1/LOCAL - The default. Enable VNCR. All local machine IPs can register.
启用VNCR,默认只允许本机的所有IP的服务注册到本监听,可通过REGISTRATION_INVITED_NODES参数添加其他有必要的服务器;

SUBNET/2 - All machines in the subnet are allowed registration.//指定子网内的服务器可以注册过来


REGISTRATION_INVITED_NODES_listener-name 
控制允许链接过来的节点,可以通过IP地址/主机名/网段来指定

Values are valid IPs, valid hosts, a subnet using CIDR notation (for ip4/6), or wildcard (*) for ipv4. 
For example:REGISTRATION_INVITED_NODES_Listener=(net-vm1, 127.98.45.209, 127.42.5.*)

Note 
that when an INVITED list is set, it will automatically include the 
machine's local IP in the list. There is no need to include it.

--11.2.0.4和12c不一样之处
在12.1 RAC数据库上,listener的参数VALID_NODE_CHECKING_REGISTRATION_listener_name默认设置为SUBNET / 2,
即子网中的所有计算机都允许注册.所以12c默认不能解决CVE-2012-1675漏洞

参考文档
Valid Node Checking For Registration (VNCR) (Doc ID 1600630.1)
How to Enable VNCR on RAC Database to Register only Local Instances (Doc ID 1914282.1)

12C数据库遭遇ORA-600 16703

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:12C数据库遭遇ORA-600 16703

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

关于tab$异常的问题,我们在10G(10g数据库遭遇ORA-600 16703),11G(警告:互联网中有oracle介质被注入恶意程序导致—ORA-600 16703)中有发现,近期在12C版本中也发现该问题.再次提醒请注意检查安装介质的md5值和现在运行的库中是否有相关恶意程序
数据库版本
12c-tab-version


alert日志报错

************************************************************
Warning: The SCN headroom for this database is only 45 days!
************************************************************
************************************************************
Warning: The SCN headroom for this database is only 45 days!
************************************************************
Picked broadcast on commit scheme to generate SCNs
Fri Jan 25 11:34:55 2019
Starting background process TMON
Fri Jan 25 11:34:55 2019
TMON started with pid=104, OS id=47282
Fri Jan 25 11:34:55 2019
Thread 1 advanced to log sequence 11942 (thread open)
Thread 1 opened at log sequence 11942
Current log# 5 seq# 11942 mem# 0: +DATA/xifenfei/ONLINELOG/redo01_01.log
Successful open of redo thread 1
Fri Jan 25 11:34:55 2019
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jan 25 11:34:55 2019
Network Resource Management enabled for Process LG00 (pid 451491) for Exadata I/O
Fri Jan 25 11:34:55 2019
SMON: enabling cache recovery
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc (incident=394486):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Jan 25 11:34:56 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc:
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Fri Jan 25 11:34:56 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Fri Jan 25 11:34:56 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_46443.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Fri Jan 25 11:34:56 2019
Error 704 happened during db open, shutting down database
USER (ospid: 46443): terminating the instance due to error 704
Fri Jan 25 11:34:57 2019
Instance terminated by USER, pid = 46443

处理思路基本上和其他两个版本一样,都是对tab$进行修复,然后直接open数据库,但是在这次的恢复中遇到ORA-00600 kdfReserveSingle_1错误
ORA-600 kdfReserveSingle_1

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280],
[], [], [], [], [], [], [], [], []
Process ID: 319852
Session ID: 2401 Serial number: 24555

---alert日志
Starting background process TMON
Fri Jan 25 20:51:41 2019
TMON started with pid=94, OS id=322554
Fri Jan 25 20:51:41 2019
Thread 1 advanced to log sequence 11943 (thread open)
Thread 1 opened at log sequence 11943
Current log# 6 seq# 11943 mem# 0: +DATA/xifenfei/ONLINELOG/redo02_01.log
Successful open of redo thread 1
Fri Jan 25 20:51:41 2019
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jan 25 20:51:41 2019
Network Resource Management enabled for Process LG00 (pid 319754) for Exadata I/O
Fri Jan 25 20:51:41 2019
SMON: enabling cache recovery
Fri Jan 25 20:51:41 2019
[319852] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:88159754 end:88160264 diff:510 ms (0.5 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Fri Jan 25 20:51:41 2019
SMON: enabling tx recovery
Fri Jan 25 20:51:41 2019
Database Characterset is ZHS16GBK
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_319852.trc (incident=466446):
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Jan 25 20:51:42 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_319852.trc:
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280], [], [], [], [], [], [], [], [], []
Fri Jan 25 20:51:42 2019
Errors in file /u01/oracle/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_319852.trc:
ORA-00600: internal error code, arguments: [kdfReserveSingle_1], [0], [65280], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 319852): terminating the instance due to error 600
Fri Jan 25 20:51:43 2019
Instance terminated by USER, pid = 319852
ORA-1092 signalled during: alter database open…
opiodr aborting process unknown ospid (319852) as a result of ORA-1092
Fri Jan 25 20:51:49 2019
ORA-1092 : opitsk aborting process

这个错误主要是由于12c的tab$和以前版本比有一些特殊,导致开始恢复不全,通过继续修复tab$之后数据库正常open.
相关文章汇总
tab$恢复错误汇总
12C数据库遭遇ORA-600 16703
10G数据库遭遇ORA-600 16703
ORA-600 16703故障解析—tab$表被清空
警告:互联网中有oracle介质被注入恶意程序导致—ORA-600 16703

SCN Compatibility问题汇总-2019年6月23日

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:SCN Compatibility问题汇总-2019年6月23日

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近有不少朋友咨询关于2019年6月23日之前数据库scn,数据库是否要升级或者打补丁等问题,这里对相关问题做一个汇总。
1. 是否一定要升级或者打补丁
不一定,以下几种情况可以不用处理
1) 数据库版本11.1.0.7.20+ /11.2.0.3.9+ /11.2.0.4+版本已经包含了该补丁,无需处理
2) 数据库版本全部低于1)中数据库版本
3) 你的数据库环境中不存在1和2中数据库dblink访问
4) 你的数据库环境中有1和2中的数据库dblink访问,但是通过通过dbms_scn设置,不让1中的数据库版本scn compatibility改变(都保持为1)

2. 到了2019年6月23日之后,数据库的scn发生什么改变
简单的说就是数据库每秒可以使用的scn变大了,距离天花板的scn更加大,出现数据库scn用完的概率大大降低

--compat            -- SCN compatibility value
--headroom_in_scn   -- Difference between current SCN and RSL
--headroom_in_sec   -- number of seconds it would take to reachRSL

SQL> set serveroutput on ;
SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
  6   max_scn_compat number;
  7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 10   dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 14   end;
 15   /
rsl=16424869609472
headroom_in_scn=16424867538319
headroom_in_sec=1002494356
cur_scn_compat=1
max_scn_compat=3

PL/SQL procedure successfully completed.

SQL> Alter Database Set SCN Compatibility 2;

Database altered.

SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
  6   max_scn_compat number;
  7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 10   dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 14   end;
 15   /
rsl=21792299122688
headroom_in_scn=21792297051479
headroom_in_sec=665048127
cur_scn_compat=2
max_scn_compat=3

PL/SQL procedure successfully completed.


SQL> Alter Database Set SCN Compatibility 3;

Database altered.

SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
  6   max_scn_compat number;
  7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 10   dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 14   end;
 15   /
rsl=34585263898624
headroom_in_scn=34585261822622
headroom_in_sec=351819476
cur_scn_compat=3
max_scn_compat=3

PL/SQL procedure successfully completed.

这里大概演示了数据库scn compatibility变化带来的相关变化,这里可以看出来每秒

3. 如何禁用/启用scn compatibility自动升级

SQL>  begin dbms_scn.DisableAutoRollover; end;
  2   /

PL/SQL procedure successfully completed.

SQL> declare
  2   EFFECTIVE_AUTO_ROLLOVER_TS date;
  3   TARGET_COMPAT number;
  4   IS_ENABLED boolean;
  5   begin
  6    dbms_scn.GETSCNAUTOROLLOVERPARAMS(EFFECTIVE_AUTO_ROLLOVER_TS,TARGET_COMPAT,IS_ENABLED);
  7    dbms_output.put_line('EFFECTIVE_AUTO_ROLLOVER_TS='||to_char(EFFECTIVE_AUTO_ROLLOVER_TS,'yyyy-mm-dd hh24:mi:ss'));
  8    dbms_output.put_line('TARGET_COMPAT=' || TARGET_COMPAT);
  9   if(IS_ENABLED)then
 10    dbms_output.put_line('IS_ENABLED IS TURE'); 
 11   else  
 12    dbms_output.put_line('IS_ENABLED IS FALSE'); 
 13   end if;
 14   end;
 15   /
EFFECTIVE_AUTO_ROLLOVER_TS=2019-06-23 00:00:00
TARGET_COMPAT=3
IS_ENABLED IS FALSE

PL/SQL procedure successfully completed.

SQL> 

4. scn compatibility手工调整

SQL> Alter Database Set SCN Compatibility 2;

Database altered.

SQL> Alter Database Set SCN Compatibility 3;

Database altered.

SQL> Alter Database Set SCN Compatibility 1;
Alter Database Set SCN Compatibility 1
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 4999609080 bytes
Fixed Size                  9145080 bytes
Variable Size            1040187392 bytes
Database Buffers         3942645760 bytes
Redo Buffers                7630848 bytes
Database mounted.
SQL>  Alter Database Set SCN Compatibility 1;

Database altered.

SQL> alter database open;

Database altered.

SQL>  set serveroutput on ;
SQL>  declare
  2   rsl number;
  3   headroom_in_scn number;
  4   headroom_in_sec number;
  5   cur_scn_compat number;
 max_scn_compat number;
  6    7   begin
  8   dbms_scn.GetCurrentSCNParams(rsl,headroom_in_scn,headroom_in_sec,cur_scn_compat,max_scn_compat);
  9   dbms_output.put_line('rsl=' || rsl);
 dbms_output.put_line('headroom_in_scn=' || headroom_in_scn);
 10   11   dbms_output.put_line('headroom_in_sec=' || headroom_in_sec);
 12   dbms_output.put_line('cur_scn_compat=' || cur_scn_compat);
 13   dbms_output.put_line('max_scn_compat=' || max_scn_compat);
 end;
 / 14   15  
rsl=16425127591936
headroom_in_scn=16425125502261
headroom_in_sec=1002510101
cur_scn_compat=1
max_scn_compat=3

PL/SQL procedure successfully completed.

参考文档:
Recommended patching and actions for Oracle database versions 12.1.0.1, 11.2.0.3 and earlier-before June 2019(Doc ID 2335265.1)
Recommended patches and actions for Oracle databases versions 12.1.0.1, 11.2.0.3 and earlier–before June 2019(Doc ID 2361478.1)