I have Oracle 10gR2 installed on Redhat Linux 5.4 on two servers (primary and physical standby).
When I import data into the primary server while the standby is started it takes too long to complete (didn't wait for it to finish). When I shutdown Oracle on the standby server it completes in 15 minutes.
Is this really due to standby? How can I speed up this slow imp? Here is the command line used:
imp userid=/ file=base.dmp ignore=yes log=log1.log
Best Answer
It depends on the configuration you have in place.
Oracle uses log shipping (as default) to feed the transactions into the standby database. In order to make sure that ALL transactions are recovered on the standby database, we should make sure that the primary database is running in FORCE LOGGING mode. Switching FORCE LOGGING may cause a difference in performance on the primary database, where FORCE LOGGING mode would be a little slower than without force logging mode.
Just shutting down could - depending on the configuration - bring the primary database to a halt (protected standby database). When the primary and standby databases are on different hardware they should hardly influence each other, other than that the transactions need to be saved and transported to the standby site. If you run in sync mode the primary could be slowed more than when running a-sync which is the default.
The configuration that makes sure that the performance impact on the primary is the least, is the max performance mode, in which the transactions are only shipped when the redo logfile is full. It also gives the least data protection.