Re: Recovery simulation

willy_gates wrote:

> Hello,
>
> I am testing hot backup and recovery.
>
> My manager is a bit unhappy about oracles redo log. I wonder if anything
> you lot can say will placate him.
>
> When I originally purchaced Oracle over SQL/Sybase/Postgres I said that it
> supported transaction logging. i.e. it kees a track of the transactions
> being performed on the tables.
>
> My manager assumed I meant that this scenario was possible:
>
> Suppose half way through Friday morning someone ran some SQL on the database
> which was discovered to be incorrect some hours after they commited the
> transactions (and after many other modifications to the database). You
> could restore from backup until the night before roll forward all redo until
> just before the dodgy SQL, then miss out the dodgy SQL, and *roll forward
> all other transactions*.

Suppose the dodgy transaction was to insert 500 new customer records, and some DML done after that point used those customer records. If you skip the creation of the customer records, how in all logic and common sense can you nevertheless ask for the subsequent DML to be performed?

> If this happened then the best Oracle can do is to roll us up to the dodgy
> SQL.
Correct.

> I understand that it rather depends on what the dodgy SQL is. For example if
> it is a dropped table then we can restore everything using a logical export
> of the table.

Correct.

> I think what he is getting at is that he wants to be able to review past
> transactions, perhaps have human readable redo.
>
> Is there anything else that Oracle offers?

Yup, it's called Log Miner, and if used appropriately could even give you the ability to recover up to the point of dodgy DML (called an incomplete recovery) and *then* re-perform subsequent DML semi-automatically (you extract the redo from the logs using Log Miner into a boring old SQL script, which you can then execute). Obviously, the dependency issue I mentioned earlier still applies, but with Log Miner you can check the redo by hand and make sure that any dependent DML does not make its way into the SQL script. A bit messy, but do-able.

There's a rather nice GUI Log Miner viewer in Enterprise Manager for 9i, if that's your tool of choice. Or you can do it all at the command line, depending on mood and preference.

Log Miner is a very nice tool, and should keep your manager happy.

Regards
HJR Received on Thu May 13 2004 - 04:19:30 CDT