PROCESSED Messages not clearing from Oracle Queue

I was contacted by Dev team to look into Development database where Oracle queue size kept  on increasing. As per them messages were not getting cleared.They were running count on Queue table and the count kept increasing. To find out exact cause I included the msg_state in the query and found out that there were…

Doing performance measurements of an OSB Proxy Service by programmatically extracting performance metrics via the ServiceDomainMBean and presenting them as an image via a PowerPoint VBA module

This article explains how the process of doing performance measurements of an OSB Proxy Service and presenting them in a “performance analysis document” was partly automated. After running a SoapUI based Test Step (sending a request to the service), extracting the service performance metrics was done by using the ServiceDomainMBean in the public API of the Oracle Service Bus. These service performance metrics can be seen in the Oracle Service Bus Console via the Service (more...)

Not Another Standby Monitoring Script

I know what you are thinking. Not another standby monitoring script, right?

There’s plenty of them out there already and what makes this one any different?

You are probably correct and there’s probably nothing different about this.

Having to work with environments that are inconsistent such as manual standby, dataguard without broker, dataguard with broker,
I have been obsessed to create standby SQL which can accommodate any environment and can be run from primary or (more...)

Bad to crosscheck archivelog all

Typically, it’s not a good idea to have “crosscheck archivelog all;” in backup scripts and

even worse to have “delete expired archivelog all;” since any evidence will be eradicated.

$ rman target /

Recovery Manager: Release - Production on Thu Jan 28 14:17:35 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HAWK (DBID=3130551611)

RMAN> list archivelog all;

using target database control file  (more...)

Why use KEEP backup?

Question I which may have been asked.

For 1 time backup to migrate/clone database, KEEP backup does not affect retention policy and not backed up 1 times clause.

Image the consequences for deleting 1 off backup which has archivelog backup and the main scripts use not backed up 1-2 times clause.

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HAWKLAS

Key     Thrd  (more...)

Unsolved Case for Missing archived_log Backup

The project was to migrate database from one DC to another.

The decision we made was to perform RMAN KEEP backup so it does not interfere with existing retention policy.

Backup also resides in its own separate directory for easier checksum and transfer.

This is for 4 nodes RAC environment and backup was taken from node1 at 2016-JAN-21 14:12:10

RMAN backup scripts.

run {

APEX 5.0 Beyond basics

iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the second tutorial is presented.

This tutorial helps you step by step through the process of migrating an application to the Universal Theme of Oracle Application Express 5.0. In this tutorial we talk about (more...)

Validate DG Broker Config for Switchover

Primary and Standby databases are running on the same server using OMF with listening on port 1530/1531

Note I have – TraceLevel = ‘SUPPORT’

+++ Check listener for DGMGRL service from PRIMARY and STANDBY.

$ lsnrctl status listener_las|grep DG -A 1
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGMGRL" has 1 instance(s).
  Instance "hawklas", status UNKNOWN, has 1 handler(s) for this service...

$ lsnrctl  (more...)

ORA-12514: TNS:listener during switchover using DGMGRL

Not sure what I am doing wrong because DEFAULT StaticConnectIdentifier does not seem to work.

Google and MOS were not much help.

Using StaticConnectIdentifier as created by dmgrl would fail with the following error during switchover.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The error can be found in the listener.log

When using tnsnames for StaticConnectIdentifier, switchover works like a charm.

I keep thinking, there must be something wrong (more...)

How Reliable is v$archive_dest_status?


Any DG Experts out there who knows more about the reliability for v$archive_dest_status as it has been many years since I have written scripts to monitor DG.

Don’t want to reinvent the wheel if I cannot make it better.

Note: gap_status is only available from 11gR2 +++

SQL*Plus: Release Production on Sun Jan 10 11:06:55 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g  (more...)

ORA-01652: unable to extend temp segment even with Autoextend ON

I have encountered a very strange feature and wondering if anyone has experienced this.

Tracing was enabled for “ORA-01652: unable to extend temp segment” using alter system set events ‘1652 trace name errorstack level 1’;.

The tablespace is configured to autotextend by 64MB with max size 31GB and currently has 4MB Free.

“create index x on abc(txt) tablespace users;” failed with ORA-01652: unable to extend temp segment by 128 in tablespace USERS.

I understand objects (more...)

New Version Of XPLAN_ASH Utility

A new version 4.22 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version primarily addresses an issue with 12c - if the HIST mode got used to pull ASH information from AWR in 12c it turned out that Oracle forgot to add the new "DELTA_READ_MEM_BYTES" columns to DBA_HIST_ACTIVE_SESS_HISTORY - although it got officially added to V$ACTIVE_SESSION_HISTORY in 12c. So now I had to implement (more...)

Patching with OPLAN

From a time far, far way, I tweeted about Oracle Software Patching with OPLAN (Doc ID 1306814.1) and decided to give it a try.

First, you will need to configure X11 else error:
Can’t connect to X11 window server using ‘localhost:10.0’ as the value of the DISPLAY variable.

Second, you will need to using OPatch Version:, else error:
Caught exception: java.lang.ExceptionInInitializerError

If you like to see (more...)

SLES shell script to check required os packages according to documentation before starting oracle forms & reports installation


If you try to install oracle forms & reports on SLES 11.3 than despite all installer checks are passed maybe you get a message like:

Error in invoking target ‘install’ of makefile ‘<FR_HOME>/sqlplus/lib/’.

The reuirement check of the installer shows only missing openmotif classes which are not really required on SLES (see Doc ID 1567715.1), but no problems with a compiler or something similar.

Checking for binutils-2.19-11. (more...)

MRP process on standby stops with ORA-00600

A rather not so great post about an ORA-00600 error i faced on a standby database. Environement was on Sun Super Cluster machine. MRP process was hitting ORA-00600 while trying to apply a specific archive log.

The error message was something like this

MRP0: Background Media Recovery terminated with error 600
Errors in file /u01/app/oracle/product/
ORA-00600: internal error code, arguments: [2619], [539], [], [], [], [], [],  (more...)

Parallel Projection

A recent case at a client reminded me of something that isn't really new but not so well known - Oracle by default performs evaluation at the latest possible point in the execution plan.So if you happen to have expressions in the projection of a simple SQL statement that runs parallel it might be counter-intuitive that by default Oracle won't evaluate the projection in the Parallel Slaves but in the Query Coordinator - even (more...)

“Bob the Builder: Build/Deploy of #ADF enterprise applications” – I’m a speaker at #DOAG2015:


Today my presentation is confirmed:  “Bob the Builder: Build/Deploy of ADF enterprise applications”.

I will speak over our experiences with following

  • build tools
  • deployment tools
  • architecture
  • versioning & branching
  • continuous integration
  • hot deployment in development
  • compile & runtime dependencies

If you speak german and you are interested in some of this, you should come and attend DOAG 2015.

Filed under: Deploying, Development Tagged: 11g, 12c, ADF, ADF Architecture, Branching, (more...)

Temp Table Transformation Cardinality Estimates – 2

Continuing from the previous part - which was about the Temp Table Transformation and join cardinality estimates - using the same simple table setup here is a slight variation of the previously used query to demonstrate the potential impact on single table cardinality estimates:

explain plan for
cte as (
select /* inline */ id from t1 t
where 1 = 1
select /*+
no_merge(a) no_merge(b)
*/ * from cte a, cte (more...)

Heuristic Temp Table Transformation – 2

Heuristic Temp Table Transformation - 2 Some time ago I've demonstrated the non-cost based decision for applying the temp table transformation when using CTEs (Common Table/Subquery Expressions). In this note I want to highlight another aspect of this behaviour.Consider the following data creating a table with delibrately wide columns:

create table a
rownum as id
, rownum as id2
, rpad('x', 4000) as large_vc1
, rpad('x', 4000) as large_vc2
, rpad('x', 4000) (more...)

Function-Based Indexes And CURSOR_SHARING = FORCE

In general it is known that Function-Based Indexes (FBIs) can no longer be used by the optimizer if the expression contains literals and CURSOR_SHARING = FORCE / SIMILAR (deprecated) turns those literals into bind variables. Jonathan Lewis described the issue quite a while ago here in detail.In a recent OTN thread this issue was raised again, but to my surprise when I played around with a test case that mimicked the OP's problem query (more...)