Monday, 10 June 2013








Create duplicate database using rman



You want to use RMAN to create a duplicate database on the same server by using RMAN backups.



Source database name is PROD



Target (duplicate) database name is CLONE



Step 1: Configure Listener.ora and tnsnames.ora files



tnsnames.ora file



PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)



CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CLONE)
)
)



listener.ora file



SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = PROD)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = PROD)
)
(SID_DESC =
(SID_NAME = CLONE)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = CLONE)
)
)



LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)



Step 2: create pfile for target database from source database



SQL> create pfile from spfile;



File created.



Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.



*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/CLONE/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’CLONE’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/CLONE/control01.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/CLONE/control02.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/CLONE/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/PROD’,'/home/oracle/oracle/product/10.2.0/oradata/CLONE’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/PROD’,'/home/oracle/oracle/product/10.2.0/oradata/CLONE’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=CLONEXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′



Step 4: Create password file



[oracle@localhost ~]$orapwd file=orapwCLONE password=oracle entries=5



Step 5: start your target database nomount stage



[oracle@localhost ~]$ export ORACLE_SID=CLONE
[oracle@localhost ~]$ rlwrap sqlplus



SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010



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



Enter user-name: sys/oracle@CLONE as sysdba



Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options



SQL> startup nomount pfile=/home/oracle/oracle/product/10.2.0/db_1/dbs/initCLONE.ora
ORACLE instance started.



Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> exit



Step 6: From source database



SQL> alter system switch logfile;



System altered.



SQL>



Step 7: Backup database and archivelog Source database



[oracle@localhost root]$ rlwrap rman target sys/pwd@PROD



Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010



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



connected to target database: PROD (DBID=2419076664)



RMAN> backup database plus archivelog;



Starting backup at 25-MAY-10
current log archived



input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/PROD/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/PROD/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/PROD/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10



(skipped)



Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PROD/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10



Step 8: Connect auxiliary database



RMAN> connect auxiliary sys/oracle@CLONE;



connected to auxiliary database: CLONE (not mounted)



Step 9: Create duplicate database.



RMAN> duplicate target database to CLONE;



Starting Duplicate Db at 25-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK



contents of Memory Script:
{
set until scn 470310;
set newname for datafile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/CLONE/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oracle/product/10.2.0/oradata/CLONE/undotbs01.dbf”;
set newname for datafile 3 to
“/home/oracle/oracle/product/10.2.0/oradata/CLONE/sysaux01.dbf”;
set newname for datafile 4 to
“/home/oracle/oracle/product/10.2.0/oradata/CLONE/users01.dbf”;
restore
check readonly
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 25-MAY-10
using channel ORA_AUX_DISK_1



skipping datafile 1; already restored to file /home/oracle/oracle/product/10.2.0/oradata/CLONE/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/CLONE/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/CLONE/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/CLONE/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CLONE” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/system01.dbf’
CHARACTER SET WE8ISO8859P1



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=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/users01.dbf



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



executing command: SET until clause



Starting recover at 25-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK



starting media recovery



archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PROD/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PROD/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-MAY-10



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



Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CLONE” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/CLONE/system01.dbf’
CHARACTER SET WE8ISO8859P1



contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/CLONE/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/CLONE/undotbs01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/CLONE/sysaux01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/CLONE/users01.dbf”;
switch clone datafile all;
}
executing Memory Script



executing command: SET NEWNAME



renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/CLONE/temp01.dbf in control file



cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/undotbs01.dbf recid=1 stamp=719956593



cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/sysaux01.dbf recid=2 stamp=719956593



cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/users01.dbf recid=3 stamp=719956593



datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/CLONE/users01.dbf



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



database opened
Finished Duplicate Db at 25-MAY-10



RMAN>



Step 10: My duplicate database succesfully created.



Enter user-name: sys/oracle@CLONE as sysdba



Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options



SQL> select status from v$instance;



STATUS
————
OPEN



SQL> select name from V$database;



NAME
———
CLONE



SQL>


Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)