Unanswered: INCLUDE LOGS or EXCLUDE LOGS - which is better ?
DB2V9.5.5 on Win2k3 EE x64
We replicate the backup image overnight and restore it to Mirror (reporting )server before the day actally begins.
Backup image is full online backup image, size 250GB on average without logs.
Daily logs are 23 logs of size 260,008 KB each.
Online backup takes 2.5 hrs, replication 2 hrs and restore takes 2 hours including RF.
This way our Mirror is ready by 7:00am roughly.
My questions is - should we include logs in backup image or copy seperately ??
I am copying it seperately, there was a reason why I did that in past.
Now DB2 includes logs in the image by default. That makes me think again.
The advantage that I am having is that in some cases, because of some uncommited or ongoing updates let's say logs turn out to be 65 logs instead of 23. In such situations we gain time in multitasking. I mean we replicate the backup image first and start RESTORE on mirror. Another script is called in parallel to bring the logs over and completes logs copy in about 20 mins.
This is working for past 2 years, never had any problem. The potential problem may trouble the process is that some logs may get out of order. I mean sequence may change or may be different for few in the list. My script copies all LOGS from log_start to log_finish counting numerically last 4 digits of log's filename.
Second question - Can this happen that squentially copied logs may have some missing ?? because of seqence number.
When you use INCLUDE LOGS during a backup, DB2 will but the minimum log files necessary to be able to roll forward the restore to get a working database.
We do a similar process in our shop, but we use the INCLUDE LOGS clause and only ship the image, then restore the image, extracting the logs at the same time. Then the rollforward command is issued to get a working database. I like this method for two reasons. First, I do not need to determine which logs are needed, DB2 does that for me. Second, the backup image gives me everything I need to get a working database restored. I do not have to worry about the logs.
I used to do that, then found that in few cases when logs are too many, DB2 took too long to zip logs into backup image. Let me say, if images total becoms 14GB then, even then it should be ok, but in our setup it (DB2) takes longer to pack, and longer to replicate, and then again some time to extract.
This issue went to IBM support team which could not see anything odd duiring their two week study, meanwhile I tried EXCLUDE LOGS and that worked equally in varying situations. I remember other day I had 119 logs, and it worked fine, although little more time to finish. the images were replicated in parallel.
The issue may be some setup (network,san etc) but EXCLUDE LOGS worked better for me. Also worth stating that I use INCLUDE LOGS on all other DBs.
I agree with you that INCLUDE LOGS is still better option.
Do you think that copying logs manually may fail because of DB2 may change sequence num before the backup finished ?
Copying the logs manually means that you need to get some of them from the archive and from the active logs. I do not think that you need to worry about out of sequence logs. What you need to worry about is getting all of the correct logs. As you already know, you are transmitting all of them whether they are actually needed or not to prevent this issue from happening.
Copying the logs manually means that you need to get some of them from the archive and from the active logs.
As you already know, you are transmitting all of them whether they are actually needed or not to prevent this issue from happening.
1. I did not have to, so far, look for any LOG that is still in ACTIVE LOGS folder.That is mainly beacuse of this :-
2. I start copying from first log reported by DB2, continue till last log reported by DB2.
So I am not transmitting all logs. Plus my script is capable of comparing create/modify time stamp for a chance that I may want rollforward to some PIT after backup.
How long are you going to keep your backup images? How long are you going to keep your log-files? Ever been in the situation where you retrieve an old backupimage from tape and then the quest for logfiles starts? That's why I recommend INCLUDE!
An online backup-image without the logs is useless, you do not want be the one to tell the boss " nice safe with all those tapes, but..eh..well.."
We Tape Backup image/logfiles everyday from our Mirror. Where there are external logs these get automatically backed up. When not, we assume that logs are included.
So while retrieving the image and/or logs, these always go along with image if any seperate.
Of course, I do include logs with each backup, still as I mentioned previously, there is at least one database which we have to use logs externally.
How long we keep backup images- the daily ones for a month and weekly ones for 3 months and monthly ones for a year - I do not manage this, threre are IMS team.