Point in time recovery of a dropped table

[yenni143]

Hi Friends,
Can somebody send me the detailed steps for point in time recovery of a dropped table?

thanks

[SIVAKUMARN]

Hi Yenny,

Simple Question, to understand the soln. u need some logical thinking.

I will explain u the scenario and try to understand...

Say evening 5 pm a gay comes and tell u that I dropped an important table in the morning 10 am and he needs that table with data and at the same time u shouldnot loose other data. OK this is your problem...

For this u need ur database in archive log mode - most important.

step 1.

Import database with full data and keep it in a different place and forget it for timebeing. say final.dmp

step2.

come to the original db and recover the database till time
(I think u know this until cancel or until time etc) say 10am
or check ur last archive log time and make it 9.30 to 10 am and recover till that archive.

Now u may loose all other data after that period. ok. but u have the table recovered by this way.

Now export this table , this table may contain 90 to 100% of its data. and keep it sepatately say table1.dmp

Now import the final.dmp but now u don't have that table in the database ok. Now import table1.dmp in the finaldatabase.

Now u got the table in the database and didn't loose any data

OK Now u Happy... Enjoy

[yenni143]

Thanks Sivakumar.

[rohitsn]

Hi,
This is ROhit Nirkhe,Oracle DBA,OCP from India.
In order to perform point in time recovery of a dropped table first ur database must be in archivelog mode.

I will give u a scenario so that u can understand the concept.
foe e.g Say at 12:00 noon a user drop a important table and he came to u at 1:00 pm saying that u want to recover the table.In this case u have to perform incomplete recovery.U have to take ur database to the point when the table existed.

Step 1: Shutdown the database with immediate/normal option
Step 2: Backup all the datafiles,controlfiles and redo logfiles
Step 3: Copy all the datafiles from the last backup
Step 4: Mount the database
Step 5: Give the following command
RECOVER DATABASE UNTIL TIME
'2000-02-19:11:55:30';
Step 6: It will apply all the redo logs till that time
Step 7: Open the database with resetlog option
Step 8: Backup the database again.

In case of any problem,please be write to
rohitsn@hotmail.com

regards,
Rohit Nirkhe,OCP





[QUOTE][i]Originally posted by yenni143 [/i]
[B]Hi Friends,
Can somebody send me the detailed steps for point in time recovery of a dropped table?

thanks [/B][/QUOTE]