Summary: How to work an Oracle RMAN restore.
Date: Around 2013
Refactor: 8 March 2025: Checked links and formatting.

If you've created a RMAN backup and have created a pfile backup you'll also want a way to restore this.

• Restore the files like this

• Log in as the oracle owner

• Set the date format to prevent problems with date format interpretation

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

Check the restored pfile, it should look something like this:

*.audit_file_dest='/opt/oracle/admin/<oraclesid>/adump'
*.audit_sys_operations=true
*.audit_trail='OS'
*.background_dump_dest='/opt/oracle/admin/<oraclesid>/bdump'
*.db_block_size=8192
*.db_domain='dbdomain.company.local'
*.audit_file_dest='/opt/oracle/admin/<oraclesid>/adump'
*.audit_sys_operations=true
*.audit_trail='OS'
*.background_dump_dest='/opt/oracle/admin/<oraclesid>/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/var/data/oracle/10.2/<oraclesid>/control01.ctl','/var/data/oracle/10.2/<oraclesid>/control02.ctl','/var/data/oracle/10.2/<oraclesid>/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/<oraclesid>/cdump'
*.db_block_size=8192
*.db_domain='dbdomain.company.local'
*.db_file_multiblock_read_count=16
*.db_name='<oraclesid>'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(protocol=TCP)'
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/var/backup/oracle/10.2/archive'
*.open_cursors=300
*.os_authent_prefix=''
*.os_roles=false
*.pga_aggregate_target=3317694464
*.processes=200
*.remote_listener=''

Make sure that all directories in this file must exist!

Note that you could manually create this file as well in case you don't have a pfile from the database you're restoring:

Now we check the pfile by starting the instance.

UNIX>rman nocatalog target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 28 17:05:31 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> startup nomount

Oracle instance started
Total System Global Area    1610612736 bytes

Fixed Size                     2084400 bytes
Variable Size                385876432 bytes
Database Buffers            1207959552 bytes
Redo Buffers                  14692352 bytes

If you encounter any error in this phase, investigate and resolve and try again. Repeat this until there are no errors left.

The controlfile is needed to start the database in mount-mode. Because we have a RMAN backup we extract the controlfile from there. This gives an extra advantage because RMAN restores teh controlfile to all designated location of the parameter file.

UNIX>rman nocatalog target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 28 17:07:16 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: <oraclesid> (not mounted)
using target database control file instead of recovery catalog

RMAN>  restore controlfile from '/var/backup/oracle/rman_workdir/<hostname>ctl_date20100927' ;

Starting restore at 28-SEP-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=286 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/var/data/oracle/10.2/<oraclesid>/control01.ctl
output filename=/var/data/oracle/10.2/<oraclesid>/control02.ctl
output filename=/var/data/oracle/10.2/<oraclesid>/control03.ctl
Finished restore at 28-SEP-10

RMAN> alter database mount ;

database mounted

UNIX>ls -lrt /var/data/oracle/10.2/<oraclesid>

-rw-r-----   1 niboracl oinstall    6373376 Sep 28 17:07 control03.ctl
-rw-r-----   1 niboracl oinstall    6373376 Sep 28 17:07 control02.ctl
-rw-r-----   1 niboracl oinstall    6373376 Sep 28 17:07 control01.ctl

We need to tell the controlfile where to look for this information. Either we catalog each file explicitly or we just point to the directory containing all candidate files.

Catalog the archive files:

UNIX> rman nocatalog target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 28 17:10:30 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: <oraclesid> (DBID=3429651417, not open)
using target database control file instead of recovery catalog

RMAN> catalog start with '/var/backup/oracle/tmp/archive';

searching for all files that match the pattern /var/backup/oracle/tmp/archive
List of Files Unknown to the Database

=====================================

File Name: /var/backup/oracle/tmp/archive/1_21621_659795164.dbf
File Name: /var/backup/oracle/tmp/archive/1_21616_659795164.dbf
..
..
..
..
File Name: /var/backup/oracle/tmp/archive/1_21734_659795164.dbf
File Name: /var/backup/oracle/tmp/archive/1_21735_659795164.dbf
File Name: /var/backup/oracle/tmp/archive/newlist.text

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

If you get an error like:

List of Files Which Where Not Cataloged

=======================================

File Name: /var/backup/oracle/tmp/archive/newlist.text
  RMAN-07517: Reason: The file header is corrupted

you can safely ignore them. These files are not recognized as part of the backupset.

Catalog the backupset:

RMAN> catalog start with '/var/backup/oracle/rman_workdir/<oraclesid>_helotm9m_2606' ;

searching for all files that match the pattern /var/backup/oracle/rman_workdir/<oraclesid>_helotm9m_2606

List of Files Unknown to the Database

=====================================

File Name: /var/backup/oracle/rman_workdir/<oraclesid>_helotm9m_2606

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

We restore the database and then forward the database to the desired date and time.

UNIX> rman nocatalog target /

RMAN> restore database ;

Starting restore at 2010-09-28 17:21:00
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=289 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /var/data/oracle/10.2/<oraclesid>/system01.dbf
restoring datafile 00002 to /var/data/oracle/10.2/<oraclesid>/undotbs01.dbf
restoring datafile 00003 to /var/data/oracle/10.2/<oraclesid>/sysaux01.dbf
restoring datafile 00004 to /var/data/oracle/10.2/<oraclesid>/users01.dbf
restoring datafile 00005 to /var/data/oracle/10.2/<oraclesid>/users02.dbf
restoring datafile 00006 to /var/data/oracle/10.2/<oraclesid>/users03.dbf
channel ORA_DISK_1: reading from backup piece /var/backup/oracle/restore/var/backup/oracle/rman_workdir/<oraclesid>_helotm9m_2606
channel ORA_DISK_1: restored backup piece 1
piece handle=/var/backup/oracle/restore/var/backup/oracle/rman_workdir/<oraclesid>_helotm9m_2606 tag=TAG20100927T030006

channel ORA_DISK_1: restore complete, elapsed time: 00:21:16
Finished restore at 2010-09-28 17:42:17

RMAN> recover database until time '2010-09-27 03:00:00'  ;

Starting recover at 2010-09-28 17:42:56
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 21700 is already on disk as file /var/backup/oracle/tmp/archive/1_21700_659795164.dbf
archive log thread 1 sequence 21701 is already on disk as file /var/backup/oracle/tmp/archive/1_21701_659795164.dbf
archive log filename=/var/backup/oracle/tmp/archive/1_21700_659795164.dbf thread=1 sequence=21700
archive log filename=/var/backup/oracle/tmp/archive/1_21701_659795164.dbf thread=1 sequence=21701
media recovery complete, elapsed time: 00:00:08
Finished recover at 2010-09-28 17:43:04

The database is still in mounted mode, but is now suitable to be opened.

RMAN>  alter database open resetlogs ;

database opened