Document Sample
Ch16 Powered By Docstoc
					CHAPTER 16
    Restore and Recover
    with RMAN

Exam Objectives
    In this chapter you will learn to
       • 052.16.1 Describe the Data Recovery Advisor
        • 052.16.1 Use the Data Recovery Advisor to Perform Recovery (Controlfile, Redo
                     Log File and Datafile)
        • 053.7.1    Perform Complete Recovery from a Critical or Noncritical Datafile Loss
                     Using RMAN
       • 053.7.2     Perform Incomplete Recovery Using RMAN
       • 053.7.3     Recover Using Incrementally Updated Backups
       • 053.7.4     Switch to Image Copies for Fast Recovery
       • 053.7.6     Recover Using a Backup Controlfile
       • 053.13.3 Perform Block Media Recovery

OCA/OCP Oracle Database 11g All-in-One Exam Guide
           In principle, restore and recovery (Oracle uses these terms very precisely) following a failure are
           simple. To restore a damaged file is to extract it from a previously made backup; to recover it is
           to apply change vectors extracted from the redo stream, thus bringing it up to date. Recovery
           can be complete (meaning no loss of data) or incomplete (meaning that you do, deliberately,
           lose data). However, there are many variations, depending on the nature of the damage and
           the downtime that can be tolerated. Using Recovery Manager automates the process, which
           can make restore and recovery operations much faster than using manual techniques and also
           eliminates the possibility of making mistakes. There is also a Data Recovery Advisor Wizard,
           which can diagnose problems and recommend the best action to take.

           The Data Recovery Advisor
           The Data Recovery Advisor (the DRA) is a facility for diagnosing and repairing problems
           with a database. There are two interfaces: the RMAN executable and Enterprise Manager.
           The DRA is capable of generating scripts to repair damage to datafiles and (in some
           circumstances) the controlfile: it does not advise on problems with the spfile or with the
           online redo log files. It is dependent on the Automatic Diagnostic Repository (the ADR)
           and the Health Monitor. The information the Health Monitor gathers and the advice the
           DRA gives follow the same diagnosis and repair methods that the DBA would follow
           without them—but they make the process quicker and less prone to error.

           The Health Monitor and the ADR
           The Health Monitor is a set of checks that run automatically when certain error
           conditions arise, or manually in response to the DBA’s instructions. The results of
           the checks are not stored in the database, but in the file system. This is because the
           nature of some errors is such that the database is not available: it is therefore essential to have
           an external repository for the Health Monitor results. This repository is the Automatic
           Diagnostic Repository (the ADR), which is located in the directory specified by the
           DIAGNOSTIC_DEST instance parameter.
               Different Health Monitor checks can run only at various stages:
                • In nomount mode, only the “DB Structure Integrity” check can run, and it can
                  only check the integrity of the controlfile.
                • In mount mode, the “DB Structure Integrity” check will check the integrity of
                  the controlfile, and of the online redo log files and the datafile headers. The
                  “Redo Integrity Check” can also run, which will check the online and archive
                  log files for accessibility and corruption.
                • In open mode, it is possible to run checks that will scan every data block for
                  corruption, and check the integrity of the data dictionary and the undo segments.
               The interfaces that will allow manual running of Health Monitor checks are
           available only when the database is open. There are two interfaces: using SQL*Plus
           to invoke procedures in the DBMS_HM PL/SQL package, and Database Control.
           Figure 16-1 shows the Database Control interface. To reach this window, from the
           database home page take the Advisor Central link in the Related Links section, and
           then the Checkers tab.
                                                                   Chapter 16: Restore and Recover with RMAN


                                                                                                               PART III
Figure 16-1 The Database Control interface to the Health Monitor

   From the window shown in Figure 16-1, you can see the results of all Health
Monitor runs (runs in reaction to errors and manual runs) and also run checks on

The Capabilities and Limitations of the DRA
The DRA can do nothing unless the instance is in nomount mode, or higher. It follows
that it cannot assist if there is a problem with the initialization file. In nomount mode,
it can diagnose problems with the controlfile and generate scripts to restore it, either by
using an existing valid copy or (if none is available) by extracting a copy from a backup
set—provided it can find one. Once the database reaches mount mode, the DRA can
diagnose problems with missing or damaged datafiles and missing online log file groups,
and generate repair scripts.
     The DRA (in the current release) only supports single-instance databases. If a fault
brings down a RAC database, you can mount it in single-instance mode, use the DRA
to repair the damage, and then shut it down and reopen it in RAC mode. This technique
may not be able to repair damage that is local to one instance. The DRA cannot repair
failures on a primary database by using blocks or files from a standby database, and
neither can it repair failures on a standby database.

             EXAM TIP The DRA will function only for a single-instance database. It cannot
             work with a RAC clustered database, nor with a Data Guard standby database.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
           Exercise 16-1: Use the DRA to Diagnose and Advise Upon
           Problems In this exercise, you will cause a problem with the database, and use the
           DRA to report on it.
                1. From an operating system prompt, launch the RMAN executable:
                   rman target /
                2. Confirm that there is a whole full backup of the SYSAUX tablespace:
                   list backup of tablespace sysaux;
                    If this does not return at least one backup set of type FULL, create one:
                   backup as backupset tablespace sysaux;
                3. Shut down the instance and exit from RMAN:
                   shutdown immediate;
                4. Using an operating system utility, delete the datafile(s) for the SYSAUX
                   tablespace that were listed in Step 2. If using Windows, you may have to
                   stop the Windows service under which the instance is running to release
                   the Windows file lock before the deletion is possible.
                5. Connect to the database with SQL*Plus, and attempt a startup:
                    This will stop in mount mode, with an error regarding the missing file. If
                    using Windows, make sure the service has been started.
                6. Launch the RMAN executable and connect, as in Step 1.
                7. Diagnose the problem:
                   list failure;
                    This will return a message to the effect that one or more non-system datafiles
                    are missing.
                8. Generate advice on the failure:
                   advise failure;
                    This will suggest that you should restore and recover the datafile, and generate
                    a repair script. Open the script with any operating system editor, and study its

           Using the Data Recovery Advisor
           The Data Recovery Advisor makes use of information gathered by the Health Monitor
           to find problems, and then it constructs RMAN scripts to repair them. As with any
           RMAN-based utility, the instance must be started. To start an instance in nomount
           mode, all that is required is a parameter file. RMAN is in fact capable of starting an
           instance without a parameter file, using the ORACLE_SID environment variable as
           a default for the one parameter for which there is no default value: the DB_NAME
           parameter. This ability may mean that is possible to bootstrap a restore and recovery
           operation from nothing.
               The flow for using the DRA is as follows:
                                                             Chapter 16: Restore and Recover with RMAN

     • Assess data failures The Health Monitor, running reactively or on demand,
       will write error details to the ADR.
     • List failures The DRA will list all failures, classified according to severity.
     • Advise on repair The DRA will generate RMAN scripts to repair the damage.
     • Execute repair Run the scripts.
    The commands can be run from the RMAN executable, or through Database
Control. The advice will only be generated for errors previously listed and still open.
No advice will be generated for additional errors that have occurred since the listing,
or for errors fixed since the listing.

             TIP If one or more failures exist, then you should typically use LIST
             FAILURE to show information about the failures and then use ADVISE

                                                                                                         PART III
             FAILURE in the same RMAN session to obtain a report of your repair.
    Figure 16-2 shows a DRA session, launched from the RMAN executable. The
situation is that the instance started and mounted the database, but failed to open.

Figure 16-2 A DRA session, using the Recovery Manager
OCA/OCP Oracle Database 11g All-in-One Exam Guide
               The first command in the figure launches the RMAN executable, from an
           operating system prompt. The connection succeeds, but RMAN reports that the
           database is not open.
               The second command lists all current failures: there is one nonsystem datafile
           missing. If this step were omitted, the next step would not return anything.
               The third command generates advice on fixing the failure. The first suggestion is
           that some error by the system administrators could be responsible for the problem
           and could be fixed manually. Then there is an automatic repair involving restore and
           recovery. This is in the form of an RMAN script. The contents of the script (not shown
           in the figure) were
           # restore and recover datafile
           restore datafile 4;
           recover datafile 4;

               To run the script, the command would be
           repair failure;

               Following this, the database can be opened.

                         TIP The DRA works, but you can often do better. For example, it does not
                         generate scripts that will minimize downtime by opening the database before
                         doing the restore and recovery (which would be possible in the example).
               On connecting with Database Control to a damaged database, there will always be
           a button named PERFORM RECOVERY. Figure 16-3 shows the window this will produce for
           the same situation shown in Figure 16-2.

           Figure 16-3 The Database Control interface to the DRA
                                                                     Chapter 16: Restore and Recover with RMAN

    The Information section seen in Figure 16-3 shows that there is one failure, and
that the database is mounted. The ADVISE AND RECOVER button will launch a wizard that
will list details of the failure, generate the repair script, and then submit it as a job to
the Enterprise Manager job system, and finally prompt you to open the database.

             EXAM TIP The DRA will not generate any advice if you have not first asked
             it to list failures. Any failures occurring since the last listing, or fixed since the
             last listing, will not be advised upon.

   The DRA can generate scripts to restore a missing or damaged controlfile copy
and to rebuild a missing online log file group and to restore and recover missing or
damaged datafiles. It will not take any action if a member of a multiplexed log file
group is damaged.

                                                                                                                 PART III
Exercise 16-2: Repair a Fault with the DRA In this exercise, you will
diagnose and repair the problem caused in Exercise 16-1 using Database Control.

     1. Using a browser, attempt to connect to Database Control. This will present
        a window stating that the database is mounted, with buttons for STARTUP and

     2. Click STARTUP. Enter operating system and database credentials and follow the
        prompts to open the database. This will fail, so click PERFORM RECOVERY.
     3. In the Perform Recovery window, click ADVISE AND REPAIR to enter the DRA Wizard.
     4. In the View And Manage Failures window, click ADVISE.
     5. In the Manual Actions window, click CONTINUE WITH ADVICE.
     6. In the Recovery Advice window, observe the script and click CONTINUE.
     7. In the Review window, click SUBMIT RECOVERY JOB.
     8. When the job completes, use either Database Control or SQL*Plus to open
        the database. It is possible that Database Control will have gotten confused
        as a result of this exercise, and may have trouble determining what state the
        database is in. If this appears to be the case, close the browser and restart
        the Database Control processes from an operating system prompt with the
        emctl stop dbconsole;
        emctl start dbconsole;
        Reconnect with the browser, and confirm that the database is now open.

Database Restore and Recovery
Some files are critical. Damage to a critical file will mean that the database instance
will terminate if it is open, and cannot be reopened until the damage is repaired.
Other files are noncritical: if these are damaged, the database can remain open or be
opened if it is closed. In either case, there is no reason to lose data: you should be
able to perform a complete recovery from any form of damage, provided that you
OCA/OCP Oracle Database 11g All-in-One Exam Guide
           have a backup and the necessary archivelog files. The one exception to this rule is if
           you lose all copies of the current online log files.
              The critical files are

                • Any copy of the controlfile
                • A datafile that is part of the SYSTEM tablespace
                • A datafile that is part of the current undo tablespace

               Noncritical files are

                • Multiplexed online log files
                • Tempfiles
                • Datafiles that are not part of the SYSTEM or current undo tablespaces

               As a general rule, damage to any number of datafiles should be repaired with a
           complete recovery: no loss of data. Restore the damaged file(s), and apply redo to bring
           them right up to date. Incomplete recovery means to restore the database (the entire
           database) and apply redo only up to a certain point. All work done after that point
           will be lost. Why would one do this? Usually for one reason only: user error. If a
           mistake is serious enough, it will be necessary to take the whole database back in
           time to before the error was made so that the work can be redone, correctly. A second
           reason for incomplete recovery is because a complete recovery was attempted, but
           failed. This will happen if archive log files are missing, or if all copies of the current
           online log file group are lost.
               There are four steps for complete recovery:

                • Take the damaged data file(s) offline.
                • Restore the damaged file(s).
                • Recover the restored files(s).
                • Bring the recovered file(s) online.

           Complete Recovery from Data File
           Loss Using RMAN
           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 ongoing operation or if it is a noncritical
           file containing “only” user data.

           Recovery of Datafiles in Noarchivelog Mode
           There is no supported technique for 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
                                                                Chapter 16: Restore and Recover with RMAN

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, because they were never backed up. 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. If you do the restore through
the Database Control interface to RMAN, this process will be fully automatic.
    In noarchivelog mode, loss of any one of possibly hundreds of datafiles can be
corrected only by a complete restore of the last backup. The whole database must be

                                                                                                            PART III
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.

             TIP Virtually all databases (including test and development systems) should
             run in archivelog mode. Even if the service level agreement says that data can
             be lost, if any work is ever lost, your users will not be happy.

             EXAM TIP If in noarchivelog mode, your only options following loss of
             any datafile are either a whole database restore, or to drop the relevant
             tablespace. There can be no recovery.

   The RMAN commands to restore a database in noarchivelog mode are
shutdown abort;
startup mount;
restore database;
alter database open resetlogs;

     The first command will terminate the instance. This will not be necessary if the
damaged file is part of either the SYSTEM or currently active undo tablespaces, because
in that case it would have aborted already. The second command brings the database
up in mount mode; this can be done only if all copies of the controlfile are available—
if they are not, the controlfile must first be restored (as described in Chapter 18). The
third command will restore all datafiles from the most recent full, or incremental level
0, backup. The fourth command will recreate the online log file members, set the log
sequence number to 1, and open the database.
     If you are using incremental backups, there is a minor variation in restoring a
database in noarchivelog mode: the RECOVER command is needed, to apply the
incremental backups. After the restore and before opening the database, run this
recover database noredo;
OCA/OCP Oracle Database 11g All-in-One Exam Guide
               This command will locate all cumulative and differential incremental level 1
           backups and apply them. The NOREDO qualifier is needed to instruct RMAN not to
           attempt to apply any redo data—because, in noarchivelog mode, there is none to apply.

           Recovery of a Noncritical File 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.

                If your backups were done with RMAN, the restore and recovery operation of a
           damaged datafile will be completely automatic. RMAN will carry out the restore in the
           most efficient manner possible, making intelligent use of full and incremental backups
           and then applying the necessary archive logs. If RMAN is linked to a tape library
           through SBT channels, it will load the tapes automatically to extract the files it needs.
                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. Either they must
           still be on disk in the archive log destination directories, or if they have been migrated
           to tape, they must be restored during the recovery operation. RMAN will do the
           extract from a backup set and restore to disk automatically. If for some reason an
           archive log file is missing or corrupted, the recovery will fail, but since archive log
           destinations and RMAN backup sets can and should be multiplexed, you should
           never find yourself in this situation. If you do, 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.

           Exercise 16-3: Recover from Loss of a Noncritical Datafile with
           Database Control First, create a tablespace and a segment within it, and back it
           up. Then simulate damage to the datafile. Diagnose the problem and resolve it. The
           database will stay open for use throughout the whole exercise. At various points you
           will be asked to supply host operating system credentials, if you have not saved them
           in previous exercises: give a suitable Windows or Unix login, such as the Oracle owner.
                                                           Chapter 16: Restore and Recover with RMAN

 1. Connect to your database as user SYSTEM using SQL*Plus, and create a
    tablespace. For example, on Windows,
    SQL> create tablespace noncrit
    datafile 'C:\APP\ORACLE\ORADATA\ORCL\noncrit.dbf' size 2m;
    or on Unix,
    SQL> create tablespace noncrit
    datafile '/app/oracle/oradata/orcl/noncrit.dbf' size 2m;
 2. Create a table within the new tablespace and insert a row into it:
    SQL> create table ex16 (c1 date) tablespace noncrit;
    SQL> insert into ex16 values(sysdate);
    SQL> commit;
 3. Using Database Control, connect to your database as user SYSTEM.

                                                                                                       PART III
 4. From the database home page, take the Availability tab, then the Schedule
    Backup link in the Manage section.
 5. In the Schedule Backup window, select the Tablespaces radio button in the
    Customized Backup section. Click SCHEDULE CUSTOMIZED BACKUP.
 6. In the Schedule Backup: Tablespaces window, click ADD.
 7. In the Tablespaces: Available Tablespaces window, select the radio button for
    your new NONCRIT tablespace, and click SELECT.
 8. In the Schedule Customized Backup: Tablespaces window, click NEXT.
 9. In the Schedule Customized Backup: Options window, leave everything on
    defaults and click NEXT.
10. In the Schedule Customized Backup: Settings window, leave everything on
    defaults and click NEXT.
11. In the Schedule Customized Backup: Schedule window, select One Time
    (Immediately) radio button and click NEXT.
12. In the Schedule Customized Backup: Review window, study the script and
    click SUBMIT JOB to run the backup.
13. Simulate a disk failure by corrupting the new datafile. On Windows, open
    the file with Windows Notepad, delete a few lines from the beginning of the
    file, and save it; it is important to use Notepad because it is one of the few
    Windows utilities that will ignore the file lock that Oracle places on datafiles.
    On Unix you can use any editor you please, such as vi. Make sure that the
    characters deleted are at the start of the file, to ensure that the file header is
14. Flush the buffer cache, so that the next read of ex16 will need to read
    from disk:
    alter system flush buffer_cache;
OCA/OCP Oracle Database 11g All-in-One Exam Guide
               15. Confirm that the file is damaged by attempting to query the table:
                   SQL> select * from ex16;
                   select * from ex16
                   ERROR at line 1:
                   ORA-01578: ORACLE data block corrupted (file # 7, block # 9)
                   ORA-01110: data file 7: 'C:\APP\ORACLE\ORADATA\ORCL\NONCRIT.DBF'

               16. In your Database Control session, take the Availability tab from the database
                   home page, and then the Perform Recovery link in the Manage section.
               17. In the Perform Recovery: Type window, take the link for Datafiles With Error.
               18. In the Perform Object Level Recovery: Datafiles window, the new datafile will
                   be listed. Select it, and click NEXT.
               19. In the Perform Object Level Recovery: Rename window, click NEXT.
               20. In the Perform Object Level Recovery: Review window, click SUBMIT.
               21. In the Perform Recovery: Result window, study the output of the operation,
                   as shown in the illustration.

               22. Confirm that the tablespace and the tables within it are now usable, with no
                   loss of data.
                   SQL> select * from ex16;
               Of the four steps for complete recovery, the first step (taking the damaged file
           offline) will be automatic if the file is noncritical. The following two steps (restore
                                                                 Chapter 16: Restore and Recover with RMAN

and recovery) are accomplished with RMAN commands, and the final step (bringing
the datafile online) can be done with either SQL*Plus or RMAN. The scripts generated
by Database Control automate the entire procedure.
    If an incremental backup strategy has been used, a restore and recover operation
will make use of a level 0 incremental backup, then apply any incremental level 1
backups that may be available, and then apply redo data to complete the recovery. The
logic behind this method (which keeps use of redo to a minimum) is that it is always
faster to apply an incremental backup than to apply redo, because an incremental
backup can be applied in a single sequential pass through the datafile, whereas
applying redo (which consists of change vectors in chronological order) will involve
random access to the datafile. The presence or absence of incremental backups does
not lead to any syntactical difference in the use of the RECOVER command; RMAN,
by interrogating its repository, will work out the best way to perform the operation.

                                                                                                             PART III
             EXAM TIP RMAN will always apply incremental backups in preference to
             applying redo data, if they are available.

Recovering from Loss of a Critical Datafile
The datafiles that make up the SYSTEM and currently active undo tablespace are
considered critical by Oracle, meaning that it is not possible to keep the database
open if they are damaged. If any portion of the SYSTEM tablespace were not available,
parts of the data dictionary would be missing. Oracle cannot function without a
complete data dictionary. If parts of the undo tablespace were not available, it would be
possible that undo data required for maintaining transactional integrity and isolation
would not be available, and Oracle can’t take that chance either. Therefore, damage to
these datafiles will cause the instance to terminate immediately.

             TIP The critical datafiles should be on disk systems with hardware redundancy,
             such as RAID level 1 disk mirroring, so that in the case of media failure the
             files will survive and the database will remain open.

     If the database does crash because of damage to critical datafiles, as ever, the first
action is to attempt a startup. This will stop in mount mode, with error messages
written to the alert log showing the extent of the damage. To recover, follow the same
routine as that for a noncritical file, and then open the database. The restore and
recover process is identical to that for a noncritical file, but it must be carried out in
mount mode. Of the four steps for complete recovery, if the damaged file is a critical
file only the second and third steps are necessary: as the database cannot be opened,
there is no need to take the damaged file offline and bring it back online afterward.

             EXAM TIP Loss of a critical data file will not mean loss of data, but it will
             mean loss of time.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
           Incomplete Recovery
           An incomplete recovery means losing data. The whole database is taken back in time
           by a restore of all the datafiles, and then it is not completely recovered. Rather than
           applying all the redo generated since the backup was taken, you deliberately stop the
           application of redo at some point, to produce a version of the database that is not
           up-to-date. All work done from that point is lost. There are only two reasons for
           performing an incomplete recovery: either complete recovery is impossible or you
           deliberately decide to lose data.
                Complete recovery will not be possible unless all archive logs generated from the
           time of the backup are available, as well as the online redo logs. If an archive log is
           missing or corrupted, then recovery will stop at that point. Complete recovery should
           never fail because of missing archive or online log files, as both file types can and
           should be multiplexed to different devices, making their total loss impossible, but
           it can happen. If so, incomplete recovery up to the point at which the missing or
           damaged redo data occurs is your only option.

                         EXAM TIP Incomplete recovery is necessary if there is a missing archive log,
                         or if all copies of the current online redo log file group are missing.

                To decide to lose data deliberately is a course of action taken after user error. It
           may be that a user has committed a transaction that is inappropriate to the business
           requirements. Such errors could include perfectly normal mistakes—we all make
           mistakes—while using package software, but more commonly they are errors made
           using tools such as SQL*Plus. Omitting a WHERE clause when issuing an UPDATE
           or DELETE statement will result in the whole table being affected, not just one row;
           if this change is committed, perhaps by exiting from the tool, then the changes are
           irreversible. As far as Oracle is concerned, it is a committed transaction and can never
           be rolled back. Worse still is issuing DDL commands. These include an implicit
           COMMIT statement. It is frighteningly easy, for example, to drop a table or even a
           schema when you think you are connected to the development database when in fact
           you are connected to production.
                Following a user error, you can restore the whole database and recover it up to the
           point just before the error, thus producing a version of the database without the mistake,
           but also, without all the correct work done since.

                         TIP There are several “flashback” technologies that may make it possible to
                         recover from user errors without resorting to an incomplete recovery.

                         EXAM TIP It is not possible to skip the recovery of a bad transaction and
                         recover all other work.
                                                               Chapter 16: Restore and Recover with RMAN

    A special case of incomplete recovery is recovery of the controlfile. Ideally, all
recovery operations will be conducted using the current controlfile, but there are
circumstances when this isn’t possible and a backup of the controlfile must be
restored. There are two possible reasons for this: either all copies of the current
controlfile have been lost and it is not possible to run a CREATE CONTROLFILE
command to recreate it, or the current controlfile does not accurately describe the
version of the database you want to restore, typically, because changes such as
dropping tablespaces have occurred since taking the backup.
    There are four steps for incomplete recovery:

     • Mount the database.
     • Restore all the datafiles.
     • Recover the database until a certain point.

                                                                                                           PART III
     • Open the database with reset logs.

     The first contrast with complete recovery is that complete recovery can be done
with the database open, unless the damaged files are critical. Incomplete recovery can
be done only in mount mode.
     Second, for a complete recovery, you restore only the damaged datafiles; incomplete
recovery operations begin with a restore of all datafiles. The datafiles do not have to
be restored from the same backup, but they must all be older than the point to which
you wish to recover. The controlfile will have to be restored as well as the datafiles if
the physical structure of the current database is different from the structure of the
version being restored. For example, if a tablespace has been accidentally dropped, the
current controlfile will know nothing about it. Restoring the datafiles that make up
the tablespace won’t help: the current controlfile will ignore them, and not include
them in the recovery. Do not restore the controlfile unless you have to; it may complicate
matters if you do.
     The third step is to apply redo from archive and (if necessary) online logs to the
desired point. This contrasts with complete recovery, where you apply all the redo to
bring the database right up-to-date; for incomplete recovery, you stop the recovery at
whatever point you want prior to the latest time. There are several options for specifying
the point to which you want to recover.
     Finally, open the database with RESETLOGS. This will reinitialize the online redo
log files, creating a new incarnation of the database. An incarnation of a database is a
version of the database with a new thread of redo, beginning at log sequence number 1.
This is the final contrast with complete recovery. After a complete recovery, the database
is exactly as it was before the problem occurred, but after an incomplete recovery it is a
different incarnation. Backups and archive logs are specific to an incarnation and those
generated by one incarnation must be kept separate from those generated by a previous

             EXAM TIP You must be connected AS SYSDBA to do an incomplete
             recovery. No normal user, and not even a SYSOPER user, can do this.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
              Having mounted the database and restored all the datafiles and (if necessary) the
           controlfile, you have three options for incomplete recovery:

                • Until time
                • Until system change number (SCN)
                • Until log sequence number

               The UNTIL TIME option will apply redo to roll the datafiles forward until a
           particular time. The precision is to the second. This option would usually be used to
           correct user error. If a user made an irreversible mistake and the time the mistake was
           made is known, then a time-based recovery to just before the mistake may be the best
               The UNTIL SCN option can be used if the exact system change number when the
           error was made is known. By using advanced tools such as the Log Miner utility or by
           using the Flashback capability to be detailed in Chapter 19, it may be possible to identify
           exactly the SCN at which a transaction was committed. The recovery can be stopped
           precisely before the problem, thus losing the minimum possible amount of data.
               The UNTIL SEQUENCE option is used if an archive log file or an online log file
           group is missing; it will recover all work up to the log switch into the missing file
           or group.

                         EXAM TIP The syntax for incomplete recovery differs between SQL*Plus and
                         RMAN. SQL*Plus uses UNTIL CANCEL and UNTIL CHANGE, where RMAN
                         would use UNTIL SEQUENCE and UNTIL SCN. They both use UNTIL TIME.

               By default, RMAN will restore the most recent backup, and apply all available
           redo. Incomplete recovery must modify this behavior: the restore must be from
           backups that are older than the point in time to which the recovery is to be made, and
           the recovery must be stopped at that time. To ensure that the restore and recover both
           use the same UNTIL TIME, it is considered best practice to execute both commands
           within a single run block. For example,
           1   run {startup mount;
           2   set until time = "to_date('27-10-08 10:00:00','dd-mm-yy hh24:mi:ss')";
           3   restore database;
           4   recover database;
           5   alter database open resetlogs;}

              This example, with line numbers added for clarity, shows the four steps for
           incomplete recovery:

                • Line 1 The database must be started in mount mode. It is of no significance
                  whether the preceding shutdown were orderly or an abort.
                • Line 2 The SET UNTIL command specifies a time that will be applied to
                  all subsequent commands. The example includes a format string that will
                  eliminate any ambiguity in interpreting the date and time. An alternative is
                                                             Chapter 16: Restore and Recover with RMAN

       to rely on matching the NLS_DATE_FORMAT environment variable. Note the
       use of double quotes around the entire string, and single quotes within it.
    • Line 3 The RESTORE command will extract datafiles from backups that are
      at least as old as the time specified in the SET UNTIL command.
    • Line 4 The RECOVER command will apply redo from archive log files and
      (if necessary) online log files, stopping at the specified time.
    • Line 5 The RESETLOGS clause instructs Oracle to initialize the online redo
      log files and reset the log switch sequence number.

   An alternative syntax is to specify the UNTIL value with each command. For
restore database until time 'sysdate - 7';

                                                                                                         PART III
recover database until time '27-OCT-08';

The first of these commands instructs RMAN to restore the database from backups
that are at least seven days old. The second command will perform an incomplete
recovery up to the beginning of October 27, 2008, assuming that the NLS_DATE_
FORMAT environment variable is set to dd-MON-rr.

Autobackup and Restore of the Controlfile
For a complete recovery, it should always be possible to use the current controlfile.
The only case where this will not be possible is if all copies have been lost—typically,
because it was not multiplexed and the one copy was damaged. The database is critically
dependent on the controlfile: it will crash if the controlfile is damaged. RMAN makes
the situation even worse: the RMAN repository, which stores details of all backups, is
stored in the controlfile. So if the controlfile is damaged, the database will crash—but
RMAN will not be able to restore it, because the information it needs will not be
available. There are two ways around this recursive problem: either use an RMAN
catalog database (as described in Chapter 17) or enable the AUTOBACKUP facility.
    Because the controlfile is vital not only to the running database but also to RMAN,
an automatic backup can be enabled. From the RMAN prompt,
configure controlfile autobackup on;

    Having executed this command, every RMAN operation will conclude with an
automatic backup of the controlfile and the spfile into a backup set stored in a well-
known location. Then if necessary, with the database in nomount mode, you can
use this:
restore controlfile from autobackup;

    This command instructs RMAN to go to the well-known filename in a well-known
location and extract the controlfile from the most recent autobackup. The restore will
be to the locations given in the spfile. Then mount the database using the restored
OCA/OCP Oracle Database 11g All-in-One Exam Guide
           controlfile, and RMAN will be able to locate the backups needed to restore the rest
           of the database. The well-known location will be in the flash recovery area if this has
           been enabled. The well-known filename is based on the DBID; the DBID (a ten-digit
           number) should be part of your most basic documentation, and will have been
           displayed whenever you connect to the database with RMAN. It is also visible as the
           column DBID in the V$DATABASE view.
               If the spfile has also been lost, start the instance with a dummy initialization file:
           a pfile with just one parameter, DB_NAME. Then connect with RMAN, and issue these
           commands, substituting your DBID number for that given:
           set dbid 1234567890;
           restore spfile from autobackup;

               The restore of an spfile will be to the default location, in $ORACLE_HOME/dbs
           for Unix or %ORACLE_HOME%\database for Windows. Then restart the instance in
           nomount mode, which will use the restored spfile, and restore the controlfile. Mount
           the controlfile, and RMAN will then have access to its repository and can locate and
           restore the datafile backups.

                         EXAM TIP The commands restore controlfile from
                         autobackup and restore spfile from autobackup can be
                         executed in nomount mode. All other RMAN commands can be executed
                         only in mount or open mode.

           Exercise 16-4: Enable Controlfile Autobackup In this exercise, enable the
           controlfile autobackup facility and observe it in operation.
                1. Connect to the database with RMAN:
                   rman target /
                2. Confirm that autobackup is not enabled:
                   show controlfile autobackup;
                    This will show that autobackup is not enabled.
                3. Enable autobackup, and confirm:
                   configure autobackup on;
                   show all;
                    Note that the default format for autobackups is ‘%F’.
                4. Carry out a backup operation:
                   backup datafile 1;
                    Observe that following the requested backup, there is a second backup set
                    created for the controlfile and the spfile.
                5. Determine the location of autobackup of the controlfile and the spfile:
                   list backup of controlfile;
                   list backup of spfile;
                                                                       Chapter 16: Restore and Recover with RMAN

         In both cases, the most recent backup set listed will be to a file in the flash
         recovery area. This can be overridden by configuring a format other than
         the ‘%F’ shown in Step 3, but if this is done, the automatic location of the
         autobackups will no longer function.
    This script accomplishes the complete restore and recovery of a database,
assuming that everything was lost:
         1    run{startup nomount pfile=dummy.pfile;
         2    set dbid=1196323546;
         3    restore spfile from autobackup;
         4    shutdown abort;
         5    startup nomount;
         6    restore controlfile from autobackup;
         7    alter database mount;
         8    restore database;

                                                                                                                   PART III
         9    recover database;
         10    alter database open resetlogs;}
     Taking this script line by line,

 1         Start the instance, using a dummy parameter file with just one parameter: DB_NAME.
 2         Tell RMAN the DBID of the database on which you are working.
 3         Extract the spfile from the most recent autobackup, relying on defaults for its name and
 4         Abort the instance, because (having been started with a dummy pfile) it is not of any
           further use.
 5         Start the instance, using the restored spfiles.
 6         Extract the controlfile from the most recent autobackup.
 7         Mount the controlfile.
 8         Restore all the datafiles.
 9         Carry out a complete recovery by applying any incremental backups, followed by archive
           log files and online log files.
 10        Open the database, and reinitialize the online redo log files. A RESETLOGS is always
           required after restoring the controlfile.

Exercise 16-5: Perform Incomplete Recovery with RMAN, Using a
Backup Controlfile In this exercise, which continues from Exercise 16-3, you
will drop a tablespace and then perform an incomplete recovery to a time just before
it was dropped. This is possibly the most complex operation that may ever be
necessary. It must be performed in several stages: first a version of the controlfile that
dates from the time the tablespace existed must be restored, and the database must be
mounted with this controlfile. Only then can the rest of the database be restored and
    Before launching RMAN or SQL*Plus, ensure that your operating system date
format is set to a known value. For example, on Windows:
set NLS_DATE_FORMAT=dd-mm-yy hh24:mi:ss
OCA/OCP Oracle Database 11g All-in-One Exam Guide
           or on Unix or Linux:
           export NLS_DATE_FORMAT=dd-mm-yy hh24:mi:ss

                1. Connect to the database with RMAN, and perform a full whole backup:
                   rman target /
                   RMAN> backup as compressed backupset database;
                2. Connect to the database with SQL*Plus as user SYSTEM, and force a few log
                   switches and archives by executing this command a few times:
                   alter system switch logfile;
                3. Connect to the database with RMAN, and back up the archive logs while
                   removing them from the archivelog destinations:
                   backup archivelog all delete all input;
                4. Repeat Step 2.
                5. Note the current time:
                   select sysdate from dual;
                6. Wait a few seconds, and insert a second row into the ex16 table:
                   insert into ex16 values(sysdate);
                7. Drop the tablespace, and attempt to query the ex16 table:
                   drop tablespace noncrit including contents and datafiles;
                   select * from ex16;
                8. In your RMAN session, execute this run block to restore the controlfile,
                   substituting the time you noted in Step 5 for that shown, and using a suitable
                   name for the restored controlfile:
                   run {shutdown abort;
                   startup mount;
                   set until time = '26-10-08 15:35:33';
                   restore controlfile to '/u01/app/oracle/oradata/orcl/control04.ctl';}
                    It is not possible to restore the controlfile over the current controlfile copies,
                    because they are already in use to mount the database.
                9. In your SQL*Plus session, adjust the CONTROL_FILES parameter so that the
                   restored controlfile will be used for the next mount:
                   SQL> alter system set
                   SQL> shutdown abort;
                   SQL> startup mount;
               10. In your RMAN session,
                   run {allocate channel d1 type disk;
                   allocate channel d2 type disk;
                   set until time = '26-10-08 15:35:33';
                   restore database;
                                                               Chapter 16: Restore and Recover with RMAN

        recover database;
        alter database open resetlogs};
    11. Study the output of running the block in Step 8. In particular, note these points:
        • Two channels were used, to parallelize the operation.
        • The date given must conform to that specified by the NLS_DATE_FORMAT
          environment variable.
        • The restore will have been from the most recent backup earlier than the
          UNTIL time.
       • The recovery uses a combination of archive logs on disk, and archive logs
          extracted from a backup set.
   12. Connect to the database with SQL*Plus as user SYSTEM, and confirm that

                                                                                                           PART III
       while the restore and recovery has succeeded, the row inserted after the UNTIL
       time is gone:
        select * from ex16;
   13. Confirm that the log sequence number has been reset to 1:
        select * from v$log;

Using Image Copies for Recovery
If your backup strategy includes creating image copies as well as (or instead of)
backup sets, then you have another option available for restore operations: do not
restore at all. As an image copy is byte-for-byte the same as the source datafile, it can
be used immediately if it is still available on disk. All that is necessary is to tell the
database the location of the image copy, and then recover the copy. This can result in
massive time savings when compared with the delays involved in extracting a datafile
from a backup set.
    To facilitate the use of image copies, use the following RMAN command:
RMAN> backup as copy database;

This command will copy every datafile to the flash recovery area, as image copies.
    To use an image copy, first take the original datafile offline (which will have
happened automatically in many cases) and then update the controlfile to point
to the copy, and recover it. For example, if a copy has been made as follows:
RMAN> backup as copy datafile 4 format '/u02/df_copies/users.dbf';

   Then it can be brought into use like this:
RMAN> run {sql 'alter database datafile 4 offline';
set newname for datafile 4 to '/u02/df_copies/users.dbf';
switch datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';}
OCA/OCP Oracle Database 11g All-in-One Exam Guide
               This accomplishes a complete recovery, without actually needing to restore. The
           SWITCH command is equivalent to the ALTER DATABASE RENAME FILE command
           that can be executed in SQL*Plus. If the whole database has been copied to the flash
           recovery area then the whole database can be “restored” with one command:
           RMAN> switch database to copy;

                         EXAM TIP Any SET commands, such as SET UNTIL, SET NEWNAME, and
                         SET DBID, can be executed only within a run block, never as stand-alone
                         commands at the RMAN prompt.

                         TIP Maintaining a reasonably up-to-date image copy of the database will
                         mean that the downtime involved in any restore and recover operation is
                         substantially reduced. If you have the disk space available, you should consider
                         doing this.

               Another use of image copies is to update them by applying incremental backups.
           This technique of updating copies with incremental backups takes a full copy as its
           starting point, and then rolls the copy forward by applying the incrementals. This
           requires as a starting point a complete copy of the database (which can be made while
           the database is open), followed by creating incremental backups with the syntax that
           will permit them to be applied to the copy. This simple script, just two commands,
           can accomplish the entire process:
           run {
           backup incremental level 1 for recover of copy with tag 'inc_copy' database;
           recover copy of database with tag 'inc_copy' ;

               The first time the script is run, the BACKUP command will attempt a level 1 backup,
           but as there is no level 0 backup on which to base the level 1, it will perform a level 0
           backup instead. The syntax will cause RMAN to make this as a copy, rather than as a
           backup set. The RECOVER command will fail, because there will be neither a copy of
           the database nor an incremental backup to apply. The second time the script is run,
           the first command will perform a level 1 backup, extracting all changed blocks since
           the first run. The second command will apply this incremental backup to the copy.
           This behavior will continue for all subsequent runs.
               A strategy based on incrementally updated backups can result in very fast recovery
           times, with minimal backup workload on the live database: there is only ever one full
           backup, and (if the script is run daily) the worst case is that the copy would be one day
           behind. It will be necessary to back up the copy (with, for example, BACKUP RECOVERY
           AREA if the copy has been directed to the flash recovery area) and the archive log files,
           because without this a point-in-time recovery to any time earlier than the most recent
           run of the script will be impossible.
                                                                 Chapter 16: Restore and Recover with RMAN

Block Recovery
In all the restore and recover operations described so far in the chapter, the granularity
of the restore has been the file: every example has restored a complete file, and recovered
it. While this is necessary if a file has been completely destroyed (by accidental deletion,
perhaps), in many cases damage is limited to just a small range of blocks: the file
remains usable, but certain blocks will have been corrupted. In this case, the file will
remain online, and the end users may not be aware that there is a problem. They will
only find out when they attempt to read the damaged blocks: if a session hits a corrupted
block, it will return an error to the user process, and a message will be written to the alert
log. RMAN can also detect corrupted blocks, and furthermore it can repair them.

Detection of Corrupt Blocks

                                                                                                             PART III
RMAN will detect corrupt blocks as it performs backup operations. Unless instructed
otherwise, it will terminate the backup as soon as it hits a corrupt block. If you wish,
you can run RMAN backups that specify a tolerance for corrupted blocks. If this is
done, then rather than throwing an error and terminating the backup immediately
when a corruption is detected, RMAN will continue to back up the datafile but will
record the addresses of any corruptions it encounters in its repository. This example
instructs RMAN to continue a backup as long as no more than 100 corrupt blocks are
RMAN> run {
set maxcorrupt for datafile 7 to 100;
backup datafile 7;}

     The details of corrupt blocks are visible in two places. The view V$DATABASE_
BLOCK_CORRUPTION shows the address of the cause of the problem: the datafile
file number and block number. The address of the block in the backup is also visible in
V$BACKUP_CORRUPTION for corruptions encountered by backup set backups, or
in V$COPY_CORRUPTION if the backup were to an image copy. In normal running,
you would not use the SET MAXCORRUPT keywords. Without them, the backup will
fail and you will thus be made aware of the problem immediately. Then rerun the
backup with SET MAXCORRUPT and after completion query the views to determine
the extent of the damage.
     By default, RMAN will always check for physical corruption, known as “media
corruption” in the non-RMAN world. An example of this would be a block that Oracle
cannot process at all: an invalid checksum, or a block full of zeros. RMAN can also be
instructed to check for logical corruption, also known as “software corruption,” as
well. These checks will occur whenever a file is backed up, whether as an image copy
or into a backup set. To override the defaults,
RMAN> backup nochecksum datafile 7;
OCA/OCP Oracle Database 11g All-in-One Exam Guide
           will not check for physical corruption, but
           RMAN> backup check logical datafile 6;

           will check for logical as well as physical corruption.

           Block Media Recovery
           If RMAN has detected a block corruption, it can do Block Media Recovery, or BMR.
           BMR changes the granularity of a restore and recover operation from the datafile to
           just the damaged blocks. This has two huge advantages over file restore and recover:
           first, the file does not have to be taken offline; normal DML can continue. Second, the
           mean time to recover is much reduced, since only the damaged blocks are involved
           in the operation, not the whole file. The only downtime that will occur is if a session
           happens to hit a block that is actually damaged and has not yet been recovered.
                The BMR mechanism provides RMAN with a list of one of more blocks that need
           recovery. RMAN will extract backups of these blocks from a backup set or an image
           copy and write them to the datafile. Then RMAN will pass through the archive logs
           generated since the backup and extract redo records relevant to the restored blocks
           and apply them. The recovery will always be complete—it would be logically impossible
           to do an incomplete recovery; incomplete recovery of just one block would leave the
           database in an inconsistent state. If a session hits a corrupted block before the BMR
           process has completed, then it will still receive an ORA-01578 error, but it is quite
           possible that the BMR operation will be complete before any users are aware of the

           The BLOCK RECOVER Command
           The BLOCK RECOVER command always specifies a list of one or more blocks to be
           restored and recovered, and it optionally specifies the backup from which the restore
           should be made. For example, this command,
           RMAN> block recover datafile 7 block 5;

           instructs RMAN to restore and recover the one specified block from the most recent
           backup set or image copy of the file. The syntax would also accept a list of blocks in
           several files:
           RMAN> block recover datafile 7 block 5,6,7 datafile 9 block 21,25;

               There may be doubt regarding the integrity of the backups. In that case, you can
           instruct RMAN to restore the block(s) from a backup that is known to be good:
           RMAN> block recover datafile 7 block 5 from backupset 1093;

           will restore from the nominated backup set, which could also be specified by a tag:
           RMAN> block recover datafile 7 block 5 from tag monthly_whole;
                                                             Chapter 16: Restore and Recover with RMAN

   If the damage is more extensive, then two other options for BMR will simplify the
process. First, provided that RMAN has populated the view V$DATABASE_BLOCK_
CORRUPTION by running a backup with MAXCORRUPT set to greater than zero,
then the CORRUPTION LIST option will instruct RMAN to restore and recover every
block listed in the view. Second, to ensure that the backup(s) used for the restore are
from a time before the corruption occurred, there is the UNTIL option. For example,
RMAN> block recover corruption list until time sysdate - 7;

instructs RMAN to restore and recover every block that has been discovered to be
damaged by a previous backup operation, using only backups made at least one
week ago.

            TIP In the BMR context, the keyword UNTIL does not denote an incomplete

                                                                                                         PART III
            recovery! It means that the restore must be from a backup made before a
            particular date (or sequence number or SCN).

Two-Minute Drill
Describe the Data Recovery Advisor
    • The Automatic Diagnostic Repository (ADR) is a set of files in the
      DIAGNOSTIC_DEST directory.
    • The DRA can repair damage to datafiles and controlfile, and replace missing
      log file groups.
    • Restore and recovery of the controlfile or a critical datafile can be done only in
      mount mode.
    • Restore and recovery of a noncritical datafile can be done while the database
      is open.

Use the Data Recovery Advisor to Perform Recovery
(Control File, Redo Log File, and Datafile)
    • Failures must be listed before they can be advised upon.
    • The DRA can be accessed through the RMAN executable or with Enterprise
    • The DRA is available in all modes: in nomount mode it can repair the
      controlfile; in mount or open mode it can repair datafiles.

Perform Complete Recovery from a Critical or
Noncritical Data File Loss Using RMAN
    • Only the datafiles that make up the SYSTEM and the current undo tablespaces
      are critical. Damage to other datafiles will not cause the database to crash.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
                • Restore and complete recovery of noncritical datafiles can be carried out with
                  the database open; critical datafiles must be restored and recovered in mount
                • RMAN will recover by applying incremental backups if possible, and archive
                  log files when necessary.

           Perform Incomplete Recovery Using RMAN
                • Incomplete recovery can be accomplished only in mount mode.
                • If all copies of the current online log file group are lost, an incomplete
                  recovery will be required.
                • The complete set of datafiles must be restored.
                • Recovery will be stopped by use of the UNTIL clause, up to (but not including)
                  a nominated time, archive log sequence number, or system change number.
                • Following incomplete recovery, the database must be opened with
                  RESETLOGS to reinitialize the online log files.

           Recover Using Incrementally Updated Backups
                • An image copy of a datafile (or the whole database) can be updated by
                  applying incremental backups.
                • The incremental backup must be made with the syntax: BACKUP
                • The incremental backup can be applied with the syntax: RECOVER COPY OF

           Switch to Image Copies for Fast Recovery
                • Image copies are immediately usable; there is no need to restore them.
                • The SET NEWNAME command informs RMAN that the copy is to be used; the
                  SWITCH command updates the target database controlfile accordingly.

           Recover Using a Backup Control File
                • A backup controlfile must be used if an incomplete recovery is to a time when
                  the physical structure was different from that at present.
                • If all copies of the controlfile are lost, the controlfile can be restored, in
                  nomount mode, from an autobackup.
                • If no datafiles or online log files are damaged, complete recovery using a
                  backup controlfile is possible—but an OPEN RESETLOGS will be necessary.
                                                             Chapter 16: Restore and Recover with RMAN

Perform Block Media Recovery
   • A backup operation will fail if it encounters a corrupt block, unless SET
     MAXCORRUPT has been specified.
   • BMR can be accomplished with the database open and the datafile online.
   • Corrupted blocks detected by RMAN will be visible in the V$DATABASE_
   • The BLOCK RECOVER command can recover a nominated list of blocks, or all
     blocks listed the V$DATABASE_BLOCK_CORRUPTION view.

Self Test

                                                                                                         PART III
   1. Loss of which of these files will cause an open database to crash? (Choose all
      correct answers.)
      A. A multiplexed controlfile
      B. A multiplexed online log file
      C. A multiplexed archive log file
      D. An active undo tablespace datafile
      E. An active temporary tablespace tempfile
       F. A datafile from the SYSAUX tablespace
      G. A datafile from the SYSTEM tablespace
      H. A datafile containing critical user data
   2. You issue the command ALTER DATABASE CLEAR LOGFILE GROUP 2, and
      it fails with the message “ORA-01624: log 2 needed for crash recovery of
      instance orcl11g (thread 1).” What could be an explanation for this? (Choose
      the best answer.)
      A. Log file group 2 is active.
      B. Log file group 2 is being used for recovery.
      C. The database has not been checkpointed.
      D. The group is not multiplexed.
   3. Your database is in noarchivelog mode, and you lose a noncritical datafile.
      What can you do to minimize loss of data? (Choose the best answer.)
      A. Restore the one damaged file, and leave the rest of the database up-to-date.
      B. Restore all the datafiles, but leave the controlfile up-to-date.
      C. Restore the whole database, and clear the online redo logs.
      D. Restore the one damaged file, and apply the online redo logs.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
                4. What sequence will allow you to add a multiplexed controlfile copy? (Choose
                   the best answer.)
                    1. Adjust the CONTROL_FILES parameter.
                    2. Copy the controlfile.
                    3. Mount the database.
                    4. Open the database.
                    5. Recover the controlfile.
                    6. Recover the database.
                    7. Shut down the database.
                    A. 7, 2, 1, 3, 4 (5 and 6 not necessary)
                    B. 7, 1, 3, 2, 6, 4 (5 not necessary)
                    C. 2, 1, 5 (3, 4, 6 and 7 not necessary)
                    D. 7, 1, 6, 3, 4 (2 and 5 not necessary)
                5. These are three DRA commands:
                    ADVISE FAILURE;
                    LIST FAILURE;
                    REPAIR FAILURE;
                    In what order must they be run to fix a problem? (Choose the best answer.)
                    A. ADVISE, LIST, REPAIR
                    B. LIST, ADVISE, REPAIR
                    C. LIST, REPAIR (ADVISE is not necessary)
                    D. ADVISE, REPAIR (LIST is not necessary)
                6. On what type or state of database can the DRA not be used? (Choose all
                   correct answers.)
                    A. A single-instance database that is shut down
                    B. A single-instance database in nomount mode
                    C. A single-instance database in mount mode
                    D. An open RAC database
                    E. A mounted standby database
                7. Where is the Automatic Diagnostic Repository stored? (Choose the best answer.)
                    A. In the Automatic Workload Repository
                    B. In the SYSAUX tablespace
                    C. In the data dictionary
                    D. In operating system files
                    E. In the Enterprise Manager repository
                                                         Chapter 16: Restore and Recover with RMAN

 8. If you issue the LIST FAILURE command with the DRA and then another
    failure occurs, when you run ADVISE FAILURE for what will you receive
    advice? (Choose the best answer.)
    A. For the original failures only
    B. For the new failure only
    C. For all the failures
    D. For none of the failures until you run a new LIST FAILURES
 9. Which file types can be repaired while the database is open? (Choose the best
    A. A damaged multiplexed controlfile copy
    B. A current multiplexed online log file

                                                                                                     PART III
    C. A damaged noncritical datafile, if the database is in archivelog mode
    D. All of the above, if the DRA is used and server-managed backups are available
10. It is now 15:00, on Tuesday. A bad transaction was committed in your
    database at about 14:30. Investigation shows that the tables and indexes
    affected were in just two tablespaces: the rest of the database is fine. The two
    tablespaces and several others were backed up last night, but some tablespaces
    are backed up only on the weekend. Your database is in archivelog mode, with
    log switches about every 10 minutes. Which of the following statements is
    correct? (Choose the best answer.)
    A. You can do an incomplete restore and recovery to 14:29, of just the two
       tablespaces. The loss of data will be about 30 minutes of work in those
    B. You must restore the whole database from the weekend backup and
       recover to 14:29. You will lose about 30 minutes of work.
    C. You must restore the whole database and do an incomplete recovery
       canceling the application of the archive log that was active at 14:30. You
       will lose about 10 minutes of work.
    D. You can restore some tablespaces from last night, the others from the
       weekend, and recover to 14:29. You will lose about 30 minutes of work.
11. Under which of the following circumstances is an incomplete recovery
    necessary? (Choose two answers.)
    A. You lose all copies of your current online log file group.
    B. You lose a critical tablespace: SYSTEM, and/or the currently active UNDO
    C. A user makes a bad transaction, and the instance crashes before he can
       issue the ROLLBACK statement.
    D. A datafile is created, used, and destroyed before it gets backed up.
    E. You back up a tablespace, drop it, and then want to get to the objects that
       were in it.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
               12. To do an incomplete recovery, what mode must the database be in? (Choose
                   the best answer.)
                    A. Incomplete recovery can be done only with the database SHUTDOWN.
                    B. Incomplete recovery can be done only in NOMOUNT mode.
                    C. Incomplete recovery can be done only in MOUNT mode.
                    D. Incomplete recovery can be in OPEN mode, if the database is in
                       archivelog mode.
                    E. SQL*Plus can do incomplete recovery only in CLOSED mode; RMAN can
                       do it in any mode.
               13. When using RMAN to restore a controlfile autobackup, what piece of
                   information might you need to supply? (Choose the best answer.)
                    A. The database name
                    B. The approximate time of the latest backup
                    C. The database ID
                    D. The instance name
                    E. The instance number
               14. You are using RMAN to perform incomplete recovery. Which of the following
                   is the best sequence to follow? (Choose the best answer.)
                    A. shutdown abort / startup mount / restore / recover / open resetlogs
                    B. shutdown immediate / startup mount / restore / open resetlogs / recover
                    C. shutdown immediate / restore / recover / open resetlogs
                    D. shutdown immediate / startup nomount / restore / recover / open resetlogs
               15. After a RESETLOGS, what will have changed? (Choose all that apply.)
                    A. There will be a new database incarnation number.
                    B. The system change number will be reset.
                    C. The log switch sequence number will be reset.
                    D. The database ID will be changed.
                    E. The instance number will be changed.
                     F. All previous backups and archivelogs will be invalid.
               16. Which of the following statements are correct about block media recovery
                   (BMR)? (Choose two answers.)
                    A. BMR can be performed only with RMAN.
                    B. BMR can be performed only with SQL*Plus.
                    C. Both RMAN and SQL*Plus can be used for BMR.
                    D. BMR is always a complete recovery.
                    E. BMR is always an incomplete recovery.
                     F. BMR can be either complete or incomplete; the DBA decides.
                                                           Chapter 16: Restore and Recover with RMAN

  17. If, during an RMAN backup, a corrupt block is encountered, what will
      happen? (Choose the best answer.)
      A. The backup will fail.
      B. The backup will succeed.
      C. It depends on the MAXCORRUPT setting.
      D. If the corruption is in the SYSTEM tablespace, the backup will fail;
         otherwise, it will continue, but the address of the corrupt block will
         be written to the RMAN repository.
  18. To what file types is BMR applicable? (Choose the best answer.)
      A. Archive log files
      B. Controlfiles

                                                                                                       PART III
      C. Datafiles
      D. Online log files
      E. Tempfiles
       F. All of the above
  19. What will be the effect of issuing this command:
     blockrecover corruption list until time sysdate - 7;

      (Choose the best answer.)
      A. The recovery will be up to but not including the system change number of
         the time specified.
      B. The recovery will be up to and including the system change number of the
         time specified.
      C. The recovery will be complete, but the restore will be from before the time
      D. The recovery will be of all blocks entered onto the corruption list before
         the time specified.
      E. The recovery will be of all blocks entered onto the corruption list after the
         time specified.

Self Test Answers
   1. þ A, D, and G. Damage to any controlfile copy will terminate the instance,
      as will damage to the critical tablespaces: SYSTEM and the current undo
      ý B, C, E, F, and H. All these are noncritical files, damage to which will not
      cause the instance to terminate.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
                2. þ A. An active log file group contains change vectors referring to blocks
                   that have not yet been written to the datafiles by the DBWn, and cannot be
                    ý B, C, and D. B is wrong because you cannot issue a CLEAR command
                    while a recovery is progress, and D because multiplexing is not relevant to
                    this. C is relevant because following a checkpoint the group would no longer
                    be active, but this is not the best answer.
                3. þ C. This is the only option in noarchivelog mode.
                    ý A, B, and D. These are all attempts at a partial restore, which is not
                    possible in noarchivelog mode.
                4. þ A. This is a sequence that will work.
                    ý B, C, and D. None of these sequences is feasible.
                5. þ B. This is a sequence that will work.
                    ý A, C, and D. Advice will be generated only for problems discovered by
                    a previous List command, and repair scripts will be generated by the Advise
                6. þ A, D, and E. The DRA cannot run against a database that is shut down,
                   nor against any RAC or Data Guard standby database.
                    ý B and C. The DRA can run against a single instance database that is in
                    nomount, mount, or open mode.
                7. þ D. The repository exists as operating system files in the location specified
                   by the DIAGNOSTIC_DEST instance parameter.
                    ý A, B, C, and E. The repository is not stored in any database tables, nor by
                    Enterprise Manager.
                8. þ A. The ADVISE FAILURE will refer only to failures previously listed.
                    ý B, C, and D. ADVISE FAILURE can run only following LIST FAILURE in
                    the same session, and will refer to the information gathered at that time.
                9. þ C. Noncritical datafiles can be restored and recovered while the database
                   is open, if it is running in archivelog mode.
                    ý A, B, and D. Damage to any controlfile copy will cause the instance to
                    terminate, and it cannot be mounted or opened until the damage is repaired.
                    A current multiplexed online log file cannot be repaired, though it can be
                    cleared when no longer current or active. The DRA cannot repair anything—it
                    can only advise.
                                                        Chapter 16: Restore and Recover with RMAN

10. þ D. An incomplete recovery is required, and the restore can be from the
    most recent backup of each datafile.
   ý A, B, and C. A is wrong because an incomplete recovery can follow
   only a restore of the complete set of datafiles (though in this case, a TSPITR
   might also have been an option). B is wrong because while it will work, it
   is less efficient than A: the recovery would take longer because more redo
   would have to be applied. C is wrong because there is no way to cancel the
   application of one archive log file, and then continue.
11. þ A and E. Loss of all copies of the current log file group necessitates an
    incomplete recovery, as does the reversion of the database to a time when a
    dropped tablespace still existed.
   ý B, C, and D. The SYSTEM and UNDO tablespaces can be completely

                                                                                                    PART III
   recovered, even though they are critical to the running of the database. An
   uncommitted transaction can never be recovered—it will always be rolled back.
12. þ C. Incomplete recovery can be accomplished only in MOUNT mode.
   ý A, B, D, and E. MOUNT is necessary, whether you are using RMAN or
13. þ C. The DBID will be needed to allow RMAN to identify an autobackup of
    that particular database.
   ý A, B, D, and E. The database name will be read from the controlfile,
   as will details of the last backup. The instance name and number are not
14. þ A. This is the correct sequence. An IMMEDIATE shutdown would be
    acceptable but would take longer.
   ý B, C, and D. These sequences are not possible.
15. þ A and C. A RESETLOGS generates a new database incarnation number
    and returns the log sequence number to zero.
   ý B, D, E, and F. The SCN continues to increment following a RESET
   LOGS, and the DBID remains the same. The instance number is not relevant.
   Previous backups and archive logs will still be usable (though this was not the
   case with releases prior to 10g).
16. þ A and D. The BMR capability is a strong reason for using RMAN, but it
    must be complete.
   ý B, C, E, and F. SQL*Plus cannot do BMR, and an incomplete BMR is not
OCA/OCP Oracle Database 11g All-in-One Exam Guide
               17. þ C. The MAXCORRUPT setting can be used to allow a backup to skip over
                   one or more corrupted blocks.
                    ý A, B, and D. By default, a corrupt block will cause a backup to fail, but
                    this behavior can be changed. It makes no difference in which tablespace the
                    corrupt block is found.
               18. þ C. Only datafile blocks can be recovered with BMR.
                    ý A, B, D, E, and F. Neither log files (of any kind) nor tempfiles can be
                    recovered. BMR cannot be applied to the controlfile: it must be restored and
                    recovered as a whole.
               19. þ C. The UNTIL clause in this context determines the latest backup that may
                   be restored.
                    ý A, B, D, and E. A and B are wrong because BMR can be only complete.
                    D and E are wrong because the entire corruption list is always restored and
                    recovered by this command.

Shared By: