| 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
|
|
|
|
|
|
| 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.
|
|
|
|
| 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.
|
|
|
|
| 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?
|
|
|
|
|
|
| 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.
|
|
|
|
|
|
|
|
|
|
| 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.
|
|
|
|
| Re: Truncate and a complete recovery? [message #262066 is a reply to message #262064] |
Fri, 24 August 2007 07:39 |
 |
Michel Cadot
Messages: 68731
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
