cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle error

HutchAdmin
Occasional Advisor

Oracle error

Dear All,

I am facing below problem in HPUX 11i in Superdome server.

The below is oracle trace.......



Node name: bscstest
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: MIS
Redo thread mounted by this instance: 0
Oracle process number: 0
3074

*** SESSION ID:(16.36) 2004-05-20 08:32:58.569
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-01114: IO error writing block to file 202 (block # 138985)
ORA-27072: skgfdisp: I/O error
HP-UX Error: 25: Not a typewriter
Additional information: 138985
ORA-01114: IO error writing block to file 202 (block # 138985)
ORA-27072: skgfdisp: I/O error
HP-UX Error: 25: Not a typewriter
Additional information: 138985



can anybody help on this


Thanks in advance

13 REPLIES
Hein van den Heuvel
Honored Contributor

Re: Oracle error


You have an IO problem in db-file 202.
SELECT file_name from dba_datafiles where file_number = 202;

(from memory. Check column names with 'describe dba_datafiles').

Once you have the file_name try for example a copy or dd to /dev/null to confirm the io problem.

You'll probably need to restore the backups and recover with archive logs.

good luck,
Hein.


T G Manikandan
Honored Contributor

Re: Oracle error

Just check your /var/adm/syslog/syslog.log file.

check whether any of the disks have gone bad.


Revert
HutchAdmin
Occasional Advisor

Re: Oracle error

Hi Hien / mani

1. There is no error in syslog regarding disk faliure.

2. File no 202 does not exists in database.


point which i like to mention here in that we had recreated database after a crash on manday........


Thanks

twang
Honored Contributor

Re: Oracle error

First of all, check the space available on your hard drive!
Then test the "problem disk"!
HutchAdmin
Occasional Advisor

Re: Oracle error

Dear Twang,

Filesystem is 61% used.....
and disk status is fine


Thanks

twang
Honored Contributor

Re: Oracle error

It seems that the database or controlfile don't see the problem datafile, try to do a backup controlfile to trace to verify it:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Joseph Loo
Honored Contributor

Re: Oracle error

hi,

looks like some permission issue looking at Oracle Metalink:

Problem Explanation: ====================

If you created another user and the permissions on the datafiles that you are trying to migrate are 600 you can not perform a data migration. When the permissions are changed to 660 the migration completes successfully.

Solution Description:
=====================

You need to change the permissions on the datafiles to 660.

You must change the permissions on the datafiles you are trying to migrate to 660. Change to the directory where the datafiles are and type the following:

chmod 660 datafile.dbf

Solution Explanation:
=====================

If another user was created and you are trying to migrate a file as the new user you must have read and write permissions to the file.

hope is helps.

regards.
what you do not see does not mean you should not believe
Yogeeraj_1
Honored Contributor

Re: Oracle error

hi,

can you try to do a full database export?

If there is a problem with a table in particular, you may trap it there!

Then if it is a database block corruption or the like, you can still try to move the table to another tablespace (alter table move tablespace ).

hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Oracle error

Hi HutchAdmin,

202 is the file# (file ID). Do the following to see the state,name, etc... of the datafile:

select *
from v$datafile
where file# = 202

And thiso one to see more about the specific block:

select *
from dba_extents
where block_id = 138985
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Oracle error

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?






Each and every day is a good day to learn.

Re: Oracle error

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
Frequent Advisor

Re: Oracle error

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
Honored Contributor

Re: Oracle error


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
/