Ilmar Kerm

Oracle, databases, Linux and maybe more

Some background info: We need to provide direct database access to many developers and data analysts. On database side managing so many different users securely means you have to use the same authentication source as all the other systems in the company, for example Active Directory. For databases we have implemented using Radius and for Oracle database this means users have to use thick Oracle client on their computers.
Installing Oracle client is not a trivial task for many users and the MacOS instantclient that Oracle provides has been very dependent on the exact MacOS version. In order to provide some help to overcome these problems I decided to create a small Virtualbox VM, that users could just download and run. The VM would be based on Fedora Linux, have preconfigured Oracle Instantclient with common client tools like SQLPlus and SQL Developer.
This kind of VM needs to be maintained and to push out changes I already set up a git respository containing ansible playbook. In VM, under root user I checked out this git respository and set up regular cron job to run one specified playbook locally.
Hourly crontab job looks something like this:

cd ansible-sqldeveloper-vm && git pull && ansible-playbook local.yml

This playbook can be used to push out changes to application level also, for example maintaining common tnsnames.ora file to include newly created databases.

It did not take long to discover first bug with the setup – SQL Developer under Linux “looses” text cursor in editor window very easily, it is just hidden. To change that users need to go to SQL Developer preferences and change how text cursor looks like. I don’t want users to fix this common error themselves.
Another issue, new SQL Developer version came out. It is easy to push out new RPM using Ansible but SQL Developer does not start immediately after that, users have to configure there to find JDK first. Another thing I really don’t want my users to deal with.

Ansible to the rescue. All I needed to do was commit the necessary changes to my local.yml playbook and update it in the git repository.

Lets take the easier issue of telling newly installed SQL Developr where to find JDK first.
SQL Developer 17.4 rads the JDK location from file $HOME/.sqldeveloper/17.4.0/product.conf so /home/sqldeveloper/.sqldeveloper/17.4.0/product.conf in my case. This file contains only one important line telling the location of JDK:

SetJavaHome /usr/lib/jvm/java-1.8.0-openjdk

All I need to do is use ansible copy module to create this file.

Now the other problem, I need to change specific SQL Developer preferences. When I did the changes required on a test system I found out that SQL Developer wrote it preferences to XML file $HOME/.sqldeveloper/system17.4.0.355.2349/o.sqldeveloper/product-preferences.xml. The file location is SQL Developer version specific. The specific change was a newly added XML tag under root element:

   <hash n="CaretOptions">
      <value n="caretColor" v="-31744"/>
      <value n="insertShape" v="8"/>
      <value n="overwriteShape" v="5"/>

This file contains all user preferences, so I don’t really want to overwrite this entire file, I only want to add this new XML tag. This is possible using Ansible xml module where I can give XPath as an argument and the module will make sure that the provided XPath expression is present in the file.
Also I want this to work for all SQL Developer versions and not change the path every time SQL Developer is upgraded.

The resulting Ansible playbook looks like this.

First I’ll describe the needed changes in a separate Ansible parameters file just to make the playbook look cleaner.

The playbook itself:

We have hundreds of developers who need access to production database for incident management purposes. But we don’t want to use shared accounts to access the database, each user has their own access to the database that is audited and has privileges according to the users access level.
Managing all these users manually on Oracle Database side is just too much, especially that all their access details are already described in Active Directory. Wouldn’t it be nice if we can just syncronise the AD users to the database side? Luckily we have an Ansible module for this task.

First, on the database side need to create a dedicated profile for the syncronised users:

CREATE PROFILE ldap_user LIMIT password_life_time UNLIMITED;

I assume you are already familiary with Ansible, so I’ll go straight to the point.
First you need to clone the ansible-oracle-modules to your playbook directory:

git clone library

This contains, among other goodies, a module that does exactly what is required 🙂 The module is called oracle_ldapuser.
This module requires extra python-ldap module to be installed. Install it using yum, not pip. Pip will install wrong version.

yum install python-ldap

The playbook looks like this:

Remember that it is a syncronisation, so new users are created and removed when the playbook is run.

I added a few larger updates to my Oracle Image Copy backup scripts.

  • Now it also has built in support for Netapp NAS storage
  • In addition to autorestore script there now is script that you can use to spin off cloned databases restored to a specified point in time

Go and check out the scripts from here

I just published for free the work I’ve done for the past 1,5 years 🙂

We used to have a few problems with backups:
* Weekly full backup took 2+ days to run on the larger databases and it always took a lot of resources to run.
* To test the backups we needed to allocate the same amount of storage space and it took too long time to restore the database from backup.
* To make matters worse, backup software provider charged by GB just to run a fancy GUI over RMAN.

But we can do better 🙂 And a script suite to manage RMAN image copy backups and to test them automatically was born.

Here is a separate page for the scripts

Code itself is published in GitHub

We’ve run this system excludively in production for over a year now and it has been working great.

Lately I’ve been upgrading our 11g Standard Edition databases in test environments to 12c Enterprise Edition and also plugging them in to a multitenant container database.
It’s a new technology for Oracle, but I was still quite surprised about the number of issues I faced when trying to plug in an existing non-CDB database. After resolving all these issues it has been quite painless process since then.

In short, upgrading 11g database to 12c pluggable database involves the following steps:
* Upgrade 11g database to 12c using the normal database upgrade procedures. This step will result in 12c non-CDB database.
* In the target CDB database plug in the new upgraded database as a new pluggable database.
* Run $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql in the new PDB. This step will convert the non-CDB data dictionary to a PDB data dictionary. After this step you can open the newly added PDB.

This post is mostly about the issues I encountered when running the last described step – executing $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql. Hopefully it’ll be helpful if you hit similar problems.

Version:, 2-node RAC
Patches: April 2016 PSU + OJVM PSU
Platform: Oracle Linux 6 x86-64

noncdb_to_pdb.sql takes a really long time to execute

This was the first problem I encountered. After 1,5 hours I killed my session. That was really weird, because executing it should onbly take about 20 minutes according to Oracle documentation. The step script was stuck on was:

-- mark objects in our PDB as common if they exist as common in ROOT

Looking at the wait events the session was not waiting for a blocker, it was actively executing many parallel sessions.
I found the following blog post that described the same problem and the described solution also helped for me: Link to Bertrand Drouvot blog

But one addition, instead of modifying the noncdb_to_pdb.sql script, I executed ALTER SESSION before running noncdb_to_pdb.sql.

SQL> alter session set container=newpdb;
SQL> alter session set optimizer_adaptive_features=false;
SQL> $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

noncdb_to_pdb.sql hangs at alter pluggable database close

Next issue I faced – noncdb_to_pdb.sql just hanged mid-execution and the statement it was executing was

SQL> alter pluggable database "&pdbname" close;

The session was waiting for opishd.

Solution: Apply bugfix for Bug 20172151 – NONCDB_TO_PDB.SQL SCRIPT HANGS DURING UPGRADE. This will just update noncdb_to_pdb.sql script itself to execute alter pluggable database “&pdbname” close IMMEDIATE instances = all; instead of normal close.

noncdb_to_pdb.sql fails with ORA-600 [kspgsp2]

That was a fun one 🙂 Not every time, but most executions noncdb_to_pdb.sql failed almost at the end with the following message:

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> drop view sys.cdb$tables&pdbid;
drop view sys.cdb$tables5
ERROR at line 1:
ORA-00600: internal error code, arguments: [kspgsp2], [0xBF3C9E3F8], [2174],
[recyclebin], [], [], [], [], [], [], [], []

Every time at the same drop view statement. Search in Oracle support did not give me anything helpful, there were many ORA-600 [kspgsp2] issues, but nothing matched my case. Finally I noticed that one argumnt was [recyclebin] and decided to try turning the recyclebin off for the session. It helped.

Successful non-CDB to PDB conversion

Getting successful execution of noncdb_to_pdb.sql required me to:
* Apply patch 20172151
* Running noncdb_to_pdb.sql using the following sequence of commands:

SQL> alter session set container=newpdb;
SQL> alter session set optimizer_adaptive_features=false;
SQL> alter session set recyclebin=off;
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Take care of the services!

This problem may be our environment specific, but I’ll describe it anyway.
We use services a lot, all applications that connect to the database get their own dedicated service. So the applications connect using JDBC connection string that looks something like this:


Where application is configured as a service using srvctl and is database domain name depending on the environment. The same application in QA environment will have connection string:


We decided to use only one CDB for all environments, but db_domain parameter cannot be different for each PDB. In order to not change the application connection strings I had to create the new services in srvctl using the FULL service name, then Oracle will not append the database domain name to the service name:

srvctl add service -database cdb -preferred cdb1,cdb2 -pdb newpdb -service
srvctl start service -database cdb -service

After adding the services for the first database all of them started just fine and applications connected just fine, but when starting the services for the second environment (qa) I got the following error:

srvctl add service -database cdb -preferred cdb1,cdb2 -pdb newpdbqa -service
srvctl start service -database cdb -service

ORA-44311: service application not running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 40
ORA-06512: at "SYS.DBMS_SERVICE", line 421
ORA-06512: at line 1

But when I tried to add a new service that did not exist previously it started just fine. I started digging into services on CDB level and found that all imported PDB-s also imprted their old short name services to CDB:

SQL> alter session set container=cdb$root;

Session altered.

SQL> select name from cdb_services order by 1;


I just assumed it can be confusing for CDB if different PDB-s have conflicting services running and I manually went into each PDB and removed the old short service names.

SQL> alter session set container=newpdb;
SQL> exec dbms_service.delete_service('application');
SQL> exec dbms_service.delete_service('application2');
SQL> alter session set container=newpdbqa;
SQL> exec dbms_service.delete_service('application');
SQL> exec dbms_service.delete_service('application2');

After that new services started just fine.

SQL> alter session set container=cdb$root;
SQL> select name from cdb_services order by 1;