OS details of Both
node:-
Linux
DB11G.example.com 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:48 EDT 2009 x86_64
x86_64 x86_64 GNU/Linux
Red Hat Enterprise
Linux Server release 5.4 (Tikanga)
DB name:
SQL> select name
from v$database;
NAME
---------
DB11G
Node1 hostname:
DB11G.example.com
Node2 hostname:
ASM_DB.example.com
step1)
create pfile for
spfile for duplicate database.
SQL> create pfile
from spfile;
File created.
step2)
make the following
changes in pfile.
I have already
created 2 ASM diskgroups DATA and FRA.
*.control_files=(+DATA,
+FRA)
*.db_recovery_file_dest=+FRA
*.db_recovery_file_dest_size=2147483648
*.db_create_file_dest=+DATA
*.db_create_online_log_dest_1=+FRA
*.db_create_online_log_dest_2=+DATA
-- optional if you want another online redo logs dest.
move password file
and pfile to target location.
[oracle@DB11G dbs]$
scp initDB11G.ora oracle@ASM_DB:/u01/app/oracle/product/11.2.0/db_1/dbs/
oracle@ASM_DB's
password:
initDB11G.ora 100%
997 1.0KB/s 00:00
[oracle@DB11G dbs]$
scp orapwDB11G oracle@ASM_DB:/u01/app/oracle/product/11.2.0/db_1/dbs/
oracle@ASM_DB's
password:
orapwDB11G 100% 1536
1.5KB/s 00:00
[oracle@DB11G dbs]$
step3)
Create directory
structure, same as DB11Gory database.
---------optional
$ mkdir -p
/u01/app/oracle/oradata/DB11G
$ mkdir -p
/u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p
/u01/app/oracle/admin/DB11G/adump
step4)
tnsname.ora on primary database server(DB11G.example.com)
# Generated by
Oracle configuration tools.
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
DB11G.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
DB11G_tar =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ASM_DB.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
(UR = A)
)
)
step5)
Start duplicate
(tareget) database in nomount.
[oracle@ASM_DB
admin]$ export $ORACLE_SID=DB11G
[oracle@ASM_DB
admin]$ echo $ORACLE_SID
DB11G
[oracle@ASM_DB
admin]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Fri Apr 1 21:50:36 2016
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as
sysdba
Connected to an idle
instance.
SQL> startup
nomount;
ORACLE instance
started.
Total System Global
Area 839282688 bytes
Fixed Size 2233000
bytes
Variable Size
503319896 bytes
Database Buffers
331350016 bytes
Redo Buffers 2379776
bytes
SQL> exit
step6)
-->connect rman
using target and auxilary command.
[oracle@DB11G
admin]$ rman TARGET sys/*****@DB11G AUXILIARY sys/*****@DB11G_tar
Recovery Manager:
Release 11.2.0.3.0 - Production on Mon Feb 20 05:22:22 2017
Copyright (c) 1982,
2011, Oracle and/or its affiliates. All
rights reserved.
connected to target
database: DB11G (DBID=405647198)
connected to
auxiliary database (not started)
RMAN> DUPLICATE
DATABASE TO DB11G
FROM ACTIVE DATABASE
NOFILENAMECHECK;2>
3>
Starting Duplicate
Db at 20-FEB-17
using target
database control file instead of recovery catalog
allocated channel:
ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=25 device type=DISK
contents of Memory
Script:
{
sql clone "alter system set control_files =
''+DATA/db11g/controlfile/current.256.936422163'',
''+FRA/db11g/controlfile/current.256.936422163'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''DB11G'' comment=
''Modified by RMAN duplicate''
scope=spfile";
sql clone "alter system set db_unique_name =
''DB11G'' comment=
''Modified by RMAN duplicate''
scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary
format
'+DATA/db11g/controlfile/current.256.936422163';
restore clone controlfile to
'+FRA/db11g/controlfile/current.256.936422163' from
'+DATA/db11g/controlfile/current.256.936422163';
sql clone "alter system set control_files =
''+DATA/db11g/controlfile/current.256.936422163'',
''+FRA/db11g/controlfile/current.256.936422163'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory
Script
sql statement: alter
system set control_files =
''+DATA/db11g/controlfile/current.256.936422163'',
''+FRA/db11g/controlfile/current.256.936422163'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter
system set db_name = ''DB11G'' comment= ''Modified by RMAN
duplicate'' scope=spfile
sql statement: alter
system set db_unique_name = ''DB11G'' comment= ''Modified by RMAN
duplicate'' scope=spfile
Oracle instance shut
down
Oracle instance
started
Total System Global
Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 503319896 bytes
Database
Buffers 331350016 bytes
Redo Buffers 2379776 bytes
Starting backup at
20-FEB-17
allocated channel:
ORA_DISK_1
channel ORA_DISK_1:
SID=41 device type=DISK
channel ORA_DISK_1:
starting datafile copy
copying current
control file
output file
name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_DB11G.f
tag=TAG20170220T052331 RECID=2 STAMP=936422612
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:03
Finished backup at
20-FEB-17
Starting restore at
20-FEB-17
allocated channel:
ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=23 device type=DISK
channel
ORA_AUX_DISK_1: copied control file copy
Finished restore at
20-FEB-17
sql statement: alter
system set control_files =
''+DATA/db11g/controlfile/current.256.936422163'',
''+FRA/db11g/controlfile/current.256.936422163'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut
down
connected to
auxiliary database (not started)
Oracle instance
started
Total System Global
Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 503319896 bytes
Database
Buffers 331350016 bytes
Redo Buffers 2379776 bytes
database mounted
contents of Memory
Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
backup as copy reuse
datafile
1 auxiliary format new
datafile
2 auxiliary format new
datafile
3 auxiliary format new
datafile
4 auxiliary format new
datafile
5 auxiliary format new
;
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
20-FEB-17
using channel
ORA_DISK_1
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00001 name=/u01/app/oracle/oradata/DB11G/system01.dbf
output file
name=+DATA/db11g/datafile/system.258.936422633 tag=TAG20170220T052351
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00002 name=/u01/app/oracle/oradata/DB11G/sysaux01.dbf
output file
name=+DATA/db11g/datafile/sysaux.259.936422679 tag=TAG20170220T052351
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00005 name=/u01/app/oracle/oradata/DB11G/example01.dbf
output file
name=+DATA/db11g/datafile/example.260.936422703 tag=TAG20170220T052351
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00003 name=/u01/app/oracle/oradata/DB11G/undotbs01.dbf
output file
name=+DATA/db11g/datafile/undotbs1.261.936422719 tag=TAG20170220T052351
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00004 name=/u01/app/oracle/oradata/DB11G/users01.dbf
output file
name=+DATA/db11g/datafile/users.262.936422721 tag=TAG20170220T052351
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:01
Finished backup at
20-FEB-17
sql statement: alter
system archive log current
contents of Memory
Script:
{
backup as copy reuse
archivelog like "/u01/archive/1_6_935977633.arc"
auxiliary format
"/u01/archive/1_6_935977633.arc" ;
catalog clone archivelog "/u01/archive/1_6_935977633.arc";
switch clone datafile all;
}
executing Memory
Script
Starting backup at
20-FEB-17
using channel
ORA_DISK_1
channel ORA_DISK_1:
starting archived log copy
input archived log
thread=1 sequence=6 RECID=4 STAMP=936422722
output file
name=/u01/archive/1_6_935977633.arc RECID=0 STAMP=0
channel ORA_DISK_1:
archived log copy complete, elapsed time: 00:00:03
Finished backup at
20-FEB-17
cataloged archived
log
archived log file
name=/u01/archive/1_6_935977633.arc RECID=4 STAMP=936422727
datafile 1 switched
to datafile copy
input datafile copy
RECID=2 STAMP=936422727 file name=+DATA/db11g/datafile/system.258.936422633
datafile 2 switched
to datafile copy
input datafile copy
RECID=3 STAMP=936422727 file name=+DATA/db11g/datafile/sysaux.259.936422679
datafile 3 switched
to datafile copy
input datafile copy
RECID=4 STAMP=936422727 file name=+DATA/db11g/datafile/undotbs1.261.936422719
datafile 4 switched
to datafile copy
input datafile copy
RECID=5 STAMP=936422727 file name=+DATA/db11g/datafile/users.262.936422721
datafile 5 switched
to datafile copy
input datafile copy
RECID=6 STAMP=936422727 file name=+DATA/db11g/datafile/example.260.936422703
contents of Memory
Script:
{
set until scn 1076176;
recover
clone database
delete archivelog
;
}
executing Memory
Script
executing command:
SET until clause
Starting recover at
20-FEB-17
allocated channel:
ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=23 device type=DISK
starting media
recovery
archived log for
thread 1 with sequence 6 is already on disk as file
/u01/archive/1_6_935977633.arc
archived log file
name=/u01/archive/1_6_935977633.arc thread=1 sequence=6
media recovery
complete, elapsed time: 00:00:01
Finished recover at
20-FEB-17
Oracle instance
started
Total System Global
Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 503319896 bytes
Database
Buffers 331350016 bytes
Redo Buffers 2379776 bytes
contents of Memory
Script:
{
sql clone "alter system set db_name =
''DB11G'' 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
sql statement: alter
system set db_name = ''DB11G'' 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 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 503319896 bytes
Database
Buffers 331350016 bytes
Redo Buffers 2379776 bytes
sql statement:
CREATE CONTROLFILE REUSE SET DATABASE "DB11G" 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
'+DATA/db11g/datafile/system.258.936422633'
CHARACTER SET WE8MSWIN1252
contents of Memory
Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy
"+DATA/db11g/datafile/sysaux.259.936422679",
"+DATA/db11g/datafile/undotbs1.261.936422719",
"+DATA/db11g/datafile/users.262.936422721",
"+DATA/db11g/datafile/example.260.936422703";
switch clone datafile all;
}
executing Memory
Script
executing command:
SET NEWNAME
renamed tempfile 1
to +DATA in control file
cataloged datafile
copy
datafile copy file
name=+DATA/db11g/datafile/sysaux.259.936422679 RECID=1 STAMP=936422745
cataloged datafile
copy
datafile copy file
name=+DATA/db11g/datafile/undotbs1.261.936422719 RECID=2 STAMP=936422745
cataloged datafile
copy
datafile copy file
name=+DATA/db11g/datafile/users.262.936422721 RECID=3 STAMP=936422745
cataloged datafile
copy
datafile copy file
name=+DATA/db11g/datafile/example.260.936422703 RECID=4 STAMP=936422745
datafile 2 switched
to datafile copy
input datafile copy
RECID=1 STAMP=936422745 file name=+DATA/db11g/datafile/sysaux.259.936422679
datafile 3 switched
to datafile copy
input datafile copy
RECID=2 STAMP=936422745 file name=+DATA/db11g/datafile/undotbs1.261.936422719
datafile 4 switched
to datafile copy
input datafile copy
RECID=3 STAMP=936422745 file name=+DATA/db11g/datafile/users.262.936422721
datafile 5 switched
to datafile copy
input datafile copy
RECID=4 STAMP=936422745 file name=+DATA/db11g/datafile/example.260.936422703
contents of Memory
Script:
{
Alter clone database open resetlogs;
}
executing Memory
Script
database opened
Finished Duplicate
Db at 20-FEB-17
RMAN> exit
step7)
check ASM RDBMS
[oracle@ASM_DB ~]$
sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Mon Feb 20 05:27:39 2017
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as
sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the
Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application
Testing options
SQL> select name
from v$database;
NAME
---------
DB11G
SQL> select
FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/db11g/datafile/system.258.936422633
+DATA/db11g/datafile/sysaux.259.936422679
+DATA/db11g/datafile/undotbs1.261.936422719
+DATA/db11g/datafile/users.262.936422721
+DATA/db11g/datafile/example.260.936422703
SQL>
