Home » RDBMS Server » Backup & Recovery » ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected (11.2.0.2.0, Fedora 19)
ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #668948] Sun, 25 March 2018 16:27 Go to next message
joe-zxc
Messages: 5
Registered: March 2018
Junior Member
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Linux 3.14.27-100.fc19.x86_64

Ultimately, I want to get the I/O error fixed, but I need to try and understand what I am seeing.

While developing an Apex application, I am getting:
Unable to create the Report page.ORA-01115: IO error reading block from file (block # ) ORA-01110: data file 2: '/u01/app/oracle/oradata/XE/sysaux.dbf' ORA-27072: File I/O error Additional information: 4 Additional information: 105600 Additional information: 12288

So now the first thing I wamt to do is to backup my db. using Export, I get most of it, but it also confirms that there is a problem:

ORA-31693: Table data object "APEX_050100"."WWV_FLOW_LIST_ITEMS" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 2: '/u01/app/oracle/oradata/XE/sysaux.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 105600
Additional information: 12288

This is the problem I need to fix, and the chase begins.
There is no db backup to restore the dbf from, so after a lot of surfing I decide that rman is the correct way to investigate.

RMAN> validate database;

Starting validate at 25-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/u01/app/oracle/oradata/XE/sysaux.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/XE/system.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/XE/undotbs1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/XE/users.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 03/25/2018 14:45:00
ORA-19501: read error on file "/u01/app/oracle/oradata/XE/sysaux.dbf", block number 105600 (block size=8192)
ORA-27072: File I/O error
Additional information: 4
Additional information: 105600
Additional information: 12288

RMAN> list failure;

no failures found that match specification

Don't know why RMAN doesn't see an issue.
The next thing I look at is if i'm out of disk space

$ df /u01
Filesystem                     1K-blocks     Used Available Use% Mounted on
/dev/mapper/vg_locutus-lv_root  51540604 23708076  27291944  47% /

That seems good. Next look at filesystem permissions

$ ll
-rw-r-----. 1 oracle dba    9748480 Mar 25 15:51 control.dbf
-rw-r-----. 1 oracle dba 1468014592 Mar 25 15:50 sysaux.dbf
-rw-r-----. 1 oracle dba  503324672 Mar 25 15:50 system.dbf
-rw-r-----. 1 oracle dba   23076864 Mar 25 14:02 temp.dbf
-rw-r-----. 1 oracle dba  387981312 Mar 25 15:50 undotbs1.dbf
-rw-r-----. 1 oracle dba  104865792 Mar 25 14:44 users.dbf

Again, ok. More surfing. Next I look at the alert log, and find (among others)

Sat Feb 03 23:56:24 2018
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 1 seq# 3 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log
Sat Feb 03 23:57:01 2018
Thread 1 cannot allocate new log, sequence 4
Checkpoint not complete
  Current log# 1 seq# 3 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log
Thread 1 advanced to log sequence 4 (LGWR switch)
  Current log# 2 seq# 4 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_f7f40g89_.log
Sat Feb 03 23:57:19 2018
Thread 1 cannot allocate new log, sequence 5
Checkpoint not complete
  Current log# 2 seq# 4 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_f7f40g89_.log
Thread 1 advanced to log sequence 5 (LGWR switch)
  Current log# 1 seq# 5 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log
Sat Feb 03 23:58:43 2018
Thread 1 cannot allocate new log, sequence 6
Checkpoint not complete
  Current log# 1 seq# 5 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log
Thread 1 cannot allocate new log, sequence 6
Private strand flush not complete
  Current log# 1 seq# 5 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log
Thread 1 advanced to log sequence 6 (LGWR switch)
  Current log# 2 seq# 6 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_f7f4
Sat Feb 03 23:59:35 2018
Thread 1 advanced to log sequence 7 (LGWR switch)
  Current log# 1 seq# 7 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log
Sun Feb 04 00:00:04 2018
Index APEX_050100.WWV_FLOWS_IDX_AUTHENTICATION or some [sub]partitions of the index have been marked unusable

(etc.)

Sat Mar 24 15:02:01 2018
Thread 1 cannot allocate new log, sequence 61
Private strand flush not complete
  Current log# 2 seq# 60 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_f7f40g89_.log
Thread 1 advanced to log sequence 61 (LGWR switch)
  Current log# 1 seq# 61 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log

Sat Mar 24 18:00:09 2018
Thread 1 advanced to log sequence 62 (LGWR switch)
  Current log# 2 seq# 62 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_f7f40g89_.log

Sun Mar 25 03:00:01 2018
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_j001_3051.trc:
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06550: line 1, column 807:
PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared
ORA-06550: line 1, column 807:
PL/SQL: Statement ignored

Sun Mar 25 14:00:27 2018
Thread 1 advanced to log sequence 63 (LGWR switch)
  Current log# 1 seq# 63 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log

Is it possible there is a problem with the log files ?
$ ls -alrt /u01/app/oracle/fast_recovery_area/XE/onlinelog
total 307244
-rw-r-----. 1 oracle dba 52429312 Jan 27 15:56 o1_mf_1_djxkny97_.log
-rw-r-----. 1 oracle dba 52429312 Jan 27 21:10 o1_mf_2_djxknzh5_.log
-rw-r-----. 1 oracle dba 52429312 Feb  3 12:52 o1_mf_1_f6tjd39d_.log
-rw-r-----. 1 oracle dba 52429312 Feb  3 23:35 o1_mf_2_f6tjd4fj_.log
drwxr-x---. 2 oracle dba     4096 Feb  3 23:46 .
drwxr-x---. 4 oracle dba     4096 Mar 24 11:11 ..
-rw-r-----. 1 oracle dba 52429312 Mar 25 14:00 o1_mf_2_f7f40g89_.log
-rw-r-----. 1 oracle dba 52429312 Mar 25 16:06 o1_mf_1_f7f40f11_.log

Now, this is confusing, because there are more log files than oracle has configured.
Are these junk, or does this point to the root cause. Can these logs be used to rebuild my dbf file?

SQL>  SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG


SQL> select * from v$logfile;
GROUP# STATUS  TYPE    MEMBER                                                               
------ ------- ------- ---------------------------------------------------------------------
     2         ONLINE  /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_f7f40g89_.log
     1         ONLINE  /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_f7f40f11_.log

Thanks for your time.
Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #668950 is a reply to message #668948] Sun, 25 March 2018 16:43 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
BTW -I was impressed by the content of your first post here.

Welcome aboard!

>ORA-19501: read error on file "/u01/app/oracle/oradata/XE/sysaux.dbf", block number 105600 (block size=8192)
If I were you, I would determine exactly which object resides at block# 105600 in SYSAUX.DBF file.

The fact that you have no backup & database was not in ARCHIVE mode, reduces the chances of any successful recovery or work around.

Was the DB doing lots of DML today?
Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #668955 is a reply to message #668948] Sun, 25 March 2018 18:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It appears your filesystem is network/nfs mounted/some lv is involved.
If so, I would first double check those mounted options, ulimit etc.
What kind of filesystem is this?

[Updated on: Sun, 25 March 2018 18:13]

Report message to a moderator

Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #668956 is a reply to message #668950] Sun, 25 March 2018 22:01 Go to previous messageGo to next message
joe-zxc
Messages: 5
Registered: March 2018
Junior Member
BlackSwan wrote on Sun, 25 March 2018 16:43
BTW -I was impressed by the content of your first post here.

Welcome aboard!

>ORA-19501: read error on file "/u01/app/oracle/oradata/XE/sysaux.dbf", block number 105600 (block size=8192)
If I were you, I would determine exactly which object resides at block# 105600 in SYSAUX.DBF file.

The fact that you have no backup & database was not in ARCHIVE mode, reduces the chances of any successful recovery or work around.

Was the DB doing lots of DML today?
Thanks !
The posting guidelines are pretty easy to follow, and writing the post helped me organize everything I was doing.
Pretty light DML. basically I was creating an apex app, and just a little data to amke it interesting.

As for the offending object(s), I have two answers.

Answer #1 : When Exporting, the utility complained as follows. Don't know if that's the only affected object or just the first one.
ORA-31693: Table data object "APEX_050100"."WWV_FLOW_LIST_ITEMS" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 2: '/u01/app/oracle/oradata/XE/sysaux.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 105600
Additional information: 12288

Answer #2 is a section from the alert log that lists about 200 indexes :

Index APEX_050100.WWV_FLOWS_IDX_AUTHENTICATION or some [sub]partitions of the index have been marked unusable
Index APEX_050100.WWV_FLOW_APP_GRP_IDX1 or some [sub]partitions of the index have been marked unusable
Index APEX_050100.WWV_FLOW_COMMMENTS_IDX1 or some [sub]partitions of the index have been marked unusable

...


Index APEX_050100.WWV_PURGE_WORKSPACES_IDX2 or some [sub]partitions of the index have been marked unusable
Index APEX_050100.WWV_PURGE_WORKSPACES_IDX3 or some [sub]partitions of the index have been marked unusable
Index APEX_050100.WWV_PURGE_WORKSPACES_IDX4 or some [sub]partitions of the index have been marked unusable
Index APEX_050100.WWV_PURGE_WORKSPACE_RESP_IDX1 or some [sub]partitions of the index have been marked unusable

And just to summarize where I'm at, it's the "orphaned" redo logs i'm trying to understand - is it a red hearing, or a part of the issue.

Joe.
Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #668957 is a reply to message #668955] Sun, 25 March 2018 22:09 Go to previous messageGo to next message
joe-zxc
Messages: 5
Registered: March 2018
Junior Member
Hi,

Hardware is a(the) local sata disk - my local mounts are the same hard drive.


$mount shows
/dev/mapper/vg_locutus-lv_root on / type ext4 (rw,relatime,seclabel,data=ordered)

There aren't any special limits either
[joe@locutus u01]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 91558
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 1024
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #668958 is a reply to message #668957] Sun, 25 March 2018 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
select 
   relative_fno, 
   owner, 
   segment_name, 
   segment_type
from 
   dba_extents
where 
   file_id in (SELECT FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSAUX')
and 
  105600 between block_id and block_id + blocks - 1;
Post results from above
above should return the object containing the bad block.

I want you to also run the "dbv" utility like below.
post results using COPY & PASTE

dbv file='/u01/app/oracle/oradata/XE/sysaux.dbf' FEEDBACK=100

[oracle@vbgeneric ~]$ dbv help=yes

DBVERIFY: Release 12.2.0.1.0 - Production on Sun Mar 25 23:18:28 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)           


Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #668959 is a reply to message #668948] Mon, 26 March 2018 01:14 Go to previous messageGo to next message
John Watson
Messages: 8082
Registered: January 2010
Location: Global Village
Senior Member
To sort out the logfile issue:

You have two logfile groups, each of which as one member. THose are the files reported when you query v$logfile. The other files in the directory are just some old junk that happens to be hanging about, and are nothing to do with this database.
THe messages in your alert log regarding checkpoints are warnings, not errors. THey may indicate a performance problem. To fix it, you need to replace your two tiny logfile groups with a greater number of larger groups, perhaps four groups with 200MB members. However, I wouldn't bother to do this. Instead....

It is not possible to fix your corrupted datafile problem, because you have no backup. However, the APEX tables in SYSaux are not irreplaceable. What I would is a Data Pump export of you APEX workspace schema and an APEX export of your APEX workspace and applications. If that succeeds, create a new database and import. If the APEX workspace/application exports fail, then we can think again.

[Updated on: Mon, 26 March 2018 01:15]

Report message to a moderator

Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #669059 is a reply to message #668958] Fri, 30 March 2018 15:42 Go to previous messageGo to next message
joe-zxc
Messages: 5
Registered: March 2018
Junior Member
Hi, thanks for the response. For the query I got

RELATIVE_FNO OWNER       SEGMENT_NAME        SEGMENT_TYPE 
------------ -----       ------------        ------------
           2 APEX_050100 WWV_FLOW_LIST_ITEMS TABLE
The results of dbv file='/u01/app/oracle/oradata/XE/sysaux.dbf' FEEDBACK=100
DBVERIFY: Release 11.2.0.2.0 - Production on Fri Mar 30 15:28:49 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/XE/sysaux.dbf
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
.....
DBV-00600: Fatal Error - [28] [27085] [0] [0]
Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #669060 is a reply to message #668959] Fri, 30 March 2018 15:51 Go to previous messageGo to next message
joe-zxc
Messages: 5
Registered: March 2018
Junior Member
Thanks for the answers, that's very helpful. I'm already starting down this path, and will post back when there is progress.
Re: ORA-27072: File I/O error for sysaux.dbf, more logfiles than expected [message #669061 is a reply to message #669059] Fri, 30 March 2018 15:57 Go to previous message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
APEX_050100.WWV_FLOW_LIST_ITEMS TABLE is sitting on BAD BLOCK (27085).
This is the first time in 2+ decades doing Oracle that I have DBV report Fatal Error.

Are you actually using APEX in this database?

Does OS messages file report any disk problem?

One possible work around is as follows below.
1) do a clean & orderly SHUTDOWN of Oracle database.
2) take OS copy of sysaux.dbf file
3) delete sysauxfile.dbf
4) copy file made in #2 back to original name
5) run dbv again to see what happens.
Previous Topic: archive logs directory (FRA) accidentally deleted
Next Topic: How To Determine The Start and End SCN of Restore and Recovery
Goto Forum:
  


Current Time: Wed Dec 11 18:56:43 CST 2019