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  |
 |
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 #668956 is a reply to message #668950] |
Sun, 25 March 2018 22:01  |
|
joe-zxc
Messages: 5
Registered: March 2018
|
Junior Member |
|
|
BlackSwan wrote on Sun, 25 March 2018 16:43BTW -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 |
|
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 #668959 is a reply to message #668948] |
Mon, 26 March 2018 01:14 |
John Watson
Messages: 8962
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 #669061 is a reply to message #669059] |
Fri, 30 March 2018 15:57 |
 |
BlackSwan
Messages: 26766
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.
|
|
|
|
Goto Forum:
Current Time: Tue Dec 17 22:18:35 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
