ORA-01243: system tablespace file suffered media failure

This is a mission critical Oracle Database 10gR2 server running on RHEL 5 64bit OS.
Size is about 2TB housing some transactions of the biggest mall chain in PH.

We have just recently completed the RMAN / archiving configuration on all the critical DB servers in this client.

Since then this is the most critical error we encountered. Alert log shows:

ORA-01243: system tablespace file suffered media failure
ORA-01114: IO error writing block to file 1 (block # 1674)
ORA-01110: data file 1: ‘/u01/oracle/oradata/xxxedw2/system01.dbf’
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 1674
Additional information: -1
DBW0: terminating instance due to error 1243
Fri May 14 13:31:02 2010
Errors in file /app/oracle/admin/xxxedw2/bdump/xxxedw2_psp0_7522.trc:
ORA-01243: system tablespace file suffered media failure
Fri May 14 13:31:02 2010
Errors in file /app/oracle/admin/xxxedw2/bdump/xxxedw2_pmon_7520.trc:
ORA-01243: system tablespace file suffered media failure
Fri May 14 13:31:02 2010
Errors in file /app/oracle/admin/xxxedw2/bdump/xxxedw2_lgwr_7528.trc:
ORA-01243: system tablespace file suffered media failure
Instance terminated by DBW0, pid = 7526

Clients system administrator reported that he had error logs on his admin console related to NAS fiber link. /u01/ is a partition linked from that storage.

SQL> startup mount;
SQL> alter database recover;

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/oracle/oradata/xxxedw2/system01.dbf’

alter database mount
Fri May 14 14:22:13 2010
Setting recovery target incarnation to 2
Fri May 14 14:22:13 2010
Successful mount of redo thread 1, with mount id 1881695121
Fri May 14 14:22:13 2010
Database mounted in Exclusive Mode
Completed: alter database mount
Fri May 14 14:23:00 2010
alter database recover
Fri May 14 14:23:00 2010
Media Recovery Start
Fri May 14 14:23:01 2010
Errors in file /app/oracle/admin/xxxedw2/udump/xxxedw2_ora_1173.trc:
ORA-01110: data file 34: ‘/u01/oracle/oradata/xxxedw2/temp_promo_08.dbf’
ORA-01114: IO error writing block to file 34 (block # 1)
ORA-27041: unable to open file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 3
Fri May 14 14:23:01 2010
Errors in file /app/oracle/admin/xxxedw2/udump/xxxedw2_ora_1173.trc:
ORA-01110: data file 33: ‘/u01/oracle/oradata/xxxedw2/temp_promo_07.dbf’
ORA-01114: IO error writing block to file 33 (block # 1)
ORA-27041: unable to open file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 3
Fri May 14 14:23:01 2010
Errors in file /app/oracle/admin/xxxedw2/udump/xxxedw2_ora_1173.trc:
ORA-01110: data file 32: ‘/u01/oracle/oradata/xxxedw2/temp_promo_06.dbf’
ORA-01114: IO error writing block to file 32 (block # 1)
ORA-27041: unable to open file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 3
Fri May 14 14:23:01 2010
Errors in file /app/oracle/admin/xxxedw2/udump/xxxedw2_ora_1173.trc:
ORA-01110: data file 31: ‘/u01/oracle/oradata/xxxedw2/CCAS_TBSP01.dbf’
ORA-01114: IO error writing block to file 31 (block # 1)
ORA-27041: unable to open file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 3
ORA-01110: data file 67: ‘/u01/oracle/oradata/xxxedw2/TSS_DATA07_01.dbf’
ORA-01201: file 1 header failed to write correctly
Fri May 14 14:32:10 2010
Errors in file /app/oracle/admin/xxxedw2/udump/xxxedw2_ora_1286.trc:
ORA-01201: file 35 header failed to write correctly
ORA-01110: data file 67: ‘/u01/oracle/oradata/xxxedw2/TSS_DATA07_01.dbf’
ORA-01201: file 1 header failed to write correctly
Fri May 14 14:32:10 2010
Media Recovery failed with error 1110
ORA-283 signalled during: alter database recover…

ALTER DATABASE MOUNT
Fri May 14 14:48:59 2010
Setting recovery target incarnation to 2
Fri May 14 14:48:59 2010
Successful mount of redo thread 1, with mount id 1881712342
Fri May 14 14:48:59 2010
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Fri May 14 14:49:07 2010
alter database open
Fri May 14 14:49:09 2010
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Fri May 14 14:49:09 2010
Started redo scan
Fri May 14 14:49:09 2010
Completed redo scan
277 redo blocks read, 74 data blocks need recovery
Fri May 14 14:49:10 2010
Started redo application at
Thread 1: logseq 122751, block 174628
Fri May 14 14:49:10 2010
Recovery of Online Redo Log: Thread 1 Group 5 Seq 122751 Reading mem 0
Mem# 0: /app/oracle/oradata/xxxedw2/redo05.log
Fri May 14 14:49:10 2010
Completed redo application
Fri May 14 14:49:10 2010
Completed crash recovery at
Thread 1: logseq 122751, block 174905, scn 38938272364
74 data blocks read, 74 data blocks written, 277 redo blocks read

Errors in file /app/oracle/admin/xxxedw2/bdump/xxxedw2_j000_6878.trc:
ORA-12012: error on auto execute of job 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 46
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 980
ORA-06512: at line 1
Fri May 14 15:01:21 2010
Errors in file /app/oracle/admin/xxxedw2/bdump/xxxedw2_j000_6878.trc:
ORA-12012: error on auto execute of job 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 46
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 980
ORA-06512: at line 1
Fri May 14 15:02:21 2010

ALTER DATABASE RECOVER datafile ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’
Fri May 14 16:47:57 2010
Media Recovery Start
parallel recovery started with 7 processes
Fri May 14 16:47:57 2010
Recovery of Online Redo Log: Thread 1 Group 5 Seq 122751 Reading mem 0
Mem# 0: /app/oracle/oradata/xxxedw2/redo05.log
Fri May 14 16:47:57 2010
Media Recovery Complete (xxxedw2)
Completed: ALTER DATABASE RECOVER datafile ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’
Fri May 14 16:49:01 2010
alter database datafile ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’ online
Fri May 14 16:49:01 2010
Starting control autobackup
******************** WARNING ***************************
The errors during Server autobackup are not fatal, as it
is attempted after sucessful completion of the command.
However, it is recomended to take an RMAN control file
backup as soon as possible because the Autobackup failed
with the following error:
ORA-19624: operation failed, retry possible
ORA-19504: failed to create file “/flash_recovery_area/oracle/xxxedw2/ctrfile_autobackups/AUTOBACKUP_ctrlfile_c-1826539610-20100514-03”
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
******************** END OF WARNING *******************
Completed: alter database datafile ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’ online
Fri May 14 16:57:29 2010
Thread 1 advanced to log sequence 122753 (LGWR switch)
Current log# 2 seq# 122753 mem# 0: /app/oracle/oradata/xxxedw2/redo02.log

rman> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/fra/oracle/flash_recovery_area/xxxedw2/ctrfile_autobackups/ctrlfile_%F’;

Errors in file /app/oracle/admin/xxxedw2/bdump/xxxedw2_j000_6878.trc:
ORA-12012: error on auto execute of job 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 46
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 980
ORA-06512: at line 1
Fri May 14 15:01:21 2010
Errors in file /app/oracle/admin/xxxedw2/bdump/xxxedw2_j000_6878.trc:
ORA-12012: error on auto execute of job 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: ‘/u01/oracle/oradata/xxxedw2/sysaux01.dbf’
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 46
ORA-06512: at “SYSMAN.EMD_MAINTENANCE”, line 980
ORA-06512: at line 1
Fri May 14 15:02:21 2010

 

 

———————————————————————————————–

Note:

This issue was fixed after the sysad restored the connectivity of the FC cable.

 

references:

http://newappsdba.blogspot.com/2007/07/ora-00376-file-6-cannot-be-read-at-this.html

Advertisements

About this entry