Interesting problem, anybody have it before?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

Thread: Interesting problem, anybody have it before?

  1. #1
    Join Date
    Apr 2001
    Posts
    108
    Had an interesting problem recently where a rollback segment datafile became corrupted. For this instance I'm running v8.1.7 on an NT4.0 server, this instance is used for DSS.

    We were performing a load and the server locked up. When we rebooted the server and restarted the database, the database recovery could not finish. This was due to the corrupted rollback segment. Now we couldn't take the rollback segment offline as it had active transactions to rollback, and the rollback could not complete as the datafile was corrupt.

    Due to time constraints I could not save this off to another server to play with, I recovered the database with the previous nights cold backup and the data load continued. While I wish I could have kept this around to experiment with I did not have the time or a spare server large enough for this. Has anybody else out there had a similar problem? And if so how else did you resolve it? Thanks.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    I had this problem.
    What u have to do:

    - startup database (with incomplete restore)
    - take this RBS offline MANUALY

    start sqlplus

    > CONNECT sys/change_on_install@UR_DB
    > UPDATE UNDO$
    > SET STATUS$ = 2
    > WHERE NAME='UR_BAD_RBS';

    SELECT NAME, STATUS$ FROM UNDO$;

    check status RBS:
    1 - online in INIT.ORA
    2 - offline
    3 - ? (online)
    ...
    COMMIT;
    drop rollback segment UR_BAD_RBS;





    [Edited by Shestakov on 05-29-2002 at 10:19 AM]

  3. #3
    Join Date
    Apr 2001
    Posts
    108
    Shestakov,

    Thanks for the reply.

    How do you take the rbs offline manually? I tried to offline it via sql*plus but it would not go offline. Do you just comment the rbs out of the init.ora file before startup? Thanks.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by justadba


    How do you take the rbs offline manually?

    This is procedure, step by step, : How u can take the RBS offline manually.

    start sqlplus

    > CONNECT sys/change_on_install@UR_DB
    > UPDATE UNDO$
    > SET STATUS$ = 2
    > WHERE NAME='UR_BAD_RBS';

    SELECT NAME, STATUS$ FROM UNDO$;

    check status RBS:
    1 - online in INIT.ORA
    2 - offline
    3 - ? (online)
    ...
    COMMIT;
    drop rollback segment UR_BAD_RBS;

  5. #5
    Join Date
    Apr 2001
    Posts
    108

    Ahhh, I understand.

    Thanks. I understand now. But for my occurance, won't that still cause a problem? The rbs would not go offline as there were still active transactions that needed to be rolled back for instance recovery on startup. If I were to do that wouldn't the database be out of sync with invalid data?

    Again thanks for the explanation.
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    If u have corrupted block in active RBS u can't restore this transaction from rbs in any way.

    U just have to make desicion:
    -- u need this (UNCOMMITED) transaction or not.
    (because, if this transaction is uncommits than in table's datablocks all OK)
    data didn't white to disk)
    (if this transaction had been commited than in log u have all indormation about it ,
    and u can restore it from online log file, not from rbs)

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Shestakov
    -- u need this (UNCOMMITED) transaction or not.
    (because, if this transaction is uncommits than in table's datablocks all OK)
    data didn't white to disk)
    that is not true... you have uncomitted data in the data blocks all the time

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by pando
    Originally posted by Shestakov
    -- u need this (UNCOMMITED) transaction or not.
    (because, if this transaction is uncommits than in table's datablocks all OK)
    data didn't white to disk)
    that is not true... you have uncomitted data in the data blocks all the time
    This isn't absolutly true. We have uncomitted data in the data blocks IN DB CACHE all the time.
    Not in tables on disk.
    In first, and any way, Oracle write data to log file and than ONLY in tablespace,
    INCLUDING RBS DATABLOCKS if it need.

    ------------------------------------------------
    In addition:
    U can check # of rollback segment in db cache using formula:

    Block class in x$bh : field CLASS
    CLASS = #RBS Header = 7 + (2*n) --> where n --> # of RBS
    CLASS = #RBS Block = 7 + (2*n) + 1 --> where n --> # of RBS

    [Edited by Shestakov on 05-29-2002 at 03:43 PM]

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    we do have uncomitted data in datafiles otherwise we wouldnt have to rollforward in instance recovery

    otehrwise what happens if a transaction is not comitted but it has to be flushed out from data block buffer due to cache shortage? that data disappears...? no...

  10. #10
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Oracle will write DATABLOCKS (not RBS) only when its blocks
    had been fixed in log file.
    Oracle may write RBS BLOCKS during transaction, but always AFTER this block had been fixed in log file.
    DBRW can (but not must) write dirty blocks to disk only if a server process
    can't find clean reusable buffers. But In this case LGWR MUST write dirty blocks to log BEFORE.
    Current log is a basis information source for recovery process.

    --------------------------------------------
    U can check this:

    1. choose db block with "well known" rowid.
    2. start transaction.
    3. update any row in this block
    4. using rowid (#file, #block) get ADDRESS this block in x$bh
    DUMP this block from db cache
    5. before commit
    DUMP this block from tablespace
    and compare blocks (they will be different)
    6. commit transaction
    7. DUMP this block from tablespace again
    and compare blocks again (very possible than they will be equal)




    [Edited by Shestakov on 05-29-2002 at 04:27 PM]

Posting Permissions

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


Click Here to Expand Forum to Full Width