How to create an Oracle AWR report with SQL and PL/SQL

AWR is a great tool (but needs diagnostics pack). It can create very useful reports for performace analysis over a given period. Most people will pull the AWR Report  from the OS level, with ‘@?/rdbms/admin/awrrpt.sql” (or one of the other scripts there). But sometime it’s not possible to access a database server’s OS level, or gain permission to do so. So we also can create the whole bunch of possible AWR and ASH reports from SQL level, too. We can have it in text or HTML, it’s just a matter of personal taste. I always preferred the text version, but there are many facts pro HTML. Anyway, here is my cheat sheet how to do it, and the difference in RAC.

What we have to know

Our database ID (DBID):

select dbid 
  from v$database;

The period we are interested in, spoken in snapshot IDs:

select /*+ FIRST_ROWS */ * 
  order by snap_id desc, instance_number desc;

How to create an Oracle AWR report with SQL and PL/SQL

FROM TABLE (dbms_workload_repository.awr_report_text(

Read more…

Oracle 12c RMAN delete obsolete behaves interactively in DBMS_SCHEDULER job type BACKUP_SCRIPT


In Oracle Database 12c, we have the long-missed feature of DBMS_SCHEDULER job type “BACKUP_SCRIPT”, that allows us to create backup jobs without creating them as an OS file, and without need for a wrapper script that’s called by a job type EXECUTABLE (as we usually did that in the last years).

Sometimes we also have to use the RMAN command “delete obsolete” to get rid of unneeded backup pieces. When executed interactively, it comes back with the question “Do you really want to delete the above objects (enter YES or NO)?”. But when running from a script, it just deletes:

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           67     30-JUN-15 
Backup Piece         67     30-JUN-15          +ORAFRA/...nnndf...
deleted backup piece
backup piece handle=+ORAFRA/...nnndf0_tag...
Deleted 1 object
Recovery Manager complete.


Strange is, that when using it within a DBMS_SCHEDULER job type BACKUP_SCRIPT, “delete obsolete” thinks it was running interactively, and the scheduler seems to compensate it, by simply answering “NO” – what leaves us with a growing number of old backup pieces!
(Taken from v$rman_output):

Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           61     30-JUN-15
Backup Piece         61     30-JUN-15          +ORAFRA/.../nnndf0_tag....
RMAN-06546 Error occurred getting response - assuming NO response

DOAG Database Conference 2015 begins #DOAGDB15

It’s time! June 16th, the DOAG Database Conference takes place in Düsseldorf, Van Der Valk Airport Hotel. Twenty-two specialized talks about Oracle Database, Replication, Tools and Security promise a great techie-day. See the schedule of lectures here.


I’m looking forward to seeing you there, having great corner talks about Oracle, life and lemons, tweeting with hashtag #DOAGDB15 and enjoying this great environment the hotel provides.

I’m also glad and honoured to be on the list of speakers: “Oracle Core for Beginners: In-Memory Column Store” at 11am.

Take care and see you tomorrow!

DOAG BI Conference: Oracle Core (for Beginners): In-Memory Column Store


On Thursday, April 23rd I’m speaking at DOAG BI 2015, the leading Oracle Business Intelligence Conference in Germany. (#DOAGBI15)

My contribution is: “Oracle Core für Einsteiger: In-Memory Columns Store”. The lecture will show, how the Oracle Database In-Memory option works “under the hood”, and how it is used to the best effect.

Please find the presentation here for download.


Looking forward to seeing you there, or discussing the presentation in the comments.


Chemnitzer Linux Tage 2015

Last weekend, I had the chance to attend the Linux Days Chemnitz. It’s an annual meeting of the German Linux family with roundabout 2,500 attendees and a FANTASTIC atmosphere. It was two days of hearing tech talks, enjoying rich nerd content and talkin’ shop.


Great atmosphere in the Great Hall


Great entrance gift: “Free Culture” by Lawrence Lessig. Thank you very much!

Read more…

Martin Klier Oracle ACE

I feel VERY proud, honoured and grateful, that Oracle awarded me with the Oracle ACE in December 2014. The Oracle ACE program is a community reward, and encourages us to participate, enrich, promote and organise Oracle community events.


When speaking about the community, first of all I’d like to highlight my Oracle User Group of Germany, the DOAG (Deutsche Oracle Anwendergruppe). They are doing lots of nice, and educational things, lifting the fog, and practicing free and self-assured community work. Helping to spread this in our region, at conferences and events, is a pleasure. I’m very proud to be part of this great team.

A little less, due to the geographical distance, but with very similar motivation and experience, I feel connected and involved with the U.S. pendant IOUG (Independent Oracle Users Group). They are open for the international crowd, and it simply feels good to be there.

And I want to say thank you to three people, for all they do, think and say about me. These very special folks are Björn, Craig and Dietmar. I’m proud to call you friends.

My Oracle ACE program shipment just arrived today.

My Oracle ACE program shipment just arrived today.

Just in case somebody cares, here’s my Oracle ACE profile.

I hope to keep up the level, and will continuously try hard to find the time to give back knowledge to the community.

Martin Klier

Speaker at DOAG 2015 Datenbank – “Oracle Core für Einsteiger: InMemory Column Store”

The DOAG 2015 Database conference is on the horizon: June 16, 2015 in Düsseldorf, Germany.

I’m proud to announce my participation as a speaker, as I was honoured with in the years before. This year, my part will be a new “Beginners” talk in German: “Oracle Core für Einsteiger: InMemory Column Store




Der Vortrag richtet sich an Einsteiger oder IT-Fachkräfte die nicht in Vollzeit als DBA arbeiten, jedoch Interesse an Datenbanktechnologie haben, bzw. für Entscheidungen hinsichtlich eingesetzter Technologien, Features und Lizenzen Anhaltspunkte suchen.

Der InMemory Column Store ist eine relativ neue Struktur der Oracle Datenbank 12c, und wird vom Hersteller massiv beworben. Der Vortrag möchte zeigen, wie diese sogenannte “In-memory Datenbank” konzipiert ist, funktioniert, und in welchen Szenarien sie sinnvoll eingesetzt werden kann.

I’m looking forward to seeing you there, for tech talk, hanging out and more tech talk. ;)
Martin Klier


DOAG Noon2Noon Event Nürnberg / MySQL vs. Oracle – Review

It was time to try something new in our DOAG Database Community. And please, please just not another frontal conference: twenty guys look forward, one looks back, like eight-oars-and-coxswain. I don’t know if DOAG invented it, but it was a success: The Noon2Noon Event.

How does it work? It’s like 24 hours of BarCamp, starting with a lunch, having a topic-of-the-day, lasting overnight, with winter barbecue, compatible for elders (thus, with Hotel and beds, no after-midnight hacking :) ). Ah, forgot to mention: One single talk being completely away from the topic, but somehow related to our work.

This first time, topic was “MySQL versus Oracle Database”. Johannes Ahrends and Oli Sennhauser as “headliners” ignited discussions about features, technologies and strategies known from Oracle, and how they are (or aren’t) in MySQL. And vice versa, but less. Participants came from all over Germany, plus Denmark and Switzerland. End users, consultants, technocrats, “boys” who go ahead and fail, and “girls” who test and succeed…

I greatly enjoyed the open format – listening, talking, drawing, discussing, swaggering, ignoring, pushing, pulling – the full repertoire. :)

From the technological aspect, it was great to learn something about MySQL – how consistent reads, clustering, and lots of other stuff works, that we never ever thought that it could be done different from the way the Oracle Database works. And that MyISAM isn’t the norm, but just another PITA. :) Go for InnoDB. What is MariaDB? And what the fork is Percona Server?


Oli swiss-talks about MySQL.

Read more…

Oracle 12c Multitenant: impdp fails w/ ORA-31625 and ORA-01031 because of Database Vault

Things are different in Oracle Database 12c with multitenancy option. My most recent example:

I tried to import a schema (new name “NEWSCHEMA”) with datapump IMPDP and REMAP_SCHEMA into the same pluggable database it has been exported from with EXPDP immediately before (name “OLDSCHEMA”), running as SYSTEM. I’m doing things like that with DBA permissions, since my users have lots of grants and stuff in the schemas, and when a DBA does the export and import, all settes right. (See the details for commands and parfiles below.)

But IMPDP fails with
ORA-39083: Object type INDEX failed to create with error:
ORA-31625: Schema NEWSCHEMA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges

So what? I’m SYSTEM and thus, DBA, and the user NEWSCHEMA is there. And SYSTEM of course has the “IMPORT FULL DATABASE” privilege, it’s a DBA! So you may think.
Read more…

Oracle: Did my SQL get worse over time? (AWR query)

Sometimes, we get statements to look at, and are told “it’s getting worse and worse”. Since DBAs are well advised not to take anything for granted and only to believe what they see with own eyes, here comes a SQL on AWR to see Buffer Gets per Minute, over time.

 (extract (day from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))*24*60)+
from dba_hist_sqlstat t, 
 dba_hist_snapshot s
where t.snap_id = s.snap_id
 and t.dbid = s.dbid
 and t.instance_number = s.instance_number
 and t.SQL_ID='vwxyz'
 and s.begin_interval_time between sysdate-90 and sysdate
order by t.SNAP_ID 

Pseudocode explanation:
Get all SQL ID’s from the historical SQL STAT view. Refer it to the snapshot details to get real-world date/time of the events. Since nobody knows how long the AWR snapshot interval was at the time of interest, make BUFFER GETS relative per minute by dividing each BUFFER GETS DELTA by the length of its interval.
Configure the SQL_ID and the interval to be reviewed in WHERE.

You can create a chart like that when exporting the result to the spreadsheet software of your choice:


Basically, this concept will also work with all other columns available in dba_hist_sqlstat, such as CPU consumption, Interconnect load, Disk IO etc.

“Everybody lies”, says Dr. House :)

PS: Please keep in mind, the system(s) to run this query on, will need Oracle’s Diagnostics Pack licensed on top of Enterprise Edition.