Oracle use cases for APIs

Oracle link + snapshot (sync)

The following script ingests links an environment database dSource (Oracle SID / Instance) and then takes a snapshot. See parameters for required values that you must provide.

This script demonstrates how to use name values inputs and get the respective Delphix object and/or object reference for use in the json input in downstream API calls.

Filename: link_oracle.sh # or link_oracle_jq.sh

Edit the file to update the parameters as required for your environment.

Copy

##########################################################
Parameter Initialization
DMIP=172.16.160.195
#DMPORT=8282
DMUSER=delphix_admin
DMPASS=delphix

# Required for Database Link and Sync
SOURCE_SID="DPXDEV01"                               # Source Environment Database SID
SOURCE_NAME="DPXDEV01"                              # Delphix dSource Name
SOURCE_ENV="Oracle Target"                          # Source Environment Name
SOURCE_GRP="Oracle_Source"                          # Delphix Group Name
DB_USER="delphixdb"                                 # Source Database SID user account
DB_PASS="delphixdb"                                 # Source Database SID user password

##########################################################
NO CHANGES REQUIRED BELOW THIS POINT
##########################################################

$ ./link_oracle.sh                                  # or
# ./link_oracle_jq.sh

Authenticating on http://172.16.160.195/resources/json/delphix
Session and Login Successful ...

group reference: GROUP-35
sourceconfig reference: ORACLE_SINGLE_CONFIG-1
primaryUser reference: HOST_USER-3

Linking Source Database ...
Job: JOB-92
Job: JOB-92 100.0% Completed ...

Container: ORACLE_DB_CONTAINER-19
Running SnapSync ...

Job: JOB-93
Current status as of Mon Aug 15 13:07:53 EDT 2016: RUNNING: 0.0% Completed
Current status as of Mon Aug 15 13:08:03 EDT 2016: RUNNING: 15.0% Completed
Current status as of Mon Aug 15 13:08:13 EDT 2016: RUNNING: 35.0% Completed
Current status as of Mon Aug 15 13:08:24 EDT 2016: RUNNING: 59.0% Completed
Current status as of Mon Aug 15 13:08:34 EDT 2016 : RUNNING : 66.0% Completed
Current status as of Mon Aug 15 13:08:44 EDT 2016 : RUNNING : 74.0% Completed
Job: JOB-93 100.0% Completed ... Done ...

Oracle provision

Filename: provision_oracle.txt

Shown below is how to use the CLI to provision an Oracle 11g database that is already ingested into the Delphix Engine.

The key is to get the object reference names first. For example, to get the source database container name:

Copy

ssh delphix_admin@[delphix_engine_ip_address_or_hostname]
> database
> ls
> select "[database_name]"
> ls
Delphix5002HWv7 database
> select 'DPXDEV01'
Delphix5002HWv7 database 'DPXDEV01'
> ls

Properties
    type: OracleDatabaseContainer
    name: DPXDEV01
    ...
    reference: ORACLE_DB_CONTAINER-18
    ...

Minimum parameters required to provision:

Copy

Delphix5002HWv7 database provision
> *commit
=== POST /resources/json/delphix/database/provision ===
{
    "type": "OracleProvisionParameters",
    "container": {
        "type": "OracleDatabaseContainer",
        "name": "VBITT", # Delphix Object Name, Typically matches VDB name
        "group": "GROUP-36" # group ls select "[group_name]" ls
    },
    "source": {
        "type": "OracleVirtualSource",
        "mountBase": "/mnt/provision" # Delphix Filesystem Mount path
    },
    "sourceConfig": {
        "type": "OracleSIConfig",
        "repository": "ORACLE_INSTALL-3", # repository, select "[repository_name]"
        "databaseName": "VBITT", # New VDB Name
        "uniqueName": "VBITT",
        "instance": {
            "type": "OracleInstance",
            "instanceName": "VBITT",
            "instanceNumber": 1
        }
    },
    "timeflowPointParameters": {
        "type": "TimeflowPointSemantic",
        "container": "ORACLE_DB_CONTAINER-18" # select "[database_name]" ls
    }
}
=== RESPONSE ===

Sample CLI session

Copy

Delphix5002HWv7 database> setopt trace=false
Delphix5002HWv7 database> provision
Delphix5002HWv7 database provision > *ls
Properties  
  type: OracleProvisionParameters  
  container:  
    type: OracleDatabaseContainer  
    name: (required)  
    description: (unset)  
    diagnoseNoLoggingFaults: true  
    group: (required)  
    performanceMode: DISABLED  
    preProvisioningEnabled: false  
    sourcingPolicy: (unset)  
    credential: (unset)  
    maskingJob: (unset)  
    newDBID: false  
    openResetlogs: true  
    physicalStandby: false  
    source:  
      type: OracleLiveSource  
      name: (unset)  
      archivelogMode: true  
      config: (unset)  
      configParams: (unset)  
      configTemplate: (unset)  
      customEnvVars: (unset)  
      dataAgeWarningThreshold: 900sec  
      fileMappingRules: (unset)  
      manualProvisioning: false  
      mountBase: (required)  
      nodeListenerList: (unset)  
      operations: (unset)  
      redoLogGroups: 3  
      redoLogSizeInMB: 0  
  sourceConfig:  
    type: OraclePDBConfig  
    cdbConfig: (required)  
    databaseName: (required)  
    environmentUser: (unset)  
    linkingEnabled: true  
    repository: (unset)  
    services: (unset)  
  timeflowPointParameters:  
    type: TimeflowPointSemantic  
    container: (required)  
    location: LATEST_POINT  
    username: (unset)
OperationsdefaultsDelphix5002HWv7 database provision > *edit container
Delphix5002HWv7 database provision container> *ls
Properties  
  type: OracleDatabaseContainer  
  name: (required)  
  description: (unset)  
  diagnoseNoLoggingFaults: true  
  group: (required)  
  performanceMode: DISABLED  
  preProvisioningEnabled: false  
  sourcingPolicy: (unset)
Delphix5002HWv7 database provision container> *set name=VBITT
Delphix5002HWv7 database provision container> *set group=GROUP-36
Delphix5002HWv7 database provision container> *back
Delphix5002HWv7 database provision > *edit source
Delphix5002HWv7 database provision source > *ls
Properties  
  type: OracleLiveSource  
  name: (unset)  
  archivelogMode: true  
  config: (unset)  
  configParams: (unset)  
  configTemplate: (unset)  
  customEnvVars: (unset)  
  dataAgeWarningThreshold: 900sec  
  fileMappingRules: (unset)  
  manualProvisioning: false  
  mountBase: (required)  
  nodeListenerList: (unset)  
  operations: (unset)  
  redoLogGroups: 3  
  redoLogSizeInMB: 0
Delphix5002HWv7 database provision source > *set type=OracleVirtualSource
Delphix5002HWv7 database provision source > *set mountBase=/mnt/provision
Delphix5002HWv7 database provision source > *back
Delphix5002HWv7 database provision > *edit sourceConfig
Delphix5002HWv7 database provision sourceConfig > *ls
Properties  
  type: OraclePDBConfig  
  cdbConfig: (required)  
  databaseName: (required)  
  environmentUser: (unset)  
  linkingEnabled: true  
  repository: (unset)  
  services: (unset)
Delphix5002HWv7 database provision sourceConfig > *set type=OracleSIConfig
Delphix5002HWv7 database provision sourceConfig > *ls
Properties  
  type: OracleSIConfig  
  databaseName: (required)  
  environmentUser: (unset)  
  instance: (required)  
  linkingEnabled: true  
  nonSysCredentials: (unset)  
  nonSysUser: (unset)  
  repository: (required)  
  services: (unset)  
  uniqueName: (required)
Delphix5002HWv7 database provision sourceConfig > *set databaseName=VBITT
Delphix5002HWv7 database provision sourceConfig > *set repository=ORACLE_INSTALL-3
Delphix5002HWv7 database provision sourceConfig > *set uniqueName=VBITT
Delphix5002HWv7 database provision sourceConfig > *set instance.instanceName=VBITT
Delphix5002HWv7 database provision sourceConfig > *set instance.instanceNumber=1
Delphix5002HWv7 database provision sourceConfig > *ls
Properties  
  type: OracleSIConfig  
  databaseName: VBITT  
  environmentUser: (unset)  
  instance:  
    type: OracleInstance  
    instanceName: VBITT  
    instanceNumber: 1  
  linkingEnabled: true  
  nonSysCredentials: (unset)  
  nonSysUser: (unset)  
  repository: '/u02/ora/app/product/11.2.0/dbhome_1'  
  services: (unset)  
  uniqueName: VBITT
Delphix5002HWv7 database provision sourceConfig > *back
Delphix5002HWv7 database provision > *edit timeflowPointParameters
Delphix5002HWv7 database provision timeflowPointParameters> *ls
Properties  
  type: TimeflowPointSemantic  
  container: (required)  
  location: LATEST_POINT
Delphix5002HWv7 database provision timeflowPointParameters> *set container=ORACLE_DB_CONTAINER-18
Delphix5002HWv7 database provision timeflowPointParameters> *back
Delphix5002HWv7 database provision > *commit  
  VBITT  
  Dispatched job JOB-348  
  DB_PROVISION job started for "Oracle Target Virtual Databases/VBITT".  
  Starting provision of the virtual database "VBITT".  
  Creating new TimeFlow.  
  Generating recovery scripts.  
  Exporting storage.  
  Mounting filesystems for the virtual database instance "1".  
  Mounting read-only archive log filesystem for the virtual database instance "1".  
  Recovering Oracle database.  
  \|/-  
  Opening the virtual database "VBITT".  
  Opening Oracle database.  
  Oracle recovery was successful.  
  Unmounting read-only archive log filesystem for the virtual database instance "1".  
  The virtual database "VBITT" was successfully provisioned.  
  DB_PROVISION job for "Oracle Target Virtual Databases/VBITT" completed successfully.
Delphix5002HWv7 database>

With the setopt trace=true option set, you can convert the JSON output from the above CLI provision command to the RESTful API cURL commands. If VBITT exists, be sure to delete it first.

Request:

Copy

curl X POST -k --data @http://172.16.160.177/resources/json/delphix/database/provision \
  -b cookies.txt -H "Content-Type: application/json" <<EOF
{
  "type": "OracleProvisionParameters",
  "container": {
    "type": "OracleDatabaseContainer",
    "name": "VBITT",
    "group": "GROUP-36"
  },
  "source": {
    "type": "OracleVirtualSource",
    "mountBase": "/mnt/provision"
  },
  "sourceConfig": {
    "type": "OracleSIConfig",
    "repository": "ORACLE_INSTALL-3",
    "databaseName": "VBITT",
    "uniqueName": "VBITT",
    "instance": {
      "type": "OracleInstance",
      "instanceName": "VBITT",
      "instanceNumber": 1
    }
  },
  "timeflowPointParameters": {
    "type": "TimeflowPointSemantic",
    "container": "ORACLE_DB_CONTAINER-18"
  }
}
EOF

Response:

Copy

{
  "type": "OKResult",
  "status": "OK",
  "result": "ORACLE_DB_CONTAINER-22",
  "job": "JOB-353",
  "action": "ACTION-649"
}

Put all the commands above within a shell script to automate the complete process of provisioning an Oracle 11.2.0.4 database.

Notice that the script below looks up 4 object references for use within the JSON input into the API.

Filename: provision_oracle.sh# or provision_oracle_jq.sh

Edit the file to update the parameters as required for your environment.

Copy

######################  DELPHIX CORP  #######################
Parameter Initialization 
DMIP=172.16.160.195
DMUSER=delphix_admin
DMPASS=delphix
COOKIE="~/cookies.txt"
COOKIE=`eval echo $COOKIE`
CONTENT_TYPE="Content-Type: application/json"
DELAYTIMESEC=10
BaseURL="http://${DMIP}/resources/json/delphix"

# Required for Database Link and Sync ...
# VDB_NAME="VBITT"                                      # Delphix VDB Name
MOUNT_BASE="/mnt/provision"                              # Delphix Engine Mount Path
SOURCE_GRP="Oracle_Target"                               # Delphix Engine Group Name
TARGET_ENV="Oracle Target"                               # Target Environment used to get repository reference value
SOURCE_SID="DPXDEV01"                                    # dSource name used to get db container reference value 

##############################################
NO CHANGES REQUIRED BELOW THIS POINT
##############################################

Sample Output

Copy

$ ./provision_oracle.sh
# or ./provision_oracle_jq.sh
Authenticating on http://172.16.160.195/resources/json/delphix
Session and Login Successful ...
group reference: GROUP-36
container reference: ORACLE_DB_CONTAINER-36
env reference: UNIX_HOST_ENVIRONMENT-3
repository reference: ORACLE_INSTALL-1
Provisioning VDB from Source Database ...
Job: JOB-155
Current status as of Mon Aug 15 23:40:51 EDT 2016 : RUNNING 0.0% Completed
Current status as of Mon Aug 15 23:40:51 EDT 2016 : RUNNING 0.0% Completed
Current status as of Mon Aug 15 23:41:01 EDT 2016 : RUNNING 9.0% Completed
Current status as of Mon Aug 15 23:41:11 EDT 2016 : RUNNING 45.0% Completed
Current status as of Mon Aug 15 23:41:21 EDT 2016 : RUNNING 45.0% Completed
Current status as of Mon Aug 15 23:41:31 EDT 2016 : RUNNING 46.0% Completed
Current status as of Mon Aug 15 23:41:41 EDT 2016 : RUNNING 48.0% Completed
Current status as of Mon Aug 15 23:41:51 EDT 2016 : RUNNING 60.0% Completed
Job: JOB-155 COMPLETED 100.0% Completed ... Done ...
$

Filename: provision_oracle_child.sh# or provision_oracle_child_jq.sh

Copy

$ ./provision_oracle_child.sh
# or ./provision_oracle_child_jq.sh
Authenticating on http://172.16.160.195/resources/json/delphix
Session and Login Successful ...
group reference: GROUP-36
container reference: ORACLE_DB_CONTAINER-118
env reference: UNIX_HOST_ENVIRONMENT-9
repository reference: ORACLE_INSTALL-6
Provisioning VDB from Source Database ...
Job: JOB-857
Current status as of Mon Sep 5 22:48:28 EDT 2016 : RUNNING 0.0% Completed
Current status as of Mon Sep 5 22:48:28 EDT 2016 : RUNNING 0.0% Completed
Current status as of Mon Sep 5 22:48:38 EDT 2016 : RUNNING 9.0% Completed
Current status as of Mon Sep 5 22:48:48 EDT 2016 : RUNNING 27.0% Completed
Current status as of Mon Sep 5 22:48:58 EDT 2016 : RUNNING 42.0% Completed
Current status as of Mon Sep 5 22:49:08 EDT 2016 : RUNNING 45.0% Completed
Current status as of Mon Sep 5 22:49:28 EDT 2016 : RUNNING 46.0% Completed
Current status as of Mon Sep 5 22:49:38 EDT 2016 : RUNNING 48.0% Completed
Current status as of Mon Sep 5 22:49:48 EDT 2016 : RUNNING 51.0% Completed
Current status as of Mon Sep 5 22:50:08 EDT 2016 : RUNNING 71.0% Completed
Job: JOB-857 COMPLETED 100.0% Completed ... Done ...