ORA-19706: invalid SCN, ora-19706scn

ORA-19706: invalid SCN, ora-19706scn

Today's site dblink reported ORA-19706: invalid SCN, database versions are 11.2.0.4. After checking metalink, the two databases will synchronize the scn number when accessing through dblink. The result of SCN Headroom must be greater than the value of _ external_scn_rejection_threshold_hours. Dblink is used for interface problems.

Installing, Executing and Interpreting output from the "scnhealthcheck. SQL" script (Document ID 1393363.1)

Applies:
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 MERs 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, using t physical standby databases. For physical standby databases the script shocould 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 MERs. oracle determined that the most suitable value for this new hidden parameter is "24" and that this value shocould be used guest SS 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 shoshould 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 operation ents the absolute minimum patch level for each release-you are strongrong advised to use the current recommendations in preference to these minimum levels, and in particle 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 random sive
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: ".

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 shoshould 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.