34 Customizing Database as a Service

This chapter describes how to customize Database as a Service. It contains the following sections:

• Pre and Post Request Creation / Deletion Scripts

Pre and Post Request Creation / Deletion Scripts

You can run custom scripts before and after a service instance has been created. These requests can range from some additional host commands on the machine on which the database was created or commands to perform additional book activities and other operations on the newly provisioned database.

Similarly, you can run scripts after a service instance has been deleted if you need to undo the changes made during service cleanup.

The custom script must follow certain guidelines and consists of four parts:

• Response File: The response file contains the service template and request specific information. Before the custom script is executed, the request procedure, the request procedure generates a response file (file with name-value pairs) containing the data from the service template as well as the request-specific data such as the SID that is auto computed, the hosts on which the database will be deployed, and so on.

• Driver Script: This is the key driver script that will be invoked by the request procedure. The driver script accepts only one argument which specifies the location of the response file.

• Other Scripts: Apart from the driver script, you can specify other perl or sql scripts. These scripts must be invoked from the driver script.

• Additional Variables: You can include a file containing additional variables that can used by the custom script as applicable.

After the scripts have been created, they must be uploaded as a directive into the Software Library. In the Describe page, the name of the directive and the description is displayed. Click the Configure tab.

Figure 34-1 Pre or Post Database Script: Configure

The driver script accepts one command line argument which must be in the INPUT_FILE format. This variable will be used at run-time to specify the location of the generated response file. Click the Select Files tab.

Figure 34-2 Pre or Post Database Script: Select Files

Specify any additional files that are required by the custom script. You can run the script from the same host on which the database instance or the database service was created or deleted.

Note:

if you need to change the content of the script, you must upload a newer version of the script to the Software Library. To use the latest version of the script, you must launch the Edit Service Template wizard and select the updated version of the script and save the template. This ensures that the latest version of the script will be used.

Selecting the Post SQL Script

A post SQL script can be created and uploaded to the Software Library. The self service administrator can select the script during service template creation. To specify the script, follow these steps:

1. Log in as a user with EM_SSA_ADMINISTRATOR role.
2. From the Enterprise menu, select Cloud. From the Cloud Home menu, select Service Template and then Create.
3. Select the Service Template type as Database and click Create.
4. Navigate to the Configuration page. Click the Search icon next to the Post SQL Script field and select the script from the Software Library.
5. In the Run As User drop down, the SYS user is selected by default.

   If this user is selected, the SYS password provided on Database page in the wizard is used to run the post database creation SQL script.

   If any other user is selected including Master Account, the password provided by the self service user for the Master Account while creating a service request will be used.

   Note:

   If the user account is locked, an error message is displayed. The user account must be open in the source database.

Sample Scripts

Sample scripts are available in the Software Library. To view the sample scripts, from the Enterprise menu, select Provisioning and Patching, then select Software Library. Select the DBaaS folder, then select Directives, and click the Samples folder to see the custom scripts.

Figure 34-3 Sample Scripts

Input Parameters for Pre and Post Database Scripts

This section lists the following:

• Input Parameters for DBaaS Pre and Post Request Creation Scripts

• Input Parameters for DBaaS Pre and Post Request Deletion Scripts

• Input Parameters for DBaas Pre and Post Custom Scripts when Provisioning a Standby DB

• Input Parameters for SchaaS Pre and Post Request Creation Scripts

• Input Parameters for SchaaS Pre and Post Request Deletion Scripts

• Input Parameters for PDBaaS Pre and Post Request Creation Scripts

• Input Parameters for PDBaaS Pre and Post Request Deletion Scripts

• Sample Input Files

• Sample PDBaaS Post Scripts

Input Parameters for DBaaS Pre and Post Request Creation Scripts

This table lists the input parameters for the DBaaS pre and post request creation scripts.

Table 34-1 DBaaS: Input Parameters for Pre and Post Request Creation Scripts

Variable Name	Description	Example
ssaRequestId	The Request ID of the SSA user request.	3
ssaUser	The SSA user name.	Ssa_user1
ssaTargetName	The zone target name.	Myzone1
ssaTargetType	The zone target type.	Self_service_zone
zoneTargetName	The zone target name.	Myzone1
zoneTargetType	The zone target type.	Self_service_zone
DBAAS_SSA_CUSTOM_PRE_SCRIPTS_URN	The URN of the directive in the software library which will be executed before the creation of the PDB target.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
DBAAS_SSA_CUSTOM_POST_SCRIPTS_URN	The URN of the directive in the software library which will be executed after the creation of the PDB target.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
SEL_HOST	The host selected as part of placement where the new database will be created.	Myhost.oracle.com
MEM_SIZE	The memory size of the requested database. This shall be the sum of sga and pga aggregate size or the total memory size.	2324
STORAGE_SIZE	The total storage size of the requested database	2324
HOST_CREDS	The credentials of the host where the database will be created.	Mycred1:dbaas_admin
PROFILE_COMPONENT_URN	The profile component URN.	oracle:defaultService:em:provisioning:1:cmp:COMP_Component:SUB_DbProfile:E9C8A650EFA5291DE04354B2F00AFF3E:0.1
WORK_DIR	The temporary working directory used for staging provisioning related files	/tmp
DB_ADMIN_PASSWORD_SAME	Indicates if the password provided for the sys, system and dbsnmp are same or different	true
DB_ORACLE_HOME_LOC	The oracle home in the selected host, from where the new Database will be created.	/scratch/aime/oraclehomes/dbbase/112030/dbhome1
DB_ORACLE_BASE_LOC	The oracle base of the selected oracle home.	/scratch/aime/oraclehomes/dbbase
TOTAL_MEMORY	The total memory for the chosen database if the memory management type is AMM.	0
SGA_MEMORY	The SGA memory value set for the database.	1744
PGA_MEMORY	The PGA target value set for the database.	580
INIT_PARAMS	The list of initialization parameter values that are set in the service template. The values are stored as paramName=paramValue[,paramName=paramValue]	processes=150,cluster_database=FALSE,db_name=,open_cursors=300,sga_target=1828716544,db_block_size=8192,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,diagnostic_dest={ORACLE_BASE},*.cpu_count=0,db_recovery_file_dest={ORACLE_BASE}/fast_recovery_area,log_archive_format=%t_%s_%r.dbf,compatible=11.2.0.0.0,audit_trail=DB,remote_login_passwordfile=EXCLUSIVE,undo_tablespace=UNDOTBS1,db_recovery_file_dest_size=4322230272,control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl"),pga_aggregate_target=608174080
DATABASE_TYPE	Type of database that will be provisioned as part of the request.	oracle_database or rac_database
USER_NAME	User Name (cannot be any of Oracle default accounts) which will be treated as the master account and will be used to login to the requested database.	Useracct1
INSTANCE_COUNT	Number of database instances that will be created in case if the request is for a RAC Database.	1
COMMON_DB_SID	The SID Prefix that has been provided in the Service Template with which a unique database name will be generated.	db000000
COMMON_DOMAIN_NAME	The Database Domain name provided in the Service Template which will be used to create the Database Target in Enterprise Manager.	Mycompany.com
LISTENER_PORT	The port number of the listener in the host to which the database will be attached.	1521
REF_ZONE	The reference zone used for the validations.	Myzone1
REF_POOL	The pool selected on the reference zone.	Mypool1
REF_HOST	The reference host chosen from the pool.	Myhost1.oracle.com
REF_TGT	The reference host chosen from the pool	Myhost1.oracle.com
COMMON_GLOBAL_DB_NAME	The global database domain name.	db000000.myhost.oracle.com

Input Parameters for DBaaS Pre and Post Request Deletion Scripts

This table lists the input parameters for DBaaS pre and post request deletion scripts.

Table 34-2 DBaaS: Input Parameters for Pre and Post Request Deletion Scripts

Variable Name	Description	ExampleDB
DB_SID	The database SID of the database which is chosen for deletion. This can be used to connect to the database.	db000000
HOST_NAME	The host name where the DB chosen for deletion, resides.	Myhost.oracle.com
DATABASE_TGT_GUID	The guid of the database which is chosen for deletion	E9C5A1149C266846E04354B2F00A9D7B
HOST_CREDS	The credentials to login to the database host to access the oracle home.	MYCREDS:DBAAS_ADMIN
ROOT_CREDS	The root credentials to login to the host as super user as specified in the pool.	MYROOTCREDS:DBAAS_ADMIN
TARGET_GUID	The guid of the database which is chosen for deletion.	E9C5A1149C266846E04354B2F00A9D7B
BACKUP_ENABLED	If the backup has been enabled for snap clone database target.	True
WORK_DIR	Temporary working directory used for deletion.	/tmp/workdir1
CUSTOM_DEL_PRE_SCRIPTS_URN	The URN of the directive in the software library which will be executed before the deletion of the Schema.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
CUSTOM_DEL_POST_SCRIPTS_URN	The URN of the directive in the software library which will be executed after the deletion of the Schema.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1

Input Parameters for DBaas Pre and Post Custom Scripts when Provisioning a Standby DB

This table lists the input parameters for the DBaaS pre and post request custom scripts when provisioning a standby database.

Variable Name	Description	Example
PRIMARY_ORACLE_HOME	The primary Oracle Home location	/u01/app/oracle/product/12201/dbhome_1
PRIMARY_ORACLE_BASE	The primary Oracle Base location	/u01/app/oracle
PRIMARY_DBNAME	The primary database name	TESTDB
PRIMARY_INSTANCE_LIST	The primary database instance list	TESTDB1:TESTDB2
PRIMARY_HOST_LIST	The primary database host list	host1.mycompany.com:host2.mycompany.com
PRIMARY_VIP_LIST	The primary database VIP list	h1-vip.mycompany.com:h2-vip.mycompany.com:
PRIMARY_LISTENER_PORT	The primary database listener port	1521
PRIMARY_SCAN_NAME	The primary SCAN name	cluster-s
PRIMARY_DOMAIN_NAME	The primary database domain name	mycompany.com
PRIMARY_PROTECTION_MODE	Data Guard protection mode	MAXIMIZEPERFORMANCE
WORK_DIR	Work directory	/tmp
DATA_DISK_GROUP	Data Disk Group	DATA
FRA_DISK_GROUP	Fast Recovery Area Disk Group	RECO
DATABASE_SERVICE_NAME	The primary database service name	testdb
DATABASE_NAME	The standby database name	STDBYDB
<STANDBY_DB_NAME>_DATABASE_SID	The standby database SID	STDBYDB
<STANDBY_DB_NAME>_RAC_DATABASE	Whether the standby database is a RAC	true
<STANDBY_DB_NAME>_DATABASE_DOMAIN_NAME	The standby database domain name	mycompany.com
<STANDBY_DB_NAME>_DATABASE_RAC_INSTANCE_COUNT	Number of RAC instances of the standby database	2
<STANDBY_DB_NAME>_DATABASE_READONLY_OPEN	Whether the standby database is open in Read Only mode	false
<STANDBY_DB_NAME>_DATABASE_ORACLE_HOME	The standby database Oracle Home	/u01/app/oracle/product/12201/dbhome_1
<STANDBY_DB_NAME>_DATABASE_HOST_LIST	The standby database host list	host3.mycompany.com host3.mycompany.com:
<STANDBY_DB_NAME>_DATABASE_INSTANCE_LIST	The standby database instance list	STDBYDB1:STDBYDB2
<STANDBY_DB_NAME>_DATABASE_SCAN_NAME	The standby SCAN Name	cluster-s
<STANDBY_DB_NAME>_DATABASE_REDO_MODE	The standby database redo mode	ASYNC
<STANDBY_DB_NAME>_DATABASE_LISTENER_PORT	The standby database listener port	1521

Input Parameters for SchaaS Pre and Post Request Creation Scripts

This table lists the input parameters for schema as a service pre and post request creation scripts.

Table 34-3 SchaaS: Input Parameters for Pre and Post Request Creation Scripts

Variable Name	Description	ExampleDB
ssaRequestID	The Request ID of the SSA User request.	3
ssaUser	The SSA user name.	Ssa_user1
ssaTargetName	The zone target name.	Myzone1
ssaTargetType	The zone target type.	Self_service_zone
zoneTargetName	The zone target name.	Myzone1
zoneTargetType	The zone target type.	Self_service_zone
SCHAAS_CUSTOM_PRE_SCRIPTS_URN	The URN of the directive in the software library which will be executed before the creation of the schema target.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
REQUEST_NAME	The name of the request	SYSMAN - Tue Oct 29 02:04:21 PDT 2013_CREATE_4_41
SERV_TEMPLATE_GUID	The service template guid.	E9C5A1149C266846E04354B2F00A9D7B
CREATE_SCHEMA_OPTION	Option to differentiate between creating empty schemas or schemas from profile. Possible values, EMPTY_SCHEMAS/SCHEMAS_FROM_PROFILE	EMPTY_SCHEMAS
MAX_NUMBER_OF_SCHEMAS	Maximum number of schemas permissible if the user does not select a profile. Applicable only when the Create Empty Schemas option is selected.	4
MASTER_ACCOUNT	The master account from the list of schemas selected.	MySchema
ROLE_NAME	Name of the database role which will assigned to all the schemas.	Mynewrole1
SCHEMA_PRIVILEGES	List of schema privileges that will be applied on the user accounts.	CREATE VIEW, CREATE DIR
ENFORCE_STORAGE_CONSTRAINT	Enforces the storage constraint on the service request. Possible values true/false	True
INITIAL_BLOCK_SIZE	The initial size of the tablespace.	2048M
AUTO_EXTEND_BLOCK_SIZE	The auto extend block size.	100M
TABLESPACE_ENCRYPTION_ALGORITHM	The algorithm used for tablespace encryption	AES128
SHARED_STORAGE_LOCATION	Shared staging location where the dump files are location across the hosts in the selected pool. Will be specified only if the create schema option is from an existing profile.	/oradbnfs/dumpfiles/
DBSERVICE_NAME	Database service name that will be provided during the request.	Service_88A370FC0FC1
DB_ORACLE_HOME_LOC	The Oracle Home for the Database where the new schema is created.	/scratch/aime/app/aime/11.2.0/dbhome_1
COMMON_DB_SID	The service name with which the Database can be connected in the host.	Mydb1
REMAP_SCHEMA_LIST	The list of schemas will that will be created on the target.	MySchema

Input Parameters for SchaaS Pre and Post Request Deletion Scripts

This table lists the input parameters for schema as a service pre and post request deletion scripts.

Table 34-4 SchaaS: Input Parameters for Pre and Post Request Deletion Scripts

Variable Name	Description	Example
DB_CONNECT_STRING	The connection string to establish a connection to the database.	DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SID= Myservice1)))
SCHEMA_NAME	Schema name selected for deletion.	Myservice1
PERM_TABLESPACE_NAME	The primary table space.	Myschema1
HOST_NAME	The host name where the DB chosen for deletion, resides.	Myhost.oracle.com
DATABASE_CREDS	The named credentials used for connecting to the database.	MYDBCREDS:DBAAS_ADMIN
GI_CREDS	The named credentials used for connecting to the Grid Infrastructure in case of RAC.	MYHOSTCREDS:DBAAS_ADMIN
WORK_DIR	Temporary working directory	/tmp/myworkdir1
CUSTOM_DEL_PRE_SCRIPTS_URN	The URN of the directive in the software library which will be executed before the deletion of the Schema	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
CUSTOM_DEL_POST_SCRIPTS_URN	The URN of the directive in the software library which will be executed after the deletion of the Schema	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1

Input Parameters for PDBaaS Pre and Post Request Creation Scripts

This table lists the input parameters for PDB as a service pre and post request creation scripts.

Table 34-5 PDBaaS: Input Parameters for Pre and Post Request Creation Scripts

Variable Name	Description	Example
ssaRequestId	The Request ID of the SSA User request.	3
ssaUser	The SSA user name.	Ssa_user1
ssaTargetName	The zone target name.	Myzone1
ssaTargetType	The zone target type.	Self_service_zone
PDBAAS_CUSTOM_PRE_SCRIPTS_URN	The URN of the directive in the software library which will be executed before the creation of the PDB target.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
PDBAAS_CUSTOM_POST_SCRIPTS_URN	The URN of the directive in the software library which will be executed after the creation of the PDB target.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
CREATE_PDB_OPTION	Option for creating Pluggable Database that includes empty PDB or PDB from profile. Possible values, EMPTY_PDB/PDB_FROM_PROFILE	EMPTY_PDB
PDB_CONNECT_STRING	The connection string to establish a connection to the pluggable database.	(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mycompany.com)(PORT=1531)))(CONNECT_DATA=(SERVICE_NAME=svc_mypdb)(INSTANCE_NAME=CDB01)(UR=A)(SERVER=DEDICATED)))
PDB_NAME	Name of the PDB target being created.	mypdb
CDB_CONNECT_STRING	The connection string to establish a connection to the container database.	(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mycompany.com)(PORT=1531)))(CONNECT_DATA=(SID=CDB01)))
MAX_TABLESPACE_ALLOWED	Maximum number of tablespaces allowed for pluggable database as a service.	10
ENFORCE_STORAGE_CONSTRAINT	Enforces the storage constraint on the service request. Possible values true/false.	True
TABLESPACE_ENCRYPTION_ALGORITHM	The algorithm used for tablespace encryption.	AES128
TABLESPACE_SIZE	The SID Prefix that has been provided in the Service Template with which a unique database name will be generated.	mydb
PDB_SID_PREFIX	SID Prefix for the new pluggable database.	Mypdb1
SHARED_STAGE_LOCATION	Shared staging location where the dump file are location across the hosts in the selected pool.	/oradbnfs/dumpfiles/
PDB_ADMIN_USER_NAME	Administrator user name for the new pluggable database.	Myadmin
PDB_SERVICE_NAME_PREFIX	The service name prefix for the pluggable database.	Service
PDBSERVICE_NAME	Pluggable database service name.	Service_000
DB_ORACLE_HOME_LOC	The Oracle Home for the Database where the new schema is created.	/scratch/aime/app/aime/11.2.0/dbhome_1
COMMON_DB_SID	The service name with which the Database can be connected in the host.	Mydb1
ROLE_OPTION	Role Option Custom New Role/ Existing Roles which will assigned to Pluggable database administrator.	CUSTOM_NEW_ROLE
INIT_PARAMS	The list of initialization parameter values that are set in the service template. The values are stored as paramName=paramValue[,paramName=paramValue].	*.open_cursors 300,*.cursor_sharing EXACT
CUSTOM_ROLE_NAME	The name of the database role which will assigned to Pluggable database administrator.	PDBAAS_OCT_29_2013_02_27_AM
CUSTOM_ROLE_DESC	The description for the new role to be created.	New db role to be assigned to pluggable database administrator.
PRIVILEGES	List of privileges of custom new role that will be applied on pluggable database administrator.	CREATE SESSION, ALTER SESSION, CREATE DIMENSION, CREATE INDEXTYPE, CREATE ANY OPERATOR, CREATE ANY PROCEDURE, CREATE ANY SEQUENCE, CREATE ANY INDEX, CREATE JOB, CREATE ANY MATERIALIZED VIEW, CREATE ANY TABLE, CREATE ANY TRIGGER, CREATE ANY TYPE, CREATE ANY VIEW, CREATE ANY SYNONYM, CREATE ANY DIRECTORY, SELECT ANY DICTIONARY
ROLES	Name of the database role which will assigned to all the schemas.	Mynewrole1
DB_ORACLE_HOME_LOC	The oracle home of the chosen Container database.	/scratch/aime/oraclehomes/dbbase/121010/dbhome1
COMMON_DB_SID	The Database sid for the selected Container Database where the new PDB will be created.	cdb

Input Parameters for PDBaaS Pre and Post Request Deletion Scripts

This table lists the input parameters for PDB as a service for pre and post request deletion scripts.

Table 34-6 SchaaS: Input Parameters for Pre and Post Request Deletion Scripts

Variable Name	Description	Example
DB_CONNECT_STRING	The connection string to establish a connection to the database.	DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SID= mypdb0001)))
PDB_NAME	PDB target name selected for deletion.	Mypdb001
HOST_NAME	The host name where the PDB chosen for deletion, resides.	Myhost.oracle.com
DATABASE_CREDS	The named credentials used for connecting to the database.	MYDBCREDS:DBAAS_ADMIN
GI_CREDS	The named credentials used for connecting to the Grid Infrastructure in case of RAC.	MYHOSTCREDS:DBAAS_ADMIN
WORK_DIR	Temporary working directory	/tmp/myworkdir1
CUSTOM_DEL_PRE_SCRIPTS_URN	The URN of the directive in the software library which will be executed before the deletion of the PDB target.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
CUSTOM_DEL_POST_SCRIPTS_URN	The URN of the directive in the software library which will be executed after the deletion of the PDB target.	oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1

Sample Input Files

This section contains a few sample input files for DBaaS, Schema as Service, and PDB as a Service.

Example 34-1 Sample Input File for DBaaS

ssaRequestId=2
ssaUser=ssa_user1
ssaTargetName=My Zone
ssaTargetType=self_service_zone
zoneTargetName=My Zone
zoneTargetType=self_service_zone
SEL_HOST=myhost.oracle.com
MEM_SIZE=2324
STORAGE_SIZE=2324
HOST_CREDS=AIME:SYSMAN
INIT_PARAMS=processes=150,cluster_database=FALSE,db_name=,open_cursors=300,
sga_target=1828716544,db_block_size=8192,audit_file_dest={ORACLE_BASE}/admin/
{DB_UNIQUE_NAME}/adump,diagnostic_dest={ORACLE_BASE},*.cpu_count=0,db_recovery
_file_dest={ORACLE_BASE}/fast_recovery_area,log_archive_format=%t_%s
_%r.dbf,compatible=11.2.0.0.0,audit_trail=DB,remote_login
_passwordfile=EXCLUSIVE,undo_tablespace=UNDOTBS1,db_recovery_file_dest
_size=4322230272,control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE
_NAME}/control01.ctl", "{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE
_NAME}/control02.ctl"),pga_aggregate_target=608174080
PROFILE_COMPONENT_URN=oracle:defaultService:em:provisioning:1:cmp:COMP
_Component:SUB_DbProfile:E9C8A650EFA5291DE04354B2F00AFF3E:0.1
DATABASE_TYPE=oracle_database
WORK_DIR=/tmp
DBAAS_SSA_CUSTOM_PRE_SCRIPTS_URN=oracle:defaultService:em:provisioning:1:cmp:COMP
_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
USER_NAME=rv
INSTANCE_COUNT=2
DB_ADMIN_PASSWORD_SAME=true
COMMON_DB_SID=db000000
COMMON_DOMAIN_NAME=mycompany.com
LISTENER_PORT=1527
COMMON_GLOBAL_DB_NAME=db000000.myhost.oracle.com
DB_ORACLE_HOME_LOC=/scratch/aime/oraclehomes/dbbase/112030/dbhome1
DB_ORACLE_BASE_LOC=/scratch/aime/oraclehomes/dbbase
TOTAL_MEMORY=0
SGA_MEMORY=1744
PGA_MEMORY=580
MEM_SIZE=2324
STORAGE_SIZE=2324
REF_ZONE=My Zone
REF_POOL=dbpool1
REF_HOST=myhost.oracle.com
REF_TGT= myhost.oracle.com

Example 34-2 Sample SQL Script

REM --- your custom sql script ---
REM --- sample: select * from v$database ---
REM --- If there are more that one sql file all files can be uploaded to the same component, and be called from the main script using 
@/<staging location>/sqlfile1.sql ----

Example 34-3 Sample Input File for Schema as a Service

ssaRequestId=1
ssaUser=SYSMAN
ssaTargetName=My Zone
ssaTargetType=self_service_zone
zoneTargetName=My Zone
zoneTargetType=self_service_zone
REQUEST_NAME=SYSMAN - Tue Oct 29 02:04:21 PDT 2013_CREATE_4_41
SERV_TEMPLATE_GUID=E9C5A1149C266846E04354B2F00A9D7B
CREATE_SCHEMA_OPTION=EMPTY_SCHEMAS
MAX_NUMBER_OF_SCHEMAS=4
REMAP_SCHEMA_LIST=MySchema
MASTER_ACCOUNT=MySchema
ROLE_NAME=schtemplate1_Oct_29_2013_02
SCHEMA_PRIVILEGES=CREATE SESSION,CREATE DIMENSION,CREATE INDEXTYPE,CREATE
OPERATOR,CREATE PROCEDURE,CREATE SEQUENCE,CREATE TABLE,CREATE TRIGGER,CREATE
TYPE,CREATE VIEW,CREATE SYNONYM
ENFORCE_STORAGE_CONSTRAINT=false
SCHAAS_CUSTOM_PRE_SCRIPTS_URN=oracle:defaultService:em:provisioning:1:cmp:COMP
_Directives:none:E9DE463D356E7433E04354B2F00A56C6:0.1
DBSERVICE_NAME=Service_88A370FC0FC1
DB_ORACLE_HOME_LOC=/scratch/aime/oraclehomes/dbbase/112030/dbhome1
COMMON_DB_SID=refdb

Example 34-4 Sample Input File for PDB as a Service

ssaRequestId=3
ssaUser=SYSMAN
ssaTargetName=My Zone
ssaTargetType=self_service_zone
zoneTargetName=My Zone
zoneTargetType=self_service_zone
CREATE_PDB_OPTION=EMPTY_PDB
MAX_TABLESPACE_ALLOWED=2
ENFORCE_STORAGE_CONSTRAINT=true
TABLESPACE_ENCRYPTION_ALGORITHM=None
PDBAAS_CUSTOM_POST_SCRIPTS_URN=oracle:defaultService:em:provisioning:1:cmp:COMP_Directives:none:0008270085383BBDE0535C56F20AB27E:0.5
PDB_SID_PREFIX=PDB
PDB_ADMIN_USER_NAME=oracle
PDB_SERVICE_NAME_PREFIX=svc_pdbps1
PDBSERVICE_NAME=svc_pdbps1
ROLE_OPTION=CUSTOM_NEW_ROLE
INIT_PARAMS=*.open_cursors=300,*.cursor_sharing=EXACT
CUSTOM_ROLE_NAME=PDBAAS_JUN_12_2014_23_16_PM
CUSTOM_ROLE_DESC=New db role to be assigned to pluggable database administrator.
PRIVILEGES=CREATE SESSION,ALTER SESSION,CREATE DIMENSION,CREATE INDEXTYPE,CREATE ANY OPERATOR,CREATE ANY PROCEDURE,CREATE ANY SEQUENCE,CREATE ANY INDEX,CREATE JOB,CREATE ANY MATERIALIZED VIEW,CREATE ANY TABLE, CREATE ANY TRIGGER, CREATE ANY TYPE, CREATE ANY VIEW, CREATE ANY SYNONYM, CREATE ANY DIRECTORY, SELECT ANY DICTIONARY
DB_ORACLE_HOME_LOC=/scratch/12c_rdbms/product/12.1.0/dbhome_1
COMMON_DB_SID=CDB06
PDB_NAME=pdbps1
PDB_CONNECT_STRING=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.example.com)(PORT=1531)))(CONNECT_DATA=(SERVICE_NAME=svc_pdbps1)(INSTANCE_NAME=CDB06)(UR=A)(SERVER=DEDICATED)))
CDB_CONNECT_STRING=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.example.com)(PORT=1531)))(CONNECT_DATA=(SID=CDB06)))

Sample PDBaaS Post Scripts

This section includes sample perl and sql scripts for PDBaaS.

Example 34-5 Sample PDBaaS Perl Script

use Getopt::Long;
	use File::Path;
	use File::Spec;
 
	#Store the passed input file path in a variable
	my $fileName = $ARGV[0];
 
#Print the input file content as it is...
print "Printing the variable values received from cloud request ...\n";
	open IN,"<",$fileName or die "Can't open file due to :$!";
	while(<>){
	  print $_;
	}
	close IN;
 
#Reads a text file with var=values pairs line delimited
#Setting the values in the environment
	sub readDat {
#Grab the passed parameter
		my $filename = $_[0];
		my @Data;

#The ubiquitous $i counter 
		my $i = 0;

#Register a filehandle
		local (*DATFILE);

#Open the file for read
		open (DATFILE, $filename) or print ( "Can't open $filename: $1");

#Read through the file one line at a time
FORA:while (<DATFILE>) {			

#Skip over any comments
		if ( /#.*/ ) {
				next FORA;
			}

 
#Clean up any extraneous garbage
			chomp;
			# no newline
			s/^\s+//;		
# no leading white
			s/\s+$//;		
# no trailing white

#		If clean up eliminated any data worth reading

#l	ets skip to the next line
next unless length;	

#			We can't load the lines with $VARS as we will
#loose any values so we'll make sure to escape them
#(the $'s that is)
			s/\$/\\\$/g;

#localizing $var and $value to make sure
# they are clean out on every read.
		my ($var, $value) = split(/=/,$_);

#					Load variable into enviroment
		$ENV{$var}="$value";

			$i++;
     		}
		
	}
print "Loading custom variables into ENV..\n";
	readDat("custom_sample_variables.txt");
	print "Print custom variables...\n";
	print "VAR1=" . $ENV{'VAR1'} . "\n" ;
	print "VAR2=" . $ENV{'VAR2'}  . "\n";
	print "VAR3=" . $ENV{'VAR3'}  . "\n";

	print "Loading cloud variables into ENV..\n";
	readDat($fileName);

	my $service_name="$ENV{'COMMON_DB_SID'}";
	$ENV{"ORACLE_HOME"}="$ENV{'DB_ORACLE_HOME_LOC'}";
 
       #find sid by parsing pmon process.
        my $pmon_ora = ".*pmon_".$service_name.".*";
        my $process = `ps -eaf |grep $pmon_ora |grep -v grep`;
        chomp($process);
        my $sid = ( split "pmon_", $process )[ -1 ];
        
        $ENV{"ORACLE_SID"}= $sid;

	print "Executing attached SQL ...\n";
	my $CMD = $ENV{"ORACLE_HOME"} . "/bin/sqlplus / as sysdba \@sample.sql $ENV{\"PDB_NAME\"}";
 
	print "Firing SQL ...\n";
	print "$CMD\n";
	system($CMD);
	my $ERROR_CODE = $?;
	print "Error code is $ERROR_CODE \n";
	if ( $ERROR_CODE == 0 )
	{
			print "Script Completed\n";
	}
	else
	{
			print "Error occured while executing \n";
	}

Example 34-6 Sample PDBaaS SQL Script

alter session set container=&1;
REM --- you custom sql goes here ---
select name, con_id from v$pdbs;
exit;

Example 34-7 Sample PDBaaS RAC Script

alter session set container=&1;
REM --- you custom sql goes here ---
select name, con_id from gv$pdbs;
exit;