Pythian Group

Please feel free to submit the backup and recovery and DR topics you want to discuss – just mention in the comments and I’ll make sure they are on the agenda!

What: Sydney Oracle Meetup #4 – Backups, Recovery and Disasters
When: May 15, 2009 5:30 PM
Where: Our usual location in CBD see even page for details.

Meetup Description:

SOM #4 is about Oracle backup and recovery. As usual, Pizza and drinks arrive at 5:30PM and we start the presentation at 6PM. We should be out by 8:30PM with optional post-event program. It’s Friday night in the end! Read the rest of this entry . . .

The story you are about to read is based on actual events. Names and paths have been changed to protect the innocent. I call this scenario “The Perfect Storm” because it took just the right combination of events and configurations. Sadly, this doesn’t make it an unlikely occurrence, so I’m posting it here in hopes that you’ll be able to save yourselves before it’s too late.

I have always had a preternatural dislike for using REDUNDANCY as a retention policy for Oracle RMAN, greatly preferring RECOVERY WINDOW instead, simply because REDUNDANCY doesn’t really guarantee anything valuable to me, whereas RECOVERY WINDOW guarantees that I’ll be able to do a point-in-time recovery to anytime within the past x days. Plus, I had already been burned once by a different client using REDUNDANCY. With the story I’m about to tell, this dislike has turned into violent hatred. I’m going to be light on the technical details, but I hope you’ll still feel the full pain.

Read the rest of this entry . . .

I was doing some research over the weekend on how transactions work “under the hood” in MySQL. I thought it might be enlightening if I wrote about what I found.

The database system must resolve the situation where, for whatever reason, the database server stops working in the middle of a transaction.  Perhaps the power goes out, the hard drive crashes, or the CPU disappears in a cloud of smoke. Maybe the MySQL Server itself crashes for some reason. What does the MySQL Server do when operation resumes?

Potentially, there are transactions in an inconsistent state. How are inconsistent transactions resolved? They are resolved through a recovery process involving log files that are called transactional logs. There are two kinds of transactional logs: redo logs and undo logs.

Read the rest of this entry . . .

I finished uploading the backup presentation that I did last Monday at the Boston MySQL User’s Group. It is here: http://www.paragon-cs.com/presentations.

I cover the basics of backup/recovery and disaster planning. Total time is about an hour and three minutes. It was a lot of fun and the Bostonians seem to appreciate it. Thanks, Sheeri for filming and editing!

A little over a week ago, a teammate and I were trying to use Oracle’s FLASHBACK TABLE to undo an “oops” UPDATE statement that a client’s developers had run on one of their test databases, clearing data from two columns in all rows of the table. The statement was actually part of a script that also contained ALTER TABLE statements to add columns. This is important to note because FLASHBACK TABLE will only let you go back as far as the most recent DDL against that table. To quote the SQL reference, “Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.”

This led me to another question: Is there a way to directly see to precisely what date and time you can flashback a table? The developer couldn’t give me a precise time, only that the UPDATE statement was executed immediately after the structure-changing DDL, making my target window very small. Naturally, one would think that the LAST_DDL_TIME in the DBA_OBJECTS view would hit that nail on the head. However it turns out that the key bit of that SQL reference quote is “change the structure of the table.”

It turns out that there are a few statements that will update the LAST_DDL_TIME without changing the table structure. For example, GRANT and REVOKE statements, which provide a user with certain privileges on an object, will trigger an update to LAST_DDL_TIME. You can then go ahead and flashback the table prior to the privilege change. Another item to note is that a prerequisite to FLASHBACK TABLE is to enable row movement on that table, via (you guessed it) an ALTER TABLE statement. The ALTER TABLE foo ENABLE ROW MOVEMENT statement also bumps LAST_DDL_TIME, but obviously doesn’t block FLASHBACK TABLE from going past it in time.

The bottom of all this is that you can’t use LAST_DDL_TIME to determine just how far back you can go with a FLASHBACK TABLE statement, as you can most likely go past it due to various non-structure-changing DDL statements that affect that timestamp.

Here’s a little demonstration to illustrate this point:

Read the rest of this entry . . .

Thinking I had something new, I wrote this article about recovering deleted files. However, it turns out Frits Hoogland had already blogged about recovery of deleted files on linux, as Frits pointed out in a comment on my blog, where he also mentioned a metalink note on this matter.

The note ID is: 444749.1 “Retrieve deleted files on Unix / Linux using File Descriptors”. I went and looked into it and the procedure it describes.

Although it does explain how to recover the deleted file, this procedure will leave the database in an inconsistent state. It will corrupt your database. Queries will produce the wrong results randomly, depending on cache usage, how busy the database is, et cetera.

(Before you read the details, I would like to point out that this metalink note is not fully reviewed, as it states in the very beginning of the note:)

“This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.”

Read the rest of this entry . . .

So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.

This procedure will even work if your database is in NOARCHIVELOG mode.

You may have reached this posting through Google, and in a rush to get the solution right away, so here it is.

The recovery is done in two phases.

Phase 1: instant recovery to prevent Oracle errors (like ORA-27041 “unable to open file”, ORA-01110, ORA-01116)

1. Find the PID of DBWRITER for the right database.

   ps -ef | grep dbw0_SID_OF_YOUR_DB
   oracle   12272     1  0 10:55 ?        00:00:00 ora_dbw0_test
   oracle   12941 11501  0 12:36 pts/0    00:00:00 grep dbw0_test
   

2. List the deleted file handles for that DBWRITER process.

   ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted
   
   lrwx------  1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
   lrwx------  1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)
    

3. Create a symbolic link to your datafile with the original name.

   ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf

   That’s all. Now you are no longer going to get errors. However, if your database goes down now, you will lose that datafile for good.

Phase 2: restore the file
Read the rest of this entry . . .