TARGET DATABASE= prod on 10.131.100.148 (NOARCHIVELOG)
AUXILIARY DATABASE= prod on 10.131.100.37
ON TARGET (10.131.100.148)
C:\> rman
RMAN> connect target;
RMAN>backup database; (the location of my bakup files and controlfile is at C:\rman_prod_backup on target)
My oracle home on target machine is D:\oracle\product\10.2.0\db_1
ON AUXILIARY (10.131.100.37)
I install oracle 10g server with starter database
Then I create service(for database) i.e prod
C:\>oradim –new –sid prod –intpwd oracle –startmode auto
NOW I CONFIGURE listener.ora ant tnsnames.ora on auxiliary machine
Listener.ora on auxiliary machine is:-
# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = prod)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = prod)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.100.37)(PORT = 1521))
)
)
tnsnames.ora on auxiliary machine is:-
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.100.148)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
prod1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.131.100.37)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
NOW I COPY ALL MY BACKUP FROM C:\rman_prod_backup on target (10.131.100.148) to auxiliary (10.131.100.37) at C:\rman_prod_backup i.e same location (of backup) on both target and auxiliary.
-Produce a pfile for the new database
On target machine-
SQL> create pfile='C:\initprod.ora' from spfile;
File created.
Edit the parameter file accordingly:-
So ,my pfile i.e to be used for auxiliary database is:
prod.__db_cache_size=176160768
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=96468992
prod.__streams_pool_size=0
audit_file_dest='D:\oracle\product\10.2.0\admin\prod\adump'
background_dump_dest='D:\oracle\product\10.2.0\admin\prod\bdump'
user_dump_dest='D:\oracle\product\10.2.0\admin\prod\udump'
compatible='10.2.0.1.0'
control_files='D:\oracle\product\10.2.0\oradata\prod\control01.ctl','D:\oracle\product\10.2.0\oradata\prod\control02.ctl','D:\oracle\product\10.2.0\oradata\prod\control03.ctl'
core_dump_dest='D:\oracle\product\10.2.0\admin\prod\cdump'
db_block_size=8192
db_cache_size=176160768
db_domain=''
db_file_multiblock_read_count=16
DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\prods\','D:\oracle\product\10.2.0\oradata\prod\'
db_name='prod'
db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
db_recovery_file_dest_size=2147483648
DB_UNIQUE_NAME='prod'
#FAL_CLIENT='prod'
#FAL_SERVER='prods'
global_names=TRUE
INSTANCE_NAME='prod'
java_pool_size=4194304
job_queue_processes=10
large_pool_size=4194304
#LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prods)'
#log_archive_dest_1='location=C:\prod_archives VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
#LOG_ARCHIVE_DEST_2='SERVICE=prods LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prods'
PLACE THE ABOVE PFILE AT D:\oracle\product\10.2.0\db_1\database\ on auxiliary machine (10.131.100.37)
Also, my oracle_home i.e locations of datafiles , controlfiles ,redolog files are same on both machine
i.e D:\oracle\product\10.2.0\db_1
ON AUXILIARY DATABASE:
C:\Windows\system32>set oracle_sid=prod
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 13:53:00 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\initprod.ora';
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 104858304 bytes
Database Buffers 176160768 bytes
Redo Buffers 7139328 bytes
SQL> DISCONNECT sys
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
NOTE: AFTER STARTING MY AUXILIARY DATABASE IN NOMOUNT STATE ,I DISCONNECT SYS AS NO SESSION MUST BE THERE WHILE DUPLICATING A DATABASE.
Now , I run duplicate command on auxiliary machine(10.131.100.37) after connecting through rman at auxiliary machine:-
C:\Windows\system32>rman TARGET sys/oracle@prod AUXILIARY sys/oracle@prod1
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 9 13:55:01 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=174111093)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate TARGET database to 'prod' NOFILENAMECHECK;
Starting Duplicate Db at 09-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script:
{
set newname for datafile 1 to
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF";
set newname for datafile 2 to
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.DBF";
set newname for datafile 3 to
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF";
set newname for datafile 4 to
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA01";
set newname for datafile 5 to
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 09-NOV-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA01
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\RMAN_PROD_BACKUP\BKS_PROD_1
5_1_20111108
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\RMAN_PROD_BACKUP\BKS_PROD_15_1_20111108 tag=TAG20111108T124629
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 09-NOV-11
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS NOARCHIVEL
OG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1752
LOGFILE
GROUP 1 SIZE 10 M ,
GROUP 2 SIZE 10 M ,
GROUP 3 SIZE 10 M
DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\UNDOTBS1.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\SYSAUX.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\SAGARDATA01
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=766763761 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\GGS_DATA01.DBF
contents of Memory Script:
{
recover
clone database
noredo
,
delete archivelog
;
}
executing Memory Script
Starting recover at 09-NOV-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
Finished recover at 09-NOV-11
contents of Memory Script:
{
shutdown clone;
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 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 104858304 bytes
Database Buffers 176160768 bytes
Redo Buffers 7139328 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS NOARCHIVEL
OG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1752
LOGFILE
GROUP 1 SIZE 10 M ,
GROUP 2 SIZE 10 M ,
GROUP 3 SIZE 10 M
DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM.DBF'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEMP.DBF";
switch clone tempfile all;
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.D
BF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF
";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA0
1";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01
.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEMP.DBF in co
ntrol file
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS1.DBF recid=
1 stamp=766763785
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX.DBF recid=2
stamp=766763786
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SAGARDATA01 recid=3
stamp=766763786
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\GGS_DATA01.DBF reci
d=4 stamp=766763786
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=766763785 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\UNDOTBS1.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=766763786 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\SYSAUX.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=766763786 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\SAGARDATA01
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=766763786 filename=D:\ORACLE\PRODUCT\10.2.0\OR
ADATA\PROD\GGS_DATA01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 09-NOV-11
