Hi,

I have a DB2 9.7 Database and I try to size the file system I need in order to have sufficient place in my db, logs, mirrored logs and archive logs file system.

The active logs are in a file system DB/logs
The mirror logs are in a file system DB/mirror/logs

I have configured the logarchmeth1 parameter of the database to:
DISK:/ArchiveLogs/ then my archive logs are in this file system

I have a backup plan as the following :
My backups are saved in a /backup/ file system.


I am trying to size all my file system :

db/logs/
db/mirror/logs/
/ArchiveLogs/
/backup/

I don't understand how does the logs works.
Then, I have some questions about that

I've seen the active logs are monitored in the CFG command :
1 - if I'm right, this means that I can have only 10 files of 40MB in my db/log file system.
2 - What are the "secondary log files" then ?

3 - I have seen when I do a FULL OFFLINE BACKUP, every usefull active logs are moved in the Archive logs path. Is that Right ?
4 - Does the FULL OFFLINE BACKUP save the active logs ?
5 - Does the FULL OFFLINE BACKUP save the archive logs ?

6 - When I do an INCREMENTAL OR DELTA BACKUP, a part of the "active" active logs referenced in the line below is saved in a log file with the same name (N°60 here) but none the same size . Then the line below is incremented (here goes to 61) an other log of 40MB is created in order to have 10 files of active logs (here N°70 : 61 to 69 already created and 70 is created now).

Then, in my active log file system, I got 10 files of 40MB AND the new file created by the INCREMENTAL or DELTA backup.

How does I know what is the max size of this file system ?

So, what must I save in order to be able to come back at every moment in case of disaster
Every backup and logs?
Only backup and active logs ?
Only backup ?
FULL OFFLINE Backup, other backup and some logs from the last full offline backup ?

Thank you for your help.

Alex

Data changes to tables in DB2 are usually applied in memory only and written to disk at a later point in time. Since memory is not very reliable in case of a power outage, DB2 logs all those changes additionally (and guarantees that those logs are written to disk when a transaction is committed). On the side, logs are also used to roll back transactions. So logs are very important (and they should be written to a fast device.)

In order to apply incremental/delta backups, you need a full backup before. Additionally, you'll need the logs to recover to a point between backups. Thus, you'll need at least:
(0) use archive logging (not circular)
(1) at least one full backup
(2) incremental/delta backups since (1)
(3) logs since (2)
If you want to recover before the full backup, you need more before that. I would probably keep more stuff around.

The maximum size you'll need depends on the amount of activity (whether lots of log records need to be written or not) and the size of the database.

Regarding the number of log files: you'll have active 10 primary log files (which are created when the database is started) and there may be up to 12 additional log files (which are created when needed). Active log files are those that contain log records of transactions that are not yet committed/rolled back. There will be older log files, which contain log records from already finished transactions.

If you have a full backup, you have a (consistent) snapshot of the database/tablespace. No logs before the backup are needed. (All the logged changes are in the backup image already.)

Hi, thank you for your reply !

However, I need some small explanations
I've read that I need a full OFFLINE backup in order to be able to do ONLINE backup then.
I've also read that for the online full backup, I need to keep the logs (because some other actions are done during the backup routine by users)

this (1) full backup is an offline or online backup ?
Do I need a full offline backup in order to recover a full online backup ?

When is this "When needed" ? Because if an active log is full, then it is moved to the archive log location. I don't see when more logs can be needed.
This means I have at maximum 10 files of 40MB (primary logs) and 12 files of 40Mb (secondary logs) in my active logs file system ?

Then, If I want to keep the relevant active log, I need to keep every active logs or only those with the date of modification after the (2) incremental/delta backup ?

This is an offline backup right ?

Thank you for your help,

Alex

I'm not sure about that. What does the manual say? (I can't be bothered to look it up right now myself.) If it states to do an offline backup first, you'll have to do it. If the manual does not say so, you don't need to.

That's right. But you can use the INCLUDE LOGS option on the command: BACKUP DATABASE

Doesn't matter. Why should it? A full backup is a full backup and contains everything in the database.

A log file is only archived if it doesn't contain stuff from a transaction that is not yet committed/rolled back. Such a log file is active. Log files that don't contain stuff from running transactions anymore are not active and can be archived. Now, if I have a long running transaction that did some INSERTs that were logged in the first log file, that file cannot be archived. Let's assume that some more transactions (or even the same one) cause log files 2..10 to be used. Even more operations requiring to be logged give us the "when needed" situation: another log file is needed and then a secondary log file is created.

See above. From the perspective of recovery, you don't care whether a log file is active or not. What matters is when the log file was created. If the log file was active after the backup, it is needed because it may contain committed transactions. If the log file was archived after the backup, it is also needed for the same reason. The transactions in the archived log file are not in the backup image, of course.


Doesn't matter. Offline/online are concepts orthogonal to full vs. incremental/delta backups. They just describe whether operations were going on in the database while the backup was taken or not. Full vs. incremental/delta describes the content of the backup (everything or just changes since a certain previous event).


I suggest that you read up a but on logging, backups and recovery in the DB2 manuals. Those concepts are described there pretty well as far as I recall.

I've read a lot of manuals, I did understand we must keep the log files in order to do a rollforward operation for the "point-in-time" backup.
But I didn't understand if the active/archive logs were included in the backup files (after some test with the include logs tag, I've got the same size of backup, this is strange) or which active/archive log were usefull to save.

That why I didn't success to size my file systems, I didn't know what file were needed.

Those replies were very helpfull, thank you for your time.

Logs that are included in a backup file are only those logs that were created or updated during the online backup. It does not inlcude any log files created in-between online backups.

Marcus is right. But if you take a backup (online or offline), the logs written before the backup are not needed because the corresponding data changes are included in the backup image itself. (The logs are still needed in case you want to roll back to a point in time that is before the backup.)

I think that logs and backups are pretty simple:

1. when a backup is taken, you get a snapshot of the database (assuming the complete database is backed up and not just a single tablespace or so)
2. if the backup was incremental/delta, you need a full backup from some point before as a base line because the backup only contains changes since the last backup (for delta since any previous backup, for incremental since the last full backup)
3. logs fill the gap between backups
4. end of story.

It's ok thank you

I understood very well.

Finally a do a the db2 backup as mentionned in my first post.
I do a backup of all the db2 backup in an other file system
I do a backup of all the logs (archive and active)
I delete every backup or log after twenty-two days retention.

With this process I can rollback at every point in time for three week

Then I've sized
my backup file system,
my log and mirror file system
my archive log file system
my global backup

thank you,

Alex

May be there's no need to copy active logs; any one of them can be written to at any time until they become archived, so your copy may be outdated. Also, the OS may not let you copy files that are open.

If you set LOGARCHMETH1 and ..2 to the correct destinations, logs will be copied there automatically as they are archived.

Tell DB2 to clean up those files by means of a "db2 prune" command. Less risk.