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 previous 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.
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
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: Thu Jan 23 21:08:46 CST 2020