table recovery

[iris]

hi, I want to recover a lost table from my rman complete database backup. Is there any link showing this scenario.

thanks,

[mindfullness]

What version of Oracle you using?

Sorry for my English

[gandolf989]

If you are using 10g/11g, the table might be in the recycle bin. Otherwise you either have to restore the whole database to a point in time before the table was lost, or restore to another location to a time before the table was lost, and export the table. How you recover depends on how that table fits in with your application. i.e. If there are foreign keys you have to make sure that the data is there and the foreign keys are enabled again.

[iris]

hi, it is oracle 9i database.


thanks,

[davey23uk]

then you probably need to do tablespace point in time recovery and then export the table into you prod instance

[mindfullness]

tablespace point in time recovery in 9i?

[mindfullness]

You can using FLASHBACK QUERY option in Oracle 9i ( DML OPERATION). If table was dropped, then have to perform Incomplete recovery. Check documentation for more details.

Regards

[mindfullness]

Sorry davey23uk, my mistake. Oracle 9i support tablespace point in time recovery!

[rakeshvgemini]

Steps :

1. Find out what time the table was dropped using logminer.

2. Restore database to the other location (clone) before the time of table dropped.

from RMAN
- do the clone steps

and finally

run {
set until time "TO_DATE('17-APR-2006:12:26:50','DD-MON-YYYY:hh24:mi:ss')";
restore database;
recover database;
}
alter database open resetlogs;

finally export the table and import it into the production database.