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 30-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 30-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.
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:
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 30-3 Sample 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 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
This table lists the input parameters for the DBaaS pre and post request creation scripts.
Table 30-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 |
This table lists the input parameters for DBaaS pre and post request deletion scripts.
Table 30-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 |
This table lists the input parameters for schema as a service pre and post request creation scripts.
Table 30-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 |
This table lists the input parameters for schema as a service pre and post request deletion scripts.
Table 30-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 |
This table lists the input parameters for PDB as a service pre and post request creation scripts.
Table 30-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 |
This table lists the input parameters for PDB as a service for pre and post request deletion scripts.
Table 30-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 |
This section contains a few sample input files for DBaaS, Schema as Service, and PDB as a Service.
Example 30-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 30-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 30-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 30-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)))
This section includes sample perl and sql scripts for PDBaaS.
Example 30-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 30-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 30-7 Sample PDBaaS RAC Script
alter session set container=&1; REM --- you custom sql goes here --- select name, con_id from gv$pdbs; exit;