How to clone the database using RMAN with out active duplicate in same host

-

This post demonstrates on how we can clone a database from existing database in same server

Details:
=====
Source Database name : Sales
Clone Database name : Test

Step 1: Take full backup of source database  using RMAN

Step 2: Create password file for the clone database Test

Step 3: Now copy the parameter file of source database and prepare accordingly for clone db

Step 4: Configure the listener configurations

Step 5:  Now set the environment to clone SID and the start up in nomount mode

Step 6: Login to RMAN utility with source /catalog/auxiliary database

RMAN> backup database plus archivelog;


Starting backup at 05-APR-19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 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=13 RECID=17 STAMP=1004795165
channel ORA_DISK_1: starting piece 1 at 05-APR-19
channel ORA_DISK_1: finished piece 1 at 05-APR-19
piece handle=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\BACKUPSET\2019_04_05\O1_MF_ANNNN_TAG20190405T180715_GBGLZVTS_.BKP tag=TAG20190405T180715 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=1 RECID=19 STAMP=1004810833
channel ORA_DISK_1: starting piece 1 at 05-APR-19
channel ORA_DISK_1: finished piece 1 at 05-APR-19
piece handle=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\BACKUPSET\2019_04_05\O1_MF_ANNNN_TAG20190405T180715_GBGLZX3Y_.BKP tag=TAG20190405T180715 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-APR-19

Starting backup at 05-APR-19
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=00004 name=C:\APP\SL38\ORADATA\SALES\USERS01.DBF
input datafile file number=00001 name=C:\APP\SL38\ORADATA\SALES\SYSTEM01.DBF
input datafile file number=00003 name=C:\APP\SL38\ORADATA\SALES\UNDOTBS01.DBF
input datafile file number=00002 name=C:\APP\SL38\ORADATA\SALES\SYSAUX01.DAT
channel ORA_DISK_1: starting piece 1 at 05-APR-19
channel ORA_DISK_1: finished piece 1 at 05-APR-19
piece handle=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\BACKUPSET\2019_04_05\O1_MF_NNNDF_TAG20190405T180718_GBGLZYRJ_.BKP tag=TAG20190405T180718 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 05-APR-19

Starting backup at 05-APR-19
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=2 RECID=20 STAMP=1004810846
channel ORA_DISK_1: starting piece 1 at 05-APR-19
channel ORA_DISK_1: finished piece 1 at 05-APR-19
piece handle=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\BACKUPSET\2019_04_05\O1_MF_ANNNN_TAG20190405T180727_GBGM07O7_.BKP tag=TAG20190405T180727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-APR-19

Starting Control File and SPFILE Autobackup at 05-APR-19
piece handle=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\AUTOBACKUP\2019_04_05\O1_MF_S_1004810850_GBGM0BLH_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 05-APR-19

RMAN> exit


Recovery Manager complete.

C:\Users\sl38>rman target sys/oracle123@sales catalog ruser/ruser@rmandb auxiliary sys/oracle123@test

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 5 18:09:09 2019

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

connected to target database: SALES (DBID=877926465)
connected to recovery catalog database
connected to auxiliary database: TEST (not mounted)

RMAN> DUPLICATE DATABASE TO "test";

Starting Duplicate Db at 05-APR-19
allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=129 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''SALES'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    3173429248 bytes

Fixed Size                     2179936 bytes
Variable Size               1728056480 bytes
Database Buffers            1426063360 bytes
Redo Buffers                  17129472 bytes

Starting restore at 05-APR-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\AUTOBACKUP\2019_04_05\O1_MF_S_1004810850_GBGM0BLH_.BKP
channel ORA_AUX_DISK_1: piece handle=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\AUTOBACKUP\2019_04_05\O1_MF_S_1004810850_GBGM0BLH_.BKP tag=TAG20190405T180730
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=C:\APP\SL38\ORADATA\TEST\CONTROL01.CTL
output file name=C:\APP\SL38\ORADATA\TEST\CONTROL02.CTL
Finished restore at 05-APR-19

database mounted

contents of Memory Script:
{
   set until scn  255469;
   set newname for datafile  1 to
 "C:\APP\SL38\ORADATA\TEST\SYSTEM01.DBF";
   set newname for datafile  2 to
 "C:\APP\SL38\ORADATA\TEST\SYSAUX01.DAT";
   set newname for datafile  3 to
 "C:\APP\SL38\ORADATA\TEST\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "C:\APP\SL38\ORADATA\TEST\USERS01.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

Starting restore at 05-APR-19
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 C:\APP\SL38\ORADATA\TEST\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to C:\APP\SL38\ORADATA\TEST\SYSAUX01.DAT
channel ORA_AUX_DISK_1: restoring datafile 00003 to C:\APP\SL38\ORADATA\TEST\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to C:\APP\SL38\ORADATA\TEST\USERS01.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\BACKUPSET\2019_04_05\O1_MF_NNNDF_TAG20190405T180718_GBGLZYRJ_.BKP
channel ORA_AUX_DISK_1: piece handle=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\BACKUPSET\2019_04_05\O1_MF_NNNDF_TAG20190405T180718_GBGLZYRJ_.BKP tag=TAG20190405T180718
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 05-APR-19

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=1004811016 file name=C:\APP\SL38\ORADATA\TEST\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=1004811017 file name=C:\APP\SL38\ORADATA\TEST\SYSAUX01.DAT
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1004811017 file name=C:\APP\SL38\ORADATA\TEST\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1004811017 file name=C:\APP\SL38\ORADATA\TEST\USERS01.DBF

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

executing command: SET until clause

Starting recover at 05-APR-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\ARCHIVELOG\2019_04_05\O1_MF_1_2_GBGM05Z4_.ARC
archived log file name=C:\APP\SL38\FLASH_RECOVERY_AREA\SALES\ARCHIVELOG\2019_04_05\O1_MF_1_2_GBGM05Z4_.ARC thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 05-APR-19

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

Fixed Size                     2179936 bytes
Variable Size               1728056480 bytes
Database Buffers            1426063360 bytes
Redo Buffers                  17129472 bytes

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

Fixed Size                     2179936 bytes
Variable Size               1728056480 bytes
Database Buffers            1426063360 bytes
Redo Buffers                  17129472 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
  MAXLOGFILES     32
  MAXLOGMEMBERS      4
  MAXDATAFILES      254
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( 'C:\APP\SL38\ORADATA\TEST\REDO01A.LOG', 'C:\APP\SL38\ORADATA\TEST\REDO01B.LOG' ) SIZE 100 M  REUSE,
  GROUP  2 ( 'C:\APP\SL38\ORADATA\TEST\REDO02A.LOG', 'C:\APP\SL38\ORADATA\TEST\REDO02B.LOG' ) SIZE 100 M  REUSE,
  GROUP  3 ( 'C:\APP\SL38\ORADATA\TEST\REDO03A.LOG', 'C:\APP\SL38\ORADATA\TEST\REDO03B.LOG' ) SIZE 100 M  REUSE
 DATAFILE
  'C:\APP\SL38\ORADATA\TEST\SYSTEM01.DBF'
 CHARACTER SET US7ASCII
contents of Memory Script:
{
   set newname for tempfile  1 to
 "C:\APP\SL38\ORADATA\TEST\TEMP01.DBF";
   switch clone tempfile all;
   catalog clone datafilecopy  "C:\APP\SL38\ORADATA\TEST\SYSAUX01.DAT",
 "C:\APP\SL38\ORADATA\TEST\UNDOTBS01.DBF",
 "C:\APP\SL38\ORADATA\TEST\USERS01.DBF";
   switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME

renamed tempfile 1 to C:\APP\SL38\ORADATA\TEST\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=C:\APP\SL38\ORADATA\TEST\SYSAUX01.DAT RECID=1 STAMP=1004811044
cataloged datafile copy
datafile copy file name=C:\APP\SL38\ORADATA\TEST\UNDOTBS01.DBF RECID=2 STAMP=1004811044
cataloged datafile copy
datafile copy file name=C:\APP\SL38\ORADATA\TEST\USERS01.DBF RECID=3 STAMP=1004811044

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1004811044 file name=C:\APP\SL38\ORADATA\TEST\SYSAUX01.DAT
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1004811044 file name=C:\APP\SL38\ORADATA\TEST\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1004811044 file name=C:\APP\SL38\ORADATA\TEST\USERS01.DBF
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 05-APR-19
RMAN>

Step 7: now edit the parameter file and remove the below entries and the create spfile


DB_FILE_NAME_CONVERT='C:\APP\SL38\ORADATA\SALES\','C:\APP\SL38\ORADATA\TEST\'
LOG_FILE_NAME_CONVERT='C:\APP\SL38\ORADATA\SALES\','C:\APP\SL38\ORADATA\TEST\'

C:\WINDOWS\system32>sqlplus sys/oracle123@test as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 5 18:20:32 2019
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup pfile=C:\app\sl38\admin\test\pfile\init.ora nomount;
ORACLE instance started.
Total System Global Area 3173429248 bytes
Fixed Size                  2179936 bytes
Variable Size            1728056480 bytes
Database Buffers         1426063360 bytes
Redo Buffers               17129472 bytes
SQL> create spfile from pfile='C:\app\sl38\admin\test\pfile\init.ora';
File created.
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.

Step 8: Now your clone database is ready.


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