Wednesday, July 28, 2010
RMAN CLONING CRMPS2 DB TO CRM1JT DB
Source db : CRMPS2
Target db : CRM1JT
Server : au07qap0q0tels2
Steps 1: Create the required directories in filesystem:
mkdir /ora/db003/data001/CRM1JT
mkdir /ora/db003/redo001/CRM1JT
mkdir /ora/db003/redo002/CRM1JT
mkdir /ora/db003/data001/back
mkdir /ora/admin/CRM1JT
cd /ora/admin/CRM1JT
mkdir udump cdump bdump adump
Steps 2 : Take a list of all datafiles , control files, online redofiles from source db CRMPS2.
select TABLESPACE_NAME,FILE_NAME from dba_data_files order by 1,2;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------------------------------
APP_CRAMERMVLOGTS2 /u02/oradata/data/CRMPS2/CRMPS2_app_cramermvlogts2_01.dbf
AUDIT1 /u02/oradata/data/CRMPS2/CRMPS2_audit_01.dbf
GENUSER /u02/oradata/data/CRMPS2/CRMPS2_genuser_01.dbf
SYSAUX /u02/oradata/data/CRMPS2/CRMPS2_sysaux_01.dbf
SYSTEM /u02/oradata/data/CRMPS2/CRMPS2_system_01.dbf
TOOLS /u02/oradata/data/CRMPS2/CRMPS2_tools_01.dbf
UNDOTBS /u07/oradata/undo/CRMPS2/CRMPS2_undotbs_01.dbf
USR /u02/oradata/data/CRMPS1/CRMPS2_usr_04.dbf
USR /u02/oradata/data/CRMPS2/CRMPS2_usr_01.dbf
USR /u02/oradata/data/CRMPS2/CRMPS2_usr_02.dbf
USR /u02/oradata/data/CRMPS2/CRMPS2_usr_03.dbf
select TABLESPACE_NAME,FILE_NAME from dba_temp_files order by 1,2;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------------------------------
TEMP /u08/oradata/temp/CRMPS2/CRMPS2temp_01.dbf
TEMPORARY /u08/oradata/temp/CRMPS2/CRMPS2temporary_01.dbf
select NAME from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/data/CRMPS2/CRMPS2_control_01.ctl
/u03/oradata/index/CRMPS2/CRMPS2_control_02.ctl
/u05/oradata/redo/CRMPS2/CRMPS2_control_03.ctl
select GROUP#,MEMBER from v$logfile order by 1,2;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
1 /u04/oradata/redo/CRMPS2/CRMPS2_redo_01_01.log
1 /u05/oradata/redo/CRMPS2/CRMPS2_redo_01_02.log
2 /u04/oradata/redo/CRMPS2/CRMPS2_redo_02_01.log
2 /u05/oradata/redo/CRMPS2/CRMPS2_redo_02_02.log
3 /u04/oradata/redo/CRMPS2/CRMPS2_redo_03_01.log
3 /u05/oradata/redo/CRMPS2/CRMPS2_redo_03_02.log
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u06/DBFR/CRMPS2
Oldest online log sequence 815
Next log sequence to archive 817
Current log sequence 817
SQL> select sum(bytes)/1024/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024/1024
-------------------------
45.6855469
SQL> c/data_files/segments
1* select sum(bytes)/1024/1024/1024 from dba_segments
SQL> /
SUM(BYTES)/1024/1024/1024
-------------------------
32.5649414
Steps 3 : Take the source db backup on disk or in tape.
RMAN > run
{
allocate channel C1 type disk;
backup database format '/ora/db003/data001/back/b_%d_%t_%s.bk';
sql 'alter system archive log current';
BACKUP FORMAT '/ora/db003/data001/back/arch_CRMPS2_%t_%s.arc'
archivelog all;
release channel C1;
}
Create the initCRM1JT.ora and copy the password to
cd $ORACLE_HOME/dbs
cp initCRMPS2.ora initCRM1JT.ora
cp –p orapwCRMPS2 orapwCRM1JT
==>>>initCRM1JT.ora <<=====
*._optimizer_cost_based_transformation='OFF'
*._trace_files_public=TRUE
*.audit_file_dest='/tmp'
*.audit_trail='FALSE'
*.background_dump_dest='/ora/admin/CRM1JT/bdump'
*.compatible='10.2.0.4'
*.CONTROL_FILE_RECORD_KEEP_TIME=16
*.control_files='/ora/db003/data001/CRM1JT/CRM1JT_control_01.ctl','/ora/db003/redo001/CRM1JT/CRM1JT_control_02.ctl','/ora/db003/redo002/CRM1JT/CRM1JT_control_03.ctl'
*.core_dump_dest='/ora/admin/CRM1JT/cdump'
*.db_file_name_convert=('/u02/oradata/data/CRMPS2','/ora/db003/data001/CRM1JT','/u02/oradata/data/CRMPS1','/ora/db003/data001/CRM1JT','/u07/oradata/undo/CRMPS2','/ora/db003/data001/CRM1JT','/u08/oradata/temp/CRMPS2','/ora/db003/data001/CRM1JT')
*.log_file_name_convert=('/u04/oradata/redo/CRMPS2','/ora/db003/redo001/CRM1JT','/u05/oradata/redo/CRMPS2','/ora/db003/redo002/CRM1JT')
*.cursor_sharing='SIMILAR'
*.DB_BLOCK_SIZE=8192
*.db_cache_size=100000000
*.db_file_multiblock_read_count=16
*.db_files=999
*.db_name='CRM1JT'
*.db_writer_processes=20
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CRM1JTXBD)'
*.fast_start_mttr_target=300
*.instance_name='CRM1JT'
*.java_max_sessionspace_size=120000000
*.java_pool_size=120000000
*.java_soft_sessionspace_limit=100000000
*.job_queue_processes=0
CRM1JT.job_queue_processes=8
*.large_pool_size=8000000
*.log_archive_dest='/u06/DBFR/CRM1JT'
*.LOG_ARCHIVE_FORMAT='arch_CRM1JT_%s_%t_%r.arc'
*.log_buffer=512000
*.NLS_DATE_FORMAT='DD-MM-YYYY'
*.nls_length_semantics='CHAR'
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=2000
*.optimizer_index_caching=0
*.optimizer_index_cost_adj=100
*.OPTIMIZER_MODE='CHOOSE'
*.optimizer_secure_view_merging=FALSE
*.parallel_adaptive_multi_user=TRUE
*.parallel_max_servers=2500
*.pga_aggregate_target=50000000
*.processes=500
*.query_rewrite_enabled='TRUE'
*.rdbms_server_dn='cn=CRM1JT,cn=OracleContext,dc=telstra,dc=com'
*.REMOTE_OS_AUTHENT=FALSE
*.service_names='CRM1JT'
*.session_cached_cursors=500
*.sessions=555
*.sga_max_size=3785120000
*.sga_target=1895825408
*.shared_pool_size=536870912
*.sort_area_retained_size=0
*.sort_area_size=0
*.sqltune_category='TELSTRA'
*.trace_enabled=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.user_dump_dest='/ora/admin/CRM1JT/udump'
*.utl_file_dir='/amdocsftpxmldir/crmps2/export/'
==>>
Vi /var/opt/oracle/oratab
Add the below line
CRM1JT:/u01/ora/product/1020/db_1:Y
. oraenv
CRM1JT
Steps 4 ===>>>
SQL> startup nomount pfile=’ /u01/ora/product/1020/db_1/dbs/initCRM1JT.ora
==> Run the below script <===
#!/bin/ksh
for sid in CRMPS2
do
ORACLE_SID=$sid;export ORACLE_SID
ORAENV_ASK=NO
ORAENV=/usr/local/bin/oraenv
export ORAENV
. $ORAENV
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
echo $ORACLE_SID >> bkp_${ORACLE_SID}.log
echo $ORACLE_HOME >> bkp_${ORACLE_SID}.log
$ORACLE_HOME/bin/rman target / nocatalog < connect auxiliary sys/Alr3_ady@CRM1JT
@/ora/admin/pwp728628/rman_duplicate.rcv
exit
!
done
-------------
script ==>> rman_duplicate.rcv
au07qap0q0tels2:CRMPS2:/ora/admin/pwp728628>cat rman_duplicate.rcv
run {
set until scn 10560195594037;
allocate channel ch1 type disk;
allocate auxiliary channel aux1 type disk;
duplicate target database to CRM1JT;
}
====>>>>>>>>>output as given below <<===========
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 16:24:38 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CRMPS2 (DBID=1287191112)
using target database control file instead of recovery catalog
RMAN>
connected to auxiliary database: CRM1JT (not mounted)
RMAN>
RMAN> run {
2> set until scn 10560195594037;
3> allocate channel ch1 type disk;
4> allocate auxiliary channel aux1 type disk;
5> duplicate target database to CRM1JT;
6> }
executing command: SET until clause
allocated channel: ch1
channel ch1: sid=497 devtype=DISK
allocated channel: aux1
channel aux1: sid=522 devtype=DISK
Starting Duplicate Db at 06-JUL-10
contents of Memory Script:
{
set until scn 10560195594037;
set newname for datafile 1 to
"/ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf";
set newname for datafile 2 to
"/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf";
set newname for datafile 3 to
"/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf";
set newname for datafile 4 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf";
set newname for datafile 5 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf";
set newname for datafile 6 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf";
set newname for datafile 7 to
"/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf";
set newname for datafile 8 to
"/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf";
set newname for datafile 9 to
"/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf";
set newname for datafile 10 to
"/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf";
set newname for datafile 11 to
"/ora/db003/data001/CRM1JT/CRMPS2_audit_01.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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 06-JUL-10
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf
restoring datafile 00002 to /ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf
restoring datafile 00003 to /ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf
restoring datafile 00004 to /ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf
restoring datafile 00005 to /ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf
restoring datafile 00006 to /ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf
restoring datafile 00007 to /ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf
restoring datafile 00011 to /ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf
channel aux1: reading from backup piece /ora/db003/data001/back/b_CRMPS2_723645314_1544.bk
channel aux1: restored backup piece 1
piece handle=/ora/db003/data001/back/b_CRMPS2_723645314_1544.bk tag=TAG20100706T123513
channel aux1: restore complete, elapsed time: 00:08:26
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to /ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf
restoring datafile 00009 to /ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf
restoring datafile 00010 to /ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf
channel aux1: reading from backup piece /ora/db003/data001/back/b_CRMPS2_723645610_1545.bk
channel aux1: restored backup piece 1
piece handle=/ora/db003/data001/back/b_CRMPS2_723645610_1545.bk tag=TAG20100706T123513
channel aux1: restore complete, elapsed time: 00:00:25
Finished restore at 06-JUL-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CRM1JT" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_01_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_01_02.log' ) SIZE 600 M REUSE,
GROUP 2 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_02_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_02_02.log' ) SIZE 600 M REUSE,
GROUP 3 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_03_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_03_02.log' ) SIZE 600 M REUSE
DATAFILE
'/ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=723659624 filename=/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf
contents of Memory Script:
{
set until scn 10560195594037;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 06-JUL-10
starting media recovery
archive log thread 1 sequence 828 is already on disk as file /u06/DBFR/CRMPS2/arch_CRMPS2_828_1_686533499.arc
archive log thread 1 sequence 829 is already on disk as file /u06/DBFR/CRMPS2/arch_CRMPS2_829_1_686533499.arc
archive log filename=/u06/DBFR/CRMPS2/arch_CRMPS2_828_1_686533499.arc thread=1 sequence=828
archive log filename=/u06/DBFR/CRMPS2/arch_CRMPS2_829_1_686533499.arc thread=1 sequence=829
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-JUL-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 3791650816 bytes
Fixed Size 2045160 bytes
Variable Size 2583694104 bytes
Database Buffers 1191182336 bytes
Redo Buffers 14729216 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CRM1JT" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_01_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_01_02.log' ) SIZE 600 M REUSE,
GROUP 2 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_02_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_02_02.log' ) SIZE 600 M REUSE,
GROUP 3 ( '/ora/db003/redo001/CRM1JT/CRMPS2_redo_03_01.log', '/ora/db003/redo002/CRM1JT/CRMPS2_redo_03_02.log' ) SIZE 600 M REUSE
DATAFILE
'/ora/db003/data001/CRM1JT/CRMPS2_system_01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 2 to
"/ora/db003/data001/CRM1JT/CRMPS2temp_01.dbf";
set newname for tempfile 3 to
"/ora/db003/data001/CRM1JT/CRMPS2temporary_01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf";
catalog clone datafilecopy "/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 2 to /ora/db003/data001/CRM1JT/CRMPS2temp_01.dbf in control file
renamed temporary file 3 to /ora/db003/data001/CRM1JT/CRMPS2temporary_01.dbf in control file
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf recid=1 stamp=723659659
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf recid=2 stamp=723659660
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf recid=3 stamp=723659660
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf recid=4 stamp=723659660
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf recid=5 stamp=723659660
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf recid=6 stamp=723659661
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf recid=7 stamp=723659661
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf recid=8 stamp=723659661
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf recid=9 stamp=723659661
cataloged datafile copy
datafile copy filename=/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf recid=10 stamp=723659662
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=723659659 filename=/ora/db003/data001/CRM1JT/CRMPS2_undotbs_01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_sysaux_01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_02.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=723659660 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_03.dbf
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_usr_04.dbf
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_app_cramermvlogts2_01.dbf
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_genuser_01.dbf
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=723659661 filename=/ora/db003/data001/CRM1JT/CRMPS2_tools_01.dbf
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=723659662 filename=/ora/db003/data001/CRM1JT/CRMPS2_audit_01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 06-JUL-10
released channel: ch1
RMAN>
RMAN> **end-of-file**
RMAN>
Recovery Manager complete.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment