How to automate data copy from one Oracle schema to another

asked 2018-06-26 02:57:27 -0600

Leigh gravatar image


Our company develops an application which uses Oracle WebLogic and Fusion Middleware with an Oracle Database on the backend. We often have customers asking us to refresh their database data from one database to another, i.e. a common scenario is to copy Production data to a Test schema within a different database.

We are really keen to investigate whether it would be possible to automate this data refresh process using Puppet. At the moment our process to refresh the data is very manual and requires a number of destructive steps to complete. For example in the target database we currently drop and recreate the database schema and then using datapump we export the data from the source database and import the same back into the target schema. Once all of the database objects and data is imported into the target database we then run a custom built SQL script to modify all of the application configuration within the target schema so that references are correct, for example any references to "Prod" are changed to "Test".

For larger customer databases this process can take hours to complete and require staff to keep an eye out on how the work is processing and at different points initiating and monitoring manual steps in the process. Ideally what we want to be able to move towards is a fully automated process whereby puppet could be used to copy the data from source to target and then make any necessary tidy up and configuration changes within the target automatically. We could then schedule refreshes at any time (even overnight) knowing that the task will complete successfully with minimal human intervention throughout the process.

We are very new to puppet and similar automation technologies so keen to know if anyone has any advice on where to start looking to build an automated process for the above (assuming Puppet would be able to do this).

Thanks in advance.


Leigh Elliott

Head of Customer IT


edit retag flag offensive close merge delete