IN YOUR SQL*PLUS SESSION, CONFIRM THAT THE PROBLEM HAS BEEN FIXED BY RUNNING THE QUERY FROM STEP 6

11. In your SQL*Plus session, confirm that the problem has been fixed by

running the query from Step 6.

EXAM TIP Damage to a multiplexed online log file results in neither

downtime nor data loss; damage to a nonmultiplexed log file group (or

every member of a multiplexed group) may result in both.

Recovery from Loss of a Tempfile

If a tempfile is damaged, the database will remain open. It can also be opened, if the

damage occurred while the database was closed. Users will only become aware of

the problem when they attempt to use the tempfile: when their server process finds it

necessary to write some temporary data, because of insufficient space in the session’s

PGA. As a DBA, you should, however, be aware of any such damage: it will be reported

in the alert log. To fix a problem with a tempfile, add another tempfile to the temporary

tablespace and drop the original:

alter tablespace temp add tempfile'/u01/app/oracle/oradata/orcl/temp02.dbf' size 50m;alter tablespace temp drop tempfile'/u01/app/oracle/oradata/orcl/temp01.dbf';

Recovery from Loss of Datafiles

Media failure resulting in damage to one or more datafiles requires use of restore and

recover routines: a backup of the datafile must be restored, and then archive redo logs

applied to it to synchronize it with the rest of the database. There are various options

available, depending on whether the database is in archivelog mode or not, and

whether the file damaged is one that is critical to Oracle’s functioning or a noncritical

file containing “only” user data.

In all cases you must determine the extent of the damage. This is done by querying

the view V$RECOVER_FILE, which will list all datafiles found to be damaged or missing.

This view is available when the database is in mount mode (necessary if a critical file has

been damaged) or open mode (if the damage is limited to noncritical files).

Recovery of Datafiles in Noarchivelog Mode

There is no concept of recovery when in noarchivelog mode, because the archive log

files needed for recovery do not exist. Therefore, only a restore can be done. But if a

restored datafile is not synchronized with the rest of the database by application of

archive redo log files, it cannot be opened. The only option when in noarchivelog

mode is therefore to restore the whole database: all the datafiles, and the controlfile.

Provided that all these files are restored from a whole offline backup, after the restore

you will have a database where all these files are synchronized, and thus a database that

can be opened. But you will have lost all the work done since the backup was taken.

Once the full restore has been done, the database will still be missing its online

redo log files, unless they were backed up and restored as well. For this reason, the

post-restore startup will fail, with the database being left in mount mode. While in

mount mode, issue ALTER DATABASE CLEAR LOGFILE GROUP <group number>

commands to recreate all the log file groups. Then open the database.

In noarchivelog mode, loss of any one of possibly hundreds of datafiles can be

P AR T III

corrected only by a complete restore of the last backup. The whole database must be

taken back in time, with the loss of users’ work. Furthermore, that last backup must

have been a whole, offline backup, which will have entailed downtime. It should by

now be apparent that the decision to operate your database in noarchivelog mode

should not be taken lightly.

EXAM TIP If in noarchivelog mode, your only option following loss of a

datafile is a whole database restore. There can be no recovery.

Recovery of a Noncritical Datafile in Archivelog Mode

In an Oracle database, the datafiles that make up the SYSTEM tablespace and the

currently active undo tablespace (as specified by the UNDO_TABLESPACE parameter)

are considered to be “critical.” Damage to any of these will result in the instance

terminating immediately. Furthermore, the database cannot be opened again until

the damage has been repaired by a restore and recover exercise. Damage to the other

datafiles, which make up tablespaces for user data, will not as a rule result in the

instance crashing. Oracle will take the damaged files offline, making their contents

inaccessible, but the rest of the database should remain open. How your application

software will react to this will depend on how it is structured and written.

TIP Is it safe to run your application with part of the database unavailable?

This is a matter for discussion with your developers and business analysts,

and an important point to consider when deciding on how to spread your

segments across tablespaces.

The restore and complete recovery of a datafile can succeed only if all the archive

log files generated since the last backup of the datafile are available. If for some reason

an archive log file is missing or corrupted, the recovery will fail and the only option is

a complete restore, and an incomplete recovery up to the missing archive, which will

mean loss of all work done subsequently.

There are four steps for open recovery of a noncritical datafile: