Lesson 3 | Diagnosing Database Corruption Errors |
Objective | 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.
- COPY C:\ORANT\DATABASEX\BACKUPORCL\USR1ORCLE.ORA C:\ORANT\DATABASE\USR1ORCLE.ORA
- To return to Server Manager, type EXIT at the prompt and press Enter.
- To start the recovery process, type RECOVERY AUTOMATIC DATAFILE 'C\ORANT\DATABASE\USR1ORCL.ORA'; at the prompt and press Enter.
- RECOVERY AUTOMATIC DATAFILE 'C\ORANT\DATABASE\USR1ORCL.ORA';
- 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.
Start Database Missing Data file
To view the result lines of the whole process, View the code below.
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> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' OFFLINE;
Statement processed.
SVRMGR> ALTER DATABASE OPEN;
Statement processed.
SVRMGR> 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
SVRMGR> RECOVERY AUTOMATIC DATAFILE 'C\ORANT\DATABASE\USR1ORCL.ORA';
Media recovery completed.
SVRMGR> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\USR1ORCL.ORA' ONLINE;
Statement processed.
SVRMGR>
The next lesson explains parallel recovery operations.