Database as a Service using Enterprise Manager – Part XXI

In this article series, we have been exploring the capabilities of Oracle Enterprise Manager Cloud Control 13c for the private Database-as-a-Service (DBaaS) cloud – including the setup of such a cloud.  In the previous parts, we started the setup of the private DBaaS cloud, including the configuration of the self-service portal. The full procedure includes setting up the Enterprise Manager software library, creating the PaaS infrastructure zones, database pools, and quotas for the users, service templates, and optionally chargeback plans to apply to the users for cloud allocation and cloud usage.

We configured the software library, set up the Enterprise Manager self-update system, and downloaded the latest plug-ins from the external Enterprise Manager store. We created a custom cloud role in Enterprise Manager, and a Cloud SSA user named as "SAI_DAS". Next, we created a PaaS Infrastructure Zone named as “Sainath_PaaS_Zone”, a group of hosts that will be used for Database as a Service. Members (hosts) were added to the PaaS Infrastructure Zone.

A new database pool was then created – a group of database servers where the database software had been pre-installed, with all servers in the pool belonging to the same platform as well as the same database version. Next, we added our Oracle Home targets to the pool, with each Oracle Home target belonging only to a single database pool. The Placement Policy Constraint “Maximum number of Database Instances per host” was set at 10 instances.

After that, we entered the quotas for the role selected. The Quotas page allows the SSA Administrator to configure the total amount of resources which are allocated to the self-service user via the role. Then we selected Data Sources from the left panel, and on the page that appears, clicked on the Data Profiles tab and created a new profile. In the creation wizard that started, we selected the Reference target as the “saiprod” database. This is the production database that will be used as the master source. We chose to include “Data Content” and “Structure and Data” by creating an RMAN backup from the source database. The profile will be used for the Database-as-a-Service functionality.

We then started to create a new database profile to be used for the snap clone functionality. We clicked on “Create” in the Data Profiles tab and specified the reference target again as the “saiprod” database, and included the “Structure and Data” by including an “RMAN Database Image” for this new profile, instead of an RMAN backup. This profile will be used for the Database-as-a-Service using the snap clone functionality.

The next step was to create service templates for the database cloud, which can then be used by the SSA user to provision databases in the pool. The profiles created in the previous section are like gold copies of an Oracle database, and they are used by service templates. The service template is what is offered to the SSA user and forms the basis of the functionality of the cloud. We created the service templates by selecting Setup | Cloud | Database and then Service Templates in the left pane, followed by clicking on the Create button. We named the service template as “Sainath 11204 Single Instance Database”. Under the Source Identification section, we selected the Create Database as “Using Profile” and then from the list of profiles, we selected the appropriate profile that was created with an RMAN Full backup.

The other options for Create Database, besides “Using Profile”, are “Using existing Database” and “Using Test Master Snapshot”. Test master snapshots are a snapshot of an RMAN Image backup from a production database. Test master snapshots are new in Enterprise Manager 13.1, and can be used to create snap clones. However, these require special storage hardware such as NetApp Storage Appliance, Sun ZFS Storage Appliance, EMC Storage Array, or even the Solaris File System (ZFS). Note that the dNFS file system and CloneDB cannot be used for test master snapshots.

Then, we selected Single instance under the Database Definition. In our case, we specified the SID prefix as “sai” (up to six letters are allowed) and the domain name as “sainath.com”. This can be defined as per company standards, or you may allow the SSA user to specify the prefix at request time. You can also select “Enable Standby Database” for this service template, provided you have already set up the infrastructure for this, and have a standby database pool of servers that you can select at this stage.

Under the section “Pools and Zones”, we clicked on Add and selected the previously created “Sainath_PaaS_Zone”. Then, we clicked on “Assign Pool” and selected the Sainath_Database11204_Pool to be assigned to this zone. The self-service databases will be created in this pool.

In the Placement section, there are two possibilities. The provisioning of the database on the host can be selected by the “Placement Algorithm”, or selected by the user during request. The placement algorithm is controlled by the placement constraints that have been configured in the PaaS Infrastructure Zone we have selected. The two constraints – Maximum CPU Utilization (%), and Maximum Memory Allocation (%), have been set to 95% in our case. 

The placement algorithm is also influenced by the Placement Policy Constraint specified in the database pool; i.e., the Maximum number of Database Instances per host, which has been specified as 10 in our case. The algorithm looks at all these factors and decides which member of the zone should be used for the creation of new services. In our case, there is just one host, but if the algorithm returns false for that host, no new service will be created.

If “selected by the user during request” is used, then the algorithm doesn’t decide on the member, the human user makes that decision. However, the constraints will still apply. If there are multiple hosts in the pool and the human user selects a host that is already over-utilized, the creation of the new service will not work.

Note that Database Cloud Requests can fail for certain reasons, such as placement logic failures. Placement logic uses a seven-day average for memory and CPU constraints when locating a suitable database server within the selected PaaS infrastructure zone where the new database can be created.

The database details step then appears. On the Database details page, select File System for both the Storage Type and Fast Recovery area (FRA). Put the Fast Recovery Size as 6000 MB. Tick “Enable Archiving”.

It is important to configure the FRA and enable archiving, if you want SSA users to schedule database backups and perform restores by themselves. This puts the onus of power right in the hands of the SSA user, and they need to use this power responsibly and wisely for the sake of their own databases.

For the Listener port, enter the port number that should be associated with the new database. If you are provisioning an 11.2 or later RAC database, you must specify the scan port number.

On the same page, enter the passwords you want to use for the administrative users (SYS, SYSTEM and DBSNMP) in a self-service created database. Note that as the SSA administrator, you are currently creating the service catalog entry for the database, and once the SSA user has created a database using your service catalog, you can then use these logins can to manage the database. The SSA user will have no access to these passwords. Since this is a demo system, we select “Use the same password”, otherwise we would use different passwords for a production or test system.

You can also select users (schemas) in the copied database that you do not want to be accessible to the SSA user. If you select such users, you need to specify a password for those users. This password will not be available to the SSA user, thus effectively stopping them from accessing those schemas.

In the “Master Account Privileges”  section, name the Master Account role as “saimaster_role”.  This role will be granted to the master account that is specified by the SSA user when creating the self-service database. There is a list of privileges that will be granted to the role. You can modify this list depending on your company requirements – for example, if you feel that the SSA master account should not have privileges like “Create any directory”, you can remove that privilege. On the other hand, if you want to add more privileges, you can do so. In our case we have added the “select any table” privilege to the role in addition to the existing privileges. This can be done if the requirement is that the master account role should be able to select any data from any table in the database.

Click on Next. The Initialization Parameters page appears. On the Initialization parameters page, simply select the “Database Sizes” that the service template will support. These are the pre-created sizes we had created in an earlier section in this article series. The limits for each size are also displayed. You can choose to include all of the sizes or only some of them in each template.

This gives the SSA user SAI_DAS an option as to which database size to select. Click on “Set as Default” to make one of the database sizes the default, so that it is the first choice for the SSA user when this service template is selected at the time of self-service database creation.

The initialization parameters for the database can also be edited on this page. However, note that the “Database Size” that is selected by the SSA user at request time will override the corresponding initialization parameters such as sga_target, pga_aggregate_target, processes and cpu_count parameters. The Storage GB, as explained before, is not a hard limit on the database but only used for thresholds and alerts.

Click on Next. The Customization page appears. On this page, you can specify the custom scripts to be executed before and after creating the service instance. A Post SQL script can also be specified. In addition, custom scripts to be executed before and after deleting the service instance can be specified. All these are optional, of course.

Target properties for the new self-service database can be specified as well on this page, such as the administrative contact, the downtime contact, the lifecycle status, the line of business and so on. Click on Next. In the Roles page that appears, you can add the role SAINATH_SSA_USER_ROLE to the template. This means the users assigned this role, such as SAI_DAS, will have access to this template in the self-service console, and will be able to create a new database.

Go to the next page, review the service template and create it. The new template now appears in the Database Service Templates list (Setup | Cloud | Database | Service Templates).

Create Service Template: Snap Clone Database

We will now create a new service template to provision a snap clone database. This will use CloneDB technology on a dNFS copy-on-write file system.

On the Database Service Templates page, click on Create.

Figure 28: Service Template for Snap Clone Database

            The first page of the wizard is seen in Figure 28. Name the new template as “Sainath Snap Clone 11204 Database using CloneDB”. From the list of profiles, select the appropriate profile “Database saiprod.sainath.com RMAN Datafile Image Backup Profile” that was created with an RMAN Database Image. As the Profile Version, choose “Selected by user at request time”. This will allow the SSA user to select any of the available RMAN backups that have been created by refreshing the profile. In our case, there is only one datafile image backup so as to conserve resources.

Due to the fact that a “Datafile Image Backup” has been created as the profile, an option appears dynamically on the page to create a CLONEDB database. Select “CLONEDB database” and not “Full Copy of the Database”. Put the SID prefix as “clone” (you can use any SID prefix as per company standards) and the domain name as “sainath.com”. Add the Sainath_PaaS_Zone and assign to the Sainath_Database11204_Pool.

The other options on this page are similar to what we have seen before. Click on Next. The Database Details page appears (Figure 29).

Figure 29: Database Details for Snap Clone Service Template

On this page, a note is displayed under the Database Storage Area Details: “Database files location needs to be a NFS location”. So we select an NFS location “/u02/copy-on-write” as the database storage area; this location has been set up previously as an NFS share at the Unix level on this database server – the instructions on how to do this are in the My Oracle Support (MOS) Document “Configure Direct NFS Client (DNFS) on Linux (11g)” (Doc ID 762374.1).

Enable the Fast Recovery area. You need to select a file system location such as “/u02/fast_recovery_area” for the fast recovery area. ASM cannot be selected in this case due to snap clone requirements for CloneDB. Select the Fast Recovery Size as 6000 MB. Also enable Archiving for the new database.

Allow the SSA user to take up to 7 (maximum 10) backups for the new database. As the backup location, specify “/u02/fast_recovery_area/clonebackups”.  Note – the backup does not use copy on write technology, so it is not a space saver if multiple backups are performed by the SSA user. So in this case you do not need to use an NFS location for the backup location in the service template.

For the Listener port, enter the port number that should be associated with the new database, or the scan listener port number if you are using 11.2 RAC and above.

Name the Master Account role as “saimaster_role” and add the “select any table” privilege to the role in addition to the existing privileges shown. This can be done if the requirement is that the master account role should be able to select any data from any table in the database.

On the next page, which is the Initialization parameters page, select the “Database Sizes” that the template will support. This gives the SSA user SAI_DAS an option as to which database size to select. Set the Small Database size as the Default. Any such “Database size” selected by the SSA user will override the corresponding initialization parameters such as sga_target, pga_aggregate_target, processes and cpu_count parameters. The Storage GB, as we mentioned before, is not a hard limit on the database but only used for thresholds and alerts.

            Move to the next page, there are no custom scripts to add. Click on Next. On the Roles page, add the role SAINATH_SSA_USER_ROLE to the template. After this, review the service template and click on “Create” on the review screen to save the template. The new template now appears in the list of service templates (Figure 30).

Figure 30: List of Service Templates

Request Settings

Next, Click on Request Settings in the left panel. Figure 31 is displayed.

Figure 31: Global Request Settings for Database

On the Request Settings page, the SSA administrator can choose the “Global Settings for Database”, that will be enforced for all the SSA user requests for new databases.

“Future Reservations” – how far in advance a user can make a request – can be restricted to a certain number of days in advance, or there can be no restrictions at all. Likewise, the requests can be set to have a restricted “Request Duration” in days, or no restrictions. You can also set the archive retention to the maximum duration after which archived requests will be automatically deleted by the system.

Enterprise Manager Cloud Control 13c has introduced new request settings on this page. For the first time in Enterprise Manager, there is a “Service Instance Approval” setting. If this is set, then approval is required from the SSA administrator for ALL create service requests requested by the SSA user on the database side.

You can also set the “Service Instance Share Settings”, whereby you either allow an SSA user to share service instances with other users, or alternatively share the service instances with roles. This way, if one SSA user creates a database instance via self-service, then either other specified SSA users or all SSA users assigned a specified role will be able to see the new database instance in their own self-service console. They will be able to share the monitoring and management. For example, a development team can use this kind of setup.        

Move to the Role Settings tab. This is seen in Figure 32.

Figure 32: Request Settings: Role Settings

            In this tab, you can specify different database request-related role settings for different roles. Simply click on “Create”, select the appropriate role, and change the settings for that role. Here we see that the SAINATH_SSA_USER_ROLE has been added, and “Request Approval” has been enabled for this role. This means that whenever the SSA user SAI_DAS requests a database, the SSA administrator will need to approve the request. Once approved, it will be automatically actioned.

So perhaps you could have a special role for certain requestors who want large production databases, so that special approval is required before the request is actioned. It would have been better of course, if the request approval requirement could have been set at the individual service request level. In this way, you could specify that certain service requests require approval, such as large production databases, or every production database. But at this point i time, it is not possible to set approval requirements at the service request level.

This concludes the article series. We hope you have enjoyed learning about Oracle Enterprise Manager and Database as a Service. Thanks for your readership.