Oracle Enterprise Manager 12c Database as a Service (DBaaS) empowers Self Service/SSA Users to rapidly spawn databases on demand in cloud. The configuration and structure of provisioned databases depends on respective service template selected by Self Service user while requesting for database. In EM12c, the DBaaS Self Service/SSA Administrator has the option of hosting various service templates in service catalog and based on underlying DBCA templates.

Many times provisioned databases require production scale data either for UAT, testing or development purpose and managing DBCA templates with data can be unwieldy. So, we need to populate the database using post deployment script option and without any additional work for the SSA Users. The SSA Administrator can automate this task in few easy steps. For details on how to setup DBaaS Self Service Portal refer to the DBaaS Cookbook

In this article, I will list steps required to enable EM 12c DBaaS to provision databases with application data in two distinct ways using: 1) Data pump 2) Transportable tablespaces (TTS). The steps listed below are just examples of how to extend EM 12c DBaaS and you can even have your own method plugged in part of post deployment script option.

Using Data Pump to populate databases

These are the steps to be followed to implement extending DBaaS using Data Pump methodolgy:

1. Production DBA should run data pump export on the production database and make the dump file available to all the servers participating in the database zone [sample shown in Fig.1]
   
   

   -- Full export expdp FULL=y DUMPFILE=data_pump_dir:dpfull1%U.dmp, data_pump_dir:dpfull2%U.dmp PARALLEL=4 LOGFILE=data_pump_dir:dpexpfull.log JOB_NAME=dpexpfull

   Figure-1:  Full export of database using data pump

   
   
2. Create a post deployment SQL script [sample shown in Fig. 2] and this script can either be uploaded into the software library by SSA Administrator or made available on a shared location accessible from servers where databases are likely to be provisioned
   
   

   -- Full import declare     h1   NUMBER; begin -- Creating the directory object where source database dump is backed up.     execute immediate 'create directory DEST_LOC as''/scratch/nagrawal/OracleHomes/oradata/INITCHNG/datafile'''; -- Running import     h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'FULL', job_name => 'DB_IMPORT10');     dbms_datapump.set_parallel(handle => h1, degree => 1);     dbms_datapump.add_file(handle => h1, filename => 'IMP_GRIDDB_FULL.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);     dbms_datapump.add_file(handle => h1, filename => 'EXP_GRIDDB_FULL_%U.DMP', directory => 'DEST_LOC', filetype => 1);     dbms_datapump.start_job(handle => h1);     dbms_datapump.detach(handle => h1); end; /

   Figure-2: Importing using data pump pl/sql procedures

   
   
3. Using DBCA, create a template for the production database – include all the init.ora parameters, tablespaces, datafiles & their sizes
   
4. SSA Administrator should customize “Create Database Deployment Procedure” and provide DBCA template created in the previous step.
5. In “Additional Configuration Options” step of Customize “Create Database Deployment Procedure” flow, provide the name of the SQL script in the Custom Script section and lock the input (shown in Fig. 3). Continue saving the deployment procedure.
   
   
   
   

   Figure-3: Using Custom script option for calling Import SQL

   
   
6. Now, an SSA user can login to Self Service Portal and use the flow to provision a database that will also  populate the data using the post deployment step.

Using Transportable tablespaces to populate databases

Copy of all user/application tablespaces will enable this method of populating databases. These are the required steps to extend DBaaS using transportable tablespaces:

1. Production DBA needs to create a backup of tablespaces. Datafiles may need conversion [such as from Big Endian to Little Endian or vice versa] based on the platform of production and destination where DBaaS created the test database. Here is sample backup script shows how to find out if any conversion is required, describes the steps required to convert datafiles and backup tablespace.
   
2. SSA Administrator should copy the database (tablespaces) backup datafiles and export dumps to the backup location accessible from the hosts participating in the database zone(s).
3. Create a post deployment SQL script and this script can either be uploaded into the software library by SSA Administrator or made available on a shared location accessible from servers where databases are likely to be provisioned. Here is sample post deployment SQL script using transportable tablespaces.
   
4. Using DBCA, create a template for the production database – all the init.ora parameters should be included. NOTE: DO NOT choose to bring tablespace data into this template as they will be created
   
5. SSA Administrator should customize “Create Database Deployment Procedure” and provide DBCA template created in the previous step.
6. In the “Additional Configuration Options” step of the flow, provide the name of the SQL script in the Custom Script section and lock the input. Continue saving the deployment procedure.
7. Now, an SSA user can login to Self Service Portal and use the flow to provision a database that will also populate the data using the post deployment step.

More Information:

• Database-as-a-Service on Exadata Cloud
• Podcast on Database as a Service using Oracle Enterprise Manager 12c
• Oracle Enterprise Manager 12c Installation and Administration guide, Cloud Administration guide
• DBaaS Cookbook
• Screenwatch: Private Database Cloud: Set Up the Cloud Self-Service Portal
• Screenwatch: Private Database Cloud: Use the Cloud Self-Service Portal

Stay Connected:

Twitter |  Face book |  You Tube |  Linked in |  Newsletter