Clone PROD database to HOME1 database on same server using RMAN Backup.
1. BACKUP YOUR DATABASE PLUS CONTROLFILE AND ARCHIVELOGS ON SOURCE SERVER
C:\Windows\system32>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 17 01:23:24 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=205599179)
Check if controlfile autobackup is not on configure rman to take controlfile auto backup
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
=============================
RMAN> backup database plus archivelog;
==============================
2. UPDATE LISTENER.ORA AND TNSNAMES.ORA FILES
Add entries for both Source (PROD) and clone (HOME) databases in Listener.ora and tnsnames.ora
listener.ora
==============================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod)
(ORACLE_HOME = E:\app\farhat\product\11.2.0\dbhome_1)
(SID_NAME =prod)
)
(SID_DESC =
(GLOBAL_DBNAME = HOME1)
(ORACLE_HOME = E:\app\farhat\product\11.2.0\dbhome_1)
(SID_NAME =HOME1)
)
)
========================
tnsnames.ora
========================
PROD =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=farhat-PC)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PROD)
(INSTANCE_NAME=PROD)
)
)
HOME1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = farhat-PC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HOME1)
)
)
========================
3. CREATE PARAMETERFILE FOR CLONE (HOME1) DATABASE
create a dummy parameter file to open clone (HOME1) database in nomount state. add paths for controlfiles of clone database and source and destination paths information for datafiles and redologs.
db_name = HOME1
db_block_size = 8192
compatible = 11.2.0.0.0
remote_login_passwordfile = exclusive
control_files = 'E:\app\farhat\oradata\HOME1\CONTROL01.CTL',
'E:\app\farhat\flash_recovery_area\HOME1\CONTROL02.CTL'
db_file_name_convert = 'E:\APP\FARHAT\ORADATA\PROD',
'E:\APP\FARHAT\ORADATA\HOME1'
log_file_name_convert = 'E:\APP\FARHAT\ORADATA\PROD',
'E:\APP\FARHAT\ORADATA\HOME1'
==============================
4. CREATE WINDOWS SERVICE FOR CLONE(HOME1) DATABASE
C:\Windows\system32>oradim -new -sid HOME1
Instance created.
==============================
5. CONNECT TO CLONE (HOME1) DATABASE AND START IN NOMOUNT STATE
C:\Windows\system32>set oracle_sid=HOME1
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 17 01:02:05 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile =E:\app\farhat\oradata\HOME1\initHOME1.ora
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL>
================================
6. START RMAN AND CONNECT TO BOTH SOURCE (PROD) AND CLONE (HOME1) DATABASE
C:\Users\farhat>rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 17 01:45:51 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target
connected to target database: PROD (DBID=205599179)
RMAN> connect auxiliary sys/oracle@HOME1
connected to auxiliary database: HOME1 (not mounted)
================================
7. RUN DUPLICATE COMMAND
RMAN> duplicate target database to HOME1;
================================
Your job finished here, let rman do everything from here onward It will start cloning process and complete it automatically. below is rman output generated during this process just for information.
RMAN OUTPUT
===============================
Starting Duplicate Db at 17-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5 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 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 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 =
''HOME1'' 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=s
pfile
sql statement: alter system set db_unique_name = ''HOME1'' comment= ''Modified by RMAN duplicate''
scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
Starting restore at 17-OCT-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 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 E:\APP\FARHAT\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\
2012_10_17\O1_MF_S_796896748_87WHKXJC_.BKP
channel ORA_AUX_DISK_1: piece handle=E:\APP\FARHAT\FLASH_RECOVERY_AREA\PROD\AUTOBACKUP\2012_10_17\O1
_MF_S_796896748_87WHKXJC_.BKP tag=TAG20121017T081228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=E:\APP\FARHAT\ORADATA\HOME1\CONTROL01.CTL
output file name=E:\APP\FARHAT\FLASH_RECOVERY_AREA\HOME1\CONTROL02.CTL
Finished restore at 17-OCT-12
database mounted
contents of Memory Script:
{
set until scn 1941837;
set newname for datafile 1 to
"E:\APP\FARHAT\ORADATA\HOME1\SYSTEM01.DBF";
set newname for datafile 2 to
"E:\APP\FARHAT\ORADATA\HOME1\SYSAUX01.DBF";
set newname for datafile 3 to
"E:\APP\FARHAT\ORADATA\HOME1\UNDOTBS01.DBF";
set newname for datafile 4 to
"E:\APP\FARHAT\ORADATA\HOME1\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 17-OCT-12
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 E:\APP\FARHAT\ORADATA\HOME1\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to E:\APP\FARHAT\ORADATA\HOME1\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to E:\APP\FARHAT\ORADATA\HOME1\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to E:\APP\FARHAT\ORADATA\HOME1\USERS01.DBF
channel ORA_AUX_DISK_1: reading from backup piece E:\APP\FARHAT\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2
012_10_17\O1_MF_NNNDF_TAG20121017T081141_87WHJFQW_.BKP
channel ORA_AUX_DISK_1: piece handle=E:\APP\FARHAT\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2012_10_17\O1_
MF_NNNDF_TAG20121017T081141_87WHJFQW_.BKP tag=TAG20121017T081141
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 17-OCT-12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=796897550 file name=E:\APP\FARHAT\ORADATA\HOME1\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=796897550 file name=E:\APP\FARHAT\ORADATA\HOME1\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=796897550 file name=E:\APP\FARHAT\ORADATA\HOME1\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=796897550 file name=E:\APP\FARHAT\ORADATA\HOME1\USERS01.DBF
contents of Memory Script:
{
set until scn 1941837;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-OCT-12
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 77 is already on disk as file E:\APP\FARHAT\FLASH_RECOVERY_A
REA\PROD\ARCHIVELOG\2012_10_17\O1_MF_1_77_87WHKTOW_.ARC
archived log file name=E:\APP\FARHAT\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2012_10_17\O1_MF_1_77_87WHK
TOW_.ARC thread=1 sequence=77
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-OCT-12
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''HOME1'' 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 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
sql statement: alter system set db_name = ''HOME1'' comment= ''Reset to original value by RMAN'' s
cope=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 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "HOME1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'E:\APP\FARHAT\ORADATA\HOME1\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'E:\APP\FARHAT\ORADATA\HOME1\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'E:\APP\FARHAT\ORADATA\HOME1\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'E:\APP\FARHAT\ORADATA\HOME1\SYSTEM01.DBF'
CHARACTER SET AR8MSWIN1256
contents of Memory Script:
{
set newname for tempfile 1 to
"E:\APP\FARHAT\ORADATA\HOME1\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "E:\APP\FARHAT\ORADATA\HOME1\SYSAUX01.DBF",
"E:\APP\FARHAT\ORADATA\HOME1\UNDOTBS01.DBF",
"E:\APP\FARHAT\ORADATA\HOME1\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to E:\APP\FARHAT\ORADATA\HOME1\TEMP01.DBF in control file
cataloged datafile copy
datafile copy file name=E:\APP\FARHAT\ORADATA\HOME1\SYSAUX01.DBF RECID=1 STAMP=796897589
cataloged datafile copy
datafile copy file name=E:\APP\FARHAT\ORADATA\HOME1\UNDOTBS01.DBF RECID=2 STAMP=796897589
cataloged datafile copy
datafile copy file name=E:\APP\FARHAT\ORADATA\HOME1\USERS01.DBF RECID=3 STAMP=796897589
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=796897589 file name=E:\APP\FARHAT\ORADATA\HOME1\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=796897589 file name=E:\APP\FARHAT\ORADATA\HOME1\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=796897589 file name=E:\APP\FARHAT\ORADATA\HOME1\USERS01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 17-OCT-12
RMAN>
===============
No comments:
Post a Comment