Remember the following when creating clone volumes for a clone database
=======================================================================
Take a snap of all the data and redo volumes involved with the dbs to be cloned.
Also make sure data volumes are snapped before the redo volumes and dont leave too much gap between the two or you need to clone the arch as well.
Clone archive volume only if we need to go back to a point in time for which the redo logs are only in the archive
If we are cloning live databases that are up and running take a hot backup and base clones on those snaps.
Make sure you dont interfere with the 30 mins regular backups.
Base the clone on the correct datacentre snaps depending on the server you are mounting it on
Notify networks to create the clones on the correct data centre based on the snaps ending with name CLONE_<ddmmyyyy>
For example to clone TEST2 voume to create TEST2 clone DB
ssh oracle@tcygg01
export ORACLE_SID=TEST21
sqlplus / as sysdba
alter database begin backup;
exit;
sudo su
/usr/bin/rsh ga_test2_data.data.dc1.oracle.net -n snap create TCYGA_TEST2_DATA TCYGA_TEST2_CLONE_25062012
/usr/bin/rsh test2_redo_a.data.dc1.oracle.net -n snap create TEST2_REDO_A TEST2_REDO_A_TEST2_CLONE_25062012
/usr/bin/rsh test2_redo_b.data.dc1.oracle.net -n snap create TEST2_REDO_B TEST2_REDO_B_TEST2_CLONE_25062012
exit
sqlplus / as sysdba
alter database end backup;
exit;
Mounting the Clone volumes on the target server
==============================================
Once the clone volumes are created by storage administrators mount the volumes on the target server as below
sudo su
vi /etc/fstab
test2_data.data.dc1.oracle.net:/DC1_WEB2_DATA /oradata/TEST2 nfs rw,bg,hard,rsize=65536,wsize=65536,vers=3,actimeo=0,nointr,suid,timeo=600,tcp 0 0
test2_redo_a.data.dc1.oracle.net:/DC1_WEB2_REDO_A /oraredo/TEST2_A nfs rw,bg,hard,rsize=65536,wsize=65536,vers=3,actimeo=0,nointr,suid,timeo=600,tcp 0 0
test2_redo_b.data.dc1.oracle.net:/DC1_WEB2_REDO_B /oraredo/TEST2_B nfs rw,bg,hard,rsize=65536,wsize=65536,vers=3,actimeo=0,nointr,suid,timeo=600,tcp 0 0
exit
mkdir /oradata/TEST2
chown oracle:dba /oradata/TEST2
mkdir /oraredo/TEST2_A
chown oracle:dba /oraredo/TEST2_A
mkdir /oraredo/TEST2_B
chown oracle:dba /oraredo/TEST2_A
mount /oradata/TEST2
mount /oraredo/TEST2_A
mount /oraredo/TEST2_B
Create the pfile to point the spfile that is on the mounted volume, check pwd file
==================================================================================
cd $ORACLE_HOME/dbs
vi initTEST21.ora
spfile=’/oradata/TEST2/TEST2/spfileTEST2.ora’
check the target file exists as below
ls /oradata/TEST2/TEST2/spfileTEST2.ora
–create symbolic link to the password file
cd $ORACLE_HOME/dbs
ln -s /oradata/TEST2/TEST2/orapwTEST2 orapwTEST21
Tidy up the tns entries
=======================
Ensure that the db could not connect to any live database by removing all tns entries to production database
Tidy up memory parameters
=========================
create pfile from spfile to edit memory parameters
==================================================
export ORACLE_SID=TEST21
sqlplus / as sysdba
create pfile=’/u01/app/oracle/product/11.1/db/dbs/deepa_test2.ora’ from spfile=’/oradata/TEST2/TEST2/spfileTEST2.ora’;
exit
df -h
tmpfs 9.0G 1.3G 7.8G 14% /dev/shm
note down memory available
vi deepa_test2.ora
delete all parameters <inst>.__ as they are dynamically managed parameters
leave the cluster database = true
*.memory_max_target=6442450944
TEST21.memory_max_target=6442450944
*.shared_pool_size=512M
remove
*.memory_target=23622320128
TEST21.memory_target=23622320128
WEB12.memory_target=21474836480
*.pga_aggregate_target=4294967296
TEST21.pga_aggregate_target=4294967296
WEB12.pga_aggregate_target=4294967296
*.sga_target=10737418240
TEST21.sga_target=10737418240
WEB12.sga_target=10737418240
*.streams_pool_size=268435456
*.large_pool_size=268435456
*.java_pool_size=268435456
*.db_cache_size=4294967296
LISTENER
========
Ensure *.remote_listener=’LISTENERS_TEST2′ entry exists in tns files
sqlplus / as sysdba
create spfile=’/oradata/TEST2/TEST2/spfileTEST2.ora’ from pfile=’/u01/app/oracle/product/11.1/db/dbs/deepa_test2.ora’;
exit
Open in mount mode and disable all scheduler and dbms_jobs
==========================================================
export ORACLE_SID=TEST21
sqlplus / as sysdba
startup mount;
show parameter job_queue
alter system set job_queue_processes=0;
recover database (using backup controlfile)
alter database noarchivelog
alter database open (resetlogs);
BEGIN
DBMS_OUTPUT.ENABLE(9000000);
FOR cur IN
(SELECT ‘BEGIN SYS.DBMS_SCHEDULER.STOP_JOB(JOB_NAME => ‘ || chr(39) || OWNER || ‘.’ || JOB_NAME || chr(39) || ‘, FORCE => TRUE); END;’ stmt,
OWNER || ‘.’ || JOB_NAME job_name
FROM DBA_SCHEDULER_JOBS
WHERE OWNER <> ‘SYS’)
LOOP
–DBMS_OUTPUT.PUT_LINE(cur.stmt);
BEGIN
EXECUTE IMMEDIATE(cur.stmt);
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(‘JOB NOT RUNNING ‘ || cur.job_name);
–DBMS_OUTPUT.PUT_LINE(SQLERRM || ‘ CAUSE:’ || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
END LOOP;
FOR cur IN
(SELECT ‘BEGIN SYS.DBMS_SCHEDULER.DISABLE(NAME => ‘ || ”” || OWNER || ‘.’ || JOB_NAME || ”” || ‘, FORCE => TRUE); END;’ stmt,
OWNER || ‘.’ || JOB_NAME job_name
FROM DBA_SCHEDULER_JOBS
WHERE OWNER <> ‘SYS’)
LOOP
–DBMS_OUTPUT.PUT_LINE(cur.stmt);
BEGIN
EXECUTE IMMEDIATE(cur.stmt);
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(‘JOB NOT DISABLED ‘ || cur.job_name);
–DBMS_OUTPUT.PUT_LINE(SQLERRM || ‘ CAUSE:’ || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
END LOOP;
END;
/
Deepa Raja's Blog 