Recovering an Oracle 10g database

Learning objective

After completing this topic, you should be able to recover a database.

Exercise overview

In this exercise, you're required to recover datafiles using Enterprise Manager.

This involves the following tasks:

• recovering a file in ARCHIVELOG mode
• recovering a file in NOARCHIVELOG mode
• preparing databases for Flashback
• using Flashback

Task 1: Recovering a file in ARCHIVELOG mode

In this case, the database is in ARCHIVELOG mode. You discover that a datafile is missing and want to retrieve only that file.

Recover the sysaux01 file to the most recent time possible.

Steps list

Instructions
1. Select Maintenance
2. Select Perform Recovery
3. Click Next
4. Click Add
5. Select the sysaux01 file
6. Click Select
7. Click Next
8. Click Next
9. Click Submit

Task 2: Recovering a file in NOARCHIVELOG mode

In this case, the database is in NOARCHIVELOG mode.

Initiate the recovery of the inventory file to the most recent time possible.

Steps list

Instructions
1. Select Maintenance
2. Select Perform Recovery
3. Click Next

Task 3: Preparing databases for Flashback

You also want to leverage the advances in the Flashback technology.

You are preparing a table for trainee sales-staff. They will be working in the SCOTT schema. You want to set up the SALGRADE table for their use and be able to flash it back if you need to.

Steps list

Instructions
1. Click Administration
2. Click Tables
3. Type Scott in the Schema text box and click Go
4. Click the SALGRADE hyperlink.
5. Click Edit
6. Click Options
7. Select Yes from the Enable Row Movement dropdown
8. Click Apply

Task 4: Using Flashback

The data in the SALGRADE table has become corrupted. You want to use Flashback technology to resolve this problem.

Flashback the SALGRADE table to Feb 16 2007, 2:20 PM.

Steps list

Instructions
1. Click Main t enance
2. Click Pe r form Recovery
3. Click Perform Object Level Recovery button
4. Select the Flashback to a timestamp radio button
5. Click Next
6. Type Scott.Salgrade in the Tables To Flashback text box, press Enter, and click Next
7. Click Submit

Recovering from media failure in Oracle 10g

Learning objective

After completing this topic, you should be able to recover from the loss of control files, redo log files, and datafiles.

1. Recovering from loss of a control file

The Oracle database has three copies of its control files by default, and you should maintain this number, or a minimum of two, at all times.

If a control file is lost, for example due to media failure, you should shut down the database using the SHUTDOWN ABORT command.

When the database has been shut down, you can restore the missing control file by copying one of the remaining control files to the expected location of the missing file.

If you can't create a new copy in the old location, perhaps because that disk has failed, then you should create a copy in a new location, and update the initialization parameter file with the new address.

Note

You could just delete the reference to the lost control file from the initialization parameter file, and restart the database. This is not recommended, however, because of the danger that the remaining control files will be lost.

Suppose that you receive user complaints saying that the database is down. You try to restart the database in Enterprise Manager, but the attempt fails. You then open SQL*Plus to investigate the problem.

First, you check the instance status, with the command

SELECT status FROM v$instance;

The status returned is STARTED, so you know that the instance is in the NOMOUNT stage.

You then attempt to mount the database, with the command

ALTER DATABASE MOUNT;

but the command fails. It returns an error that says a control file can't be identified, and advises you to check the alert log for more details.

The alert log tells you which of the control files is missing. You can then restore the missing control file by copying one of the remaining files. The database can then be reopened.

Question

Your system had two control files but one has been lost. Which of the following are recommended practices for recovering from control file loss?

Options:

0. Change the CONTROL_FILES parameter so that it doesn't point at the missing file
0. Check the alert logs
0. Check instance status in V$INSTANCE view
0. Make a copy of the remaining control file

Answer

You should check the instance status, read the alert log, and then make a copy of the remaining control file.

Option 1 is incorrect. If you only had the very minimum of two control files to start with, you shouldn't simply change the CONTROL_FILES parameter when a file is lost.

Option 2 is correct. The alert logs tell you which copy of the control file has been lost.

Option 3 is correct. The database is returned to the NOMOUNT state when a control file is lost. The instance status tells you what state the database is in.

Option 4 is correct. You should always have three copies of the control file, or at the very least two, in case one is lost. If you only have two and one is lost, you should immediately copy the other so that you have two copies again.

2. Recovering from loss of redo log files

The redo log files are organized into redo log groups. If one of these files is lost, a message will be sent to the alert log, though the instance can continue as long as at least one member of the redo log group is available.

If you lose a redo log file you should restore it as soon as possible in case the other group members are lost.

Suppose you see a message in the alert log telling you that a redo log file is missing.

ORA-00313: open failed for members of log group 1 of thread 1


ORA-00312: online log 1 thread 1:


'/oracle/oradata/orcl/redo01.log' ORA-27037: unable to obtain


file status Linux Error: 2: No such file or directory

You can restore the missing file by copying one of the remaining log files in the command line.

$ cp < remaining log file>< missing log file>

Question

Which of these are characteristics of redo log files?

Options:

0. Redo log files can only be restored when the database is in MOUNT state
0. The instance can continue with one member of each redo log group
0. The instance will shut down if a redo log file is lost
0. You can replace log files by copying existing files

Answer

The instance can continue with one member of each redo log group, and you can replace redo log files by copying existing files.

Option 1 is incorrect. You can restore redo log files while the database is open.

Option 2 is correct. You must have at least one member of each redo log group to continue. You should make sure that there are at least two members, however, to provide redundancy.

Option 3 is incorrect. If a redo log file is lost it will be recorded in the alert log. The instance will only shut down if all of the files in a redo log group are lost.

Option 4 is correct. If you lose a redo log file you can replace it by copying one of the other log files from the group.

3. Recovering from the loss of a datafile

A media failure could lead to the database losing datafiles. The steps you take to restore the datafile will depend on the tablespace the datafile belongs to, and the database mode:

• NOARCHIVELOG mode
• ARCHIVELOG mode
• datafile from SYSTEM or UNDO tablespaces

NOARCHIVELOG mode

If the database is running in NOARCHIVELOG mode, you must perform a full recovery of the database to restore any datafile. This recovery will only restore the database up to the last backup.

ARCHIVELOG mode

If the database is in ARCHIVELOG mode you can recover a single datafile as long as it is not from the SYSTEM or UNDO tablespaces, leaving the rest of the database open for users to work with. The restored datafile will contain all changes made up to the last committed change.

datafile from SYSTEM or UNDO tablespaces

The datafiles in the SYSTEM and UNDO tablespaces are system-critical. You must shut down the database instance and place the database in the MOUNT state while these datafiles are being recovered.

Suppose you receive a message from a user saying that they can't access data from the EMPLOYEES table. The database is in NOARCHIVELOG mode. You check the table to see if there is a problem, with the command

SELECT count (*) FROM hr.employees

This tells you that one of the datafiles can't be found.

You open Enterprise Manager to try to recover the missing file.

You click the Maintenance tab.

You then select the Perform Recovery link.

Because the database is in NOARCHIVELOG mode, you must perform a complete recovery. The Whole Database option is automatically selected in the Object Type drop-down list, and you click Next to continue.

This will shut down the instance before directing you to a recovery wizard that you use to restore the whole database. The database will be recovered to the time of the last backup, so any changes made after that time must be re-entered.

Suppose the database was in ARCHIVELOG mode. After discovering the datafile that was missing, you could choose to recover only that file.

You open Enterprise Manager, and select the Perform Recovery link on the Maintenance tabbed page as before.

In this case you are only recovering a single file, so you accept the default Datafiles setting in the Object Type drop-down list.

By default, the datafile will be restored to the current time, though you can choose to restore it to a previous state. You accept the default option Recover to current time or a previous point-in-time.

You must enter your credentials, to prove that you are authorized to perform the recovery, and then click Next.

You then select the datafiles to be recovered.

You click Add to display the list of datafiles.

You click the Select checkbox for the users01.dbf file.

You click Next to accept the selected file.

And then click Next again to proceed.

You click Submit to perform the recovery.

If a datafile from the SYSTEM or UNDO tablespaces is lost, the database will shut down. Any attempts to restart will fail, and the database will be left in the MOUNT state.

You click Perform Recovery and follow the same sequence of steps as before.

When the recovery has succeeded, you can restart the database.

Question

How does recovering a SYSTEM datafile or a datafile belonging to an UNDO tablespace differ from recovering other datafiles?

Options:

0. You can replace SYSTEM and UNDO tablespace files by copying remaining files
0. You can only recover SYSTEM and UNDO datafiles if the database is in ARCHIVELOG mode
0. SYSTEM or UNDO datafiles must be recovered from the MOUNT state

Answer

If any datafiles from the SYSTEM or UNDO tablespaces are lost, the database shuts down. The files must be recovered while the database is in MOUNT mode.

Option 1 is incorrect. You must recover the datafiles from backups with the database shut down.

Option 2 is incorrect. These datafiles must be recovered while the database is shut down, but can be recovered whether the database was in ARCHIVELOG or NOARCHIVELOG mode. With the database in NOARCHIVELOG mode, recovery is possible only up to the time of the last backup.

Option 3 is correct. The instance shuts down immediately if datafiles are lost from the SYSTEM or UNDO tablespaces. You must place the instance in MOUNT mode to recover the lost files.

Question

Your database is running in NOARCHIVELOG mode when a media failure causes you to lose the employees01.dbf datafile.

Select the option that recovers the lost file.

Options:

0. Click the Maintenance tab, and select Perform Recovery. Then click Next.
0. Click the Maintenance tab, and select Perform Recovery. Select Datafiles from the Recovery Type drop down list, and click Next, then select the employees01.dbf, click Next again, and click Submit.

Answer

You click the Maintenance link, and select Perform Recovery. Then you click Next.

Summary

The Oracle database has three copies of its control files by default, and you should maintain this number, or a minimum of two, at all times. When a control file is lost, the database instance stops and the database goes to the NOMOUNT state. You can reopen the database once you have restored the missing control file from a surviving copy.

Redo log files are organized into redo log file groups. There must be at least one member of each group available in the database, otherwise the instance will stop. A message is sent to the alert log whenever a redo log file is lost. When this happens, you should create a copy from one of the remaining members of the group.

If datafiles are lost with the database in NOARCHIVELOG mode, you must restore the entire database to recover them. In ARCHIVELOG mode you can recover just the missing datafiles. If any SYSTEM or UNDO tablespace datafiles are lost, the instance will shut down.

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.