Archive for the 'Oracle' Category

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

Situation

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.

Problem

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! Read more…



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.

2015-DB-Banner-468x180

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!
Martin



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

Oracle-inmemory-architecture

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.

Oracle-Inmemory-OLTP-and-OLAP

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

Thanks
Martin



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.

O_ACEIcon_clr

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.

Regards
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

2015-DB-Speaker-180x180

 

Abstract:

====================
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. ;)
Yours
Martin Klier

2015-DB-Banner-468x180



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?

IMG_20150205_135854_hdr

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.

select s.BEGIN_INTERVAL_TIME,
 round(t.BUFFER_GETS_DELTA/
 0.0001+
 (extract (day from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))*24*60)+
 (extract (HOUR from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))*60)+
 (extract (MINUTE from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME)))+
 (extract (SECOND from (s.END_INTERVAL_TIME-s.BEGIN_INTERVAL_TIME))/60)
 ,0) as BG_PER_MINUTE
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:

bg-over-time-from-awr

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 :)
Martin

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.



Martin Klier in DOAG.tv Interview: Oracle Standard Edition

A while ago, DOAG.tv interviewed me about Oracle’s Standard Edition and database system migrations. The material was published just recently, so I’d like to share it. Enjoy, and if there are questions, just let me know!

2014-Klier-DOAG-Interview

http://www.doag.org/home/aktuelle-news/article/oracle-database-standard-edition-versus-enterprise-edition.html

I have to add a corrigenda: SE RAC is not – at least not at the moment – limited to a number of nodes. It’s currently limited to CPU sockets, to be exact, 4 of them. So a four-node SE RAC is possible, if I did not completely misunderstand the licencing policies.

Disclaimer: Licensing and pricing here are my OPINIONS, and not a reliable source to make decisions or confront Oracle with. :) If you need some tailored licensing information, feel free to email info-at-performing-db.com for advice.

Regards
Martin



DOAG 2014 Presentation and Whitepaper online: Database I/O

Hi,

my #DOAG2014 presentation and whitepaper are online now!

“Oracle Core für Einsteiger: Datenbank I/O”

Presentation: http://www.performing-databases.com/uploads/media/OracleCoreDatabaseIO-Klier-2014.pdf

Whitepaper: http://www.performing-databases.com/uploads/media/Klier-Database-IO.pdf

20141120-Klier-Datenbank-IO

IMG_20141120_115919

Thank you all for attending!

Regards
Martin Klier




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.