Oracle DBA – A lifelong learning experience

Archive for the ‘12c new features’ Category

Performance problems with OEM AWR warehouse

Posted by John Hallas on December 20, 2016

The Enterprise Manager AWR Warehouse is designed to hold performance data from multiple databases  for long-term analysis. It promoses that it will save storage and improve performance on your production systems. In that it is indeed correct. However the warehouse itself does not seem to be performant when taking in multiple sources and retaining them long-term – 400 days in our case. Why 400 days is an obvious question that might be asked. Primarily because we are a Retail organisation and Easter is variable each year.


The AWR repository database is performing poorly during the insert stage of the upload process.
Just to quickly summarise the process:
  • A dmp file is extracted on the source database and transferred across to the AWR server
  • The dmp file is then imported into a temporary schema called AWR$XXXXXX (this loads quickly)
  • This data is then inserted into the main AWR tables inside the SYS schema. Is is this stage that is slow.

In order to completely isolate the issue, we altered a parameter, so only one AWR file gets loaded at once, cutting any contention / locking issues out of the equation:

Read the rest of this entry »

Posted in 12c new features, Oracle | Tagged: , , , | 3 Comments »

EXcellent blog post on SQL Profiles

Posted by John Hallas on July 5, 2016

Sometimes you across a blog entry and you think to yourself – ‘I wish I had written that’.

It is clear, it is on a subject you are interested in and the examples help illustrate the points clearly.

Well I did come across just such an article today. It was written by Franck Pachot and was on the subject of SQL Profiles

It was written 18 months ago and has only had 1800 hits. I think it deserves many more

Posted in 11g new features, 12c new features, Oracle | Leave a Comment » enhancement – large trace file produced when cursor becomes obsolete

Posted by John Hallas on October 15, 2015

A minor change came in with which is causing large trace files to be produced under certain conditions.

The traces are produced as a result of an enhancement introduced in an unpublished bug.

The aim of the bug is to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and it’s child cursors after the parent cursor has been obsoleted N times.
A parent cursor will be marked as obsolete after a certain number of child cursors have been produced under that parent as defined by the parameter “_cursor_obsolete_threshold”. In 12.1, the default is 1024 child cursors.

A portion of the trace file is shown below – we are only seeing it in a  OEM database at the moment and the problem with obsoleted parent cursors is not affecting us in a noticeable manner, although the size of the trace files and their frequency is.

----- Cursor Obsoletion Dump sql_id=a1kj6vkgvv3ns -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=0xc4720a8f0 phd=0xc4720a8f0


The SQL being:



The ‘feature’ is used to help Oracle support track issues with cursor sharing.

There is a parameter which can be used to stop or reduce the frequency of traces from MoS note 1955319.1

The dump can be controlled by the parameter “_kks_obsolete_dump_threshold” that can have a value between 0 and 8.

When the value is equal to 0, the obsolete cursor dump will be disabled completely:

alter system set "_kks_obsolete_dump_threshold" = 0;

When set to a value N between 1 and 8, the cursor will be dumped after cursor has been obsoleted N times:
By default, a parent cursor that is obsoleted is dumped when the parent SQL is obsoleted first time, i.e., N is 1.

alter system set "_kks_obsolete_dump_threshold" = 8;


The work around is to set the below underscore parameter that controls when the cursor will be dumped with a range of 0 to 8, 0 being disabled and 8 being after 8 iterations of being made obsolete etc.

We will be raising a support call re the cursor problem but probably setting the dump threshold to 8 at first and then 0 if we still keep on getting large traces. The default is set to 1.

Posted in 12c new features, Oracle | Tagged: , , , | Leave a Comment »

Using SYSBACKUP in 12c with a media manager layer

Posted by John Hallas on September 10, 2015

I think most large sites who have multiple support teams are aware of how the phrase “Segregation of Duties” is impacting the DBA world. The basic principle, that one user should not be able to, for instance, add a user, grant it privileges, let the user run scripts and then drop the user and remove all log files is a sound one and cannot be argued with.

With the release of 12c Oracle e added three new users to perform administrative tasks. Each user as a corresponding privilege with the same name as the user, which is a bit confusing.

SYSBACKUP – for RMAN backup and recovery work

SYSDG –  to manage DataGuard operations

SYSKM – to manage activities involving ‘key management’ including wallets and Database Vault

I have no real experience of key management so cannot comment on that. I do fail to see which type of user would be allowed to manage a DG setup and yet not be allowed to perform other DBA work on the databases, however it probably does mean that any requirement to login as ‘sysdba’ is now reduced which can only be a good thing. Read the rest of this entry »

Posted in 12c new features | Tagged: , , , , , | 2 Comments »

Creating standby database inc DG Broker and 12c changes

Posted by John Hallas on July 29, 2015

I thought I would refresh my knowledge of creating a standby database and at the same time include some DataGuard Broker configuration which also throws in some changes that came along with 12c


Database Name QUICKIE host server 1 ASM disk

Database Name STAN host server 2 ASM disk

Create a standby database STAN using ACTIVE DUPLICATE from the source database QUICKIE


(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1522))

server2 – listener.ora – note I have selected 1524 as that port is not currently in use and I do not want to interfere with any existing databases


(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))

(ORACLE_HOME = /app/oracle/product/


server2 – tnsnames.ora

(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))

(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))


  1. Start clone listener on server2

Read the rest of this entry »

Posted in 12c new features, Oracle | Tagged: , , , | 2 Comments »

Startup PDB databases automatically when a container is started – good idea?

Posted by John Hallas on July 27, 2015

I posed a note on the Oracle-L Mailing list around pluggable database and why they were not opened automatically by default when the container database was opened. The post is below

I am trying to get my head around the thing about how pluggable databases react after the container database is restarted.

Pre it was necessary to put a startup trigger in to run a ‘alter pluggable database all open;’ command to move them from mounted to open.

Now allows you to save a state in advance using ‘alter pluggable database xxx save state’ which does seem a step forward

However why would the default not be to start all the pluggable databases (or services as they are seen) not leave them in a mounted state. Obviously Oracle have thought about this and changed the trigger method, maybe due to customer feedback but I wonder why they have not gone the whole hog and started the services automatically.

I would much prefer to have the default to be up and running rather than relying on the fact that I have saved the state previously

I did get some interesting and very helpful responses. Jared Still made a couple of good points. The first being that the opening time for all the pluggable databases might be very long if you had 300 of them. That blew my mind a little and I must admit that I had considered scenarios where you might have half a dozen maximum, not into the hundreds.

I did a little test on a virtual 2 CPU, 16Gb server, already loaded with 6 running non container databases. I created 11 pluggables (I have created a new word there) from an existing one – each one took less than 2 minutes Read the rest of this entry »

Posted in 12c new features | Tagged: , , , | 1 Comment »

Issues around recreating a standby database in 12c

Posted by John Hallas on July 22, 2015

When you create a database in 12C it now creates a resource in HAS/CRS , which isn’t a problem

However, when you come to recreate a standby database, probably because it has got such a big lag that it is quicker to recreate than recover the log files, then you will see the following error message :-


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/22/2015 15:45:57
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 07/22/2015 15:45:57
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'STAN' comment= '' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

It did stump me for a while and I thought it was around having files in the ASM DATA group from the previous incarnation but removing them did not solve it.

The word ‘resource’ gave me a clue and looking at the resources using srvctl I could see that the database STAN already existed

srvctl status database -d STAN

Database is not running.

So the fix was obvious – and indeed the error message was accurate.

srvctl remove database -d STAN

Remove the database STAN? (y/[n]) Y

Posted in 12c new features, Oracle | Tagged: , , , , , | Leave a Comment »

Discrepancies in v$parameter default values in 12c

Posted by John Hallas on April 10, 2015

In my last blog about security parameters I mentioned I had found some oddities in the default values for parameters in, this is a more in-depth analysis of my findings.

Taking the parameter SEC_RETURN_SERVER_RELEASE_BANNER as an example.

Prior to 12c the default value for this parameter was ‘FALSE’, whereas the documentation for 12c ( states that the default is ‘TRUE’.

To confirm this, I made a connection to a 12c ( database and ran the following query:

select name, value,  default_value,  isdefault

from v$parameter

where name = 'sec_return_server_release_banner';
NAME                                     VALUE               DEFAULT_VALUE       ISDEFAULT
---------------------------------------- -------------------- -------------------- ---------
sec_return_server_release_banner         FALSE               TRUE                 TRUE

After confirming that the parameter had not been explicitly set in the parameter file, or as part of an alter system/session command, we could see that the actual value, held in ‘VALUE’, given to the parameter does not match the value provided by ‘DEFAULT_VALUE’ nor did it match the value it should have been assigned according to the documentation. Read the rest of this entry »

Posted in 11g new features, 12c new features, Oracle | Tagged: , , , , , , , | 2 Comments »