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:
- Snap Clone on Exadata
- Using Snap Clone on EMC storage with ASM
- Will the REAL Snap Clone functionality please stand up?
- An Introduction to Snap Clone and the Issues It Addresses
- Data Subsetting
- Data Cloning and Refresh
- 18.104.22.168 Cloud / DBaaS Features
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:
- Clone to Cloud – If you are looking at moving a project to use Oracle Cloud, the first step is to clone the database to the cloud.
- Clone within Cloud – Once cloning to the cloud is done, each team member needs their own copy in the cloud, so of course you would need to clone the database within your cloud environment.
- Clone from Cloud – Once the development is done using cloud resources, you need to bring back all the artifacts to on-premises (assuming your Production environment is still within the secure perimeter of your data center).
- 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.
- 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.
- 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).
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.
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 22.214.171.124. 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).
- 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 126.96.36.199 ORACLE_HOME, while your cloned environment was using 188.8.131.52.2. 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.
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. 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:
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!