Q
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
owners=schema1,schema2,..,schemaN
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||'
'||owner||'.'||object_name||';'
FROM dba_objects
WHERE owner IN ('SCHEMA1','SCHEMA2',...,'SCHEMAN');
spool off
@drop_objects.sql
@drop_objects.sql
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
full=y
The FULL=Y parameter tells the import utility to import the entire contents of the dump file.

For More Information


This was last published in March 2003

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close