I was hoping that someone here may be able to fill in a few blanks for me on the subject of online backups and archive logging. Basically the documentation I have found online thus far has allowed me to fumble my way through, but nothing has actually allowed me understand what it going on, which I find very frustrating.
I switch to archive logging thus:
UPDATE DB CFG FOR TEST
USING logarchmeth1 "DISK:/db2archive/db2cq2/test"
logprimary 15 logsecond 10 logfilsiz 1000;
Now, I see that (in the default location) various logs are created over time, and these are referred to as the active logs. Fine and dandy. But eventually these active logs get relegated to archive logs and are moved by DB2 into the location I specified, something like the following: /db2archive/db2cq2/test/db2cq2/TEST/NODE0000/Cxxxxxxx/sxxxxxxx.LOG
I now go to make a full online backup:
BACKUP DATABASE TEST ONLINE
WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 COMPRESS
Now, I'm a bit confused as to exactly what logs are being included in the backup here. Is it all the currently active logs, or just logs of transactions that took place during the online backup, are archive logs factored in at all?
I now have:
- a database, with some active logs.
- a directory of archive logs
- an online backup for a given date
When I issue the following commands:
RESTORE DATABASE TEST
FROM "/db2backup/db2cq2/test" TAKEN AT date
WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
ROLLFORWARD DATABASE TEST
TO END OF LOGS AND COMPLETE;
The database is restored to the state of the backup and then rolled forward all the way back to the state it was in when I initiated the restore command in the first place, with no loss of data.
A few questions:
1) in the restore I specified a LOGTARGET, is this simply a temporary directory to restore the backed up log files to for use during the restore, and I can then safely delete these aftwards? What happens to these restored log files?
2) What happens log-wise when restoring a database to a previous state, are logs deleted, or is further logging created that shows that the database was restored from state x to state y?
3) If I take a weekly full online backup, with INCLUDE LOGS, am I right in saying that to restore the database to the point in time that the backup was taken I need nothing more than that backup file itself? If so, how would I restore the database to that point in time, my attempts to restore an online backup without rolling forward have proved unsuccessful thus far.
3) Can someone give me some context on Archive logs? These are logs no longer needed by DB2 apparently, but they play some part in being able to rollforward?
4) If the backup image includes logs then what role, if any, do the archived logs play when restoring a database?
5) Perhaps it wil be obvious to me if someone answers #3, but should you delete archived logs, and how do you determine which to delete & when?
I appreciate that's a lot of questions. If you've read this through to the end you probably deserve a break now
For you question as to which logs are included in the backup, it will be active logs. For your other questions:
1) You should move the restored log files to the active log directory BEFORE rollforward.
2) I believe that all log files in the active log directory are deleted and logging picks up at the point where rollforward left off.
3) Archive logs are indeed for rollforward. If you do a nightly online backup at, for example, 10 PM. Work on the DB continues normally and there are 5 logs that are archived since the backup. The database crashes at 4:40 PM the next day. To get the DB to the state before the crash, you need to restore the previous night's backup, which is over 18 hours old. You then rollforward to end of logs or 4:39 PM (your choice). This will use the archived logs to process all the transactions that occurred since 10 PM.
4) See number 3.
5) If you are backing up every day, you can delete archived logs that are 2 days old or more.
There is something I just found out that I have to add. DB2 will also include archived logs in the backup image when the INCLUDE LOGS option is specified. I think this is because those logs contain transactions that span passed the end of the archived log an into the active logs. DB2 will retrieve these from the archive and include them in the backup image. This is to insure that the backup image can be used to make a usable DB through the rollforward process.