Customizing a VDB using Configure Clone Hook (KBA4011)

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

Description

For Oracle VDBs, hooks are bash shell-scripts that are executed on the target database server at various points during VDB operations as documented in Customizing Oracle Management.  The Delphix virtualization engine starts a shell on the target database server, sets a few shell environment variables as documented in Hook Operations, and then executes the shell commands you specify.

The Configure Clone hook executes immediately after the initial provisioning of a VDB, and also executes after each refresh operation.  It is the most commonly-used hook as it is ideal for customizing a VDB.

Running INSERT Statements in a Hook

The best method with Oracle databases is the SQL*Plus command-line program executed from the shell.  All Delphix VDB hooks provide the environment variables ORACLE_HOME, ORACLE_SID, and ORACLE_BASE with appropriate values, but you may also want to set the PATH and LD_LIBRARY_PATH variables as well.  At the top of your hook script, you may want to include commands as below.

export PATH=${ORACLE_HOME}/bin:${PATH}
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}

A log file of the script should be created, so create the name of that log file in a variable for later use...

_logFile="/tmp/configure_clone_hook_`date '+%Y%m%d_%H%M%S'`.log"

This specifies that a log file will be created in the universal "/tmp" temporary file directory with the name "configure_clone_hook_YYMMDD_HHMISS.log, where "YYMMDD" is the date and "HHMISS" is the time the file was created.

Then, when executing SQL*Plus, I personally like to first validate that the ORACLE_HOME directory exists, that the necessary subdirectories also exist, and of course that the Oracle executables exist and are executable.

if [[ "${ORACLE_SID}" = "" ]]
then
  echo "`date` - ERROR: ORACLE_SID variable not set; aborting..." | tee -a ${_logFile}
  exit 1
fi
if [[ "${ORACLE_HOME}" = "" ]]
then
  echo "`date` - ERROR: ORACLE_HOME variable not set; aborting..."
| tee -a ${_logFile}    exit 1
fi
if [ ! -d ${ORACLE_HOME} ]
then
  echo "`date` - ERROR: ORACLE_HOME directory \"${ORACLE_HOME}\" not found; aborting..." | tee -a ${_logFile}
  exit 1
fi
if [ ! -x ${ORACLE_HOME}/bin/sqlplus ]
then
  echo "`date` - ERROR: executable \"${ORACLE_HOME}/bin/sqlplus\" not found; aborting..." | tee -a ${_logFile}
  exit 1
fi

Finally, once everything is ready, execute the SQL*Plus command-line utility, while entering the SQL*Plus and SQL commands through standard input redirection, saving all output to the log file.

${ORACLE_HOME}/bin/sqlplus / as sysdba << __EOF__ >> ${_logFile}
whenever oserror exit failure rollback
whenever sqlerror exit failure rollback
set echo on feedback on timing on
insert into table (col_1, col_2, col_3) values (1, 2, 3);
exit success commit
__EOF__
if (( $? != 0 ))
then
  echo "`date` - ERROR: SQL*Plus script failed; aborting..." | tee -a ${_logFile}
  exit 1
fi 

The INSERT command is the fourth line in the code fragment above.

Finally, if the hook is completing successfully, be sure to return the exit status of zero ("0") indicating successful completion back to Delphix, otherwise the hook will fail and (most likely) the entire operation (i.e. provision, refresh, etc) will also fail.  Note that only return a non-zero exit status back to Delphix if you wish the entire Delphix operation to fail.

Pre-Refresh and Post-Refresh Hooks

The example(s) running SQL*Plus as well as the Oracle DataPump Export and Import utilities are intended for use in the Pre-Refresh and Post-Refresh hooks in order to preserve the settings of Oracle database account passwords and database link definitions across a REFRESH operation. This resolves the problem where a REFRESH from the source completely replaces everything in a VDB with the values in the source database, which is often a production database.

You must reset Oracle database account passwords and database link definitions in a VDB once the VDB is initially provisioned. Note that without these hooks in the Pre-Refresh and Post-Refresh hooks, passwords and dblinks would have to redefined after every refresh too.

The scripts are named "ora_vdb_prerefresh.sh" and "ora_vdb_postrefresh.sh". See add_oratab_tnsname.sh.txt