-
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!
-
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]
-
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!
-
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;
-
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!
-
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)
-
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
-
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]
-
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...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|