A moment with DataPump

Recently we received 3 different requests from a client. The first was to create a new schema, as a backup of another schema, the second was a data-only refresh of the original schema, followed by another data-only refresh of a separate schema.

The plan was to take 2 exports, one from production to refresh UAT, and another from UAT of the 2 schemas to be replaced. Then, 3 separate imports of the backup schema and the refreshes.

Note: The backup of the original UAT schema is an absolute must! You should always create a backup of whatever you are changing in case things go pear-shaped, and you need to revert to the original schema. Even though we are creating a backup, we do not know what the client’s intentions are with this schema, and they may make changes that need to be reverted.

One of the many parameters you have at your disposal is the ‘remap_schema’ parameter. This allows you to import one schema as another schema, to essentially create a copy of that schema. Perfect for our scenario.

Another handy parameter is the parallelism parameter. This allows for multiple threads to work on different parts of the schema at the same time, speeding up the import and export processes. You stipulate how many streams using ‘parallel=X’ where X is the number of streams you want the dumpfile to be broken into. Parallelism allows for speed, but always be sensible regarding this parameter, as it’s relative to the number of CPU Cores the environment has access to. Using a value too high could end up tying up resources and slowing things down. This should be especially noted if this is a production environment.

For the data-only refresh aspect of the work, there is something worth noting. Importing millions of rows into a large table with indexes on probably would be quite time intensive (even with Parallelism incorporated), so instead I dropped the indexes (taking a copy of the DDL first of course), then imported the data before finally re-creating the indexes. This also has the added bonus of making the indexes more efficient and ‘tidy’.

The final task is to compare the source & target environments using the Schema Comparison tool bundled with Toad. This gave an overview of any differences so we could guarantee everything was the same. In this instance, there were some grants missing in UAT which were fixed by running the fix-up script provided and handed the environment over to the client for their testing.

There is a plethora of useful features when it comes to using Datapump, this is just an example of one scenario.

For further information on management of your data, please contact RDB Concepts.

Suki Bola

Contact Us