Sunday 12 February 2017

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
By at
Labels: , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Comparing Oracle Database Appliance X8-2 Model Family

September 2019 Oracle announced Oracle Database Appliance X8-2 (Small, Medium and HA). ODA X8-2 comes with more computing resources com...