Performance problems with OEM AWR warehouse

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:

EXcellent blog post on SQL Profiles

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

OEM 12 AWR Warehouse – Not quite the finished product (yet)

My colleague John Evans  sits next to me and after hearing another grumble encountered while using the 12c OEM AWR Repository I asked him to write-up some of the issues he had come across and their fixes / workarounds. It does appear to me that the idea is fantastic but it is not been given sufficient exposure to sites that would be wanting to put a lot of data into it from various sources and therefore a lot of the necessary functionality is not robust enough as yet. This is John’s summary

Background – What is it and why you might want to make use of it

The AWR contains a wealth of useful information, primarily used for troubleshooting it could also be used for; trend analysis of system usage, capacity planning and forecasting, year on year comparisons… and probably many more tasks that the DBA usually ends up being assigned. It helps to be able to keep this information around for a considerable period of time, perhaps a year or more.

However with a great wealth of information comes a not inconsiderable volume of disk usage.. So how do you balance retaining data for extended periods against avoiding overburdening your databases? Use a repository! Keeping the data centrally reduces the overhead at source databases, means AWR reports can be run from a single location for your entire estate, and for the slightly more adventurous could mean a single database from which to draw information to feed an Apex dashboard.

The before

Previously at this site there was a in-house written solution which used a combination of PL/SQL packages, DBA_SCHEDULER jobs, UTL_FILE and database links. Our home-brewed version had 2.5Tb of data covering 400 days per database. While it worked well there were a number of issues that cropped up as the volume of source databases increased:

1) Work required to restart failed extracts (Updating control tables in the source database and repository then restarting the extracts)

2) Fetching extract files from remote sites which failed during transfer

3) All databases were experiencing a race on SCN use due to the use of DB links (There’s an Exadata data warehouse on site driving SCNs up…)

The after

As of Enterprise Manager 12.1.0.4 there is now a built-in AWR warehouse functionality, all one need do is donate a database in which to store the AWR data. Even better, if something breaks its Oracle supported

What it provides:

1) Job scheduling / retry mechanism using OEM’s job scheduler

2) A dashboard for viewing the volume of uploads as well as a central screen from which to run AWR reports from

3) Integration with OEM, utilising existing credential sets, you offload running AWR reports to a dedicated system etc

The caveats

Well there had to be a few as it is a new solution… but truth be told there are numerous issues which tend to result in the new solution taking more effort to maintain than the old one.

1) If you add a source database which contains snapshots > retention period in the AWR then they will not be purged.

-> This is fixed using dbms_workload_repository.drop_snapshot_range (and then updating dbsnmp.caw_load_metadata)

2)During the loading operation a schema is created to stage the snapshot data. This site uses a strong password policy, as a result the generated password used by the AWR is of an insufficient strength so the schema creation fails and the load as a whole fails.  A fix to this is pending as enhancement request 21291950

3) Cannot specify an alternative location for the dump file extract. While there is a property in DBSNMP.CAW_EXTRACT_PROPERTIES named ‘dump_dir_1’  (Where 1 is the instance number so there are additional rows for RAC databases) this cannot be modified without breaking the process. This is currently raised as bug 21826657 – The short of it is that the process expects the dump file in the agent instance directory, if is written out anywhere else then it cannot be zipped up and transferred… Problematic as some systems at this site generate 4GB+ dump files and fill up the local file system.

4) Dump files will not be retrieved in a RAC installation if the agent executing the collection job is on a different node to where the extract was written to.

e.g.

4.1) Extract job runs on dbnode1, dump file is written to dbserver1:/u01/app/oracle/product/gc12.1/agent12c/1_27CBB7FC057C24F0E0533104580AC38A_4108517940_11518_11603.dmp

4.2) Collection job runs a number of hours later on dbserver2… DBSNMP.CAW_EXTRACT_METADATA has a row for the file but the file does not exist on dbserver2! Fail! This mainly tends to happen with the ‘Upload Snapshots Now’

(Note, you cannot use a shared file system for the dump files – see point 3. Running multiple agents from the same directory is not going to work either)

5) The retention period and the extract schedule cannot be modified once the AWR is configured, the Oracle provided fix is:

From Oracle Support:

“As a work around, you can execute the following steps to Reconfigure:

– Please remove all the source databases from the AWR Warehouse console UI .

– After removing _all_ the source databases, the ‘Configure’ button on ‘Settings’ page will be enabled.

– Click it to re-configure the warehouse.

– After re-configuring, add all the source databases”

That’s not particularly user-friendly when there are 100+ databases with a combined totally of nearly 1TB of snapshot data. The alternative is to modify the relevant values in the below tables

DBSNMP.CAW_PROPERTIES – Repository DB

DBSNMP.CAW_EXTRACT_PROPERTIES – Each source database

6) The AWR dashboard – The pane that displays the number of incidents also displays loading errors… but /only/ if there is an incident! The errors are inserted into DBSNMP.CAW_LOAD_ERRORS

After all that… if something breaks and your snapshots aren’t loading you have to go trace the problem through the OEM job log step by step, then the import log, then the repository error log… and still have to update all the control tables at both ends if dump files get corrupted (because the repository dump area filled up). More complexity, more to break, more to fix – when it works properly though it is rather nice!

Once you have fiddled with the parallel load parameter however you are firmly on your own

7) The alerting. After all the above issues, including where the AWR stopped loading snapshots entirely (point 2) there is no out of box alerting of when there are issues with loading data and you cannot readily review the errors via the dashboard (point 6)

Nice to haves in a future release

1) Automated cleanup of the expdp/impdp logs on the source and repository database servers

2) Ability to track switchovers – at the moment you need to add the new primary to the AWR when you switchover – and you cannot disable the OEM CAW_TRANSFER job once the target is switched over (So it runs against the now standby database and fails silently).

The details are stored in the repository table dbsnmp.caw_dbid_mapping but not used (yet)

As for the CAW_TRANSFER job, the outstanding runs can be deleted from the OEM job scheduler.

Quirks

For source databases older than 11.2.0.1 the capture time of the snapshots is not listed in the ‘Search and Select’ popup window (Though if your data source is the database itself and not the AWR repository it /does/ populate the times)

The worrisome bits

The documentation states that the DBA role be granted to the user which runs the extract (which will be SYSTEM):

From: http://docs.oracle.com/database/121/TDPPT/tdppt_awr_warehouse.htm#CACJBEBG

Adding and Removing Source Databases

A source database whose AWR data you want to upload to the warehouse must be the same or earlier (to 10.2.0.4) as the version of the warehouse database. You can add and remove source databases provided you have access to the database target and to database credentials with execute privileges on the sys.dbms_swrf_internal package and the DBA role.

 

Since giving DBA to any user is not a good idea, this is the alternative:

grant execute on sys.dbms_swrf_internal to system;

grant execute on utl_file to dbsnmp;

grant execute on dbms_random to dbsnmp;

 

For further information see AWR Master Note: 1907335.1

12.1.0.2 enhancement – large trace file produced when cursor becomes obsolete

A minor change came in with 12.1.0.2 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 12.1.0.2  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:

SELECT TP.PROPERTY_NAME, TP.PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES TP, MGMT_TARGETS T WHERE TP.TARGET_GUID = T.TARGET_GUID AND T.TARGET_NAME = :B2 AND T.TARGET_TYPE = :B1

 

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.

Using SYSBACKUP in 12c with a media manager layer

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.

Creating standby database inc DG Broker and 12c changes

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

Overview

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

 

QUICKIE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = QUICKIE)
)
)

STAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAN)
)

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

 

LISTENERCLONE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))
)
)

(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STAN)
(ORACLE_HOME = /app/oracle/product/12.1.0.2/dbhome_1)
(SID_NAME = STAN)
)
)

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

server2 – tnsnames.ora

STAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAN)
)
)

LISTENERCLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524))
)
(CONNECT_DATA =
(SERVICE_NAME = STAN)
)

 

1. Start clone listener on server2

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

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 12.1.0.2 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 12.1.0.2 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

Issues around recreating a standby database in 12c

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

Discrepancies in v$parameter default values in 12c

In my last blog about security parameters I mentioned I had found some oddities in the default values for parameters in 12.1.0.2, 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 (https://docs.oracle.com/database/121/REFRN/refrn10275.htm) states that the default is ‘TRUE’.

To confirm this, I made a connection to a 12c (12.1.0.2) 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.

Security parameters in 11G and 12C

There are 5 parameters that are all prefixed with ‘sec’ in an 11g and 12c database. Actually that is a lie because one is now deprecated in 12c. They are all, as you might guess related to security. This blog is about changes in the default values and some thoughts about whether or not the default value is appropriate or not.

SEC_CASE_SENSITIVE_LOGON	TRUE in 11GR1 , 11GR2, DEPRECATED IN 12C
SEC_MAX_FAILED_LOGIN_ATTEMPTS	default 11GR1,11GR2=10, 12c=3
SEC_PROTOCOL_ERROR_FURTHER_ACTION	default is  CONTINUE in 11GR1, 11GR2, drop, 3 in 12c
SEC_PROTOCOL_ERROR_TRACE_ACTION	default is TRACE 11GR1,11GR2, 12c
SEC_RETURN_SERVER_RELEASE_BANNER	default is FALSE in 11GR1, 11GR2, TRUE in 12c

 

SEC_CASE_SENSITIVE_LOGON
Let’s cover the deprecated one first. This came along in 11g (as all 5 did) and is now deprecated as 12c  is forcing case sensitive passwords. In parallel the orapwd function in 12c no longer has the ignorecase option either.
The one application that I know does not support case sensitive passwords is EBS R12.1.1 but there is a patch (12964564) if you wish to upgrade to 12c (or even continue to run at 11GR1) .