Problem solve Get help with specific problems with your technologies, process and projects.

Replicating test database to production

We are on Oracle 9.2 and Sun 5.8. We have a database that has static data that changes once every two months or so... So, what my developers do every two months is load new data (sometimes old data is updated) into TEST database. Once they are done updating old data and adding new data, they tell me to update the production database with all the new changes they just made to TEST. How is that possible without bringing the production database down? How can TEST be replicated to the production database?

There are a few options available to you to move data. The biggest problem is that you want to leave the data in the production instance available to you until you have the new data moved to production. If I had to tackle this issue, I'd look at using the following method:

  1. Move the data from test to production with the Transportable Tablespace option. If you want to keep the same table name(s), then use different table owners. You can use other methods to move the data from test to production as well like export/import or copying the data across a database link.
  2. With one SQL statement, issue something like the following:
       DELETE FROM prod_user.tableX;
       INSERT INTO prod_user.tableX
          SELECT * FROM test_user.tableX;
    When you delete the data from prod_user.tableX, no one will be able to see that you've deleted the data until you COMMIT. Before you commit, you insert the new data. Once the COMMIT is complete, the data in the table is refreshed. Depending on the data volume, you might need a very large rollback segment to handle this sort of transaction. If that is the case, then set one up and make sure that your transaction uses that rollback segment for this operation.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.