inserting existing test data to virtual database

  • 1
  • 1
  • Question
  • Updated 7 days ago
  • Answered
Hi - We are using Delphix in our orgnization. We are copying production data using Delphix. However we want to merge existing test data to the virtual db created from production. Is it possible to merge the data using hooks or some other way using Delphix?
Photo of mahesh nautiyal

mahesh nautiyal

  • 176 Points 100 badge 2x thumb

Posted 2 weeks ago

  • 1
  • 1
Photo of Tim Gorman

Tim Gorman, Delphix Field Services

  • 3,836 Points 3k badge 2x thumb
Official Response
Mahesh,

For Oracle VDBs, hooks are bash shell-scripts that are executed on the target database server at various points during VDB operations as documented HERE.  The Delphix starts a shell on the target database server, sets a few shell environment variables as documented HERE, 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 because it is ideal for customizing a VDB.

I strongly recommend that you avoid putting extensive coding into the hook, but instead have the hook call a shell-script residing on the target database server.  This makes testing much easier.

My email address is "tim.gorman@delphix.com";  please email if you'd like an example?

Thanks!

-Tim
Photo of Tim Gorman

Tim Gorman, Delphix Field Services

  • 3,836 Points 3k badge 2x thumb
Official Response
Mahesh,

Responding to your question about 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.  So, at the top of your hook script, you may want to include commands like...

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

I also believe that a log file of the script should be created, so I 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.  Only return a non-zero exit status back to Delphix if you wish the entire Delphix operation to fail.

I will send additional examples running SQL*Plus as well as the Oracle DataPump Export and Import utilities.  These examples 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 a 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.  It should only be necessary to reset Oracle database account passwords and database link definitions in a VDB once, when that VDB is initially provisioned.  But 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".

Please use these scripts as a template for other similar hook operations?

Please let me know what you think?

Thanks!

-Tim