Updating a test database without disrupting production?

346360 pts.
Tags:
Oracle 9i
Oracle Database
Production environments
We have an Oracle 9i (9.2.0.3) database and we need to update my test database many times a day. What is the best manner to update my test database without stopping my production database?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could do a couple of different methods. Both of which can be done while the production database is running.

1. You could set up a script file that would truncate the tables in the test environment and then load the tables from the production environment. (select * in the example assumes the tables are exactly alike in structure)

connect test/pwd@test (include @test if your test db is in a different instance- you would need to set up database links)

Truncate table test.table1;
Truncate table test.table2;
Insert into test.table1 select * from prod.table1@prod; (include @prod if your test and production dbs are in different instances)
Insert into test.table2 select * from prod.table2@prod;

2. Another option is to export the data from the production database with the export (EXP) program Then import (IMP) into your test environment. You would still need to either drop the tables in the test environment or truncate them. Use EXP help=y and IMP help=y to view the parameters that can be specified.

With EXP, you can specify the user to export and/or you could export specific tables.
Example parameters for EXP will export all of the schema PROD objects:

FILE=C:\oraback.dmp
LOG=C:\oraexp.log
owner=PROD

With IMP, if your schema is different from your production to your test dbs, you can use the FROMUSER and TOUSER parameters. Example IMP parameters:

FILE=C:\oraback.dmp
LOG=C:\oraimp.log
FROMUSER=PROD —> specify FROMUSER / TOUSER if the schemas are different
TOUSER=TEST
tables=(table1, table2, table3) —> you can specify specific tables if you don’t want all
GRANTS=n
INDEXES=y
ROWS=y
CONSTRAINTS=y
COMMIT=y

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following