Snap Cloning Databases on Exadata using Enterprise Manager

By Subhadeep Sengupta (Oracle)

Historically, Exadata has mostly been deployed for heavy, production workloads, leaving cheap commodity hardware and third-party storage to perform as infrastructure for Development and Testing. From the viewpoint of Enterprise Manager, we have seen customers clone production databases running on Exadata to secondary storage such as ZFS Storage Appliance or even third-party NAS or SAN for the purpose of testing. Customers mainly used RMAN (with or without Enterprise Manager) to clone the databases. While the master clones (often referred to as Test Master) could be further cloned via storage efficient snapshots, there were significant limitations to the approach.

• First of all, the testing on non-like systems from Exadata (both compute and storage) often yielded erroneous inferences. 
• Second, both the compute and storage on existing Exadata racks often remained underutilized.

Most surveys establish that there are several Dev/Test copies for every Production database, and leaving Dev/Test outside the realm of Exadata can only yield partial usage of engineered systems. 

Two recent advancements in Exadata break this existing barrier. First of all, the compute nodes on Exadata can now be virtualized. Consolidated environments can now use Oracle Virtual Machine (OVM) on X5-2, X4-2, X3-2, and X2-2 database servers to deliver higher levels of consolidation and isolation between workloads. The Virtual machines can be configured on demand with the appropriate number of Virtual CPUs (vCPUs) for iterative testing. Second, with Oracle Database 12c Release 1 (12.1) release 12.1.0.2 BP5 or later, space-efficient database snapshots can now be quickly created for test and development purposes on Exadata.

Snapshots start with a shared read-only copy of the production database (referred to as the Test Master in Enterprise Manager Parlance) that has been masked and/or cleansed of any sensitive information. Snapshot technology as deployed on Exadata is "allocate on first write", not copy on write. As changes are made, each snapshot writes the changed blocks to a sparse disk group. Multiple users can create independent snapshots from the same base database, therefore multiple test and development environments can share space while maintaining independent databases for each task. The base database must remain read-only during the usable lifespan of snapshots that are created from that base database. If there is a need to create another set of snapshots from a more recent copy of production data, a new read-only base from a production database needs to be created.

Enterprise Manager 12cR5 leverages the capabilities of Exadata to extend the Snap Clone capabilities for Exadata Sparse clones. As shown in this tutorial, with Snap Clone, Enterprise Manager can create a Test Master using either Dataguard technology or RMAN preceded by data masking. The Test Master can be created either on the same Exadata rack or on a different one. Once the Test Master has been created, snapshots can then be created on the sparse disk groups using the Deployment Procedures. The Deployment Procedures also automate the post-cloning discovery and promotion of the cloned targets, making them fully managed right from inception. Internal testing confirms that for cloning a Terabyte of database with a complete discovery of all its components takes less than a minute.

Enterprise Manager also helps DBAs track the lineage of the clones by providing a report on the production database, the Test Master and its clones. Enterprise Manager Snap Clone on Exadata supports both regular as well as pluggable databases with optional ACFS configuration. In addition to support for Exadata sparse clones, Snap Clone continues to support NAS (ZFS Storage Appliance and NetApp) and SAN (certain EMC storage arrays), in case users want to deploy these for their Dev-Test environments. 

Further Reading Resources

• To learn more about Database as a Service visit the otn page.
• Prerequisites for setting up Exadata Snapshots are documented here.
• Watch the video, Snap Clone Multitenant (Pluggable Database) on Exadata here.

--Subhadeep Sengupta (@Subh_here) 

Managing Oracle Database 12c with Enterprise Manager – Part XVI

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we were exploring the Activity tab in the Performance Hub of Enterprise Manager Database Express 12c. Let us move to the Monitored SQL Tab.

This is the Real-time SQL Monitoring feature of the Diagnostics pack. This screen shows all the long running SQL statements (that have consumed 5 seconds or more of combined CPU and I/O time in a single execution, or are using parallel query).

Ever wondered why that report was taking so long? It is possible to drill down and see the plan steps executing for the SQL statement, as can be seen in the following screenshot. This helps considerably in analyzing long-running SQL statements.

The next two tabs of the Performance Hub show ADDM (Automatic Database Diagnostics Monitor) and its results, including Real-time ADDM which was previously used for emergency database issues, but now runs proactively to catch database issues before they cause a real problem.

For example, the following real-time ADDM report shows library cache contention, and the “Show Reasons” button suggests that the system is CPU bound.

Real-time ADDM runs in the database automatically every 3 seconds, and in this way is able to detect transient performance issues. The performance data in memory is examined, and any performance spikes are detected. The administrator is then informed of the spike and its root cause.

This blog post was originally published at this link.

Managing Oracle Database 12c with Enterprise Manager – Part XV

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we started to look into the Performance Hub of Enterprise Manager Database Express 12c. Let us move to the Activity tab.

The red line shows the CPU cores used by this VBOX image, and obviously at a point of time this line has been exceeded. The total CPU wait class can be seen in green, the red part shows the concurrency wait class and so on.

We can drill down to the actual SQL Id and user session at this point of time. However, we can also change the top dimensions of this graph to the actual wait event, as seen below.

Notice that the graph has now changed to display the individual wait events such as db file sequential read, log file parallel write, row cache lock and so on. You can drill down further on any of these wait events and find the actual SQL and session causing the events. 

You can also change the lower dimension, here we have drilled down on the wait event: db file sequential read, and changed the lower dimension to “object” – this displays the objects causing this wait event, and also the SQL below. Pretty powerful.

You can select a number of other dimensions such as SQL ID, object, Instance, PDB, Service, User ID, and so on in this graph.

This blog post was originally published at this link.