Scripts automating creation of Oracle dNFS thin clone databases

Here, as I mentioned before, are the scripts I'm using to automate thin cloning.  See the original posts here (http://philsumner.blogspot.com/2014/06/clonedbpl-art-of-cloning-database.html) and here (http://philsumner.blogspot.com/2014/06/clonepl-thin-provisioning-of-oracle.html).

I welcome comments on these - I'm aware they're not perfect.  For my purposes, they do the job that I need them to do.  In doing this process I've already spotted various bits and pieces that should probably be changed.  Also please be aware that the conversion from script -> HTML hasn't been painless, so there may very well be mistakes around redirection that I haven't caught.  Use with care.

I put these scripts here as references for not only myself, but as something that others may find useful.

They're fairly well commented, I think.  If you have any questions, comment here or email me at blog -at- philsumner.co.uk

I should also add that I modified a copy of clone.pl (DocID 1210656.1) so that it only puts files ending in ".dbf" in the control file, this is how I can keep the RMAN script, archive logs and database files in the same directory.  I'm aware this isn't ideal, but it works for me!

clonedb_1.sh - Run on source to create a hot backup with archive logs

#!/bin/bash

# Script to create image copy of database and set up list of 
# archive logs with appropriate "catalog" commands
# for use with clonedb_2.sh and dNFS thin provisioning

# Inputs:
#   $1 = SID to backup
#   $2 = Directory where archivelogs are found

usage() {
printf '\n\nUsage: ./clonedb_1.sh ORACLE_SID ArchiveLogDir\n\n'
}

# Should be 2 parameters
[ "$#" -ne 2 ] && { usage ; exit 1 ; }

ARCHIVELOGDIR="$2"
OUTPUTDIR="/u1/nfs_shares/image_copy"

PATH=/usr/local/bin:$PATH
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1

printf 'Creating pfile from spfile'
echo "create pfile='$OUTPUTDIR/init${ORACLE_SID}.ora' from spfile;" | NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' sqlplus / as sysdba

# Find earliest required archive log
printf 'Working out earliest archivelog required'
START_SEQ=$(cat <<EOT | NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' sqlplus -s / as sysdba 2> /dev/null
set heading off
set feedback off
select max(sequence#)
from v\$log
group by thread#
order by thread#;
EOT
)

# Remove whitespace from result
START_SEQ=$(echo $START_SEQ | sed -e 's/^ *//' -e 's/ *$//')

# Increase by one - value returned by query is 
# the sequence currently being
# prepared, but not yet written to disk
START_SEQ=$(($START_SEQ + 1))

# Force log switch. Log seq being written now 
# is the one returned by the value
# in $START_SEQ
printf 'Switching logfile before backup'
echo "sql 'alter system archive log current';" | NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' rman target /

# Create backup
printf 'Starting backup as copy'
cat <<EOT | NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' rman target / > /tmp/RMAN_log.txt 2>&1
run {
  allocate channel d1 device type disk ;
  allocate channel d2 device type disk ;
  allocate channel d3 device type disk ;
  allocate channel d4 device type disk ;
  backup as copy database tag 'ROLLING_INC' format '$OUTPUTDIR';
}
EOT

# After backup completes, force another log switch
printf 'Switching logfile after backup'
echo "sql 'alter system archive log current';" | NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' rman target /

# Get sequence number of log file being written to now
FINISH_SEQ=$(cat <<EOT | NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' sqlplus -s / as sysdba 2> /dev/null
set heading off
set feedback off
select max(sequence#)
from v\$log
group by thread#
order by thread#;
EOT
)

# Remove whitespace from result
FINISH_SEQ=$(echo $FINISH_SEQ | sed -e 's/^ *//' -e 's/ *$//')

# Subtract by one - value returned by query is 
# the sequence being
# prepared, but not yet written to disk
FINISH_SEQ=$(($FINISH_SEQ-1))

# Get range of archivelogs dated between the 
# earliest and latest sequence 
# numbers found above, convert to file names, 
# and add these to an RMAN script
# - $(( )) forces Bash to treat variable as 
# number and ignore whitespace
# - ${...##*/} removes everything up to the 
# last slash from variable
# - Use ARCLOGDIR as tag to replace in script2 
# with the right directory
for ARCHIVELOGFILE in $(eval ls $ARCHIVELOGDIR/*{${START_SEQ}..${FINISH_SEQ}}*.arc)
do 
  echo "catalog archivelog 'ARCLOGDIR/${ARCHIVELOGFILE##*/}';" >> $OUTPUTDIR/archivelogs.txt
done

# Increase FINISH_SEQ by one to use in RECOVER UNTIL SEQUENCE...
FINISH_SEQ=$(($FINISH_SEQ + 1))

# Write "recover" and "open" commands to file
echo "recover database until sequence $FINISH_SEQ;" >> $OUTPUTDIR/archivelogs.txt
echo "alter database open resetlogs;" >> $OUTPUTDIR/archivelogs.txt

# Add temp tablespace, drop invalid one, and 
# rename temp2 to temp
# (missing from Oracle's clone.pl)
cat <<EOT >> $OUTPUTDIR/archivelogs.txt
sql 'CREATE TEMPORARY TABLESPACE TEMP2';
sql 'ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2';
sql 'DROP TABLESPACE TEMP';
sql 'ALTER TABLESPACE TEMP2 RENAME TO TEMP';
EOT

# Exit cleanly
exit 0;

clonedb_2.sh - Run on the clone system

#!/bin/bash 

# Phil Sumner (12/06/2014)

# Script to clone DB from RMAN image copy or file system copy
# of source DB visible on some kind of mount point (CIFS/NFS)

# Changes are stored on NFS filesystem

# Required environment variables:
# $ORACLE_BASE
# $ORACLE_HOME

# Assumptions:
# 1: Source files are available at $SOURCE_DIR
#      (hard coded right now)
# 2: NFS mount point for delta/change files is available at
#      /data/u1/app/oracle/oradata/{DB_NAME} ($ORADATA_SID)
# 3: Source pfile is available at ${SOURCE_DIR}/init${SOURCE_SID}.ora ($SOURCE_DIR/$SOURCE_PFILE)
# 4: Clone.pl is in /tmp
# 5: Source files are on read-only file system
# 6:  RMAN script to make DB consistent is 
#     available as archivelogs.txt in $SOURCE_DIR/$ARCHIVELOGS
# 7: Archivelogs are in $SOURCE_DIR/archivelogs

usage() {
  printf '\n'
  printf 'Usage: clonedb.sh SOURCE_SID NEW_SID\n'
  printf '\n'
  printf 'Also ensure the following environment variable is exported:\n'
  printf '  ORACLE_BASE - Usually /u1/app/oracle\n'
  printf '\n'
}

# Check number of parameters
[ "$#" -ne 2 ] && { usage ; exit 1 ; }
[ "${#2}" -gt 8 ] && { printf "ERROR: NEW_SID must be <= 8 characters\n\n" ; exit 1 ; }

SOURCE_SID="$1" # Take old SID from command line parameter
NEW_SID="$2" # Take new SID from parameter on command line

# Check environment variables are set
if [ -z "$ORACLE_BASE" ]
then
  usage
  printf 'Missing envinronment variable: ORACLE_BASE\n\n'
  exit 1
fi

# Set up directories required
SOURCE_DIR="/data/u1/app/oracle/image_copy"
ORADATA="$ORACLE_BASE/oradata"
ORADATA_SID="$ORADATA/$NEW_SID"
ORA_FRA="$ORACLE_BASE/fast_recovery_area/$NEW_SID"
ORA_ADUMP="$ORACLE_BASE/admin/$NEW_SID/adump"
ORA_DPDUMP="$ORACLE_BASE/admin/$NEW_SID/dpdump"
SOURCE_PFILE="init${SOURCE_SID}.ora"
NEW_PFILE="init${NEW_SID}.ora"
ARCHIVELOGS="archivelogs.txt"

# Logfiles for database creation steps
LOG1=/tmp/create_controlfile.log
LOG2=/tmp/rename_files.log
LOG3=/tmp/recover_db.log

# Environment variables for Clone.pl
export ORACLE_SID=$NEW_SID
export CLONE_FILE_CREATE_DEST="$ORADATA_SID"
export MASTER_COPY_DIR="$SOURCE_DIR"
export CLONEDB_NAME="$NEW_SID"

# Check if CLONE_FILE_CREATE_DEST exists and is an NFS mount point
if [ ! -d "$CLONE_FILE_CREATE_DEST" ]
then
  printf "Expected destination (%s) not a directory!\nCreating.. " "$CLONE_FILE_CREATE_DEST"
  mkdir -p $CLONE_FILE_CREATE_DEST
  if [ "$?" -ne 0 ]
  then
    printf ".. Error creating - exiting\n\n"
    exit 1
  else
    printf ".. Success\n"
  fi
fi

printf 'Checking if %s is on NFS mount ..' "$CLONE_FILE_CREATE_DEST"
MOUNT_TYPE=$(stat -f "$CLONE_FILE_CREATE_DEST" | awk -F\  '/nfs/ { print $6 }')
[ -z "$MOUNT_TYPE" ] && { printf 'Problem with provided path - check it exists.\n\n' ; exit 1 ; }
if [ "$MOUNT_TYPE" = 'nfs' ]
then
  printf '.. yes\n'
else
  printf 'Failed.  Check that this is mounted from an NFS server.\n\n'
fi

# Check if Oracle is running with the same SID as provided in CLONEDB_NAME
PID=$(pgrep -f -u oracle "ora_pmon_${NEW_SID}")
RETCODE="$?"
if [ "$RETCODE" -eq 0 ] 
then
  printf '\nOracle already running for provided SID:\n  ORACLE_SID=%s\n  PID=%d\n\n' "$NEW_SID" "$PID"
  exit 1
fi

# Check if directory structures exist
# Create if required
for DIRNAME in "$ORADATA" "$ORADATA_SID" "$ORA_FRA" "$ORA_ADUMP" "$ORA_DPDUMP"
do
  printf 'Checking for dir: %s ..' "$DIRNAME"
  if [ ! -d "$DIRNAME" ]
  then
    printf '.. does not exist. Creating\n'
    mkdir -p "$DIRNAME" > /dev/null 2>&1
    RETCODE="$?"
    [ "$RETCODE" -ne 0 ] && { printf "Failure creating %s - exiting\n\n" "$DIRNAME" ; exit 1 ; }
  else
    printf '.. yes\n'
  fi
done

# Move RMAN script to /tmp
# Replace references to ARCLOGDIR with proper path
if [ -e "$SOURCE_DIR/$ARCHIVELOGS" ]
then
  mv "$SOURCE_DIR/$ARCHIVELOGS" /tmp > /dev/null 2>&1
  sed -i "s;ARCLOGDIR;${SOURCE_DIR}/archivelogs;g" /tmp/$ARCHIVELOGS
else 
  printf 'RMAN script (%s/%s) not found\n\n' "$SOURCE_DIR" "$ARCHIVELOGS"
  exit 1;
fi

# Move pfile from source NFS mount to /tmp
if [ -e "$SOURCE_DIR/$SOURCE_PFILE" ] 
then
  mv "$SOURCE_DIR/$SOURCE_PFILE" "/tmp/$NEW_PFILE" > /dev/null 2>&1
  RETCODE="$?"
  if [ "$RETCODE" -ne 0 ]
  then
    # Clean up and exit
    printf "Error moving pfile (%s/%s) to /tmp\n\n" "$SOURCE_DIR" "$SOURCE_PFILE"
    mv /tmp/$ARCHIVELOGS $SOURCE_DIR/ > /dev/null 2>&1
    exit 1
  fi
else
  printf "Source PFILE (%s/%s) not found\n\n" "$SOURCE_DIR" "$SOURCE_PFILE"
  # Clean up and exit
  mv /tmp/$ARCHIVELOGS $SOURCE_DIR/ > /dev/null 2>&1
  exit 1;
fi

# Modify pfile - removing lines not required and changing SID
sed -i -e "s/$SOURCE_SID/$NEW_SID/g" \
-e '/memory_target/d' \
-e '/log_archive_d/d' \
-e '/remote_os_authent/d' \
"/tmp/$NEW_PFILE" > /dev/null 2>&1
RETCODE="$?"
[ "$RETCODE" -ne 0 ] && { printf "Error running 'sed' on /tmp/%s - exiting\n\n" "$NEW_PFILE" ; exit 1 ; }

# Add "clonedb=true" to end of pfile
echo '*.clonedb=true' >> "/tmp/$NEW_PFILE"
RETCODE="$?"
[ "$RETCODE" -ne 0 ] && { printf "Error adding line to /tmp/%s - exiting\n\n" "$NEW_PFILE" ; exit 1 ; }

printf '\nAbout to start creation of cloned DB.\n'

# Run clone.pl to create scripts to create DB
/usr/bin/perl /tmp/Clone.pl "/tmp/$NEW_PFILE" /tmp/create_db.sql /tmp/rename_db.sql
RETCODE="$?"
[ "$RETCODE" -ne 0 ] && { echo "Error running Clone.pl - exiting" ; exit 1 ; }

# Add QUIT lines to both new scripts as they don't have them in by default
echo "" >> /tmp/create_db.sql
echo "QUIT;" >> /tmp/create_db.sql
echo "" >> /tmp/rename_db.sql
echo "QUIT;" >> /tmp/rename_db.sql

# Run create control file script
echo "Running SQL*Plus to create new control file"
sqlplus / as sysdba @/tmp/create_db.sql > "$LOG1" 2>&1
RETCODE="$?"
[ "$RETCODE" -ne 0 ] && { echo "Failure creating new control file - exiting" ; exit 1 ; }

grep 'ORA-' "$LOG1" > /dev/null 2>&1
RETCODE="$?"
if [ "$RETCODE" -ne 1 ]
then
  echo "ORA-XXXX error detected during control file creation - exiting"
  echo "Check logfile: $LOG1"
  exit 1
fi

# Run rename script
echo "Running SQL*Plus to rename data files"
sqlplus / as sysdba @/tmp/rename_db.sql > "$LOG2" 2>&1
RETCODE="$?"
[ "$RETCODE" -ne 0 ] && { echo "Failure renaming data files - exiting" ; exit 1 ; }

grep 'ORA-' "$LOG2" 2> /dev/null | grep -v -E 'ORA-01152|ORA-01110|ORA-01109' > /dev/null 2>&1
RETCODE="$?"
if [ "$RETCODE" -ne 1 ]
then
  echo "Unexpected ORA-XXXX error detected during data file renaming - exiting"
  echo "Check logfile: $LOG2"
  exit 1
fi

grep -v 'ORA-01152' "$LOG2" > /dev/null 2>&1
RETCODE="$?"
if [ "$RETCODE" -eq 0 ]
then
  printf "Expected ORA-01152 error detected during data file renaming - recovery of datafiles required to make database consistent\n"
  printf "Starting recovery of datafiles using archivelogs\n"
  NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' rman target / @/tmp/$ARCHIVELOGS > "$LOG3" 2>&1
  if [ "$?" -ne 0 ]
  then
    printf 'Error during DB recovery, check log: %s\n' "$LOG3"
    exit 1
  else
    printf 'DB cloning and recovery complete\n\n'
  fi
fi

printf 'Moving init file to %s\n\n' "$ORACLE_HOME"
mv "${CLONE_FILE_CREATE_DEST}/${NEW_PFILE}" "${ORACLE_HOME}/dbs"
if [ "$?" -ne 0 ]
then
  printf "Error moving %s/%s to %s" "$CLONE_FILE_CREATE_DEST" "$NEW_PFILE" "${ORACLE_HOME}/dbs"
  exit 1
fi

# Exit normally
printf "Normal Exit - tidying up logfiles\n\n"
rm $LOG1 $LOG2 $LOG3 > /dev/null 2>&1
exit 0;