Delphix Products

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?

#Virtualization
#Masking

Mahesh,

The question is:  where that existing test data?

If the existing test data is in another database somewhere, then a Configure Clone hook can be used to extract the data from the other database and insert it into the VDB after initial provisioning and after each refresh.  Likewise if the existing test data is stored in "flat files" or an export dump somewhere.

However, if what you're asking is how to preserve existing test data in a VDB across a refresh from the source, then that would likely entail using both the Pre-Refresh and Post-Refresh hooks.  The Pre-Refresh hook, firing immediately prior to the refresh of the VDB from production, would be used to export the existing test data within the VDB.  Then, the VDB would be refreshed, which would of course replace all of the database in the VDB, so afterward the Post-Refresh hook fires and imports the exported test data back into the VDB.  The question here is:  how would you identify the existing test data in the VDB during the Pre-Refresh hook?

Are either of these the scenarios you had in mind?  If not, please elaborate further on your question?

Thanks!

-Tim

Tim, Thank you so much for your thoughtful answer. 

The data stays in another physical or virtual database. let me be more specific with my needs. Our performance team has around 20k test users in performance test database. The data for these users lives in many different tables in the database. So once we create production virtual copy of the database all the test data need to be merged with production data. This need to happen every time we refresh the database from production so that target database has both current production data and test data as well. 

Does configure clone hook works if i have data coming from many tables? Could you please guide me on how to build configure clone hook and how it can extract data and how it can insert the data?

Currently it takes 10 min for us to create a database from production, would configure hook slow down the process of database creation?

Your second part of email also hold true, may be you can recommend which one i can use on going basis... please let me know if i need to provide any more details...

 if you could guide me further on this that will be great help...thank you for your help in advance..

Thanks!
Mahesh

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

Hi Tim,

Thank you so much for your thoughtful answers. I tried to send you an email from my work id not sure if you received it. Could you please send me the examples at Mahesh.c.nautiyal@jpmchase.com and at Mahesh.nautiyal@gmail.com.

So are we saying that clone hook will run some shell scripts which will eventually run some insert sqls? so we need to upfront prepare those insert sqls and keep them ready somewhere?

If you have example of clone hook with sample inserts that will help me understand it and build a target as well for this.

Thanks!
Mahesh

thanks Tim !

Mahesh,

I sent you the requested email with attached shell-script with a ".txt" file-extension on 14-April to that email address.  Evidently, it was filtered even with the ".txt" file-extension.

Let me try again...

-Tim

please copy my gmail addresss(Mahesh.nautiyal@gmail.com).. just incase if it get filtered.

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

This discussion has been formally documented in the following knowledge article: https://support.delphix.com/Delphix_Virtualization_Engine/Delphix_Admin/Customizing_a_VDB_using_Configure_Clone_Hook_(KBA4011)