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:

(more…)

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.”

(more…)

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
(more…)