Pythian Group

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.

Read the rest of this entry . . .

This Friday, at noon, Paul Vallee and Andrew Waitman, Pythian’s owner and CEO, respectively, have an exclusive lunch with Larry Ellison at the Oracle head-quarters in San Francisco, CA.

As you have already guessed, Mr. Ellison has personally invited Paul and Andrew as a recognition to an unparalleled heroic efforts by Pythian to motivate and support 42 (forty-two) of its most talented DBAs to receive recognition as Oracle ACEs and ACE Directors, due to their wide contribution to the Oracle and MySQL communities around the world.

The Pythian executives will be accepting the awards on behalf of the 42 DBAs from the hands of Larry himself. The agenda of today’s meeting also includes the random selection of 13 of those 42 DBAs; the lucky ones will have the pleasure to enjoy an afternoon of sailing on Larry’s boat later this month. They have committed to go sail around the globe 42 times.
Read the rest of this entry . . .

A few days ago I learned about this year’s NoCOUG SQL Challenge and decided to to put the gray matter between my ears to work. I’ve been teaching a MySQL course this week and my first impulse was to use my MySQL VM to test my solution attempts. However, I eventually decided to use Recursive Subquery Factoring to solve the proposed problem and had to switch to an Oracle 11gR2, since it’s the only database that implements this feature that I know how to use (are there any others?).

I was happy with my solution, but frustrated that I couldn’t run it on MySQL. So I decided to try to make it somehow work on MySQL.
Read the rest of this entry . . .

Once again the great Wizards of Northern California have reached out to the community, pleading for help in the deciphering of one more challenging riddle. The second edition of the NoCOUG SQL Challenge has been published and is open for submissions! This time Iggy and his ensemble came up not only with a SQL challenge but also with a brain-bender riddle that must be resolved before you can start coding your solution. Very nice!

I’ve taken my stab at the problem and described my solution below. If you want to make a fresh attempt at the problem, stop here, otherwise scroll down for my solution.
Read the rest of this entry . . .

Last Friday in the Sydney Oracle Meetup I talked about Oracle Flashback technology and how it helps to reduce downtime. The session generated great interest among the attendees, which led to interesting discussions and many questions about the subject.

Some of the questions couldn’t be answered during the meeting so I’ve followed up on them and I’m posting the answers here since they may be of interest for many others.

Q: Is there a separate background process for writing flashback logs?

A: Yes. Read the rest of this entry . . .

It’s only one week to go now and the program for the AUSOUG National Conference Series 2009 is out. I’ll be presenting on the first day in Perth (Nov 10th) about Oracle Flashback technology.

I’m looking forward to attending the conference in Perth, not only because I’ll be presenting there but also because it’s my first time in Western Australia. All going well my presentation will be honed before the weekend and I’ll be arriving in Perth still this week, on Friday, to enjoy an extended weekend in Perth and Margareth River wine region with my wife before the conference begins.

This post originated from a quick discussion we had internally on how to quickly and easily compare schemas between two Oracle databases. I learned about Sheeri Cabral’s post with a quick comparison solution for MySQL databases and I though of using a similar approach for Oracle. I did some testing and it worked quite well.

There certainly are tools in the market, free or not, that do this for us, and even generate scripts to correct differences. The steps below only go as far as to tell you what the differences are. However, they don’t require any additional tool and can be easily executed in any *nix or Windows environment.

The procedure uses the Oracle Data Pump utilities to extract one schema’s metadata and the Unix diff command to compare them. The metadata files generated by Data Pump, though, contain a lot of information about tables and indexes statistics that add too much noise to the diff output, since they usually differ a lot between databases. To eliminate that noise, I used the following small Perl script to pre-process the metadata files: Read the rest of this entry . . .

Have you ever been asked to restore a database and wondered which backup files were available? And if many were available, which ones you should use, and in which order?

Getting familiar with the msdb schema, especially the backupset and backupmediafamily tables, helps to answer that question since all the backup history is stored in that database. But you would still have to check if the backups are still available on disk (tape?) and figure out what are the commands to restore, and the order in which to restore each file.

Okay, this is not a difficult thing to do, but when you do it over and over again, it becomes tedious, and the automation bug in you starts to look for a better and quicker way to handle it. The bug in me found the following answer.

Read the rest of this entry . . .

I love puzzles. So when I heard about the NoCoug SQL Challenge I felt tempted to give it a go.

The Northern California Oracle Users Group (NoCoug) has challenged us to find a good way to calculate the probability of getting different sums for x throws of a n-sided die using only SQL. The probabilities for the faces of a single die are stored in a table and that’s all you need to start playing with the problem. The SQL Challenge rules can be found on the NoCoug website, along with some other relevant information.

After working out my very first solution, I read the rules and found it wasn’t fit for the challenge, as it used non-SQL extensions (SQL*Plus). So I started again, this time using pure SQL. I came up with a few options but wasn’t happy with them from a performance perspective. They needed more sweating.

I find that walking is very good for thinking. Whenever I can, and when weather permits, I walk home from work at the end of the day. The distance between work and home is about 6 km, which takes me around one hour to cover. After you’ve done it a few times the walking becomes automatic and  you don’t have to think about it anymore; obstacles, kerbs, corners, street-crossings are all handled in auto-pilot mode. Then, as you don’t have anything else to do, you think.

During the days I was working on the challenge solution, the SQL query used to occupy my thoughts along most of my (almost) daily walk. I decided to use only standard SQL features and wanted to be able to run the solution on both Oracle and SQL Server. While walking, I started thinking on ways to improve my initial solution’s performance without having to resort to non-standard tricks.

Read the rest of this entry . . .

Early in 2008 I came across a toolkit that Microsoft had released a couple of months before, called RML Utilities. I downloaded RML Utilities from the Microsoft website, started playing with it, and found it quite interesting.

Then I tried to find more information about it, blog posts, stories of people using it, only to realize that not much existed about it. Back then, Google only had a vague idea of it.

More than one year after that, and one cumulative update later, I still find that relatively few people in the SQL Server world know about this toolkit. When I ask a SQL Server DBA about RML Utilities it’s not uncommon to hear in reply: “RML-what?”

Read the rest of this entry . . .