ORA-01113 On Database Startup
An ORA-01113 error will be issued whenever a datafile is not in sync with the rest of the database.
Apart from the case in which a tablespace is in hot backup mode and a database crash or shutdown abort or machine reboot takes place, this error is returned in the following cases also :
a) We open the database with the old version of a datafile that was restored from backup without bringing it up to date (by recovery).
b) We try to bring an offline datafile online.
A very important point to note is that Oracle requires the entire database (control files,redo log files, data files) to be in sync when it is opened. The checkpoint SCN must be the same for all the datafiles.
When we take a datafile offline its checkpoint SCN will freeze. If we attempt to bring the file online without first recovering it, its SCN is likely to be older than that of the other online datafiles and hence we get the ORA-01113 error.
To simulate this scenario we take a datafile offline and shutdown abort the database;
No media failure has taken place and no backup has been restored still we get the ORA -01113 error !
SVRMGR> select name , status from v$datafile;
NAME STATUS
--------------------------------------------------------------------------------
… ….
/sp105db04/ORACLE/rman/users01.dbf
RECOVER
/sp105db04/ORACLE/rman/users02.dbf
ONLINE
SVRMGR> alter database datafile '/sp105db04/ORACLE/rman/users01.dbf' online;
alter database datafile '/sp105db04/ORACLE/rman/users01.dbf' online
*
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/sp105db04/ORACLE/rman/users01.dbf'
SVRMGR> recover datafile '/sp105db04/ORACLE/rman/users01.dbf';
Media recovery complete.
SVRMGR> select name,status from v$datafile where name like '%users%';
NAME STATUS
--------------------------------------------------------------------------------
……….
/sp105db04/ORACLE/rman/users01.dbf
OFFLINE
/sp105db04/ORACLE/rman/users02.dbf
ONLINE
2 rows selected.
SVRMGR> alter database datafile '/sp105db04/ORACLE/rman/users01.dbf' online;
Statement processed.