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;

Comments

Post a Comment

Popular posts from this blog

Data pump - "ORA-39786: Number of columns does not match between export and import databases"

Today I was asked if it was possible to import data for a specific table from a data pump export file, which was taken from when a table had a different structure to what it has now. e.g. when the export was taken, there were 10 columns.  Now there are 11. I thought that was simple, and I was sure I'd heard of this being done before.  Simply import using the "CONTENT=DATA_ONLY" parameter.  Right? Apparently not: Centos-/home/oracle/demo> impdp DEMO_USER/yyyyyyy directory=INBOUND dumpfile=demo_data.dmp logfile=impdpdemo.log content=data_only tables=xxxxxxxx Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 08 July, 2014 14:32:20 Copyright (c) 2003, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production Master table "DEMO_USER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "DEMO_USER"."SYS_IMPORT_TABLE_01":  xxxxxxxx/******** directory=INBOUND dumpfile...
Read more

APEX, SERT, and EPG

APEX, SERT, and EPG Application Express, Security Evaluation and Recommendation Tool, and the Embedded PL/SQL Gateway My organisation has only just started seriously working with APEX and have recently launched our first internal production app (that was a challenge... I was told that this app needed to be in production in 2 days and nobody had bothered to check with me what was needed to make this happen).  I've learned a lot about APEX in the past few weeks and I've got a lot more to go. I recently came across SERT ( https://github.com/OraOpenSource/apex-sert ) and it sounds like a really nifty tool.  Today I found some time to try it out and see if our dev team might find it useful.  I decided to install it in our dev database.  This uses the EPG rather than ORDS. The install didn't go so well.  The first install failed near the end because I think I used a lowercase 'y' for a response somewhere and it got confused, bombing out with an ORA-06502...
Read more

RMAN-05531 During RMAN Duplicate from Active Data Guard Standby

[oracle@target dbs]$ rman auxiliary sys@dup target sys@source [...] RMAN> duplicate target database to DUP from active database db_file_name_convert '/u2/data/PROD','/u2/data/DUP' LOG_FILE_NAME_CONVERT '/u2/data/PROD/onlinelog','/u2/data/DUP' nofilenamecheck; Starting Duplicate Db at 25-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=171 device type=DISK RMAN-00571: =================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======= RMAN-00571: =================================================== RMAN-03002: failure of Duplicate Db command at 02/25/2015 16:04:44 RMAN-05501: aborting duplication of target database RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy Using Oracle 11.2.0.3 (anything below 11.2.0.3.6), this is the error if an attempt is made to duplicate from an activ...
Read more