Gregory Williams is a long-time database administrator.
I firmly believe that most Oracle database shops should have both a Test and a Production database. Of course, this also means that you also need to keep them in sync by refreshing your Test data on a regular basis -- a process which begs for automation.
When I decided to automate refreshing my Test database with data from Production data, I started by googling the Internet and discovered that most of the information I found suggested doing a full export/import of the Production database into Test database, or creating a clone of the Production database and recreating the Test database. These procedures assumed that you have at least the same amount of space on your test server as you do on your production server. In my case, I have a lot less space on my test server then I have on my production server; therefore, neither one of these procedures will work for me. Thanks to Oracle's Data Pump technology, I can export only the data that I need from Production into my Test database. For instance, I have videos stored in my Production database, which takes up a lot space, which I do not need in my Test database. The other feature that Data Pump has that's not in the old export/import technology is that it lets you export from a remote server. This is the main reason that I can automate my refresh test data process.
A new Oracle technology that I used is DBMS_SCHEDULER which let me create scheduled chains and scheduled programs. These technologies are all put together and run within Oracle's database using PLSQL packages and a lot of "execute immediate" functions. In this article, I show how to automate the refreshing of Test database with data from their Production database using the following Oracle technologies:
- Data Pump
- API -- export
- API -- import
- DBMS_SCHEDULER
- scheduled chains
- scheduled programs
- scheduled jobs
- PLSQL
- Dynamic SQL (execute immediate)
In this article, I first list the steps involved, then explain each of the steps, and finally show how DBMS_SCHEDULER is used to execute each step as a chain.
The following steps automatically execute the Refresh Test Data procedure. All of these steps are accessed from within a PLSQL package -- SCHEMA_REFRESH -- except the last one which is run directly from the DBMS_SCHEDULER package. This PLSQL package is located in the Test database on the Test server:
- Export Prod Database
- Clear Schemas
- Import Prod Dump File
- Cleanup Test Database
- Send email
