RMAN

Restore Scenarios

TOC

Full Database Restore
Full Database Restore (Using an Edited Parameter File)
Tablespace Restore (online)
Tablespace Restore (offline)
Restoring a Datafile
Control File Restoration
Point-In-Time-Recovery (PIT)
Restore to Another System
Restore After Erroneous Resetlogs Cmd Issued
Test Scenario

These scenarios require that you have done what is required to backup an Oracle database.

Prerestore Comments

For control file default backups the DBID is embedded in the name.
Example: c-1184749195-20140626-02
If Block Change Tracking has been used make sure to disable it before the recover stage.
RMAN> sql 'alter database disable block change tracking';
For full database restores, if any tablespaces have been EXCLUDED you have to skip them in the recover stage.
RMAN> recover database SKIP tablespace "X15","X16";
If you get end-of-file on communication channel type errors try performing the steps
outlined in the Full Database Restore (Using an Edited Parameter File) scenario.
DB1 has been used in the examples here. Change as required to match the database name for your environment. Use your databases DBID of course too.

Full Database Restore

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
database opened

Full Database Restore (Using an Edited Parameter File)

Make your required changes to a backup ASCII pfile (R:\rman\initDB1.ora).
You have to remove the below type headings: entries to use a pfile in this way.

<InstanceName>.___<param>=<value>

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> set dbid 1184749195
RMAN> startup nomount pfile='R:\rman\initDB1.ora';
RMAN> restore spfile from 'R:\rman\C-1184749195-20140412-00';
RMAN> startup force nomount pfile='R:\rman\initDB1.ora';
RMAN> restore controlfile from 'R:\rman\C-1184749195-20140412-00';
RMAN> shutdown immediate
RMAN> exit

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> startup mount pfile='R:\rman\initDB1.ora';
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened

One common restore scenario when having to do this is the removal of the *.local_listener entry from the pfile.

Example entry to be removed: *.local_listener='LISTENER_DB1'

Tablespace Restore (online)

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> sql 'alter tablespace users offline';
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';

A SYSTEM tablespace cannot be recovered with the database online.

Tablespace Restore (offline)

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
database opened

Restoring a Specific Datafile

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile '/oradata/DB1/dbf/users01.dbf';
RMAN> recover datafile '/oradata/DB1/dbf/users01.dbf';
RMAN> alter database open;
database opened

Control File Restoration

Prerequisite: In your rman backup directory determine the latest control file backup.

Default Control File Format: c-nnnnnnnnnn-nnnnnnnn-nn

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> shutdown abort;
RMAN> startup nomount;
RMAN> set dbid = 1184749195
RMAN> restore controlfile from '/oradata/DB1/rman/c-1184749195-20060626-02'
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened

Database Point-In-Time-Recovery (PITR)

Also known as time-based incomplete recovery.

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database until time "to_date('11/07/2022 12:15:00', 
      'MM/DD/YYYY HH24:MI:SS')";
RMAN> recover database until time "to_date('11/07/2022 12:15:00', 
      'MM/DD/YYYY HH24:MI:SS')";
RMAN> alter database open resetlogs;
database opened

Restore to Another System

Prerequisites

Ensure destination system configured exactly like source.
- Same OS version and patch level.
- Same drives (C:, D:, S: etc.).
- CPU and RAM same or better.
The same version of Oracle is installed on the target system as the source.
Ensure the ORACLE_BASE, ORACLE_HOME and ORACLE_SID environment variables are set.
Create and configure the Listener.
Copy RMAN backupset files to the destination system rman directory.
If Windows:
- Create the password file.
  cd %ORACLE_HOME%\dbs
  orapwd file=orapwDB1 password=mypassword
  This creates the file %ORACLE_HOME%\dbs\orapwDB1
- Copy %ORACLE_HOME%\dbs\orapwDB1 to %ORACLE_HOME%\database.
  In some instances of a restore like this it may look for the file here.
- Create and start the Windows database instance service.
  cd %ORACLE_HOME%\database
  oradim -new -sid DB1 -intpwd mypassword -startmode MANUAL

This command:

Creates and starts the Windows service.
Creates the file: %ORACLE_HOME%\database\PWDDB1.ORA

Ensure Oracle database service started.
Create database physical volumes and directories.
Ensure the drive-path to the admin (adump,bdump,cdump,udump), data and redo directories on
the source and destination systems are identical. Example below:

    
     Admin Dump Directories
       mkdir C:\app\oracle\product\11.2.0.3\admin
       mkdir C:\app\oracle\product\11.2.0.3\admin\DB1
       mkdir C:\app\oracle\product\11.2.0.3\admin\DB1\adump
       mkdir C:\app\oracle\product\11.2.0.3\admin\DB1\bdump
       mkdir C:\app\oracle\product\11.2.0.3\admin\DB1\cdump
       mkdir C:\app\oracle\product\11.2.0.3\admin\DB1\udump
     Control File Directories
       mkdir C:\oradata\ctl
       mkdir X:\oradata\ctl
     Data Directories
       mkdir C:\oradata\dbf
       mkdir X:\oradata\dbf
     Flashback Directory
       mkdir C:\oradata\fbr
     Redo and Archive Log Directories
       mkdir C:\oradata\rdo
       mkdir X:\oradata\rdo
       mkdir C:\oradata\arc
       mkdir X:\oradata\arc

Procedure

Restore SPFILE and Control File

%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> set dbid 161080442
RMAN> startup nomount;
RMAN> restore spfile from 'R:\rman\C-161080442-20080313-00';
      Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA
RMAN> startup force nomount
RMAN> restore controlfile from 'R:\rman\C-161080442-20080313-00';
RMAN> shutdown immediate
RMAN> exit

Notes on startup nomount;

Creates the file: ...\database\hc_db1.dat
You may get the below error because the SPFILE has not been restored yet. It is normal.

   startup failed: ORA-01078: failure in processing parameters 
   LRM-00109: could not open parameter file 'C:\PathToFile'

Note on: recover database;
If you do not have\need the very last log(s) you can disregard error messages similar to:

   ORA-00310: archived log contains sequence 100; sequence 101 required...

If the database refuses to start when you issue the first startup nomount AND continues to give you the below error:

   starting Oracle instance without parameter file for retrieval of spfile
   RMAN-00571: ===========================================================
   RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   RMAN-00571: ===========================================================
   RMAN-03002: failure of startup command at 04/08/2014 08:56:17
   RMAN-04014: startup failed: ORA-03113: end-of-file on communication channel

Start it with a copy of your ASCII pfile. Example:

startup nomount pfile=C:\app\oracle\product\11.2.0.3
        \dbhome_1\database\pfile.ora

Restore and Recover the Data

Note you can also perform a PITR if desired here too.

cd %ORACLE_HOME%\database
%ORACLE_HOME%\bin\rman target sys/MyPassword nocatalog
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
      database opened

Make sure you perform a full backup after this operation!

Configure Listener

You can use the Net Configuration Assistant to quickly Delete and Add a Listener.

Restore After Resetlogs Issued

When you run resetlogs you change the incarnation of the database. In the event that you attempted an RMAN restore and mistakenly issued a resetlogs command you can still restore\recover. You will need:

Your DBID.
A Control File from your last backup.
All required archived redo logs.

Procedure

DBID in this example = 123456789.

1. From the last backup log get date\time last backup ended. 
   Ex: 09\21\2015 @ 17:21

2. Confirm controlfile matching last RMAN date\time.    
   Ex: 09\21\2015 @ 17:21

    ==> Given the above set the restore & recover time to a minute afterward.
        Ex: 09/21/2015 17:22:00

3. RMAN> shutdown abort
4. RMAN> startup nomount
5. RMAN> set dbid = 123456789
6. RMAN> restore controlfile from 'R:\rman\C-123456789-20150921-00';
7. RMAN> alter database mount;
8. RMAN> restore database until time "to_date('09/21/2015 17:22:00',
         'MM/DD/YYYY HH24:MI:SS')";
9. RMAN> recover database until time "to_date('09/21/2015 17:22:00',
         'MM/DD/YYYY HH24:MI:SS')";
10. If above all goes perfect: RMAN> alter database open resetlogs;

Test Scenario

desc TEST.CUST;
 Name			    Null?    Type
 -------------------------- -------- ------------------
 CUST_ID		    NOT NULL NUMBER
 NAME				     VARCHAR2(50)
 CITY				     VARCHAR2(50)
 COUNTRY			     VARCHAR2(50)

-- Confirm
COL name FORMAT a15
COL city FORMAT a15
SELECT cust_id,name,city FROM test.cust WHERE cust_id=50;


 CUST_ID    NAME 	      CITY
 ---------- --------------- ---------------
 50         Josiah A. Oneil  Opprebais

-- Backup
$ORACLE_HOME/bin/rman target / nocatalog
backup database plus archivelog;

 --> Note time backup ended. <--

-- Change Data (some minutes later than above)
UPDATE test.cust
SET city='Cape Canaveral'
WHERE cust_id=50;
COMMIT;

-- Restore 
-- Set until time to after backup time but before data change time.
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;

RMAN> restore database until time "to_date('10/04/2016 09:15:00','MM/DD/YYYY HH24:MI:SS')";
RMAN> recover database until time "to_date('10/04/2016 09:15:00','MM/DD/YYYY HH24:MI:SS')";
RMAN> alter database open resetlogs;

-- Confirm
SELECT cust_id,name,city FROM test.cust WHERE cust_id=50;

<- RMAN