RMAN Restore by Command-Line

Overview: I will explain RMAN fundamentals and work through an example to demonstrate an RMAN restore.

Fundamentals

An RMAN restore is really just two commands:

1. RESTORE DATABASE. This command instructs RMAN to search the controlfile for a list of valid database backups, select the most recent and issue a restore. A restore is simply the placing of database files on disk.

2. RECOVER DATABASE. This command tells RMAN: make the database consistent. Before a database can be opened, it must be consistent. Unless directed by Oracle Support, this rule can not be overturned, and is always enforced by the database.

In Oracle terms, consistency means all database files must be at the same System Change Number(SCN). SCN is the way Oracle keeps internal time – every change to the database is assigned a unique SCN. Every change – INSERT, UPDATE, DELETE, ALTER, COMMIT or System Checkpoints – is assigned a unique SCN.

Now, a consistent restore is not necessarily complete. In Oracle terms, if a backup was taken while the database was open (hence using archivelog mode), any recovery from such a backup will be incomplete.

A backup taken from an open database in archivelog mode can not backup the online REDO logs. So any changes recorded in a Redo log will not be backed up, hence the reason why the restore is referred to as incomplete. This is not a problem, since for most intents & purposes, a restore to the last available archivelog is expected.

Example

We will use RMAN to restore a database called LIVE as the example process. We assume:

·         The database was in archivelog mode

·         Autobackup turned on

·         The backup & autobackup are available on disk

In this example, commands to be typed are shown in bold - in the context of the command output. Steps should be followed in the order given since later steps depend on successful completion of previous steps.

1. This step may be optional. If the restore is to be done on a new server, then this step is necessary, otherwise the instance may already exist. Create a new Oracle instance called LIVE using these commands in a new Command Prompt window:

CD /D C:\oracle\product\10.2.0\db_1\BIN

oradim -NEW -SID LIVE -SYSPWD password_live

2. In this step, we will use RMAN to restore the latest spfile and controlfile using what is known as an Autobackup. An autobackup contains the controlfile and spfile of the LIVE database.

HUMAN INTERVENTION NECESSARY: You need to identify the appropriate autobackup file. It is usually located in the latest-dated sub-folder with an Oracle-generated name. When typing the commands below, substitute the appropriate file names.

You will need to determine the appropriate controlfile path & name for this step. Note that the entire file name is enclosed by single quotes.

3. Perform the restore:
1. Open a Command prompt and type:

set oracle_sid=LIVE

   rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Oct 27 16:18:41 2011

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

connected to target database (not started)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file 'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\

SERVER\DATABASE\INITXE.ORA'

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     1286292 bytes

Variable Size                 58724204 bytes

Database Buffers              96468992 bytes

Redo Buffers                   2904064 bytes

RMAN> restore spfile from 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\AUTOBAC

KUP\2011_10_27\O1_MF_S_765644418_7BLS3FWT_.BKP';

Starting restore at 27-OCT-11

using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA

\XE\AUTOBACKUP\2011_10_27\O1_MF_S_765644418_7BLS3FWT_.BKP

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 27-OCT-11

RMAN>

RMAN> shutdown abort;

ORACLE instance shut down.

RMAN> startup nomount;

ORACLE instance started.

Total System Global Area 1677721600 bytes

Fixed Size                  2066240 bytes

Variable Size             318769344 bytes

Database Buffers         1342177280 bytes

Redo Buffers               14708736 bytes

RMAN> restore controlfile from 'E:\DSLD01\FRA\LIVE\AUTOBACKUP\2010_07_28\O1_MF_S

_725565668_650RLONZ_.BKP';

Starting restore at 29-JUL-10

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

output filename=+DG1/sby02/controlfile/control01.ctl

Finished restore at 29-JUL-10

RMAN> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

RMAN> startup mount

ORACLE instance started.

Total System Global Area 1677721600 bytes

Fixed Size                  2066240 bytes

Variable Size             318769344 bytes

Database Buffers         1342177280 bytes

Redo Buffers               14708736 bytes

Database mounted.

HUMAN INTERVENTION NECESSARY: Note the following command requires the correct folder/date to be substituted (similar to commands above).

RMAN> catalog start with ‘E:\FRA\LIVE\BACKUPSET\2010_07_28\' noprompt;

Starting implicit crosscheck backup at 29-JUL-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=479 devtype=DISK

Crosschecked 8 objects

Finished implicit crosscheck backup at 29-JUL-10

Starting implicit crosscheck copy at 29-JUL-10

using channel ORA_DISK_1

Finished implicit crosscheck copy at 29-JUL-10

searching for all files in the recovery area

cataloging files...

no files cataloged

[Not all output shown, ignore any errors]

4. Here we perform the restore and recover. The Recover command may produce some errors – ignore.

RMAN> RUN

{ allocate channel disk1 type disk;

  RESTORE DATABASE;

  RECOVER DATABASE;

}

allocated channel: disk1

channel disk1: sid=479 devtype=DISK

Starting restore at 29-JUL-10

channel disk1: starting datafile backupset restore

channel disk1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to +DG1/live/datafile/system.256.619109535

restoring datafile 00002 to +DG1/live/datafile/undotbs1.258.619109535

restoring datafile 00003 to +DG1/live/datafile/sysaux.257.619109535

restoring datafile 00004 to +DG1/live/datafile/users.259.619109535

...

channel disk1: reading from backup piece E:\FRA\LIVE\BACKUPSET\2010_07_23

\O1_MF_NNNDF_TAG20100723T180022_64MLO798_.BKP

Make a cup of tea or two. This will take some time.

5. At this point, we should have an incompletely recovered database. RMAN should say:

...

archive log filename=E:\FRA\LIVE\ARCHIVELOG\2010_07_28\O1_MF_1_4607_651BY

17T_.ARC thread=1 sequence=4607

media recovery complete, elapsed time: 00:01:23

Finished recover at 29-JUL-10

released channel: disk1

Continue with the process:

SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> QUIT;

That’s the end of the RMAN restore process.