OraMatt: YABAOracle

#!/bin/sh  -xv
#
#
# 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
# srvctl stop instance -d ovmrac -i ovmrac1 -o immediate
# srvctl stop instance -d ovmrac -i ovmrac2 -o immediate
#
# export ORACLE_SID=ovmrac1
#
# rman target / <<EOF
#
# run {
# startup mount;
# allocate channel matt_backup_disk1 type disk;
# backup spfile;
# backup as copy database include current controlfile format '+FRA/clone_dir/rac_clone_%d_%p_%s_%u'  ;
# release channel matt_backup_disk1;
# }
# exit
# EOF
#
# srvctl start database -d ovmrac
# srvctl status database -d ovmrac
#
# End of RMAN Script
# 

# Let's go!
clear

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

echo "----------------------------------------"
echo "Please enter both Nodes: "
echo "Node1 Name>"
#read NODE1
export NODE1=matt1
echo $NODE1
echo "Node2 Name>"
#read NODE2
export NODE2=matt2
echo $NODE2

echo "----------------------------------------"

echo "What is the target database connect string? >"
echo "Please enter like matt-scan:1521/prime   >"
#read TARGET_DB
export TARGET_DB=matt-scan:1521/prime
echo $TARGET_DB

echo "----------------------------------------"

echo "What is the name of the new database? >"
read DBNAME
echo $DBNAME

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 "----------------------------------------"

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

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

# Shutdown new database
srvctl stop database -d $DBNAME

# Create a minimal init.ora
echo "*.compatible='11.2.0.2.0' " >mini_init.ora
echo "*._compression_compatibility='11.2.0'" >>mini_init.ora
echo "*.control_files='+DATA_Q4/no/controlfile/current.260.752598491','+RECO_Q4/no/controlfile/current.256.752598491'" >>mini_init.ora
echo "*.db_name='${DBNAME}'" >>mini_init.ora

# Single Instance stuff
export ORACLE_SID=${DBNAME}1
sqlplus / as sysdba <<EOF
#shutdown immediate;
startup nomount pfile='/home/oracle/mini_init.ora';
exit;
EOF

# See what's running
ps -ef | grep pmon | grep $DBNAME

# 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@matt-scan:1521/test <<EOF
change archivelog all validate;
duplicate database to ${DBNAME} pfile = /home/oracle/mini_init.ora backup location '+RECO_Q4/clone_dir'
NOFILENAMECHECK;
exit;
EOF

# SQL for controlfile
echo "set heading off;" >get_controlfile.sql
echo "set echo off;" >>get_controlfile.sql
echo "spool clone_controlfile.tmp" >>get_controlfile.sql
echo "select '*.control_files='''||name||'''' from v\$controlfile;" >>get_controlfile.sql
echo "exit;" >>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
@get_controlfile.sql
exit;
EOF

cat clone_controlfile.tmp

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

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

# Script for starting up Node2
echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1" >start_node2.sh
echo "export PATH=$PATH:$ORACLE_HOME/bin" >>start_node2.sh
echo "export ORACLE_SID=${DBNAME}2" >>start_node2.sh
echo "/u01/app/oracle/product/11.2.0/db_1/bin/sqlplus / as sysdba <<EOF" >>start_node2.sh
echo "startup pfile='/home/oracle/${DBNAME}_RAC.ora';" >>start_node2.sh
echo "exit;" >>start_node2.sh
echo "EOF" >>start_node2.sh
chmod +x start_node2.sh

cat start_node2.sh

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

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

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

echo "----------------------------------------"

# Status?
srvctl status database -d $DBNAME

# Sanity Check
sqlplus matt/matt@matt-scan:1521/${DBNAME} <<EOF
spool sanity_check.tmp
select count(*) from matt.emp_test;
select file_name from dba_data_files;
exit;
EOF

cat sanity_check.tmp