By Franck Pachot

Delphix is a tool for easy cloning of databases. The idea is that all is automated: the user can create a clone, rewind or refresh it with one click. However, I was suprised that the following common scenario is not managed by the Delphix engine:

  • You clone from production, say Oracle 12c
  • You upgrade the clone, say Oracle 19c
  • You test there
  • You refresh the clone from production, obviously being back to Oracle 12c

This is very common. You use clones to test the application, and testing on upgraded database version is probably the most common clone usage.

So, there’s an ‘Upgrade’ button, just near the refresh one, but this is a false-friend. It doesn’t upgrade anything but just sets the Oracle Home known by Delphix to the new one after you upgraded yourself. Because Delphix does not detect this change automatically. But Delphix requires it in order to run the toolkit actions. No problem, the upgrade of Oracle is easy with Oracle AutoUpgrade, and that’s just one additional manual action. So why is it called ‘Upgrade’ and not ‘Change Oracle Home’? That’s the problem: you can change only to a newer version Oracle Home. Then… how do you revert back to the previous version in order to refresh the clone? You can’t.

The only solution provided by the support (without additional consulting charges) is a manual action on the console. Not the Web console, but the telnet one.

That’s not exactly what I call a ‘CLI’ – Command Line Interface is supposed to accept actions and parameters other than stdin and return codes. But let’s try to automate that.

Here I’m showing a template for a Do-It-Yourself solution. Please don’t use it as-is. Test it. Because having access to the tty console rather than a clean CLI or API doesn’t allow for professional error handling.

SSH key

This console is accessible through ssh with the admin user. Or an admin user that you have defined in the management console. This is not the setup console.
I’ve written a glossary about this:

You don’t want to have hardcoded passwords, so better use passwordless authentication. I’ll do all that from the database server as root. so I check my public key that I’ve generated with `ssh-keygen`:

# cat ~/.ssh/
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDRIw5638wyewN716iARPTKpaeCP+HtNOEa5TSKfI8Eh3h3EUwb+H3qzrWtv/b0k147QC0ET93kf2Y4AgvoaFKvo3ms3U6pI5BtCBN3h49KCcj4k1sPKmytJap6G6C79BMZKoGbG6hOSQ7PbbHHPoSgSYiXrxaO3Rh8OqWl+EqSQ45TSLE5Nb6+YuASEeILSUv3fezE21/kZ4dxsYJeE+6pfaUHCm/sCTFKM7JZJsviQ/3usq+7m8w+AreedQXAYERq9tDdCcrCUkmrj3OhiLh3YoYre8XkZ0QiBT1bwhkPlxGO5aN5bkihqm2ETF3y9sbdf2d/xXpKTnx3tTWZo6tr [email protected]

This can be put once in the Delphix console.

This only time I’ll connect with the password I defined when creating the user from the GUI

:~$ ssh [email protected]

ip-10-0-1-10> ls


ip-10-0-1-10> user
ip-10-0-1-10 user> ls

dev       [email protected]
qa        [email protected]
labadmin  [email protected]
admin     [email protected]

ip-10-0-1-10 user> current

ip-10-0-1-10 user 'admin'> ls
    type: User
    name: admin
ip-10-0-1-10 user 'admin'> update
ip-10-0-1-10 user 'admin' update *> ls
    type: User
    name: admin

ip-10-0-1-10 user 'admin' update *> set publicKey="ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDRIw5638wyewN716iARPTKpaeCP+HtNOEa5TSKfI8Eh3h3EUwb+H3qzrWtv/b0k147QC0ET93kf2Y4AgvoaFKvo3ms3U6pI5BtCBN3h49KCcj4k1sPKmytJap6G6C79BMZKoGbG6hOSQ7PbbHHPoSgSYiXrxaO3Rh8OqWl+EqSQ45TSLE5Nb6+YuASEeILSUv3fezE21/kZ4dxsYJeE+6pfaUHCm/sCTFKM7JZJsviQ/3usq+7m8w+AreedQXAYERq9tDdCcrCUkmrj3OhiLh3YoYre8XkZ0QiBT1bwhkPlxGO5aN5bkihqm2ETF3y9sbdf2d/xXpKTnx3tTWZo6tr [email protected]"
ip-10-0-1-10 user 'admin' update *> commit;
ip-10-0-1-10 user 'admin'> exit
Connection to closed.

this is all, now I can ssh without providing the password

(re)set the Oracle Home

Let’s see how to automate the manual actions given by the support engineer. Here is my script and I explain later:

[email protected] 

for source in $({
ssh "$management" <<SSH
} | awk '/ true /{ print $1}'
) ; do
echo "# looking if source=$source is on this server and finding oracle home"
ssh "$management" <<SSH
select $source
} | awk '/repository/{sub("^ *repository: ","");repo=$0; print source,sid,$0}/ instanceName/{sid=$NF}' source=$source |
while read source sid repository ; do
 # this should run as root to see the current directory from/proc
 dbs=$(readlink /proc/$(pgrep -f _pmon_$sid\$)/cwd)
 # process it only if home is found (this source is on this host
 if [ -n "$dbs" ] ; then
  home=$(dirname "$dbs")
  if [ "$repository" != "$new" ] ; then
   echo "## setting new repository source=$source (sid=$sid home=$home) to repository=$new (previous was $repository)"
   ssh "$management" <<SSH
select $source
set repository="$new"


First, I define the ssh connection as I’ll have to ssh many times to read the answer and continue.
The first block will list all sources (VDBs) known by Delphix, with ssh output processed by awk.
The second block, for each source, will look at the configuration to get the “instanceName” which is the ORACLE_SID.
With this, I’ll get the Oracle home with:

but of course you can read /etc/oratab

Now, this is set in Delphix “repository” property where a prefix identifies the host (“environment” in Delphix terms) so I just replace the last part.

Here is the output when I run it on the Delphix Labs sandbox:

## setting new repository source=devdb (sid=devdb home=/u01/app/oracle/product/11.2.0/xe) to repository=TargetA/'/u01/app/oracle/product/18.0.0/xe' (previous was TargetA/'/u01/app/oracle/product/11.2.0/xe')
    type: OracleSIConfig
    name: devdb
    cdbType: NON_CDB
        type: PasswordCredential
        password: ********
    databaseName: devdb
    discovered: true
    environmentUser: TargetA/delphix
        type: OracleInstance
        instanceName: devdb
        instanceNumber: 1
    linkingEnabled: false
    nonSysCredentials: (unset)
    nonSysUser: (unset)
    reference: ORACLE_SINGLE_CONFIG-2
    repository: TargetA/'/u01/app/oracle/product/18.0.0/xe'
            type: OracleService
            discovered: true
            jdbcConnectionString: jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=broken)(ADDRESS=(PROTOCOL=tcp)(HOST=
    tdeKeystorePassword: (unset)
    uniqueName: devdb
    user: delphixdb

[[email protected] ~]# 

But please, test and adapt it. The idea here is that it can run to sync the Delphix information to the currently running databases, which is probably never a bad idea.