Monday Jun 10, 2013

Schema-as-a-Service implementation using Oracle Enterprise Manager 12c

Schema-as-a-Service capability was released few months back as part of Oracle Enterprise Manager 12c Release 2 Plug-in Update 1 and is one of the widely talked about deployment models in a database cloud environment enabling deepest level of consolidation density possible and in turn driving maximum ROI. Schema consolidation also requires careful planning in terms of identifying the right mix of applications and business case and also meeting up with isolation requirements at operational, security and workload management levels. The following video presents Schema as a Service and other features supported by EM 12c Database as a Service.

In this post, I will go through the steps required to setup a Schema cloud service using Oracle Enterprise Manager 12c Release 2.This note is chalked down based on a recent customer experience and can be referred to by Administrators who are planning to or in process of setting up Schema-as-a-Service using Oracle Enterprise Manager 12c.

  • Project Planning - Define scope of implementation, engage end users/cloud consumers in the planning process and get the project plan approved from all stakeholders.
  • Setup Cloud Management infrastructure
    • Review the EM 12c Sizing paper before you get started with EM 12c Release 2 install
    • Refer to EM 12c Administrator’s guide for High Availability, Security, Network/Firewall best practices and options
    • Deploy EM 12c site using EM 12c Release 2 Installation guide
    • Oracle Cloud Application, Database, Chargeback and Capacity planning, Storage Management Framework (SMF) and Virtualization (VT) plug-ins are required for enabling overall Database-as-a-Service capabilities
    • Install latest EM PSU [MOS note 822485.1] and also recommended patches for Database-as-a-Service [MOS Note 1549855.1]
    • Setup Roles and Users - Cloud Administrator (EM_CLOUD_ADMINISTRATOR), Self Service Administrator (EM_SSA_ADMINISTRATOR), Self Service User (EM_SSA_USER) are the important roles required for cloud lifecycle management and different users can be created and mapped to their respective roles as per their functional responsibility. Roles and users are managed by Super Administrator via Setup –> Security option. For Self Service/SSA users custom role(s) based on EM_SSA_USER should be created and EM_USER, PUBLIC roles should be revoked during SSA user account creation.
    • Setup Software Library - Cloud Administrator configures Software library via Enterprise –> Provisioning and Patching option and the storage location provided is OMS shared filesystem. Software Library is the centralized repository that stores all software entities
    • Setup Self Update – Self update can be configured via Setup -> Extensibility option by Super Administrator and is the unified delivery mechanism to get all new and updated entities (Agent software, plug ins, connectors, gold images, provisioning bundles etc) in EM 12c.
  • Setup Schema Cloud
    • Identify candidate servers to be pooled together into database cloud and deploy agents on those servers
    • Next step is to pool these server resources into a 'PaaS Infrastructure Zone'. As Cloud Administrator user, Create PaaS Infrastructure Zone via Enterprise -> Cloud -> Middleware and Database Cloud option
    • Deploy Oracle Database and Configure listener - As SSA Administrator user, setup target database(s) for Schema pool using Provision Oracle Database deployment procedure Refer to EM 12c database provisioning section in EM 12c Lifecycle Management Administrator's guide. Ensure listener is configured and discovered in EM for all the servers part of the Schema Cloud.
    • Setup Database Pool for Schema Cloud - As SSA Administrator user, create database pool via Setup -> Cloud -> Database option. you can specify max workload allocated to each database and also can enable Resource manager at this stage
  • Setup Service Catalog
    There are two distinct use cases here. you can either have SSA user get a) reference schema(s) imported or b) empty schema(s) provisioned . In case of empty schema(s) use case you can directly create service template and skip first step of configuring profile
    • Configure Profile - Profile captures required information from reference database including metadata and actual schema(s) content. As SSA Administrator, create profile via Setup -> Cloud -> Database and select 'Profiles and Service Templates' under Database cloud SSA portal and use 'Export Schema Objects' option.
    • Create Service template - Service template defines the standardization and actual flow that will be run when SSA user requests for Schema(s) from service catalog. As SSA Administrator, create a new service template via 'Profiles and Service Templates' option. you can select the related profile using which schema should be auto-imported when SSA user requests for schema. Important Tip - As part of Service template creation flow, there is this option of including standard custom scripts that can run part of service request flow before or after self-service schema(s) are provisioned.
  • Setup governance rules and policies
    • Configure Request Settings - As SSA Administrator, configure request settings via Setup -> Cloud -> Database and select Request settings tab under Database cloud SSA portal. you can define maximum duration of requests, how far in advance requests can be made etc and other request associated settings
    • Configure Quotas - As SSA Administrator, configure quotas via Quotas tab in Database cloud SSA portal and define total amount of memory, storage, no of Schemas that can be granted to each user. Quotas are assigned at role level and only the roles that has EM_SSA_USER privilege assigned can be selected.
    • Configure Chargeback/Showback - Chargeback is all about accountability and here you can track and meter resource usage and related costs in shared resource model and provide IT and business some powerful reports related to resource utilization and consumption costs. As SSA Administrator, Refer to Setting up Chargeback flow for defining charge plans and cost centers, chargeback settings. Important tip - Cost Center name is set by default as SSA user name for any new schema service provisioned by SSA users. Cost center can be changed via the Setup -> Cloud -> Database ->  Chargeback tab,  select 'Configure targets' and here the schema service target can be assigning to a different cost center.
  • Rollout Self-Service Portal to SSA Users
    •  Requesting Schema service - SSA Users at this stage can login to the Database cloud SSA portal and select the service template to request for schema service. During schema service request you can provide schema prefix to be added, workload size of service, schema passwords etc
    • Access Schema service - SSA users can access provisioned schema service from SSA portal and they can use the connection details from summary. There are performance and usage graphs provided for schema service monitoring by SSA users. There is also option to export/import data for all Schemas part of this Schema service.
  • Monitor and Manage Schema Cloud
    • SSA Administrator can monitor the schema service requests via request dashboard via Enterprise -> Cloud -> Middleware and Database request dashboard.
    • Refer to Private Cloud Administrator's guide for additional details to monitor the infrastructure at service, pool and zone levels. Important tip - Services Instances UI provides lot of useful detail in a single screen as you get to see all types of services along with their pool, zone association and also with related service template details.

More information -

Stay Connected:

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

Friday Mar 08, 2013

Schema as a Service for Extreme Consolidation

As we deal with Database as a Service use cases, we often find that consumers do not need dedicated databases of their own. Developers of a home-grown application, for example, might be satisfied with a logical slice of the database. This logical slice, leads us to the concept of Schema as a Service—a new capability offered in the latest release of Oracle Enterprise Manager 12c Release 2 Plug-in Update 1.

Schema as a service is the ultimate and extreme in consolidating multiple schemas in a shared database model. Cloud users can request one or more schemas, with or without seed data, from Oracle Enterprise Manager 12c’s out-of-the-box self service portal. It offers excellent manageability, not only for its fast efficient provisioning, but because administrators only need to manage a small number of databases.

Schema as a Service: Consolidate Multiple Schemas in a Shared Database Cloud Services Model

However, consolidation comes at the expense of isolation, because the operating system and database are not isolated among the database consumers. While enabling Schema as a Service, it’s important to isolate the workloads as much as possible to make sure that one user doesn't run away with all the database resources. Administrators can guarantee this does not happen by using Oracle Enterprise Manager 12c’s CPU monitoring capabilities built in to Oracle Database Resource Manager to maintain service levels.

For security, the more consolidated you get, the more concerns administrators have about data isolation and security. Using Oracle Data Vault can help resolve these issues. It is integrated with Oracle Enterprise Manager 12c, and administrators can use Oracle Data Vault to enable fine grain control based on roles and privileges within the database cloud service.

For reporting purposes, metering and chargeback capabilities can be implemented to help IT organizations gain in-depth visibility into resource consumption and expenses incurred with each schema as a service deployment. This is useful for regulatory compliance requirements as well.

Schema as a Service at a Glance:

  • Consolidate multiple application schemas in a shared database deployment model
  • Each application user (i.e. developers or testers) can provision one or more database schema(s) with a dedicated database cloud service
  • Automated placement can be based on workload characteristics and specifications
  • Service levels are guaranteed through Oracle Database Resource Manager
  • Service governance is done through quotas, retirement policies and chargeback plans
  • Integrated with Oracle Data Vault for security isolation and control
  • De-provision schemas when needs change


  • Save resources through ultimate consolidation of multiple database applications
  • Boost administrator productivity and increase efficiency with automated provisioning
  • Deploy schema as a service implementations consistently using self-service profiles and templates
  • Metering and chargeback helps keep track of resource consumption and usage for accountability and reporting
  • Minimize administrative overhead and compliance challenges by preventing database sprawl

How To:
There are several steps involved when setting up and deploying database schema as a service in Oracle Enterprise Manager’s self service portal. Here is a quick summary of what’s involved. For more details be sure to review the resources below.

1. Setting up Platform as a Service Zones

  • Before deploying your schema as a service, you first need to create a Platform as a Service (PaaS) infrastructure using Oracle Enterprise Manager 12c’s self-service portal. A PaaS Zone comprises multiple hosts, i.e. servers with Oracle Enterprise Manager 12c agent installed.
  • Use the portal to create a PaaS zone and organize it by function type (i.e. based on geography, line of business (sales, development) or application lifecycle. (i.e. dev, test, QA, production)
  • Next expose the PaaS zone to the self-service cloud users in the portal. For example, developers can now have the option to select a development PaaS zone or testers can select a QA zone.
  • Visibility of each zone can be restricted based on the self-service user's credentials.

2. Setting up Database Pools

  • Database pools are a collects of databases used to host schema as a service.
  • To create a new database pool, you can use a portion of resources that are available to the zone. Keep in mind that all members of the database pool need to be the same target type. For example, a single database instance or database cluster; platform, or same database version. This ensures provisioning consistency during deployment.
  • Next configure placement constraints and policies for the database pool. For placing databases within the pool and controlling how resources are utilization, you need to first create a placement constraint and set its policies. This provides protection for the database members within the pool for resource consumption. For example, a production database pool might enforce more conservative constraints whereas a development pool might allow liberal limits.
  • You can set a constraint for each database in the pool by services or by workload associated with the service request based on CPU and memory. You can also enable Oracle Database Resource Manager for the database pool to control your CPU usage and the underlying service levels.

3. Request Settings

  • During this part of the schema as a service set up, future reservations, archive retention and duration of request can all be enabled.

4. Quotas

  • Controlling quotas and setting limits for users based on role level can be assigned in this step of the process. Oracle Enterprise Manager supports quota based on CPU, memory and number of database services.

5. Profiles and Service Templates

  • A service template is standardized definition that is offered to self-service users to create a database or schemas within the deployment. A service template defines the workload characteristics and schema details that can be generated with or without seed data.
  • To create a service template with seed data, you need to create a profile. A profile is an entity that captures source database information for provisioning purposes. Once you create your service template it becomes part of a collection which makes up the service catalog. This catalog is then exposed to cloud users in the self-service portal.
  • Next, you can either export the seed data from the source database or export the schema definitions without the data. Once you decide, a Data Pump Export job will be created.
  • You can now map your newly created profile and service templates to the required zone(s) and database pools.

6. Chargeback

  • The final step in deploying schema as a service is to configure resource metering and chargeback.
  • Setting up metering and chargeback can easily be done in order to track resource usage within the schema as a service implementation.
  • For more information on how to set up chargeback we recommend reading this white paper.


Product Info:
  • Oracle Cloud Management
  • Zero to Cloud Resource Center
  • Demos:
  • Oracle Cloud Management
  • Setting up Database Clouds for Schema as a Service
  • Whitepapers:
  • Delivering Database as a Service using Oracle Enterprise Manager 12c
  • Best Practices for Database Consolidation in Private Clouds
  • Oracle Enterprise Manager 12c: Metering and Chargeback
  • Cloud Management for Oracle Database

  • Stay Connected:
    Twitter |
    Facebook | YouTube | Linkedin | Newsletter
    Download the Oracle Enterprise Manager Cloud Control12c Mobile app

    Latest information on Oracle Enterprise Manager and Oracle Management Cloud.

    Related Blogs


    « July 2016