_external_scn_rejection_threshold_hour

1?Warning - High Database SCN: Current SCN value is 0x0b7b.0008e40b, threshold SCN value is 0x0b75.055dc000
If you have not previously reported this warning on this database, 
please notify Oracle Support so that additional diagnosis can be performed.

2?Warning: The SCN headroom for this database is only NN days!

3?Warning: The SCN headroom for this database is only N hours!

4?WARNING: This patch can not take full effect until this RAC database has been completely shutdown and restarted again.
Oracle recommends that it is done at the earliest convenience.

5?Rejected the attempt to advance SCN over limit by 9374 hours worth to 0x0c00.00000f66, by distributed transaction remote logon,
remote DB: REMDB.XX.ORACLE.COM.
Client info : DB logon user ME, machine yy, program sqlplus@yy (TNS V1-V3), and OS user uuu

6?Rejected the attempt to advance SCN over limit by 9375 hours worth to 0x0c00.000003c6, by distributed transaction logon,
remote DB: REMDB.XX.ORACLE.COM.
Client info : DB logon user TC, machine xx, program oracle@xx (TNS V1-V3), and OS user xxx 

7?Rejected the attempt to advance SCN over limit by 9374 hours worth to 0x0c00.00000f66, by XXXXX
Client info : DB logon user TC, machine mmm, program sqlplus@mmm (TNS V1-V3), and OS user uuu

Where XXXXX is a string such as:
 ? PL/SQL RPC (remote)
 ? sql exec with curSCN
 ? sql exec with outSCN

select version,
       date_time,
       dbms_flashback.get_system_change_number current_scn,
       indicator
  from (select version,
               to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
               ((((((to_number(to_char(sysdate, 'YYYY')) - 1988) * 12 * 31 * 24 * 60 * 60) +
               ((to_number(to_char(sysdate, 'MM')) - 1) * 31 * 24 * 60 * 60) +
               (((to_number(to_char(sysdate, 'DD')) - 1)) * 24 * 60 * 60) +
               (to_number(to_char(sysdate, 'HH24')) * 60 * 60) +
               (to_number(to_char(sysdate, 'MI')) * 60) +
               (to_number(to_char(sysdate, 'SS')))) * (16 * 1024)) -
               dbms_flashback.get_system_change_number) /
               (16 * 1024 * 60 * 60 * 24)) indicator
          from v$instance)

VERSION           DATE_TIME           CURRENT_SCN  INDICATOR
----------------- ------------------- ----------- ----------
11.2.0.3.0        2012/05/17 12:38:49     2775567   9068.525

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

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 J
    anuary 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 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.

[oracle@vrh1 ~]$ oerr ora 19706
19706, 00000, "invalid SCN"
// *Cause:  The input SCN is either not a positive integer or too large.
// *Action: Check the input SCN and make sure it is a valid SCN.

The system change number (SCN) is a logical, internal timestamp used by the Oracle Database.
SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction.

The database uses SCNs to query and track changes. For example, if a transaction updates a row,
then the database records the SCN at which this update occurred. Other modifications in this transaction typically
have the same SCN. When a transaction commits, the database records an SCN for this commit.
Multiple transactions that commit at the same time may share the same SCN.

SCNs occur in a monotonically increasing sequence, and there is a very large upper
limit to how many SCNs an Oracle Database can use - that limit is currently 281 trillion,
or specifically 281,474,976,710,656 (is 2^48) SCN values.

Given that there is an upper limit, it is important that any given Oracle Database does not
run out of available SCNs. The Oracle Database uses a time based rationing system to ensure that this does not happen.

At any point in time, the Oracle Database calculates a "not to exceed" limit for the number
of SCNs a database can have used, based on the number of seconds elapsed since 1988, multiplied by 16,384. 

This is known as the database's current maximum SCN limit.
Doing this ensures that Oracle Databases will ration SCNs over time,
allowing over 500 years of data processing for any Oracle Database.

The difference between the current SCN the database is using, and the "not to exceed" upper limit,
is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second.

However, Oracle has determined that some software bugs could cause the database to attempt to exceed
the current maximum SCN value (or get closer to the limit than was warranted).

Generally if the database does try to exceed the current maximum SCN value, the transaction that
caused this event would be cancelled by the database, and the application would see an error. 

The next second the limit increases, so typically the application then continues with a slight hiccough in processing.
However, in some very rare cases, the database does need to shut down to preserve its integrity.
In no cases is data lost or corrupted.

Similar to how clocks are kept synchronized in a computer network, when two databases communicate
with each other over a database link, they synchronize their SCNs by picking the largest SCN in use by the two.
So in some cases, databases experienced rapidly decreasing SCN headroom not because of a bug in that specific database,
but because the bug was active in one or more of the databases that database was connected to. Since the database always
rejects SCNs that exceed the current maximum SCN, the provision of being able to run Oracle Databases for more than 500 years
was not affected in any of the cases.