What are the risks of the parameter PRESERVE_COMMIT_ORDER of Oracle’s DBMS_LOGSTDBY

oracleoracle-10g

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

Best Answer

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.

Related Question