4

We have the following setting, with Oracle 10g:

  • One primary database
  • One logical standby database with setup with data guard. The logical standby is only used for generating reports and alike.

Currently the parameter PRESERVE_COMMIT_ORDER of DBMS_LOGSTDBY is set to true. In order to enhance the speed of data replication, we are thinking of setting it to false. But what are the risks, if any, of doing so? (Oracle says: "See the Usage Notes for details and recommendations." ... but I couldn't find these recommendations.)

|improve this question
1

I found the following white paper: SQL Apply Best Practices: Oracle Data Guard 11g Release 1 that brings a lot of enlightement. On page 12 is says

If you are using the SQL Apply database only for disaster-recovery purposes or if the reporting application using the standby database can accommodate transactions being applied out of sequence, then set the PRESERVE_COMMIT_ORDER parameter to FALSE. When SQL Apply is running in this model, transactions that are unrelated to each other may be applied out of sequence.

there are some examples that illustrates the effects (logical and performance related) of setting this parameter to false.

Most reporting application are reporting about data that was processed some time ago (yesterday, the last month) it seems to be no problem to et this parameter to true

The most performant way to run a standby is to run it as a physical standby with the limitation that the database is a read only 1:1 copy of the primary database.

|improve this answer
-1

The risk is severe. The risk is that the standby database would hold different data than primary database. Both would be self-consistent, but may be different.

This parameter means to it is not necessarily for the multiple SQL Apply sessions to commit in the same order. Consider this example:

TransactionA: UPDATE employee SET salary=1000;
TransactionA: COMMIT;
TransactionB: UPDATE employee SET salary=1001;
TransactionB: COMMIT;
SELECT salary FROM employee;

I think the order of commits is relevant.

|improve this answer
  • 1
    Tho Oracle documentation states for PRESERVE_COMMIT_ORDER with value FALSE "Transactions containing non-overlapping sets of rows may be committed in a different order than they were committed on the primary database.". In your example the sets of rows are overlapping, so they whould be committed in the original order. – mr_georg Jun 22 '12 at 7:21
  • Ups. I didn't know that :) – kubanczyk Jun 22 '12 at 16:00
  • -1 because the answer is wrong – miracle173 Jul 23 '12 at 6:17

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.