Archive for the 'Oracle' Category
My last post was about generating AWR reports from SQL – not a very complicated case. “But” – little word, big meaning. And suddenly we get a strange error message.
In my example, I was especially interested in a Real Application Cluster’s “global” report. The subprogram call for that purpose is dbms_workload_repository.awr_global_report_text(), accepting the DBID, begin snapshot, end snapshot, an option field, and, l_inst_num for the instance number. Regardign the latter, Oracle documentation says: “List of instance numbers to be included in report. If set to NULL, all instances for which begin and end snapshots are available …”
But when I did that, I got an error that felt strange to me:
ORA-06553: PLS-307: too many declarations of ‘AWR_GLOBAL_REPORT_TEXT’ match this call
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 */ * from DBA_HIST_SNAPSHOT order by snap_id desc, instance_number desc;
Retrieving the report
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text( l_dbid=>123456789, l_inst_num=>1, l_bid=>24142, l_eid=>24143 ) );
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 Read more...
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!
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.
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.
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.
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. ;)
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?
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.