Home » RDBMS Server » Backup & Recovery » Loss of Table (Oracle 9i Rel 2)
Loss of Table [message #358369] Mon, 10 November 2008 23:48 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello,

I have been asked like, there is a table which has been accidentally dropped by the developer . And it has no backup also . Now the situation is to recover that lost table without using Tablespace Point in Time Recovery as it'll be affected for the other tables in the Tablespace .

Can we use logminer to recover that table ? or any other mechanism is there for that. Please provide me solution for that .

Kindly help me out .


Regards,
Raj

Report message to a moderator

Re: Loss of Table [message #358380 is a reply to message #358369] Tue, 11 November 2008 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you might use Log Miner, if you have all archived logs since the table was created.

But the right answer is:
0/ The correct way is Tablespace Point in Time Recovery
1/ The developer should be fired as he should not access the production database (if it is not a production database then a dropped table does not have to be recovered)
2/ The DBA should be fired if he have not a backup and is not able to recover the table
3/ The boss should be fired if he had allowed developer privileges on the production database

Regards
Michel

Report message to a moderator

Re: Loss of Table [message #358385 is a reply to message #358380] Tue, 11 November 2008 01:26 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Thanks a ton Mike, wish I can give the same answer to my manager .

I did using logminer but I recovered only at the row level , but I did'nt do it at the table level .

Can you please help me out how to do that how to do at the table level.

--Raj

Report message to a moderator

Re: Loss of Table [message #358391 is a reply to message #358385] Tue, 11 November 2008 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to enable DDL tracking (option sys.dbms_logmnr.ddl_dict_tracking) on Log Miner but if you have no recorded dictionary (in file or in redo log) when the table existed I'm afraid you can't have table and columns names and so you will have to guess which DDL statement is related to your table.

Regards
Michel

Report message to a moderator

Re: Loss of Table [message #358396 is a reply to message #358380] Tue, 11 November 2008 01:52 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Mike, but if we are using TSPITR , we need to make the tablespace offline and for that one dropped table , all the other tables will be affected right . So any other way to overcome this .

Report message to a moderator

Re: Loss of Table [message #358402 is a reply to message #358396] Tue, 11 November 2008 02:26 Go to previous message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, TSPITR is done in an auxiliary database not on the current one.

Regards
Michel

Report message to a moderator

Previous Topic: Removing .flb files
Next Topic: export script
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 02 07:18:43 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.