ORA-01113 On Database Startup
Note : Database is running in ARCHIVELOG mode
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, shutdown abort, simulate a media failure by removing some or all the datafiles and then restoring the same from a backup.
Steps :
svrmgr> STARTUP MOUNT
svrmgr> SELECT * FROM V$DATAFILE;
Check the STATUS column and see if any of the datafiles is 'OFFLINE' or 'RECOVER'.
svrmgr> ALTER DATABASE DATAFILE '/ekdw3/ORACLE/beta/user01.dbf' ONLINE;
svrmgr> RECOVER DATABASE;
Aply archived redo logs.
svrmgr>SELECT * FROM V$DATAFILE;
Check to see whether the status of the particular file has changed to 'ONLINE'.
svrmgr> ALTER DATABASE OPEN;
Check whether tablespace is offline;
svrmgr> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;
If Not ONLINE then ...
svrmgr> ALTER TABLESPACE USERS ONLINE;