A Use Case: Refreshing Test Data

This appendix demonstrates how Enterprise Manager can be used to enable application developers and testers to quickly and easily provision secure, storage-efficient database clones on-demand, whenever updates are made to production data.

This appendix contains the following sections:

A.1 The Challenge of Refreshing Test Data

IT environments typically require multiple copies of production databases for application development and testing purposes. However, provisioning a full clone of production data is a time-consuming process that not only involves coordination among database administrators, systems administrators and storage administrators, but also requires significant storage space.

When an application developer or tester needs a database clone, he or she must typically go through an approval cycle, which then initiates a cumbersome and time-consuming cloning process that may span days. Clones are shared by multiple users and applications, which results in degraded performance due to increased sharing by multiple users. In such an environment, refreshing test data to reflect changes made to a production database is typically done on a fixed schedule, and may not be done as often as needed. As a result, it may often not be feasible to ensure that developers and testers are testing with the latest data.

A.2 The Enterprise Manager Solution

The following sections provide a solution to the previously noted challenges that leverages the features of Enterprise Manager. The goal is to enable developers and testers to provision secure, reduced-size copies of production databases on-demand, whenever required due to production database updates.

Note that this solution requires that the enterprise already has the Database as a Service (DBaaS) Cloud feature enabled. See Chapter 12, "Enabling the DBaaS Cloud" for instructions on enabling DBaaS.

The basic flow of this example scenario is illustrated below:

Figure A-1 The Enterprise Manager Solution

The Enterprise Manager Solution

A.3 Create the Test Master Database

This scenario assumes that an administrator-level role will create a "test master database" - a copy of the production database that will be used for application testing. Oracle offers various options for creating a test master database, but this scenario assumes that the administrator will use RMAN backup and RMAN restore operations to create a backup of the production database, then restore it as the test master.

The expectation is that RMAN backups will be used to refresh the test master database with current data from the production database at regularly scheduled intervals in a discrete manner.

A.4 Mask and Subset Data on the Test Master Database

To enable production data to be utilized in testing, data identified as sensitive should be masked - or scrambled - on the test master database. Ideally data subsetting should also be used to reduce the database size to include only the data that is needed for testing.

For example, assume a company is developing a human resources application, and needs to test it using employee Social Security and salary data. This sensitive data could be masked/scrambled, while at the same time extraneous data such as employee location could be excluded from the data subset actually needed for testing.

Oracle's inline masking and subsetting feature makes it possible to mask sensitive data and simultaneously reduce the size of the database, all within the same task flow. This serves the dual purpose of protecting sensitive production data while greatly reducing hardware costs related to storing large masked production databases for testing.

A.5 Create Database Clones On-Demand

After a process is in place to create and refresh the test master and protect and subset the data to be used in testing, application developers and testers - also called self service users in DBaaS parlance - can quickly and easily create clones of the test master database using Enterprise Manager's Self Service Portal, the primary user interface for Database as a Service.

This functionality is enabled through Snap Clone, a feature provided with Database as a Service that enables the creation of fully functional copies of Oracle databases with minimal storage requirements. With Snap Clone, users can create test master clones containing terabytes of data in just minutes instead of hours, while keeping storage requirements to a minimum.

A.6 Implementing the Example Scenario

This section outlines the tasks involved in implementing the test data refresh solution outlined in the previous section.

A.6.1 Assign and Create Roles in Enterprise Manager

Role: Super Administrator

Roles are used to manage system and object privileges within Enterprise Manager. Roles must be assigned to both administrators and self service users.

A.6.1.1 Assign Administrator Roles to Administrators

Assign the following administrator roles to users who will perform various DBaaS setup tasks within this scenario. These roles are provided out of the box within Enterprise Manager.

  • EM_SSA_ADMINISTRATOR: Users with this role can define quotas and constraints for the self service users who will create clones.

  • EM_STORAGE_ADMINISTRATOR: Users with this role will have privileges to register storage hardware required to use Snap Clone.

See Section 3.3, "Defining Roles and Assigning Users" for instructions on completing this task.

A.6.1.2 Create Custom Roles for Self Service Users

Custom self service user roles must be created for users who will use the Self Service Portal to create clones of the test master database. Typically you will create roles for each of the different functional groups involved in testing, such as application developers and functional testers.

All roles will be based on the EM_SSA_USER role provided with Enterprise Manager.

See Section 3.3.1, "Creating Custom Roles for Self Service Application Administrators and Users" for instructions on completing this task.

A.6.1.3 Create Self Service Users

After the various roles are created, create self-service user accounts for each individual tester or developer who will create clones. Each user will be assigned one of the custom roles defined in the previous task.

See Section 3.3.2, "Creating a User and Assigning Roles" for instructions on completing this task.

A.6.2 Create a Backup of the Production Database

Role: DBA

Use an Oracle RMAN backup operation to create a backup of the production database. This backup will then be restored to create the test master database from which clones will be created.

Although several options are available for creating a test master database, RMAN backup and restore is used because the backup database created can be masked and subsetted.

See the following documentation for instructions on creating the database backup:

http://www.oracle.com/pls/topic/lookup?ctx=em121&id=EMLCM93231

A.6.3 Prepare to Mask and Subset Data

Role: DBA and Security Analyst

The following steps must be completed to prepare for masking and subsetting data.

A.6.3.1 Plan the Masking and Subsetting Strategy

Before beginning, determine what data must be protected, what data is needed for testing, and how to best implement masking and subsetting.

  • Identify the sensitive data that must be masked.

  • Locate the tables and columns containing the sensitive data. The Data Discovery and Modeling feature in Oracle Enterprise Manager facilitates such data discovery using pattern search criteria.

  • Determine which masking algorithms match the enterprise's needs. Can the Oracle-supplied formats for masking common types of sensitive database be used, or will custom formats need to be created?

  • Determine how to best subset the data, such as deciding which tables are not needed and can be excluded.

A.6.3.2 Create the Application Data Model (ADM)

Both data subsetting and data masking depend on an Application Data Model (ADM). An ADM is a knowledge base within Enterprise Manager that captures and stores application metadata, such as referential relationships and sensitive columns within an application.

See the following documentation for instructions on creating the ADM:

http://www.oracle.com/pls/topic/lookup?ctx=em121&id=RATUG4045

A.6.3.3 Create the Masking Definition

Create the data masking definition using the Enterprise Manager user interface to apply the masking formats to the sensitive columns. Oracle supplies an extensive library of masking formats that can be used out of the box or modified as needed. Custom masking formats can also be created.

See the following documentation for instructions on completing this task:

http://www.oracle.com/pls/topic/lookup?ctx=em121&id=RATUG04000

A.6.4 Configure the Storage Server

Role: Storage Administrator

The Snap Clone feature requires adequate storage for the database clones that will be created. In addition to providing native storage support, Oracle also supports a software approach to Snap Clone using the Oracle Sun ZFS file system.

This scenario assumes that Oracle Sun ZFS file system storage will be utilized. The basic flow is to first configure the ZFS file system, then register the storage in Enterprise Manager. Note that as a first step, storage must be acquired and allocated to the ZFS storage server.

The key steps are:

  • Configure or create a user who can administer the storage from Enterprise Manager.

  • Configure the ZFS pool that is used to host the storage volumes, and grant privileges to the pool.

  • Allocate quota to the ZFS pool and assigns permissions to the user.

  • Register the ZFS file system with Enterprise Manager by specifying the user name, protocol, and storage credentials.

  • Create the storage volumes

See Section 12.7.3.1, "Configuring Storage Servers" for instructions on completing this task.

A.6.5 Create the Test Master Database on the Storage Volume

The test master database must be created on the storage volume on which the snap clone copies will be created. The test master can be created from a snapshot or from an RMAN Backup profile taken at a prior point in time and refreshed at specific intervals.

See Section 20.5.6, "Creating a Discretely Synchronized Test Master" for instructions on completing this task.

A.6.6 Enable the Test Master Database to Use Snap Clone

Once the test master database is created, it must be enabled for snap clone.

  • Return to the Storage Registration page in Enterprise Manager

  • After the test master database configuration has been refreshed, the target is automatically detected and visible on the Storage Registration page. Select the test master database, then select the Enable Snap Clone option at the top of the page.

    The test master database is now Snap Clone-ready. For more details, see Section 20.4.7, "Enabling the Test Master for Snap Clone"

A.6.7 Mask and Subset the Test Data

Role: Self Service Administrator

Once the test master is ready, the data can be masked and subsetted according to the rules defined.

When a change is made to the production database, the test master database must be refreshed. It may be necessary to revisit the masking and subsetting rules to reflect changes to the production database. A new subset definition can then be applied to the test master database.

A.6.7.1 Create the Subset Definition

Data masking and subsetting can be performed simultaneously by creating a subset definition through the Enterprise Manager user interface. The basic flow is:

  • Select the test master as the source database.

  • Define table rules to filter the data to include in the subset. Perform a space estimates action to determine how much the size of the database has been reduced. This can be an iterative process as you fine-tune the rules to resize the database subset.

  • Reference the masking definition you created in Section A.6.3.3, "Create the Masking Definition".

  • Optionally, use column rules to mask additional columns, typically vertical columns such as CLOB and BLOB columns. This can also aid in reducing the subset size.

See the following documentation for instructions on completing this task:

http://www.oracle.com/pls/topic/lookup?ctx=em121&id=RATUG4053

A.6.7.2 Save and Execute the Subset Script

Once the subset definition is created, apply it to the test master database. The key steps are:

  • Save and subset the definition as a ZIP archive.

  • Extract the archive on the test master database host.

  • Execute the subset script against the test master database to apply the mask and subset the data.

See the following documentation for instructions on completing this task:

http://www.oracle.com/pls/topic/lookup?ctx=em121&id=RATUG4214

A.6.8 Configure the Database Cloud Self Service Portal

Role: Self Service Administrator

After the storage has been configured and the test master database has been set up, the self service portal, which will be used by self service users to create test master clones must be configured.

The first step is to create a Platform as a Service (PaaS) infrastructure zone. The PaaS zone defines placement policy constraints for a set of targets (database hosts) that clones will be created on, and the users (based on roles) who can access these targets. For example, you could create a PaaS zone for a group of developers (assigned the APP_DEV role) and give them access to a set of specific hosts, and another for a group of testers, giving them access to a different set of hosts.

Hosts within each zone are then grouped into pools, depending on the operating system and the type and version of Oracle database installed on each host. For example, one pool could be created for all hosts configured with single instance Oracle Database 12.1.01 on Linux, another for Oracle RAC on Solaris, and so on.

To enable users to create clones of the test master database using the Snap Clone feature, you must create a provisioning profile for the test master database. Clones created by users will be based on this profile.

Finally, a "Snap Clone service template" is created to encapsulate the above configuration. When self service users create a clone, they will select this template which defines the host and database on which the clones will be created.

A.6.8.1 Create the PaaS Infrastructure Zone

Create the PaaS infrastructure zone containing the hosts onto which test master clones will be deployed.

  • Add one or more database host targets to the zone.

  • Specify the placement policy constraints for each host.

  • Select the self service user roles who can access this zone.

See Section 11.2.2.1, "Creating a PaaS Infrastructure Zone" for instructions on completing this task.

A.6.8.2 Create Database Pools

Within the PaaS infrastructure zone, create one or more database pools, one for each database type.

  • Specify the host credentials required for Snap Clone.

  • Select the PaaS infrastructure zone into which the database pool is to be created.

  • Specify the configuration (single instance or cluster), platform and version of the database being deployed.

  • Specify the number of database instances that can be running on each host within the pool.

See Section 11.2.2.2.1, "Creating a Database Pool" for instructions on completing this task.

A.6.8.3 Configure Request Settings

Configure the database request settings by specifying when a request can be made, its duration, and so on. To configure the request settings, you must specify the following:

  • Future Reservation Length

  • Maximum Archive Retention Period

  • Default Retirement Period

See Section 11.2.3, "Configuring Request Settings" for instructions on completing this task.

A.6.8.4 Set Up User Quotas

Quotas control the amount of DBaaS resources that are available to each self service user. To assign quotas:

  • Select the self service user role to which the quota is to be assigned.

  • Specify the total memory and storage that can be assigned to all databases created by the self service user.

  • Specify the number of databases that can be requested by the user.

See Section 11.2.4, "Setting Up Quotas"for instructions on completing this task.

A.6.8.5 Create a Database Provisioning Profile Using Snapshots

Create the provisioning profile for the test master database. The service template that will be created in the next step will use this profile.

  • Select the test master database as the database you want to create a profile for. The database must be enabled for Snap Clone as described in Section A.6.6, "Enable the Test Master Database to Use Snap Clone".

  • Specify the Named Credentials that will be required to access the database.

  • Enter a unique name for the profile, for example "Snap Clone Profile".

See Section 20.4.8, "Creating a Database Provisioning Profile Using Snapshots" for instructions on completing this task.

A.6.8.6 Create the Service Template

Finally, create the service template that testers and developers will use to create clones of the test master database.

  • Select the "Snap Clone Profile" created in the previous step as the profile to base the template on.

  • Specify the database definition details such as database type (single instance or RAC), database SID, and domain name.

  • Select the PaaS infrastructure zone and the database pool. Database clones created using this service template will be deployed onto this pool. The host target is populated in the Reference Host field.

  • Configure the storage volumes that will be used by the database clones. Specify the prefix for the new mount point of the database being deployed and the amount of space required for block changes.

  • Specify the number of snapshots that the self service user can take on the cloned database.

  • Specify the snapshot policy and the credentials for the system schemas of the clones.

  • Configure the initialization parameters that can affect the operation of the new database.

  • Select the self service user roles that will have access to this service template.

See Section 20.4.9, "Creating Service Template Using Snap Clone Profile" for instructions on completing this task.

A.6.9 Create Database Clones to Use in Testing On-Demand

Role: Self Service Users

At this point, the Database Cloud Self Service Portal interface provided with Enterprise Manager Cloud Control can now be used by testers and developers to create thin clones of the test master database whenever needed. The essential steps self service users will follow are:

  • Select the test master service template as the template to use.

  • Select the PaaS infrastructure zone onto which the clone is to be deployed

See Section 22.2, "Requesting a Database" for instructions on completing this task.

A.7 Ongoing Refresh of Test Data

Once the infrastructure to support the refreshing of test data has been set up, a strategy for regularly refreshing the data to reflect updates made to the production data must be put in place. The following steps should be repeated each time a data refresh is needed.