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.

No comments:

Post a Comment