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

Oracle中dblink所产生远程会话的一些表现

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

Oracle领域被称为Oracle的世界,当之无愧;一丁点的想当然就让我们偏离于事实。

以dblink的表现为例,我一直认为dblink的远程连接session仅在操作(select,dml)发生时短期存在,在操作完成后依据一定条件保留或退出。

而事实并非如此,随便使用一个远程查询语句如下:

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

在远程数据库端观察session,可以发现:

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

SID USERNAME MACHINE PROGRAM MODULE
1 526 SYSTEM WIN_DESK1 ORACLE.EXE ORACLE.EXE

且该远程会话一直保留直到原会话退出为止,无论是成功退出还是程序失败。

若希望在原会话中关掉已打开的远程会话,则需要使用一下命令:
Alter session close database link DBLINKNAME;

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

若没有执行commit,会出现:

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;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5072553
SQL> select current_scn from v$database@PROD;
CURRENT_SCN
-----------
7798262
SQL> insert into testlink@PROD values(1);
1 row created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
7798282

由于这种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" ,
substr(decode(bitand(ksuseidl,11),
1,'ACTIVE',
0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',
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,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
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
||','||p2text||'='||p2
||','||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

 

沪ICP备14014813号

沪公网安备 31010802001379号