Chapter 8. Ease the Chaos with Test Data Management

There are many instances in production where databases have increased drastically in size, due to application growth and increased usage. These databases must nevertheless be used for testing purposes, and at such times the DBA would be called upon to make an appropriate copy of production for the test team.

The easiest way to make a test copy is to clone a database, and this is fine for small- to medium-sized databases where there is sufficient space allocated in the test system. However, for large databases it is usually not possible to use a similarly sized database for testing because of space issues in the test environment. In this case, the DBA is forced to use a reduced set of data in the test database, in order to meet the space requirements and reduce the storage costs.

But even this is not easy, because it requires an understanding of the data model before the reduced data can be extracted, and manual scripts need to be written...

Test Data Management

Enterprise Manager Cloud Control 12c provides a new licensed pack, known as the Test Data Management Pack. This is mainly used for the purpose of data subsetting, that is, to create a relationally intact subset of data from production.

For this purpose, an Application Data Model (ADM) needs to be created and stored in Enterprise Manager. This is a new concept in Cloud Control 12c. This model defines the database schema with all its relations. You can then define the subset of the data that is required from the model. At the same time, you can define the data-sensitive columns in the tables, such as a credit card number. This information will then be used to create the subset of data for testing, and also to mask sensitive data using the Data Masking Pack (as we will see in the next chapter).

Creating packages

A prerequisite to using the new facility of Test Data Management is to create special packages in each database that will be used for this scenario. These packages...

Data subsetting

The Test Data Management Pack allows the capability of data subsetting. This lets you subset your production databases to create smaller sets of data for test or development databases, based on the Application Data Model. The continual task of refreshing test or development databases is made easier, and storage space is also saved in the process. Let us have a look at how to achieve this. First, select Enterprise | Quality Management | Data Subset Definitions to create your Subset Definition instance. This brings up the following screen:

The workflow shown in the preceding screenshot is now at the Data Subset Definitions stage, which is the next step in the process. Click on Create.

Name the definition appropriately, then select the Application Data Model instance to be used.

The Source Database instance also needs to be selected, however there is only one choice—the saiprod database—since the data model was created from that database. Click on Continue.

On this page, the...

Generate subset

From the Actions menu, the next step is to select Generate Subset....

In the Generate Subset wizard, you can specify how the subset is to be created. You can either write the subset data to export files (this will use the Oracle Data Pump expdp facility), or you can delete the data directly from the target database.

If you select the latter, you will be asked to verify that you are deleting the data from a non-production database. You need to be very careful at this point and make sure you use the glass icon to select the target database. Since we have associated the saitest database with this data model, the saitest database also appears in the selection list, and you can select it for the data subsetting operation at this point.

However, we decide instead to create the subset by writing the data to export files. Use this option—it is faster and requires the least space. Select the saiprod database as the target database, and use Global Named Credential as the logon to that...

Benefits and capabilities

When you create a data subset, you can define subset criteria (such as a where condition), which will limit the amount of data that will be extracted for the subset. As a result, the storage requirements in the target database are reduced. This is one of the most important benefits of data subsetting, since it helps to reduce expensive storage costs.

You can select multiple applications, that is, schemas (in the case of multi-tenanted databases) for the subset. The definition of tables associated with these applications and the relationships between them will be extracted from the Application Data Model into the subset definition, along with additional metadata of the constraints and indexes. You can totally avoid the manual labor normally associated with collecting this information and writing appropriate scripts.

Table rules can be defined with where clauses and bind variables that limit the data that is extracted. Table statistics are then analyzed to get an estimate...

Summary

When we set up test systems, it is often the case that full production copies cannot be used due to the cost of storage, especially for large databases. In such cases, you would need to extract a relationally valid subset of data. Doing this manually is not an easy task, but it has to be done. Consequently, setting up test systems is often a slow, error-prone, manually-scripted process.

A lot of investigation is required to find the relationships in the database across various schemas; the rules for extraction need to be defined (keeping intact all the relationships), and even then you are not sure of the space requirements for the extracted subset.

The Test Data Management Pack allows you to reverse engineer an Application Data Model from your database, which is stored in the Enterprise Manager Cloud Control 12c repository. The metadata definition of tables and relationships from a single or multiple schemas can exist in a model, which can then be used to enable test data operations...