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 CookbookIn 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:
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 exportexpdp 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
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
Normal
0
-- Full importdeclare 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
Using DBCA, create a template for the production database – include all the init.ora parameters, tablespaces, datafiles & their sizes
SSA Administrator should customize “Create Database Deployment Procedure” and provide DBCA template created in the previous step.
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
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:
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.
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).
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.
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
SSA Administrator should customize “Create Database Deployment Procedure” and provide DBCA template created in the previous step.
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.
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
