DBMS: Oracle v.9.2.0.1.0
OS: MS Server 2003 R2 SP2 x86
Problem: Database begin to stop every few minutes. I start to check and found that one and the largest of two datafiles is probably corrupted. Now I have no idea how to repair that datafile.
Firstly, I look into the alert.log and see that^
...
Mon Jul 29 11:02:03 2013
SMON: enabling tx recovery
Mon Jul 29 11:02:03 2013
Database Characterset is CL8MSWIN1251
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Mon Jul 29 11:02:42 2013
KCF: write/open error block=0x3c009f online=1
file=4 F:\ORACLE\ORADATA\ORCL\USERS_1.ORA
error=27069 txt: 'OSD-04026: Invalid parameter passed. (OS 3932319)'
Mon Jul 29 11:02:42 2013
Errors in file c:\oracle\admin\orcl\bdump\orcl_dbw0_3604.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 4 (block # 3932319)
ORA-01110: data file 4: 'F:\ORACLE\ORADATA\ORCL\USERS_1.ORA'
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 3932319)
DBW0: terminating instance due to error 1242
Instance terminated by DBW0, pid = 3604
Dump file c:\oracle\admin\orcl\bdump\alert_orcl.log
...
So, I turned archivelog on and open database but it continue to stop when somebody calls to some DB objects.
Then, I check v$headers:
SQL> select file#, status, recover, fuzzy, tablespace_name, to_char(CHECKPOINT_CHANGE#), name from v$datafile_header;
FILE# STATUS REC FUZ TABLESPACE_NAME TO_CHAR(CHECKPOINT_CHANGE#) NAME
---------- ------- --- --- -------------------- ---------------------------------------- ----------------------------------
1 ONLINE NO YES SYSTEM 9679059694215 F:\ORACLE\ORADATA\ORCL\SYSTEM.ORA
2 ONLINE NO YES UNDO 9679059694215 F:\ORACLE\ORADATA\ORCL\UNDO.ORA
3 ONLINE NO YES USERS 9679059694215 F:\ORACLE\ORADATA\ORCL\USERS.ORA
4 OFFLINE YES YES USERS 9679059697551 F:\ORACLE\ORADATA\ORCL\USERS_1.ORA
For some reason, USERS in USERS_1.ORA is offline and marked as requiring recovery.
I tried to recover datafile, but get some errors:
SQL> recover datafile 'F:\ORACLE\ORADATA\ORCL\USERS_1.ORA';
ORA-00283: recovery session canceled due to errors
ORA-01115: IO error reading block from file 4 (block # 3932319)
ORA-01110: data file 4: 'F:\ORACLE\ORADATA\ORCL\USERS_1.ORA'
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 3932319)
That have looking creepy for me.
I tries to verify datafile:
dbv file=F:\oracle\oradata\orcl\users_1.ora blocksize=16384 logfile=F:\oracle\oradata\orcl\dbvlog.txt
The result of verification was unexpectedly clean:
DBVERIFY: Release 9.2.0.1.0 - Production on Tue Jul 30 05:03:26 2013
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = F:\oracle\oradata\orcl\users_1.ora
DBVERIFY - Verification complete
Total Pages Examined : 3932320
Total Pages Processed (Data) : 94051
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 19378
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3753059
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 65832
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Now I have that offlined tablespace in the probably not corrupted datafile and no idea how to get DB into the normal state.
Upd: I did database validation by RMAN:
RMAN> BACKUP VALIDATE DATABASE;
Starting backup at 30-JUL-13
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00004 name=F:\ORACLE\ORADATA\ORCL\USERS_1.ORA
input datafile fno=00002 name=F:\ORACLE\ORADATA\ORCL\UNDO.ORA
input datafile fno=00003 name=F:\ORACLE\ORADATA\ORCL\USERS.ORA
input datafile fno=00001 name=F:\ORACLE\ORADATA\ORCL\SYSTEM.ORA
channel ORA_DISK_1: backup set complete, elapsed time: 00:33:06
Finished backup at 30-JUL-13
That would been check my DB and put information of corrupted blocks to a V$DATABASE_BLOCK_CORRUPTION, but no! There's nothing:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
Nevertheless, database doesn't open, until I switch bad datafile to offline^
SQL> alter database datafile 'F:\ORACLE\ORADATA\ORCL\USERS_1.ORA' online;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'F:\ORACLE\ORADATA\ORCL\USERS_1.ORA'
Message was edited by: Llywelyn.yv