Rman backup based duplication with same DB name and directory structure and target db have Read only tablespaces

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.

You can replicate a target (source) database using an RMAN backup when duplicating to an auxiliary (destination) database. The basic idea here is to copy an RMAN backup to an auxiliary server and create the auxiliary database directly from the backup. This is a simple and powerful technique for replicating a database. It is especially applicable where there’s no direct network connection between the target database and the auxiliary.

Here we are having same database name and same directory structure. And target database have read only tablespaces.

Environment details 
Target (source) details:

Database Name	prod
Hostname	Nsmdev
Ip Address	172.16.110.18
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

 Destination details:

Database Name	prod
Hostname	Nsmqty
Ip Address	172.16.110.16
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

 
Pre-requisites

• Password file from target database.
• Sqlnet.ora should have correct parameters.
• Target database should be running through spfile.

 Steps on target (source) server
1.    export Oracle sid.

[oracle@Nsmdev dbs]$ hostname

Nsmdev

[oracle@Nsmdev dbs]$ export ORACLE_SID=prod

 2.    Login to database and check tablespace details.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME      STATUS

-------------------- ----------

SYSTEM               ONLINE

SYSAUX               ONLINE

UNDOTBS1             ONLINE

TEMP                 ONLINE

USERS                ONLINE

EXAMPLE              ONLINE

DATA                 READ ONLY

TEST                 ONLINE

TOOLS                READ ONLY

9 rows selected.

  3.    Connect to RMAN.

[oracle@Nsmdev ~]$ rman target sys/oracle

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 15 12:44:17 2015

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

connected to target database: PROD (DBID=284539893)

RMAN>

  4.    Issue the following command to initiate backup. Before issue the backup command check the backup location is sat correctly.

RMAN> show all;

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> backup database plus archivelog;

Starting backup at 15-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=161 RECID=157 STAMP=877092331

channel ORA_DISK_1: starting piece 1 at 15-APR-15

channel ORA_DISK_1: finished piece 1 at 15-APR-15

piece handle=/backup/rman_backup/db_arch_3lq4enfb_1_1.bkp tag=TAG20150415T124531 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-APR-15

Starting backup at 15-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 15-APR-15

channel ORA_DISK_1: finished piece 1 at 15-APR-15

piece handle=/backup/rman_backup/db_arch_3mq4enfd_1_1.bkp tag=TAG20150415T124533 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Finished backup at 15-APR-15

Starting backup at 15-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=162 RECID=158 STAMP=877092389

channel ORA_DISK_1: starting piece 1 at 15-APR-15

channel ORA_DISK_1: finished piece 1 at 15-APR-15

piece handle=/backup/rman_backup/db_arch_3nq4enh6_1_1.bkp tag=TAG20150415T124629 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-APR-15

Starting Control File Autobackup at 15-APR-15

piece handle=/backup/rman_backup/controlfile_c-284539893-20150415-00 comment=NONE

Finished Control File Autobackup at 15-APR-15

RMAN>

  5.    Create pfile and copy to destination server.

SQL> create pfile='/backup/rman_backup/initprod.ora' from spfile;

File created.

[oracle@Nsmdev rman_backup]$ scp initprod.ora  oracle@172.16.110.16:/data1/oracle11g/product/dbs/

oracle@172.16.110.16's password:

initprod.ora                                                                                                                                     

                 100% 1169     1.1KB/s   00:00

create directory structure to accommodate rman backup on destination server and copy the backup pieces.

[oracle@Nsmqty prod]$ mkdir -p /backup/rman_backup/

6.    Copy rman backup to auxiliary database server

[oracle@Nsmdev rman_backup]$ scp *.bkp oracle@172.16.110.16://backup/rman_backup/

oracle@172.16.110.16's password:

db_arch_2eq3umgh_1_1.bkp                                                                                                                         

                 100% 1860MB  11.2MB/s   02:46

db_arch_2fq3umjs_1_1.bkp                                                                                                                         

                 100% 1132MB  11.2MB/s   01:41

db_arch_2gq3umlu_1_1.bkp                                                                                                                          

                 100%   38KB  38.0KB/s   00:00

  7.    Create password file on destination server

/data1/oracle11g/product/dbs

[oracle@Nsmqty dbs]$ orapwd file=orapwprod password=ora123

Steps on destination server
8.    Create directory structure same as target (source) database.

[oracle@Nsmqty prod]$ mkdir -p /data2/prod

[oracle@Nsmqty prod]$ mkdir -p /backup/archive/

9.    Start the instance in nomount.

[oracle@Nsmqty dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9 12:14:35 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

10.    Connect to rman

[oracle@Nsmqty dbs]$ rman auxiliary sys/oracle

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 9 12:19:37 2015

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

connected to auxiliary database: PROD (not mounted)

RMAN>

  11.    Issue the following command to duplicate database.

RMAN> duplicate database to prod backup location '/backup/rman_backup' nofilenamecheck;

Starting Duplicate Db at 15-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 =

 ''PROD'' 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-20150415-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 =  ''PROD'' 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 15-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/prod/control01.ctl

output file name=/data2/prod/control02.ctl

Finished restore at 15-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  5291226;

   set newname for datafile  1 to

 "/data2/prod/system01.dbf";

   set newname for datafile  2 to

 "/data2/prod/sysaux01.dbf";

   set newname for datafile  3 to

 "/data2/prod/undotbs01.dbf";

   set newname for datafile  4 to

 "/data2/prod/users01.dbf";

   set newname for datafile  5 to

 "/data2/prod/example01.dbf";

   set newname for datafile  6 to

 "/data2/prod/data01.dbf";

   set newname for datafile  7 to

 "/data2/prod/test01.dbf";

   set newname for datafile  8 to

 "/data2/prod/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 15-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/prod/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /data2/prod/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /data2/prod/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /data2/prod/users01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /data2/prod/example01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /data2/prod/data01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /data2/prod/test01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00008 to /data2/prod/tools01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_3mq4enfd_1_1.bkp

channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_3mq4enfd_1_1.bkp tag=TAG20150415T124533

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55

Finished restore at 15-APR-15

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=877102442 file name=/data2/prod/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=877102443 file name=/data2/prod/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=877102443 file name=/data2/prod/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=877102444 file name=/data2/prod/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=5 STAMP=877102444 file name=/data2/prod/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=6 STAMP=877102444 file name=/data2/prod/data01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=7 STAMP=877102445 file name=/data2/prod/test01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=8 STAMP=877102445 file name=/data2/prod/tools01.dbf

contents of Memory Script:

{

   set until scn  5291226;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 15-APR-15

using channel ORA_AUX_DISK_1

datafile 6 not processed because file is read-only

datafile 8 not processed because file is read-only

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=162

channel ORA_AUX_DISK_1: reading from backup piece /backup/rman_backup/db_arch_3nq4enh6_1_1.bkp

channel ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_3nq4enh6_1_1.bkp tag=TAG20150415T124629

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_162_872701561.dbf thread=1 sequence=162

channel clone_default: deleting archived log(s)

archived log file name=/backup/archive/1_162_872701561.dbf RECID=1 STAMP=877102457

media recovery complete, elapsed time: 00:00:03

Finished recover at 15-APR-15

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  db_name =

 ''PROD'' 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 =  ''PROD'' 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 "PROD" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '/data2/prod/redo01.log' ) SIZE 50 M  REUSE,

  GROUP  2 ( '/data2/prod/redo02.log' ) SIZE 50 M  REUSE,

  GROUP  3 ( '/data2/prod/redo03.log' ) SIZE 50 M  REUSE

 DATAFILE

  '/data2/prod/system01.dbf'

 CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/data2/prod/temp01.dbf";

   switch clone tempfile all;

   catalog clone datafilecopy  "/data2/prod/sysaux01.dbf",

 "/data2/prod/undotbs01.dbf",

 "/data2/prod/users01.dbf",

 "/data2/prod/example01.dbf",

 "/data2/prod/test01.dbf";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data2/prod/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/data2/prod/sysaux01.dbf RECID=1 STAMP=877102481

cataloged datafile copy

datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=877102482

cataloged datafile copy

datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=877102482

cataloged datafile copy

datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=877102483

cataloged datafile copy

datafile copy file name=/data2/prod/test01.dbf RECID=5 STAMP=877102483

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=877102481 file name=/data2/prod/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=877102482 file name=/data2/prod/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=877102482 file name=/data2/prod/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=877102483 file name=/data2/prod/example01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=5 STAMP=877102483 file name=/data2/prod/test01.dbf

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

contents of Memory Script:

{

   catalog clone datafilecopy  "/data2/prod/data01.dbf",

 "/data2/prod/tools01.dbf";

   switch clone datafile  6 to datafilecopy

 "/data2/prod/data01.dbf";

   switch clone datafile  8 to datafilecopy

 "/data2/prod/tools01.dbf";

   #online the readonly tablespace

   sql clone "alter tablespace  TOOLS online";

   #online the readonly tablespace

   sql clone "alter tablespace  DATA online";

}

executing Memory Script

cataloged datafile copy

datafile copy file name=/data2/prod/data01.dbf RECID=6 STAMP=877102546

cataloged datafile copy

datafile copy file name=/data2/prod/tools01.dbf RECID=7 STAMP=877102547

datafile 6 switched to datafile copy

input datafile copy RECID=6 STAMP=877102546 file name=/data2/prod/data01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=7 STAMP=877102547 file name=/data2/prod/tools01.dbf

sql statement: alter tablespace  TOOLS online

sql statement: alter tablespace  DATA online

Finished Duplicate Db at 15-APR-15

RMAN>

12.     Check the tablespaces status once database replicated.

SQL>  select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME      STATUS

-------------------- ----------

SYSTEM               ONLINE

SYSAUX               ONLINE

UNDOTBS1             ONLINE

TEMP                 ONLINE

USERS                ONLINE

EXAMPLE              ONLINE

DATA                 READ ONLY

TEST                 ONLINE

TOOLS                READ ONLY

9 rows selected.

Conclusion
In the above article, we have learned that backup based duplication using Rman utility with same database name and same directory structure, and target(source) database have Read only tablespaces.
 
BY
Name: Omer
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com