RMAN Restoration to New Server with Different Directory and New Database Name

In this case, I have an existing production database with RMAN Level 0 backup:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\flash_recovery_area\galaxy\backupsets\autobackup\SAMSUNG-ctl_%F';
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\flash_recovery_area\galaxy\backupsets\level0\level0-%d-%T-%U' MAXPIECESIZE 4G;
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7';
SHOW ALL;
REPORT SCHEMA;
LIST BACKUP OF DATABASE;
REPORT NEED BACKUP;
REPORT UNRECOVERABLE;
LIST EXPIRED BACKUP BY FILE;
LIST ARCHIVELOG ALL;
REPORT OBSOLETE;
CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK COPY OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
RUN {
sql 'alter system archive log current';
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT;
}
LIST BACKUP OF DATABASE;
REPORT NEED BACKUP;
REPORT UNRECOVERABLE;
LIST EXPIRED BACKUP BY FILE;
LIST ARCHIVELOG ALL;
REPORT OBSOLETE;
CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK COPY OF ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;



The backupsets will be restored into a new server with different file structure. The database will also be different. In the new server, I have created a blank database (SID or CUSTOM database with the name DBGRID). This is easier so I won't have to create any directories, user ORADIM or create new PWFile.


This document assumes that a blank (starter) database has been created in the new server. The RDBMS version is the same as the source database.
In this example, the source database name is GALAXY and the target database name is DBGRID.

Let me know if you have any feedback or comments. All I want is to share these steps with you and make it better so we can help other DBA's as well.


Steps start here 
-->

ORIGINAL DATABASE: GALAXY
DUPLICATE DATABASE: DBGRID
  

FROM THE SOURCE DATABASE (GALAXY)

1. Backup the database using RMAN incremental backups.

2. Check the RMAN logs generated and look for the result of the command:

C:\Users\Administrator>set ORACLE_SID=GALAXY

C:\Users\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 1 06:55:08 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GALAXY (DBID=3578870300)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name GALAXY

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    680      SYSTEM               ***     C:\ORACLE\ORADATA\GALAXY\SYSTEM01.DBF
2    510      SYSAUX               ***     C:\ORACLE\ORADATA\GALAXY\SYSAUX01.DBF
3    140      UNDOTBS1             ***     C:\ORACLE\ORADATA\GALAXY\UNDOTBS01.DBF
4    5        USERS                ***     C:\ORACLE\ORADATA\GALAXY\USERS01.DBF
5    106      EXAMPLE              ***     C:\ORACLE\ORADATA\GALAXY\EXAMPLE01.DBF
6    2048     ALLINDEX             ***     C:\ORACLE\ORADATA\GALAXY\ALLINDEX01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       C:\ORACLE\ORADATA\GALAXY\TEMP01.DBF




This command shows all the physical database files that will be backed-up by the current RMAN job.

List the online redolog files:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE  C:\ORACLE\ORADATA\GALAXY\REDO03A.LOG     NO
         3         ONLINE  C:\ORACLE\ORADATA\GALAXY\REDO03B.LOG     NO
         2         ONLINE  C:\ORACLE\ORADATA\GALAXY\REDO02A.LOG     NO
         2         ONLINE  C:\ORACLE\ORADATA\GALAXY\REDO02B.LOG     NO
         1         ONLINE  C:\ORACLE\ORADATA\GALAXY\REDO01A.LOG     NO
         1         ONLINE  C:\ORACLE\ORADATA\GALAXY\REDO01B.LOG     NO

6 rows selected.

3. Since the target server has different structure, it is easier to use a pfile to startup the database rather than restoring the spfile from backup.

C:\Users\Administrator>set ORACLE_SID=GALAXY
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 1 06:56:14 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='C:\TEMP\initGALAXYrman.ora' from spfile;

File created.


4. Edit the pfile to have the following parameters:

(These parameters has been edited to fit the target database requirements)
galaxy entries has been changed to dbgrid. Directories has been corrected as well.

dbgrid.__db_cache_size=192937984
dbgrid.__java_pool_size=4194304
dbgrid.__large_pool_size=4194304
dbgrid.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
dbgrid.__pga_aggregate_target=281018368
dbgrid.__sga_target=524288000
dbgrid.__shared_io_pool_size=0
dbgrid.__shared_pool_size=301989888
dbgrid.__streams_pool_size=4194304
*.audit_file_dest='D:\app\Administrator\admin\DBGRID\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\oradata\DBGRID\control01.ctl','D:\oradata\DBGRID\control02.ctl'
*.db_block_size=8192
*.db_domain=''
Leave as it is for now --> *.db_name='GALAXY'
*.db_recovery_file_dest='D:\fra\backupsets\duplicate'
*.DB_RECOVERY_FILE_DEST_SIZE=4G
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBGRIDXDB)'
*.job_queue_processes=1000
*.log_archive_dest_1='location=D:\fra\backupsets\archs'
*.log_buffer=10485760
*.memory_target=805306368
*.open_cursors=300
*.processes=800
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=300
*.sessions=885
*.undo_tablespace='UNDOTBS1'

Make sure that all the directory values in this edited pfile are existing in the target server.

5. Copy all the RMAN backup pieces generated in the source database. Transfer the edited pfile as well. Make sure that all directories presented in the pfile
exists in the target server.

In this example, RMAN backupsets are located at:

Backupsets:
Level0 and Level1 = D:\fra\backupsets\duplicate\level0 , D:\fra\backupsets\duplicate\level1
Controlfile and Spfile Autobackup = D:\fra\backupsets\duplicate\autobackup
Edited pfile initGALAXYrman= C:\TEMP\initDBGRIDnew.ora


FROM THE TARGET DATABASE (DBGRID)

6. Connect to RMAN and perform database duplication using RMAN backup restoration

C:\Users\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 1 07:26:45 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> set dbid = 3578870300; --> THIS IS THE DBID OF THE SOURCE DATABASE!

executing command: SET DBID


RMAN> startup nomount pfile='C:\TEMP\initDBGRIDnew.ora'; -->restore the edited pfile

Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                327156176 bytes
Database Buffers             192937984 bytes
Redo Buffers                  14192640 bytes

RMAN> restore controlfile from 'D:\fra\backupsets\duplicate\autobackup\SAMSUNG-CTL_C-3578870300-20120801-00'; -->restore the correct controlfile

Starting restore at 01-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=D:\ORADATA\DBGRID\CONTROL01.CTL
output file name=D:\ORADATA\DBGRID\CONTROL02.CTL
Finished restore at 01-AUG-12


RMAN> alter database mount; -->mount the database

database mounted
released channel: ORA_DISK_1


RMAN> catalog start with "D:\fra\backupsets\duplicate";
--> catalog the location of the backup pieces to make it known to the control file

Starting implicit crosscheck backup at 01-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 01-AUG-12

Starting implicit crosscheck copy at 01-AUG-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-AUG-12

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern D:\fra\backupsets\duplicate

List of Files Unknown to the Database
=====================================
File Name: D:\FRA\BACKUPSETS\duplicate\autobackup\SAMSUNG-CTL_C-3578870300-20120801-00
File Name: D:\FRA\BACKUPSETS\duplicate\level0\LEVEL0-GALAXY-20120801-01NHGTGV_1_1
File Name: D:\FRA\BACKUPSETS\duplicate\level0\LEVEL0-GALAXY-20120801-02NHGTIA_1_1
File Name: D:\FRA\BACKUPSETS\duplicate\level0\LEVEL0-GALAXY-20120801-03NHGTMT_1_1

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

List of Cataloged Files
=======================
File Name: D:\FRA\BACKUPSETS\duplicate\autobackup\SAMSUNG-CTL_C-3578870300-20120801-00
File Name: D:\FRA\BACKUPSETS\duplicate\level0\LEVEL0-GALAXY-20120801-01NHGTGV_1_1
File Name: D:\FRA\BACKUPSETS\duplicate\level0\LEVEL0-GALAXY-20120801-02NHGTIA_1_1
File Name: D:\FRA\BACKUPSETS\duplicate\level0\LEVEL0-GALAXY-20120801-03NHGTMT_1_1


RMAN> list backup summary; -->check your backup they are now known


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  A  A DISK        01-AUG-12       1       2       NO         TAG20120801T011054
2       B  0  A DISK        01-AUG-12       1       2       NO         TAG20120801T011137
3       B  A  A DISK        01-AUG-12       1       2       NO         TAG20120801T011405


RMAN> list archivelog all; -->list you archivelog for point in time recovery

List of Archived Log Copies for database with db_unique_name GALAXY
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
26      1    29      A 01-AUG-12
        Name: D:\FRA\BACKUPSETS\ARCHS\ARC0000000029_0785167457.0001


Perform database restoration and recovery. The sequence number is Seq+1 for complete recovery, so that will be 29 + 1 = 30. You can also do recovery via SCN or time based recovery.

Remember in the previous step: RMAN> REPORT SCHEMA; 
Each datafile has a corresponding datafile number.
Follow the format to convert them into their respective new restoration directory in the target server.



SET NEWNAME has the format:

SET NEWNAME FOR DATAFILE [FILE NUMBER IN THE REPORT SCHEMA COMMAND] TO
‘[NEW LOCATION AND FILENAME OF THE DATAFILES]’;


RMAN> run {
2> SET UNTIL SEQUENCE 30 THREAD 1;
3> set newname for datafile 1 to 'D:\oradbgrid\oradata\SYSTEM01.DBF';
4> set newname for datafile 2 to 'D:\oradbgrid\oradata\SYSAUX01.DBF';
5> set newname for datafile 3 to 'D:\oradbgrid\oradata\UNDOTBS01.DBF';
6> set newname for datafile 4 to 'D:\oradbgrid\oradata\USERS01.DBF';
7> set newname for datafile 5 to 'D:\oradbgrid\oradata\EXAMPLE01.DBF';
8> set newname for datafile 6 to 'D:\oradbgrid\oradata\ALLINDEX01.DBF';
9> restore database;
10> switch datafile all;
11> recover database;
12> }

 executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\oradbgrid\oradata\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\oradbgrid\oradata\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\oradbgrid\oradata\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\oradbgrid\oradata\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\oradbgrid\oradata\EXAMPLE01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\oradbgrid\oradata\ALLINDEX01.DBF
channel ORA_DISK_1: reading from backup piece D:\FRA\BACKUPSETS\DUPLICATE\LEVEL0\LEVEL0-GALAXY-20120801-02NHGTIA_1_1
channel ORA_DISK_1: piece handle=D:\FRA\BACKUPSETS\DUPLICATE\LEVEL0\LEVEL0-GALAXY-20120801-02NHGTIA_1_1 tag=TAG20120801T011137
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:06
Finished restore at 01-AUG-12


Starting recover at 01-AUG-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 29 is already on disk as file D:\FRA\BACKUPSETS\ARCHS\ARC0000000029_0785167457.0001
archived log file name=D:\FRA\BACKUPSETS\ARCHS\ARC0000000029_0785167457.0001 thread=1 sequence=29
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-AUG-12




Log in to SQL*Plus to recreate the redolog files.

Format:

ALTER DATABASE RENAME FILE ‘[OLD REDOLOG LOCATION]’ TO ‘[NEW REDOLOG LOCATION]’;


C:\Users\Administrator>sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 1 08:54:27 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
dbgrid           MOUNTED

SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\GALAXY\REDO03A.LOG' TO 'C:\oracle\oradata\dbgrid\REDO03A.LOG';

Database altered.

SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\GALAXY\REDO03B.LOG' TO 'C:\oracle\oradata\dbgrid\REDO03B.LOG';

Database altered.

SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\GALAXY\REDO02A.LOG' TO 'C:\oracle\oradata\dbgrid\REDO02A.LOG';

Database altered.

SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\GALAXY\REDO02B.LOG' TO 'C:\oracle\oradata\dbgrid\REDO02B.LOG';

Database altered.

SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\GALAXY\REDO01A.LOG' TO 'C:\oracle\oradata\dbgrid\REDO01A.LOG';

Database altered.

SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\GALAXY\REDO01B.LOG' TO 'C:\oracle\oradata\dbgrid\REDO01B.LOG';

Database altered.




Open the database in resetlogs option. Create spfile from the edited pfile then bounce the database.


RMAN> alter database open resetlogs;

database opened

SQL> create spfile from pfile='C:\TEMP\initDBGRIDnew.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             327156176 bytes
Database Buffers          192937984 bytes
Redo Buffers               14192640 bytes
Database mounted.
Database opened.
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
dbgrid           OPEN



Create a new TEMP tablespace, make this as the new default temporary tablespace. Drop the old TEMP tablespace. Restart the database.


SQL> CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE 'D:\oradbgrid\oradata\TEMP0101.dbf' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

Database altered.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             327156176 bytes
Database Buffers          192937984 bytes
Redo Buffers               14192640 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Rename the database using NID utility.



C:\Users\Administrator>nid target=sys/oracle_4U DBNAME=DBGRID

DBNEWID: Release 11.2.0.1.0 - Production on Wed Aug 1 09:12:24 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database GALAXY (DBID=3578870300)

Connected to server version 11.2.0

Control Files in database:
    D:\ORADATA\DBGRID\CONTROL01.CTL
    D:\ORADATA\DBGRID\CONTROL02.CTL

Change database ID and database name GALAXY to DBGRID? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3578870300 to 1076830599
Changing database name from GALAXY to DBGRID
    Control File D:\ORADATA\DBGRID\CONTROL01.CTL - modified
    Control File D:\ORADATA\DBGRID\CONTROL02.CTL - modified
    Datafile D:\ORADBGRID\ORADATA\SYSTEM01.DB - dbid changed, wrote new name
    Datafile D:\ORADBGRID\ORADATA\SYSAUX01.DB - dbid changed, wrote new name
    Datafile D:\ORADBGRID\ORADATA\UNDOTBS01.DB - dbid changed, wrote new name
    Datafile D:\ORADBGRID\ORADATA\USERS01.DB - dbid changed, wrote new name
    Datafile D:\ORADBGRID\ORADATA\EXAMPLE01.DB - dbid changed, wrote new name
    Datafile D:\ORADBGRID\ORADATA\ALLINDEX01.DB - dbid changed, wrote new name
    Datafile D:\ORADBGRID\ORADATA\TEMP0101.DB - dbid changed, wrote new name
    Control File D:\ORADATA\DBGRID\CONTROL01.CTL - dbid changed, wrote new name
    Control File D:\ORADATA\DBGRID\CONTROL02.CTL - dbid changed, wrote new name
    Instance shut down

Database name changed to DBGRID.
Modify parameter file and generate a new password file before restarting.
Database ID for database DBGRID changed to 1076830599.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.



Log in to SQL*Plus and change the following parameters:


C:\Users\Administrator>sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 1 09:15:07 2012

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

Connected to an idle instance.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             327156176 bytes
Database Buffers          192937984 bytes
Redo Buffers               14192640 bytes

SQL> ALTER SYSTEM SET DB_NAME=DBGRID SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;

ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             327156176 bytes
Database Buffers          192937984 bytes
Redo Buffers               14192640 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.



Restart the database to check for consistency.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             327156176 bytes
Database Buffers          192937984 bytes
Redo Buffers               14192640 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

 Backup and test your newly created database from RMAN restoration.