Overview:
Oracle Recovery Manager
(RMAN) provides a comprehensive foundation for efficiently backing up and
recovering the Oracle databases, it provides a common interface, via command line and
Enterprise Manager, for backup tasks across different host operating systems, automates administration of your
backup strategies.
In this scenario we have different database name and different
directory structure. And we are transforming the directory structure in pfile
itself, then no need to give extra parameters in rman duplicate command.
Target (source) details
Database Name
|
Prod
|
Hostname
|
Netsoftmate
|
Ip Address
|
172.16.110.18
|
OS
|
Linux
|
Version
|
x86_64
|
Datafile Location
|
/data2/prod
|
Backup Location
|
/backup/rman_backup
|
Destination details
Database Name
|
Cipfile
|
Hostname
|
Netsoftmate1
|
Ip Address
|
172.16.110.16
|
OS
|
Linux
|
Version
|
x86_64
|
Datafile Location
|
/data2/cipfile
|
Backup Location
|
/backup/rman_backup
|
Prerequisites
Password file from target database.
Sqlnet.ora should have correct parameters.
Target database should be running through pfile or
spfile.
Auxiliary instance should be start with pfile in nomount
stage.
Steps on target (source) server
1. Set Oracle sid on which backup will perform.
[oracle@Netsoftmate dbs]$ hostname
Netsoftmate
[oracle@Netsoftmate dbs]$ export ORACLE_SID=prod
[oracle@Netsoftmate dbs]$ rman target sys/oracle
Recovery Manager: Release 11.2.0.1.0 - Production on
Mon Apr 13 10:13:01 2015
Copyright (c) 1982, 2009, Oracle and/or its
affiliates. All rights reserved.
connected to target
database: PROD (DBID=284539893)
RMAN>
3. Issue the following command to initiate backup. Before issue the backup command check the backup location is sat correctly.
RMAN> show all;
using target database control file instead of
recovery catalog
RMAN configuration parameters for database with
db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
TYPE DISK TO '/backup/rman_backup/controlfile_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK
TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman_backup/db_arch_%U.bkp';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE
'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #
default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f'; # default
RMAN> list backup;
specification does not match any backup in the
repository
4. Initiate Backup.
RMAN> backup database plus archivelog;
Starting backup at 13-APR-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in
backup set
input archived log thread=1 sequence=153 RECID=149
STAMP=876910536
channel ORA_DISK_1: starting piece 1 at 13-APR-15
channel ORA_DISK_1: finished piece 1 at 13-APR-15
piece
handle=/backup/rman_backup/db_arch_2uq495u9_1_1.bkp tag=TAG20150413T101536
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:01
Finished backup at 13-APR-15
Starting backup at 13-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup
set
input datafile file number=00001
name=/data2/prod/system01.dbf
input datafile file number=00002
name=/data2/prod/sysaux01.dbf
input datafile file number=00005
name=/data2/prod/example01.dbf
input datafile file number=00003
name=/data2/prod/undotbs01.dbf
input datafile file number=00006
name=/data2/prod/data01.dbf
input datafile file number=00004
name=/data2/prod/users01.dbf
input datafile file number=00007
name=/data2/prod/test01.dbf
input datafile file number=00008
name=/data2/prod/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 13-APR-15
channel ORA_DISK_1: finished piece 1 at 13-APR-15
piece handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp
tag=TAG20150413T101538 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:55
Finished backup at 13-APR-15
Starting backup at 13-APR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in
backup set
input archived log thread=1 sequence=154 RECID=150
STAMP=876910594
channel ORA_DISK_1: starting piece 1 at 13-APR-15
channel ORA_DISK_1: finished piece 1 at 13-APR-15
piece handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp
tag=TAG20150413T101635 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:01
Finished backup at 13-APR-15
Starting Control File Autobackup at 13-APR-15
piece handle=/backup/rman_backup/controlfile_c-284539893-20150413-00
comment=NONE
Finished Control File Autobackup at 13-APR-15
5. List out the backups
RMAN> list backup;
List of Backup Sets
===================
BS Key
Size Device Type Elapsed
Time Completion Time
------- ---------- ----------- ------------
---------------
32
3.36M DISK 00:00:00 13-APR-15
BP Key:
32 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101536
Piece
Name: /backup/rman_backup/db_arch_2uq495u9_1_1.bkp
List of
Archived Logs in backup set 32
Thrd Seq Low SCN
Low Time Next SCN Next Time
---- -------
---------- --------- ---------- ---------
1 153
5172938 13-APR-15 5176490 13-APR-15
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
33
Full 1.11G DISK 00:00:54 13-APR-15
BP Key:
33 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101538
Piece
Name: /backup/rman_backup/db_arch_2vq495ua_1_1.bkp
List of
Datafiles in backup set 33
File LV Type
Ckp SCN Ckp Time Name
---- -- ----
---------- --------- ----
1 Full 5176498 13-APR-15 /data2/prod/system01.dbf
2 Full 5176498 13-APR-15 /data2/prod/sysaux01.dbf
3
Full 5176498 13-APR-15
/data2/prod/undotbs01.dbf
4 Full 5176498 13-APR-15 /data2/prod/users01.dbf
5 Full 5176498 13-APR-15 /data2/prod/example01.dbf
6 Full 5176498 13-APR-15 /data2/prod/data01.dbf
7 Full 5176498 13-APR-15 /data2/prod/test01.dbf
8 Full 5176498 13-APR-15 /data2/prod/tools01.dbf
BS Key
Size Device Type Elapsed
Time Completion Time
------- ---------- ----------- ------------
---------------
34
9.00K DISK 00:00:00
13-APR-15
BP Key:
34 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101635
Piece
Name: /backup/rman_backup/db_arch_30q49603_1_1.bkp
List of
Archived Logs in backup set 34
Thrd Seq Low SCN
Low Time Next SCN Next Time
---- -------
---------- --------- ---------- ---------
1 154
5176490 13-APR-15 5176531 13-APR-15
BS Key Type LV
Size Device Type Elapsed Time
Completion Time
------- ---- -- ---------- ----------- ------------
---------------
35
Full 9.52M DISK 00:00:02 13-APR-15
BP Key:
35 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101636
Piece
Name: /backup/rman_backup/controlfile_c-284539893-20150413-00
Control File
Included: Ckp SCN: 5176543 Ckp time:
13-APR-15
RMAN>
6. Create pfile and copy to destination server.
SQL> create
pfile='/backup/rman_backup/initprod.ora' from spfile;
File created.
[oracle@Netsoftmate dbs]$ scp initprod.ora
oracle@172.16.110.16:/data1/oracle11g/product/dbs/initcipfile.ora
oracle@172.16.110.16's password:
initprod.ora
100% 1169 1.1KB/s 00:00
Before start scp/ftp to destination server create
directory structure to accommodate Rman backup on destination server.
[oracle@Netsoftmate1 prod]$ mkdir -p
/backup/rman_backup/
7. Copy rman backup from target to destination server.
RMAN> exit
Recovery Manager complete.
[oracle@Netsoftmate dbs]$ cd /backup/rman_backup/
[oracle@Netsoftmate rman_backup]$ ls -lrt
total 1176020
-rw-r-----. 1 oracle dba 3526656 Apr 13 10:15
db_arch_2uq495u9_1_1.bkp
-rw-r-----. 1 oracle dba 1190690816 Apr 13 10:16
db_arch_2vq495ua_1_1.bkp
-rw-r-----. 1 oracle dba 9728 Apr 13 10:16
db_arch_30q49603_1_1.bkp
-rw-r-----. 1 oracle dba 9994240 Apr 13 10:16
controlfile_c-284539893-20150413-00
[oracle@Netsoftmate rman_backup]$ scp *
oracle@172.16.110.16:/backup/rman_backup/
oracle@172.16.110.16's password:
controlfile_c-284539893-20150413-00
100% 9760KB 9.5MB/s 00:01
db_arch_2uq495u9_1_1.bkp 100%
3444KB 3.4MB/s 00:00
db_arch_2vq495ua_1_1.bkp
100%
1136MB 11.1MB/s 01:42
db_arch_30q49603_1_1.bkp
100% 9728 9.5KB/s
00:00
Steps on destination server
8. Create new directory structure.
[oracle@Netsoftmate1 dbs]$ mkdir -p /data2/cipfile
[oracle@Netsoftmate1 dbs]$ vi initcipfile.ora
Here we have modified the pfile by adding these
parameters as follows.
*.db_name='cipfile'
*.control_files='/data2/cipfile/control01.ctl','/data2/cipfile/control02.ctl'
*.db_file_name_convert=('/data2/prod','/data2/cipfile')
*.log_file_name_convert=('/data2/prod','/data2/cipfile')
10. Create password file in
$ORACLE_HOME/dbs directory
/data1/oracle11g/product/dbs
[oracle@Netsoftmate1 dbs]$ orapwd file=orapwcipfile
password=ora123
[oracle@Netsoftmate1 dbs]$ export ORACLE_SID=cipfile
[oracle@Netsoftmate1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 13
11:59:27 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2212696 bytes
Variable Size 352324776 bytes
Database Buffers 788529152 bytes
Redo Buffers 9383936 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise
Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
[oracle@Netsoftmate1 dbs]$
[oracle@Netsoftmate1 dbs]$ echo $ORACLE_SID
cipfile
[oracle@Netsoftmate1 dbs]$ rman auxiliary sys/ora123
Recovery Manager: Release 11.2.0.1.0 - Production on
Mon Apr 13 12:03:23 2015
Copyright (c) 1982, 2009, Oracle and/or its
affiliates. All rights reserved.
connected to auxiliary
database: CIPFILE (not mounted)
RMAN>
RMAN> DUPLICATE
TARGET DATABASE TO cipfile BACKUP LOCATION '/backup/rman_backup';
Starting Duplicate Db at 13-APR-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 1152450560 bytes
Fixed Size 2212696 bytes
Variable Size 335547560 bytes
Database Buffers 805306368 bytes
Redo Buffers 9383936 bytes
contents of Memory Script:
{
sql clone
"alter system set db_name =
''PROD''
comment=
''Modified by
RMAN duplicate'' scope=spfile";
sql clone
"alter system set db_unique_name =
''CIPFILE''
comment=
''Modified by
RMAN duplicate'' scope=spfile";
shutdown
clone immediate;
startup
clone force nomount
restore
clone primary controlfile from
'/backup/rman_backup/controlfile_c-284539893-20150413-00';
alter clone
database mount;
}
executing Memory Script
sql statement: alter system set db_name =
''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''CIPFILE'' comment= ''Modified by RMAN
duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1152450560 bytes
Fixed Size 2212696 bytes
Variable Size 335547560 bytes
Database Buffers 805306368 bytes
Redo Buffers 9383936 bytes
Starting restore at 13-APR-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:00:03
output file name=/data2/cipfile/control01.ctl
output file name=/data2/cipfile/control02.ctl
Finished restore at 13-APR-15
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK
contents of Memory Script:
{
set until
scn 5176531;
set newname
for datafile 1 to
"/data2/cipfile/system01.dbf";
set newname
for datafile 2 to
"/data2/cipfile/sysaux01.dbf";
set newname
for datafile 3 to
"/data2/cipfile/undotbs01.dbf";
set newname
for datafile 4 to
"/data2/cipfile/users01.dbf";
set newname
for datafile 5 to
"/data2/cipfile/example01.dbf";
set newname
for datafile 6 to
"/data2/cipfile/data01.dbf";
set newname
for datafile 7 to
"/data2/cipfile/test01.dbf";
set newname
for datafile 8 to
"/data2/cipfile/tools01.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
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 13-APR-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
/data2/cipfile/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to
/data2/cipfile/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/data2/cipfile/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/data2/cipfile/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to
/data2/cipfile/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to
/data2/cipfile/data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to
/data2/cipfile/test01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to
/data2/cipfile/tools01.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/backup/rman_backup/db_arch_2vq495ua_1_1.bkp
channel ORA_AUX_DISK_1: piece
handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp tag=TAG20150413T101538
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed
time: 00:00:45
Finished restore at 13-APR-15
contents of Memory Script:
{
switch clone
datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=876917132 file
name=/data2/cipfile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=876917133 file
name=/data2/cipfile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=876917134 file
name=/data2/cipfile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=876917134 file
name=/data2/cipfile/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=876917135 file
name=/data2/cipfile/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=876917136 file
name=/data2/cipfile/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=876917137 file
name=/data2/cipfile/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=16 STAMP=876917138 file
name=/data2/cipfile/tools01.dbf
contents of Memory Script:
{
set until
scn 5176531;
recover
clone
database
delete
archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 13-APR-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=154
channel ORA_AUX_DISK_1: reading from backup piece
/backup/rman_backup/db_arch_30q49603_1_1.bkp
channel ORA_AUX_DISK_1: piece
handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp tag=TAG20150413T101635
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=/backup/archive/1_154_872701561.dbf
thread=1 sequence=154
channel clone_default: deleting archived log(s)
archived log file
name=/backup/archive/1_154_872701561.dbf RECID=1 STAMP=876917145
media recovery complete, elapsed time: 00:00:04
Finished recover at 13-APR-15
contents of Memory Script:
{
shutdown
clone immediate;
startup
clone nomount;
sql clone
"alter system set db_name =
''CIPFILE''
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
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1152450560 bytes
Fixed Size
2212696 bytes
Variable Size 335547560 bytes
Database Buffers 805306368 bytes
Redo Buffers 9383936 bytes
sql statement: alter system set db_name =
''CIPFILE'' 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 1152450560 bytes
Fixed Size 2212696 bytes
Variable Size 335547560 bytes
Database Buffers 805306368 bytes
Redo Buffers 9383936 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE
"CIPFILE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/data2/cipfile/redo01.log' ) SIZE 50
M REUSE,
GROUP 2 ( '/data2/cipfile/redo02.log' ) SIZE 50
M REUSE,
GROUP 3 ( '/data2/cipfile/redo03.log' ) SIZE 50
M REUSE
DATAFILE
'/data2/cipfile/system01.dbf'
CHARACTER SET
WE8MSWIN1252
contents of Memory Script:
{
set newname
for tempfile 1 to
"/data2/cipfile/temp01.dbf";
switch clone
tempfile all;
catalog
clone datafilecopy
"/data2/cipfile/sysaux01.dbf",
"/data2/cipfile/undotbs01.dbf",
"/data2/cipfile/users01.dbf",
"/data2/cipfile/example01.dbf",
"/data2/cipfile/data01.dbf",
"/data2/cipfile/test01.dbf",
"/data2/cipfile/tools01.dbf";
switch clone
datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /data2/cipfile/temp01.dbf in
control file
cataloged datafile copy
datafile copy file name=/data2/cipfile/sysaux01.dbf
RECID=1 STAMP=876917170
cataloged datafile copy
datafile copy file name=/data2/cipfile/undotbs01.dbf
RECID=2 STAMP=876917170
cataloged datafile copy
datafile copy file name=/data2/cipfile/users01.dbf
RECID=3 STAMP=876917171
cataloged datafile copy
datafile copy file name=/data2/cipfile/example01.dbf
RECID=4 STAMP=876917171
cataloged datafile copy
datafile copy file name=/data2/cipfile/data01.dbf
RECID=5 STAMP=876917172
cataloged datafile copy
datafile copy file name=/data2/cipfile/test01.dbf
RECID=6 STAMP=876917172
cataloged datafile copy
datafile copy file name=/data2/cipfile/tools01.dbf
RECID=7 STAMP=876917173
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=876917170 file
name=/data2/cipfile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=876917170 file name=/data2/cipfile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=876917171 file
name=/data2/cipfile/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=876917171 file
name=/data2/cipfile/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=876917172 file
name=/data2/cipfile/data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=876917172 file
name=/data2/cipfile/test01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=876917173 file
name=/data2/cipfile/tools01.dbf
contents of Memory Script:
{
Alter clone
database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 13-APR-15
RMAN>
Conclusion
In the preceding scenario based article, we have learned that backup based duplication using Rman utility with different database name and different directory structure, using db_file_name_convert and log_file_name_convert option in pfile
BY
Name: Mirza Hidayathullah Baig
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com
Name: Mirza Hidayathullah Baig
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com
No comments:
Post a Comment