OraMatt: YABAOracle

#!/bin/bash
#
#
# Script to create database and duplicate with RMAN
#
# Matt D 6/02/2011
#
#
# Assumes a backup copy exists in ASM
# Creates RAC database via DBCA
# Clones from Backup
# Starts up RAC Database
#
#
# Here's my RMAN backup script
#!/bin/bash
#
#
# update per site requirements
# clear
#
# unset $DBNAME
# unset $ASMDG
# unset $BKUPPATH
#
# export ORACLE_BASE=/u01/app/oracle
# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
# export PATH=$PATH:$ORACLE_HOME/bin
#
# echo "----------------------------------------"
# echo "oracle_home is set to:"
# echo $ORACLE_HOME
# echo "----------------------------------------"
#
# echo "path is set to:"
# echo $PATH
# echo "----------------------------------------"
#
#
# echo "Please enter Database name to do cold backup:"
# read DBNAME
# echo $DBNAME
# echo "----------------------------------------"
#
#
# echo "Please enter ASM Disk Group name:"
# read ASMDG
# echo +${ASMDG}
# echo "----------------------------------------"
#
#
# echo "Please enter path to store backup to:"
# read BKUPPATH
# echo $BKUPPATH
# echo "----------------------------------------"
#
#
#
# srvctl stop instance -d ${DBNAME} -i ${DBNAME}1 -o immediate
# srvctl stop instance -d ${DBNAME} -i ${DBNAME}2 -o immediate
# srvctl stop database -d ${DBNAME}
# srvctl status database -d ${DBNAME}
#
# export ORACLE_SID=${DBNAME}1;
#
# rman target / <<EOF
#
# run {
# startup mount;
# allocate channel clone_backup_disk1 type disk;
# backup spfile;
# backup as copy database include current controlfile format '+${ASMDG}/${BKUPPATH}/%n_exa_clone_%T_%u';
# release channel clone_backup_disk1;
# shutdown immediate;
# }
# exit
# EOF
#
# srvctl start database -d ${DBNAME}
# srvctl start instance -d ${DBNAME} -i ${DBNAME}1 -o open
# srvctl start instance -d ${DBNAME} -i ${DBNAME}2 -o open
#
# srvctl status database -d ${DBNAME}
#
# # Check ASM dir
# #. oraenv <+ASM7
# #export ORACLE_SID=+ASM7
# #/u01/grid/bin/asmcmd <<EOF
# #ls -l +reco_q4/clone_dir
# #EOF
#
#
#
# End of RMAN Script
#
# Let's go!
clear

# Unset variables
echo "----------------------------------------"
echo "Unset Variables"
echo "----------------------------------------"
unset $DBAME
unset $ORACLE_SID
unset $NODE1
unset $NODE2

echo $DBNAME
echo $ORACLE_SID
echo $NODE1
echo $NODE2

# Set the Working Directory
export WORKINGDIR=`pwd`

# Update per site requirements
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

echo "----------------------------------------"
echo "Get Node Information"
echo "----------------------------------------"
echo "Please enter both Nodes: "
echo "Node1 Name>"
read NODE1
echo "Node1 is $NODE1"
echo "Node2 Name>"
read NODE2
echo "Node 2 is $NODE2"

echo "----------------------------------------"
echo "Get Target Database Connect String"
echo "----------------------------------------"
echo "What is the target database connect string? >"
echo "Please enter like rstexa1-scan:1521/thor   >"
read TARGET_DB
echo "The connect string is $TARGET_DB"

echo "----------------------------------------"
echo "Get Clone Database Name"
echo "----------------------------------------"
echo "What is the name of the new database? >"
read DBNAME
echo "The name of the clone is $DBNAME"

echo "----------------------------------------"
echo "Please enter the name for the"
echo "ASM Disk Group that the backup is storeed in"
echo "without the + symbol"
echo "----------------------------------------"
read ASMDG
echo "The ASM Disk Group is +${ASMDG}"

echo "----------------------------------------"
echo "Enter the path for the backup"
echo "----------------------------------------"
read BKUPPATH
echo "The path to the backup is $BKUPPATH"

echo "----------------------------------------"
echo "Running DBCA for RAC"
echo "----------------------------------------"
# Call DBCA RAC Version
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName $DBNAME -sid $DBNAME -sysPassword oracle -systemPassword oracle -emConfiguration NONE -storageType ASM -diskGroupName DATA_Q4 -nodelist $NODE1,$NODE2 -characterSet WE8IS08859P15 -listeners $DBNAME -memoryPercentage 25 -initParams compatible=11.2.0.2.0 -continueOnNonFatalErrors true

echo "----------------------------------------"
echo "Get the pfile for $DBNAME"
echo "----------------------------------------"
# Get the pfile for later
export ORACLE_SID=${DBNAME}1
sqlplus / as sysdba <<EOF
create pfile='$WORKINGDIR/${DBNAME}_RAC.ora' from spfile;
exit;
EOF

echo "----------------------------------------"
echo "Update $DBNAME pfile"
echo "----------------------------------------"
# Change the init.ora
cat $WORKINGDIR/${DBNAME}_RAC.ora | sed "s/*.memory_target/#*.memory_target/g" | sed "s/*._compression_compatibility='11.2.0.2'/*._compression_compatibility='11.2.0'/g" >$WORKINGDIR/${DBNAME}_RAC.tmp
mv $WORKINGDIR/${DBNAME}_RAC.tmp $WORKINGDIR/${DBNAME}_RAC.ora

echo "----------------------------------------"
echo "Shutdown $DBNAME"
echo "----------------------------------------"
echo "Would you like to continue? "
echo "Enter yes or no"
read DOWHAT
if [[ $DOWHAT = no ]]; then
	exit 1
fi
# Shutdown new database
srvctl stop database -d $DBNAME

echo "----------------------------------------"
echo "Create a simple init.ora for the clone process"
echo "----------------------------------------"
# Create a minimal init.ora
echo "*.compatible='11.2.0.2.0' " >$WORKINGDIR/mini_init.ora
echo "*._compression_compatibility='11.2.0'" >>$WORKINGDIR/mini_init.ora
echo "*.control_files='+DATA_Q4/no/controlfile/current.511.752822857'" >>$WORKINGDIR/mini_init.ora
echo "*.db_name='${DBNAME}'" >>$WORKINGDIR/mini_init.ora

echo "----------------------------------------"
echo "Startup $DBNAME with simple init.ora"
echo "----------------------------------------"
# Single Instance stuff
export ORACLE_SID=${DBNAME}1
sqlplus / as sysdba <<EOF
#shutdown immediate;
startup nomount pfile='$WORKINGDIR/mini_init.ora';
exit;
EOF

echo "----------------------------------------"
echo "See if $DBNAME is running"
echo "----------------------------------------"
# See what's running
ps -ef | grep pmon | grep $DBNAME

echo "----------------------------------------"
echo "Start the cloning process"
echo "----------------------------------------"
echo "Would you like to continue? "
echo "Enter yes or no"
read DOWHAT
if [[ $DOWHAT = no ]]; then
	exit 1
fi
# Export the SID to work around Doc ID 419440.1
export ORACLE_SID=${DBNAME}1
echo $ORACLE_SID
# Connect to new aux database
# Change per your site settings
rman auxiliary / target sys/oracle@$TARGET_DB <<EOF
change archivelog all validate;
duplicate database to ${DBNAME} pfile = $WORKINGDIR/mini_init.ora backup location '+${ASMDG}/${BKUPPATH}/'
NOFILENAMECHECK;
exit;
EOF

echo "----------------------------------------"
echo "Get the clone's new controlfile"
echo "----------------------------------------"
# SQL for controlfile
echo "set heading off;" >$WORKINGDIR/get_controlfile.sql
echo "set echo off;" >>$WORKINGDIR/get_controlfile.sql
echo "spool $WORKINGDIR/clone_controlfile.tmp" >>$WORKINGDIR/get_controlfile.sql
echo "select '*.control_files='''||name||'''' from v\$controlfile;" >>$WORKINGDIR/get_controlfile.sql
echo "exit;" >>$WORKINGDIR/get_controlfile.sql

# Get the new controlfile info for later
export ORACLE_SID=${DBNAME}1
echo $ORACLE_SID
sqlplus / as sysdba <<EOF
set heading off
set echo off
@$WORKINGDIR/get_controlfile.sql
exit;
EOF

cat $WORKINGDIR/clone_controlfile.tmp

echo "----------------------------------------"
echo "Update the $DBNAME RAC init.ora with the clone controlfile"
echo "----------------------------------------"
# Change the RAC based init to have the clone's controlfile
export CLONECONTROLFILE=`cat clone_controlfile.tmp | grep DATA`
echo $CLONECONTROLFILE
cat $WORKINGDIR/${DBNAME}_RAC.ora | sed "s/*.control_file/#.control_file/g" > $WORKINGDIR/${DBNAME}_RAC.ctlfile_tmp
cat $WORKINGDIR/clone_controlfile.tmp | grep DATA>> $WORKINGDIR/${DBNAME}_RAC.ctlfile_tmp
mv  $WORKINGDIR/${DBNAME}_RAC.ctlfile_tmp  $WORKINGDIR/${DBNAME}_RAC.ora

echo "----------------------------------------"
echo "Startup the clone"
echo "----------------------------------------"
echo "Would you like to continue? "
echo "Enter yes or no"
read DOWHAT
if [[ $DOWHAT = no ]]; then
	exit 1
fi

# Startup RAC Clone
export ORACLE_SID=${DBNAME}1
sqlplus / as sysdba <<EOF
shutdown immediate;
startup pfile='$WORKINGDIR/${DBNAME}_RAC.ora';
create spfile from pfile='$WORKINGDIR/${DBNAME}_RAC.ora';
exit;
EOF

echo "----------------------------------------"
echo "Start the clone on $NODE2"
echo "----------------------------------------"
echo "Would you like to continue? "
echo "Enter yes or no"
read DOWHAT
if [[ $DOWHAT = no ]]; then
	exit 1
fi
# Script for starting up Node2
echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1" >$WORKINGDIR/start_node2.sh
echo "export PATH=$PATH:$ORACLE_HOME/bin" >>$WORKINGDIR/start_node2.sh
echo "export ORACLE_SID=${DBNAME}2" >>$WORKINGDIR/start_node2.sh
echo "/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus / as sysdba <<EOF" >>$WORKINGDIR/start_node2.sh
echo "startup pfile='$WORKINGDIR/${DBNAME}_RAC.ora';" >>$WORKINGDIR/start_node2.sh
echo "create spfile from pfile='$WORKINGDIR/${DBNAME}_RAC.ora';" >>$WORKINGDIR/start_node2.sh
echo "exit;" >>$WORKINGDIR/start_node2.sh
echo "EOF" >>$WORKINGDIR/start_node2.sh
chmod +x $WORKINGDIR/start_node2.sh

cat $WORKINGDIR/start_node2.sh

# Copy new RAC init.ora to 2nd Node
scp $WORKINGDIR/${DBNAME}_RAC.ora oracle@$NODE2:$WORKINGDIR/
scp $WORKINGDIR/start_node2.sh oracle@$NODE2:$WORKINGDIR/

# Startup 2nd Instance
ssh $NODE2  $WORKINGDIR/start_node2.sh

# Start the Clone
#srvctl start database -d $DBNAME

echo "----------------------------------------"
echo "Check the status of $DBNAME"
echo "----------------------------------------"

# Status?
srvctl status database -d $DBNAME

echo "----------------------------------------"
echo "See if $DBNAME is using different datafiles"
echo "----------------------------------------"
# Sanity Check
sqlplus matt/matt@rstexa1-scan:1521/${DBNAME} <<EOF
set echo off;
set lines 200;
spool $WORKINGDIR/sanity_check.tmp
prompt Database Name:
select ora_database_name from dual;
prompt Data File List:
select file_name from dba_data_files;
prompt My test...should be 28
set autot on;
select count(*) from matt.emp_test;
prompt This Should Not Return Anything...
select * from emp_test where ename like '%Matt%';
exit;
EOF

cat $WORKINGDIR/sanity_check.tmp

# Clean Up
echo "----------------------------------------"
echo "Clean Up"
echo "----------------------------------------"
  rm $WORKINGDIR/sanity_check.tmp
  ssh $NODE2 rm $WORKINGDIR/start_node2.sh
  ssh $NODE2 rm $WORKINGDIR/${DBNAME}_RAC.ora
  rm $WORKINGDIR/start_node2.sh
  rm $WORKINGDIR/${DBNAME}_RAC.ora
  rm $WORKINGDIR/get_controlfile.sql
  rm $WORKINGDIR/clone_controlfile.tmp
  rm $WORKINGDIR/mini_init.ora</pre>