Re: Rejected to advance the SCN Issues
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "krishna000@xxxxxxxxx" <krishna000@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Jun 2013 08:13:45 -0700 (PDT)
"So which means , since there is a increase in SCN number we should be
facing this kind of issues even for local transactions rather than
distrubted transactions right ? but we're not facing such issues as such ?"
No, and you're not. Distributed transactions span instances/databases, where
SCNs aren't likely to match, thus the maximum SCN in the distributed
transaction chain is selected. The problem arises when one database in that
distributed transaction chain generates an SCN that exceeds the current soft
limit imposed for one or more of the other databases in that chain. It isn't
likely that the local database will overgenerate SCNs and reject its own values
(the soft limit increases 16K per second, so unless your database is generating
16k+1 SCNs per second [a highly unlikely event] you won't run into the
distributed transaction problem on local transactions) but it is possible, as
you know, for another database in the set of databases performing distributed
transactions to generate an SCN that does exceed the current soft limit for one
or more of the other databases.
The soft limit is generated using the following 'formula':
(number of seconds since midnight on 1/1/1988)*16384
The hard limit for SCNs is, if my memory is not too faulty, 281 trillion. but
that may be larger now with the fairly common use of 64-bit operating systems.
Calculating the SCN soft limit for June 25, 2013 at 09:00:00 we get:
SQL> select ((trunc(sysdate)+(9/24)) -
to_date('01/01/1988','MM/DD/RRRR'))*86400*16384 SCN_LIMIT
2 from dual
3 /
SCN_LIMIT
---------------
13175311564800
SQL>
A fairly large limit, but remember it's tied to the system date/time so any
database in a time zone ahead of yours can generate SCNs which exceed the soft
limit for your database. A timezone just an hour ahead of Mountain Time (where
I am) changes that soft limit to:
SQL> select ((trunc(sysdate)+(10/24)) -
to_date('01/01/1988','MM/DD/RRRR'))*86400*16384 SCN_LIMIT
2 from dual
3 /
SCN_LIMIT
---------------
13175370547200
SQL>
It should be fairly easy to see how distributed transactions can generate the
errors you're seeing while local transactions do not.
David Fitzjarrell
________________________________
From: Bala Krishna <krishna000@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 25, 2013 1:29 AM
Subject: Rejected to advance the SCN Issues
Hi All,
Almost all our Databases are affected with below errors , we suspect that
its because of our distrubuted transactions .
Rejected the attempt to advance SCN over limit by 212 hours worth to
0x0c2c.ae9cc069, by distributed transaction remote logon, remote DB:
XXXXX.xxxx.com.
Client info : DB logon user APPS, machine XXXXXXX, program , and OS user
XXXXXXX
As if now we've mitigated this issue by setting below parameters.
_external_scn_rejection_threshold_hours$
My question:-
1. Distrubuted Transactions are being failed sometimes with ORA-600 ,
ORA-19706:
invalid SCN & some instance crashes are also observed.
I've read in this blog (
http://www.orainternals.com/2012/01/20/scn-what-why-and-how/) and it says
that
At commit time, a co-ordinated SCN is needed for the distributed
transaction and maximum SCN value from all participating databases is
chosen.
So which means , since there is a increase in SCN number we should be
facing this kind of issues even for local transactions rather than
distrubted transactions right ? but we're not facing such issues as such ?
Regards
Bala
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
