Pythian Group

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.

Argh! Of all the frustrating, partially-completed features Oracle has released, this is the most frustrating. Did I mention this frustrating feature is frustrating when you get bitten by it? Why? Because you only need it when you actually really, really need it.

What am I referring to? FLASHBACK QUERY on a table that lives in a database with a large UNDO_RETENTION specified with lots and lots of UNDO_TABLESPACE space.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     604800
undo_tablespace                      string      UNDOTBS_1
SQL> 

So, what do we have here? UNDO_RETENTION to set somewhere in the 7-days range. How much space do we have in the tablespace? Read the rest of this entry . . .

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