Restore DB to different dbname on different host with RMAN

OS:  AIX 7.1
DB: Oracle RAC 11.2.0.4.160119 with ASM

We have a DB named dwetldb in production and want to restore db to our test host with another dbname: udwetldb. In addition, there is already one db named dwetldb running on the target host.

All the steps are executed on the target host. The key is to keep dbname the same and db_unique_name different. With the same dbname and db_unique_name, you may end up joining the existing RAC cluster. With different dbname, you cannot mount the database. Furthermore, paths of redo log files and data files need to be changed accordingly as well.

# stop database as oracle:

srvctl stop database -d udwetldb

# as grid, delete datafile tempfile controlfile and onlinelog:

asmcmd
cd +DATADG/udwetldb/
rm -rf datafile onlinelog tempfile controlfile
cd +FLASHDG/udwetldb
rm -rf ARCHIVELOG ONLINELOG CONTROLFILE

# as oracle:

export ORACLE_SID=udwetldb1
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
rman target /

create pfile='/tmp/pfile.ora' from spfile='+DATADG/udwetldb/spfileudwetldb.ora';
vi /tmp/pfile.ora
# cluster_database=true
# db_name='dwetldb'         <--- DB_NAME must be the same as the original DB
# db_unique_name='udwetldb' <--- Note this is different from the original DB
# optionally, check control_file path in SQL*Plus

— restore controlfile

run {
allocate channel c1 type sbt;
send 'NB_ORA_CLIENT=PDWHDBS-scanip' ;
restore controlfile from 'cntrl_2636_1_926739243';
release channel c1;
}

alter database mount;

— rename redolog file to new directories

sqlplus / as sysdba

-- check original redolog file path
set lines 200 pages 200
col member for a60
select member from v$logfile;

set lines 200 pages 200
select 'alter database rename file '||chr(39)||member||chr(39)||' to '||chr(39)||replace(member, 'dwetldb', 'udwetldb')||chr(39)||';'
from v$logfile;

alter database rename file '+DATADG/dwetldb/onlinelog/group_1.433.890232235' to '+DATADG/udwetldb/onlinelog/group_1.433.890232235';
alter database rename file '+DATADG/dwetldb/onlinelog/group_2.434.890232235' to '+DATADG/udwetldb/onlinelog/group_2.434.890232235';
alter database rename file '+DATADG/dwetldb/onlinelog/group_3.435.890232237' to '+DATADG/udwetldb/onlinelog/group_3.435.890232237';

— modify datafile path

-- generate set newname clause for rman
set lines 200 pages 200 
col name for a60
select file#, name
from v$datafile
order by file#;

set lines 200 pages 200
select 'set newname for datafile '||file#||' to '||chr(39)||replace(name, 'dwetldb', 'udwetldb')||chr(39)||';'
from v$datafile;

run {
set until time "to_date('10/29/2016 12:15:00', 'MM/DD/YYYY HH24:MI:SS')";
allocate channel c1 type sbt;
allocate channel c2 type sbt;
send 'NB_ORA_CLIENT=PDWHDBS-SCANIP';
set newname for datafile 1 to '+DATADG/udwetldb/datafile/system.439.890232245';
set newname for datafile 2 to '+DATADG/udwetldb/datafile/sysaux.440.890232249';
set newname for datafile 3 to '+DATADG/udwetldb/datafile/undotbs1.441.890232253';
set newname for datafile 4 to '+DATADG/udwetldb/datafile/undotbs2.443.890232261';
set newname for datafile 5 to '+DATADG/udwetldb/datafile/users.444.890232265';
set newname for datafile 6 to '+DATADG/udwetldb/datafile/infadwrep.464.890233227';
restore database;
switch datafile all;
recover database;
release channel c2;
release channel c1;
}

— alter database open and update spfile

alter database open resetlogs;

SQL> shutdown immediate;

SQL> create spfile='+DATADG/udwetldb/spfileudwetldb.ora' from pfile='/tmp/pfile.ora';

— start RAC database

srvctl start database -d udwetldb
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s