Re: SCN Jumping issue

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Thu, 26 May 2016 13:52:08 -0700
Message-ID: <CAA2DszxSkm-6F=QkF61f8cogkJ6v7nngFiir8RArYwJbnOQw1Q_at_mail.gmail.com>



Hello Deepak,
  • Is there a way to identify connections that come over using database links? You can check if any session is/was suffering from database link related wait events such as 'SQL*Net message from dbLink", or audit data to identify the incoming machine names. If your client machine names is in a different domain or or follow certain pattern, then you can quickly eliminate direct client connections from your scope and identify the connections through the database links.
    • Once all remote DB Links connecting into our DB are identified, is there a way to find which ones may be causing the most SCN jump/increments ? Hard question. Not too direct, but, you can use combination of methods to identify:

   Fair warning, this section uses underscore parameters, so, you need to work with support before setting up in a production database.

   Use the following underscore parameters to control or identify the SCN spike. (I don't want to give specific values since I don't want someone to just set these up in their databases). So, these parameters can be used to reject if the SCN spike beyond a threshold. If the spike exceeds a threshold then the spike is rejected and can lead to connection failures. So, monitor the alert log carefully as some lines are written about the spike. Using these rejections, you can identify the time of the event, and then map that back to a source database using auditing information. If the audit data is not sufficient then, you can dump the redo log file during that time frame to identify the incoming session trying to spike the SCN.

     _external_scn_logging_threshold_seconds
     _external_scn_rejection_threshold_minutes
     _external_scn_rejection_threshold_hours

  Again, understand the parameters, check with the support, proceed cautiously.

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle Practices <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Thu, May 26, 2016 at 10:26 AM, Deepak Sharma < sharmakdeep_oracle_at_yahoo.com> wrote:

> Thanks Riyaj, that is very good info.
>
> Couple of questions:
>
> - Is there a way to identify connections that come over using database
> links?
>
> - Once all remote DB Links connecting into our DB are identified, is there
> a way to find which ones may be causing the most SCN jump/increments ?
>
>
> On Thursday, May 26, 2016 10:51 AM, Riyaj Shamsudeen <
> riyaj.shamsudeen_at_gmail.com> wrote:
>
>
> Hello Deepak,
> Your assertion is correct, database link sessions will synchronize the
> SCN between the databases, thereby, increasing the SCN of one or more
> databases sharply.
>
> So, if any one of your database is encountering from the bug, then, all
> databases interconnected through database links can suffer the
> consequences. You may be using RMAN backup, however, some database
> connecting to your database over database link(s) might be using database
> level 'begin backup' command, infecting your database SCN value too.
>
> This will be an hard issue to debug: You need to identify if any of the
> database connecting to your database, directly or indirectly, is using
> 'alter database begin backup' command and if so, does that database have
> the bug fix for the issue ?
>
> Yeah, 55 days seems serious, if the database is affected by the bug, you
> can consume 55 days in a matter of days (if not hours).
>
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com - Specialists in Performance,
> RAC and EBS
> Blog: http://orainternals.wordpress.com/
> Oracle ACE Director and OakTable member <http://www.oaktable.com/>
> Co-author of the books: Expert Oracle Practices
> <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL,
> <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC
> Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices
> <http://tinyurl.com/book-expert-plsql-practices>
>
> <http://tinyurl.com/book-expert-plsql-practices>
>
> On Wed, May 25, 2016 at 9:15 PM, Deepak Sharma <
> dmarc-noreply_at_freelists.org> wrote:
>
> Just recently (last week) we came across an alert from OEM for one of our
> primary databases, related to SCN Jumping or increasing very rapidly.
>
> We already have an Oracle SR in place, but wanted to check with the
> community about their thoughts in case someone has had similar experience
> in the past (and any solution).
>
> This is an Oracle 11.2.0.4 database (1) using RMAN backup, and (2) has
> several connections coming over using database links to it (research shows
> the issue could be db link related).
>
> Some of the other DBs that have DB Links to ours, see messages related to
> sudden increase of SCNs for their own DBs, in alertlog (fyi).
>
> The OEM alert seem pretty serious, and says we have about 55 days before
> the DB would freeze temporarily or throw ORA-600 errors.
>
> Could be related to this :
>
> https://community.oracle.com/thread/2336601?tstart=0
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 26 2016 - 22:52:08 CEST

Original text of this message