oracledba.help
RMAN

Restore Scenarios

TOC

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:

  1. Your DBID.
  2. A Control File from your last backup.
  3. 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