banjora {na+au+uk{auxilium}}

Like so many others who continue to support Oracle8i databases for required legacy systems, I was engaged on a impaired database that required the recovery of 9 datafiles. I have performed this type of recovery in the past with the odd datafile, but none to this extent of involvement.

It is noteworthy to mention that several sins exists with this legacy system, specifically, using a de-supported Oracle8i database on an non-certified Windows operating system. Regardless, real life solutions and equations seldom show mercy.

ERROR:
The Oracle8i database reside on Windows 2003 clustered architecture, and when the Event Logs were examined, no related events were recorded. However, the only reported errors was that of the database’s Alertlog.

KCF: write/open error block=0x2f4b4 online=1
file=9 S:\ORADATA\CIM4644\DATA\CIM_DATA02.DBF
error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service.'
Automatic datafile offline due to write error on
file 9: S:\ORADATA\CIM4644\DATA\CIM_DATA02.DBF

APPLIES TO:

• Oracle8i Enterprise Edition 8.1.7.4.1
• Oracle FailSafe 3.3.3
• Windows 2003 Enterprise Edition, SP3

SOLUTION:
To address this issue rather quickly, I connected onto the database as the SYSDBA and performed a simple ‘recover database’ statement.

c:\> sqlplus /nolog

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2098952220 bytes
Fixed Size                    75804 bytes
Variable Size             421076992 bytes
Database Buffers         1677721600 bytes
Redo Buffers                  77824 bytes
Database mounted.

SQL> recover database;
Media recovery complete.

SQL> alter database open;

Database altered.
SQL>

Yet, I still receive an error when the application is launched and begins to access it’s related tables objects.

Errors in file r:\oradata\CIM4644\bdump\cim4644SMON.TRC:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 2: 'S:\ORADATA\CIM4644\DATA\CIM_DATA07.DBF'

Performing a simple recovery command did not extend down to the datafile recovery, so additional steps are required. To identify what datafiles required additional recovery, I execute a simple PLSQL command to show me these remaining datafiles that are in need of recovery.

SQL> select file_name from dba_data_files df, v$recover_file vf where df.file_id = vf.file# order by 1;
SQL>

For each datafile reported, I simply execute a recovery command that is datafile specific.

SQL> recover datafile 'S:\ORADATA\CIM4644\DATA\CIM_DATA07.DBF';

ORA-00279: change 37719885 generated at 11/06/2010 03:04:19 needed for thread 1
ORA-00289: suggestion : U:\ORADATA\CIM4644\ARCHIVE\CIM4644T001S6760.ARC
ORA-00280: change 37719885 for thread 1 is in sequence #6760

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL>

Once the datafile is successfully recovered, I am then able to bring the datafile online.

SQL> alter database datafile 'S:\ORADATA\CIM4644\DATA\CIM_DATA07.DBF' online;

Database altered.
SQL>

With all the questionable datafiles recovered successfully and brought online, I then perform an export of the application’s schema to ensure no data corruption.

c:\> exp <account>/<password>@<tnsnames> file=my-test-export_201011a.dmp log=t_my-test-export_201011a.log owner=<schema name> grants=yes rows=yes indexes=yes

I have found that a quick data export of a schema provides an excellent means of quickly
determining data corruption.