ORA-19706: invalid SCN

水深无声 2022-08-01 00:27 192阅读 0赞

今天现场dblink报ORA-19706: invalid SCN,数据库版本都是11.2.0.4。查了下metalink,大致意思是通过dblink访问的时候，两个数据库会做scn号的同步，SCN Headroom的结果必须要比参数\_external\_scn\_rejection\_threshold\_hours的值大才行。选用dblink这种方式做接口带来的问题。

**   Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script (文档 ID 1393363.1)**

**APPLIES TO:**  
Oracle Database - Enterprise Edition - Version 10.1.0.5 to 12.1.0.1 \[Release 10.1 to 12.1\]  
Oracle Database - Standard Edition - Version 10.1.0.5 to 12.1.0.1 \[Release 10.1 to 12.1\]  
Information in this document applies to any platform.  
**PURPOSE**  
This document describes the "scnhealthcheck.sql" script, including where to download the script, how to execute it and how to interpret the output.  
For customers with Enterprise Manager an alternative to this script is to obtain and use the patch described in Document:1404410.1. The "Recommended Actions" section of that document will direct you back to this document for details of what action to take.  
**SCOPE**  
This document is intended for Oracle DBAs.  
The "scnhealthcheck.sql" script described here is intended for execution on any database version 10.1 or higher, except physical standby databases. For physical standby databases the script should be executed against the primary.  
**DETAILS**  
Installing the "scnhealthcheck.sql" script  
The "scnhealthcheck.sql" script can be downloaded here: Patch:13498243.  
If you install the patch then it will create "scnhealthcheck.sql" in the $ORACLE\_HOME/rdbms/admin directory.  
Alternatively you can use the script directly from the unzipped patch without actually installing it in your $ORACLE\_HOME.  
Executing "scnhealthcheck.sql"  
"scnhealthcheck.sql" can be executed as any DBA user of a database. It can be used in any 10.1 or higher database, regardless of patch level.   
To execute the script:  
Change to the directory where the "scnhealthcheck.sql" script is located.  
Use sqlplus to execute the script spooling output to a file.  
eg: Use an appropriate spool path for your platform below.  
sqlplus SYSTEM/xxxxx  
 spool /tmp/scncheck\_out  
 @scnhealthcheck  
 spool off  
 exit  
Interpreting "scnhealthcheck.sql" Output  
The output gives a snapshot of the SCN health at a given point in time. Example output might look like this:  
\------------------------------------------------------------  
ScnHealthCheck  
\------------------------------------------------------------  
Current Date: 2012/01/17 01:01:09  
Current SCN:  384089  
Version:      11.1.0.7.0  
\------------------------------------------------------------  
Result: A - SCN Headroom is good  
Apply the latest recommended patches  
based on your maintenance schedule  
AND set \_external\_scn\_rejection\_threshold\_hours=24 after apply.  
For further information review MOS document id 1393363.1  
\------------------------------------------------------------  
Take the appropriate action as indicated by the "Result":  
**Result: A - SCN Headroom is good**  
SCN health is good at this time. The majority of databases are expected to fall into this category. You are recommended to ensure that your database is patched to the current level as indicated by "My Oracle Support" recommendations using your normal maintenance schedule. No additional action is required at this time. See note \#1 and \#2 in the Notes section below.  
**Result: B - SCN Headroom is low**  
SCN health is low. You are recommended to ensure that your database is patched to the current level as indicated by "My Oracle Support" recommendations immediately rather than waiting for your normal maintenance window. Once patched the headroom is expected to increase over time. Continue to monitor the health daily. Please note that it may take several days or weeks after patching for the output to report that the headroom is good. See note \#1 and \#3 in the Notes section below.  
**Result: C - SCN Headroom is low**  
SCN health is low. This database appears to have a high rate of SCN increase. You are recommended to ensure that your database is patched to the current level as indicated by "My Oracle Support" recommendations immediately. If you have not already done so please follow the instructions in Document:1388639.1 to log a Service Request with Oracle Support so that additional advice can be given . See note \#1 and \#3 in the Notes section below.  
In addition to the above result the script output may advise to set the hidden parameter "\_external\_scn\_rejection\_threshold\_hours" on some Oracle versions. The following text gives more information about setting this parameter:  
Set \_external\_scn\_rejection\_threshold\_hours=24 after apply  
The hidden parameter "\_external\_scn\_rejection\_threshold\_hours" is introduced in January 2012 Critical Patch Update (CPU) and Patch Set Update (PSU) releases (and related bundles). Oracle recommends setting this parameter to the value 24 in 10g and 11.1 releases - it does not need to be set in 11.2 or later releases. The parameter is static and so must be set in the init.ora or spfile used to start the instance.   
eg:  
In init.ora:   
  \# Set threshold on dd/mon/yyyy - See MOS Document 1393363.1  
  \_external\_scn\_rejection\_threshold\_hours = 24   
   
In the spfile:   
  alter system set "\_external\_scn\_rejection\_threshold\_hours" = 24   
   comment='Set threshold on dd/mon/yyyy - See MOS Document 1393363.1'   
   scope=spfile ;  
Why do I need to set "\_external\_scn\_rejection\_threshold\_hours"=24 ?  
Oracle has many hidden parameters which all have default or derived values, and those parameters are not generally intended to be set by customers. Oracle determined that the most suitable value for this new hidden parameter is "24" and that this value should be used across all releases. 10g and 11.1 January 2012 CPU / PSU releases have a different default value compiled in and so this setting has to be made explicitly on those releases to ensure that the required value of 24 is used. Customers are not expected to tune this value themselves. Latest releases should have a default value of "24" already.  
**Notes:**  
\#1 For help with current recommendations see:  
Document:756671.1 for Latest Recommended Database patches.  
Document:331.1   Item "2. Plan" for general information about how to find recommended patches  
Document:1374524.1 for January 2012 Patch Set Update and Critical Patch Update Availability. This documents the absolute minimum patch level for each release - you are strongly advised to use the current recommendations in preference to these minimum levels, and in particular use CPU / PSU from July 2012 or later where available (See Document:1455387.1 for July 2012 CPU/PSU availability).  
Document:742060.1 Release Schedule and Patching End Dates of Current Database Releases  
Please note that there are no SCN hardening fixes for the following versions. The recommendation for these versions is to upgrade to a newer release:  
All versions up to and including 9.2.0.7  
Versions 10.1.0.2 to 10.1.0.4 inclusive  
Versions 10.2.0.1 and 10.2.0.2  
Version 11.1.0.6  
Version 11.2.0.1  
Versions 11.2.0.4 and 12.1.0.1 onwards already include the main SCN fixes but the "scnhealthcheck" script described above can still be used on those releases to check the current headroom.

\#2 The output of the "scnhealthcheck" script is a snapshot for the current point in time. As database activity can vary the output may be different at a later time. If any execution of the SQL shows output other than "Result: A" then please follow the guidance above.

\#3 After applying patches it may take some time (days or weeks) before the headroom grows sufficiently for the script to report "Result: A".

To confirm that the headroom is increasing you can change the "VERBOSE=FALSE" clause in "scnhealthcheck.sql" to "VERBOSE=TRUE". This will cause the script to output an additional "SCN Headroom" line. After patching one should see the "SCN Headroom" value increase slowly over time. Note that the value cannot increase by more than 1.0 per day. If the value continues to decrease, or becomes an increasingly large negative number, then if you have not already done so please follow the instructions in Document:1388639.1 to log a Service Request with Oracle Support.