Home » RDBMS Server » Backup & Recovery » What is the best restore scenario (Oracle 10g)
What is the best restore scenario [message #466097] Fri, 16 July 2010 19:36 Go to next message
divacvl
Messages: 4
Registered: July 2010
Junior Member
Dear experts,

I am in charge for maintaining an application which utilize Oracle 10g database in background.

Full online backup of DB is scheduled every night 00:45. This is working OK and we have backups from last 7 days available.

Yesterday one user accidentally dropped one important table (with completely static content) at 10AM.

Database is productional only during working hours (9-17) and outside this period can be offline as long as it needs for maintainance (in this case restore) purposes.

What I would like to achieve is to restore dropped table content from backup, but also not to loose changes today from 10-17h (unfortunately there were some huge insertions today after 10).

My idea is to make full offline backup outside office hours, then perform incomplete restore (until 10AM yesterday, while the table was still there), then export that table (with exp tool), then perform restore from offline backup to restore current state and then import dropped table (with imp).

Is this conceptually OK? (I will search myself for the exact RMAN commands later)
Is there some better way to accomplish what I want?
Once again, downtime is not an issue.

Vladan


Re: What is the best restore scenario [message #466102 is a reply to message #466097] Fri, 16 July 2010 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 22777
Registered: January 2009
Senior Member
Can you FLASHBACK table to its state at 10AM?
Re: What is the best restore scenario [message #466117 is a reply to message #466102] Sat, 17 July 2010 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59065
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this conceptually OK? (I will search myself for the exact RMAN commands later)

Yes this is the standard way to get back a table with the precision that you restore the database/table in another place when the production one is still in use.
Of course if there were ON DELETE CASCADE constraints you also have to restore the dependent tables.

Quote:
Is there some better way to accomplish what I want?

It depends on the service you have to guarantee, the server and space you have... For the futur, it may be interesting to activate flashback log (search for it in documentation).

Regards
Michel
Re: What is the best restore scenario [message #467080 is a reply to message #466117] Thu, 22 July 2010 04:09 Go to previous messageGo to next message
trantuananh24hg
Messages: 606
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Michel & BS: I did not see the topic owner said about FlashBack in his database?

Sometime, in my Prod DB, I've got an accident like, however, I choose the way to restore all database in another server, of course, restore point-in-time. But, the other server is Auxiliary DB, so that, I've never wondered about the data in its. Drop, Truncate table, Import/Export Stats, Analyze, Rebuild DB in test case, ... About 2TB using ASM, it take 2 hours to restore/recovery in AuxDB. After Restore, I import the lost table from AuxDB into ProdDB, of course, check any-trigger afford to it. Still now, 5 accidents I solved by using this way. It's the good way?

Thank you!
Re: What is the best restore scenario [message #467114 is a reply to message #467080] Thu, 22 July 2010 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59065
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said in my answer:

Quote:
Yes this is the standard way to get back a table


But also read the warning.

Regards
Michel
Re: What is the best restore scenario [message #467542 is a reply to message #466097] Sun, 25 July 2010 12:13 Go to previous messageGo to next message
prahlad_buddi
Messages: 2
Registered: July 2010
Location: hyderabad
Junior Member
hi if u want the dropped table with out any loss of data to other objects means then u has to use logminer utility which is a grnular level recovery
Re: What is the best restore scenario [message #467543 is a reply to message #467542] Sun, 25 July 2010 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59065
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Please read OraFAQ Forum Guide and don't use IM/SMS speak

2/ You can't recover a dropped table or its data with Log Miner.

Regards
Michel
Re: What is the best restore scenario [message #467550 is a reply to message #467543] Sun, 25 July 2010 17:46 Go to previous message
divacvl
Messages: 4
Registered: July 2010
Junior Member
Hi,

Sorry for not having the time to post you the results.
I performed restore on 18th July with the scenario I initially described, and everything went fine, dropped content now available and after-drop changes preserved.

Unfortunately, it turned out that I could have done it much more elegantly, since RECYCLEBIN parameter was set to TRUE by default.

I wasn't aware of this FLASHBACK feature, and I overlooked BlackSwan entry, since I thought this is some advanced/optional feature, and I didn't have the time at the time to read more about it.
I tested it later with creating some dummy table, dropping it and restoring it with FLASHBACK, and it is working instantaneously like a charm.
I could have spent 4 hours of my life much better.

I also find this link useful discussing similar scenarios:
h**p://arjudba.blogspot.com/2008/05/recover-from-drop-or-delete-or-truncate.html

Problem solved. Thank you all.
Previous Topic: RMAN copy
Next Topic: Hot backup
Goto Forum:
  


Current Time: Mon Sep 15 05:07:28 CDT 2014

Total time taken to generate the page: 0.10647 seconds