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

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 inDocument: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
- The "scnhealthcheck" script described above can still be used on latest database versions to check the current headroom.
  eg: The script can be used on 12.1.0.2 and later
#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.

Version History:

16/Oct/2015 Remove specific version numbers as script is still relevant for latest database versions
4/Sep/2013 Script can be run in 12c releases to check headroom
28/Jun/2013 Show about the VERBOSE option
19/Jun/2013 List versions with no SCN hardening fix available
17/Jun/2013 Stress to use latest recommended patch/es
9/Feb/2012 Add details of the EM SCN Monitor patch
17/Jan/2012 Initial customer version
19/Jan/2012 Add why parameter needs setting
31/Jan/2012 Minor re-wording

"scnhealthcheck.sql" script (文件 ID 1393363.1)