Using RMAN to restore a database to another server in an ASM environment

It is possible to restore an Oracle 11g database to another server easily in an ASM environment by following the steps below.

Perform a full database backup using RMAN. The backup pieces will reside in the Flash Recovery Area of the source database.

The example below uses the database control file for its catalog:

rman target /

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

release channel prmy1;

release channel prmy2;

release channel prmy3;

release channel prmy4;

}

When the backup completes successfully, copy the entire backup to the remote database’s Flash Recovery Area. (The assumption here being; the remote database is an empty “barebones” DB, having just default tablespaces and users created). We can use DBMS_FILE_TRANSFER.PUT_FILE procedure for this, but first we must create an Oracle Directory on source and target:

logon to node A (source) as oracle

set following environment vaiables for DB
E.g.

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

export ORACLE_SID=MYDB

Create an Oracle Directory on top of +FLASH/mydb/backupset/<todays_date>

sqlplus / as sysdba

SQL> create directory BACKUPSET_NODEA as '+FLASH/mydb/backupset/2009_12_07';

Directory created.

Create a temporary user for file transfer

SQL> create user filetrans identified by filetrans;

User created.

SQL> grant connect, resource, dba to filetrans;

Grant succeeded.

SQL> grant read, write on directory BACKUPSET_NODEA to filetrans;

Grant succeeded.

logon to node B (target) as oracle
set following environment vaiables for ASM
E.g.

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/asm

export ORACLE_SID=+ASM

Make an ASM directory under +FLASH/mydb/backupset

asmcmd

ASMCMD> cd +FLASH/mydb/backupset

ASMCMD mkdir 2009_12_07

Create an Oracle Directory on top of ASM directory (+FLASH/mydb/backupset/2009_12_07) as in step c above, but name it BACKUPSET_NODEB
Create a temporary user as in step d above and grant read, write privileges on the Directory to user

Use DBMS_FILE_TRANSFER.PUT_FILE to transfer the backup files from Node A to Node B.

logon to node A (source) as oracle
set following environment vaiables for ASM
E.g.

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/asm

export ORACLE_SID=+ASM

use ASM command line interpreter to list backup files

asmcmd ls +FLASH/mydb/backupset/<todays_date>

E.g.

annnf0_TAG20091126T021140_0.10954.703908705

annnf0_TAG20091126T021140_0.2068.703909105

annnf0_TAG20091126T021140_0.330.703908703

annnf0_TAG20091126T031122_0.10989.703912285

annnf0_TAG20091126T031122_0.14617.703912553

annnf0_TAG20091126T031122_0.15436.703912285

annnf0_TAG20091126T031122_0.2063.703912285

annnf0_TAG20091126T031122_0.6825.703912285

ncnnf0_TAG20091126T013849_0.14464.703906809

nnndf0_TAG20091126T013849_0.11351.703906831

nnndf0_TAG20091126T013849_0.6933.703906751

nnndf0_TAG20091126T013849_0.6938.703906749

nnndf0_TAG20091126T013849_0.9652.703906829

nnsnf0_TAG20091126T013849_0.9043.703906813

set following environment variables for DB
E.g.

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

export ORACLE_SID=MYDB

logon as filetrans user and create a database link. (Assuming TNS Admin is configured)

sqlplus filetrans/ filetrans

SQL> create database link NODEB connect to filetrans identified by filetrans using ‘NODEB’;

Database link created.
Put backup files (1 at a time) from NodeA DB and to NodeB DB

E.g.

SQL> exec dbms_file_transfer.put_file('BACKUPSET_NODEA','annnf0_TAG20091126T021140_0.10954. 703908705','BACKUPSET_NODEB','annnf0_TAG20091126T021140_0.10954','NODEB');

PL/SQL procedure successfully completed.

[ dbms_file_transfer.put_file( SOURCE_DIRECTORY_OBJECT, SOURCE_FILE_NAME, DESTINATION_DIRECTORY_OBJECT, DESTINATION_FILE_NAME, DESTINATION_DATABASE ) ]

Repeat step f for each file in source database backup directory until all files are transfered.

N.B. The unique incarnation number attached to the filename by ASM must be removed on the destination filename else the file transfer will fail to the following error:

Restore the database on target server using RMAN. The first step is to restore the database control file that contains the backup information, then catalog the backup files.

logon to Node B as oracle

set following environment vaiables for DB
E.g.

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

export ORACLE_SID=MYDB

logon to target database as sysdba

sqlplus / as sysdba

shutdown and startup (nomount) database and exit

SQL> shutdown immediate

SQL> startup nomount

SQL> exit

connect to database using RMAN

rman target /

restore original database control file from backuppeice ( get filename from RMAN backup log file )

RMAN> restore controlfile from '+FLASH/mydb/backupset/2009_12_07/ncnnf0_tag20091126t013849_0.14464.703906809';

mount database from RMAN prompt

RMAN> alter database mount;

Database mounted.

restore database using RMAN

RMAN> restore database;

Starting restore at 07-DEC-09

Starting implicit crosscheck backup at 07-DEC-09

allocated channel: ORA_DISK_1

Crosschecked 45 objects

Finished implicit crosscheck backup at 07-DEC-09

Starting implicit crosscheck copy at 07-DEC-09

using channel ORA_DISK_1

Finished implicit crosscheck copy at 07-DEC-09

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.268.704548253

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.269.704548299

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.270.704548339

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.271.704548411

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.272.704548447

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.273.704548517

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.274.704548645

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.275.704548683

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.276.704548711

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.277.704548739

File Name: +flash/MYDB/BACKUPSET/FILE_TRANSFER_0.278.704549283
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

. . .

channel ORA_DISK_1: restore complete, elapsed time: 00:43:56

Finished restore at 07-DEC-09

N.B. When the files are transferred to target DB ASM directory, ASM will rename the files to +flash/MYDB/BACKUPSET/FILE_TRANSFER_

Aliases (with original filename) are automatically created in +flash/MYDB/BACKUPSET/2009_12_07 pointing to respective files in +flash/MYDB/BACKUPSET/ directory.

Recover the target database using RMAN.

logon to target database using RMAN

rman target /

Get the highest sequence number from RMAN catalog

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name MYDB

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

Key Thrd Seq S Low Time

------- ---- ------- - ---------

197651 1 51537 A 07-DEC-09

Name: +FLASH/mydb/archivelog/2009_12_07/thread_1_seq_51537.4384.703902883

197655 1 51538 A 07-DEC-09

Name: +FLASH/mydb/archivelog/2009_12_07/thread_1_seq_51538.6894.703903155

. . .

197730 2 53401 A 07-DEC-09

Name: +FLASH/mydb/archivelog/2009_12_07/thread_2_seq_53401.316.704565073

Recover database to last sequence number in backup

RMAN> recover database until sequence 53401;

When recovery complete, open database from RMAN prompt

RMAN> alter database open resetlogs;

Database opened.

Finally drop the filetrans temporary user and BACKUPSET_NODEA Oracle Directory from the source database.

_______________________________________________________________________________

Did you find the article useful?

Please provide your feedback by voting now.

If you have a comment or question, please complete and submit the form below.

_______________________________________________________________________________