Re: HELP! Can I recover from this?

In article <2t2sg0$n65_at_delphinium.cig.mot.com>, tim_at_sol.rtsg.mot.com (Tim Michael) writes:
> Hi.
>
> I have 3 oracle databases running on a Motorola Delta 8000.
> All 3 are very plain in that they use mostly default settings
> and have only one (1) tablespace created at installation time.
> All 3 are of size 10487808 with 2 log files each which are of
> size 512512. All 3 have been running quite happily for 2+ years.
>
> Upon examining the daily export results, I noticed the following
> error:
>
> ***************************************************************
> EXP-00014: error on row 135 of table USERS
> System error message: No such file or directory
> EXP-00008: ORACLE error 1578 encountered
> ORA-01578: ORACLE data block corrupted (file # 1, block # 2152)
> ***************************************************************
>
> Any attempt to query the table USERS fails with the error ORA-01578.
>
> Oracle error 1578 says:
>
> ORA-1578: ORACLE data block corrupted (file #NUM, block #NUM)
> ***************************************************************
> Cause: The given data block was corrupted, probably as a result
> of program errors.
>
> Action: Try to restore the segment containing the given data block.
> This may involve dropping the segment and recreating it. If
> there is a trace file, report the errors recorded in it to
> your Customer Support Representative.
> ***************************************************************
>
> Well, we don't currently have support from oracle, and before I
> go goofing around with segments (which I know very little about),
> I had the machine rebooted (gracefull shutdown of oracle) with
> a file system check performed. Indeed, the fsck found some errors
> that it said were resolved and all 3 database instances were started
> properly.
>
> Ok, I thought, I'll just import the most recent exports that are error
> free. According to my export log, all were indeed error free. Import had
> the following to say:
>
> . . importing table "AAAAAAA" 22 rows imported
> . . importing table "BBBBBBB" 97 rows imported
> . . importing table "CCCCCCC" 11 rows imported
> . . importing table "DDDDDDD"
> IMP-00003: ORACLE error 1547 encountered
> ORA-01547: failed to allocate extent of size 140 in tablespace 'SYSTEM'
> IMP-00028: partial import of previous table rollbacked: 1281 rows rollbacked
> . . importing table "EEEEEEE" 1 rows imported
> . . importing table "FFFFFFF" 1330 rows imported
> . . importing table "GGGGGGG"
> IMP-00003: ORACLE error 1547 encountered
> ORA-01547: failed to allocate extent of size 140 in tablespace 'SYSTEM'
> IMP-00028: partial import of previous table rollbacked: 3690 rows rollbacked
> Import terminated successfully with warnings.
>
>
> Oracle error 1547 says:
>
> ORA-1547: Failed to allocate extent of size *NUM* in tablespace *NAME*
> *******************************************************************
> Cause: The tablespace does not have enough free space to allocate
> the requested extent of *NUM* blocks.
>
> Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one
> or more files to the tablespace indicated, or create the
> object in another tablespace if this happens during a
> CREATE statement.
> *******************************************************************
>
> These errors occur randomly in 2 of the 3 databases when attempting
> to import data. The errors are not consistent with the tables showing
> errors in the export log. The 3rd database imports without errors
> and everything seems to be ok.
>
> There should be plenty of room for these tables as they have all lived
> there in the past. Did the reboot with fsck cause any problems? Is there
> any way to recover from this situation?
>
> Any assistance would be greatly appreciated.
>
> Thanks,
> Tim Michael

I see a couple possibilities:

1. You may have plenty of space, but it may be fragmented. When ORACLE allocates a new extent for an object, it must be contiguous. Export tries to compress the tables into a single extents. So, even though the table may have lived there before, it might not be able to live there now because of the fragmentation.

   You can look at the free space by looking at the DBA_FREE_SPACE view.    You can look at how much space IMP is looking for by extracting the    create statements out to a file using the INDEXFILE command.

        $ imp / file=expdat.dmp indexfile=tmp.sql

   Options are:

	reorganize your tablespace.  Since this is the SYSTEM tablespace,
	this means doing a full export, recreating the database, and doing
	a full import. Make sure you take a good backup before attempting
	this.

	add a new datafile to the SYSTEM tablespace. The new space will 
	be contiguous.

2. If this is a large table and you did not specify COMMIT=Y, your rollback

   segments may be filling up your tablespace.

     Option:

        Specify COMMIT=Y on the IMP command

Comment:

It is generally not recommended that you place everything in the SYSTEM tablespace. However, I have not had experience with working with such a small database, so maybe it is OK. In our environments, the only thing in the SYSTEM tablespace is the data dictionary. Rollback segments, temporary segments, objects owned by SYSTEM, and user objects all go in their own tablespaces.

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285