Home » RDBMS Server » Backup & Recovery » Truncate and a complete recovery?
Truncate and a complete recovery? [message #261763] Thu, 23 August 2007 09:25 Go to next message
tobyc
Messages: 9
Registered: March 2007
Junior Member
We have a 9.2.0.8 database (archive log mode) with RMAN backups setup to run nightly. We truncate a few tables every Monday morning.
Say it is Monday afternoon at 2PM the backup ran last night at 10PM the truncate job ran at 8AM. What happens if we need to do a full database restore at 2PM?

The truncate job is not logged so will the data be in the truncated tables? Will we be able to do a complete recovery since we had the truncate between the last backup and the time that we need to restore to.


Thanks,

toby
Re: Truncate and a complete recovery? [message #261772 is a reply to message #261763] Thu, 23 August 2007 09:58 Go to previous messageGo to next message
sroy
Messages: 38
Registered: July 2005
Member
How do you run the truncate job? Through dbms_job? If your answer is yes, break that job first and then complete your full database restore. Then un-break the truncate job.
Re: Truncate and a complete recovery? [message #261773 is a reply to message #261772] Thu, 23 August 2007 10:04 Go to previous messageGo to next message
tobyc
Messages: 9
Registered: March 2007
Junior Member
I'm not sure that I follow what you are saying.

If the truncate job had already ran at 8AM and it is now 2PM can we do a complete restore/recover at 2PM from the level 0 backup that was taken last night at 10PM.... My worry is that we will not be able to recover due to the truncate...

I don't know what the status of the job has to do with the recover. I use a cronjob for the truncate.
Re: Truncate and a complete recovery? [message #261784 is a reply to message #261763] Thu, 23 August 2007 10:37 Go to previous messageGo to next message
sroy
Messages: 38
Registered: July 2005
Member
If the 0 backup was done last night (assuming it was Sunday at 10PM) and the truncate job ran the next day (Monday morning at 8 AM as you said it), the full database recovery will restore everything from the 0 backup including the data (If any) for those tables you just truncated.
Re: Truncate and a complete recovery? [message #261789 is a reply to message #261763] Thu, 23 August 2007 10:54 Go to previous messageGo to next message
tobyc
Messages: 9
Registered: March 2007
Junior Member
Thanks, but I have my doubts.

Backup Sunday 10PM
Truncate Monday 8AM

Need to restore and recover the database to Monday at 2PM. This recovery will require me to restore from the 10PM full backup and then apply the archive logs up until 2PM.

If the files from the truncate operation are there how do the rows that were added between the truncate and 2PM get added? Wouldn't the row numbers have comflicts during the recovery?

Any thoughts on this?

Re: Truncate and a complete recovery? [message #261797 is a reply to message #261789] Thu, 23 August 2007 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your question (or your problem).
Recovery:
1/ restores the database as it was at the time of your backup (10PM)
2/ applies the logs till the time you specified (2AM)

What is the problem? The truncate operation does not exist before 8AM.

Regards
Michel
Re: Truncate and a complete recovery? [message #261799 is a reply to message #261763] Thu, 23 August 2007 11:22 Go to previous messageGo to next message
tobyc
Messages: 9
Registered: March 2007
Junior Member
The restore time is 2PM That is 6 hours after the truncate job ran.

Sunday backup 10PM
Monday truncate job 8AM
ON Monday afternoon 2(PM) we need to restore and recover up until 2PM using full backup and all the archive logs.

Hope that clears things up.
Re: Truncate and a complete recovery? [message #261814 is a reply to message #261799] Thu, 23 August 2007 12:11 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
if you want to recover that table
incomplete recovery until 7:59 AM.
Re: Truncate and a complete recovery? [message #261820 is a reply to message #261799] Thu, 23 August 2007 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, AM/PM are not in used in France and so not easy to handle.
If you want to retrieve your data, then recover the table just before the truncate export the table, then continue the recover till now and reimport the data.

Quote:
The truncate job is not logged

Of course it is logged and replay when 8AM is reached during recovery.

Regards
Michel
Re: Truncate and a complete recovery? [message #261867 is a reply to message #261763] Thu, 23 August 2007 21:03 Go to previous messageGo to next message
sroy
Messages: 38
Registered: July 2005
Member
First restore database from backup, then recover until 2PM. But, the question is do you still want the data restored to the tables that were truncated on Monday 8AM?
Re: Truncate and a complete recovery? [message #262064 is a reply to message #261867] Fri, 24 August 2007 07:35 Go to previous messageGo to next message
tobyc
Messages: 9
Registered: March 2007
Junior Member
Michel cleared it up, thanks.

I do not want the data that was truncated.
My thinking was that since the actual truncate is not logged (that is why it is faster than a delete) that it would not get replayed with the restore.

I'm going to test this out as soon as I have time. If I come up with anything weird I'll post a message.

Thanks to all who helped.

Tobyc.
Re: Truncate and a complete recovery? [message #262066 is a reply to message #262064] Fri, 24 August 2007 07:39 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Truncate is just operations on dictionary (SYS tables) this is why it is fast and this is also why it is logged: operations on SYS tables are always logged.

Regards
Michel
Previous Topic: Datafile OFFLINE...........
Next Topic: Backup And Recovery
Goto Forum:
  


Current Time: Wed Dec 07 12:59:55 CST 2016

Total time taken to generate the page: 0.05992 seconds