Restore backup from NON-DEFAULT location

I would like to share. How to restore database backup from non-default location. Using target database control file instead of recovery catalog database trying to create database.

Using CATALOG BACKUPPIECE trying to restore backup from non-default location.

I have one test database name called "TempDb" & database version 10.2.0.4 & linux 32-bit. Now taking full database backup in TempDb under "/dbbackup/backup/TEMPDB"

[oracle@Babu TEMPDB]$ cat tempdb_backup_log

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Oct 24 11:08:49 2009

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

connected to target database: TEMPDB (DBID=3762582214)

RMAN> run
2> {
3> allocate channel c1 type disk format '/dbbackup/backup/TEMPDB/%s_%p_%t';
4> backup database;
5> backup archivelog all;
6> release channel c1;
7> }
8>
9>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=142 devtype=DISK

Starting backup at 24-OCT-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/dbbackup/oradata/TempDb/system01.dbf
input datafile fno=00003 name=/dbbackup/oradata/TempDb/sysaux01.dbf
input datafile fno=00002 name=/dbbackup/oradata/TempDb/undotbs01.dbf
input datafile fno=00004 name=/dbbackup/oradata/TempDb/users01.dbf
channel c1: starting piece 1 at 24-OCT-09
channel c1: finished piece 1 at 24-OCT-09
piece handle=/dbbackup/backup/TEMPDB/10_1_701089743 tag=TAG20091024T110902 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:26
Finished backup at 24-OCT-09

Starting backup at 24-OCT-09
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=28 recid=28 stamp=701089834
channel c1: starting piece 1 at 24-OCT-09
channel c1: finished piece 1 at 24-OCT-09
piece handle=/dbbackup/backup/TEMPDB/11_1_701089839 tag=TAG20091024T111036 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-OCT-09

Starting Control File and SPFILE Autobackup at 24-OCT-09
piece handle=/dbbackup/backup/TEMPDB/_c-3762582214-20091024-00 comment=NONE
Finished Control File and SPFILE Autobackup at 24-OCT-09

released channel: c1

Recovery Manager complete.
[oracle@Babu TEMPDB]$ ls -lrt
total 560360
-rw-r--r-- 1 oracle oinstall 142 Oct 24 11:08 tempdb_backup.rcv
-rw-r----- 1 oracle oinstall 543621120 Oct 24 11:10 10_1_701089743
-rw-r----- 1 oracle oinstall 22456832 Oct 24 11:10 11_1_701089839
-rw-r----- 1 oracle oinstall 7143424 Oct 24 11:10 _c-3762582214-20091024-00
-rw-r--r-- 1 oracle oinstall 1844 Oct 24 11:10 tempdb_backup_log
[oracle@Babu TEMPDB]$

After taking backup under /dbbackup/backup/TEMPDB/*.* moving to different location. (New location /dbbackup/backup/CATDB/)


[oracle@Babu TEMPDB]$ mv 10_1_701089743 11_1_701089839 _c-3762582214-20091024-00
/dbbackup/backup/CATDB/

[oracle@Babu TEMPDB]$ ls -lrt
total 8
-rw-r--r-- 1 oracle oinstall 142 Oct 24 11:08 tempdb_backup.rcv
-rw-r--r-- 1 oracle oinstall 1844 Oct 24 11:10 tempdb_backup_log

[oracle@Babu TEMPDB]$ cd ../CATDB/

[oracle@Babu CATDB]$ ls -lrt
total 560352
-rw-r----- 1 oracle oinstall 543621120 Oct 24 11:10 10_1_701089743
-rw-r----- 1 oracle oinstall 22456832 Oct 24 11:10 11_1_701089839
-rw-r----- 1 oracle oinstall 7143424 Oct 24 11:10 _c-3762582214-20091024-00
[oracle@Babu CATDB]$

Trying to create "CATDB" database from different backup location using RMAN DU

[oracle@Babu dbs]$ export ORACLE_SID=CATDB

[oracle@Babu dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs

[oracle@Babu dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwCATDB

password=sys entries=8

[oracle@Babu dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 24 12:37:06 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> exit
Disconnected


[oracle@Babu dbs]$ cd /dbbackup/oradata/
[oracle@Babu oradata]$ ls
DEVDB JAVA TempDb

[oracle@Babu oradata]$ mkdir CATDB

[oracle@Babu oradata]$ cd /u01/app/oracle/product/10.2.0/db_1/admin/

[oracle@Babu admin]$ ls
TempDb TEMPDB

[oracle@Babu admin]$ cp -R T
TempDb/ TEMPDB/

[oracle@Babu admin]$ cp -R TEMPDB CATDB

[oracle@Babu admin]$

[oracle@Babu admin]$ ls
CATDB TempDb TEMPDB

[oracle@Babu admin]$ cd CATDB/

[oracle@Babu CATDB]$ ls
adump bdump cdump dpdump pfile udump

[oracle@Babu CATDB]$ cd bdump/
[oracle@Babu bdump]$ ls
alert_TEMPDB.log

[oracle@Babu bdump]$ rm alert_TEMPDB.log

[oracle@Babu bdump]$ export ORACLE_SID=CATDB
[oracle@Babu bdump]$
[oracle@Babu bdump]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 24 12:38:07 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1266608 bytes
Variable Size 117443664 bytes
Database Buffers 83886080 bytes
Redo Buffers 7118848 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@Babu bdump]$


[oracle@Babu CATDB]$ rman target=sys/sys@tempdb auxiliary / cmdfile=duplicate_catdb.sql

log=duplicate_catdb_log
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> [oracle@Babu CATDB]$

[oracle@Babu CATDB]$ cat duplicate_catdb_log

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Oct 24 12:49:59 2009

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

connected to target database: TEMPDB (DBID=3762582214)
connected to auxiliary database: CATDB (not mounted)

RMAN> run
2> {
3> allocate auxiliary channel c1 type disk;
4> allocate auxiliary channel c2 type disk;
5> CATALOG BACKUPPIECE '/dbbackup/backup/CATDB/_c-3762582214-20091024-00';
6> CATALOG BACKUPPIECE '/dbbackup/backup/CATDB/11_1_701089839';
7> CATALOG BACKUPPIECE '/dbbackup/backup/CATDB/10_1_701089743';
8> duplicate target database to CATDB;
9> }
10>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=156 devtype=DISK

allocated channel: c2
channel c2: sid=155 devtype=DISK

cataloged backuppiece
backup piece handle=/dbbackup/backup/CATDB/_c-3762582214-20091024-00 recid=12

stamp=701095838

cataloged backuppiece
backup piece handle=/dbbackup/backup/CATDB/11_1_701089839 recid=13 stamp=701095839

cataloged backuppiece
backup piece handle=/dbbackup/backup/CATDB/10_1_701089743 recid=14 stamp=701095840

Starting Duplicate Db at 24-OCT-09

contents of Memory Script:
{
set until scn 616663;
set newname for datafile 1 to
"/dbbackup/oradata/CATDB/system01.dbf";
set newname for datafile 2 to
"/dbbackup/oradata/CATDB/undotbs01.dbf";
set newname for datafile 3 to
"/dbbackup/oradata/CATDB/sysaux01.dbf";
set newname for datafile 4 to
"/dbbackup/oradata/CATDB/users01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24-OCT-09

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /dbbackup/oradata/CATDB/system01.dbf
restoring datafile 00002 to /dbbackup/oradata/CATDB/undotbs01.dbf
restoring datafile 00003 to /dbbackup/oradata/CATDB/sysaux01.dbf
restoring datafile 00004 to /dbbackup/oradata/CATDB/users01.dbf
channel c1: reading from backup piece /dbbackup/backup/TEMPDB/10_1_701089743
channel c1: restored backup piece 1
failover to piece handle=/dbbackup/backup/CATDB/10_1_701089743 tag=TAG20091024T110902
channel c1: restore complete, elapsed time: 00:00:47
Finished restore at 24-OCT-09
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CATDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/dbbackup/oradata/CATDB/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/dbbackup/oradata/CATDB/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/dbbackup/oradata/CATDB/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/dbbackup/oradata/CATDB/system01.dbf'
CHARACTER SET WE8ISO8859P1


contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=701095901 filename=/dbbackup/oradata/CATDB/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=701095901 filename=/dbbackup/oradata/CATDB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=701095901 filename=/dbbackup/oradata/CATDB/users01.dbf

contents of Memory Script:
{
set until scn 616663;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 24-OCT-09

starting media recovery

archive log thread 1 sequence 28 is already on disk as file

/dbbackup/archivelog/TEMPDB/1_28_699301197.dbf
archive log filename=/dbbackup/archivelog/TEMPDB/1_28_699301197.dbf thread=1 sequence=28
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-OCT-09

contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 209715200 bytes

Fixed Size 1266608 bytes
Variable Size 117443664 bytes
Database Buffers 83886080 bytes
Redo Buffers 7118848 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CATDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/dbbackup/oradata/CATDB/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/dbbackup/oradata/CATDB/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/dbbackup/oradata/CATDB/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/dbbackup/oradata/CATDB/system01.dbf'
CHARACTER SET WE8ISO8859P1


contents of Memory Script:
{
set newname for tempfile 1 to
"/dbbackup/oradata/CATDB/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/dbbackup/oradata/CATDB/undotbs01.dbf";
catalog clone datafilecopy "/dbbackup/oradata/CATDB/sysaux01.dbf";
catalog clone datafilecopy "/dbbackup/oradata/CATDB/users01.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /dbbackup/oradata/CATDB/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/dbbackup/oradata/CATDB/undotbs01.dbf recid=1 stamp=701095955

cataloged datafile copy
datafile copy filename=/dbbackup/oradata/CATDB/sysaux01.dbf recid=2 stamp=701095955

cataloged datafile copy
datafile copy filename=/dbbackup/oradata/CATDB/users01.dbf recid=3 stamp=701095956

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=701095955 filename=/dbbackup/oradata/CATDB/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=701095955 filename=/dbbackup/oradata/CATDB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=701095956 filename=/dbbackup/oradata/CATDB/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 24-OCT-09

Recovery Manager complete.
[oracle@Babu CATDB]$

[oracle@Babu dbbackup]$ . oraenv
ORACLE_SID = [CATDB] ?
[oracle@Babu dbbackup]$
[oracle@Babu dbbackup]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 24 14:04:39 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


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

SQL> select name from v$database;

NAME
---------
CATDB

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/dbbackup/oradata/CATDB/system01.dbf
/dbbackup/oradata/CATDB/undotbs01.dbf
/dbbackup/oradata/CATDB/sysaux01.dbf
/dbbackup/oradata/CATDB/users01.dbf

Successfully created/restored database from different backup location. Feel free write your comments here...