RE: Rejected to advance the SCN Issues

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Tue, 25 Jun 2013 15:43:28 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1186D831F_at_G6W2491.americas.hpqcorp.net>

David, nice explanation.

Bala, I am not sure the notes I posted reference to mention it though I think the notes do cover a bug Oracle has if manual hot backups are used and the entire database is set into hot backup mode with distributed transactions taking place that leads to an SCN consumption issue. Oracle posted an SQL health check script to identify this problem. For further information review MOS document id 1393363.1

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell Sent: Tuesday, June 25, 2013 11:14 AM
To: krishna000_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Rejected to advance the SCN Issues

"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)) -
SQL> 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)) - SQL> 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_at_gmail.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 25 2013 - 17:43:28 CEST