Oracle error

[Eric Antunes]

As you said you've recovered the database. I supose you done:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "MIS" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 150
MAXINSTANCES 1
MAXLOGHISTORY 680
LOGFILE
GROUP 1 (
'/disc1/oradata/MIS/log01a.dbf',
'/disc2/app/oracle/MIS/log01b.dbf'
) SIZE 10M,
GROUP 2 (
'/disc1/oradata/MIS/log02a.dbf',
'/disc2/app/oracle/MIS/log02b.dbf'
) SIZE 10M
DATAFILE
'/disc1/oradata/MIS/system01.dbf',
'/disc1/oradata/MIS/rbs01.dbf',
'/disc1/oradata/MIS/temp01.dbf',
...
'/disc1/oradata/MIS/system02.dbf'
;

RECOVER DATABASE;

ALTER DATABASE OPEN RESETLOGS;

Have you done all this steps?

[Stephen Wales_1]

From your init.ora, what is your db_files parameter set to? Is it 200?

I also am assuming here that you're on Oracle 9+

I recently had a similar problem trying to find file 201 - in Oracle 8 and below there were only 'datafiles'. Now, in Oracle 9+, they've introduced 'datafiles' and 'tempfiles'. You now have 2 v$ views - v$datafile and v$tempfile.

In my case, file-id 201 indicated a temporary file #1 (db_files + FileID)

Are your temporary tablespaces set to AUTOEXTEND ON? There is a definite bug under Windows where if a Temp tablespace goes to autoextend over a 4 GB boundary (4GB, 8GB, 12 GB etc) you will get this error - but I don't know if it's true under HPUX.

Also, if you have autoextend on and you don't have largefiles defined on your filesystem and the temp file is trying to autoextend over the 2 GB limit, you may also find yourself with this error (although I can't test this one to be sure).

Hope some of those pointers give you something to look at. Have you also searched at Metalink?

Steve

[Emilio Brusa]

HutchAdmin,
Reciently has increased your temporary or created tablespaces temporary ?
If you don't have problem of corruption of data it can be that your or your temporary tablespaces are had big that your real space in the filesystem.

Oracle sometimes allows you to add temporary datafiles from a superior size to that of your filesystem and when it really ends up occupying it it sends you that error class.
when you look for the id doesn't appear since it doesn't exist alone in the moment that oracle needs to use an extent of the temporal.

cheers.
E.

[Hein van den Heuvel]

You may want to UNION the file names and file numbers from the v$xxxxfile tables as per script below.

If you can not open teh database then queries are allowed on fixed tables/views only. So you can query v$datafile, but not dba_data_files;

Hein.

column status format a8
column file format a45
column id format 999
column type format a7

set pages 9999
set heading off
set FEEDBACK off
select 'Redo' "type", l.group# "Id", l.status, l.bytes/(1024*1024) "MB",
MEMBER "File" from v$logfile f, v$log l where l.group# = f.group#
union
select 'Data' "type", FILE# "Id", status, bytes/(1024*1024) "MB",
name "File" from v$datafile
union
select 'Temp' "type", FILE# "Id", status, bytes/(1024*1024) "MB",
name "File" from v$tempfile
union
select 'Control' "type", rownum "Id", status, 0 "MB",
name "File" from v$controlfile
order by 1,2
/