I work on Oracle 8.0.5 (Enterprise edition) on a Windows NT system. There are two instances (TEST, PROD) of Oracle...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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
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
imp80 userid=system@test file=move.dmp log=imp.log full=y
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.