Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Oct 2016
    Posts
    18

    Unanswered: DB2 Tablespace stuck in Rollforward in Progress

    Good Morning All,
    We had an event occur last night that caused our DB servers to go down, they lost power completely. All the DB2 databases came back up except one...kind of. Our UNIX Admin brought the Server back up, mounted the data LUNs and started the database. It started and I could connect to the DB, but I couldn't query a table without receiving an error that "Tablespace Access Is Not Allowed".

    I tried running the following command, "rollforward db <dbname> to end of logs and complete tablespace (<tablespacename>) online", but it returned an error which I later found in the DB2DIAG.log as "Can Not Access Container" and gave the container location it was looking for. When I ran a DF -K, I could see that LUN had not been mounted. Our UNIX Admin mounted after that, didnt stop the DB or anything, and I tried running the same command again.

    Now I get a different error at the command prompt and just that one tablespace is now stuck in "rollforward in progress". I can;t make heads or tails on what the problem is now.

    Command Prompt Error: SQL1263N The archive file "<logname>" is not a valid log file for database "<dbname>" on node "0".
    DB2DIAG Error: "Invalid Log Ext file" DIA8422C An invalid log file was encountered.

    Any hints on what I should try next? I was wondering if it created logs when it started up without the LUN and now that its there, it can't roll forward because the new logs were created and conflict with the logs that are required to roll forward...any truth to that or am I way off base. I must warn, I'm not a DB2 admin...just got thrown into it as we're in between admins for that platform.

    Any help is greatly appreciated!

    DB2 LUW 9.5 or 9.7 running on AIX

  2. #2
    Join Date
    Aug 2016
    Posts
    20
    Provided Answers: 1
    What is the state of tablespaces,

    db2 list tablespaces show detail ,

    when the container was missing, mostly the tablespaces should be offline.

    Just mount the missing container filesystem and

    Alter tablespace tbspname switch online

    this shld working

  3. #3
    Join Date
    Oct 2016
    Posts
    18

    DB2 Tablespace stuck in Rollforward in Progress

    Quote Originally Posted by mpaul View Post
    What is the state of tablespaces,

    db2 list tablespaces show detail ,

    when the container was missing, mostly the tablespaces should be offline.

    Just mount the missing container filesystem and

    Alter tablespace tbspname switch online

    this shld working
    Tablespace detail shows all tablespaces online with a 0x0000 state; however, the problematic tablespace in question shows "Roll Forward In Progress" with a state of 0x0040.

    I tried running the alter tablespace command you recommended, but no change in status...still In Progress.

    If I try to run "rollforward db <dbname> to end of logs and complete tablespace (<tablespacename>) online", I receive the following error at the command prompt.

    "SQL1263N The archive file "S0133717.LOG" is not a valid log file for database "UHG_MAIN" on node "0"."

    I looked for the file being referenced, but dont see it anywhere with the rest of the backup logs or normal log directory so not sure where it is coming from, or if it is possibly complaining because it needs it and can't find it. Below is what the db2diag.log shows when I try to issue the command above that yields the error.

    2016-10-18-12.42.58.374060-300 E1188901856A511 LEVEL: Info
    PID : 5242884 TID : 71451 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000 DB : UHG_MAIN
    APPHDL : 0-4540 APPID: *LOCAL.db2harmi.161018174258
    AUTHID : DB2HARMI
    EDUID : 71451 EDUNAME: db2agent (UHG_MAIN)
    FUNCTION: DB2 UDB, data protection services, sqlufrol, probe:1820
    MESSAGE : ADM1604I DB2 is invoking the forward phase of the table space
    rollforward recovery.

    2016-10-18-12.42.58.453724-300 I1188902368A365 LEVEL: Warning
    PID : 5242884 TID : 3086 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000
    EDUID : 3086 EDUNAME: db2logmgr (UHG_MAIN)
    FUNCTION: DB2 UDB, data protection services, sqlpgRetrieveLogFile, probe:4130
    MESSAGE : Started retrieve for log file S0133717.LOG.

    2016-10-18-12.42.58.547521-300 I1188902734A406 LEVEL: Warning
    PID : 5242884 TID : 3086 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000
    EDUID : 3086 EDUNAME: db2logmgr (UHG_MAIN)
    FUNCTION: DB2 UDB, data protection services, sqlpgRetrieveLogFile, probe:4148
    MESSAGE : Completed retrieve for log file S0133717.LOG on chain 0 to
    /db2/mo_logs/.

    2016-10-18-12.42.58.547775-300 I1188903141A555 LEVEL: Error
    PID : 5242884 TID : 71451 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000 DB : UHG_MAIN
    APPHDL : 0-4540 APPID: *LOCAL.db2harmi.161018174258
    AUTHID : DB2HARMI
    EDUID : 71451 EDUNAME: db2agent (UHG_MAIN)
    FUNCTION: DB2 UDB, recovery manager, sqlpInitTblRecovery, probe:18
    RETCODE : ZRC=0x86100015=-2045771755=SQLPR_INVALID_LOG_EXT
    "Invalid Log Ext file"
    DIA8422C An invalid log file was encountered.

  4. #4
    Join Date
    Aug 2016
    Posts
    20
    Provided Answers: 1
    ok,

    Currently the state is rollforward in progress,

    so when the DB came up after the power went down, what was the state of tablespace (when the container was missing) ? .

    regds
    paul

  5. #5
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mpaul View Post
    ok,

    Currently the state is rollforward in progress,

    so when the DB came up after the power went down, what was the state of tablespace (when the container was missing) ? .

    regds
    paul

    It was in a Rollforward Pending state, which is why I ran the rollforward command. It stayed that way, even after running the command, until the LUN was mounted, at which time it went into the In Progress state.

  6. #6
    Join Date
    Aug 2016
    Posts
    20
    Provided Answers: 1
    Yes, I believe it stuck looking for that log,

    my bad I though it was in offline state, when it came up it do crash recovery , and put this tablespaces in rollforward pending coz it cldnt find the container

    regds
    paul

  7. #7
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Hello,

    Retrieve the following logs manually to some directory:
    S0133716.LOG
    S0133717.LOG
    S0133718.LOG

    and check them:
    db2cklog 133716 to 133718
    What's the result?
    Regards,
    Mark.

  8. #8
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mark.bb View Post
    Hello,

    Retrieve the following logs manually to some directory:
    S0133716.LOG
    S0133717.LOG
    S0133718.LOG

    and check them:
    db2cklog 133716 to 133718
    What's the result?

    Well, that's the tough part...I can't locate the S0133717.LOG and am waiting on it to be restored from Backup Tape, but we're having issues with the library currently as a lingering effect from last night's event. In my log directory, the log name starts with 133722...in my backup directory, it starts with 133718 through 133722.

    I ran on the 133718 file and it completed as clean...Return Code 0

    ================================================== ======
    "db2cklog": Processing log file header of "S0133718.LOG".


    "db2cklog": Processing log pages of "S0133718.LOG" (total log pages: "22288").
    ==> page "1" ...
    ==> page "10001" ...
    ==> page "20001" ...


    "db2cklog": Finished processing log file "S0133718.LOG". Return code: "0".
    ================================================== ======

  9. #9
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    It complained about S0133717.LOG.
    This means that either this log is corrupted, or it isn't compatible with the previous or the next one.
    You must check it since this log is needed for your tablespace rollforward.
    Regards,
    Mark.

  10. #10
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mark.bb View Post
    It complained about S0133717.LOG.
    This means that either this log is corrupted, or it isn't compatible with the previous or the next one.
    You must check it since this log is needed for your tablespace rollforward.
    I can't find where that file is coming from...I searched the BackUps directory where others are stored and also ran a FIND command on the box and it wasn't anywhere to be found...Our backups move these files after a while. Is it possible it is complaining because it can't find the file to interrogate? My plan was to restore the file today and hopefully it passes the check and I can load it and rollforward.

  11. #11
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    2016-10-18-12.42.58.547521-300 I1188902734A406 LEVEL: Warning
    PID : 5242884 TID : 3086 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000
    EDUID : 3086 EDUNAME: db2logmgr (UHG_MAIN)
    FUNCTION: DB2 UDB, data protection services, sqlpgRetrieveLogFile, probe:4148
    MESSAGE : Completed retrieve for log file S0133717.LOG on chain 0 to
    /db2/mo_logs/.

    2016-10-18-12.42.58.547775-300 I1188903141A555 LEVEL: Error
    PID : 5242884 TID : 71451 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000 DB : UHG_MAIN
    APPHDL : 0-4540 APPID: *LOCAL.db2harmi.161018174258
    AUTHID : DB2HARMI
    EDUID : 71451 EDUNAME: db2agent (UHG_MAIN)
    FUNCTION: DB2 UDB, recovery manager, sqlpInitTblRecovery, probe:18
    RETCODE : ZRC=0x86100015=-2045771755=SQLPR_INVALID_LOG_EXT
    "Invalid Log Ext file"
    DIA8422C An invalid log file was encountered.


    This means that DB2 was able to restore this log file (probably from some storage manager like TSM) successfully as a part of the rollforward process.
    But it was not able to use this file since it was incompatible with other logs.

    So, I'm afraid that you should be ready for bad news...
    Regards,
    Mark.

  12. #12
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mark.bb View Post
    2016-10-18-12.42.58.547521-300 I1188902734A406 LEVEL: Warning
    PID : 5242884 TID : 3086 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000
    EDUID : 3086 EDUNAME: db2logmgr (UHG_MAIN)
    FUNCTION: DB2 UDB, data protection services, sqlpgRetrieveLogFile, probe:4148
    MESSAGE : Completed retrieve for log file S0133717.LOG on chain 0 to
    /db2/mo_logs/.

    2016-10-18-12.42.58.547775-300 I1188903141A555 LEVEL: Error
    PID : 5242884 TID : 71451 PROC : db2sysc
    INSTANCE: db2harmi NODE : 000 DB : UHG_MAIN
    APPHDL : 0-4540 APPID: *LOCAL.db2harmi.161018174258
    AUTHID : DB2HARMI
    EDUID : 71451 EDUNAME: db2agent (UHG_MAIN)
    FUNCTION: DB2 UDB, recovery manager, sqlpInitTblRecovery, probe:18
    RETCODE : ZRC=0x86100015=-2045771755=SQLPR_INVALID_LOG_EXT
    "Invalid Log Ext file"
    DIA8422C An invalid log file was encountered.


    This means that DB2 was able to restore this log file (probably from some storage manager like TSM) successfully as a part of the rollforward process.
    But it was not able to use this file since it was incompatible with other logs.

    So, I'm afraid that you should be ready for bad news...

    I really wish I could find it...i'm not familiar with the Storgae Manager other than the log mounts it said it moved it to, which its not there. So, in short I should prepare to restore from the last full backup and roll the logs forward? Never done that before on DB2, so am nervous.

  13. #13
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by ChrisBarnhart12 View Post
    I really wish I could find it...i'm not familiar with the Storgae Manager other than the log mounts it said it moved it to, which its not there. So, in short I should prepare to restore from the last full backup and roll the logs forward? Never done that before on DB2, so am nervous.
    Any suggestions or pointers you may have on the steps and order in which to perform them for the restore and rollforward would be greatly appreciated. I'm going to look at doucmentation, but real-life experience is always helpful

  14. #14
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    What's there result of the following command?
    db2harmi> db2 get db cfg for UHG_MAIN | grep -i logarchmeth
    Regards,
    Mark.

  15. #15
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mark.bb View Post
    What's there result of the following command?
    db2harmi> db2 get db cfg for UHG_MAIN | grep -i logarchmeth
    Funny you ask, I was just figuring out where to see where the setting for logarchmeth1 could be found and ran db cfg show detail. I think the answer is 'USerExit' Below are the pertinent settings:


    First log archive method (LOGARCHMETH1) = USEREXIT USEREXIT
    Options for logarchmeth1 (LOGARCHOPT1) =
    Second log archive method (LOGARCHMETH2) = OFF OFF
    Options for logarchmeth2 (LOGARCHOPT2) =
    Failover log archive path (FAILARCHPATH) =
    Number of log archive retries on error (NUMARCHRETRY) = 5 5
    Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 20
    Vendor options (VENDOROPT) =
    Auto restart enabled (AUTORESTART) = ON ON
    Index re-creation time and redo index build (INDEXREC) = SYSTEM SYSTEM (RESTART)
    Log pages during index build (LOGINDEXBUILD) = OFF OFF
    Default number of loadrec sessions (DFT_LOADREC_SES) = 1 1
    Number of database backups to retain (NUM_DB_BACKUPS) = 12 12
    Recovery history retention (days) (REC_HIS_RETENTN) = 366 366
    Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF OFF

    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •