Netsoftmate

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

Written by

Netsoftmate

in

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

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts