Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an Oracle database that runs a 6 hours batch job every day. This process slows down performance during the 6 hours timeframe.

Is there any method that I could use to build another server that runs the batch job, and once is done, sync the data to the production server? (The time taken must be shorter than 6 hours.)

share|improve this question

3 Answers 3

You could use database link to transfer data between servers.

Assuming you already have the link set up, replicating data can be as simple as:

insert into target_table
select * from source_table@link_name;
commit;

Of course this will be more involved if the data volume is large or you have complex schema.

share|improve this answer

Maybe yes, you can use STREAMS technology in a bi-directional configuration but probably will not solve the problem. STREAMS capture all DDL/DML and replicate them on another DB. This means that in case of heavy load both DB might experience the same load.

Another approach is to consider RAC solutions but you should tackle another ton of questions from cost of licenses to application optimization for RAC. Furthermore, oracle 9i RAC is really far from actual RAC solutions.

You may consider the standby database solution to offload all enquiry on the standby database but again some consideration between performance and costs should be done. If you upgrade to Oracle 11g you can use stanby database realtime query that is much faster than a logical standby solution available in the 9i.

The first solution in any case is to try to optimize the actual batch chain.

share|improve this answer

You could also create batch/cron jobs that run the Data Pump utility by exporting (expdp), moving the dump files to your target database, and importing (impdp) the data.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.