"Backup .. include logs" requires archived logs ?

vilius · #1 (**permalink**) 05-20-09, 03:52

Hello,

Yesterday I tried to make online backup using "include logs" option and got this:

Code:

SQL2428N  The BACKUP did not complete because one or more of the requested log files could not be retrieved.

Our systems archiving:
1. Userexit archives logs from log_dir to log_archive.
2. Cron job moves logs from log_archive elswhere.
(so log retrieval using userexit is not possible)

I always thought that "include logs" option adds active log files(at the moment backup is started) to backup image. Here is some log entries from db2diag.log (backup start time 20:31):

Code:

2009-05-19-20.00.51.651769+180 I6482618A360       LEVEL: Warning
PID     : 282694               TID  : 1           PROC : db2logmgr (DBP) 0
INSTANCE: db2dbp               NODE : 000
FUNCTION: DB2 UDB, data protection, sqlpgArchiveLogFile, probe:3170
MESSAGE : Completed archive for log file S0063621.LOG to USEREXIT from
          /db2/DBP/log_dir/NODE0000/.
----------------------------------------------------------------------------------------------------
2009-05-19-20.55.37.675820+180 E6493117A491       LEVEL: Error
PID     : 266446               TID  : 1           PROC : db2bm.598144.4 0
INSTANCE: db2dbp               NODE : 000         DB   : DBP
INSTANCE: db2dbp               NODE : 000
FUNCTION: DB2 UDB, data protection, sqlpgRetrieveLogFile, probe:4165
MESSAGE : ADM1847W  Failed retrieve for log file "S0063621.LOG" on chain "0"
          from "/db2/DBP/log_dir/NODE0000/".
----------------------------------------------------------------------------------------------------
2009-05-19-20.55.37.675820+180 E6493117A491       LEVEL: Error
PID     : 266446               TID  : 1           PROC : db2bm.598144.4 0
INSTANCE: db2dbp               NODE : 000         DB   : DBP
APPHDL  : 0-808                APPID: *LOCAL.db2dbp.090519173110
FUNCTION: DB2 UDB, database utilities, sqlubProcessLogExtent, probe:1917
MESSAGE : ADM8010E  Backup was unable to copy requested log file "S0063621.LOG"
          for inclusion in the backup image. The backup has been aborted.

So S0063621.LOG was archived 20:00, then 20:31 backup was started, then 20:55 db2 tried to retrieve S0063621.LOG(which do not exist - see above ) and failed.

Do db2 backup with "include logs" option realy needs logs which are archived even before backup is started ?
If yes why ?

thanks
Vilius

przytula_guy · #2 (**permalink**) 05-20-09, 04:30

when the backup is started, the log file name is retained
while running the backup log files can get closed and archived
at end of backup logs need to be included in image and log file has been archived; at that time we retrieve a log from archive location. if log file has been renamed in mean time by other procedure, the log file is not available anymore and backup will fail

vilius · #3 (**permalink**) 05-20-09, 04:52

Hi,

Code:

while running the backup log files can get closed and archived

Yes I understand that - but my example shows that db2 tried to retrieve log file which was archived half an hour before backup started ?

V

dr_te_z · #4 (**permalink**) 05-20-09, 05:10

Quote:

Originally Posted by vilius

Our systems archiving:
1. Userexit archives logs from log_dir to log_archive.
2. Cron job moves logs from log_archive elswhere.
(so log retrieval using userexit is not possible)

No delay in the cron procedure? You got guts!
My experions:
on v9.5 the "include logs" work fine, the database can be re-created on another server using the log-files which were included.
Recently we needed te re-create an old database (13 months ago) and that was still v8.2 We did not succeed to restore/rollforeward based upon the included logs. Extra restore of old logs was required.
So it seems that IBM did some work on that area and now (v9.5) it works as designed.
Hope this helps....

przytula_guy · #5 (**permalink**) 05-20-09, 07:12

yes because some luw has started long time ago and is still present
to find the complete luw we need the older log files and the current files, to have it all

vilius · #6 (**permalink**) 05-20-09, 08:06

I've done one mistake I thought that log is archived when it is inactive, but:
Userexit archives logs as soon as they are full, even if they are still active. So S0063621.LOG probably was active at the time backup started. The only explanation is that it became inactive during backup time.

Quote:

yes because some luw has started long time ago and is still present

I don't agree with that - if log contains transaction entries which is not commited, even log is full - it should stay in log_dir.

V

JamesTJr · #7 (**permalink**) 11-03-09, 10:14

I'm using db2 9 on AIX and have the same problem. Is there a way to find the LSN the database is expecting to see what the oldest log file the backup will need to include? Or some other way to find all the log files a backup neededs to include before it starts?

koganti · #8 (**permalink**) 11-03-09, 11:04

Run the command Archive logs before the backup.
Logs will be truncated and then you may not need archived logs

db2girl · #9 (**permalink**) 11-03-09, 11:22

Quote:

Originally Posted by JamesTJr

I'm using db2 9 on AIX and have the same problem. Is there a way to find the LSN the database is expecting to see what the oldest log file the backup will need to include? Or some other way to find all the log files a backup neededs to include before it starts?

db2 will include all logs that were active (some of them may be already archived/reused and some not) from the time backup started and until it ended. You can get the "First active log file" from db cfg.

JamesTJr · #10 (**permalink**) 11-03-09, 11:29

I have tried 'archive log' command before backup and still get the error. Also, the log file identified in the diaglog needed to be retrieved is the one before the 'first active log'.

db2girl · #11 (**permalink**) 11-03-09, 11:34

Do you move the logs from the archived location somewhere else?

koganti · #12 (**permalink**) 11-03-09, 11:40

Looks like you are running into a bug. May want to open a PMR with IBM
Backup command should have the ability to grab any logs that is required when you use "InCLUDE LOGS" option

JamesTJr · #13 (**permalink**) 11-03-09, 11:45

Db backup runs daily at 9.30pm for several hours. Daily at 5am sysadms backup archive log path and clean it out. Log backup is never running while db backup is. There are once in a blue moon times where the db has no update activity and the backup is looking for an archived log that was cleaned-out 16 1/2 hours earlier.

db2dummy1 · #14 (**permalink**) 11-03-09, 13:18

Logs to be included in the backup include those active at the start of the oldest in-flight uncommitted transaction running at the start of backup

db2girl · #15 (**permalink**) 11-03-09, 14:20

Quote:

Originally Posted by JamesTJr

the log file identified in the diaglog needed to be retrieved is the one before the 'first active log'.

This doesn't sound the way DB2 should work - it should not look for any logs prior to the 'first active log'.

Try to disable the manual cleanup of archived logs for a few days and see if the problem reoccurs. If it does, then follow koganti's advice and open a pmr.

Internet Services

Web Development

Digital Marketing

Consumer Tech