Provisioning a vPDB from a non-multitenant source

Delphix supports provisioning a virtual pluggable database (vPDB) from a snapshot of a non-multitenant (non-MT) source database (VDB). This feature is only available through the API or command-line interface (CLI). This topic describes how to provision a vPDB from a snapshot of a non-MT VDB (also simply referred below as source VDB) using CLI.

The high-level workflow for the provisioning is as follows:

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 (where the new vPDB will be plugged into) must be a physical CDB. Virtual CDB targets are not supported.
For provisioning from a source VDB of Oracle 11g version, the VDB must be upgraded before provisioning (i.e. you must use the Upgrade Option 1 described below). Upgrade option 2 cannot be used in this case.

Choose the upgrade option to use if the target CDB (where the new vPDB will be plugged into) version is newer than the source VDB. Refer to the prerequisites section below.
Create the required hook scripts.
Take source VDB snapshot.
Provision the vPDB to the target CDB using the VDB snapshot.

Prerequisites

Environment requirements

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

Source host with a non-MT Oracle 11g or newer source database.
VDB Target host for provisioning a non-MT 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.

Select upgrade option

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 source VDB is provisioned and before vPDB is provisioned via CLI. This upgrade is done manually by the user, before initiating the vPDB provisioning API/CLI. This option requires the ability to upgrade to the Oracle target version on the VDB target host.
Upgrade Option 2: After plugging the newly provisioned vPDB into the target CDB database. This upgrade is performed by Delphix API/CLI using a hook script.
If the source VDB's Oracle version is 11g, Upgrade Option 1 must be selected. For source VDB's with Oracle versions 12c and above, either option may be selected.

Prepare hook scripts

There are following three scripts used during this procedure and must be created manually before executing the vPDB provisioning command by CLI:

Pre-snapshot Hook on source VDB: This hook will open the database in "read only" mode and issue a call to the dbms_pdb.describeprocedure to generate an XML file called delphix_plugin.xml, describing the VDB. The XML file will be used to plug the source VDB data files into the target CDB. The source 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. This can be added using Delphix Management Application UI from the Datasets panel by selecting the source VDB and then going to Configuration -> Hooks tab.
Post-snapshot Hook on source VDB: This hook will return the VDB to "read write" mode. This is an optional script. The source VDB can also remain read only. This can be added using Delphix Management Application UI from the Datasets panel by selecting the source VDB and then going to Configuration -> Hooks tab.
Post-plug Hook for vPDB: This script will run as a hook present on the Linked CDB target host and will be executed after the newly provisioned vPDB is plugged into the target CDB.
a. If no upgrade is required or using Upgrade Option 1, then this script will call the Oracle script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql to convert the VDB into a PDB. b. If using Upgrade Option 2, this script will upgrade the database and then call Oracle script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql.
This script must be created manually and stored in the root folder of the Delphix Toolkit directory of the target CDB 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.

Refer to the Sample Scripts section below for the content of scripts.

Note about the sample scripts provided in this document:

These scripts are provided as-is, without warranty of any kind or commercial support through Delphix.
The scripts may need to be modified depending on the Oracle version or the SQL script package version being used.

Workflow

Link the non-MT source database as a dSource within Delphix.
Provision a non-MT Oracle VDB from the dSource onto the VDB target host. This will be referred to as the Golden VDB.
1. If no upgrade is required:
  1. Create a Pre-snapshot hook on the Golden VDB.
  2. (Optional) Create a Post-snapshot hook on the Golden VDB.
  3. Take a snapshot of the Golden VDB.
  4. Create a PDB conversion script named dx-post-plug-hook.shin the root of the Delphix toolkit directory of the Linked CDB target host.
2. If using Upgrade Option 1:
  1. Upgrade the Golden VDB to the Oracle target version: manually upgrade the database and point it to the new Oracle home.
  2. Create a Pre-snapshot hook on the Golden VDB.
  3. (Optional) Create a Post-snapshot hook on the Golden VDB.
  4. Take a snapshot of the Golden VDB.
  5. Create a PDB conversion script named dx-post-plug-hook.shin the root of the Delphix toolkit directory of the Linked CDB target host.
3. If using Upgrade Option 2:
  1. Create a Pre-snapshot hook on the Golden VDB.
  2. (Optional) Create a Post-snapshot hook on the Golden VDB.
  3. Take a snapshot of the Golden VDB.
  4. Create a PDB Upgrade and Conversion script named dx-post-plug-hook.shin the root of the Delphix toolkit directory of the Linked CDB target host.
Select the snapshot on the Golden VDB created above and provision a vPDB to the Linked CDB target. The detailed steps for this are documented in the next section.

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.
^{$ 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
```

(Optional) 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.
1. Linux and Unix hosts, this mount path must be the full path and not include symlinks.
  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: PasswordCredential (*)
    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)
  
Operations
defaults

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/Golden 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 source 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

This script converts the source/Golden VDB datafiles into PDB datafiles. 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=$1
SCRIPT_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 and conversion script

This script upgrades the newly provisioned vPDB to the target CDB version and then converts the source/Golden VDB datafiles into PDB datafiles. 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=$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

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