Harness the power of Configuration Search to know what's out there and drive automation

By DaveWolf

Oracle Enterprise Manager collects and monitors configuration information for every target it manages and layers support for sophisticated lifecycle operations on top of this foundation. Within the configuration management area itself Enterprise Manager has formalized support for Drift Management, Inventory Management, Topology visualization and Compliance Management. Although these features cover a good segment of configuration management use cases there will always be additional uses for this important information. Enterprise Manager makes it easy to leverage this valuable information both inside and outside the product using the Configuration Search feature.

Within the Enterprise Manager UI, users can build sophisticated queries against Enterprise Manager’s configuration management repository to generate reports without writing even one line of SQL. If the Oracle provided Search library does not already contain a matching search a user can build a search to their exact specification completely graphically.

Outside of the Enterprise Manager UI, users can find and run saved searches using the EMCLI in both interactive and the new script mode. When used in a script, configuration search results can be used to drive other lifecycle operations like patch automation and provisioning.

In this article we will take a closer look at the Configuration Search feature using some common real world examples.

Arguably one of the most important configuration items collected by Enterprise Manager is applied patches. Finding the location of applied patches can cause some confusion at first owing to the new target model introduced in Enterprise Manager 12c. Oracle Home is now a separate and proper target with its own configuration collection which includes patch information. This makes great sense as patches are in fact applied to the Oracle Home and not the software running out of it.

The question is how can you figure out which targets ( ie databases ) are using which Oracle Homes? The answer is using relationships. Enterprise Manager 12c now discovers and collects relationships between targets. These relationships include both physical (observed) and logical (inferred from configuration). As an example, all databases running out of a given Oracle Home will have an “Installed At” relationship to its specific Oracle Home target. These relationships can be graphically viewed using the topology viewer available under the configuration menu of all targets. They can also be used when building a Configuration Search when starting with a well known target like database instance.

Find all single instance databases with Advanced Compression option that do not have a patch applied. – Step by Step

Let’s build a configuration search to find all single instance databases with Advanced Compression option that do NOT have a patch applied to their Oracle Home. Since patches are typically specific to a version let’s narrow it down to version 11.2.0.3 databases and patch 14275605. ( Database Patch Set Update : 11.2.0.3.4 )

1. Start by navigating to the Configuration Search Library. Enterprise->Configuration –>Search…

2. Click Create to start building a new Configuration Search.

3. Select Database Instance from the Target Type list of value.

Next we need to narrow the list of databases to those of version 11.2.0.3 and single instance. To do this we will use the target model to choose properties which contain this data so we can filter it further.

4. Click Properties on the Database Instance row.

5. Open the Target Properties and Instance Information folders and Select Property Name, Property Value,Version, Name and Selected as shown.

6. Click OK.

To filter down the results, we enter criteria into the text boxes to the right of the properties.

7. Enter 11.2.0.3 next to Version.

8. Enter ‘Advanced Compression’ for Name and ‘TRUE’ for Selected under Database Options

9. Select ‘Metric Scope’ for property Name and Enter ‘DB’ for value. ( Metric scope can have a value of DB for single instance and RACINST for RAC instances. )

Your search should look something like this:

At any point while you are creating a Configuration Search, you can see how your search is coming along by clicking Search. Doing so at this point will show results similar to the results shown here. ( Note: If you are not interested in seeing the results of a column you can uncheck the property to remove it from the results. )

At this point we need to pull the Oracle Home target into the picture to get at the applied patches configuration information.

10. Click Relationships on the Database Instance row.

11. Choose “Oracle Home” as the Destination Target Type then Click Search. This should result in one relationship type “Installed At”. Select this row and click OK.

We now have something that looks like this:

To add collected patch information from the Oracle Home target we need to use the target model again.

12. Click Properties on the Oracle Home row.

13. Open the “Patches installed in Oracle Home” folder and select “Patch ID” property.

14. Click OK.

15. Enter 14275605 in the text box next to “Patch ID” to narrow the results to this patch.

16. Click Search. You should see something similar to the results below.

But wait, this shows the databases that HAVE patch 14275605 installed. We are after databases that DON’T have this patch installed.

Fortunately we can achieve this result by using the “Advanced Options” capabilities.

17. Click the “Advanced Options” button on the “Patches installed in Oracle Home” row. ( Be sure to select the correct one! )

18. Change the Condition in the resulting dialog box to “NOT EXISTS”. ( The explanatory text shown just happens to use patch search as an example. )

19. Click OK.

Notice the addition of “Condition : NOT EXISTS” on the “Patches installed in Oracle Home” row. This will show targets in which none of the targets matches the criteria. In our case, an Oracle Home may have hundreds of patches applied. Only if none of the patch IDs equal 14275605 will the target be in the results.

20. Click Search.

This time, the results finally display what we are after. That is “11.2.0.3 Single Instance database with Advanced Compression option that do NOT have patch 14275605 applied.”

21. Click ‘Save As’ to save the search with the name “11.2.0.3 SI AC DBs without patch 14275605”.

22. Click OK.

The library now shows our new search. You or any other user can run the search by selecting it and clicking Run. You can modify it by using Edit or make a copy with Create Like to continue to refine it without affecting the original.

Running Configuration Search using Interactive EMCLI

As mentioned at the opening, Enterprise Manager Release 3 now supports the execution of saved Configuration Search from the EMCLI. There are two verbs with which you can run configuration searches: get_targets and run_configuration_search

The get_targets verb has been available since Release 1 but now has an additional switch to specify a configuration search. This results in a standardized result containing the Target Name, Target Type and Status.

Here is an example using the configuration search we just built.

The run_config_search verb generates results exactly as you see them in the results of the configuration search. The results are a little harder to read but the output could be re-directed to an output file for import into something like a document editor or spreadsheet for easier viewing or analysis.

Scripting Lifecycle processes using EMCLI Script mode

Enterprise Manager Release 3 introduced the EMCLI Script mode which is especially effective when performing tasks in bulk or many tasks at once. This mode enables you to create Jython scripts, store them as files and pass them as an argument to EMCLI. For more information on EMCLI see the documentation here.

In this section, we will expand on our previous work to automate the creation of patch plans to automate the application of the missing patch. We will use a python script to retrieve the list of databases without a patch, and then create a patch plan for each database.

As a prerequisite you must create a sample patch plan for a single instance database which has the desired patch ( 14275605 ) added to the plan. We will use this plan to create the others.

The work flow is as follows:

1. Retrieve specified patch plan metadata and extract required patch information.
2. Get list of databases without the patch applied using a configuration search.
3. Create a patch plan for each database.

Here is the script in its entirety. Obviously you will need to make modifications for you environment, specific patch plan and configuration search names.

---

 #emcli_config_search.py

from emcli import *
import xml.dom.minidom


# Set Connection properties and logon
set_client_property('EMCLI_OMS_URL','https://oem.example.com/em')
set_client_property('EMCLI_TRUSTALL','true')
login(username='DWWOLF1',password='password')

patch_id = []
release_id = []
platform_id = []
language_id = []
target_type = []

# Get Sample Patch Metadata
pp_xml = show_patch_plan(name='PSU4 Rollout').out()

# Parse plan metadata into XML
patchPlan = xml.dom.minidom.parseString(pp_xml)

# Retrieve metadata for each patch in the sample patch plan
for patchList in patchPlan.getElementsByTagName("patchList"):
        for patch in patchList.getElementsByTagName("patch"):
                patch_id.append(patch.getElementsByTagName("id")[0].toxml().replace("","").replace("",""))
                release_id.append(patch.getElementsByTagName("release_id")[0].toxml().replace("","").replace("",""))
                platform_id.append(patch.getElementsByTagName("platform_id")[0].toxml().replace("","").replace("",""))
                language_id.append(patch.getElementsByTagName("language_id")[0].toxml().replace("","").replace("",""))
                target_type.append(patch.getElementsByTagName("target_type")[0].toxml().replace("","").replace("",""))

# Run stored configuration search to get list of databases missing the patch
target_array = get_targets(config_search='11.2.0.3 SI AC DBs without patch 14275605').out()['data']

# For each target create a patch plan containing the patches in the sample patch plan
for targets in target_array:
    tn = targets['Target Name']
    nodeCount = 0
    f = open('patchplan.txt', mode='w')
        for node in patch_id:
                f.write( "patch." + str(nodeCount) + ".patch_id=" + patch_id[nodeCount] + "\n")
                f.write( "patch." + str(nodeCount) + ".release_id=" + release_id[nodeCount] + "\n")
                f.write( "patch." + str(nodeCount) + ".platform_id=" + platform_id[nodeCount] + "\n")
                f.write( "patch." + str(nodeCount) + ".language_id=" + language_id[nodeCount] + "\n")
                f.write( "patch." + str(nodeCount) + ".target_name=" + tn + "\n")
                f.write( "patch." + str(nodeCount) + ".target_type=" + target_type[nodeCount] + "\n")
                nodeCount += 1
        f.close()
        planName = 'PSU4 ' + tn
        create_patch_plan(name=planName,input_file='data:patchplan.txt',impact_other_targets='add_all')

exit()

A zip of the script is available for download here.

Run the script by passing it as an argument to EMCLI:

>emcli @emcli_config_search.py

Here we can see all of the patch plans created by the script plus the sample patch plan “PSU4 Rollout”

Conclusion

Enterprise Manager’s Configuration Search feature is a powerful tool that can be leveraged both inside and outside of the UI. It can quickly and easily provide answers to difficult configuration questions without writing any SQL. When used via through the EMCLI it can be used to dynamically generate a target list which can be used to drive complex and otherwise time consuming tasks in the UI quickly and efficiently.

Configuration Search and patch automation are both features of Enterprise Manager’s Database lifecycle management pack.

For more information on Enterprise Manager’s database lifecycle management capabilities, visit http://www.oracle.com/technetwork/oem/lifecycle-mgmt/index.html

By Dave Wolf

What's New in Database Lifecycle Management in Enterprise Manager 12c Release 3

Article by HariSrinivasan

Enterprise Manager 12c Release 3 includes improvements and enhancements across every area of the product. This blog provides an overview of the new and enhanced features in the Database Lifecycle Management area. I will deep dive into specific features more in depth in subsequent posts.

"What's New?"  In this release, we focused on four things:

1. Lifecycle Management Support for new Database12c, especially around the pluggable databases (aka multitenant option)

2. Management of long running processes, such as a security patch cycle (Change Activity Planner)

3. Management of large number of systems by

· Leveraging new framework capabilities for lifecycle operations, such as the new advanced ‘emcli’ script option

· Refining features such as configuration search and compliance

4. Minor improvements and quality fixes to existing features

· Rollback support for Single instance databases

· Improved "OFFLINE" Patching experience

· Faster collection of ORACLE_HOME configurations

Lifecycle Management Support for new Database 12c - Pluggable Databases

Database 12c introduces Pluggable Databases (PDBs), the brand new addition to help you achieve your consolidation goals. Pluggable databases offer unprecedented consolidation at database level and native lifecycle verbs for creating, plugging and unplugging the databases on a container database (CDB).

Enterprise Manager can supplement the capabilities of pluggable databases by offering workflows for migrating, provisioning and cloning them using the software library and the deployment procedures. For example, Enterprise Manager can migrate an existing database to a PDB or clone a PDB by storing a versioned copy in the software library. One can also manage the planned downtime related to patching by  migrating the PDBs to a new CDB.

While pluggable databases offer these exciting features, it can also pose configuration management and compliance challenges if not managed properly. Enterprise Manager features like inventory management, topology associations and configuration search can mitigate the sprawl of PDBs and also lock them to predefined golden standards using configuration comparison and compliance rules.

Learn More ...

Management of Long Running datacenter processes - Change Activity Planner (CAP)

Currently, customers resort to cumbersome methods to create, execute, track and monitor change activities within their data center. Some customers use traditional tools such as spreadsheets, project planners and in-house custom built solutions. Customers often have weekly sync up meetings across stake holders to collect status and updates. Some of the change activities, for example the quarterly patch set update (PSU) patch rollouts are not single tasks but processes with multiple tasks. Some of those tasks are performed within Enterprise Manager Cloud Control (for example Patch) and some are performed outside of Enterprise Manager Cloud Control. These tasks often run for a longer period of time and involve multiple people or teams.

Enterprise Manger Cloud Control supports core data center operations such as configuration management, compliance management, and automation. Enterprise Manager Cloud Control release 12.1.0.3 leverages these capabilities and introduces the Change Activity Planner (CAP).

CAP provides the ability to plan, execute, and track change activities in real time. It covers the typical datacenter activities that are spread over a long period of time, across multiple people and multiple targets (even target types).

Here are some examples of Change Activity Process in a datacenter:

· Patching large environments (PSU/CPU Patching cycles)

· Upgrading large number of database environments

· Rolling out Compliance Rules

· Database Consolidation to Exadata environments

CAP provides user flows for Compliance Officers/Managers (incl. lead administrators) and Operators (DBAs and admins).

Managers can create change activity plans for various projects, allocate resources, targets, and groups affected. Upon activation of the plan, tasks are created and automatically assigned to individual administrators based on target ownership. Administrators (DBAs) can identify their tasks and understand the context, schedules, and priorities. They can complete tasks using Enterprise Manager Cloud Control automation features such as patch plans (or in some cases outside Enterprise Manager). Upon completion, compliance is evaluated for validations and updates the status of the tasks and the plans.
Learn More about CAP ... 

Improved Configuration & Compliance Management of a large number of systems

Improved Configuration Comparison: 

Get to the configuration comparison results faster for simple ad-hoc comparisons. When performing a 1 to 1 comparison, Enterprise Manager will perform the comparison immediately and take the user directly to the results without having to wait for a job to be submitted and executed.

Flattened system comparisons reduce comparison setup time and reduce complexity. In addition to the previously existing topological comparison, users now have an option to compare using a “flattened” methodology. Flattening means to remove duplicate target instances within the systems and remove the hierarchy of member targets. The result are much easier to spot differences particularly for specific use cases like comparing patch levels between complex systems like RAC and Fusion Apps.

Improved Configuration Search & Advanced EMCLI Script option for Mass Automation

Enterprise manager 12c introduces a new framework level capability to be able to script and stitch together multiple tasks using EMCLI. This powerful capability can be leveraged for lifecycle operations, especially when executing a task over a large number of targets. Specific usages of this include, retrieving a qualified list of targets using Configuration Search and then using the resultset for automation. Another example would be executing a patching operation and then re-executing on targets where it may have failed. This is complemented by other enhancements, such as a better usability for designing reusable configuration searches.

IN EM 12c Rel 3, a simplified UI makes building adhoc searches even easier. Searching for missing patches is a common use of configuration search. This required the use of the advanced options which are now clearly defined and easy to use.

Perform “Configuration Search” using the EMCLI. Users can find and execute Configuration Searches from the EMCLI which can be extremely useful for building sophisticated automation scripts.

For an example, Run the Search named “Oracle Databases on Exadata” which finds all Database targets running on top of Exadata. Further filter the results by refining by options like name, host, etc..

emcli get_targets -config_search="Databases on Exadata" –target_name="exa%“

Use this in powerful mass automation operations using the new emcli script option. For example, to solve the use case of – Finding all DBs running on Exadata and housing E-Biz and Patch them. Create a Python script with emcli functions and invoke it in the new EMCLI script option shell.

Invoke the script in the new EMCLI with script option directly:

$/emcli @myPSU_Patch.py
Richer compliance content: 

Now over 50 Oracle Provided Compliance Standards including new standards for Pluggable Database, Fusion Applications, Oracle Identity Manager, Oracle VM and Internet Directory.

9 Oracle provided Real Time Monitoring Standards containing over 900 Compliance Rules across 500 Facets. These new Real time Compliance Standards covers both Exadata Compute nodes and Linux servers. The result is increased Oracle software coverage and faster time to compliance monitoring on Exadata.

Enhancements to Patch Management:

Overhauled "OFFLINE" Patching experience: Simplified Patch uploads UI to improve the offline experience of patching. There is now a single step process to get the patches into software library.

Customers often maintain local repositories of patches, sometimes called software depots, where they host the patches downloaded from My Oracle Support. In the past, you had to move these patches to your desktop then upload them to the Enterprise Manager's Software library through the Enterprise Manager Cloud Control user interface.

You can now use the following EMCLI command to upload multiple patches directly from a remote location within the data center:

$emcli upload_patches -location -from_host

The upload process filters all of the new patches, automatically selects the relevant metadata files from the location, and uploads the patches to software library.

Other Improvements: 

Patch rollback for single instance databases, new option in the Patch Plan to rollback the patches added to the patch plans. Upon execution, the procedure would rollback the patch and the SQL applied to the single instance Databases.

Improved and faster configuration collection of Oracle Home targets can enable more reliable automation at higher level functions like Provisioning, Patching or Database as a Service.

Just to recap, here is a list of database lifecycle management features: 

* Red highlights mark – New or Enhanced in the Release 3.

• Discovery, inventory tracking and reporting

• Database provisioning including

o Migration to Pluggable databases

o Plugging and unplugging of pluggable databases

o Gold image based cloning

o Scaling of RAC nodes

•Schema and data change management

•End-to-end patch management in online and offline modes, including

o Patch advisories in online (connected with My Oracle Support) and offline mode

o Patch pre-deployment analysis, deployment and rollback (currently only for single instance databases)

o Reporting

• Upgrade planning and execution of the upgrade process

• Configuration management including

• Compliance management with out-of-box content

• Change Activity Planner for planning, designing and tracking long running processes

For more information on Enterprise Manager’s database lifecycle management capabilities, visit http://www.oracle.com/technetwork/oem/lifecycle-mgmt/index.html

Article by HariSrinivasan

Enterprise Manager 12c Release 3 (PS2) is available

I noticed that Enterprise Manager 12c Release 3 (PS2) is now available to download from the internet:

http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html

You can install stand-alone or upgrade an existing installation. Upgrade instructions are published on the internet on:
http://docs.oracle.com/cd/E24628_01/upgrade.121/e22625/overview_upg_approaches.htm#CIHCDHHA

New Features in Release 3 are published on the internet on: http://docs.oracle.com/cd/E24628_01/doc.121/e25353/whats_new.htm#CEGEHEEH

Regards,
Porus.

Planning Database as a Service Implementation Project

By Nilesh Agrawal

People, process and planning are the three key elements to success in a private cloud journey. Some common questions i hear from field/customers often relates to tasks involved in setting up Database-as-a-Service(DBaaS) using Oracle Enterprise Manager 12c from scratch and how these tasks are mapped to current IT roles and responsibilities - overall list of activities that needs to be performed to setup and achieve DBaaS and who does what during the implementation process.

In terms of roles, responsibilities and users, you will map to these categories

• Project Manager is responsible for working with all stakeholders, project team and coordinating in planning and delivering DBaaS implementation project
• EM Admin sets ups Cloud management infrastructure and is responsible for overall EM 12c installation/configuration and Administration
• Cloud Admins are responsible for managing the Cloud infrastructure(Servers, Network, Storage) and related Administration
• SSA Admins or Cloud DBAs are responsible for Self Service portal setup, managing quotas, policies, target DBs S/W provisioning/ongoing Administration
• SSA Users or End users of Cloud who actually requests for DB instances to be rapidly provisioned on-demand from Self Service portal

Here is a sample Work-breakdown structure(WBS) listing down tasks involved and with individual owners mapped. Note - we have all type of use cases covered in the plan (DBCA, RMAN, Schema as a Service, Snap Clone) and you can pick up any or mix or all of these tasks in your plan depending on your use cases and requirements for implementing DBaaS -

Activity	Description	Owner
A. Initiation and Startup
Determine scope of implementation	Determine and document scope of DBaaS Cloud implementation together with customer and end users. Determine requirements and goals. Introduction meeting implementation team.	Project Manager
Coordination and quality management	Coordination and quality management on throughout all phases of the implementation	Project Manager
Define High availability Needs	Determine the level of High availability required	Project Manager
Technical intake	Technical intake meeting; Infrastructure requirements (Hardware, OS, network/firewall, s/w requirement) for cloud setup	Project Manager
Functional intake	Functional intake meeting; Monitoring /Reporting/Alerting requirement. User/Roles/Groups/Rules model	Project Manager
Deliver implementation plan	Document requirements and goals. Document required functional and technical configuration. Build architecture Diagram. Update estimate planning based on implementation plan.	Project Manager
B. Infrastructure Setup
Install hardware systems	Plan EM12c compatible hardware systems	EM Admin
Operating system installation	Installation of certified OS platform [Refer Certify on MOS]	EM Admin
Network configuration of hardware systems	General network configuration of hardware systems I.e. TCP/IP, routing, NTP, firewall access, SSH, HTTP and HTTPS access. Set up network access between systems and work place of end users. Optionally implementation of remote access	EM Admin
C. Production EM 12c Cloud Control Installation
Install of Oracle Database 11gR2	Installation of Oracle Database 11gR2 for Repository	EM Admin
Installation of EM 12c Cloud Control Release 2 Update 1 and latest PSU	Installation of EM 12c Cloud Control OMS/Agents	EM Admin
Review HA, Security, Sizing Best practices	Setup as per EM 12c Admin guide	EM Admin
Review recommended patches	Review latest PSU and DBaaS patches as per MOS Note 1549855.1	EM Admin
D. Setting up High Availability
Configure Repository For High Availability	Enable Repository as per database high availability best practices. Configure Standby database @ DR site for EM cloud control repository. Configure repository database as RAC database with 2 or more nodes Configure OMS for RAC/standby repository	EM Admin
Configure OMS for High Availability	See Enterprise Manager Cloud Control Administrator's Guide for Configuring OMS for high availability	EM Admin
Configure Agents for High availability	See Enterprise Manager Cloud Control Administrator's Guide for Configuring Agents for high availability	EM Admin
Test failover strategy	Test the Repository and OMS failover to standby site	EM Admin
Setup backups	Setup back ups for Repository, OMS and agents	EM Admin
E. EM 12c Cloud Control Functional configuration
Defining Roles and assigning users	Setup Cloud, SSA Admin and SSA User roles and users	EM Admin
Setup S/W Library	Setup S/W Library for self update/plug-ins/cloud functionality	Cloud Admin
Setup Self Update	Setup self update to get new and updated capabilities when they become available between official releases	EM Admin
Setup required plug-ins on OMS	Oracle Cloud Application, Database, Chargeback and Capacity planning, Storage Management Framework (SMF) and Virtualization (VT) plug-ins are required	EM Admin
Customize SSA Login Page (Optional)	Configure Self Service Portal to customize the login page (eg option to have company brand image/service provider and tenant brand)	EM Admin
Define/setup Notification methods	Setup mail server for Notification. Setup email address for yourself and other administrators. Test and setup other notification methods if required	EM Admin
Add Host targets which are going to be used for Cloud	Push Agents on Servers part of PaaS Infrastructure. Push plug-ins on hosts	Cloud Admin
Configure Hosts for Privilege Delegation	Configure Hosts for Privilege Delegation. Setup Privilege delegation settings in Cloud Control. Setup named credential for cloud self service	EM Admin
Provisioning Oracle RDBMS s/w on Hosts	Provision/Configure and add Oracle Home as target in EM12c - Refer to EM 12c Lifecycle Management Admin guide for DB provisioning details. Optional - If planning to use GI/RAC for cloud services, provision/configure GI/RAC Oracle Homes as targets in EM12c In case of Exadata, Grid Infrastructure and RAC Database software is  already deployed on compute nodes via OneCommand from Oracle, so SSA  Administrator just needs to discover Oracle Homes and Listener as EM  targets. Databases will be created as and when users request for databases from cloud.	SSA Admin
Configure Listener	Configure and add Oracle Database Listener as target in EM12c	SSA Admin
F. Exadata Targets Monitoring Configuration in EM (Optional - in case DBaaS setup is on Exadata)
Deploy Agents on compute nodes	Deploy Agents on Exadata compute nodes	EM Admin
Exadata System Discovery and Configuration	Specify host from which Exadata server is being discovered. Specify discovery parameters for Infiniband switches. Specify discovery parameters for associated subsystems, such as ILOM, KVM, Cisco switch. Complete discovery and confirm that all targets have been discovered. Verify that cell groups have been created for cells associated with each database. Verify the targets in the topology view of the Exadata Database Machine system. Navigate the topology and verify the configuration of the each of the targets in the Exadata Database Machine system.	EM Admin
Exadata System Alerts, Metrics, Performance Monitoring and Actions	This use case will demonstrate the different alerts, metrics monitoring and actions available	EM Admin
Database to Exadata drilldown and IORM	Demonstrate the ability to drill down from the database I/O to Exadata and then control the database I/O on Exadata using IORM	EM Admin
G. DBaaS Functional configuration (RMAN, DBCA)
Creating PaaS Infrastructure zone	Create Database zones. Add Hosts in PaaS Infrastructure zones	Cloud Admin
Create Database Pool	Setup a pool of Oracle homes. The Oracle homes should be of the same version, platform	SSA Admin
Configuring Request settings	Configure the database request settings by specifying when a request can be made, its duration and its retention period	SSA Admin
Configure Quotas	Configure Quota as aggregate amount of resources that can be granted to each self service user belonging to a certain role	SSA Admin
Create DBCA/RMAN based Profiles	Configure Profile that capture source database information as RMAN backup or DBCA template	SSA Admin
Setup Service Templates in Catalog	Create service templates as standardized service definition that is offered to self service users to create databases	SSA Admin
Configure Chargeback	Defining a Universal Charge Plan. Create Extended Charge Plan. Create a Business Hierarchy of Cost Centers	SSA Admin
Provisioning Oracle database	Provisioning Oracle database instances using SSA	SSA User
Verify Chargeback Reports	Verify Chargeback Reports	SSA Admin
H. Schema-as-a-Service Functional configuration
Discover/Provision Oracle Database as Managed Target	Provision/Configure and add source database and Databases where Schema is going to provisioned as targets in EM12c	SSA Admin
Creating PaaS Infrastructure zone	Create Database zones. Add Hosts in PaaS Infrastructure zones	Cloud Admin
Create Database Pool	Setup a pool of databases. The databases should be of the same version, platform	SSA Admin
Configure Schema export based Profiles	Create a database provisioning profile using Export Schema objects	SSA Admin
Setup Service Templates in Catalog	Create service templates as standardized service definition that is offered to self service users to create schemas	SSA Admin
Configure Quotas	Configure Quota as aggregate amount of resources that can be granted to each self service user belonging to a certain role	SSA Admin
Configure Chargeback	Defining a Universal Charge Plan. Create Extended Charge Plan. Create a Business Hierarchy of Cost Centers	SSA Admin
Provision Schemas	Provisioning Oracle schema/service instances using SSA	SSA User
Verify Chargeback Reports	Verify Chargeback Reports	SSA Admin
I. Snap Clone Functional configuration
Creating PaaS Infrastructure zone	Create Database zones. Add Hosts in PaaS Infrastructure zones	Cloud Admin
Create Database Pool	Setup a pool of databases. The databases should be of the same version, platform.	SSA Admin
Register and Manage Storage servers	Configure and setup ZFS/Netapp storage servers in EM 12c	SSA Admin
Configure Snap Clone based Profiles	Create Database Provisioning Profile Using Snapshots	SSA Admin
Setup Service Templates in Catalog	Create service templates as standardized service definition that is offered to self service users to create instances	SSA Admin
Configure Quotas	Configure Quota as aggregate amount of resources that can be granted to each self service user belonging to a certain role	SSA Admin
Configure Chargeback	Defining a Universal Charge Plan. Create Extended Charge Plan. Create a Business Hierarchy of Cost Centers	SSA Admin
Provision using Snap Clone template	Provisioning Oracle database instances using SSA	SSA user
Verify Chargeback Reports	Verify Chargeback Reports	SSA Admin
J. Knowledge transfer
DBaaS Cloud setup demo/presentation	session to deliver overview of the implemented system and usage	Cloud/SSA Admin
K. Delivery
Delivery meeting	Delivery meeting initiated and organized by PM	Project Manager
Delivery of final implementation rollout	Update of implementation plan to reflect latest implementation changes	Project Manager
L. Post implementation tasks
Periodic health checks	Health check of the EM12c system, EMDiag	EM Admin
Periodic Cloud infrastructure usage analysis	Cloud infrastructure monitoring periodic basis and realigning quotas/policies and pool infrastructure with business priorities	EM Admin
DBaaS Workshop for Administrators/end users	Periodic training sessions for end users and Admins on DBaaS and EM 12c Monitoring/Administration capabilities	Cloud/SSA Admin
Post implementation assistance	Periodic reviews of Cloud and Management infrastructure and expansion planning with stakeholders and project  team	EM/Cloud/SSA Admin

Additional information -

• Video : Meet Growing IT Demand for Databases with Private DBaaS
• Private Cloud Setup and Administration guide
• Zero to Cloud Resource Center
• Schema as a Service for Extreme Consolidation 
• Delivering Database as a Service using Oracle Enterprise Manager 12c
• Best Practices for Database Consolidation in Private Clouds
• Demo - Oracle Cloud Management
• Demo - Setting up Database Clouds for Schema as a Service
• Oracle Database Resource Manager 
  

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

By Nilesh Agrawal

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 -

• Private Cloud Setup and Administration guide
• Schema as a Service for Extreme Consolidation 
• Delivering Database as a Service using Oracle Enterprise Manager 12c
• Best Practices for Database Consolidation in Private Clouds
• Demo - Setting up Database Clouds for Schema as a Service
• Oracle Database Resource Manager