shell script to automate oracle database cloning using rman active duplicate feature

##########################################################
#Script to clone xxxxxx from xxxxxxxx from active database #
#Author: Manoj                                           #
#Date: 30th Oct 2012                                     #
#Approximate time 1 Hour                                 #
###########################################################
#
#Be very carefully while configuring listener
#hostname and tnsnames hostname should be a perfect match
#
#

LOG_FILE=/u01/app/oracle/diag/custom_logs/FIN1_clone.log
RMAN_LOG=/u01/app/oracle/diag/custom_logs/FIN1_RMAN.log
export LOG_FILE
export RMAN_LOG


ORACLE_SID=FIN1
export ORACLE_SID

#echo "confirm"
#echo $ORACLE_SID

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
echo "--------------------------------------" >> $LOG_FILE
echo "Start of Script at `date`" >> $LOG_FILE

#Drop database
#echo "shut immediate" | sqlplus '/ a sysdba' >> $LOG_FILE
#echo "startup nomount restrict" | sqlplus '/ as sysdba' >> $LOG_FILE
#echo "drop database" | sqlplus '/ as sysdba' >> $LOG_FILE
$ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB FINUAT -sysDBAUserName sys -sysDBAPassword xxxxxxxxx  >> $LOG_FILE
#add exception handling here


#Copy password file
cp $ORACLE_HOME/dbs/orapwFIN2 $ORACLE_HOME/dbs/orapwFIN1 >> $LOG_FILE
#add exception handling here

#create password file
#orapwd file=$ORACLE_HOME/dbs/orapwFIN1 password=satyam entries=10

echo "Start of clone of FINUAT from FINPRD from active databse `date`" >> $LOG_FILE
#Copy init file
cp $ORACLE_HOME/dbs/bkp_init1.ora $ORACLE_HOME/dbs/initFIN1.ora >> $LOG_FILE
#add exception handling here

#Add entry to tnsnames.ora

echo "FINUAT =   " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo "  (DESCRIPTION = " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo "    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1621)) " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo "    (CONNECT_DATA =  " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo "      (SERVER = DEDICATED) " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo "      (SERVICE_NAME = FIN1)" >> $ORACLE_HOME/network/admin/tnsnames.ora
echo "    )" >> $ORACLE_HOME/network/admin/tnsnames.ora
echo "  )" >> $ORACLE_HOME/network/admin/tnsnames.ora

#Shut down if started.
#echo "shut immediate" | sqlplus '/ as sysdba'

#Start database in nomount
echo "startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/bkp_initFIN1.ora" | sqlplus '/ as sysdba' >> $LOG_FILE

#create spfile from pfile
echo "create spfile from pfile;" | sqlplus '/ as sysdba' >> $LOG_FILE

#stop DB
echo "shut immediate;" | sqlplus '/ as sysdba' >> $LOG_FILE

#Stop and start listener services
lsnrctl stop FIN1 >> $LOG_FILE
lsnrctl start FIN1 >> $LOG_FILE
#

#start DB with Pfile
echo "startup nomount;" | sqlplus '/ as sysdba' >> $LOG_FILE

#clone from active database
$ORACLE_HOME/bin/rman target sys/xxxx@FIN2 auxiliary sys/xxxxxxx@FIN2 msglog $RMAN_LOG << EOF
run
{
allocate auxiliary channel a1  device type disk;
allocate auxiliary channel a2  device type disk;
allocate auxiliary channel a3  device type disk;
allocate auxiliary channel a4  device type disk;
allocate channel a5  device type disk;
allocate channel a6  device type disk;
allocate channel a7  device type disk;
allocate channel a8  device type disk;
duplicate database to FIN1 from active database;
release channel a1 ;
release channel a2 ;
release channel a3 ;
release channel a4 ;
release channel a5 ;
release channel a6 ;
release channel a7 ;
release channel a8 ;
}
EOF
echo "Backup finished at `date`" >> $LOG_FILE
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||" >> $LOG_FILE

# add entry to /etc/oratab file
echo "FIN1:/u01/app/oracle/product/11.2.0/dbhome_1:N #added by md86506" >> /etc/oratab

echo
echo "@ /u01/app/oracle/Backup/SCRIPTS/FIN1_POST_CLONE.sql" | sqlplus '/ as sysdba' >> $LOG_FILE
exit ;


#LOCAL_LISTENER='ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1621))'
#
#FINUAT =
#  (ADDRESS_LIST =
#        (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCFINUAT))
#        (ADDRESS= (PROTOCOL= TCP)(Host= hostname)(Port= 1621))
#   )
#
#SID_LIST_FINUAT =
#  (SID_LIST =
#    (SID_DESC =
#      (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
#      (SID_NAME = FIN1)
#    )
#    (SID_DESC =
#      (SID_NAME = PLSExtProc)
#      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
#      (PROGRAM = extproc)
#    )
#  )
#

move execution plan between two oracle databases

IMPORT/EXPORT execution plan from one Database to another

You use DBMS_SQLTUNE subprograms to move SQL profiles and SQL tuning sets from one system to another using a common programmatic model. In both cases, you create a staging table on the source system and populate that staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as datapump, import/export, or database link), where it is used to reconstitute the objects in their original form. These steps are implemented by means of subprograms included in this package:

Call the CREATE_STGTAB_SQLPROF Procedure or the CREATE_STGTAB_SQLSET Procedure to create the staging table on the source system.

Call the PACK_STGTAB_SQLPROF Procedure or PACK_STGTAB_SQLSET Procedure to populate the staging table with information from the source system.

Once you have moved the staging table to the destination system, you call the UNPACK_STGTAB_SQLPROF Procedure or the UNPACK_STGTAB_SQLSET Procedure to recreate the object on the new system.


EXAMPLE:

NOTE: Do not sys schema for staging table

Create a staging table:
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name  => 'PROFILE_TO_PROD');

dump profile to staging table
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(profile_name => 'migrate_to_prod_1',profile_category => 'DEFAULT',staging_table_name=> 'PROFILE_TO_PROD', staging_schema_owner => 'MD86506');

export/import staging table
exp tables=PROFILE_TO_PROD file=prof01.dmp log=prof01.log
imp file=prof01.dmp log=profimp.log tables=PROFILE_TO_PROD

unpack the staging table for sql_profile to come into effect
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace            => FALSE, -
                                        staging_table_name => 'PROFILE_STGTAB');


References: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sqltun.htm#CHDGFHEC

exadata to non-exadata cloning

 clone
-----------------------
1)
Create init.ora file (example instance name: TPTST)
*._gby_hash_aggregation_enabled=FALSE
*._optimizer_cost_based_transformation='OFF'
*._optimizer_use_feedback=FALSE
*._unnest_subquery=FALSE
*.audit_sys_operations=FALSE
*.audit_trail='NONE'
*.compatible='11.2.0.2.0'
*.control_files='xxxxxxxxxxxxxxxcontrol01.ctl','xxxxxxxxxxxcontrol02.ctl'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_cache_size=750M
*.db_domain=''
*.db_files=1000
*.db_name='TPTST'#Reset to original value by RMAN
*.diagnostic_dest='xxxxxxxxx'
*.large_pool_size=250M
*.log_file_name_convert='xxxxxxx','/u03/','xxxxxxxxxxxx','/u02/'
*.open_cursors=700
*.optimizer_index_caching=99
*.optimizer_index_cost_adj=40
*.optimizer_use_sql_plan_baselines=FALSE
*.parallel_adaptive_multi_user=FALSE
*.parallel_execution_message_size=8384
*.parallel_max_servers=6
*.parallel_min_servers=2
*.pga_aggregate_target=800M
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='DEFAULT_PLAN'
*.session_cached_cursors=250
*.sga_target=2048M
*.shared_pool_size=700M
*.undo_tablespace='UNDOTBS1'

2)
source oracle_Sid

3)
start DB using init.ora file in nomount state

4)
create spfile from pfile

5)
restart DB using spfile in nomount state

6)
rman auxiliary /

7)
execute below command to create instance.
Check space usage and redirect tablespaces using "set newname for tablespace" clause.


RUN
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk ;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk ;
SET NEWNAME FOR DATABASE TO '/u04/ORADATA/HREDU/%b';
duplicate database SFNPRD to SFNPRD1 BACKUP LOCATION '/xx/xx/xx'
LOGFILE GROUP 1 ('/xx/xx/xx/redo01.log') SIZE 1G REUSE,
      GROUP 2 ('/xx/xx/xx/redo02.log') SIZE 1G REUSE,
      GROUP 3 ('/xx/xx/xx/redo03.log') SIZE 1G REUSE;
}

8)
mast sensitive data
remove hybrid columanar compression if zfs storage is not available
run appliation specific post clone scripts
drop unwanted undo, temp and redo's

------Active duplicate-----

NOTE:
* make sure to have local_listener parameter in spfile
* make sure listener entries are available in listener.ora in auxiliary and in tnanames.ora in both target and auxiliary

run
{
allocate auxiliary channel a1  device type disk;
allocate auxiliary channel a2  device type disk;
allocate channel a5  device type disk;
allocate channel a6  device type disk;
SET NEWNAME FOR DATABASE TO 'xxxxxxxxxxxxx/%b';
duplicate database to xxxxxxxxxxx from active database;
release channel a1 ;
release channel a2 ;
release channel a5 ;
release channel a6 ;
}