Flahsback Database and Roll Forward

Following up on the previous post, now suppose the table is lost but you do not want to rollback the entire database to prior to the loss.
This is a clever way of doing this but it means living with the truncated table until some downtime is available.

Flashback until the appropriate time as outlined in the previous post. Then open the database read-only and not open resetlogs. Export the table before the loss occured. After the export is completed issue a RECOVER DATABASE command, so Oracle fast forwards to the time of clean shutdown. Open the database and then import back the Table. Thus you have the table before data was lost without having flashed back the entire database.

Restore the Entire Database with Flashback

Don't know how this will work on a large database, but looks like the easiest way to restore the entire Oracle database. On a home grown database, 3GB, it took just a few seconds.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance
shut down.

SQL> startup mount exclusive;
ORACLE instance started.
Database mounted.

SQL> FLASHBACK DATABASE to timestamp to_timestamp('20-04-2009 10:00', 'DD-MM-YYYY HH24:MI:SS');
Flashback complete.

SQL> alter database open resetlogs;

If on the other hand you are ok to just recover the database to the last scn, for example a Table has been accidentally truncated in a DWH, and no transactional activity had taken place before the truncate, you can recover the database back to the scn, using Flashback by

sql> select current_scn from v$database;

Current SCN
------------
2592544

Table truncated

sql> Connect / as sysdba
connected.

sql>shutdown immediate;
Database closed.
Database dismounted
ORACLE instance shutdown.

sql> Startup Mount;

sql>flashback database to scn 2592544;
Flashback complete

sql> alter database open resetlogs;

RMAN Correct Syntax for Until Time Restore

As brilliant as the book by Matthew Hart and Scott Jesse on High Availability with Oracle 10g RAC is, there are some unfortunate syntax errors which shouldn't be there.

For example on pp 304, the correct syntax for when specifying a point in time to recover from with RMAN should read as follows:

RMAN> run {
set until time "to_date('04-NOV-2003 12:00:00', 'DD-MON-YYYY hh24:mi:ss')";
restore database;
recover database;
}

and not the way it is printed in the book.