Top 60 Oracle Blogs

Recent comments

Advances in Data Clone and Refresh in EM13c

Enterprise Manager 12c introduced a lot of functionality around cloning Oracle databases and refreshing the data once it was cloned. You can see some of the earlier material I have posted on this area in the following posts:

Data Cloning Use Cases

As you can see if you look back over this material, there are quite a few different use cases we can go through for data cloning and refresh, but for now I want to focus on the 6 use cases shown on the following image:

clone1 300w, 768w, 250w, 150w" sizes="(max-width: 882px) 100vw, 882px" />

  1. Extreme Performance Database – In an environment where extreme performance is critical, stress testing is equally critical. You must be sure that the environment you build can live up to the extreme expectations that your end users will have, and the only way to do that is to take a clone of your Production environment to another machine where you can perform your stress testing without impacting your end users. If possible, this clone should be a full clone to simulate the data loads you have in your Production environment.
  2. Cloud to Cloud Disaster Recovery – Some people think that because your data is stored in the cloud, there is no need for disaster recovery (DR). However, when you look below the covers, data in the cloud is still stored somewhere on one or more physical machines in your cloud provider’s data center. Those machines, or indeed the data center itself, can still fail for a number of reasons so you need to protect your data from such disasters by cloning it to another cloud environment.
  3. Cloud to On Premises Disaster Recovery – The same situation as we mentioned in the last use case, but here you can protect your data by failing over from the cloud environment to your own on premises environment.
  4. Partner testing and certification – When partners release new versions of their applications, or need to certify their applications against new versions of Oracle software, cloning an existing environment allows them to test in multiple ways, or indeed to have multiple tests running against different clones. Building such clones manually can be a very time consuming exercise, so using Enterprise Manager’s automated Snap Clone functionality can be very useful.
  5. Development and Test environments – Cloning capabilities make the building of Development and Test environments very straightforward. Depending on the type of testing being done, you can create either full clones (for performance and stress testing) or snap clones (for functional testing).
  6. Try before you buy – Customers that want to try new versions of the Oracle software or partner software can use the cloning capabilities of Enterprise Manager to build clones on hardware belonging either to Oracle or to the partner. This allows them to test the functionality of the new version before making a decision to buy more hardware or software to support the new application.

EM12c Data Cloning Options

Before we move on to looking at the advances in data cloning and refreshing, let’s take a minute to see what we already have available to us in EM12c (see the diagram below). At the highest level, that can be broken down into creating full clones and snap (or thin) clones.

clone2 300w, 768w, 250w, 150w" sizes="(max-width: 882px) 100vw, 882px" />

Full clones are great if you need to look at stress or performance testing. To do that well, you need to take a full copy of your Production environment so you can reproduce the issues you are going to face in Production. Full copies can be taken using either an RMAN restore operation or the RMAN Duplicate command, or of course via a full export using the Data Pump utility. The main issue you face in this area is the need to have sufficient storage to build a complete copy of your Production environment. Once you have built that you can use tools such as Real Application Testing (commonly referred to as RAT) to do the actual stress or performance tests.

Snap clones can be built using either software solutions (where you do not need to have specific vendor hardware) or hardware solutions which require specific vendor hardware. If you have either Oracle’s ZFS File System or ACFS available to you, you can use the native snapshotting capabilities within those products to create a snap clone. Alternatively, you can use CloneDB, which has been available as part of the RDBMS itself since From the hardware side of the house, snap clones can be built on top of Oracle Exadata, the Sun ZFS Storage Appliance, NetApp, or EMC hardware.

This breadth of both full clone and snap clone capabilities allows you to leverage your existing investments in hardware and software, cater to both your functional and stress testing needs, and maximize your cloning efforts for best performance. Of course, if you are going to perform any of these cloning options on a regular basis, it’s best to automate these capabilities, and the Snap Clone functionality within Enterprise Manager allows you to do just that. It also allows you to pass off this need to your users via a Self Service portal. Use of Snap Clone requires the Cloud Management Pack for Oracle Database.

How to Create a ‘Test Master’

In EM12c, there were clone and refresh options from Oracle Database 10g up to and including Oracle Database 12c (including multitenant databases). Depending on the configuration, you also had the capability to make a number of changes along the way as well (see the picture below).

clone3 300w, 768w, 250w, 150w" sizes="(max-width: 882px) 100vw, 882px" />

These included:

  • Configuration changes – you could have your Production environment running as a 3 node Real Application Clusters (RAC) database, while your cloned environment was running as a single instance environment, or vice versa.
  • In-line PSU patch applications – your Production environment could be using an ORACLE_HOME, while your cloned environment was using This allowed you to test the effect an update from one version of the RDBMS to another.
  • Data masking – As you were creating your cloned Test Master database, you could mask sensitive data, allowing you to protect personal information that you would not want exposed in a test environment.
  • Customization hooks – As you created your cloned environment, you may have needed to make environment specific changes. The cloning process allows you to run both pre-creation and post-creation scripts, as well as running custom SQL scripts (for example, if you wanted to mask the data in ways that standard data masking was not capable of).
  • Advanced PDB creation options – when you created your cloned environment, if it was running in a multitenant architecture there were options to specify both maximum size and maximum shared tablespace size, as well as logging options.

In addition, you can also automate the post-cloning discovery and promotion of the cloned targets, making them fully managed right from inception. 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.

Flavors of ‘Test Master’

Up until now, we’ve been discussing the functionality that was available prior to EM13c. There were two different flavors of Test Master databases, shown in the figure below. One was using an RMAN image backup to create the Test Master, and in this environment you could both mask the data and change the configuration from RAC to single instance (or vice versa) when creating the Test Master database. The second flavor used Data Guard to create a standby database that could then be promoted to Test Master. In this environment, masking is not possible since it uses SQL to mask the data and in a Data Guard environment you are sending a redo stream from your primary environment to the standby, not SQL.

clone4 300w, 768w, 250w, 150w" sizes="(max-width: 882px) 100vw, 882px" />

In EM13c we have added another variety of test master, called a test master snapshot. Let’s spend some time looking at that now in more detail.

Introducing: Test Master Snapshot

Creating a test master database in Enterprise Manager currently requires these steps:

  • Create one or more volumes of appropriate size and mount them on the required hosts, using the correct mount option.
  • Manually copy a database backup to the above volumes
  • Bring up the database
  • Trigger an association procedure in Enterprise Manager to detect this new database
  • Enable Snap Clone on this database.
  • Create profiles and service template and provision new databases.

The test master snapshot functionality gives the administrator the ability to launch a single operation that will create a storage volume, backup the data files and archive logs, and perform continuous sync between the test master environment and its database source. Note that the test master is a logical construct, rather than an actual physical database. The administrator can create test master databases based on any existing source database (both single instance and RAC are supported, though there is no PDB support in this initial release). They can setup schedules to continuously sync the Test Master from the source database. Only single instance target clones based on the test master are supported now. The admin can then create a Snap Clone based on the Test Master as needed. This new functionality is supported via the Enterprise Manager GUI, as well as the EMCLI command line tool.

As just noted, in a test master snapshot you use RMAN to take a backup of the data files, control files and archive logs to the test master storage volumes. These are then unmounted from the source host and mounted on the clone host, and a recovery operation performed using the source SID and the redo logs. The database is then renamed to a new SID using the nid executable. The new database is registered with the appropriate listener and registered as a target within Enterprise Manager.

There are a couple of requirements with this new functionality. Firstly, it use snapshots that allow a point-in-time copy of the active file system. For this reason, NetApp, Sun ZFS storage appliances and Solaris ZFS are supported, while EMC is not. Also, the source database must of course have ARCHIVELOGMODE enabled, and it needs to have block change tracking enabled.

Agile ‘Data Refresh’

In simple terms, the Test Master (or the Data Guard standby if you’re using that) is regularly refreshed with current data from Production. From the Test Master / Standby, you can make as many scheduled or manual storage snapshots or RMAN backups as you like. These are called “Profiles” and are indicated by the t0, t1, t2, … tN times in the diagram below:

clone6 300w, 768w, 250w, 150w" sizes="(max-width: 882px) 100vw, 882px" />

From each of these profiles, clones can be taken. Each user gets a personal read-write database clone, as the data was at the time the profile was created, and of course, can take as many private backups of their clone as they desire. The user also has the ability to refresh their clones to more recent profiles as they deem necessary. This is done in a self-service manner, so the database administrator does not need to be involved in the data refresh process.


So that’s what’s new in EM13c in the areas of data cloning and refresh. In my next post, I’ll share a screenwatch of the functionality in use, so stay tuned for more!

The post Advances in Data Clone and Refresh in EM13c appeared first on