corrupt SYSTEM tablespace file - cannot startup instance

[robwall7]

SunOS 5.6 sun4u sparc SUNW,Ultra-2
ORACLE 8i 8.1.5

One of the system files for the SYSTEM TABLESPACE is corrupt for one of two instances. I get this error on instance startup.

ORA-01110: data file 18: '/u05/oradata/

http://wwwt/system03.dbf'

ORA-01115: IO error reading block from file 18 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1

This is a development database that has been static since a full instance export w/rows the night before.

I have been the default DBA for 5 months and up till now only required to do maintenance and tuning.

What are my options?

 

[rcurva]

The problem here is that your datafile exceeded the max. file size limit which I think for that version of SunOs is 2G. You have set your system tablespace to autoextend and it hit this threshhold. Add more datafiles for the system tablespace.

To check if the file is really corrupted use, DBVERIFY utility for the datafile.



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rcurva]

Try also to transfer the datafile from one filesystem to another.

1. Mount the database.

SVRMGR> STARTUP NOMOUNT
This command will read the control file but will not mount the datafiles.

2. Rename the file inside Oracle.
SVRMGR> ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

3. Open the database.
SVRMGR> ALTER DATABASE OPEN;

If this still fails then proceed below.

If your are quite sure that there are bad blocks on the datafile (System), you only have two options to recover.

1. Restore the file from a backup and perform media recovery.
2. Rebuild the database.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[robwall7]

The system03.dbf file was found to be corupt and the instance shut itself down and will not restart.

I have heard from others that my only option now is to record instance directory file names and sizes, delete current files, CREATE DATABASE, recreated TABLESPACES (in same locations, same size or bigger), import FULL export.

Thanks for you help,
I have started a new thread for CREATE DATABASE question.

Robert
default dba
think twice, commit once

 

[rcurva]

Ah! The second option --> Rebuild database. I was short of explanation on the options that I gave you.

If your are quite sure that there are bad blocks on the datafile (System), you only have two options to recover.

1. Restore the file from a backup and perform media recovery.
-- you do this when your DB is in archivedlog mode, this is very easy. you put the backed-up datafiles, and perform forward recovery.

2. Rebuild the database.
-- if your Db is not in archivedlog mode, your recreate the DB and perform a full import.




Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[asdfa]

your only option is to restore the datafile
from your last backup cold or hot of system
datafiles
and perform complete recovery.

note: if you are running noarchivelog then you dont
have any options left but to restore last full cold
backup of your database.
Hope this helps...


Irving P Ilagan
8i OCP-DBA