Backup Options   «Prev  Next»

Lesson 3 Diagnosing Database Corruption Errors
ObjectiveIdentify 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.
SQL> STARTUP OPEN PFILE='C:\orant\database\InitORCL.ora'
ORACLE instance started.
Total System Global Area11710464 bytes
Fixed Size49152 bytes
Variable Size11177984 bytes
Database Buffers409600 bytes
Redo Buffers73728 bytes
Database mounted.
ORA-01157: cannot identify data file 2 - file not found
ORA-01110: data file 2: 'C:\ORANT\DATABASE\USR1ORCL.ORA'
SQL>

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

  1. If the database is closed, mount the database.
  2. Take the damaged or lost datafile offline via the 
    ALTER DATABASE DATAFILE <datafile_name> OFFLINE [IMMEDIATE];
    command.
  3. Open the database via the ALTER DATABASE OPEN; command.
  4. Restore the backup copy of the damaged datafile via the operating system COPY command (on NT platform).
  5. Recover the tablespace or datafiles using archived redo logs.
  6. Bring the recovered tablespace online via the ALTER DATABASE DATAFILE <datafile_name> ONLINE; command.

Missing Database File 1
1) Missing Database File 1
Missing Database File 2
2) Missing Database File 2
Missing Database File 3
3) Missing Database File 3
Missing Database File 4
4) Missing Database File 4
Missing Database File 5
5) Missing Database File 5
Missing Database File 6
6) Missing Database File 6
Missing Database File 7
7) Missing Database File 7
Missing Database File 8
8) Missing Database File 8
Missing Database File 9
9) Missing Database File 9
  1. 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.
  2. STARTUP OPEN PFILE='C:\ORANT\DATABASE\INITORCL.ORA
  3. Please check your entry and try again.
  4. 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.
  5. ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' OFFLINE;
  6. Please check your entry and try again.
  7. The database is now ready for startup. Type |||(S1)ALTER DATABASE OPEN;|||(S0) at the prompt and press Enter.
  8. ALTER DATABASE OPEN;
  9. Please check your entry and try again.
  10. To start to restore the damaged datafile, you have to access the operating system. Type |||(S1)HOST|||(S0) at the prompt and press Enter.
  1. 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.
  2. COPY C:\ORANT\DATABASEX\BACKUPORCL\USR1ORCLE.ORA C:\ORANT\DATABASE\USR1ORCLE.ORA
  3. To return to Server Manager, type EXIT at the prompt and press Enter.
  4. To start the recovery process, type RECOVERY AUTOMATIC DATAFILE 'C\ORANT\DATABASE\USR1ORCL.ORA'; at the prompt and press Enter.
  5. RECOVERY AUTOMATIC DATAFILE 'C\ORANT\DATABASE\USR1ORCL.ORA';
  6. 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. 
  1. ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' ONLINE;
  2. Please check your entry and try again.
  3. 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.

Start Oracle Database with missing datafile

  1. 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 
    STARTUP OPEN PFILE='C:\ORANT\DATABASE\INITORCL.ORA'
    at the prompt and press Enter.
  2. 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 
    ALTER DATABASE DATAFILE
'C:\ORANT\DATABASE\USR1ORCL.ORA' OFFLINE;
    at the prompt and press Enter.
  3. The database is now ready for startup. Type ALTER DATABASE OPEN; at the prompt and press Enter.
  4. To start to restore the damaged datafile, you have to access the operating system. Type HOST at the prompt and press Enter.
  5. To restore the damaged datafile, type COPY C:\ORANT\DATABASEX\BACKUPORCL\USR1ORCLE.ORA C:\ORANT\DATABASE\USR1ORCLE.ORA at the prompt and press Enter.
  6. To return to Server Manager, type EXIT at the prompt and press Enter.
  7. To start the recovery process, type RECOVERY AUTOMATIC DATAFILE 'C\ORANT\DATABASE\USR1ORCL.ORA'; at the prompt and press Enter.
  8. 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.
  9. 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 completes the Simulation.


To view the result lines of the whole process, view the code below. 
SQL > 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'
SQL> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' OFFLINE;
Statement processed.
SQL> ALTER DATABASE OPEN;
Statement processed.
SQL> HOST
Microsoft<R> Windows NT<TM>
<C> Copyright 1985-1996 Microsoft Corp.

C:\orant> COPY C:\ORANT\DATABASEX\BACKUPORCL\USR1ORCLE.ORA C:\ORANT\DATABASE\USR1ORCLE.ORA
C:\ORANT\DATABASEX\BACKUPORCL\USR1ORCLE.ORA
	1 file(s) copied.
C:\orant> EXIT
SQL> RECOVERY AUTOMATIC DATAFILE 'C\ORANT\DATABASE\USR1ORCL.ORA';
Media recovery completed.
SQL> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' ONLINE;
Statement processed.
SQL>

The next lesson explains parallel recovery operations.
SEMrush Software 3 SEMrush Banner 3