Link to home
Start Free TrialLog in
Avatar of Chad Heinz
Chad HeinzFlag for United States of America

asked on

Oracle 8i RBS datafile corruption

I have an old 8.1.7 Oracle database that had disk corruption on a rollback segment datafile due to a bad block on the HP-UX disk.  I have replaced the disk and all the datafiles (except the corrupt rbs datafile - only had part of the file).  I was unable to take the RBS segments offline and drop them before the disk replacement due them being set at "NEEDS RECOVERY" .  I have tried everything and am open to starting over with RBS segments if needed.  I don't care about the small data loss in them.  

I had to comment out the rollback_segment init parameter to get the database to open.

SEGMENT_NAME          OWNER TABLESPACE_NAME        STATUS  

   

------------------------------ ------ ------------------------------ -----------

-----

RBS01             SYS  RBS              NEEDS RECOVERY  

RBS02             SYS  RBS              NEEDS RECOVERY  

RBS03             SYS  RBS              NEEDS RECOVERY  

RBS04             SYS  RBS              NEEDS RECOVERY  

RBS05             SYS  RBS              NEEDS RECOVERY  

RBS06             SYS  RBS              NEEDS RECOVERY  

RBS07             SYS  RBS              NEEDS RECOVERY  

RBS08             SYS  RBS              NEEDS RECOVERY  

RBS09             SYS  RBS              NEEDS RECOVERY  

RBSBIG             SYS  RBS              NEEDS RECOVERY  

SYSTEM             SYS  SYSTEM             ONLINE  

   

SYSTEM_RBS           SYS  SYSTEM             OFFLINE  

****

A few of the things I have tried are:

SVRMGR> alter database datafile '/db01/HMM/rbs_01.dbf' offline drop;

Statement processed.

SVRMGR> alter database open;

Statement processed.



SVRMGR> alter rollback segment rbs01 offline;

ORA-01598: rollback segment 'RBS01' is not online;

SVRMGR> drop rollback segment rbs01;

ORA-01545: rollback segment 'RBS01' specified not available



*****



SVRMGR> alter database create datafile '/db01/HMM/rbs_01.dbf' as '/db01/HMM/rbs_

01.dbf' size 300M reuse;

alter database create datafile '/db01/HMM/rbs_01.dbf' as '/db01/HMM/rbs_01.dbf'

size 300M reuse

*

ORA-01181: file 5 created before last RESETLOGS, cannot recreate

ORA-01110: data file 5: '/db01/HMM/rbs_01.dbf'

SVRMGR>



****

EVEN IF I TRY TO CREATE A NEW TS, I GET THE FOLLOWING:



SVRMGR> CREATE TABLESPACE RBS1

   2> DATAFILE '/db01/HMM/rbs_backup_01.dbf' SIZE 100M

   3> DEFAULT STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 1)

   4> ONLINE;

CREATE TABLESPACE RBS1

*

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 5 cannot be read at this time

ORA-01110: data file 5: '/db01/HMM/rbs_01.dbf'

***



Any thoughts would be much appreciated!!!


Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Welcome to the site!

An 8i database will definitely require some really old brain cells...

I assume you can easily recover to the state you are in now in case things go horribly wrong?

If yes:
Try and follow these steps:
http://technopark02.blogspot.com/2007/01/oracle-lost-redo-log.html

I scanned them and they seem reasonable.

The only step I might disagree with is step 9:
Shutdown the database, remove the parameter _allow_resetlogs_corruption=TRUE from the init.ora file and restart the database instance. If the instance comes up fine, shut it down and take a backup. Otherwise, check the alert messages carefully and act accordingly to fix the things up.


I remember many MANY years ago having to use that parameter to force a database open.  Even though everything will appear fine, you should do a full export and create a new database to import everything back in.  Not that you are supported, it is the safest way to ensure you don't have problems later.
Avatar of Chad Heinz

ASKER

Thanks for the suggestions, but the parameter of allow_resetlogs_corruption  doesn't look to be valid for Oracle 8.1.7 when I try it.

I found something else (below) but doesn't look like corruptedrollback_segment init parameter is an option either.

SQL> SHUTDOWN IMMEDIATE/NORMAL
SQL> STARTUP RESTRICT MOUNT
SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE DROP;
SQL> ALTER DATABASE OPEN
SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
SQL> -- Recreate the rollback tablespace with all its rollback segments.

Remember to bring the rollbacks online after you create them.
Assuming that you can't open your database and you get the following error messages: ORA-604, ORA-376, and ORA-1110.
-- Comment out the ROLLBACK_SEGMENTS parameter and add the following line:
corruptedrollback_segments = ( <rollback1>,...., <rollbackN> )
with exact the same rollbacks.
WARNING: Use this parameter ONLY IN THIS SPECIFIC SCENARIO or as instructed by Oracle Customer Support.

SQL> STARTUP RESTRICT
SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
SQL> Recreate the rollback tablespace with all its rollback segments.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- put back your ROLLBACK_SEGMENTS parameter in the init.ora file.
-- remove CORRUPTEDROLLBACK_SEGMENTS PARAMETER 

>> but the parameter of allow_resetlogs_corruption  doesn't look to be valid for Oracle 8.1.7 when I try it.

It's a hidden parameter, did you include the leading '_'?    It is important:  _allow_resetlogs_corruption
I didn't try that.  I also don't have good backup (bad tape drive and not space to keep copy on server) and am nervous of recreating control files.

Did you see the process I mentioned with a corruptedrollback_segment init parameter.  Ever heard or used it?
And my issue is related to rollback segments and not redo logs as the doc is explaining.
Yep, confused the two.

Possibly open the database with the resetlogs option.  That might clean things up enough to properly drop things.

If your database is open,  export the important schemas, create a new database, and import everything back in.
I figured out my issue with using the leading "-" and the corrupted rollback parameter.  Thanks for that suggestion!
Having re-read this, you tried to create a new tablespace using the same names.

Try creating RBS2 with all new datafiles.  Then change your pfile to point to the new ones.

Then you should be able to manually remove everything associated with RBS1.  Maybe?
I had tried different names.  It wouldn't let me create any datafiles/tablespaces until I removed the corrupt RBS segments.

If you edited your init.ora and removed all the corrupt ones (or everything pointing to RBS1), not sure why it doesn't allow you to create a new tablespace with all new files.

Any progress on exporting the schemas you wish to save?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.