Chad Heinz
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!!!
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!!!
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
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_corruptio n
It's a hidden parameter, did you include the leading '_'? It is important: _allow_resetlogs_corruptio
ASKER
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?
Did you see the process I mentioned with a corruptedrollback_segment init parameter. Ever heard or used it?
ASKER
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.
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.
ASKER
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?
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?
ASKER
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?
Any progress on exporting the schemas you wish to save?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers 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.
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_corruptio
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.