OraFAQ Forum: Backup & Recovery » Truncate and a complete recovery?

Home » RDBMS Server » Backup & Recovery » Truncate and a complete recovery?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Truncate and a complete recovery? [message #261763]

Thu, 23 August 2007 09:25

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

Report message to a moderator

Re: Truncate and a complete recovery? [message #261772 is a reply to message #261763]

Thu, 23 August 2007 09:58

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.

Report message to a moderator

Re: Truncate and a complete recovery? [message #261773 is a reply to message #261772]

Thu, 23 August 2007 10:04

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.

Report message to a moderator

Re: Truncate and a complete recovery? [message #261784 is a reply to message #261763]

Thu, 23 August 2007 10:37

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.

Report message to a moderator

Re: Truncate and a complete recovery? [message #261789 is a reply to message #261763]

Thu, 23 August 2007 10:54

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?

Report message to a moderator

Re: Truncate and a complete recovery? [message #261797 is a reply to message #261789]

Thu, 23 August 2007 11:18

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Truncate and a complete recovery? [message #261799 is a reply to message #261763]

Thu, 23 August 2007 11:22

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.

Report message to a moderator

Re: Truncate and a complete recovery? [message #261814 is a reply to message #261799]

Thu, 23 August 2007 12:11

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

if you want to recover that table
incomplete recovery until 7:59 AM.

Report message to a moderator

Re: Truncate and a complete recovery? [message #261820 is a reply to message #261799]

Thu, 23 August 2007 12:46

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Truncate and a complete recovery? [message #261867 is a reply to message #261763]

Thu, 23 August 2007 21:03

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?

Report message to a moderator

Re: Truncate and a complete recovery? [message #262064 is a reply to message #261867]

Fri, 24 August 2007 07:35

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.

Report message to a moderator

Re: Truncate and a complete recovery? [message #262066 is a reply to message #262064]

Fri, 24 August 2007 07:39

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Datafile OFFLINE...........
Next Topic:	Backup And Recovery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 15:51:06 CDT 2025