Results 1 to 2 of 2
  1. #1
    FredLiu Guest

    Answered: HELP! All Logical Log 100% Full and system halting...

    Dear all,
    We are testing Informix 7.3X on Linux now and we perhaps meet a "Long Transaction" problem. We have redirect tapes to /dev/null. In this case, somebody execute a big SQL instruction and we see the log status are all 100% and then the Informix is halted.we cannot shutdown the Inofrmix, so we kill the informix server process. The mode always be "Recovery" when we restart it. Here is the onstat -l content...

    • Linux>onstat -l <Enter>
      Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-1 0
      16 0 0 0.00% phybegin physize phypos phyused %used 10003f 500 65 0 0.00

      Logical Logging Buffer bufused bufsize numrecs numpages numwrits
      recs/pages pages/io L-1 10 16 0 0 0 0.0 0.0 Subsystem numrecs Log
      Space used

      Buffer Waiting Buffer ioproc flags L-1 10f972e4 0x1 0

      address number flags uniqid begin size used %used 10b02de4 1 U-BTEST- 313
      100233 250 250 100.00% 10b02e00 2 U-BTEST- 314 10032d 250 250 100.00%
      10b02e1c 3 U-BTEST- 315 100427 250 250 100.00% 10b02e38 4 U-BTEST- 316
      100521 250 250 100.00% 10b02e54 5 U-BTESTL 317 10061b 250 250 100.00%
      10b02e70 6 UTESTC-- 318 100715 250 250 100.00%

  2. Best Answer
    Posted by jsalomon

    "Fred ol' pal,

    you have a couple of alternatives with this. Then I'll tell you how to avoid this in the future.

    If you are willing to use "onarchive" once, there is an off-line archive facility that will may be able to clear the logs. It's called "ondatartr" (OnLine Data Retrieve) and is described in chapter 9 of the "IDS 7.3 Archive and Backup Guide".

    Having done this only once in my entire career (and that was in a training class) I may be shaky about the details but the command should be:
    Code:
    $ ondatartr /logfile /tape=/dev/your-TapeDrive
    This command should be executed with the server off line - no shared memory, no recovery in progress. Also, it might be a good idea to change the LOGTAPE parameter to the name of a real device. I'm not sure if this is necessary but, like chicken soup for a cadaver, it can't hurt.

    You may want to start this with tech support on the line to hold your hand.

    The idea is that once you have archived the logs, they will be freed up.

    On the other hand, since the logs filled up in middle of a transaction, it might just try to roll the logs back, running into the sdame brick wall. You'll never know until you try it.

    Also, when it's over and you are back on-line, you will need to catalog the new tape via the CATALOG option in onarchive.

    OK, if that fails, you may need to call for Informix Advanced Support and give them dial-in access to your system. After making y'all sign and fax confidentiality agreements, they may download to your system a utility called tbone (Pronounced TB-one; the authors were real sensitive about folks pronouncing it T-Bone, as in a type of steak. This will create a new log file for you, from off-line, allowing the transaction to roll back cleanly into the new log space. (I have heard rumors of undocumented oncheck options that have obviated the need for a C compiler, but have never seen these in action.)

    BTW, do NOT try to grab a copy of the C code before the engineer has removed it; invariably, a customer who does this will use it, mess something up and Informix will find out about it when they call tech support. You will then be sued. Do NOT ask the engineer to leave it there; he will be fired if he does so.

    Now how do you avoid getting into this mess in the first place?

    . Read this entire list and discuss it with colleagues before trying to do anything. It's a mouthful, even for me.
    1. BIGGER logs. 6 logs of < 1/2 MB add up to < 3MB of log space. Any moderate-sized load command will fill your logs to 42 seconds. Add logs and make them 10MB each, and start with 10 of these. (My own environment has some systems with 3GB of log space.) Show that system you mean business!
    2. Set the LBU_PRESERVE parameter to 1. This freezez all user-level database activity when there is one log left unfilled. This gives you a chance to kill the offending session and have its transaction rolled back. Rollbacks can continue even when there is only that one log remaining.
    3. Set LTXHWM to something like 40. Thus, in your 100mb of log space (you DID follow step 1 above, didn't you?) if any transaction spans a 40-MB lengh of the total log space, the server will start rolling it back.
    4. Set LTXEHWM to something around 45. This helps prevent multiple rogue transactions from filling the logs.
    5. Define a temp dbspace and remember to set the DBSPACETEMP parameter to name that dbspace. Many queries cause the engine to create temp tables. If it creates them in [the default] rootdbs, it may fill the rootdbs and it all logged. Using a temp dbspace avoids both problems. There are no log records for operations on a temp table in a temp dbspace.
    6. Should you go back to logging to /dev/null: Use the dbload utility to run large loads. You can set it to commit work often anough so that logs will not get filled in a single big transaction.
    7. Go to a few Informix classes, mainly for IDS system administration.

    Good luck, bud. Sorry, Fred. "


  3. #2
    jsalomon Guest
    Fred ol' pal,

    you have a couple of alternatives with this. Then I'll tell you how to avoid this in the future.

    If you are willing to use "onarchive" once, there is an off-line archive facility that will may be able to clear the logs. It's called "ondatartr" (OnLine Data Retrieve) and is described in chapter 9 of the "IDS 7.3 Archive and Backup Guide".

    Having done this only once in my entire career (and that was in a training class) I may be shaky about the details but the command should be:
    Code:
    $ ondatartr /logfile /tape=/dev/your-TapeDrive
    This command should be executed with the server off line - no shared memory, no recovery in progress. Also, it might be a good idea to change the LOGTAPE parameter to the name of a real device. I'm not sure if this is necessary but, like chicken soup for a cadaver, it can't hurt.

    You may want to start this with tech support on the line to hold your hand.

    The idea is that once you have archived the logs, they will be freed up.

    On the other hand, since the logs filled up in middle of a transaction, it might just try to roll the logs back, running into the sdame brick wall. You'll never know until you try it.

    Also, when it's over and you are back on-line, you will need to catalog the new tape via the CATALOG option in onarchive.

    OK, if that fails, you may need to call for Informix Advanced Support and give them dial-in access to your system. After making y'all sign and fax confidentiality agreements, they may download to your system a utility called tbone (Pronounced TB-one; the authors were real sensitive about folks pronouncing it T-Bone, as in a type of steak. This will create a new log file for you, from off-line, allowing the transaction to roll back cleanly into the new log space. (I have heard rumors of undocumented oncheck options that have obviated the need for a C compiler, but have never seen these in action.)

    BTW, do NOT try to grab a copy of the C code before the engineer has removed it; invariably, a customer who does this will use it, mess something up and Informix will find out about it when they call tech support. You will then be sued. Do NOT ask the engineer to leave it there; he will be fired if he does so.

    Now how do you avoid getting into this mess in the first place?

    . Read this entire list and discuss it with colleagues before trying to do anything. It's a mouthful, even for me.
    1. BIGGER logs. 6 logs of < 1/2 MB add up to < 3MB of log space. Any moderate-sized load command will fill your logs to 42 seconds. Add logs and make them 10MB each, and start with 10 of these. (My own environment has some systems with 3GB of log space.) Show that system you mean business!
    2. Set the LBU_PRESERVE parameter to 1. This freezez all user-level database activity when there is one log left unfilled. This gives you a chance to kill the offending session and have its transaction rolled back. Rollbacks can continue even when there is only that one log remaining.
    3. Set LTXHWM to something like 40. Thus, in your 100mb of log space (you DID follow step 1 above, didn't you?) if any transaction spans a 40-MB lengh of the total log space, the server will start rolling it back.
    4. Set LTXEHWM to something around 45. This helps prevent multiple rogue transactions from filling the logs.
    5. Define a temp dbspace and remember to set the DBSPACETEMP parameter to name that dbspace. Many queries cause the engine to create temp tables. If it creates them in [the default] rootdbs, it may fill the rootdbs and it all logged. Using a temp dbspace avoids both problems. There are no log records for operations on a temp table in a temp dbspace.
    6. Should you go back to logging to /dev/null: Use the dbload utility to run large loads. You can set it to commit work often anough so that logs will not get filled in a single big transaction.
    7. Go to a few Informix classes, mainly for IDS system administration.

    Good luck, bud. Sorry, Fred.

Posting Permissions

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