Home » RDBMS Server » Backup & Recovery » recovering lost table..
recovering lost table.. [message #189055] Wed, 23 August 2006 01:10 Go to next message
Messages: 142
Registered: March 2006
Senior Member
can someone explain me or give link for foll. recovery scenario.

7:00 am create tbs1
7:15 am create table1 in tbs1
7:30 am complete offline backup
7:45 am table1 is dropped
8:30 am by this time more 5 tables with lots of data added
9:00 am dba realizes and needs to recover table1 and maintain the other 5 tables also

Re: recovering lost table.. [message #189059 is a reply to message #189055] Wed, 23 August 2006 01:32 Go to previous messageGo to next message
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Can we do it this way..

1. Restore the complete backup on a test server.

2. Export the table1 table.

3. Import it into the prod. server.
Re: recovering lost table.. [message #189620 is a reply to message #189059] Fri, 25 August 2006 05:01 Go to previous message
Messages: 506
Registered: March 2005
Senior Member
Buddy In Oracle 10g below query will recover table


Flashback Table feature in Oracle Database 10g, which makes the revival of a dropped table as easy as the execution of a few statements.


But before 10g i mean to say in 9i or 8i we have to do time based recovery.

For the senario given by the tridentadm below are steps to recover table.

 Restore backup, ONLY and ALL DATAFILES.

 Goto server manager in 8i OR SQL In 9i.
 SQL>Startup mount.
 SQL>Recover database until time ‘2006-08-23:7:44:00’;
 SQL>Alter database open resetlogs.

Note:- As per senario made by tridentadm on 2006-08-23:7:45:00 table was drop which we have to recover.

Any how its difficult to recovery complete database in this senario.

But if you know only 5 new table were added after deleting table then we can do one thing before restoring from the backup take an export of full database.

And after below written query

SQL>Alter database open resetlogs.

Import the 5 table form full export.
Note :- before importing dont forget to restore from backup and do all those step which was shown earlier...

Now, consider after droping table, other than adding 5 table few update on old table is done than what we can do... so take full export, before restoring database.


[Updated on: Fri, 25 August 2006 05:04]

Report message to a moderator

Previous Topic: Rman theory
Next Topic: full backup
Goto Forum:

Current Time: Mon Jul 24 03:54:01 CDT 2017

Total time taken to generate the page: 0.13944 seconds