0

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.)

4

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.

2

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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