CLI cookbook: provisioning a virtual PDB from a non-multitenant source database

Delphix supports provisioning a vPDB from a non-multitenant source database. This feature is only available through the API or command-line interface.

This topic describes how to provision a virtual pluggable database (vPDB) from a non-multitenant source database using the command-line interface.

This feature has the following restrictions:

Transparent Data Encryption (TDE) is not supported.

The provision point must correspond to a snapshot. Provisioning from a point in time between snapshots is not supported.

The target CDB must be a physical CDB. Virtual CDB targets are not supported.

Prerequisites

Provisioning a vPDB from a non-multitenant source has the following environment requirements:

Source host with a non-multitenant Oracle 11g or newer source database.
VDB target host for provisioning a virtual non-multitenant VDB from the source database.
CDB target host with a running Oracle target version CDB. The target CDB will be automatically linked if it is not already linked.

The target CDB can be a newer Oracle version than the source database (for example, the source is 12.2 and target is 19c). When an upgrade is also required, there are two options for upgrading the database:

Upgrade Option 1: After provisioning the VDB. This option requires the ability to upgrade to the Oracle target version on the VDB target host.
Upgrade Option 2: After plugging into the Linked CDB target database.

There are three scripts used during this procedure:

Pre-snapshot Hook on VDB: This hook will open the database in "read only" mode and issue a call to the dbms_pdb.describe procedure to generate an XML file describing the VDB.
Post-snapshot Hook on VDB: This hook will return the VDB to "read write" mode.
Non-CDB to PDB Script: This script will run as a hook present on the Linked CDB target host. This should call into the Oracle script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql to convert the VDB into a PDB. This script should also upgrade the vPDB if doing Upgrade Option 2.

Workflow

Link the non-multitenant Oracle 11g or newer source database as a dSource within Delphix.
Provision a non-multitenant Oracle VDB from the dSource onto the VDB target host. This will be referred to as the Golden VDB.
(If using Upgrade Option 1) Upgrade the Golden VDB to the Oracle target version: manually upgrade the database and point it to the new Oracle home. This step is only necessary if the source and target Oracle versions are not the same and the data files will not be upgraded when they are converted below.
Create a Pre-snapshot hook on the Golden VDB to open the database in read only mode and issue the dbms_pdb.describe procedure call to create an XML file called delphix_plugin.xml. The XML file will be used to plug the Golden VDB data files into the target CDB. The Golden VDB must be open read only during the subsequent snapshot so that the VDB data files do not require recovery when plugging them into the target CDB.
(Optional) Create a Post-snapshot hook on the Golden VDB to remove the database from read only mode. The Golden VDB can also remain read only.
Take a snapshot of the Golden VDB.
Create a PDB conversion script named dx-post-plug-hook.sh in the root of the Delphix toolkit directory of the Linked CDB target host. The name of the vPDB being provisioned/converted will be supplied by Delphix as the first parameter to the script when it invokes the script. The VDB data files will already be plugged into the Linked CDB target at the time the script is invoked. The script should do the following:
1. (If using Upgrade Option 2) Upgrade the vPDB data files prior to the conversion.
2. Call into $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql and perform any customizations for the multitenant conversion.
Select a snapshot (point-in-time not supported) on the Golden VDB that has the delphix_plugin.xml file and provision a virtual PDB to the Linked CDB target. Virtual CDB targets are not supported.Note: This step can be executed via the API / CLI only, and will not be allowed via the Delphix UI.

CLI procedure to provision a vPDB from a VDB

Log into the Delphix command-line interface using the admin user or a user with admin privileges.
CODE
```
$ ssh admin@YOUR_ENGINE
```
Move to the database provisioning command line object.
CODE
```
delphix> database provision
```
Set the parameter type to OracleMultitenantProvisionParameters.
CODE
```
set type=OracleMultitenantProvisionParameters
```

Set the login details for the provision and Delphix OS user who is to perform the provision.

CODE

delphix database provision *> set username=delphix
delphix database provision *> set credential.type=PasswordCredential
delphix database provision *> set credential.password=delphix

Give the dataset a name.

CODE

delphix database provision *> set container.name=vpdb

Place the new dataset in a Group that appears in the Delphix GUI, in this case, the Targets group.
CODE
```
delphix database provision *> set container.group=Targets
```
Set the destination mount point which Delphix NFS mounts are to be linked to under the virtual PDB. This folder must exist at a file system level on the Linked CDB target host. Do not use single quotes around the mount path.
CODE
```
delphix database provision *> set source.mountBase="/mnt/provision"
```
If automatically restarting the vPDB is not required after a reboot of the Linked CDB target host, set this to option to false. False is possibly a better option given the container database would need to be running prior to any attempt to pull up a vPDB.
CODE
```
delphix database provision *> set source.allowAutoVDBRestartOnHostReboot=false
```
Supply the destination container database name. The container database should already be discovered. This will be where the vPDB will ultimately be placed.
CODE
```
delphix database provision *> set sourceConfig.cdbConfig=CDBSTAGE
```
Name the vPDB. This is what it will appear as in the destination container database.
CODE
```
delphix database provision *> set sourceConfig.databaseName=vpdb
```
Supply the source Golden VDB details. In this example, the provision will use the latest snapshot available from the Golden VDB as the point in time from which to provision the vPDB. A specific snapshot can also be picked, but an arbitrary point in time is not supported.
CODE
```
delphix database provision *> set timeflowPointParameters.type=TimeflowPointSemantic
delphix database provision *> set timeflowPointParameters.container=gold_vdb
delphix database provision *> set timeflowPointParameters.location=LATEST_SNAPSHOT
```

Check that all the settings you require are in place using the "ls" command.

CODE

delphix database provision *> ls
Properties
  type: OracleMultitenantProvisionParameters
  container:
    type: OracleDatabaseContainer
    name: vpdb (*)
    description: (unset)
    diagnoseNoLoggingFaults: true
    group: Targets (*)
    performanceMode: DISABLED
    preProvisioningEnabled: false
    sourcingPolicy: (unset)
  credential:
    type: Password Credential (*)
    password: ******** (*)
  masked: (unset)
  maskingJob: (unset)
  source:
    type: OracleVirtualPdbSource (*)
    name: (unset)
    allowAutoVDBRestartOnHostReboot: false (*)
    config: (unset)
    customEnvVars: (unset)
    fileMappingRules: (unset)
    LogCollectionEnabled: false
    mountBase: /mnt/provision (*)
    operations: (unset)
    parentTdeKeystorePassword: (unset)
    parentTdeKeystorePath: (unset)
    tdeExportedKeyFileSecret: (unset)
  sourceConfig:
    type: OraclePDBConfig
    cdbConfig: CDBSTAGE (*)
    databaseName: vpdb (*)
    environmentUser: (unset)
    linkingEnabled: true
    nonSysCredentials: (unset)
    nonSysUser: (unset)
    repository: (unset)
    services: (unset)
  timeflowPointParameters:
    type: TimeflowPointSemantic
    container: gold_vdb (*)
    location: LATEST_SNAPSHOT (*)
  username: delphix (*)
  VirtualCdb: (unset)  Operationsdefaults

Initiate the provision by committing the operation in the CLI.

CODE

delphix database provision *> commit
  vpdb
  Dispatched job JOB-333
  DB_PROVISION job started for "Targets/vpdb".
  Starting provision of virtual PDB database "vpdb" converted from a single tenant database.
  Preparing multitenant container database "CDBSTAGE".
  Creating new TimeFlow.
  Generating recovery scripts.
  Exporting storage.
  Preparing XML manifest file prior to plugin.
  Plugging in Oracle pluggable database.
  Running user-defined post plug hook.
  Opening Oracle pluggable database.
  Setting OMF destination for Oracle pluggable database.
  Creating PDB tempfiles.
  Checking Oracle pluggable database plugin violations.
  DB_PROVISION job for "Targets/vpdb" completed successfully.

To refresh the data in the vPDB from production, first, refresh the Golden VDB from the dSource, then refresh the vPDB from the new snapshot in the Golden VDB.

There are some workflow customizations required for RAC databases:

The PDB conversion script must be in the root of the Delphix toolkit directory for all the target CDB RAC instances.
The Golden VDB Pre-Snapshot hook, as provided below, will not work in a clustered (RAC) environment with more than one active instance because it only shuts down the local instance. dbms_pdb.describewill not execute while an instance is open read-write. The workarounds are:
1. Provision the Golden VDB as single-instance, either by provisioning to a non-RAC target or by provisioning to a RAC target with only one active instance. The sample hook will work in this case.
2. Write a customized pre-snapshot hook that shuts down all instances, restarts only one instance in read-only mode, and runs dbms_pdb.describe.
3. Manually perform the actions of the hook: shutdown the Golden VDB, restart one of the instances in read-only mode and then run dbms_pdb.describe.

Sample scripts

Golden VDB pre-snapshot hook

Restarts the source VDB in read only mode and runs dbms_pdb.describe to generate an XML file describing the VDB. The XML file will be used to plug the VDB into the Linked CDB target. The target for the XML file must be $DELPHIX_MOUNT_PATH/$DELPHIX_DATABASE_UNIQUE_NAME/datafile/delphix_plugin.xml.

CODE

#!/bin/sh

sqlplus "/ AS SYSDBA" <<-EOF
  whenever sqlerror exit 2;
  spool $DELPHIX_MOUNT_PATH/$DELPHIX_DATABASE_UNIQUE_NAME/datafile/presnapshot.
out replace
  shutdown immediate
  startup mount
  alter database open read only;
  exec dbms_pdb.describe(pdb_descr_file=>'$DELPHIX_MOUNT_PATH/$DELPHIX_DATABASE_UNIQUE_NAME/datafile/delphix_plugin.xml');
  exit;
EOF

Golden VDB post-snapshot hook

This is only necessary if the VDB should not be left in read-only mode after the snapshot.

CODE

#!/bin/sh

sqlplus "/ AS SYSDBA" <<-EOF
  whenever sqlerror exit 2;
  spool $DELPHIX_MOUNT_PATH/$DELPHIX_DATABASE_UNIQUE_NAME/datafile/postsnapshot.out replace
  shutdown immediate
  startup
  exit;
EOF

PDB conversion script

The script should be named dx-post-plug-hook.sh and reside in the root of the Delphix toolkit directory of the Linked CDB target host. Delphix will supply the name of the PDB being provisioned/converted as the first parameter.

The VDB datafiles will have already been plugged into the target CDB at the time the script is invoked and the virtual PDB will be in the mounted (not open) state. The PDB conversion script should return with the virtual PDB in either the mounted or open (not restricted) state. Delphix does not enforce a time-out for the script.

CODE

#!/bin/sh
DELPHIX_PDB_NAME=$1SCRIPT_DIR="$( cd "$( dirname "$0" )" && pwd )"
CONVERT_LOGFILE=$SCRIPT_DIR/$DELPHIX_PDB_NAME-pdbconvert.log

sqlplus "/ AS SYSDBA" <<-EOF
  whenever sqlerror exit 2;
  spool $CONVERT_LOGFILE replace
  alter session set container=$DELPHIX_PDB_NAME;
  @?/rdbms/admin/noncdb_to_pdb.sql
  exit;
EOF

PDB upgrade script

The following script will upgrade the vPDB. Use a wrapper that runs both this script and the prior conversion script (or combine the two in a single script) if doing both an upgrade and a conversion.

CODE

#!/bin/sh

DELPHIX_PDB_NAME=$1
SCRIPT_DIR="$( cd "$( dirname "$0" )" && pwd )"
UPGRADE_LOGFILE=$SCRIPT_DIR/$DELPHIX_PDB_NAME-dx-post-plug-upgrade.log
UPGRADE_LOGDIR=$SCRIPT_DIR/$DELPHIX_PDB_NAME-upgrade

mkdir $UPGRADE_LOGDIR
cd $ORACLE_HOME/rdbms/admin
switches="-c '$DELPHIX_PDB_NAME' -l $UPGRADE_LOGDIR"
$ORACLE_HOME/perl/bin/perl catctl.pl $switches catupgrd.sql &>> $UPGRADE_LOGFILE