How the Process Works

When you issue the RESTORE command, RMAN automatically decides how to extract the data files from any of the following available backups:

• Full database backup
• Incremental level-0 backup
• Image copy backup generated by BACKUP AS COPY command

After the files are restored from a backup, you are required to apply redo to them via the RECOVER command. When you issue the RECOVER command, Oracle examines the SCNs in the affected data files and determines whether any of them need to be recovered. If the SCN in the data file is less than the corresponding SCN in the control file, then media recovery will be required.

Oracle retrieves the data file SCN and then looks for the corresponding SCN in the redo stream to establish where to start the recovery process. If the starting recovery SCN is in the online redo log files, the archived redo log files are not required for recovery.

During a recovery, RMAN automatically determines how to apply redo. First, RMAN applies any incremental backups available that are greater than level 0, such as the incremental level 1. Next, any archived redo log files on disk are applied. If the archived redo log files do not exist on disk, RMAN attempts to retrieve them from a backup set.

To be able to perform a complete recovery, all the following conditions need to be true:

• Your database is in archivelog mode.
• You have a good baseline backup of your database.
• You have any required redo that has been generated since the backup (archived redo log files, online redo log files, or incremental backups that RMAN can use for recovery instead of applying redo).

There are a wide variety of restore-and-recovery scenarios. How you restore and recover depends directly on your backup strategy and which files have been damaged. Listed next are the general steps to follow when facing a media failure:

1. Determine which files need to be restored.
2. Depending on the damage, set your database mode to nomount, mount, or open.
3. Use the RESTORE command to retrieve files from RMAN backups.
4. Use the RECOVER command for data files requiring recovery.
5. Open your database.

Your particular restore-and-recovery scenario may not require that all the previous steps be performed. For instance, you may just want to restore your spfile, which doesn’t require a recovery step.

The first step in the restore-and-recovery process is to determine which files have experienced media failure. You can usually determine which files need to be restored from the following sources:

• Error messages displayed on your screen, either from RMAN or SQL*Plus
• Alert.log file and corresponding trace files
• Data dictionary views

If you’re using Oracle Database 11g or higher, then in addition to the previously listed methods, you should consider the Data Recovery Advisor for obtaining information about the extent of a failure and corresponding corrective action.

Using Data Recovery Advisor

The Data Recovery Advisor tool was introduced in Oracle Database 11g. In the event of a media failure, this tool will display the details of the failure, recommend corrective actions, and perform the recommended actions if you specify that it do so. It’s like having another set of eyes to provide feedback when in a restore-and-recovery situation. There are four modes to Data Recovery Advisor:

• Listing failures
• Suggesting corrective action
• Running commands to repair failures
• Changing the status of a failure

The Data Recovery Advisor is invoked from RMAN. You can think of the Data Recovery Advisor as a set of RMAN commands that can assist you when dealing with media failure.

RMAN> list failure;

List of Database Failures
=========================
  
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
6222       CRITICAL OPEN      12-JAN-13     System datafile 1:
 '/u01/dbfile/o12c/system01.dbf' is missing

RMAN> list failure 6222 detail;

Impact: Database cannot be opened

RMAN> advise failure 6222;

Optional Manual Actions
=======================
1. If file /u01/dbfile/o12c/system01.dbf was unintentionally renamed or moved,
restore it
  
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 1
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /ora01/app/oracle/diag/rdbms/o12c/o12c/hm/reco_4116328280.hm

$ cat /ora01/app/oracle/diag/rdbms/o12c/o12c/hm/reco_4116328280.hm

# restore and recover datafile
restore ( datafile 1 );
recover datafile 1;
sql 'alter database datafile 1 online';

RMAN> repair failure preview;

RMAN> repair failure;

Do you really want to execute the above repair (enter YES or NO)?

YES

repair failure complete

RMAN> list failure;

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
5          HIGH     OPEN      12-JAN-13     One or more non-system datafiles
                                            are missing

RMAN> change failure 5 priority low;

Do you really want to change the above failures (enter YES or NO)?

RMAN> list failure low;

Shutting Down

The S HUTDOWN command works the same from RMAN as it does from SQL*Plus. There are four types of shutdown: ABORT, IMMEDIATE, NORMAL, and TRANSACTIONAL. I usually first attempt to stop a database using S HUTDOWN IMMEDIATE; if that doesn’t work, don’t hesitate to use S HUTDOWN ABORT. Here are some examples:

RMAN> shutdown immediate;
RMAN> shutdown abort;

If you don’t specify a shutdown option, NORMAL is the default. Shutting down a database with NORMAL is rarely viable, as this mode waits for currently connected users to disconnect at their leisure. I never use NORMAL when shutting down a database.

Starting Up

As with SQL*Plus, you can use a combination of S TARTUP and A LTER DATABASE commands with RMAN to step the database through startup phases, like this:

RMAN> startup nomount;
RMAN> alter database mount;
RMAN> alter database open;

Here is another example:

RMAN> startup mount;
RMAN> alter database open;

If you want to start the database with restricted access, use the DBA option:

RMAN> startup dba;

Testing Restore and Recovery

You can determine which files RMAN will use for restore and recovery before you actually perform the restore and recovery. You can also instruct RMAN to verify the integrity of the backup files that will be used for the restore and recovery.

RMAN> restore database preview;

RMAN> restore database preview summary;

List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
224     Full    775.37M    DISK        00:02:22     12-JAN-13
        BP Key: 229   Status: AVAILABLE  Compressed: NO  Tag: TAG20130112T120713
        Piece Name: /u02/O12C/rman/r29gnv7q7i_1_1.bk
  List of Datafiles in backup set 224
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 4586940    12-JAN-13 /u01/dbfile/o12c/system01.dbf
  3       Full 4586940    12-JAN-13 /u01/dbfile/o12c/undotbs01.dbf
  4       Full 4586940    12-JAN-13 /u01/dbfile/o12c/users01.dbf

RMAN> restore tablespace system preview;
RMAN> restore archivelog from time 'sysdate -1' preview;
RMAN> restore datafile 1, 2, 3 preview;

RMAN> restore database validate header;

RMAN> restore database validate;

RMAN> restore database validate check logical;

RMAN> restore datafile 1,2,3 validate;
RMAN> restore archivelog all validate;
RMAN> restore controlfile validate;
RMAN> restore tablespace system validate;

RMAN> connect target /
RMAN> startup mount;
RMAN> restore tablespace users;
RMAN> recover tablespace users test;

RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 6 ...

ORA-10574: Test recovery did not corrupt any data block
ORA-10573: Test recovery tested redo from change 4586939 to 4588462
ORA-10572: Test recovery canceled due to errors
ORA-10585: Test recovery can not apply redo that may modify control file

RMAN> recover database test;
RMAN> recover tablespace users, tools test;
RMAN> recover datafile 1,2,3 test;

Restoring and Recovering the Entire Database

The RESTORE DATABASE command will restore every data file in your database. The exception to this is when RMAN detects that data files have already been restored; in that case, it will not restore them again. If you want to override that behavior, use the FORCE command.

When you issue the R ECOVER DATABASE command, RMAN will automatically apply redo to any data files that need recovery. The recovery process includes applying changes found in the following files:

• Incremental backup pieces (applicable only if using incremental backups)
• Archived redo log files (generated since the last backup or incremental backup applied)
• Online redo log files (current and unarchived)

You can open your database after the restore-and-recovery process is complete. Complete database recovery works only if you have good backups of your database and access to all redo generated after the backup was taken. You need all the redo required to recover the database data files. If you don’t have all the required redo, then you’ll most likely have to perform an incomplete recovery (see the section “Incomplete Recovery, ” later in this chapter).

You can perform a complete database-level recovery with either the current control file or a backup control file.

$ rman target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Statement processed

$ rman target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

Statement processed

Restoring and Recovering Tablespaces

Sometimes you’ll have a media failure that’s localized to a particular tablespace or set of tablespaces. In this situation, it’s appropriate to restore and recover at the tablespace level of granularity. The RMAN RESTORE TABLESPACE and RECOVER TABLESPACE commands will restore and recover all data files associated with the specified tablespace(s).

$ rman target /
RMAN> sql 'alter tablespace users offline immediate';
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';

sql statement: alter tablespace users online

$ rman target /
RMAN> alter tablespace users offline immediate;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter tablespace users online;

$ rman target /
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> restore tablespace system;
RMAN> recover tablespace system;
RMAN> alter database open;

Statement processed

Restoring Read-Only Tablespaces

RMAN will restore read-only tablespaces along with the rest of the database when you issue a RESTORE DATABASE command. For example, the following command will restore all data files (including those in read-only mode):

RMAN> restore database;

Prior to Oracle Database 11g, you were required to issue RESTORE DATABASE CHECK READONLY to instruct RMAN to restore read-only tablespaces along with tablespaces in read-write mode. This is no longer a requirement in Oracle Database 11g and higher.

Restoring Temporary Tablespaces

Starting with Oracle Database 10g, you don’t have to restore or re-create missing locally managed temporary tablespace temp files. When you open your database for use, Oracle automatically detects and re-creates locally managed temporary tablespace temp files.

When Oracle automatically re-creates a temporary tablespace, it will log a message to your target database alert.log  such as this:

Re-creating tempfile <your temporary tablespace filename>

If, for any reason, your temporary tablespace becomes unavailable, you can also re-create it yourself. Because there are never any permanent objects in temporary tablespaces, you can simply re-create them as needed. Here is an example of how to create a locally managed temporary tablespace:

CREATE TEMPORARY TABLESPACE temp TEMPFILE
'/u01/dbfile/o12c/temp01.dbf' SIZE 1000M
EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 512K;

If your temporary tablespace exists, but the temporary data files are missing, you can just add them, as shown:

alter tablespace temp
add tempfile '/u01/dbfile/o12c/temp02.dbf' SIZE 5000M REUSE;

Restoring and Recovering Data Files

A data file–level restore and recovery works well when a media failure is confined to a small set of data files. With data file–level recoveries, you can instruct RMAN to restore and recover either with data file name or data file number. For data files not associated with the SYSTEM or UNDO tablespaces, you have the option of restoring and recovering while the database remains open. While the database is open, however, you must first take offline any data files being restored and recovered.

RMAN> sql 'alter database datafile 4, 5 offline';
RMAN> restore datafile 4, 5;
RMAN> recover datafile 4, 5;
RMAN> sql 'alter database datafile 4, 5 online';

RMAN> sql "alter database datafile ''/u01/dbfile/o12c/users01.dbf'' offline";
RMAN> restore datafile '/u01/dbfile/o12c/users01.dbf';
RMAN> recover datafile '/u01/dbfile/o12c/users01.dbf';
RMAN> sql "alter database datafile ''/u01/dbfile/o12c/users01.dbf'' online";

RMAN> alter database datafile 4 offline;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
RMAN> alter database datafile 4 online;

RMAN> alter database datafile '/u01/dbfile/o12c/users01.dbf' offline;
RMAN> restore datafile '/u01/dbfile/o12c/users01.dbf';
RMAN> recover datafile '/u01/dbfile/o12c/users01.dbf';
RMAN> alter database datafile '/u01/dbfile/o12c/users01.dbf' online;

$ rman target /
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> alter database open;

$ rman target /
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile '/u01/dbfile/o12c/system01.dbf';
RMAN> recover datafile '/u01/dbfile/o12c/system01.dbf';
RMAN> alter database open;

Restoring Data Files to Nondefault Locations

Sometimes a failure will occur that renders the disks associated with a mount point inoperable. In these situations, you will need to restore and recover the data files to a location different from the one where they originally resided. Another typical need for restoring data files to nondefault locations is that you’re restoring to a different database server, on which the mount points are completely different from those of the server on which the backup originated.

Use the SET NEWNAME and SWITCH commands to restore data files to nondefault locations. Both of these commands must be run from within an RMAN run{} block. You can think of using SET NEWNAME and SWITCH as a way to rename data files (similar to the SQL*Plus ALTER DATABASE RENAME FILE statement).

This example changes the location of data files when doing a restore and recover. First, place the database in mount mode:

$ rman target /
RMAN> startup mount;

Then, run the following block of RMAN code:

run{
set newname for datafile 4 to '/u02/dbfile/o12c/users01.dbf';
set newname for datafile 5 to '/u02/dbfile/o12c/users02.dbf';
restore datafile 4, 5;
switch datafile all; # Updates repository with new datafile location.
recover datafile 4, 5;
alter database open;
}

This is a partial listing of the output:

datafile 4 switched to datafile copy
input datafile copy RECID=79 STAMP=804533148 file name=/u02/dbfile/o12c/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=80 STAMP=804533148 file name=/u02/dbfile/o12c/users02.dbf

If the database is open, you can place the data files offline and then set their new names for restore and recovery, as follows:

run{
sql 'alter database datafile 4, 5 offline';
set newname for datafile 4 to '/u02/dbfile/o12c/users01.dbf';
set newname for datafile 5 to '/u02/dbfile/o12c/users02.dbf';
restore datafile 4, 5;
switch datafile all; # Updates repository with new datafile location.
recover datafile 4, 5;
sql 'alter database datafile 4, 5 online';
}

Starting with Oracle Database 12c, you no longer need to specify the RMAN sql command when running SQL statements, such as ALTER DATABASE; for example,

run{
alter database datafile 4, 5 offline;
set newname for datafile 4 to '/u02/dbfile/o12c/users01.dbf';
set newname for datafile 5 to '/u02/dbfile/o12c/users02.dbf';
restore datafile 4, 5;
switch datafile all; # Updates repository with new datafile location.
recover datafile 4, 5;
alter database datafile 4, 5 online;
}

Performing Block-Level Recovery

Block-level corruption is rare and is usually caused by some sort of I/O error. However, if you do have an isolated corrupt block within a large data file, it’s nice to have the option of performing a block-level recovery. Block-level recovery is useful when a small number of blocks are corrupt within a data file. Block recovery is not appropriate if the entire data file needs media recovery.

RMAN will automatically detect corrupt blocks whenever a B ACKUP, V ALIDATE, or B ACKUP VALIDATE command is run. Details on corrupt blocks can be viewed in the V$DATABASE_BLOCK_CORRUPTION view. In the following example the regular backup job has reported a corrupt block in the output:

ORA-19566: exceeded limit of 0 corrupt blocks for file...

Querying the V$DATABASE_BLOCK_CORRUPTION view indicates which file contains corruption:

SQL> select * from v$database_block_corruption;
  
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         4         20          1                  0 ALL ZERO           0

Your database can be either mounted or open when performing block-level recovery. You do not have to take offline the data file being recovered. You can instruct RMAN to recover all blocks reported in V $DATABASE_BLOCK_CORRUPTION, as shown:

RMAN> recover corruption list;

If successful, the following message is displayed:

media recovery complete...

Another way to recover the block is to specify the data file and block number, like so:

RMAN> recover datafile 4 block 20;

It’s preferable to use the RECOVER CORRUPTION LIST syntax because it will clear out any blocks recovered from the V $DATABASE_BLOCK_CORRUPTION view.

Block-level media recovery allows you to keep your database available and also reduces the mean time to recovery, as only the corrupt blocks are offline during the recovery. Your database must be in archivelog mode for performing block-level recoveries. Starting with Oracle Database 11g, RMAN can restore the block from the flashback logs (if available). If the flashback logs are not available, then RMAN will attempt to restore the block from a full backup, a level-0 backup, or an image copy backup generated by the B ACKUP AS COPY command. After the block has been restored, any required archived redo logs must be available to recover the block. RMAN can’t perform block media recovery using incremental level-1 (or higher) backups.

Restoring a Container Database and Its Associated Pluggable Databases

Starting with Oracle Database 12c, you can create pluggable databases within one container database (see Chapter 23 for details). When dealing with container and associated pluggable databases, there are three basic scenarios:

• All data files have experienced media failure (container root data files as well as all associated pluggable database data files).
• Just the data files associated with the container root database have experienced media failure.
• Only data files associated with a pluggable database have experienced media failure.

The prior scenarios are covered in the following sections.

$ rman target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

RMAN> alter pluggable database all open;

$ rman target /
RMAN> startup mount;
RMAN> restore database root;
RMAN> recover database root;
RMAN> alter database open;

RMAN> alter pluggable database all open;

SQL> select name, open_mode from v$pdbs;

$ rman target /
RMAN> alter pluggable database salespdb close;
RMAN> restore pluggable database salespdb;
RMAN> recover pluggable database salespdb;
RMAN> alter pluggable database salespdb open;

$ rman target sys/foo@salespdb
RMAN> shutdown immediate;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Restoring to the Default Location

The following command will restore all archived redo log files that RMAN has backed up:

RMAN> restore archivelog all;

If you want to restore from a specified sequence, use the FROM SEQUENCE clause. You may want to run this query first to establish the most recent log files and sequence numbers that have been generated:

SQL> select sequence#, first_time from v$log_history order by 2;

This example restores all archived redo log files from sequence 68:

RMAN> restore archivelog from sequence 68;

If you want to restore a range of archived redo log files, use the FROM SEQUENCE and UNTIL SEQUENCE clauses or the SEQUENCE BETWEEN clause, as shown. The following commands restore archived redo log files from sequence 68 through sequence 78, using thread 1:

RMAN> restore archivelog from sequence 68 until sequence 78 thread 1;
RMAN> restore archivelog sequence between 68 and 78 thread 1;

By default, RMAN won’t restore an archived redo log file if it is already on disk. You can override this behavior if you use the FORCE, like so:

RMAN> restore archivelog from sequence 1 force;

Restoring to a Nondefault Location

Use the SET ARCHIVELOG DESTINATION clause if you want to restore archived redo log files to a location different from the default. The following example restores to the nondefault location u01/archtemp. The option of the SET command must be executed from within an RMAN run{} block.

run{
set archivelog destination to '/u01/archtemp';
restore archivelog from sequence 8 force;
}

Using a Recovery Catalog

When you’re connected to the recovery catalog, you can view backup information about your control files even while your target database is in nomount mode. To list backups of your control files, use the LIST command, as shown:

$ rman target / catalog rcat/foo@rcat
RMAN> startup nomount;
RMAN> list backup of controlfile;

If you’re missing all your control files, and you’re using a recovery catalog, then issue the STARTUP NOMOUNT and the RESTORE CONTROLFILE commands:

RMAN> startup nomount;
RMAN> restore controlfile;

RMAN restores the control files to the location defined by your CONTROL_FILES initialization parameter. You should see a message indicating that your control files have been successfully copied back from an RMAN backup piece. You can now alter your database into mount mode and perform any additional restore and recovery commands required for your database.

Using an Autobackup

When you enable the autobackup of your control file and are using an FRA, restoring your control file is fairly simple. First, connect to your target database, then issue a STARTUP NOMOUNT command, followed by the RESTORE CONTROLFILE FROM AUTOBACKUP command, like this:

$ rman target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;

RMAN restores the control files to the location defined by your CONTROL_FILES initialization parameter. You should see a message indicating that your control files have been successfully copied back from an RMAN backup piece. Here is a snippet of the output:

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

You can now alter your database into mount mode and perform any additional restore and recovery commands required for your database.

Specifying a Backup File Name

When restoring a database to a different server, these are generally the first few steps in the process take a backup of the target database, copy to the remote server, and then restore the control file from the RMAN backup. In these scenarios, I usually know the name of the backup piece that contains the control file. Here is an example in which you instruct RMAN to restore a control file from a specific backup piece file:

RMAN> startup nomount;
RMAN> restore controlfile from
'/u01/O12C/rman/rman_ctl_c-3423216220-20130113-01.bk';

The control file will be restored to the location defined by the CONTROL_FILES initialization parameter.

Determining the Type of Incomplete Recovery

Time-based restore and recovery is commonly used when you know the approximate date and time to which you want to recover your database. For instance, you may know approximately the time you want to stop the recovery process, but not a particular SCN.

Log sequence–based and cancel-based recovery work well in situations in which you have missing or damaged log files. In such scenarios, you can recover only up to your last good archived redo log file.

SCN-based recovery works well if you can pinpoint the SCN at which you want to stop the recovery process. You can retrieve SCN information from views such as V$LOG and V$LOG_HISTORY. You can also use tools such as LogMiner to retrieve the SCN of a particular SQL statement.

Restore point recoveries work only if you have established restore points. In these situations, you restore and recover up to the SCN associated with the specified restore point.

TSPITR is used in situations in which you need to restore and recover just a few tablespaces. You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.

Performing Time-Based Recovery

To restore and recover your database back to a point in time in the past, you can use either the UNTIL TIME clause of the RESTORE and RECOVER commands or the SET UNTIL TIME clause within a run{} block. RMAN will restore and recover the database up to, but not including, the specified time. In other words, RMAN will restore any transactions committed prior to the time specified. RMAN automatically stops the recovery process when it reaches the time you specified.

The default date format that RMAN expects is YYYY-MM-DD:HH24:MI:SS. However, I recommend using the TO_DATE function and specifying a format mask. This eliminates ambiguities with different national date formats and having to set the OS NLS_DATE_FORMAT variable. The following example specifies a time when issuing the restore and recover commands:

$ rman target /
RMAN> startup mount;
RMAN> restore database until time
      "to_date('15-jan-2013 12:20:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
      "to_date('15-jan-2013 12:20:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open resetlogs;

If everything goes well, you should see output such as this:

Statement processed

Performing Log Sequence-Based Recovery

Usually this type of incomplete database recovery is initiated because you have a missing or damaged archived redo log file. If that’s the case, you can recover only up to your last good archived redo log file, because you can’t skip a missing archived redo log.

How you determine which archived redo log file to restore up to (but not including) will vary. For example, if you are physically missing an archived redo log file, and RMAN can’t find it in a backup set, you’ll receive a message such as this when trying to apply the missing file:

RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 19...

Based on the previous error message, you would restore up to (but not including) log sequence 19.

$ rman target /
RMAN> startup mount;
RMAN> restore database until sequence 19;
RMAN> recover database until sequence 19;
RMAN> alter database open resetlogs;

If successful, you should see output such as this:

Statement processed

Performing SCN-Based Recovery

SCN-based incomplete database recovery works in situations in which you know the SCN value at which you want to end the restore-and-recovery session. RMAN will recover up to, but not including, the specified SCN. RMAN automatically terminates the restore process when it reaches the specified SCN.

You can view your database SCN information in several ways:

• Using LogMiner to determine an SCN associated with a DDL or DML statement
• Looking in the alert.log file
• Looking in your trace files
• Querying the FIRST_CHANGE# column of V$LOG, V$LOG_HISTORY and V$ARCHIVED_LOG

After establishing the SCN to which you want to restore, use the U NTIL SCN clause to restore up to, but not including, the SCN specified. The following example restores all transactions that have an SCN that is less than 95019865425:

$ rman target /
RMAN> startup mount;
RMAN> restore database until scn 95019865425;
RMAN> recover database until scn 95019865425;
RMAN> alter database open resetlogs;

If everything goes well, you should see output such as this:

Statement processed

Restoring to a Restore Point

There are two types of restore points: normal and guaranteed. The main difference between a guaranteed restore point and a normal restore point is that a guaranteed restore point is not eventually aged out of the control file; a guaranteed restore point will persist until you drop it. Guaranteed restore points do require an FRA. However, for incomplete recovery using a guaranteed restore point, you do not have to have flashback database enabled.

You can create a normal restore point using SQL*Plus, as follows:

SQL> create restore point MY_RP;

This command creates a restore point, named MY_RP, that is associated with the SCN of the database at the time the command was issued. You can view the current SCN of your database, as shown:

SQL> select current_scn from v$database;

You can view restore point information in the V$RESTORE_POINT view, like so:

SQL> select name, scn from v$restore_point;

The restore point acts like a synonym for the particular SCN. The restore point allows you to restore and recover to an SCN without having to specify a number. RMAN will restore and recover up to, but not including, the SCN associated with the restore point.

This example restores and recovers to the MY_RP restore point:

$ rman target /
RMAN> startup mount;
RMAN> restore database until restore point MY_RP;
RMAN> recover database until restore point MY_RP;
RMAN> alter database open resetlogs;

Restoring Tables to a Previous Point

Starting with Oracle Database 12c, you can restore individual tables from RMAN backups via the RECOVER TABLE command. This gives you with the ability to restore and recover a table back to a point in time in the past.

The table-level restore feature uses a temporary auxiliary instance and the Data Pump utility. Both the auxiliary instance and Data Pump create temporary files when restoring the table. Before initiating a table-level restore, first create two directories: one to hold files used by the auxiliary instance and one to store a Data Pump dump file:

$ mkdir /tmp/oracle
$ mkdir /tmp/recover

The prior two directories are referenced within the RECOVER TABLE command via the AUXILIARY DESTINATION and DATAPUMP DESTINATION clauses. In the following bit of code, the INV table, owned by MV_MAINT, is restored as it was at a prior SCN:

recover table mv_maint.inv
until scn 4689805
auxiliary destination '/tmp/oracle'
datapump destination '/tmp/recover';

Providing that RMAN backups are available that contain the state of the table at the specified SCN, a table-level restore and recovery is performed.

When RMAN performs a table-level recovery, it automatically creates a temporary auxiliary database, uses Data Pump to export the table, and then imports the table back into the target database as it was at the specified restore point. After the restore is finished, the auxiliary database is dropped, and Data Pump dump file is removed.

FLASHBACK TABLE TO BEFORE DROP

When you drop a table, if you don’t specify the PURGE clause, Oracle renames the table with a system-generated name. Because the table isn’t really dropped, you can use FLASHBACK TABLE TO BEFORE DROP to instruct Oracle to rename the table with its original name. Here is an example. Suppose the INV table is accidentally dropped:

SQL> drop table inv;

Verify that the table has been renamed by viewing the contents of the recycle bin:

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
INV              BIN$0zIqhEFlcprgQ4TQTwq2uA==$0 TABLE        2013-01-11:12:16:49

The SHOW RECYCLEBIN statement shows only tables that have been dropped. To get a more complete picture of renamed objects, query the RECYCLEBIN view:

select object_name, original_name, type
from recyclebin;

Here is the output:

OBJECT_NAME                         ORIGINAL_NAM TYPE
----------------------------------- ------------ -------------------------
BIN$0zIqhEFjcprgQ4TQTwq2uA==$0      INV_PK       INDEX
BIN$0zIqhEFkcprgQ4TQTwq2uA==$0      INV_TRIG     TRIGGER
BIN$0zIqhEFlcprgQ4TQTwq2uA==$0      INV          TABLE

In this output the table also has a primary key that was renamed when the object was dropped. To undrop the table, do this:

SQL> flashback table inv to before drop;

The prior command restores the table to its original name. This statement, however, doesn’t restore the index to its original name:

SQL> select index_name from user_indexes where table_name='INV';
  
INDEX_NAME
-------------------------------------------------
BIN$0zIqhEFjcprgQ4TQTwq2uA==$0

In this scenario, you have to rename the index:

SQL> alter index "BIN$0zIqhEFjcprgQ4TQTwq2uA==$0" rename to inv_pk;

You also have to rename any trigger objects in the same manner. If referential constraints were in place before the table was dropped, you must manually re-create them.

If, for some reason, you need to flash back a table to a name different from the original name, you can do so as follows:

SQL> flashback table inv to before drop rename to inv_bef;

Flashing Back a Table to a Previous Point in Time

If a table was erroneously deleted from, you have the option of flashing back the table to a previous point in time. The Flashback Table feature uses information in the undo tablespace to restore the table. The point in time in the past depends on your undo tablespace retention period, which specifies the minimum time that undo information is kept.

If the required flashback information isn’t in the undo tablespace, you receive an error such as this:

ORA-01555: snapshot too old

In other words, to be able to flash back to a point in time in the past, the required information in the undo tablespace must not have been overwritten.

SQL> select current_scn from v$database;
  
CURRENT_SCN
-----------
   4760099

SQL> alter table inv enable row movement;
SQL> flashback table inv to scn 4760089;

SQL> alter table inv enable row movement;
SQL> flashback table inv to timestamp(sysdate-1/96) ;

SQL> flashback table inv to timestamp
     to_timestamp('14-jan-13 12:07:33','dd-mon-yy hh24:mi:ss'),

SQL> create restore point point_a;

SQL> alter table inv enable row movement;
SQL> flashback table inv to restore point point_a;

Step 1. Create an RMAN Backup on the Originating Database

When backing up a database, make sure you have the autobackup control file feature turned on. Also, include the archive redo logs as part of the backup, like so:

RMAN> backup database plus archivelog;

You can verify the names and locations of the backup pieces via the LIST BACKUP command. For example, this is what the backup pieces look like for the source database:

rman1_bonvb2js_1_1.bk
rman1_bqnvb2k5_1_1.bk
rman1_bsnvb2p3_1_1.bk
rman_ctl_c-3423216220-20130113-06.bk

In the prior output the file with the c-3423216220 string in the name is the backup piece that contains the control file. You’ll have to inspect the output of your LIST BACKUP command to determine which backup piece contains the control file. You’ll need to reference that backup piece in step 8.

Step 2. Copy the RMAN Backup to the Destination Server

For this step, use a utility such as rsync or scp to copy the backup pieces from one server to another. This example uses the scp command to copy the backup pieces:

$ scp rman*  oracle@DEVBOX:/ora01/rman/DEVDB

In this example the /ora01/rman/DEVDB directory must be created on the destination server before copying the backup files. Depending on your environment, this step might require copying the RMAN backups twice: once from the production server to a secure server and once from the secure server to a test server.

Step 3. Ensure That Oracle Is Installed

Make sure you have the same version of the Oracle binaries installed on the destination server as you do on the originating database.

Step 4. Source the Required OS Variables

You need to establish the OS variables, such as ORACLE_SID, ORACLE_HOME, and PATH. Typically, the ORACLE_SID variable is initially set to match what it was on the original database. The database name will be changed as part of the last step in this recipe (optional). Here are the settings for ORACLE_SID and ORACLE_HOME on the destination server:

$ echo $ORACLE_SID
o12c
  
$ echo $ORACLE_HOME
/ora01/app/oracle/product/12.1.0.1/db_1

At this point also consider adding the Oracle SID to the oratab file. If you plan on using this database after you’ve replicated it, then you should have an automated method for setting the required OS variables. See Chapter 2 for details on sourcing OS variables in conjunction with the oratab file.

Step 5. Create an init.ora File for the Database to Be Restored

Copy the init.ora file from the original server to the destination server, and modify it so that it matches the destination box in terms of any directory paths. Ensure that you change the parameters, such as the CONTROL_FILES, to reflect the new path directories on the destination server (/ora01/dbfile/DEVDB, in this example).

Initially, the name of the init.ora file is ORACLE_HOME/dbs/inito12c.ora, and the name of the database is o12c. Both will be renamed in a later step. Here are the contents of the init.ora file:

control_files='/ora01/dbfile/DEVDB/control01.ctl',
              '/ora01/dbfile/DEVDB/control02.ctl'
db_block_size=8192
db_name='o12c'
log_archive_dest_1='location=/ora01/arc/DEVDB'
job_queue_processes=10
memory_max_target=300000000
memory_target=300000000
open_cursors=100
os_authent_prefix=''
processes=100
remote_login_passwordfile='EXCLUSIVE'
resource_limit=true
shared_pool_size=80M
sql92_security=TRUE
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
workarea_size_policy='AUTO'

Step 6. Create Any Required Directories for Data Files, Control Files, and Dump/Trace Files

For this example the directories /ora01/dbfile/DEVDB and /ora01/oraredo/DEVDB are created:

$ mkdir -p /ora01/dbfile/DEVDB
$ mkdir -p /ora01/oraredo/DEVDB
$ mkdir -p /ora01/arc/DEVDB

Step 7. Start Up the Database in Nomount Mode

You should now be able to start up the database in nomount mode:

$ rman target /
RMAN> startup nomount;

Step 8. Restore the Control File from the RMAN Backup

Next, restore the control file from the backup that was previously copied; for example,

RMAN> restore controlfile from
'/ora01/rman/DEVDB/rman_ctl_c-3423216220-20130113-06.bk';

The control file will be restored to all locations specified by the CONTROL_FILES initialization parameter. Here is some sample output:

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/ora01/dbfile/DEVDB/control01.ctl
output file name=/ora01/dbfile/DEVDB/control02.ctl

Step 9. Start Up the Database in Mount Mode

You should now be able to start up your database in mount mode:

RMAN> alter database mount;

At this point, your control files exist and have been opened, but none of the data files or online redo logs exist yet.

Step 10. Make the Control File Aware of the Location of the RMAN Backups

First, use the CROSSCHECK command to let the control file know that none of the backups or archive redo logs are in the same location that they were in on the original server:

RMAN> crosscheck backup; # Crosscheck backups
RMAN> crosscheck copy;   # Crosscheck image copies and archive logs

Then, use the CATALOG command to make the control file aware of the location and names of the backup pieces that were copied to the destination server.

In this example any RMAN files that are in the /ora01/rman/DEVDB directory will be cataloged in the control file:

RMAN> catalog start with '/ora01/rman/DEVDB';

Here is some sample output:

List of Files Unknown to the Database
=====================================
File Name: /ora01/rman/DEVDB/rman1_bqnvb2k5_1_1.bk
File Name: /ora01/rman/DEVDB/rman1_bonvb2js_1_1.bk
File Name: /ora01/rman/DEVDB/rman_ctl_c-3423216220-20130113-06.bk
File Name: /ora01/rman/DEVDB/rman1_bsnvb2p3_1_1.bk
  
Do you really want to catalog the above files (enter YES or NO)?

Now, type YES (if everything looks okay). You should then be able to use the RMAN LIST BACKUP command to view the newly cataloged backup pieces:

RMAN> list backup;

Step 11. Rename and Restore the Data Files to Reflect New Directory Locations

If your destination server has the exact same directory structure as the original server directories, you can issue the RESTORE command directly:

RMAN> restore database;

However, when restoring data files to locations that are different from the original directories, you’ll have to use the SET NEWNAME command. Create a file that uses an RMAN run{} block that contains the appropriate SET NEWNAME and RESTORE commands. I like to use an SQL script that generates SQL to give me a starting point. Here is a sample script:

set head off feed off verify off echo off pages 0 trimspool on
set lines 132 pagesize 0
spo newname.sql
--
select 'run{' from dual;
--
select
'set newname for datafile ' || file# || ' to ' || '''' || name || '''' || ';'
from v$datafile;
--
select
'restore database;' || chr(10) ||
'switch datafile all;' || chr(10) ||
'}'
from dual;
--
spo off;

After running the script, these are the contents of the newname.sql script that was generated:

run{
set newname for datafile 1 to '/u01/dbfile/o12c/system01.dbf';
set newname for datafile 2 to '/u01/dbfile/o12c/sysaux01.dbf';
set newname for datafile 3 to '/u01/dbfile/o12c/undotbs01.dbf';
set newname for datafile 4 to '/u01/dbfile/o12c/users01.dbf';
restore database;
switch datafile all;
}

Then, modify the contents of the newname.sql script to reflect the directories on the destination database server. Here is what the final newname.sql script looks like for this example:

run{
set newname for datafile 1 to '/ora01/dbfile/DEVDB/system01.dbf';
set newname for datafile 2 to '/ora01/dbfile/DEVDB/sysaux01.dbf';
set newname for datafile 3 to '/ora01/dbfile/DEVDB/undotbs01.dbf';
set newname for datafile 4 to '/ora01/dbfile/DEVDB/users01.dbf';
restore database;
switch datafile all;
}

Now, connect to RMAN, and run the prior script to restore the data files to the new locations:

$ rman target /
RMAN> @newname.sql

Here is a snippet of the output:

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=790357985 file name=/ora01/dbfile/DEVDB/system01.dbf

All the data files have been restored to the new database server. You can use the RMAN REPORT SCHEMA command to verify that the files have been restored and are in the correct locations:

RMAN> report schema;

Here is some sample output:

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name O12C
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     /ora01/dbfile/DEVDB/system01.dbf
2    500      SYSAUX               ***     /ora01/dbfile/DEVDB/sysaux01.dbf
3    800      UNDOTBS1             ***     /ora01/dbfile/DEVDB/undotbs01.dbf
4    50       USERS                ***     /ora01/dbfile/DEVDB/users01.dbf
  
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    500      TEMP                 500         /u01/dbfile/o12c/temp01.dbf

From the prior output you can see that the database name and temporary tablespace data file still don’t reflect the destination database (DEVDB). These will be modified in subsequent steps.

Step 12. Recover the Database

Next, you need to apply any archive redo files that were generated during the backup. These should be included in the backup because the ARCHIVELOG ALL clause was used to take the backup. Initiate the application of redo via the RECOVER DATABASE command:

RMAN> recover database;

RMAN will restore and apply as many archive redo logs as it has in the backup pieces and then may throw an error when it reaches an archive redo log that doesn’t exist; for example,

RMAN-06054: media recovery requesting unknown archived log for...

That error message is fine. The recovery process will restore and recover archive redo logs contained in the backups, which should be sufficient to open the database. The recovery process doesn’t know where to stop applying archive redo logs and therefore will continue to attempt to do so until it can’t find the next log. Having said that, now is a good time to verify that your data files are online and not in a fuzzy state:

select file#, status, fuzzy, error, checkpoint_change#,
to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time
from v$datafile_header;

Step 13. Set the New Location for the Online Redo Logs

If your source and destination servers have the exact same directory structures, then you don’t need to set a new location for the online redo logs (so you can skip this step).

However, if the directory structures are different, then you’ll need to update the control file to reflect the new directory for the online redo logs. I sometimes use an SQL script that generates SQL to assist with this step:

set head off feed off verify off echo off pages 0 trimspool on
set lines 132 pagesize 0
spo renlog.sql
select
'alter database rename file ' || chr(10)
|| '''' || member || '''' || ' to ' || chr(10) || '''' || member || '''' ||';'
from v$logfile;
spo off;

For this example, here is a snippet of the renlog.sql file that was generated:

alter database rename file
'/u01/oraredo/o12c/redo01a.rdo' to
'/u01/oraredo/o12c/redo01a.rdo';
...
alter database rename file
'/u02/oraredo/o12c/redo03b.rdo' to
'/u02/oraredo/o12c/redo03b.rdo';

The contents of renlog.sql need to be modified to reflect the directory structure on the destination server. Here is what renlog.sql looks like after being edited:

alter database rename file
'/u01/oraredo/o12c/redo01a.rdo' to
'/ora01/oraredo/DEVDB/redo01a.rdo';
...
alter database rename file
'/u02/oraredo/o12c/redo03b.rdo' to
'/ora01/oraredo/DEVDB/redo03b.rdo';

Update the control file by running the renlog.sql script:

SQL> @renlog.sql

You can select from V$LOGFILE to verify that the online redo log names are correct:

SQL> select member from v$logfile;

Here is the output for this example:

/ora01/oraredo/DEVDB/redo01a.rdo
/ora01/oraredo/DEVDB/redo02a.rdo
/ora01/oraredo/DEVDB/redo03a.rdo
/ora01/oraredo/DEVDB/redo01b.rdo
/ora01/oraredo/DEVDB/redo02b.rdo
/ora01/oraredo/DEVDB/redo03b.rdo

Make sure the directories exist on the new server that will contain the online redo logs. For this example, here is the mkdir command:

$ mkdir -p /ora01/oraredo/DEVDB

Step 14. Open the Database

You must open the database with the OPEN RESETLOGS command (because there are no redo logs, and they must be re-created at this point):

SQL> alter database open resetlogs;

If successful, you should see this message:

Statement processed

Step 15. Add the Temp File

When you start your database, Oracle will automatically try to add any missing temp files to the database. Oracle won’t be able to do this if the directory structure on the destination server is different from that of the source server. In this scenario, you will have to add any missing temp files manually. To do this, first take offline the temporary tablespace temp file. The file definition from the originating database is taken offline like so:

SQL> alter database tempfile '/u01/dbfile/o12c/temp01.dbf' offline;
SQL> alter database tempfile '/u01/dbfile/o12c/temp01.dbf' drop;

Next, add a temporary tablespace file to the TEMP tablespace that matches the directory structure of the destination database server:

SQL> alter tablespace temp add tempfile '/ora01/dbfile/DEVDB/temp01.dbf'
     size 100m;

You can run the REPORT SCHEMA command to verify that all files are in the correct locations.

Step 16. Rename the Database

This step is optional. If you need to rename the database to reflect the name for a development or test database, create a trace file that contains the CREATE CONTROLFILE statement, and use it to rename your database.

The steps for renaming your database are as follows:

1. Generate a trace file that contains the SQL command to recreate the control files:

   SQL> alter database backup controlfile to trace as '/tmp/cf.sql' resetlogs;

2. Shut down the database:

   SQL> shutdown immediate;

3. Modify the /tmp/cf.sql trace file; be sure to specify SET DATABASE "<NEW DATABASE NAME>" in the top line of the output:

   CREATE CONTROLFILE REUSE SET DATABASE "DEVDB" RESETLOGS ARCHIVELOG
       MAXLOGFILES 16
       MAXLOGMEMBERS 4
       MAXDATAFILES 1024
       MAXINSTANCES 1
       MAXLOGHISTORY 876
   LOGFILE
     GROUP 1 (
       '/ora01/oraredo/DEVDB/redo01a.rdo',
       '/ora01/oraredo/DEVDB/redo01b.rdo'
     ) SIZE 50M BLOCKSIZE 512,
     GROUP 2 (
       '/ora01/oraredo/DEVDB/redo02a.rdo',
       '/ora01/oraredo/DEVDB/redo02b.rdo'
     ) SIZE 50M BLOCKSIZE 512,
     GROUP 3 (
       '/ora01/oraredo/DEVDB/redo03a.rdo',
       '/ora01/oraredo/DEVDB/redo03b.rdo'
     ) SIZE 50M BLOCKSIZE 512
   DATAFILE
     '/ora01/dbfile/DEVDB/system01.dbf',
     '/ora01/dbfile/DEVDB/sysaux01.dbf',
     '/ora01/dbfile/DEVDB/undotbs01.dbf',
     '/ora01/dbfile/DEVDB/users01.dbf'
   CHARACTER SET AL32UTF8;

   If you don’t specify SET DATABASE in the top line of the prior script, when you run the script (as shown later in this example), you’ll receive an error such as this:

   ORA-01161: database name ... in file header does not match...

4. Create an init.ora file that matches the new database name:

   $ cd $ORACLE_HOME/dbs
   $ cp init<old_sid>.ora init<new_sid>.ora
   $ cp inito12c.ora initDEVDB.ora

5. Modify the DB_NAME variable within the new init.ora file (in this example, it’s set to DEVDB):

   db_name='DEVDB'

6. Set the ORACLE_SID OS variable to reflect the new SID name (in this example, it’s set to DEVDB):

   $ echo $ORACLE_SID
   DEVDB

7. Start up the instance in nomount mode:

   SQL> startup nomount;

8. Run the trace file (from step 2) to re-create the control file:

   SQL> @/tmp/cf.sql

    Note   In this example the control files already exist in the location specified by the CONTROL_FILES initialization parameter; therefore, the REUSE parameter is used in the CREATE CONTROL FILE statement.

9. Open the database with OPEN RESETLOGS:

   SQL> alter database open resetlogs;

   If successful, you should have a database that is a copy of the original database. All the data files, control files, archive redo logs, and online redo logs are in the new locations, and the database has a new name.

10. As a last step, ensure that your temporary tablespace exists:

    ALTER TABLESPACE TEMP ADD TEMPFILE '/ora01/dbfile/DEVDB/temp01.dbf'
         SIZE 104857600  REUSE AUTOEXTEND OFF;