Database, SQL | PLSQL

Oracle Database 9i: Where is my SQL_ID? Which SQL is in lock?

 , , ,

Struggling with that, right?
As you know, in Oracle Database 9i the view V$SESSION doesn’t have SQL_ID column…
So how to map SQLs in my database? And, for example, how to get the SQLs causing a lock?

In the end of the day, the SQL_ID is only a representation of the hash_value of an SQL. You can even make the translation from SQL_ID to Hash Value as you can check on this post by Tanel Poder.

Ok, but I have to map which sql is causing the lock in my 9i database, how can I do that?

Here it goes:

If session status is ACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.SQL_ADDRESS = s1.ADDRESS

If session status is INACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.prev_sql_addr = s1.ADDRESS

You’re welcome! 😉

See you next week!

Errors and Bugs, GoldenGate

OGG-01411 – Cannot convert input file ./dirdat/xx with format RELEASE 9.0/9.5 to output file ./dirdat/zz

 ,

Hi.

If you search solutions to this Error, you will perceived only one documented root cause:

Error:

OGG-01411  Cannot convert input file ./dirdat/xx000549 with format RELEASE 9.0/9.5 to output file ./dirdat/zz000034 with format RELEASE 12.1.

Cause:

“The output trail of the data pump has a different format (version) than the input trail of the data pump”

If you are using GG 12.1 version, and all trails (rmttrail and exttrail) are correctly set with “format release”, you fell into a bug.

Oracle recommend to do upgrade to GG 12.2.

To work around this issue and start process, you need write a new trail, perform etrollover and reposition pump process.

On the Target System, process works fine, but not receive new trails, because pump process are abended.

GGSCI (lab2.grepora.net) 001> info rep01

REPLICAT rep01 Last Started 2017-03-20 12:08 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 13563
Log Read Checkpoint File ./dirdat/zz000034
2017-03-22 09:53:31.004144 RBA 30683971

More“OGG-01411 – Cannot convert input file ./dirdat/xx with format RELEASE 9.0/9.5 to output file ./dirdat/zz”

Application | Middleware | GoldenGate, GoldenGate, RAC | Data Guard

How To Delete Archive Logs From Downstream Database

After deploy GoldenGate with downstream database option archives from downstream database not cleaning ? All database transaction are gracefull, but archives (from downstream) are not cleaning?

Are no log transactions registered on GG Extract?
check this:

GGSCI (dbcloud) 3> send ETL01 showtrans

Sending SHOWTRANS request to EXTRACT ETL01 ...

Oldest redo log files necessary to restart Extract are:
------------------------------------------------------------
XID:                  14.6.3931092
Items:                1
Extract:              ETL01
Redo Thread:          1
Start Time:           2017-00-00:00:00:00
SCN:                  1682.4049305132 (7228184297004)
Redo Seq:             8612
Redo RBA:             20965491728
Status:               Running
------------------------------------------------------------

If you on same case, make this:

It should reach (and delete) JUST archived log that have already read by GoldenGate.

set serveroutput on size unlimited
set line 1000
set trimsp on
set feed off
set pages 5000
set pagesize 0
spool [[ some_dir ]]/delete_archives__dowstream_goldengate.sh

-- SR 3-14409179111 - Golden Gate Configuration, How To Delete Archive Logs From Downstream Database (Doc ID 2011174.1)
SELECT 'rm ' || r.NAME
-- case when (r.next_scn > c.required_checkpoint_scn) then 'NO' else 'YES' end purgable
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME and r.source_database = c.source_database and r.next_scn < ( select min(required_checkpoint_scn) from dba_capture where captur
e_type='DOWNSTREAM')
and purgeable='YES'
order by modified_time;
spool off

Schedule the output in automation tool (crontab / dba_job / windows job scheduler, (whatever)):

Database, Errors and Bugs, Linux

AIX: “WARNING: Heavy swapping observed on system in last 5 mins.”

 , , , ,

Quick one today!

Having message below in your 11.2.0.3 on AIX like this?

WARNING: Heavy swapping observed on system in last 5 mins. 
pct of memory swapped in [31.28%] pct of memory swapped out [3.81%]. 
Please make sure there is no memory pressure and the SGA and PGA are configured correctly. 
Look at DBRM trace file for more details.

Stand down, this issue is caused by unpublished Bug 11801934, mentioned in MOS False Swap Warning Messages Printed To Alert.log On AIX (Doc ID 1508575.1).

Basically happens because the v$osstat does not reflect proper stats for the swap space paging.

So, stay calm and see you next week!

Database, Errors and Bugs, GoldenGate

Extract abend with ORA-03113: end-of-file on communication channel

GoldenGate Extract / Replicat abend with bellow error:

Source Context :
SourceModule : [ggdb.ora.sess]
SourceID : [/scratch/aime/adestore/views/aime_stuya22/oggcore/OpenSys/src/gglib/ggdbora/ocisess.c]
SourceFunction : [OCISESS_context_def::oci_try(int, const char *, ...)]
SourceLine : [832]

2017-04-19 07:52:07 ERROR OGG-00665 OCI Error executing single row select (status = 3113-ORA-03113: end-of-file on communication channel)

It’s is a common stuff in Oracle Database not stable or it’s taking some 00600.

Review items:

  • Oracle Database alert log
  • If user process has been killed
  • Network communication (IPTABLES stuff)
  • Server crash

Suggest you to review ‘Master Note: Troubleshooting ORA-03113 (Doc ID 1506805.1)‘ on Oracle support services and keep database away from ORA-03113.

🙂

Database, Enterprise Manager, Errors and Bugs

Metric evaluation error start – Get dynamic property error,Dynamic Category property error,Computation of a critical dynamic property failed. Retries Completed.

 , ,

Hi all,
Getting this error? OMS 12.1.0.3?
Ok… After an investigation on MOS and also a SR opened with Oracle is was pointed to relation with Bug 17575631 CLUSTER DB HOME PAGE ERRORS- REGIONS FETCH REAL TIME DATA WILL NOT BE DISPLAYED on Database Plugin.
It’s solved in OMS 12.1.0.4 and also in 12.1.0.5.1 EM Database Plugin Bundle patch.

 I’ll show you how to workaround it. 🙂

More“Metric evaluation error start – Get dynamic property error,Dynamic Category property error,Computation of a critical dynamic property failed. Retries Completed.”

Community, Events

DBA Brasil 2.0 – Oracle Database Backup Service for On-Premise Databases

 , , ,

Hello all,

Just letting you know I’ll be speaking in DBA Brasil 2.0 about Oracle Database Backup Service and specially about it use for On-Premise databases. I’ll be at room 94, from 9 to 10AM BRT.

The session is made for DBAs and Managers being focused on covering ROI, SLAs, Legal Aspect and Pros/Cons on this solution. This session includes a demo on this solution.

NewScreenshot 2017-04-12 às 18.39.28

The participation is free! Please make your self present through: http://www.dbabr.com.br/dbabrasil2/inscricao/.

For complete list of sessions: http://www.dbabr.com.br/dbabrasil2/grade/

See you there!

Database, Performance, SQL | PLSQL

Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query

 , , , , ,

This post is just a reflection, basically based on my last post about Adaptive Query Optimization/SQL Plan Directives. Several times we find some situations that can be solved by setting a parameter (likely a “_”) and we just proceed with an “alter system” disregarding about all the impact of this.

This is specially important when talking about “optimizer” parameters even for bugs causing ORA-600’s or performance issues… Changing those parameters in system will affect not only the SQL with the error but all SQLs in database, which may cause a really bad effect in some cases.

bad-idea-sign

Ok Matheus, but what can I do?
Let’s to the obvious and generally applied methods:

More“Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query”

Database, Errors and Bugs, Migration | Upgrades | Patching, Performance

Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade

 , , , , ,

Hello all!

As DBAs we are always being recommended by Oracle and also recommending to clients to update their databases, but we have to be aware about new features and their effects. This is the case of Adaptive Query Optimization and in this particular case on SQL Plan Directives.

SQL Plan Directives are one of the functionalities that compose the Adaptive Query Optimization in Oracle Database 12c. The basic idea is pretty interesting: The SQL Optimizer keeps reviewing bad (“suboptimal”, as they like to say) plans, tipically incorrect cardinality estimations and generates SQL plan directives, like for missing histograms or extended statistics.

In my case, just after the upgrade to 12c (made on Jan 27th), the CPU usage increased for the same report always ran in the database:

12c_upgrade

Ok, how to check it?

More“Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade”