OGB Appreciation Day : manipulating execution plans

Tims call for OGB Appreciation Day 2019 (#ThanksOGB) comes in time this year.

Even unemployed at the moment, I had a chance to help a friend with some problematic SQL statements.

The statements are not that important at all but he possibilities we had to address the problems were great!

First of all an existing execution plan, together with session stats, ASH/AWR and in one case SQLTRACE. None of these sources of information (more...)

Simple getTracefile

Tracefiles are a very valuable source of information in Oracle databases.
In Versions prior to 12.2 quite complicated objects (with java dependency) were required.
Fortunately, in 12.2 Oracle introduced (& documented) the View V$DIAG_TRACE_FILE_CONTENTS. Unfortunately for some (only good from an internal technical perspective) reasons it does not contain the file itself, or a LOB locator, but several chunks as VARCHAR2. So it's still worth to write some code for a simple interface (more...)

possible changes in Oracle 20

As this post is written in July 2019, it's highly speculative writing about changes in Oracle version 20.
But at least it's based on some information available from Oracle.

A new autoupgrade.jar version is available at AutoUpgrade Tool (Doc ID 2485457.1). In this Note, there is also a BUGS_20190715.txt which contains (among others):
AUPG-955 Disable NONCDB validation when upgrading a DB to 20c
AUPG-975 upgrade from to 20 (more...)

ORA-21700 in data dictionary

This whole story started with a nice little ticket:

When I try to edit a scheduler job in <DB>  with user <USER> I am getting error:
ORA-21700: object does not exist or is marked for delete
even I can see the object and the object is not deleted or marked as delete.

At first I could not reproduce the error, so I asked for something more specific, and I got 2 screenshots. (one shown here)


this is the output of ORADEBUG DOC for Version 19c.

SQL> oradebug doc

Internal Documentation

EVENT Help on events (syntax, event list, ...)
COMPONENT [<comp_name>] List all components or describe <comp_name>

oradebug doc event

SQL> oradebug doc event

Event Help:

Formal Event Syntax
<event_spec> ::= '<event_id> [<event_scope>]

<event_id> ::= <event_name | number>[<target_parameters>]

<event_scope> ::= [<scope_name>: scope_parameters]

<event_filter> ::= {<filter_name>: filter_parameters}

<action> ::= <action_name>(action_parameters)

<action_parameters> ::= <parameter_name> = [<value>|<action>][, (more...)

deleting orphaned CommVault backups

In the current environment Commvault is used as a central backup solution. The setup is not very common, as CommVaults scheduler is not used for any activities. All backups are scheduled by a system which also takes some infrastructural limitations into consideration. These are (among others): shared IO resources of consolidated data­bases on a cluster, shared backup network resources of clusters, shared media agents in different datacenters, and some other constraints as well.
Also retention policies (more...)

invisible IOs

My team identified an interesting case of invisible IOs this week. During some checks of system statistics graphs, something unexpected ocured:
One of those "small & unimportant" test DBs had an interesting IO pattern:

This was something interesting, so a colleague decided to have a deeper look at it. The next obvious step is to see if AAS shows anything interesting. And it does:

A lot of blue (which is used for IO in all (more...)

limit IOs by instances

This is a follow up to my previous post.

I had to answer if I can limit the (IO) resources a DB instance can utilize. Unfortunately, in a simple instance I can not do so. It can be done in PDBs, but right now PDBs are out of scope.
So a simpler approach was developed: limiting IOs by creating cgroups.

There are 2 steps:

  1. disks
  1. create a proper cgroup
  2. get all related devices for (more...)

shared resources

Some days ago I had one of these calls from an application colleague:
The database was slow yesterday, can you please check what's the problem?
Of course, I had some short discussion if he really means the DB or should it be called the application is slow. Also some other questions needed to be asked first, e.g. if it's a response time or throughput issue, when it was "good" last time, what "bad" and (more...)

connection manager – what happens at startup & shutdown

Oracle Connection Manager (cman) is a great tool to create a gateway between networks which can not be connected, or filter those who can access to a service.
Most of the time, it's a very robust implementation and doesn't need a lot of attention. But if it's required to dig into it, a basic knowledge about it's components can be useful.

In this post I'll show what happens in a simple startup (& (more...)

preupgrade.jar – enforced recommendations

Recently I upgraded a database from version 12.1 to 12.2.
I take upgrades serious - similar (but not as skilled) to Mike Dietrich. So I downloaded latest preupgrade.zip and unzipped it [I made a small error by unzipping it NOT to $ORACLE_HOME/rdbms/admin but to a temporary directory - later more about this].
Beside such errors, I try to follow Oracles recommended Upgrade Process.

At some point Oracle recommends to Gather Dictionary Stats (more...)

SQL Real Time Monitoring pure HTML report (thanks to SQLDeveloper 18.3)

Yesterday (from the writing of this post perspective) SQLDeveloper 18.3 came out.
(it seems SQL Developer does not strict follow Oracle Database Release Number schema, otherwise it must be 18.4 already as it's released in 4th quarter of 2018)

There are many bugs fixed on 18.3 and some nice enhancements there as well.

My favorite enhancement is Real Time SQL Monitoring, HTML exports now available - no flash!

As Tanel Poder wrote (more...)

seing your DB as it was some minutes ago

Last week during a discussion with a colleague we thought if it would make sense to have in SQLDeveloper the possibility to see the system "as it was some minutes ago".
Small errors can happen and also resource control isn't always perfect in every company.

Björn Rost suggested to use DBMS_FLASHBACK.ENABLE_AT_TIME:

TFA reports full dba_feature_usage_statistics to MyOracleSupport

Those who deal with MyOracleSupport (MOS) regularly will have noticed their demand for Trace File Analyzer (TFA) diagnostic collections.
Few will know they are also providing a full content of their DBs DBA_FEATURE_USAGE_STATISTICS view; at least when running the parameters
tfactl diagcollect -database <DB_UNIQUE_NAME>

The report calls tfa_home/resources/sql/db_feature_usage.sql and it's result is stored in the <hostname>.tfa_<timestamp>.zip file.

I did not find any mention about this behavior in the documentation, so it might be (more...)

RBAL process needs to release FORMER disk

Oracles Automatic Storage Management (ASM) has many nice advantages against other volume managers. One of my favorite is to add and remove or rebalance disks without any interruption for the database. Unfortunately the remove of devices after the rebalance is completed is not as straight forward as expected: 
In a current environment (Grid Infrastructure:, rdbms: and others) after the ALTER DISKGROUP xxx (more...)

unwanted cleanup in /var/tmp/.oracle

In my previous blog missing oraagent.bin process I raised a question:
why does files in /var/tmp/.oracle disappear? 
At least this question I am now able to answer.

By the help of Linux kernel's audit system and a proper rule:
-a always,exit -F arch=b64 -S rename,rmdir,unlink,unlinkat,renameat -F path=/var/tmp/.oracle -F key=ora.delete

I was able to see the culprit process:
type=PROCTITLE msg=audit(08/06/2018 17:13:48.382:58122) : proctitle=/usr/bin/systemd-tmpfiles --clean
type=PATH msg=audit(08/06/2018 17:13:48.382:58122) : (more...)

missing oraagent.bin process

It all started with a small error when I tried to stop/start a service:

srvctl start service -d cmpp01_site1 -s cmpp01
PRCD-1084 : Failed to start service CMPP01 
PRCR-1079 : Failed to start resource ora.cmpp01_site1.cmpp01.svc
CRS-2680: Clean of 'ora.cmpp01_site1.cmpp01.svc' on 'av2l954p' failed
CRS-5802: Unable to start the agent process

It's not fine the agent can not be started. so let's check the logfile crsd_oraagent_oracle.trc:
2018-07-23 21:29:14.339 :  (more...)

enabling Database Vault on e-business RAC database

Right now I'm in a process to setup Database Vault for an E-Business suite database. This is a 2 node RAC cluster.
The DB is with April 17 BP.

As the DB exists already, I followed How To Enable Database Vault in a 12c database ? (Doc ID 2112167.1).
Everything looks smooth, but unfortunately, at the Configuration of DV
exec dvsys.configure_dv('DVOWNER','DVMANAGER');
failes with
ERROR at line 1: 
ORA-47500: Database Vault (more...)

OUD – no operational route

Oracle Universal Directory is by far not my preferred tool.
Probably it's me, I'm just not used to it.

The latest joy was an error when I tried to run
eusm addDatabase
and I got
javax.naming.AuthenticationException: [LDAP: error code 49 - Invalid Credentials]
Very unspecific, so I logged into Oracle Unified Directory Services Manager (OUDSM)

There a click on Data Browser showed another error message:
LDAP: error code 52 - The load balancing  (more...)

when it runs on your computer …

... please don't see it ready for customers.

I try to enable Database Vault on an existing 12.2 database.
A proper document to follow is How To Enable Database Vault in a 12c database ? (Doc ID 2112167.1)

At some point it tells to run
@$ORACLE_HOME/rdbms/admin/catmac.sql system temp <system_password>

unfortunately this fails with
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20025: Password (more...)