Restore RMAN backup to another server with different SID

by , under Oracle

Step 0: Prepare

Source database;
Target Database : fopsp
Target Database Server : bdcorap02
Using ASM storage on Windows Server 2008 R2

Destination database;
Auxiliary Database : fopst
Auxiliary Database Server : bdcorat02
Using OS file system on Windows Server 2008 R2

Step 1:

Take the incremental level 0 backup of the Target database using RMAN.
In my case, I had the backup of my target database (fopsp) taken at the location “\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\”

PS Microsoft.PowerShell.Core\FileSystem::\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP> dir | more

Directory: \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP

Mode LastWriteTime Length Name
—- ————- —— —-
-a— 05/10/2015 12:33 PM 9961472 C-1427950135-20151005-00
-a— 05/10/2015 6:22 PM 9961472 C-1427950135-20151005-01
-a— 05/10/2015 12:33 PM 176651264 FOPSP_ARCHLOG_T892298026_S564_P1
-a— 05/10/2015 6:22 PM 318571008 FOPSP_ARCHLOG_T892318958_S566_P1
-a— 22/07/2015 6:20 PM 9961472 C-1427950135-20150722-02
-a— 23/07/2015 12:18 PM 9961472 C-1427950135-20150723-00
-a— 27/06/2015 12:24 PM 213430272 FOPSP_ARCHLOG_FOPSP_T883484670_S88_P1
-a— 27/06/2015 5:48 PM 183261184 FOPSP_ARCHLOG_FOPSP_T883504088_S89_P1
-a— 27/06/2015 11:43 PM 93816832 FOPSP_ARCHLOG_FOPSP_T883525414_S90_P1
-a— 27/06/2015 3:45 AM 3621756928 FOPSP_FULL_FOPSP_2MQAGRGR_1_883453467
-a— 27/06/2015 3:45 AM 9961472 FOPSP_FULL_FOPSP_2NQAGRJH_1_883453553

 

Step 2:

These backup pieces are able to be accessed from Target server and Auxiliary server at the same time.

 

 

Step 3:

On the Auxiliary server, edit the pfile that was copied earlier to the desired entries (dump locations, control file location, datafile locations, if using ASM then specify the desired disk group) and rename it to the desired instance name file (init<SID>.ora). Below is the sample I had it done.

initfopst.ora which is copied from initfopsp.ora

fopsp.__db_cache_size=2113929216
fopsp.__java_pool_size=16777216
fopsp.__large_pool_size=50331648
fopsp.__oracle_base=’C:\Oracle’#ORACLE_BASE set from environment
fopsp.__pga_aggregate_target=419430400
fopsp.__sga_target=3355443200
fopsp.__shared_io_pool_size=0
fopsp.__shared_pool_size=1124073472
fopsp.__streams_pool_size=16777216
*.audit_file_dest=’C:\Oracle\admin\FOPST\adump’
*.audit_trail=’NONE’
*.compatible=’11.2.0.4.0′
*.control_files=’E:\FOPST\control\CONTROLFOPST.CTL’
*.cursor_sharing=’SIMILAR’
*.db_block_size=8192
*.db_domain=’WESTERNFOREST.COM’
*.db_name=’FOPST’
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest=’E:\FOPST\fast_recovery_area’
*.diagnostic_dest=’C:\Oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=FOPSTXDB)’
*.log_archive_dest_1=’LOCATION=E:\FOPST\arch’
*.log_archive_format=’ARC%S_%R.%T’
*.memory_max_target=3774873600
*.memory_target=3774873600
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=3250585600
*.sga_target=3250585600
*.shared_pool_size=956301312
*.undo_tablespace=’UNDOTBS1′

Step 4:

a. Create password file

PS H:\> orapwd file=C:\Oracle\product\11.2.0\dbhome_1\database\PWDFOPST.ora password=xxxxx

b. Create Windows Service for Oracle new SID

PS H:\> C:\oracle\product\11.2.0\dbhome_1\bin\oradim -new -sid FOPST -startmode manual -shutmode immediate

 

Step 5:

When you sqlplus login if you get this error message:

ERROR:
ORA-12631: Username retrieval failed

You can try this steps;

a. You can log in as a NT local user.

b. You can disable NTS in sqlnet.ora by setting the following parameter:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

PS H:\> sqlplus sys/xxxxx as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 2 16:12:04 2015

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

Connected to an idle instance.

16:12:06 >
16:12:06 > startup nomount;

Total System Global Area 1.7771E+10 bytes
Fixed Size 2288480 bytes
Variable Size 6777996448 bytes
Database Buffers 1.0939E+10 bytes
Redo Buffers 52224000 bytes
16:14:12 >

Step 6:

Connect the auxiliary instance through RMAN and start the duplication.

PS H:\> rman auxiliary sys/xxxxx

Recovery Manager: Release 11.2.0.4.0 – Production on Fri Oct 2 16:15:21 2015

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

connected to auxiliary database: FOPST (not mounted)

RMAN>

Script:
RUN {
set newname for datafile 1 to ‘E:\FOPST\oradata\SYSTEM01.DBF’;
set newname for datafile 2 to ‘E:\FOPST\oradata\SYSAUX01.DBF’;
set newname for datafile 3 to ‘E:\FOPST\oradata\UNDOTBS1_01.DBF’;
set newname for datafile 4 to ‘E:\FOPST\oradata\USERS01.DBF’;
set newname for datafile 5 to ‘E:\FOPST\oradata\forestops_index01.DBF’;
set newname for datafile 6 to ‘E:\FOPST\oradata\forest_ops_data_ts01.DBF’;
set newname for tempfile 1 to ‘E:\FOPST\oradata\TEMP01.DBF’;
duplicate database to ‘FOPST’ backup location ‘\\bdcdr4k01\DBBACKUP\Oracle\Production\FOPSP’;
switch datafile all;
}
Result:
————————————————————————————–
executing command: SET NEWNAME

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 Duplicate Db at 05-OCT-15

contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script

sql statement: create spfile from memory

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

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”FOPSP” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”FOPST” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from ‘\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\C-1427950135-20151004-01’;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”FOPSP” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”FOPST” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes

Starting restore at 05-OCT-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\FOPST\CONTROL\CONTROLFOPST.CTL
Finished restore at 05-OCT-15

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK

contents of Memory Script:
{
set until scn 4298569114718;
set newname for datafile 1 to
“E:\FOPST\oradata\SYSTEM01.DBF”;
set newname for datafile 2 to
“E:\FOPST\oradata\SYSAUX01.DBF”;
set newname for datafile 3 to
“E:\FOPST\oradata\UNDOTBS1_01.DBF”;
set newname for datafile 4 to
“E:\FOPST\oradata\USERS01.DBF”;
set newname for datafile 5 to
“E:\FOPST\oradata\forestops_index01.DBF”;
set newname for datafile 6 to
“E:\FOPST\oradata\forest_ops_data_ts01.DBF”;
restore
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

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 05-OCT-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to E:\FOPST\oradata\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to E:\FOPST\oradata\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to E:\FOPST\oradata\UNDOTBS1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to E:\FOPST\oradata\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to E:\FOPST\oradata\forestops_index01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to E:\FOPST\oradata\forest_ops_data_ts01.DBF
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_FULL_H8QIOI7P_1_892
094713
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_FULL_H8QIOI7P_1_892094713 tag=WE
EKLY_FULL_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 05-OCT-15

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

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=892303045 file name=E:\FOPST\ORADATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=892303045 file name=E:\FOPST\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=892303045 file name=E:\FOPST\ORADATA\UNDOTBS1_01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=892303045 file name=E:\FOPST\ORADATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=892303045 file name=E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=892303045 file name=E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF

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

executing command: SET until clause

Starting recover at 05-OCT-15
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=590
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892122428_
S554_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892122428_S554_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000590_0881072057.0001 thread=1 sequence=590
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000590_0881072057.0001 RECID=1 STAMP=892303048
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=591
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=592
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892146539_
S556_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892146539_S556_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000591_0881072057.0001 thread=1 sequence=591
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000591_0881072057.0001 RECID=3 STAMP=892303055
archived log file name=E:\FOPST\ARCH\ARC0000000592_0881072057.0001 thread=1 sequence=592
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000592_0881072057.0001 RECID=2 STAMP=892303054
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=593
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892165775_
S558_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892165775_S558_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=E:\FOPST\ARCH\ARC0000000593_0881072057.0001 thread=1 sequence=593
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000593_0881072057.0001 RECID=4 STAMP=892303062
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=594
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892209183_
S560_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892209183_S560_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000594_0881072057.0001 thread=1 sequence=594
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000594_0881072057.0001 RECID=5 STAMP=892303065
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=595
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=596
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892230482_
S562_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892230482_S562_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000595_0881072057.0001 thread=1 sequence=595
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000595_0881072057.0001 RECID=7 STAMP=892303071
archived log file name=E:\FOPST\ARCH\ARC0000000596_0881072057.0001 thread=1 sequence=596
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000596_0881072057.0001 RECID=6 STAMP=892303070
media recovery complete, elapsed time: 00:00:06
Finished recover at 05-OCT-15
Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”FOPST” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ”FOPST” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “FOPST” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 262145 K ,
GROUP 2 SIZE 262145 K ,
GROUP 3 SIZE 262145 K
DATAFILE
‘E:\FOPST\ORADATA\SYSTEM01.DBF’
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
“E:\FOPST\oradata\TEMP01.DBF”;
switch clone tempfile all;
catalog clone datafilecopy “E:\FOPST\ORADATA\SYSAUX01.DBF”,
“E:\FOPST\ORADATA\UNDOTBS1_01.DBF”,
“E:\FOPST\ORADATA\USERS01.DBF”,
“E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF”,
“E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to E:\FOPST\oradata\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\SYSAUX01.DBF RECID=1 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\UNDOTBS1_01.DBF RECID=2 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\USERS01.DBF RECID=3 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF RECID=4 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF RECID=5 STAMP=892303104

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=892303104 file name=E:\FOPST\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=892303104 file name=E:\FOPST\ORADATA\UNDOTBS1_01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=892303104 file name=E:\FOPST\ORADATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=892303104 file name=E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=892303104 file name=E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF

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

database opened
Finished Duplicate Db at 05-OCT-15
RMAN>

RMAN>

RMAN>

RMAN>

RMAN> exit
Recovery Manager complete.
PS H:\> sqlplus sys/xxxxx as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 5 14:02:59 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 – 64bit Production

14:02:59 fopst> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
14:03:15 fopst> startup
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size 2285592 bytes
Variable Size 1090523112 bytes
Database Buffers 2130706432 bytes
Redo Buffers 16723968 bytes
Database mounted.
Database opened.
14:03:32 fopst> create spfile from pfile;

File created.

Elapsed: 00:00:00.09
14:03:40 fopst> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
14:03:54 fopst> startup mount;
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size 2285592 bytes
Variable Size 1090523112 bytes
Database Buffers 2130706432 bytes
Redo Buffers 16723968 bytes
Database mounted.
14:04:09 fopst> alter database noarchivelog;

Database altered.

Elapsed: 00:00:00.04
14:04:15 fopst> alter database open;

Database altered.

Elapsed: 00:00:03.66
14:04:22 fopst> show parameter spfile;

NAME TYPE VALUE
———————————— ——————————– ——————————
spfile string C:\ORACLE\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SPFILEFOPST.ORA
14:04:27 fopst>

Leave a Reply