Unable to take online backup including logs with HP Data Protector - DB2 9.7 fixpack 8

[Former Member]

Dear All,

Infrastructure : -

Database - IBM DB2 LUW 9.7 fixpack 8

OS - HP-UX 11.31

Backup solution - Data protector v7

OEM of tape library i.e. HP asked me to make changes in DB parameters with values as

--> LOGRETAIN to RECOVERY

--> USEREXIT to ON

--> LOGARCHMETH1 to USEREXIT

These parameters are used for activating online backup of Database (DB2 LUW 9.7 fixpack 8).

when backup is initiated from DP, backup happen successfully with option of excluding archive logs where as when we execute backup from DP(Data protector) with option including archive logs. Backup terminates with error.

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

Dear all need your expect help.

Thanks in advance.

Neeraj

[Former Member]

Hello Neeaj

USEREXIT is deprecated in V9.7

The LOGARCHMETH1 is set to VENDOR:<llibrary path>  (for hp data
protector)

Please refer to the online documentation

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r001...

To find the location of your vendor library, it can be located in /usr/ or /opt

Best Regards

Rachel

[former_member188883]

Hi Neeraj,

Do you see log_archive or log_dir being full when such backups are triggered.

Please also attache db2diag.log

Regards,

Deepak Kori

[Former Member]

Hi Deepak,

Thanks for your quicky reply and i have check the directory log_dir and log_archive are having enough space available.

Please find the db2diag.log file.

       Database Configuration for Database

Database configuration release level                    = 0x0d00

Database release level                                  = 0x0d00

Database territory                                      = en_US

Database code page                                      = 1208

Database code set                                       = UTF-8

Database country/region code                            = 1

Database collating sequence                             = IDENTITY_16BIT

Alternate collating sequence              (ALT_COLLATE) =

Number compatibility                                    = OFF

Varchar2 compatibility                                  = OFF

Date compatibility                                      = OFF

Database page size                                      = 16384

Dynamic SQL Query management           (DYN_QUERY_MGMT) = DISABLE

Statement concentrator                      (STMT_CONC) = OFF

Discovery support for this database       (DISCOVER_DB) = ENABLE

Restrict access                                         = NO

Default query optimization class         (DFT_QUERYOPT) = 5

Degree of parallelism                      (DFT_DEGREE) = 1

Continue upon arithmetic exceptions   (DFT_SQLMATHWARN) = NO

Default refresh age                   (DFT_REFRESH_AGE) = 0

Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM

Number of frequent values retained     (NUM_FREQVALUES) = 10

Number of quantiles retained            (NUM_QUANTILES) = 20

Decimal floating point rounding mode  (DECFLT_ROUNDING) = ROUND_HALF_EVEN

Backup pending                                          = NO

All committed transactions have been written to disk    = NO

Rollforward pending                                     = NO

Restore pending                                         = NO

Multi-page file allocation enabled                      = YES

Log retain for recovery status                          = RECOVERY

User exit for logging status                            = YES

Self tuning memory                    (SELF_TUNING_MEM) = ON

Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(3455670)

Database memory threshold               (DB_MEM_THRESH) = 10

Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(20000)

Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(90)

Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(161633)

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(480362)

Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(50000)

Database heap (4KB)                            (DBHEAP) = AUTOMATIC(3102)

Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 2560

Log buffer size (4KB)                        (LOGBUFSZ) = 1024

Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 10000

Buffer pool size (pages)                     (BUFFPAGE) = 10000

SQL statement heap (4KB)                     (STMTHEAP) = AUTOMATIC(8192)

Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(256)

Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40000)

Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(4384)

Interval for checking deadlock (ms)         (DLCHKTIME) = 10000

Lock timeout (sec)                        (LOCKTIMEOUT) = 3600

Changed pages threshold                (CHNGPGS_THRESH) = 20

Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(2)

Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(5)

Index sort flag                             (INDEXSORT) = YES

Sequential detect flag                      (SEQDETECT) = YES

Default prefetch size (pages)         (DFT_PREFETCH_SZ) = AUTOMATIC

Track modified pages                         (TRACKMOD) = YES

Default number of containers                            = 1

Default tablespace extentsize (pages)   (DFT_EXTENT_SZ) = 2

Max number of active applications            (MAXAPPLS) = AUTOMATIC(125)

Average number of active applications       (AVG_APPLS) = AUTOMATIC(3)

Max DB files open per application            (MAXFILOP) = 61440

Log file size (4KB)                         (LOGFILSIZ) = 16380

Number of primary log files                (LOGPRIMARY) = 60

Number of secondary log files               (LOGSECOND) = 0

Changed path to log files                  (NEWLOGPATH) =

Path to log files                                       = /db2/ECP/log_dir/NODE                                                                                        0000/

Overflow log path                     (OVERFLOWLOGPATH) =

Mirror log path                         (MIRRORLOGPATH) =

First active log file                                   = S0000187.LOG

Block log on disk full                (BLK_LOG_DSK_FUL) = YES

Block non logged operations            (BLOCKNONLOGGED) = NO

Percent max primary log space by transaction  (MAX_LOG) = 0

Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count                          (MINCOMMIT) = 1

Percent log file reclaimed before soft chckpt (SOFTMAX) = 300

Log retain for recovery enabled             (LOGRETAIN) = RECOVERY

User exit for logging enabled                (USEREXIT) = ON

HADR database role                                      = STANDARD

HADR local host name                  (HADR_LOCAL_HOST) =

HADR local service name                (HADR_LOCAL_SVC) =

HADR remote host name                (HADR_REMOTE_HOST) =

HADR remote service name              (HADR_REMOTE_SVC) =

HADR instance name of remote server  (HADR_REMOTE_INST) =

HADR timeout value                       (HADR_TIMEOUT) = 120

HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC

HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0

First log archive method                 (LOGARCHMETH1) = USEREXIT

Options for logarchmeth1                  (LOGARCHOPT1) =

Second log archive method                (LOGARCHMETH2) = OFF

Options for logarchmeth2                  (LOGARCHOPT2) =

Failover log archive path                (FAILARCHPATH) =

Number of log archive retries on error   (NUMARCHRETRY) = 5

Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20

Vendor options                              (VENDOROPT) =

Auto restart enabled                      (AUTORESTART) = ON

Index re-creation time and redo index build  (INDEXREC) = SYSTEM (RESTART)

Log pages during index build            (LOGINDEXBUILD) = OFF

Default number of loadrec sessions    (DFT_LOADREC_SES) = 1

Number of database backups to retain   (NUM_DB_BACKUPS) = 12

Recovery history retention (days)     (REC_HIS_RETENTN) = 60

Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF

TSM management class                    (TSM_MGMTCLASS) =

TSM node name                            (TSM_NODENAME) =

TSM owner                                   (TSM_OWNER) =

TSM password                             (TSM_PASSWORD) =

Automatic maintenance                      (AUTO_MAINT) = ON

   Automatic database backup            (AUTO_DB_BACKUP) = OFF

   Automatic table maintenance          (AUTO_TBL_MAINT) = ON

     Automatic runstats                  (AUTO_RUNSTATS) = ON

       Automatic statement statistics  (AUTO_STMT_STATS) = ON

     Automatic statistics profiling    (AUTO_STATS_PROF) = ON

       Automatic profile updates         (AUTO_PROF_UPD) = ON

     Automatic reorganization               (AUTO_REORG) = ON

Auto-Revalidation                          (AUTO_REVAL) = DEFERRED

Currently Committed                        (CUR_COMMIT) = DISABLED

CHAR output with DECIMAL input        (DEC_TO_CHAR_FMT) = NEW

Enable XML Character operations        (ENABLE_XMLCHAR) = YES

WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0

Monitor Collect Settings

Request metrics                       (MON_REQ_METRICS) = BASE

Activity metrics                      (MON_ACT_METRICS) = BASE

Object metrics                        (MON_OBJ_METRICS) = BASE

Unit of work events                      (MON_UOW_DATA) = NONE

Lock timeout events                   (MON_LOCKTIMEOUT) = WITHOUT_HIST

Deadlock events                          (MON_DEADLOCK) = WITHOUT_HIST

Lock wait events                         (MON_LOCKWAIT) = NONE

Lock wait event threshold               (MON_LW_THRESH) = 5000000

Number of package list entries         (MON_PKGLIST_SZ) = 32

Lock event notification level         (MON_LCK_MSG_LVL) = 1

SMTP Server                               (SMTP_SERVER) =

SQL conditional compilation flags         (SQL_CCFLAGS) =

Section actuals setting               (SECTION_ACTUALS) = NONE

Connect procedure                        (CONNECT_PROC) =

Regards

Neeraj

db2diag.log_ECP.txt.zip

[Former Member]

Hi Neeraj,

As per the db2diag.log:

MESSAGE : ADM8010E  Backup was unable to copy requested log file "S0000143.LOG"

If log file's being corrupted or log file chain is the problem,

then I think we can reset the log file chain, by switching the logging

method from Archival to circular and back to Archival

logging by changing the logarchmeth1/logretain parameter twice. But you are

going to take an Offline backup in the process,

when you switch back from Circular to Archival logging mode.

For example, if your database is in Archival logging mode, then turn it into

circular logging mode, by disabling the logarchmeth1 path or by updating

logretain to OFF.

And then update the logretain back to ON or update logarchmeth1, take an

full offline database backup.

From this point onwards, I think the log file chain should have changed and

you should be able to take an online backup.

Hope this workaround will work for you.

Thanks

Ajitabh

[Former Member]

Hi Ajitabh,

Thanks for suggesting me workaround, but was unsuccessful.

I switch off LOGRETAIN, USEREXIT and LOGARCHMETH1 to status OFF and then initiated offline backup and then reset the parameter USEREXIT to status ON and then initiated offline backup again.

and after doing all the activities, i executed online backup command

db2 backup db MIP ONLINE to /<location> COMPRESS INCLUDE LOGS

and backup was successful where i check db2diag.log file which says.

2014-03-03-14.23.31.050062+330 E75087323A374      LEVEL: Info

PID     : 7577                 TID  : 15          PROC : db2sysc 0

INSTANCE: db2mip               NODE : 000

EDUID   : 15                   EDUNAME: db2logmgr (MIP) 0

FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3108

DATA #1 : <preformatted>

Started archive for log file S0000000.LOG.

2014-03-03-14.23.31.443451+330 E75087698A512      LEVEL: Error

PID     : 7577                 TID  : 15          PROC : db2sysc 0

INSTANCE: db2mip               NODE : 000

EDUID   : 15                   EDUNAME: db2logmgr (MIP) 0

FUNCTION: DB2 UDB, data protection services, sqlpgUserexitLogAdminMsg, probe:1150

MESSAGE : ADM1833E  The user exit program returned an error when archiving log

          file "S0000000.LOG" from "/db2/MIP/log_dir/NODE0000/" for database

          "MIP".  The error code was "8".

2014-03-03-14.23.31.455949+330 E75088211A431      LEVEL: Warning

PID     : 7577                 TID  : 15          PROC : db2sysc 0

INSTANCE: db2mip               NODE : 000

EDUID   : 15                   EDUNAME: db2logmgr (MIP) 0

FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3150

MESSAGE : ADM1848W  Failed to archive log file "S0000000.LOG" to "USEREXIT"

          from "/db2/MIP/log_dir/NODE0000/".

2014-03-03-14.23.31.456203+330 E75088643A431      LEVEL: Error

PID     : 7577                 TID  : 15          PROC : db2sysc 0

INSTANCE: db2mip               NODE : 000

EDUID   : 15                   EDUNAME: db2logmgr (MIP) 0

FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3155

DATA #1 : <preformatted>

Failed to archive log file S0000000.LOG to USEREXIT from /db2/MIP/log_dir/NODE0000/ return code 8.

2014-03-03-14.23.31.456294+330 I75089075A426      LEVEL: Warning

PID     : 7577                 TID  : 15          PROC : db2sysc 0

INSTANCE: db2mip               NODE : 000

EDUID   : 15                   EDUNAME: db2logmgr (MIP) 0

FUNCTION: DB2 UDB, data protection services, sqlpgRetryFailedArchive, probe:4780

MESSAGE : Still unable to archive log file 0 due to rc 8 for LOGARCHMETH1 using

          method 4 and target .

Please help me.

Thanks in advance

Neeraj

[Former Member]

Hello Neeraj,

Check permission on archive log directory as RC =8 means system is not able to access file.

Thanks

Ajitabh

[Former Member]

Hi Ajitabh,

Permission for directory log_archive is set to 755

Thanks in advance.

Best Regards,

Neeraj

[Former Member]

Hello Neeraj,

Below is the Workaround for your Backup Issue which you are facing now.

Excluding the log backup from the database backup is the only solution that DB2 offers. Moving the log backup to different media is the only work-around that NB can offer

This condition exists because:

• Some of the logs were previously backed up to media and removed from disk.
• The database backup was then started and is backing up to the same media.
• Once the database is backed up, DB2 wants to back up the logs that are no longer on disk.
• So DB2 starts a restore which needs the media that is already held by the backup.

Hence there are three ways to avoid this kind of problem:

• Don't do an independent backup of the logs; just back them up with the database.
• Exclude the logs from the database backups (since they are backed up independently).
• Backup the logs to different media than the database. Suggestion backing up to DSU as long as it stages or duplicates to different media.

Here is the detailed explanation:

As you are backing up DB2 using the NetBackup DB2 agent. Upon a backup job you are seeing that during the backup a restore request kicks in and takes precedence. This in turn causes your backups to fail as the media for the backups is being used for the restore of a log file.

With the introduction of DB2 version 8.2, you were able to if you would like have a cut of the archive logs in the same backup set. However you would have to include in the backup command the following statement "include logs".

So where in your backup command you have the following:

Command ="db2 BACKUP DATABASE $MY_DB2 $MY_SCHED LOAD $MY_LIB OPEN 4 SESSIONS BUFFER 1024"

What is happening now is, that you are backing up your database and it is by default including a archive log backup to be included in this backup set.

DB2 may decide that another log is required for this backup to complete and hence will restore the log file in order to include it in the backup set.

You have 3 options here:

1. 1. Change the volume pool for the archive logs to be different to the one it is presently in (it’s in the same as the backup one).
2. 2. Backup your logs to a Disk storage unit (DSU).
3. 3. Not use the include logs options (as you were not using it pre 9.5 anyway). To do this you will need to specify the EXCLUDE LOGS parameter in the backup command.

OR Go through the Below SAP Note:

1099320 - DB6: Online Backup failure due to state 'Backup in Progress'

Hope the above workarounds  will solve your purpose.If Helpful Kindly provide points.

Thanks

Ajitabh

[former_member278126]

Hi All ,

We are also trying to configure HPDP with DB2 9.7 . and integeration guide says you need to turn on userexit and keep logarchmeth as userexit. But SAP has given in there guide book that userexit paramater is not supported by SAP anymore. Could you please help if you have used VENDOR API for HPDP or if you have used some other parameter for Logarchmeth1.