Oracle XE Backup Restore Problem

backuporacle-xerestore

I have a Oracle XE 11g on Windows 7. I try to backup my database and restore it.

First, i backup my database with running backup.bat script which is in C:\oraclexe\app\oracle\product\11.2.0\server\bin which ended with no error.

Then, for testing i drop a table with this command drop table karzarar; and then committed.

I restore my backup by clicking Restore Database from Startup menu. It restored with no error.

But when i connected to the database and try to select the table that i dropped it gives me error.

SQL> select * from karzarar;
select * from karzarar
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I fallowed the instructions from there to backup the database.

Did i miss anything?

Best Answer

After datafiles are restored, oracle performs complete recovery by applying all changes from archived and online redolog files. Those changes also covers "drop statement" you did run after the backup. Hence, the recovery after the restore applies "drop table" statement during "restore database"

In order to recover drop table stament, you need to choose/perform incomplete (time/SCN based) recovery. That will bring database to a time before the drop statement is run.

Related Question