Recovering from user errors

Sometimes you may have to recover from some unintentional but undesirable user errors like:

• a table has been dropped or truncated; or
• some data has been deleted and committed which was not required.

User errors normally requires some time based recovery procedure. Here the database will be running and there will be no outward signs of a failure, but you have to recreate the database at a certain point of time.

• Shutdown the database:

SQLPLUS> connect SYS as SYSDBA
connected
SQLPLUS> shutdown
Database Closed
Database Dismounted
ORACLE Instance Shut Down

• Copy all the datafiles, redo logs and controlfiles from an earliest hot backup.

• Recover the database:

SQLPLUS> startup mount
Oracle Instance Started
Database Mounted

SQLPLUS> recover database until 2000-07-11:15:30:00 using backup controlfile

Note that you want to recover till 11th July, 2000 3:30 PM. The format of the time string is YYYY-MM-DD:HH:MI:SS. When SQL*Plus prompts you for archived logfiles to apply, just enter AUTO. The database will be recreated till the indicated time. After that the following message will appear.

Media Recovery Complete.

• Open the database:

SQLPLUS> alter database open resetlogs;
Statement Processed
SQLPLUS> exit

• Now the old archived logfiles are useless. Delete them and take a backup of the database.