Database cloning using duplicate database from active database RMAN command

-
Introduction:
=========

Oracle RMAN Active Database Duplication is a new feature introduced in Oracle 11g R1. This option helps perform database copies without the need for relying on the source database backup. Oracle RMAN makes a copy of the source database (active database) at run time without the need of using its backup for the duplicate exercise. 

I have found it very interesting and finally want to implement before I want to post it. However I feel this process will consume more load on the source server 


This blog gives you a very simple way of cloning the database for the novice , as I am not making too complicated . Before I start I would like to share my environment


 source Database name : orcl 
 Target Database name : clone 
 source server hostname : test1.oracle.com 
 target server hostname  : test2.oracle.com
 Source listener  running on 1529 
 Target listener running on 1522
 TNS name for source :  test1_orcl 
 TNS name for target  :   test2_clone 

Note: make sure tnsname.ora is updated with above tns entries  on both server and check the connectivity using tnsping 

Prechecks:
========

1) Make sure the source database is archive mode , but is not necessary 
2) Make sure you are not using dynamic registration for both the source and target , but for source at-least  we          should not have any dynamic registration 
3) Source and destination database should use the same password file so create a password file for source             and   then SCP the file to target 
4) The source database and auxiliary instances must use the same SYS user password, which means that both instances must have password files.

5) make sure there is no firewall between source and target 


Lets start the activity:
===============

1-> Login to the target server  and then prepare listener.ora file and then update tnsname. ora file 


      




Now I am updating tns entries from source server 






2-> Now prepare the parameter file on the target database with the below entries 

db_domain=oracle.com
db_name=clone
control_files=("/u01/app/oracle/oradata/clone/control01.ctl", "/u01/app/oracle/oradata/clone/control02.ctl")
compatible=11.2.0.0.0
diagnostic_dest=/u01/app/oracle
memory_target=857735168
DB_FILE_name_CONVERT=('/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/clone')
LOG_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/clone')
[oracle@test2 admin]$



3-> no start change the name of copied password file from the server to target database

4-> startup the target database nomount mode

SQL> startup nomount pfile=/u01/app/oracle/admin/clone/pfile/init.ora;
ORACLE instance started.

Total System Global Area  855982080 bytes
Fixed Size                  2218152 bytes
Variable Size             503318360 bytes
Database Buffers          348127232 bytes
Redo Buffers                2318336 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@test2 pfile]$ cd $ORACLE_HOME/dbs
[oracle@test2 dbs]$ ls







RMAN> run
2> {
3> allocate channel t1 type disk;
4> allocate auxiliary channel a1 device type disk;
5> DUPLICATE TARGET DATABASE TO 'clone' from active database;
6> }



allocated channel: t1
channel t1: SID=28 device type=DISK

allocated channel: a1
channel a1: SID=19 device type=DISK

Starting Duplicate Db at 31-MAR-18

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     855982080 bytes

Fixed Size                     2218152 bytes
Variable Size                507512664 bytes
Database Buffers             343932928 bytes
Redo Buffers                   2318336 bytes
allocated channel: a1
channel a1: SID=19 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CLONE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/clone/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/clone/control02.ctl' from
 '/u01/app/oracle/oradata/clone/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     855982080 bytes

Fixed Size                     2218152 bytes
Variable Size                507512664 bytes
Database Buffers             343932928 bytes
Redo Buffers                   2318336 bytes
allocated channel: a1
channel a1: SID=19 device type=DISK

Starting backup at 31-MAR-18
channel t1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20180331T132237 RECID=3 STAMP=972220959
channel t1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-MAR-18

Starting restore at 31-MAR-18

channel a1: copied control file copy
Finished restore at 31-MAR-18

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/clone/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/clone/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/clone/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/clone/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/clone/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/clone/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/clone/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/clone/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/clone/users01.dbf"   datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/clone/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 31-MAR-18
channel t1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/clone/system01.dbf tag=TAG20180331T132251
channel t1: datafile copy complete, elapsed time: 00:00:55
channel t1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/clone/sysaux01.dbf tag=TAG20180331T132251
channel t1: datafile copy complete, elapsed time: 00:00:45
channel t1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=/u01/app/oracle/oradata/clone/example01.dbf tag=TAG20180331T132251
channel t1: datafile copy complete, elapsed time: 00:00:07
channel t1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/clone/undotbs01.dbf tag=TAG20180331T132251
channel t1: datafile copy complete, elapsed time: 00:00:07
channel t1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/clone/users01.dbf tag=TAG20180331T132251
channel t1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-MAR-18

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u02/FRA/ORCL/archivelog/2018_03_31/o1_mf_1_5_fcyhpbf7_.arc" auxiliary format
 "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_959281861.dbf"   ;
   catalog clone archivelog  "/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_959281861.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 31-MAR-18
channel t1: starting archived log copy
input archived log thread=1 sequence=5 RECID=2 STAMP=972221091
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_959281861.dbf RECID=0 STAMP=0
channel t1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 31-MAR-18

cataloged archived log
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_959281861.dbf RECID=2 STAMP=972221097

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=972221097 file name=/u01/app/oracle/oradata/clone/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=972221097 file name=/u01/app/oracle/oradata/clone/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=972221097 file name=/u01/app/oracle/oradata/clone/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=972221097 file name=/u01/app/oracle/oradata/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=972221098 file name=/u01/app/oracle/oradata/clone/example01.dbf

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

executing command: SET until clause

Starting recover at 31-MAR-18

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_959281861.dbf
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_959281861.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-MAR-18

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''CLONE'' 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     855982080 bytes

Fixed Size                     2218152 bytes
Variable Size                507512664 bytes
Database Buffers             343932928 bytes
Redo Buffers                   2318336 bytes
allocated channel: a1
channel a1: SID=18 device type=DISK

sql statement: alter system set  db_name =  ''CLONE'' 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     855982080 bytes

Fixed Size                     2218152 bytes
Variable Size                507512664 bytes
Database Buffers             343932928 bytes
Redo Buffers                   2318336 bytes
allocated channel: a1
channel a1: SID=18 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/clone/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/clone/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/clone/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/clone/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/clone/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/clone/sysaux01.dbf",
 "/u01/app/oracle/oradata/clone/undotbs01.dbf",
 "/u01/app/oracle/oradata/clone/users01.dbf",
 "/u01/app/oracle/oradata/clone/example01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/clone/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/sysaux01.dbf RECID=1 STAMP=972221127
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/undotbs01.dbf RECID=2 STAMP=972221127
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/users01.dbf RECID=3 STAMP=972221127
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/clone/example01.dbf RECID=4 STAMP=972221127

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=972221127 file name=/u01/app/oracle/oradata/clone/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=972221127 file name=/u01/app/oracle/oradata/clone/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=972221127 file name=/u01/app/oracle/oradata/clone/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=972221127 file name=/u01/app/oracle/oradata/clone/example01.dbf

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

database opened
Finished Duplicate Db at 31-MAR-18
released channel: t1
released channel: a1

RMAN>



Now your clone database or source database orcl is ready to use 


Note: if you want to place the backup files of source database to ASM storage of target database then just prepare the parameter file as below 

db_domain=oracle.com
db_name=clone
control_files=("/u01/app/oracle/oradata/clone/control01.ctl", "/u01/app/oracle/oradata/clone/control02.ctl")
compatible=11.2.0.0.0
diagnostic_dest=/u01/app/oracle
memory_target=857735168
DB_FILE_name_CONVERT=('/u01/app/oracle/oradata/orcl','+DATA_DG1/clone')
LOG_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl','+DATA_DG1/clone')
[oracle@test2 admin]$


Thanks,
Satya














Comments

Post a Comment

Popular posts from this blog

Understanding Terraform

-
 Terraform is a tool used  for building, changing, and versioning infrastructure safely and efficiently. A Terraform can manage multiple cloud services using a provider plugin  Terraform work flow? Terraform work flow will be comprised of validate -> plan -> apply and finally destroy. what is the important file of terraform which records the actions of Terraform apply? For every Terraform execution the action will be recorded in a file called Terraform state file .  The terraform init command is used to initialise a working directory containing Terraform configuration files. During init, the configuration is searched for module blocks, and the source code for referenced modules is retrieved from the locations given in  their source arguments. The terraform plan command is used to create an execution plan. It will not modify things in infrastructure. The terraform apply command is used to apply the changes required to reach the desired state of the configur...
Read more

How to make CRS and ASM not to restart after server reboot

-
I have noticed that server rebooting is taking lot of time on a VM machine for every reboot, so I have decided to auto start of the crs and ASM service During this phase I have noticed an attribute called auto_start when I try to execute the below command crsctl stat res -p This AUTO_START attribute is there for several resources like ora.ASM ,ora.crs,ora.DATA_DG it has got 2 options restore(0) , never(2) and always (1) always (1)  Causes the resource to restart when the node restarts regardless of the resource’s state when the node stopped -restore (0)  Does not start the resource at restart time if it was in an offline state, such as STATE=OFFLINE, TARGET=OFFLINE, when the node stopped. The resource is restored to its state when the node went down. The resource is started only if it was online before and not otherwise. -never (2)  Oracle Clusterware never restarts the resource regardless of the resource’s state when the node is stopped. So I have dec...
Read more

How to repair ASM disk header

-
Image
What is ASM disk header corruption? ASM disk header corruption is a loss or damage to the disk header which contains the metadata essential for the operation and availability of an ASM disk group in Oracle. Imagine that you have an ASM disk corruption in any of the ASM diskgroups that have critical business data and the diskgroup has been configured as "EXTERNAL" redundancy, meaning that if one disk is inaccessible the whole diskgroup will be unavailable. Now what? Is your only option to restore it from backup by recreating the diskgroup or are there any other faster ways to recover the data? Here you need to find out whether it’s a complete loss of data in the ASM disk or is it just a header corruption. Hopefully for you, this is just a header corruption. Because, yes, there is a way out!  Below find the steps to restore from the ASM header corruption. Most of the data in the ASM disk header is of interest to that disk only. However, some information in the ASM disk header ...
Read more