Oracle selective redo log shipping

I am not very proficient with this topic but I think there are the following options, the first three of them need extra licenses. You should to check if my recommendations contradict your DR requirements.

The problem of of the logical dataguard is that one SQL statement on the primary server can cause a lot of SQL statement on the standby server.

Example: the statement

delete from mytable where entry_date <=to_date('2014-01-01','YYYY-MM-DD') 

may cause the deletion of 1 000 000 rows in the primary database in a more or less efficient way. This statements causes the execution of 1 000 000 statements on the standby side of the form

delete from mytable where id=:id

where id is the primary key field of mytable. This kind of deletion is the most imperformant way to delete rows.

If one uses s physical dataguard the situation is much better: the redo records transmitted to the standby contains the change records that must be written to the datablock and the standby applies these changes. This is a lot of less activity on the standby side.

You should run the dataguard in an asynchronous mode (maximum performance mode) so that the SQL-execution on the standby side does not influence the performance on the primary site. There is a parameter in sql_apply, PRESERVE_COMMIT_ORDER, which is TRUE by default and can be set to FALSE to simplify log apply.

1. Buy additional licenses:

   1. Use partitioning and truncate partitions instead of deleting rows. If if is possible to organize your database in such a way then this is a very good solution because the truncation of a partition does generate almost no redo information. On the standby side the truncate command will be executed in the same way as on the primary side. An additional license is necessaary to use partitioning.

   2. use physical standby with active datafguard. Active dataguard allows running the database in read only mode while redos are applied. a physical standby is more performant as a logical one as explained above. An additional license is necessary to use active dataguard.

   3. You can combine 1.1) and 1.2). This will be the most performant way.

2. create a solution using physical standby If you don't want to bou additional licenses you can tinker around with your own solution.

   1. mimic active dataguard: mix "open read only / apply redo" phases with your physical dataguard: snapshot database

      • apply the redo to your standby database
      • stop redo apply
      • open it as snapshot database
      • do your reporting
      • close your database
      • convert back to physical standby and continue with a)

   2. you can improve solution 2.1): before step "apply the redo to your standby database" that you have gathered during the reporting phase you can make an incremental backup (with respect of the scn of the restore point) from the primary and apply this to the standby site this may save you a lot of redo apply.

3. improve your sql apply solution I assume that you have one table that is heavily affected by your batch job. I call it mytable.

   1. before you start the batch process exclude mytable from the sql apply process. after your job has finished reinstantiate the table and include it in sql apply again. This makes sense if the percentage of deleted rows in the table is rather high.
   2. exclude mytable from sql apply and replicate the data of mytable to the primary database by other methods. This replication method should handle the deletes of the batch job in a suitable way. One possibility maybe Advanced Replication, especally procedureal replication. Another may be oracle Streams.

I suppose you could write the information directly to the redo log at each modification, but that would mean a lot of very small 'write' operations on disk.

If you cache this instead you can at least wait the next 'COMMIT' and do less 'writes'. In my experience this buffer does not need to be very large because of this, on a typical system COMMITs will come quite often, and there are other rules to flush as well...

So to answer the question: For performance reasons!