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,..,schemaNList 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.sqlThe 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=yThe FULL=Y parameter tells the import utility to import the entire contents of the dump file.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.