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

Refreshing one 8.0.5 instance's data with another

I work on Oracle 8.0.5 (Enterprise edition) on a Windows NT system. There are two instances (TEST, PROD) of Oracle running on the same machine. I would like to refresh TEST instance's data (database files) with PROD instance's data as the TEST instance data seem to have gotten corrupted. I would like to know the steps/procedures to it. Thanks in advance.

Since you are using Oracle 8, probably the best recourse is to use the export/import utilities to move the data. I'm going to assume that you have two aliases (TEST and PROD) set up in your TNSNAMES.ORA file so that you can point to both instances. In that case, first export the schema owners from PROD:

exp80 userid=system@prod file=move.dmp log=move.log
List all of the schemas, or owners that you want to export.

Next, sign on to the TEST instance with SQL*Plus or Oracle Enterprise Manager. Drop all objects owned by these owners. If you are using SQL*Plus, you can generate a script to do this for you as follows:

spool drop_objects.sql
SELECT 'DROP '||object_type||'
FROM dba_objects
spool off
The first part dynamically generates a script to drop all objects. You then run this script. You actually run this script twice to take care of objects that can't be dropped the first time due to Foreign Key constraints. Now that all objects are dropped, just import the dump file you created in the first step:
imp80 userid=system@test file=move.dmp log=imp.log
The FULL=Y parameter tells the import utility to import the entire contents of the dump file.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.