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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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
