Recover duplicate a production database on a different server

  • Anuradha Mudgal

In the following scenario, We are going to learn about those steps which use to recover duplicate a production database on a different server maintaining the same SID and directory structures with ‘duplicate‘ RMAN command I simply want to have a copy of my production database to a different server preserving the same database directory structures and even the same database name.

My production database is running on vsi08 server and its system id (ORACLE_SID) is PROD; the remote server is vsi10 and I want to duplicate here the PROD database currently available on vsi08.

Usually, when I install a new database I try to save all the related scripts to recreate it: it’s just another checkbox during a typical dbca installation.

On the local vsi08 server I can find those scripts located at $ORACLE_BASE/admin/’db_name’/scripts, that is:

[oracle@vsi08 ~]$ cd /opt/app/oracle/admin/PROD/scripts
[oracle@vsi08 scripts]$ ll
total 52
-rw-r----- 1 oracle oinstall 234 Mar 27 13:41 apex.sql
-rw-r----- 1 oracle oinstall 756 Mar 27 13:41 CreateDBCatalog.sql
-rw-r----- 1 oracle oinstall 396 Mar 27 13:41 CreateDBFiles.sql
-rw-r----- 1 oracle oinstall 1219 Mar 27 13:41 CreateDB.sql
-rw-r----- 1 oracle oinstall 1916 Mar 27 13:41 init.ora
-rw-r----- 1 oracle oinstall 202 Mar 27 13:41 interMedia.sql
-rw-r----- 1 oracle oinstall 443 Mar 27 13:41 JServer.sql
-rw-r----- 1 oracle oinstall 507 Mar 27 13:41 lockAccount.sql
-rw-r----- 1 oracle oinstall 211 Mar 27 13:41 ordinst.sql
-rw-r----- 1 oracle oinstall 786 Mar 27 13:41 postDBCreation.sql
-rwxr-xr-x 1 oracle oinstall 685 Mar 27 13:41 PROD.sh
-rwxr-xr-x 1 oracle oinstall 781 Mar 27 13:41 PROD.sql
-rw-r----- 1 oracle oinstall 395 Mar 27 13:41 xdb_protocol.sql

If you look at the content of the unique sh file you can verify it creates few directories. I want to use the content of that file to create the same database directory structures on the remote server.

[oracle@vsi08 scripts]$ more PROD.sh
#!/bin/sh

OLD_UMASK=`umask`
umask 0027
mkdir -p /opt/app/oracle/admin/PROD/adump
mkdir -p /opt/app/oracle/admin/PROD/dpdump
mkdir -p /opt/app/oracle/admin/PROD/pfile
mkdir -p /opt/app/oracle/cfgtoollogs/dbca/PROD
mkdir -p /opt/app/oracle/flash_recovery_area
mkdir -p /opt/app/oracle/flash_recovery_area/PROD
mkdir -p /opt/app/oracle/oradata/PROD
mkdir -p /opt/app/oracle/product/11.2.0/db_1/dbs
umask ${OLD_UMASK}
ORACLE_SID=PROD; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH

You should Add this entry in the /etc/oratab:

PROD:/opt/app/oracle/product/11.2.0/db_1:Y
/opt/app/oracle/product/11.2.0/db_1/bin/sqlplus /nolog

@/opt/app/oracle/admin/PROD/scripts/PROD.sql
So on remote vsi10 server I recreate the same directory structures of vsi08:

[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/admin/PROD/adump
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/admin/PROD/dpdump
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/admin/PROD/pfile
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/cfgtoollogs/dbca/PROD
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/flash_recovery_area
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/flash_recovery_area/PROD
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/oradata/PROD
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/product/11.2.0/db_1/dbs

Next step is to create a pfile from the current PROD database located on vsi08 server and transfer that copy to vsi10 server:

[oracle@vsi08 pfile]$ sqlplus / as sysdba
SQL> create pfile='/tmp/initPROD_20130328.txt' from spfile;

File created.
[oracle@vsi08 pfile]$ scp /tmp/initPROD_20130328.txt vsi10.mydomain.it:/opt/app/oracle/admin/PROD/pfile
oracle@vsi10.mydomain.it's password:
initPROD_20130328.txt 100% 944 0.9KB/s 00:00

Now it’s time to create a tns entry for vsi10 server referencing PROD database at vsi08 server. I first have a look at the current tns entry on the local vsi08 server…

[oracle@vsi08 admin]$ pwd
/opt/app/oracle/product/11.2.0/db_1/network/admin
[oracle@vsi08 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vsi08.mydomain.it)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)

… and then, using a simple copy-and-paste operation, I can create the new one on tnsnames.ora file of vsi10 server.
I modify only the reference of the tns entry from PROD to PROD_AT_VSI08 because I really want to remember I’m connecting to PROD database located at vsi08 server:

[oracle@vsi10 ~]$ cd $ORACLE_HOME
[oracle@vsi10 db_1]$ cd network/admin/
[oracle@vsi10 admin]$ vi tnsnames.ora
[oracle@vsi10 admin]$ cat tnsnames.ora
PROD_AT_VSI08 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vsi08.mydomain.it)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)

After the creation of PROD_AT_VSI08 tns entry I want to test it and try to connect from vsi10 server to PROD database on vsi08 server.
Before proceeding I have to successfully establish a connection otherwise my attempt to duplicate PROD database on vsi10 server won’t work.

[oracle@vsi10 admin]$ sqlplus sys/oracle@PROD_AT_VSI08 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 28 12:37:34 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

Ok… I’m able to connect to PROD database at vsi08 server and now it’s time to have a complete backup of the database I want to duplicate. So from vsi08 server I set the following environment settings, backing up the database and its archivelogs. RMAN is configured using autobackup on so it will perform also a backup of the current control file and spfile:

[oracle@vsi08 ~]$ export NLS_DATE_FORMAT='DD-MM-RRRR HH24:MI:SS'
[oracle@vsi08 ~]$ export ORACLE_SID=PROD
[oracle@vsi08 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 28 10:49:42 2013

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

connected to target database: PROD (DBID=223010867)

RMAN> backup database plus archivelog;

Starting backup at 28-03-2013 10:52:07
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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=50 RECID=3 STAMP=811248727
channel ORA_DISK_1: starting piece 1 at 28-03-2013 10:52:07
channel ORA_DISK_1: finished piece 1 at 28-03-2013 10:52:08
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_annnn_TAG20130328T105207_8o84p7o6_.bkp tag=TAG20130328T105207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-03-2013 10:52:08

Starting backup at 28-03-2013 10:52:08
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=/opt/app/oracle/oradata/PROD/system01.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
input datafile file number=00004 name=/opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-03-2013 10:52:08
channel ORA_DISK_1: finished piece 1 at 28-03-2013 10:52:33
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T105208_8o84p8vq_.bkp tag=TAG20130328T105208 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 28-03-2013 10:52:33

Starting backup at 28-03-2013 10:52:33
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=51 RECID=4 STAMP=811248753
channel ORA_DISK_1: starting piece 1 at 28-03-2013 10:52:34
channel ORA_DISK_1: finished piece 1 at 28-03-2013 10:52:35
piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_annnn_TAG20130328T105233_8o84q231_.bkp tag=TAG20130328T105233 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-03-2013 10:52:35

Starting Control File and SPFILE Autobackup at 28-03-2013 10:52:35
piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/o1_mf_s_811248755_8o84q3cz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-03-2013 10:52:36

The current backup pieces are located into two directories:

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
5 3.00K DISK 00:00:00 28-03-2013 10:52:07
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20130328T105207
Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_annnn_TAG20130328T105207_8o84p7o6_.bkp

List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 50 730126 28-03-2013 10:48:33 730283 28-03-2013 10:52:07

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
6 Full 725.99M DISK 00:00:17 28-03-2013 10:52:25
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20130328T105208
Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T105208_8o84p8vq_.bkp
List of Datafiles in backup set 6

File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 730292 28-03-2013 10:52:08 /opt/app/oracle/oradata/PROD/system01.dbf
2 Full 730292 28-03-2013 10:52:08 /opt/app/oracle/oradata/PROD/sysaux01.dbf
3 Full 730292 28-03-2013 10:52:08 /opt/app/oracle/oradata/PROD/undotbs01.dbf
4 Full 730292 28-03-2013 10:52:08 /opt/app/oracle/oradata/PROD/users01.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7 2.00K DISK 00:00:00 28-03-2013 10:52:34
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20130328T105233
Piece Name: /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_annnn_TAG20130328T105233_8o84q231_.bkp

List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 51 730283 28-03-2013 10:52:07 730305 28-03-2013 10:52:33

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8 Full 9.36M DISK 00:00:00 28-03-2013 10:52:35
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20130328T105235
Piece Name: /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/o1_mf_s_811248755_8o84q3cz_.bkp
SPFILE Included: Modification time: 28-03-2013 10:45:50
SPFILE db_unique_name: PROD
Control File Included: Ckp SCN: 730314 Ckp time: 28-03-2013 10:52:35

I need to create the same backup directories into vsi10 server:

[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/
[oracle@vsi10 ~]$ mkdir -p /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/

From vsi08 server I copy the backup pieces to vsi10 server into the appropriate directories:

[oracle@vsi08 ~]$ ll /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/
total 744164
-rw-r----- 1 oracle oinstall 3584 Mar 28 10:52 o1_mf_annnn_TAG20130328T105207_8o84p7o6_.bkp
-rw-r----- 1 oracle oinstall 2560 Mar 28 10:52 o1_mf_annnn_TAG20130328T105233_8o84q231_.bkp
-rw-r----- 1 oracle oinstall 761266176 Mar 28 10:52 o1_mf_nnndf_TAG20130328T105208_8o84p8vq_.bkp
[oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/* vsi10.mydomain.it:/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/
oracle@vsi10.mydomain.it's password:
o1_mf_annnn_TAG20130328T105207_8o84p7o6_.bkp 100% 3584 3.5KB/s 00:00
o1_mf_annnn_TAG20130328T105233_8o84q231_.bkp 100% 2560 2.5KB/s 00:00
o1_mf_nnndf_TAG20130328T105208_8o84p8vq_.bkp 100% 726MB 40.3MB/s 00:18
[oracle@vsi08 ~]$ ll /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/
total 9616
-rw-r----- 1 oracle oinstall 9830400 Mar 28 10:52 o1_mf_s_811248755_8o84q3cz_.bkp
[oracle@vsi08 ~]$ scp /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/* vsi10.mydomain.it:/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/
oracle@vsi10.mydomain.it's password:
o1_mf_s_811248755_8o84q3cz_.bkp 100% 9600KB 9.4MB/s 00:00

From the vsi10 machine I need to start the “next” duplicated PROD instance in NOMOUNT mode and with the previously copied pfile.

[oracle@vsi10 admin]$ export ORACLE_SID=PROD
[oracle@vsi10 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 28 12:38:49 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.
SQL> startup nomount pfile='/opt/app/oracle/admin/PROD/pfile/initPROD_20130328.txt'
ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 989857704 bytes
Database Buffers 687865856 bytes
Redo Buffers 6987776 bytes

When the instance is started in NOMOUNT mode I can remotely connect RMAN to PROD database at vsi08 as target connection and to the “next” PROD database at the local server (vsi10) as auxiliary connection:

[oracle@vsi10 admin]$ rman target sys/oracle@PROD_AT_VSI08 auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Mar 28 14:34:25 2013

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

connected to target database: PROD (DBID=223010867)
connected to auxiliary database: PROD (not mounted)

With the following duplicate command I want to restore all the datafiles located into the backup pieces and automatically start the new database on vsi10 server. At the end of the duplicate process I will have a complete copy of my production database with the same directory structures and even the same SID:

RMAN> duplicate target database to PROD nofilenamecheck;

Starting Duplicate Db at 28-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

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

Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 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;
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 1686925312 bytes

Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes

Starting restore at 28-MAR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 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 /opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/o1_mf_s_811248755_8o84q3cz_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/autobackup/2013_03_28/o1_mf_s_811248755_8o84q3cz_.bkp tag=TAG20130328T105235
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/PROD/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/PROD/control02.ctl
Finished restore at 28-MAR-13

database mounted

contents of Memory Script:
{
set until scn 730305;
set newname for datafile 1 to
"/opt/app/oracle/oradata/PROD/system01.dbf";
set newname for datafile 2 to
"/opt/app/oracle/oradata/PROD/sysaux01.dbf";
set newname for datafile 3 to
"/opt/app/oracle/oradata/PROD/undotbs01.dbf";
set newname for datafile 4 to
"/opt/app/oracle/oradata/PROD/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 28-MAR-13
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 /opt/app/oracle/oradata/PROD/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T105208_8o84p8vq_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_nnndf_TAG20130328T105208_8o84p8vq_.bkp tag=TAG20130328T105208
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 28-MAR-13

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

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=811262217 file name=/opt/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=811262217 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=811262217 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=811262217 file name=/opt/app/oracle/oradata/PROD/users01.dbf

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

executing command: SET until clause

Starting recover at 28-MAR-13
using channel ORA_AUX_DISK_1

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=51
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_annnn_TAG20130328T105233_8o84q231_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/flash_recovery_area/PROD/backupset/2013_03_28/o1_mf_annnn_TAG20130328T105233_8o84q231_.bkp tag=TAG20130328T105233
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=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_03_28/o1_mf_1_51_8o8kvv94_.arc thread=1 sequence=51
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/flash_recovery_area/PROD/archivelog/2013_03_28/o1_mf_1_51_8o8kvv94_.arc RECID=5 STAMP=811262219
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-MAR-13

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

Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 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 1686925312 bytes

Fixed Size 2213976 bytes
Variable Size 1006634920 bytes
Database Buffers 671088640 bytes
Redo Buffers 6987776 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/opt/app/oracle/oradata/PROD/system01.dbf'
CHARACTER SET WE8MSWIN1252


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

executing command: SET NEWNAME

renamed tempfile 1 to /opt/app/oracle/oradata/PROD/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf RECID=1 STAMP=811262238
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf RECID=2 STAMP=811262238
cataloged datafile copy
datafile copy file name=/opt/app/oracle/oradata/PROD/users01.dbf RECID=3 STAMP=811262238

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=811262238 file name=/opt/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=811262238 file name=/opt/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=811262238 file name=/opt/app/oracle/oradata/PROD/users01.dbf

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

database opened
Finished Duplicate Db at 28-MAR-13

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

"The fragrance always remains in the hand that gives the rose." I am a person who believes in sharing knowledge among all. My Oracle rewards are OCI, OCP, OCA.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.