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,
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,
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,
       dbms_flashback.get_system_change_number current_scn,
  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)

----------------- ------------------- ----------- ----------        2012/05/17 12:38:49     2775567   9068.525

Current Date: 2012/01/17 01:01:09
Current SCN:  384089
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.

    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.


惯性思维总是 令我们离大师们 有着一定的距离,这种差距 在知识广袤的领域尤其明显。




SQL> select * from help@LZ;       –LZ 为dblink名


select sid,username,machine,program,module from v$session where module=’ORACLE.EXE’



Alter session close database link DBLINKNAME;

这里要注意,需要先执行commit后以上关闭远程session SQL方会成功,即便是Select操作也是如此;


ORA-02080: 数据库链接正在使用中

SCN may jump in a distributed transaction with dblink

在分布式事务环境中数据库的SCN可能瞬间暴增,这种行为被称作SCN jump in distributed transaction,注意这种现象是正常的。在发生分布式事务的2个数据库中,SCN较低的DB会将SCN和较高的那个数据库同步,这有时候会造成我们的一些误解认为可能是Hot backup等操作引起了SCN的猛增。


SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE      Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - Production

SQL> select * from global_name;


SQL> select current_scn from v$database;


SQL> select current_scn from v$database@PROD;


SQL> insert into testlink@PROD values(1);

1 row created.

SQL> select current_scn from v$database;


由于这种SCN跳跃可能引起ORA-600 [2252]内部错误,相关的bug case.

Script to show Active Distributed Transactions

该脚本可以用于显示活跃的分布式事务(Distributed Transactions from dblink),可以协助诊断dblink远程事务:


REM distri.sql
column origin format a13
column GTXID format a35
column LSESSION format a10
column s format a1
column waiting format a15
Select /*+ ORDERED */
    substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
    substr(g.K2GTITID_ORA,1,35) "GTXID",
    substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
               0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
               3,'SNIPED', 'KILLED'),1,1) "S",
    substr(event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
-- where  g.K2GTeXCB =t.ktcxbxba <= use this if running in Oracle7
where  g.K2GTDXCB =t.ktcxbxba -- comment out if running in Oracle8 or later
   and g.K2GTDSES=t.ktcxbses
   and s.addr=g.K2GTDSES
   and w.sid=s.indx;

REM distri_details.sql
set headin off
select /*+ ORDERED */
Curent Time : '|| substr(to_char(sysdate,'dd-Mon-YYYY HH24.MI.SS'),1,22) ||'
'||'GTXID='||substr(g.K2GTITID_EXT,1,10) ||'
'||'Ascii GTXID='||g.K2GTITID_ORA ||'
'||'Branch= '||g.K2GTIBID ||'
Client Process ID is '|| substr(s.ksusepid,1,10)||'
running in machine : '||substr(s.ksusemnm,1,80)||'
  Local TX Id  ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,10) ||'
  Local Session SID.SERIAL ='||substr(s.indx,1,4)||'.'|| s.ksuseser ||'
  is : '||decode(bitand(ksuseidl,11),1,'ACTIVE',0,
          2,'SNIPED',3,'SNIPED', 'KILLED') ||
  ' and '|| substr(STATE,1,9)||
  ' since '|| to_char(SECONDS_IN_WAIT,'9999')||' seconds' ||'
  Wait Event is :'||'
  '||  substr(event,1,30)||' '||p1text||'='||p1
        ||','||p3text||'='||p3    ||'
  Waited '||to_char(SEQ#,'99999')||' times '||'
  Server for this session:' ||decode(s.ksspatyp,1,'Dedicated Server',
                                          2,'Shared Server',3,
                                         'PSE','None') "Server"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
-- where  g.K2GTeXCB =t.ktcxbxba <= use this if running Oracle7
where  g.K2GTDXCB =t.ktcxbxba -- comment out if running Oracle8 or later
   and  g.K2GTDSES=t.ktcxbses
   and  s.addr=g.K2GTDSES
   and  w.sid=s.indx;
set headin on
-- end script



沪公网安备 31010802001379号