We recently had requirement to write script to Clone Rac database on same server with different database name . Below is script I prepared.
In script i am resetting source database password on each execution . If we don't want to change password we can keep same password and encrypt password in script . Below Blog mentions how we can encrypt password/.
https://abdul-hafeez-kalsekar.blogspot.com/2021/10/oracle-password-encrypt-for-shell.html
Reference : STEP BY STEP RMAN DUPLICATE Database From RAC ASM To RAC ASM (Doc ID 1913937.1)
#Script clone_auto.sh to clone database on same server
#Author: Abdul Hafeez Kalsekar #
#Date : 29th August 2021 #
#Script clone_auto.sh to clone database on same server
#Author: Abdul Hafeez Kalsekar #
#Date : 29th August 2021 #
#Usage : Need to pass four argument to run script
#clone_auto.sh Target_Database_Isntance_Name Target_Database_Name ASM_Isntance_Name Source_Database_Instance_Name SOURCE_DATABASE_NAME #
#clone_auto.sh CLONE1 CLONE +ASM1 RMAN1 RMAN #
#clone_auto.sh Target_Database_Isntance_Name Target_Database_Name ASM_Isntance_Name Source_Database_Instance_Name SOURCE_DATABASE_NAME #
#clone_auto.sh CLONE1 CLONE +ASM1 RMAN1 RMAN #
# Before Runing Cloning we need to ensure we have backup pfile for new clone database
#`echo $DATABASE_HOME`/dbs/`echo $INSTANCE_NAME`_non_rac.ora'
#`echo $DATABASE_HOME`/dbs/`echo $INSTANCE_NAME`_non_rac.ora'
# In case we loose pfile for Clone Database we can create pfile from spfile of source database and chaneg below parameters
# DB_FILE_NAME_CONVERT / LOG_FILE_NAME_CONVERT / db name / db unique name / controlfile , local_listener , huge pages , cluster_database
# Below is Sequence of script
# 1) Unlock Sys account in source
# 2) Drop Existing Clone Daatabase
# 3) Modfiy new parameters in Clone spfile
# 4) Copy spfile and Password file to Asm
# 5) Lock Sys account in source database
# DB_FILE_NAME_CONVERT / LOG_FILE_NAME_CONVERT / db name / db unique name / controlfile , local_listener , huge pages , cluster_database
# Below is Sequence of script
# 1) Unlock Sys account in source
# 2) Drop Existing Clone Daatabase
# 3) Modfiy new parameters in Clone spfile
# 4) Copy spfile and Password file to Asm
# 5) Lock Sys account in source database
# Before we start Cloning below steps were already completed
# 1) Create Diskgroup for clone database
# 2) Create parameter file for clone databse for cloning
# 3) Create password file for new clone database
# 4) Create tns entry for new database
# 5) Create new listener for auxiliary instance
# 6) Added entry for CLone database in Oratab
# 7) Encrypt Password
# 8) Do manual cloning once and add database to ocr and
# check srvctl config database if password file and spfile pointing to desired location as mentioned in script
#Below are common issues faced
#RMAN-04006 ORA-01017 when connecting to auxiliary database on Standby From Primary (Doc ID 2445873.1)
#RMAN Active Duplication Fails With Ora-17629, Ora-17627 Errors (Doc ID 2119741.1)
# Below was used to add new listener and add manual entry to listener.ora under grid home
#srvctl add listener -l LISTENER_DBNAME -p TCP:4191 -s
#SID_LIST_LISTENER_DBNAME =
# (SID_LIST =
# (SID_DESC =
# (GLOBAL_DBNAME=DB_NAME )
# (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1 )
# (SID_NAME = Instance_name)
# )
# )
#Below entry was added to database home tnsnames.ora
#Instance_name =
# (DESCRIPTION =
# (ADDRESS_LIST =
# (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
# )
# (CONNECT_DATA =
# (SID = Instance_name)
# )
# )
############################ Start of actual script ###############################
set -e
INSTANCE_NAME=$1
DBNAME=$2
ASM_INSTANCE_NAME=$3
SOURCEINSTANCE=$4
SOURCEDATABASE=$5
export DATABASE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export ASM_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
export PWFILE=$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`_clone_donotdelete
date=$(date +'%a_%m_%d_%Y')
export LOG_FILE=/u01/app/oracle/log/clone_logs/`echo $INSTANCE_NAME`_clone_$date.log
export RMAN_LOG=/u01/app/oracle/log/clone_logs/`echo $INSTANCE_NAME`_RMAN_$date.log
export LOG_DIRECTORY='/u01/app/oracle/log/clone_logs/'
if [ -d $LOG_DIRECTORY ]
then
echo "Directory already exists"
else
mkdir -p $LOG_DIRECTORY
chmod 744 $LOG_DIRECTORY
fi
if [ -f "$LOG_FILE" ] ; then
mv "$LOG_FILE" "$LOG_FILE".old
fi
if [ -f "$RMAN_LOG" ] ; then
mv "$RMAN_LOG" "$RMAN_LOG".old
fi
####################
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
alter user sys IDENTIFIED BY Pass#`echo $date` account unlock ;
exit
EOF
if [ -f "$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`" ] ; then
rm $ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`
fi
orapwd file=$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE` password=Pass#`echo $date` >> $LOG_FILE
####################
export ORACLE_SID=$ASM_INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
set +e
$ASM_HOME/bin/asmcmd ls +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`
if [ $? -eq 0 ]
then
$ASM_HOME/bin/asmcmd rm +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`
fi
$ASM_HOME/bin/asmcmd ls +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/
fi
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/orapw`echo $SOURCEDATABASE` +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`
set -e
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
if [ -f "$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`" ] ; then
rm $ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`
fi
####################
if [ `ps -ef | grep pmon | grep -i $INSTANCE_NAME | grep -v grep | wc -l` == 1 ]; then
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
alter system set cluster_database=false scope=spfile sid='*' ;
exit
EOF
$ORACLE_HOME/bin/srvctl stop database -db $DBNAME >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
startup mount restrict ;
drop database ;
exit
EOF
$ORACLE_HOME/bin/srvctl remove database -db `echo $DBNAME` << EOF >> $LOG_FILE
y
EOF
fi
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
if [ -f "$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`" ] ; then
rm $ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`
fi
orapwd file=$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME` password=Pass#`echo $date` >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
startup nomount pfile='${ORACLE_HOME}/dbs/init`echo $INSTANCE_NAME`_non_rac.ora' ;
exit
EOF
echo "cloning start , please refer to cloning log `echo $RMAN_LOG`" >> $LOG_FILE
if [ -f ${INSTANCE_NAME}_clone.sql ] ; then
rm ${INSTANCE_NAME}_clone.sql
fi
echo " run " >> `echo $INSTANCE_NAME`_clone.sql
echo " { " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src1 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src2 type disk;" >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src3 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src4 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux1 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux2 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux3 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux4 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux5 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux6 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " set newname for database to '+`echo $DBNAME`_DATADG'; " >> `echo $INSTANCE_NAME`_clone.sql
echo " duplicate target database to '`echo $DBNAME`' from active database NOFILENAMECHECK; " >> `echo $INSTANCE_NAME`_clone.sql
echo " } " >> `echo $INSTANCE_NAME`_clone.sql
$ORACLE_HOME/bin/rman target sys/Pass#`echo $date`@$SOURCEINSTANCE auxiliary sys/Pass#`echo $date`@$INSTANCE_NAME log=$RMAN_LOG cmdfile=`echo $INSTANCE_NAME`_clone.sql << EOF >> $LOG_FILE
EOF
echo "cloning completed" >> $LOG_FILE
echo " alter system set cluster_database=true scope=spfile sid='*'; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system reset undo_tablespace scope=spfile sid='*' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set undo_tablespace=UNDOTBS2 scope=spfile sid='`echo $DBNAME`2' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set undo_tablespace=UNDOTBS1 scope=spfile sid='`echo $DBNAME`1' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system reset instance_number scope=spfile sid='*' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set instance_number=1 scope=spfile sid='`echo $DBNAME`1' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set instance_number=2 scope=spfile sid='`echo $DBNAME`2' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system reset thread scope=spfile sid='*' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set thread=1 scope=spfile sid='`echo $DBNAME`1' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set thread=2 scope=spfile sid='`echo $DBNAME`2' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
@`echo $INSTANCE_NAME`_postclone_parameters.sql
shu immediate
exit
EOF
####################
export ORACLE_SID=$ASM_INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
set +e
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
if [ $? -eq 0 ]
then
$ASM_HOME/bin/asmcmd rm +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
fi
set -e
set +e
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
if [ $? -eq 0 ]
then
$ASM_HOME/bin/asmcmd rm +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
fi
set -e
set +e
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/
fi
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/
fi
set -e
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/spfile`echo $INSTANCE_NAME`.ora +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/orapw`echo $INSTANCE_NAME` +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
if [ -f nodename.log ] ; then
rm nodename.log
fi
$ASM_HOME/bin/olsnodes -n >> nodename.log
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
srvctl add database -db `echo $DBNAME` -oraclehome `echo $DATABASE_HOME` -dbtype RAC -spfile +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora -pwfile +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME` -dbname `echo $DBNAME` >> $LOG_FILE
cat nodename.log |while read LINE ;
do
srvctl add instance -db `echo $DBNAME` -i `echo $DBNAME``echo ${LINE}|awk '{print $2}'` -n `echo ${LINE}|awk '{print $1}'`
done
srvctl start database -db `echo $DBNAME` >> $LOG_FILE
rm `echo $DATABASE_HOME`/dbs/spfile`echo $INSTANCE_NAME`.ora >> $LOG_FILE
rm `echo $DATABASE_HOME`/dbs/orapw`echo $INSTANCE_NAME` >> $LOG_FILE
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
sqlplus "/as sysdba" << EOF >> $LOG_FILE
alter user sys account lock password expire ;
exit
EOF
########### end of script #####################
# 1) Create Diskgroup for clone database
# 2) Create parameter file for clone databse for cloning
# 3) Create password file for new clone database
# 4) Create tns entry for new database
# 5) Create new listener for auxiliary instance
# 6) Added entry for CLone database in Oratab
# 7) Encrypt Password
# 8) Do manual cloning once and add database to ocr and
# check srvctl config database if password file and spfile pointing to desired location as mentioned in script
#Below are common issues faced
#RMAN-04006 ORA-01017 when connecting to auxiliary database on Standby From Primary (Doc ID 2445873.1)
#RMAN Active Duplication Fails With Ora-17629, Ora-17627 Errors (Doc ID 2119741.1)
# Below was used to add new listener and add manual entry to listener.ora under grid home
#srvctl add listener -l LISTENER_DBNAME -p TCP:4191 -s
#SID_LIST_LISTENER_DBNAME =
# (SID_LIST =
# (SID_DESC =
# (GLOBAL_DBNAME=DB_NAME )
# (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1 )
# (SID_NAME = Instance_name)
# )
# )
#Below entry was added to database home tnsnames.ora
#Instance_name =
# (DESCRIPTION =
# (ADDRESS_LIST =
# (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
# )
# (CONNECT_DATA =
# (SID = Instance_name)
# )
# )
############################ Start of actual script ###############################
set -e
INSTANCE_NAME=$1
DBNAME=$2
ASM_INSTANCE_NAME=$3
SOURCEINSTANCE=$4
SOURCEDATABASE=$5
export DATABASE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export ASM_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
export PWFILE=$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`_clone_donotdelete
date=$(date +'%a_%m_%d_%Y')
export LOG_FILE=/u01/app/oracle/log/clone_logs/`echo $INSTANCE_NAME`_clone_$date.log
export RMAN_LOG=/u01/app/oracle/log/clone_logs/`echo $INSTANCE_NAME`_RMAN_$date.log
export LOG_DIRECTORY='/u01/app/oracle/log/clone_logs/'
if [ -d $LOG_DIRECTORY ]
then
echo "Directory already exists"
else
mkdir -p $LOG_DIRECTORY
chmod 744 $LOG_DIRECTORY
fi
if [ -f "$LOG_FILE" ] ; then
mv "$LOG_FILE" "$LOG_FILE".old
fi
if [ -f "$RMAN_LOG" ] ; then
mv "$RMAN_LOG" "$RMAN_LOG".old
fi
####################
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
alter user sys IDENTIFIED BY Pass#`echo $date` account unlock ;
exit
EOF
if [ -f "$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`" ] ; then
rm $ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`
fi
orapwd file=$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE` password=Pass#`echo $date` >> $LOG_FILE
####################
export ORACLE_SID=$ASM_INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
set +e
$ASM_HOME/bin/asmcmd ls +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`
if [ $? -eq 0 ]
then
$ASM_HOME/bin/asmcmd rm +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`
fi
$ASM_HOME/bin/asmcmd ls +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/
fi
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/orapw`echo $SOURCEDATABASE` +`echo $SOURCEDATABASE`_DATADG/`echo $SOURCEDATABASE`/PASSWORD/orapw`echo $SOURCEDATABASE`
set -e
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
if [ -f "$ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`" ] ; then
rm $ORACLE_HOME/dbs/orapw`echo $SOURCEDATABASE`
fi
####################
if [ `ps -ef | grep pmon | grep -i $INSTANCE_NAME | grep -v grep | wc -l` == 1 ]; then
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
alter system set cluster_database=false scope=spfile sid='*' ;
exit
EOF
$ORACLE_HOME/bin/srvctl stop database -db $DBNAME >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
startup mount restrict ;
drop database ;
exit
EOF
$ORACLE_HOME/bin/srvctl remove database -db `echo $DBNAME` << EOF >> $LOG_FILE
y
EOF
fi
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib/
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
if [ -f "$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`" ] ; then
rm $ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME`
fi
orapwd file=$ORACLE_HOME/dbs/orapw`echo $INSTANCE_NAME` password=Pass#`echo $date` >> $LOG_FILE
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
startup nomount pfile='${ORACLE_HOME}/dbs/init`echo $INSTANCE_NAME`_non_rac.ora' ;
exit
EOF
echo "cloning start , please refer to cloning log `echo $RMAN_LOG`" >> $LOG_FILE
if [ -f ${INSTANCE_NAME}_clone.sql ] ; then
rm ${INSTANCE_NAME}_clone.sql
fi
echo " run " >> `echo $INSTANCE_NAME`_clone.sql
echo " { " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src1 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src2 type disk;" >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src3 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate channel src4 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux1 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux2 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux3 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux4 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux5 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " allocate auxiliary channel aux6 type disk; " >> `echo $INSTANCE_NAME`_clone.sql
echo " set newname for database to '+`echo $DBNAME`_DATADG'; " >> `echo $INSTANCE_NAME`_clone.sql
echo " duplicate target database to '`echo $DBNAME`' from active database NOFILENAMECHECK; " >> `echo $INSTANCE_NAME`_clone.sql
echo " } " >> `echo $INSTANCE_NAME`_clone.sql
$ORACLE_HOME/bin/rman target sys/Pass#`echo $date`@$SOURCEINSTANCE auxiliary sys/Pass#`echo $date`@$INSTANCE_NAME log=$RMAN_LOG cmdfile=`echo $INSTANCE_NAME`_clone.sql << EOF >> $LOG_FILE
EOF
echo "cloning completed" >> $LOG_FILE
echo " alter system set cluster_database=true scope=spfile sid='*'; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system reset undo_tablespace scope=spfile sid='*' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set undo_tablespace=UNDOTBS2 scope=spfile sid='`echo $DBNAME`2' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set undo_tablespace=UNDOTBS1 scope=spfile sid='`echo $DBNAME`1' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system reset instance_number scope=spfile sid='*' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set instance_number=1 scope=spfile sid='`echo $DBNAME`1' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set instance_number=2 scope=spfile sid='`echo $DBNAME`2' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system reset thread scope=spfile sid='*' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set thread=1 scope=spfile sid='`echo $DBNAME`1' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
echo " alter system set thread=2 scope=spfile sid='`echo $DBNAME`2' ; " >> `echo $INSTANCE_NAME`_postclone_parameters.sql
$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF >> $LOG_FILE
@`echo $INSTANCE_NAME`_postclone_parameters.sql
shu immediate
exit
EOF
####################
export ORACLE_SID=$ASM_INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $ASM_INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
set +e
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
if [ $? -eq 0 ]
then
$ASM_HOME/bin/asmcmd rm +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
fi
set -e
set +e
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
if [ $? -eq 0 ]
then
$ASM_HOME/bin/asmcmd rm +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
fi
set -e
set +e
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/
fi
$ASM_HOME/bin/asmcmd ls +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/
if [ $? -ne 0 ]
then
$ASM_HOME/bin/asmcmd mkdir +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/
fi
set -e
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/spfile`echo $INSTANCE_NAME`.ora +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora
$ASM_HOME/bin/asmcmd cp `echo $DATABASE_HOME`/dbs/orapw`echo $INSTANCE_NAME` +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME`
if [ -f nodename.log ] ; then
rm nodename.log
fi
$ASM_HOME/bin/olsnodes -n >> nodename.log
export ORACLE_SID=$INSTANCE_NAME
export ORACLE_HOME=`cat /etc/oratab | grep -w $INSTANCE_NAME | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
srvctl add database -db `echo $DBNAME` -oraclehome `echo $DATABASE_HOME` -dbtype RAC -spfile +`echo $DBNAME`_DATADG/`echo $DBNAME`/PARAMETERFILE/spfile`echo $DBNAME`.ora -pwfile +`echo $DBNAME`_DATADG/`echo $DBNAME`/PASSWORD/orapw`echo $DBNAME` -dbname `echo $DBNAME` >> $LOG_FILE
cat nodename.log |while read LINE ;
do
srvctl add instance -db `echo $DBNAME` -i `echo $DBNAME``echo ${LINE}|awk '{print $2}'` -n `echo ${LINE}|awk '{print $1}'`
done
srvctl start database -db `echo $DBNAME` >> $LOG_FILE
rm `echo $DATABASE_HOME`/dbs/spfile`echo $INSTANCE_NAME`.ora >> $LOG_FILE
rm `echo $DATABASE_HOME`/dbs/orapw`echo $INSTANCE_NAME` >> $LOG_FILE
export ORACLE_SID=$SOURCEINSTANCE
export ORACLE_HOME=`cat /etc/oratab | grep -w $SOURCEINSTANCE | cut -d ":" -f 2`
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/lib:.
sqlplus "/as sysdba" << EOF >> $LOG_FILE
alter user sys account lock password expire ;
exit
EOF
########### end of script #####################
Exceptional bro u rocked
ReplyDelete