Recovering from user errors
From Oracle FAQ
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.