topic Re: Oracle error in Operating System - HP-UX

Oracle error

HutchAdmin — Wed, 19 May 2004 23:41:33 GMT

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

Re: Oracle error

Hein van den Heuvel — Thu, 20 May 2004 00:31:00 GMT

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.

Re: Oracle error

T G Manikandan — Thu, 20 May 2004 00:44:59 GMT

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

check whether any of the disks have gone bad.

Revert

Re: Oracle error

HutchAdmin — Thu, 20 May 2004 00:55:33 GMT

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

Re: Oracle error

twang — Thu, 20 May 2004 00:56:05 GMT

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

Re: Oracle error

HutchAdmin — Thu, 20 May 2004 01:04:48 GMT

Dear Twang,

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

Thanks

Re: Oracle error

twang — Thu, 20 May 2004 01:16:37 GMT

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;

Re: Oracle error

Joseph Loo — Thu, 20 May 2004 01:19:09 GMT

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.

Re: Oracle error

Yogeeraj_1 — Thu, 20 May 2004 02:54:35 GMT

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

Re: Oracle error

Eric Antunes — Fri, 21 May 2004 03:19:00 GMT

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

Re: Oracle error

Eric Antunes — Fri, 21 May 2004 03:29:36 GMT

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?

Re: Oracle error

Stephen Wales_1 — Fri, 21 May 2004 10:38:35 GMT

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

Re: Oracle error

Emilio Brusa — Sat, 22 May 2004 06:20:36 GMT

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.

Re: Oracle error

Hein van den Heuvel — Sat, 22 May 2004 13:24:44 GMT

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
/