Identify how to start Database with missing Datafiles
Diagnosing Database Corruption Errors
When a media failure brings down the database, you usually have no idea what has happened to the database files until you open the database. For example, if the system fails to start up, you'll receive a complaint about missing datafiles.
The highlighted lines in the code show a corrupted datafile.
SVRMGR> STARTUP OPEN PFILE='C:\orant\database\InitORCL.ora'
ORACLE instance started.
Total System Global Area
11710464 bytes
Fixed Size
49152 bytes
Variable Size
11177984 bytes
Database Buffers
409600 bytes
Redo Buffers
73728 bytes
Database mounted.
ORA-01157: cannot identify data file 2 - file not found
ORA-01110: data file 2: 'C:\ORANT\DATABASE\USR1ORCL.ORA'
SVRMGR>
Lost or Damaged Datafile
Even if there is more than one lost or damaged datafile, you will only receive a message about the first datafile encountered each time you try to open the database. Once you receive the message about the damaged datafile, you can go to your well-documented database records to find out which tablespace this damaged datafile belongs to. If the damaged datafile only affects the application tablespace, then, to minimize downtime, you can start the database without this tablespace.
The datafile name can give a hint of the name of the tablespace you are looking for if you named your datafiles carefully.
The following simulation walks you through the steps of starting a database with missing datafiles.
You may also preview all of the steps.
Steps to start Database with missing Datafiles
If the database is closed, mount the database.
Take the damaged or lost datafile offline via the
ALTER DATABASE DATAFILE <datafile_name> OFFLINE [IMMEDIATE];
command.
Open the database via the ALTER DATABASE OPEN; command.
Restore the backup copy of the damaged datafile via the operating system COPY command (on NT platform).
Recover the tablespace or datafiles using archived redo logs.
Bring the recovered tablespace online via the ALTER DATABASE DATAFILE <datafile_name> ONLINE; command.
Suppose a media failure brings down the database and you try to start up the system after the media failure is corrected. To open the database, type |||(S1)STARTUP OPEN PFILE='C:\ORANT\DATABASE\INITORCL.ORA'|||(S0) at the prompt and press Enter.
STARTUP OPEN PFILE='C:\ORANT\DATABASE\INITORCL.ORA
Please check your entry and try again.
You receive the message that a datafile is missing. From the name of the datafile, you know it belongs to the USER_DATA tablespace. Since the system is in the mount mode, you have to take the damaged datafile offline. Type |||(S1)ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' OFFLINE;|||(S0) at the prompt and press Enter.
ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' OFFLINE;
Please check your entry and try again.
The database is now ready for startup. Type |||(S1)ALTER DATABASE OPEN;|||(S0) at the prompt and press Enter.
ALTER DATABASE OPEN;
Please check your entry and try again.
To start to restore the damaged datafile, you have to access the operating system. Type |||(S1)HOST|||(S0) at the prompt and press Enter.
To restore the damaged datafile, type |||(S1)COPY C:\ORANT\DATABASEX\BACKUPORCL\USR1ORCLE.ORA C:\ORANT\DATABASE\USR1ORCLE.ORA|||(S0) at the prompt and press Enter.
The recovered datafile is ready to be put back online. Type
ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' ONLINE;
at the prompt and press Enter.
ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' ONLINE;
Please check your entry and try again.
You have successfully completed three steps. You started the database with a missing datafile, recovered the damaged datafile, and you brought it back online. This is the end of the Simulation. Click the Exit button.