Quickly Diagnose the Root Cause of Stuck Threads using Oracle Enterprise Manager 12c JVM Diagnostics

By Shiraz Kanga-Oracle

One of the hidden gems in Oracle Enterprise Manager 12c is JVM Diagnostics. If you purchased the Weblogic Management Pack license then you already own it. JVMD allows administrators to diagnose performance problems in production Java applications. By eliminating the need to reproduce these “production only” problems in QA, it reduces the time required to resolve them. It does not require complex instrumentation or restarting of the application to get in-depth application details. Application administrators will be able to identify Java problems or database issues that are causing application downtime without any detailed knowledge of the application internals. It is also very well suited to diagnosing issues with “Stuck Threads” which will be the focus of this blog.

What is a [STUCK] Thread

In a Weblogic server, all incoming requests are handled by a thread pool which is controlled by a work manager. Worker threads that are taken out of the pool and not returned after a specified time period are marked as [STUCK] by the work manager. This time period is 10 minutes by default but it is configurable on a per work manager basis using the "StuckThreadMaxTime" parameter (default is set to 600 seconds).

Note that it is possible that some of your threads are doing legitimate work for over 10 min with no issues. If you have such threads then you should consider placing them in a another work manager with proper setting for the "StuckThreadMaxTime" parameter

Why JVMD is Well Suited to Diagnosing [STUCK] Threads

Traditionally, developers will use a stack trace generated by jstack or kill -3 and try to determine the cause of a stuck thread. However, in my experience a majority of the time this stack is not even the culprit. The problem often lies in another tier of the application or even in another thread of the same application. JVMD has the ability to provide additional context such as the name of the request and which tier it called out to Eg: RDBMS servers, LDAP servers, Web servers, RMI servers, etc. Using fine grained thread states (i.e. DB, Network, IO, CPU, RMI, Lock, etc) and the ability to see additional details about the thread, JVMD users can quickly pinpoint the root cause of the problem. Since JVMD is always on, it can also debug these issues that happened in the past and can proactively notify you about stuck threads Eg: Get an email at 1am when you had stuck threads. And lastly, sometimes developers have no access to the target host due to lack of credentials needed to run command line applications.

On several occasions, the thread may be stuck but is doing legitimate work. In such scenarios JVMD allows you to scan back and forth through a large number of samples to see what work is being processed by the thread. In addition, you can take a look at other threads that were serviced the same request to see if they behaved similarly or not. This will allow you to quickly determine whether there is really a problem or not.

Real-Time [STUCK] Thread Analysis

With JVMD there are two real use cases for stuck thread analysis. If you get notified about a stuck thread in real-time (via email, etc) then you can perform a real-time stuck thread analysis. Alternatively, if you are investigating a thread that was stuck in the past but is not present any more, then you can perform a historical stuck thread analysis. In either case the first thing to do is to navigate to the JVM (or JVM pool) where the thread is stuck. We do this by clicking on Targets -> Middleware as shown

From here we can filter the list of targets by target type or by target name. Your most recent filter request will be remembered the next time you visit the page. Select the Target Type of JVM to see all of the JVM targets.

Pick the JVM for the Weblogic server which is having the stuck thread issues and click on it. This will take you to the target home page. Click the button at the top that says “Live Thread Analysis”. Type the word "stuck" into the thread name search box and click on the arrow to filter the table. Now you should see all the stuck threads. In this case we can see a thread that is stuck in the “Network Wait” state. It is stuck on line 358 in function writeBuffer() of OutputRecord.java which is in package com.sun.ssl.internal.ssl which makes it clear that this stuck thread has made an SSL call and the remote server has not responded in a reasonable amount of time so the client thread is stuck.

Here is another example of stuck threads, this time in the “DB Wait” state. Notice how the tool tip over the SQL ID field shows the SQL being executed. Click on it to view longer SQL statements. Also try clicking on the DB Wait link which takes you directly to this specific database session in the Oracle Database Diagnostics section of EM for further analysis. The columns displayed are controlled by “View” drop-down menu. Here we added the “User” column to show the logged in user who executed the request.

Historical [STUCK] Thread Analysis

In order to start historical stuck thread analysis you need to navigate to the JVM target home page in the same way as discussed in the real-time section. From the target home page clicking on the “JVM Performance Diagnostics” button at the top of the page. On the performance diagnostics page you can filter the data to make it more relevant to your task. The first filter to apply is of time. If you know the exact time you can use the “Edit Date and Time” button to specify it as shown. Otherwise use the handy shortcut links for Day, 1 Hours, 1 Hour or 15 Minutes as needed.

The next thing to filter is the Thread Name. Expand the filter options region if necessary and add the Thread Name filter to be “[STUCK]*” so you only see threads whose name starts with [STUCK]

Below the filter region the “General” tab will show you the Thread States, Top Requests, Top Methods, Top SQLs, Top DB Wait Events and Top Databases – only for the filtered data i.e. for only threads that are stuck. Try clicking on method names to see the call stack for the method. The charts are all interactive and fetch additional data about the item clicked.

If you want to find a specific thread move from the “General” tab over to the “Threads” tab. This is fine grained data with each sample and state transition visible. You can click on any sample to view it in the sample analyzer which should look familiar to you if you saw the threads in real-time. Details about SQLs, Wait states, etc are all available here also along with the complete call stack which can also be exported to a CSV file.

In conclusion, we can see the JVMD provides a rich set of additional details which are only a mouse click away that help you to diagnose the root causes of your stuck threads.

NOTE: Many of the screen shots taken here are using testing & debug code, which deliberately tries to create stuck threads. This does not and should not reflect on the nature of any Oracle products being shipped to customers.

By Shiraz Kanga-Oracle

The case for Snap Clone over point tools

By Sudip Datta 

Today, I stumbled over a competitor blog, conspicuous by its factual incorrectness on Enterprise Manager Snap Clone. However, I must compliment the author of the blog, because inadvertently, he has raised a point that we have been highlighting all along. The author, with reference to Dataguard and storage technologies, argues against the cobbling of technologies together and adding another technology stack to the mix without any automated management.  

Precisely the point! In the wide realm of technologies, there are necessities and there are accessories aka nice-to-haves. The necessities are technologies that are needed anyway, such as a high fidelity, high performance storage from a reputed vendor or a good DR solution for a mission critical database environment. Similarly, for any Oracle DBA worth his/her salt, Enterprise Manager 12c is a necessity, a part of the daily life. The Enterprise Manager agent, keeping vigil on every host, is therefore not an overhead, but the representative (the "agent" in true sense) of the DBA. Deep diagnostics, performance management, large scale configuration management, patching and compliance management make Enterprise Manager the darling of any Oracle DBA. All surveys suggest that any DBA spends considerable amount of time in Enterprise Manager for performing things beyond just data cloning, so why invest in an accessory for the cloning of Oracle test databases and unnecessarily proliferate the number of point tools (and possibly several instances of them) that you need to manage and maintain, not to ignore the past history that cites that very few such point tools solved customers' CAPEX and OPEX problems over the long run. It is like using spreadsheet for expenses and ERP for all other financial tasks.This is not to suggest that these point tools do not have good, innovative features. Over my tenure in the industry, I have come across several such tools with nice features, but often the hidden costs outweigh the benefits. Our position in this aspect has been consistent, whether it is on a competitor’s tool or our own. Few years back, we integrated My Oracle Support into Enterprise Manager with the same consistent goal that Enterprise Manager will serve as the single pane of glass for the Oracle ecosystem. Same has been our position on any product that we acquire.

Snap Clone's support for Dataguard and native storage stems from popular customer demand to leverage technologies they already invested in, and not create standalone islands of automation. Moreover, several customers have voiced in favor of the performance and scalability advantages that they would get by leveraging the native storage APIs. How else would you support one of the world's largest banks, a Snap Clone customer, who performs 60,000 (sixty thousand) data refreshes per year! In any case, that should not imply that we bind ourselves to any of those technologies. We do support cloning on various storage systems based on ZFS filesystem. Similarly, the Test Master refresh can be achieved with one among RMAN, Dataguard, Golden Gate or storage replication and optionally orchestrated with EM Job System.

Enterprise Manager 12c has taken a great step in delivering features via plugins that can be revisioned independent of the framework. An unwanted side effect is that the awareness often lags what is actually supported in the latest version of the product. For example, the filesystem support was introduced last Fall. And of course Enterprise Manager 12c Snap Clone supports RAC. My esteemed colleague and DBA par excellence, in her blog has highlighted some of these to dispel some of the prevalent awareness issues. Snap Clone's usage among the E-Business Suite and Developer community does not need any special accreditation. It is heavily used by the world's largest E-Business Suite Developer community-the Oracle E-Business Suite Engineering organization itself! It is true that Snap Clone does not support  restoration to any arbitrary point in time, but then our customers and prospects have not voiced a need for it. In reality, most customers want to perform intermediate data transformation such as masking and subsetting as they clone from production to test, and Enterprise Manager 12c already boasts of sophisticated data masking technologies, again via the same interface. It also includes testing features like Real Application Testing (RAT) that can complement and follow the test database creation. Future releases of Enterprise Manager will support a tighter integration among these features.

Snap Clone is delivered as a part of the Database as a Service feature set that has been pioneering, industry-leading and getting adopted at a great pace. Little wonder that we have already received a copious amount of Openworld paper submissions on the topic. In this emerging trend of DBaaS adoption, we find no reason to fragment the tasks such as fresh database creation, pluggable database provisioning and cloning across silo'ed point tools (not to mention broader PaaS capabilities which may be needed for complete application testing). Each use case could be different but needs a single service delivery platform. EM12c is that platform for Oracle. Period. So, think twice before 'adding another technology to the mix'. You do not need to.

By Sudip Datta 

Leveraging Target Properties to Enhance Enterprise Manager Capabilities

By Courtney Llamas

Do you still maintain a spreadsheet with Database or Server contact or business unit ownership?  In Oracle Enterprise Manager 12c (EM) Target Properties allow you to store descriptive target information, such as Contact or Location, which can then be used in dynamic/administration group definition, reports, incident rules and notifications.   This blog will show you how you can better leverage the features of EM to store your configuration data and utilize it to the fullest extent. 

The out-of-the-box target properties include:

• Contact
• Cost Center
• Customer Support Identifier
• Department
• Life Cycle Status (Development, Test, Staging, Mission Critical, Production)
• Line of Business
• Location
• Operating System – populated by collected configuration data
• Platform Target Type – populated by collected configuration data
• Target Version – populated by collected configuration data

LifeCycle Status

The LifeCycle Status target property has a special role within EM 12c.   The values are restricted to one of the predefined options:  Development, Test, Staging, Production, Mission Critical.   The value of this property is used by EM when it is under heavy load in prioritizing incoming alerts and data.   Mission Critical and Production targets are given the highest priority so that alerts always get loaded.   Targets of Development or Test status may be asked to "back off" until the load returns to a manageable level.  You can not add to these values, but you can modify the display name with EM CLI.   For example, you could use the command below to rename the Test status to QA.   

$ emcli modify_lifecycle_stage_name  -name="Test"  -new_name="QA" 

Setting Target Properties

To set target properties on an individual target, you can select the target menu (i.e. Host, Oracle Database) then selectTarget Setup / Properties and click the Edit button. This will provide an input screen such as the following:

As you can see, the Operating System, Platform, Target Version and Customer Support Identifier are not editable.  These are inherited from settings elsewhere or through configuration data collected by EM.

The most efficient time to set target properties is when you’re discovering or promoting a new target.   You will see a button Set Global Target Properties which will set the target properties for all selected targets. 

This will open a dialog box where you can set the target properties for all selected targets.  

If you're unable to set the properties while discovering a large number of databases, your best bet is to bulk set target properties using EM’s Command Line Interface (EM CLI).  In the example below we set the group MyProdGroup to a LifeCycle Status of Production.  The flag –propagate_to_members can be used to cascade target properties to a targets members, such as a group, domain or cluster.  In this example, all members of MyProdGroup would have a LifeCycle Status of Production.

$ emcli set_target_property_value 
-property_records="MyProdGroup:composite:LifeCycle Status:Production" 
-propagate_to_members

Using EM CLI can be very helpful when setting target properties on a large number of targets, or when you want to modify a large number of targets (e.g. changing Department of Support to Operations).  For a sample script that can be leveraged for setting target properties in bulk please see the previously published blog Using EM CLI for mass update of Lifecycle Status Property Value.

User Defined Target Properties

EM also allows you to create custom target properties, or User Defined Target Properties.  While you cannot use custom target properties in dynamic or administration group selection criteria, you can use them for reporting and notifications.  Creating custom target properties is accomplished by using EM CLI and providing the property name and the target type it applies to.  While it is possible to set a custom property for just one target type, it is recommended to standardize these custom properties across all target types as they are stored in a sequential fashion (udtp_1, udtp_2, etc).  If you set per target type, udtp_1 may be Owner on Host targets and Function on Oracle Database targets.  This can cause confusion in reporting and notifications as you will see later.   

To add a custom target property for Owner, we use the EM CLI command below:

$ emcli add_target_property –target_type=”*” –property=”Owner”

Next, we’re going to set the value of the Owner target property to Courtney for a particular target (emrep) of target type (oracle_database).

$ emcli set_target_property_value 
–property_records=”emrep:oracle_database:Owner:Courtney”

On the following screen you can see that we’ve got a new property “Owner” with a value set to Courtney.

Utilizing Target Properties

All target properties are stored in the MGMT_ALL_TARGET_PROPS table with corresponding internal names (udtp_1, udtp_2, etc.).   The following query will show you what the internal names are:

SQL>  select property_display_name, property_name      from mgmt$all_target_prop_defs;

PROPERTY_DISPLAY_NAME          PROPERTY_NAME------------------------------ -------------------------Comment                        orcl_gtp_commentContact                        orcl_gtp_contactCost Center                    orcl_gtp_cost_centerCustomer Service Identifier    orcl_gtp_csiDepartment                     orcl_gtp_departmentDeployment Type                orcl_gtp_deployment_typeLifeCycle Status               orcl_gtp_lifecycle_statusLine of Business               orcl_gtp_line_of_busLocation                       orcl_gtp_locationOperating System               orcl_gtp_osPlatform                       orcl_gtp_platformTarget Version                 orcl_gtp_target_versionOwner                          udtp_1

Now you can access the custom target properties through their internal names in custom notification scripts. For instance, OS Command notifications can call the environment variables such %UDTP_1%, %ORCL_GTP_LIFECYCLE_STATUS%, etc.    

Properties can also be accessed in reports against the MGMT$ view tables, which will allow you to build custom reports that show additional target information such as Owner or LifeCycle Status. 

SQL> select t.target_name, t.target_type,
       d.property_display_name, p.property_value     from mgmt$target t,          mgmt$target_properties p,          mgmt$all_target_prop_defs d     where t.target_guid=p.target_guid       and p.property_name=d.property_name       and p.property_value is not null;

TARGET_NAME          TARGET_TYPE      PROPERTY_DISPLAY_NAME PROPERTY_VALUE-------------------- ---------------- --------------------- --------------emrep                oracle_database  Target Version        11.2.0.3.0emrep                oracle_database  Owner                 Courtneyemrep                oracle_database  Operating System      Linuxemrep                oracle_database  Platform              x86_64

By leveraging the out-of-the-box Target Properties and the User Defined Target Properties, you can easily extend the flexibility of EM’s groups, incident rules, reports and notifications to suit your business needs. For additional information on Target Properties used in Administration Groups see the Oracle Enterprise Manager Cloud Control 12c Administrator's Guide.   

By Courtney Llamas

Enterprise Manager not effected by Heartbleed

The Oracle Security team has indicated that Enterprise Manager Cloud Control, Grid Control as well as Ops Center are not effected by the Heartbleed vulnerability. This is mentioned in the internet published document on the Oracle Technical Network (OTN):

http://www.oracle.com/technetwork/topics/security/opensslheartbleedcve-2014-0160-2188454.html

An excerpt from this document:

1.0 Oracle products that, while using OpenSSL, were not subject to CVE-2014-0160

Global Product Security has determined that the following products are using OpenSSL cryptographic libraries whose versions have been externally reported as not vulnerable to CVE-2014-0160 or did not use OpenSSL libraries to implement the vulnerable TLS protocol. No further action is therefore expected for these products:

• Advanced Lights Out Manager (ALOM) [Product ID 9843/ALOM/ALOM]
• ALOM-CMT [Product ID 9846/SYSFW-ALL/ALOM-CMT]
• Audit Vault [Product ID 1977,9749]
• Brocade(McData) Fiber Channel Switches and Management Software [Product ID 9864]
• Cisco MDS Fiber Channel Switches and Management Software [Product ID 9865]
• Corente Services Gateway
• E-Business Suite 11i
• eGate Integrator 5.0.5 SRE
• Enterprise Manager Cloud Control
• Enterprise Manager Cloud Control Plug-ins and Connectors
• Enterprise Manager Grid Control [Product ID 1370]
• Enterprise Manager Grid Control Plug-ins and Connectors
• Enterprise Manager Ops Center
• Exadata [Product ID 2546]
• ....
• ....

Please read the document for the full list.

Regards,

Porus.

Steps to Fast Track your Database Cloud implementation on Exadata

By Nilesh Agrawal on Feb 13, 2014

Oracle Exadata Database Machine is the ideal consolidation platform for Enterprise Database Cloud and Oracle Enterprise Manager provides the most optimized and comprehensive solution to rapidly setup, manage and deliver Enterprise Clouds. Clearly, very significant innovations have been delivered via Exadata X4, Enterprise Manager 12c and Database 12c in Cloud Computing space and customers can start realizing benefits from this combination of most powerful and unique enterprise database cloud solution in industry.

As per OracleVoice blog on Forbes.com:  "Why Database As A Service (DBaaS) Will Be The Breakaway Technology of 2014":

"Database as a Service (DBaaS) is arguably the next big thing in IT. Indeed, the market analysis firm 451 Researchprojects an astounding 86% cumulative annual growth rate, with annual revenues from DBaaS providers rising from $150 million in 2012 to $1.8 billion by 2016."

In this blog post, I will walk through the steps aiming to simplify DBaaS Setup on Exadata and also describe automation kits available to achieve the following rapidly - 

• Setup Monitoring and Management of Exadata Database Machine platform in EM 12c
• Setup and Deliver DBaaS on Exadata using EM 12c
• Manage and Optimize Exadata and EM 12c powered DBaaS cloud platform on an ongoing basis

There are 2 separate automation kits that are provided with EM 12c, first kit is for enabling rapid monitoring and management setup of Exadata stack in EM 12c and second kit is for rapid setup of DBaaS -

1) Deploy EM 12c site or use existing site - If you do not have existing EM 12c R3 setup, you can use EM Automation Kit for Exadata for installing EM 12c R3 Plug-in update 1. This kit is available via patch 17036016 on My Oracle Support(MOS) and can be used to deploy EM 12c latest release. Refer to Readme of patch and MOS note "Obtaining the Oracle Enterprise Manager Setup Automation kit for Exadata (Doc ID 1440951.1)" for additional details. Please note that this will setup EM12c Oracle Management Service along with Management Repository. It can be deployed on a single m/c or OMS and OMR can be setup on different machines.

2) Deploy EM 12cR3 agents and required plug-ins on Exadata Machine - Agent kit is also part of the same EM Automation Kit for Exadata and can be used for deploying agents and plug-ins on Exadata stack. Refer to MOS note "Obtaining the Oracle Enterprise Manager Setup Automation kit for Exadata (Doc ID 1440951.1)" for additional details. Best practice is to use most recent version of Agent kit and also deploy latest plug-ins. Patch details for respective platform are described in the MOS note.

Agent kit script will require Java 1.6.0_43 or greater version on database node where this script is being run. Agent kit script will need to be run as root OS user on Exadata db node, however JAVA_HOME and PATH with JAVA_HOME/bin should be set up as agent OS owner, so these OS env variables need to setup in profile of agent OS owner.

Agent Automation kit helps with achieving following - 

• EMCLI setup on Exadata Server
• EM 12c R3 site compatibility checks
• Setup and remove SSH between Exadata nodes to test SSH setup
• Deploy EM 12c Agent and required Plugins on all DB Nodes of Exadata Machine
• Confirm Exachk tool availability and run Exachk tool
• Run Exadata Discovery Prerequisites
• Discover Targets Cluster, Grid Infrastructure, RAC database and listener targets

Note - In case of Exadata X4, ensure you have the EM 12cR3 latest Bundle patch(released in January 2014). Refer to following MOS notes -
Enterprise Manager 12.1.0.3 Bundle Patch Master Note (Doc ID 1572022.1)
Enterprise Manager for Exadata Plug-in 12cR3 Bundle Patch Bug List (Doc ID 1613177.1)

3) Discover Grid Infrastructure and RAC targets – Above setup script will discover Targets Cluster, Grid Infrastructure, RAC database and listener targets. Discover Grid Infrastructure, ASM and RAC targets manually if required.

4) Please note that this setup script will not discover Oracle Exadata Database machine target in EM 12c. You need to discover the machine using following steps

• From the Setup menu, select Add Targets, then select “Add targets Manually”.
• In the “Add Targets Manually” page, select 'Add Targets Using Guided Process (Also Adds Related Targets)' and Target Type as Oracle Exadata Database Machine.
• Click Add Using Guided Discovery and follow the wizard.

5) Setup Database Cloud Using Rapid Start Kit - Once you have setup Exadata management in EM 12c, next step is to setup database cloud. Refer to Rapid Start Kit for setting up cloud for both DBaaS and Pluggable DBaaS/PDBaaS. This kit will help achieve the following -

• Create Cloud Admin, SSA Admin and SSA User custom roles
• Create Cloud Admin, SSA Admin and SSA Users
• Grant Quota to SSA User custom roles
• Setup Zones with Placement Policy Constraints
• Setup Pools with Placement Constraints
• Setup Service Template/Catalog and grant it SSA User custom roles.

Here are brief steps for setting up Database Cloud using Rapid start Kit, available in EM Agent Kit 12.1.0.3.0, after login to Exadata machine first DB node as EM 12c agent owner

• Change to /cloudsetup directory.
• Review the input files under config directory and customize the dbaas_cloud_input.xml for configuring DBaaS cloud and pdbaas_cloud_input.xml for configuring Pluggable Database as a Service.
• Run the following command to setup DBaaS on Exadata Machine.

/emcli login -username=sysman
/emcli @exadata_cloud_setup.py –dbaas
Above command will use dbaas_cloud_input.xml (under cloudsetup/config) as input file for configuring DBaaS. 

• To setup PDBaaS on Exadata, please use following command.

/emcli @exadata_cloud_setup.py –pdbaas
Above command will use pdbaas_cloud_input.xml (under cloudsetup/config) as input file for configuring PDBaaS

Note: Currently Rapid Start kit for DBaaS makes use of 11.2.0.3.0 Database "Exadata Data Warehouse" Profile available out-of-box. However you can create your own DBCA based Profiles and customize the dbaas_cloud_input.xml. Also if you need to use RMAN backup based or Snap clone based profile, you can to login to EM12c SSA Portal as SSA Administrator, to create the profile and setup service template.  

At this stage, you will be able to manage and deliver your Exadata powered enterprise database cloud using EM 12c.

Additional References:

Obtaining the Oracle Enterprise Manager Setup Automation kit for Exadata (Doc ID 1440951.1)

Oracle® Enterprise Manager Cloud Administration Guide12c Release 3 (12.1.0.3)

Oracle® Enterprise Manager Exadata Management Getting Started Guide Release 12.1.0.5.0

New Technical article: Back Up a Thousand Databases Using Enterprise Manager Cloud Control 12c

Friends,

I am pleased to announce that a new technical article of mine has been published (January 2014) on the Oracle Technical Network.


Back Up a Thousand Databases Using Enterprise Manager Cloud Control 12c

http://www.oracle.com/technetwork/articles/oem/havewala-rman-em12c-2104270.html

This detailed technical article explains the set up and scheduling of full and incremental RMAN Database backups for  thousands of databases using Enterprise Manager Cloud Control (Enterprise Manager) 12c, and how this is done more easily and efficiently than the older, more time-consuming, manual method of performing Unix shell scripting, RMAN scripting, and cron jobs for each database to be backed up. 

And with the Database Group Backup feature new to Enterprise Manager Cloud Control 12c, it can be even faster to set up RMAN backups for multiple databases - even if there are thousands - that are part of an Enterprise Manager Database Group.

The article also highlights the advantages of using PDBs in Oracle Database 12c and backing them up using RMAN. RMAN cannot backup individual schemas, and it has always been difficult to perform point-in-time-recovery (PITR) at an individual schema level, since schemas can easily be distributed across multiple tablespaces. The advantage in using PDBs in a Container Database is that you can easily set up RMAN backups at the Container Database level, and yet perform PITR at the PDB level. This is a clear technical advantage of the Multi-tenant architecture of Oracle Database 12c.

The set up and scheduling of RMAN database backups forms a part of the Base Database Management features of Enterprise Manager that enables numerous customers to use Enterprise Manager 12c more and more.  In fact I had personally introduced Enterprise Manager to HDFC bank in India in 2007 for the purpose of their RMAN backups, they started using it for the first time, and today they are a DBaaS-Exadata reference customer who have presented in OOW for the last 2 years.

Regards,

Porus.

What is EM 12c DBaaS Snap Clone?

By Adeesh Fulay 

Happy New Year to all! Being the first blog post of the new year, lets look at a relatively new feature in EM that has gained significant popularity over the last year - EM 12c DBaaS Snap Clone.

The ‘Oracle Cloud Management Pack for Oracle Database’ a.k.a the Database as a Service (DBaaS) feature in EM 12c has grown tremendously since its release two years ago.  It started with basic single instance and RAC database provisioning, a technical service catalog, an out of box self service portal, metering and chargeback, etc. But since then we have added provisioning of schemas and pluggable databases, full clones using RMAN backups, and Snap Clone. This video showcases the various EM12c DBaaS features.

This blog will cover one of the most exciting and popular features – Snap Clone. In one line, Snap Clone is a self service way of creating rapid and space efficient clones of large (~TB) databases.

Self Service - empowers the end users (developers, testers, data analysts, etc) to get access to database clones whenever they need it.
Rapid - implies the time it takes to clone the database. This is in minutes and not hours, days, or weeks.
Space Efficient - represents the significant reduction in storage (>90%) required for cloning databases 

Customer Scenario 

To best explain the benefits of Snap Clone, let’s look at a Banking customer scenario:

• 5 production databases total 30 TB of storage
• All 5 production databases have a standby
• Clones of the production database are required for data analysis and reporting
• 6 total clones across different teams every quarter
• For security reasons, sensitive data has to be masked prior to cloning

Based on the above scenario, the storage required, if using traditional cloning techniques, can be calculated as follows:

5 Prod DB                  = 30 TB
5 Standby DB            = 30 TB
5 Masked DB             = 30 TB (These will be used for creating clones)
6 Clones (6 * 30 TB) = 180 TB
                               ------------------
Total                           = 270 TB
Time = days to weeks

As the numbers indicate, this is quite horrible. Not only 30 TB turn into 270 TB, creating 6 clones of all production databases would take forever. In addition to this, there are other issues with data cloning like:

• Lack of automation. Scripts are good but often not a long term solution.
• Traditional cloning techniques are slow while, existing storage vendor solutions are DBA unfriendly 
• Data explosion often outpaces storage capacity and hurts ITs ability to provide clones for dev and testing
• Archaic processes that require multiple users to share a single clone, or only supports fixed refresh cycles
• Different priorities between DBAs and Storage admins

Snap Clone to the Rescue 

All of the above issues lead to slow turnaround times, and users have to wait for days and weeks to get access to their databases. Basically, we end up with competing priorities and requirements, where the user demands self service access, rapid cloning, and the ability to revert data changes, while IT demands standardization, better control, reduction in storage and administrative overhead, better visibility into the database stack, etc.

EM 12c DBaaS Snap Clone tries to address all these issues. It provides:

• Rapid and space efficient cloning of databases by leveraging storage copy-on-write (or similar) technology
• Supports all database versions from 10g to 12c
• Supports various storage vendors and configurations NAS and SAN
• Lineage and association tracking between clone master and its various clones and snapshots
• 'Time Travel' capability to restore and access past data
• Deep visibility into storage, OS, and database layer for easy triage of performance and configuration issues
• Simplified access for end user via out-of-the-box self service portal
• RESTful APIs to integrate with custom portals and third party products
• Ability to meter and charge back on the clone databases

So how does Snap Clone work?

The secret sauce lies in the Storage Management Framework (SMF) plug-in. This plug-in sits between the storage system and the DBA, and provides the much needed layer of abstraction required to shield DBAs and users from the nuances of the different storage systems. At the storage level, Snap Clone makes use of storage copy-on-write (or similar) technology. There are two options in terms of using and interacting with storage:

1. Direct connection to storage: Here storage admins can register NetApp and ZFS storage appliance with EM, and then EM directly connects to the storage appliance and performs all required snapshot and clone operations. This approach requires you to license the relevant options on the storage appliance, but is the easiest and the most efficient and fault tolerant approach.

2. Connection to storage via ZFS file system: This is a storage vendor agnostic solution and can be used by any customer. Here instead of connecting to storage, the storage admin mounts the volumes to a Solaris server and format it with ZFS file system. Now all snapshot and clone operations required on the storage are conducted via ZFS file system,. The good thing about this approach is that it does not require thin cloning options to be licensed on the storage since ZFS file system provides these capabilities.

For more details on how to setup and use Snap Clone, refer to a previous blog post. 

Now, lets go back to our Banking customer scenario and see how Snap Clone helped then reduce their storage cost and time to clone.

5 Prod DB                      = 30 TB
5 Standby DB                 = 30 TB
5 Masked DB                 = 30 TB
6 Clones (6 * 30 TB)      = 180 TB
6 Clones (6 * 5 * 2 GB) = 60 GB
                                   ------------------
Total                               = 270 TB 90 TB
Time = days to weeks minutes

Assuming the clone databases will have minimal writes, we allocate about 2GB of write space per clone. For 5 production databases and 6 clones, this totals to just 60GB in required storage space. This is a whopping 99.97% savings in storage. Plus, these clones are created in matter of minutes and not the usual days or weeks. The product has out-of-the-box charts that show the storage savings across all storage devices and cloned databases. See the screenshot below.

Where can you use Snap Clone databases?

As i said earlier, Snap Clone is most effective when cloning large databases  (~TBs). Common scenarios we see our customers best use Snap Clone are:

• Application upgrade testing. For example, EBusiness suite upgrade to R12. 
• Functional testing. For example, testing using production datasets.
• Agile development. For example, run parallel development sprints by giving each sprint its own cloned database.
• Data Analysis and Reporting. For example, stock market analysis at the close of market everyday.

Its obvious that Snap Clone has a strong affinity to applications, since its application data that you want to clone and use. Hence it is important to add that the Snap Clone feature when combined with EM12c middleware-as-a-service (MWaaS) can provide a complete end-to-end self service application deployment experience. If you have existing portals or need to integrate Snap Clone with existing processes, then use our RESTful APIs for easy integration with third party systems.

In summary, Snap Clone is a new and exciting way of dealing with data cloning challenges. It shields DBAs from the nuances of different storage systems, while allowing end users to request and use clones in a rapid and self service fashion. All of this while saving storage costs. So try this feature out today, and your development and test teams will thank you forever.

In subsequent blog posts, we will look at some popular deployment models used with Snap Clone.

-- Adeesh Fulay (@adeeshf)

Additional References

Cloud Management Page on OTN

Cloud Administration Guide (Documentation)

Enterprise Manager 12c Database-as-a-Service Snap Clone Overview (Presentation)