Media failure in Oracle 10g

Learning objective

After completing this topic, you should be able to identify the types of media failure that can cause an instance to fail.

1. Opening a database

As you start a database, it moves from a SHUTDOWN state through NOMOUNT and MOUNT states, to the final OPEN state.

At each of these stages, the database performs internal consistency checks to make sure that the startup can proceed.

The instance checks different files at each stage of the startup.

• NOMOUNT
• MOUNT
• OPEN

NOMOUNT

The first stage in opening the database is the NOMOUNT stage. The instance is said to have STARTED status.

At this stage, none of the datafiles or control files are available, but the initialization parameter file has been read.

MOUNT

After the initialization files have been read, the database can be mounted.

In the MOUNT stage the database checks to see that the control files listed in the initialization file are present in the database.

If any of these files are missing or corrupt, an error is recorded, and the database must stay in the NOMOUNT state.

OPEN

As the database moves into the OPEN state, it checks for

• redo log group member availability
• datafile availability
• datafile synchronization

The control files list groups of redo logs. There must be at least one member of each of these groups present in the database. If any members are missing, this is noted in the alert log.

All of the datafiles listed in the control files must be present unless they are offline. The database can be opened with offline datafiles, as long as those files don't belong to the SYSTEM or UNDO tablespaces.

If any datafiles are missing, an error is returned, and the database stays in MOUNT state. The error message only lists the first file to be missed, but the V$RECOVER_FILE dynamic performance view lists all of the files that need to be recovered.

The online datafiles must also be synchronized with the control file. The database will automatically use the online redo logs to synchronize datafiles if necessary, but this may not provide sufficient recovery.

If a datafile cannot be synchronized using the online redo log files, the instance will stay in the MOUNT state.

An error will be returned, telling the administrator that a file is in need of media recovery.

Again, only the first failed file will be listed in the error message - other files needing recovery can be found using the V$RECOVER_FILE view.

Question

Which conditions must be met before a database can be opened?

Options:

0. All control files must be present and synchronized
0. All online datafiles must be present and synchronized
0. The database must have been shut down in a clean state
0. There must be at least one member of each redo log group listed in the control file present

Answer

The database can only be opened if all control files and online datafiles are present and synchronized, and there is at least one member of each redo log group present.

Option 1 is correct. If any of the control files listed in the initialization parameter file are missing or corrupt, the database will remain in the NOMOUNT state.

Option 2 is correct. The datafiles listed in the control file must either be present and synchronized or present but offline. The database can perform some synchronization automatically, but in some cases media recovery must also be performed by the database administrator.

Option 3 is incorrect. As long as the redo log files, datafiles, and control files are present, the database can be returned to a clean state as it is opened.

Option 4 is correct. The database can open with some redo log files missing, as long as there is at least one member of each redo log group present.

2. Changing the instance status

When you start a database instance you will usually want to complete the startup, opening the database completely. This is the default startup mode.

In some situations, you shouldn't start the database in OPEN mode.

For example, if you want to change the database control files, you should start the database in NOMOUNT mode. If you want to rename the datafiles, or change the redo log files that are archived, you open the database in MOUNT mode.

You can start a database from a SQL interface, in NOMOUNT, MOUNT, or OPEN mode. If no argument is specified, the database is started in OPEN mode.

STARTUP [NOMOUNT | MOUNT | OPEN]

You can also use SQL commands to change a database's status.

ALTER DATABASE [NOMOUNT | MOUNT | OPEN]

You can also use Enterprise Manager to start the database in different modes. If the database is currently shut down, you can start it again from the Startup/Shutdown page.

If you click the Yes button, the database will start again in OPEN mode.

Suppose you want to start the database in a mode other than OPEN.

You click the Advanced Options button to open the database in other modes.

The Advanced Startup Options screen allows you to choose between starting the instance, which starts the database in NOMOUNT mode, mounting the database, and opening the database.

Suppose you have shut the database down to perform maintenance. You need to start the database instance to a state where the initialization file has been read and you can change the database control files.

You select Start the instance, then click OK, and Yes.

The database instance is starting.

Question

You have started a database instance without opening the database. You want to place the instance in a mode that will allow you to rename datafiles.

Type the SQL command needed to change the status of the database.

Answer

The ALTER DATABASE MOUNT command changes the database to the MOUNT state, which allows you to rename datafiles.

Question

You have shut the database down to perform maintenance. You now want to start the database instance to a state where the initialization file has been read, and you can change the database control files.

Select the option that starts the database in the correct state.

Options:

0. Click the Advanced Options button, and select Mount the database, then click OK, and Yes.
0. Click the Advanced Options button, and select Start the instance, then click OK, and Yes.

Answer

You click the Advanced Options button, and select Start the instance, then click OK, and Yes.

3. Loss of files through media failure

Some critical files must be kept available as long as the database is open. If the database suffers a media failure, the loss of a hard disk for example, and loses one of these files, the instance will fail. These critical files are

• control files
• datafiles from the SYSTEM or UNDO tablespaces
• redo log groups

control files

The control files contain the names and locations of the database datafiles and redo log files. If any of the database control files are lost, the database instance will fail.

datafiles from the SYSTEM or UNDO tablespaces

The SYSTEM tablespace contains the data dictionary tables for the rest of the database. The UNDO tablespace stores undo information and rollback segments. The database instance cannot continue if datafiles from either of these tablespaces are lost.

redo log groups

Each redo log group contains several redo log files. The instance can continue as long as at least one member of each group is available, but will fail if all group members for a group are lost.

The instance may not shut down immediately when a critical file is lost, but it will be unable to do any more work. The first step in recovering from the media failure should be to shut down the database.

Most of the database shutdown modes attempt to complete transactions, write information to the redo logs, or undo changes before closing. These actions may not be possible if the UNDO tablespace or a redo log group has been lost, in which case you should use the SHUTDOWN ABORT option.

Question

What consequences of media failure can cause an instance to fail?

Options:

0. Loss of a control file
0. Loss of all datafiles from a normal tablespace
0. Loss of any datafiles from the SYSTEM or UNDO tablespaces
0. Loss of all files from a redo log group

Answer

The instance will fail if any control files are lost, or any datafiles from the SYSTEM or UNDO tablespaces, or all the files from a redo log group.

Option 1 is correct. The control files contain the names of the data and redo log files. If a control file is lost the database will not be able to locate those files, and the instance will fail.

Option 2 is incorrect. If the datafiles from a tablespace are lost, you will not be able to make any more changes to that tablespace. However, the rest of the database will be accessible, so the instance can continue. The SYSTEM and UNDO tablespaces are exceptions to this rule. If any files are lost from either of these tablespaces, the instance will fail.

Option 3 is correct. The SYSTEM tablespace contains the data dictionary for the database, and the UNDO tablespace stores undo information. If any files from these tablespaces are lost, the instance will fail.

Option 4 is correct. The instance can continue if some files from a redo log group are lost, as long as at least one file remains. If all the files from the redo log group are lost, the instance will fail.

Summary

When a database is opened, it moves from the SHUTDOWN state through NOMOUNT and MOUNT states to the OPEN state. At each of these stages it checks that the correct files are present and synchronized. If critical files are missing, the database will remain in the NOMOUNT or MOUNT state, depending on which files are missing.

By default, the database will attempt to start in OPEN mode, but you can choose to start it in NOMOUNT or MOUNT modes instead by adding an argument to the STARTUP command. You can also change the database state once it has opened, using the ALTER DATABASE command. Enterprise Manager also allows you to choose which mode to open the database in.

A media failure when the database is open may cause loss of certain files. If they include the database control files, datafiles from the UNDO and SYSTEM tablespaces, or all of the files in any redo log group, the database instance will fail. You should perform a SHUTDOWN ABORT before attempting to recover the lost files.