Oracle Standby Database Techniques for faster application of redo logs

Question

I am quite new with the concept of standby databases in Oracle and I am not really a DBA, so help me out.

I have been reading a bit on the concept of Oracle standby databases and I am pretty much interested in logical databases. Currently, we have a potential significant slowdown in our standby database during our system's weekly DB maintenance.

During this maintenance, millions of records (usually the oldest records) will be deleted from several tables that have a number of records ranging from tens of millions to hundreds of millions. Usually, these records will be deleted using a single delete statement with a where clause referring to the oldest records.

From my understanding of how logical standby databases apply redo logs (do correct me If I am wrong), redo logs are process to create transactions which will then be applied to the standby database. So, when batch deleting millions of records in the primary database, what will happen in the standby database is something like a sequential deletion of these millions of records, which in turn is quite slow.

This leads me to my question. Can you share some thoughts/ideas on how to speed up the application of redo logs from the primary database to the standby database to avoid the issue I have raised above? The solution could be a different standby database configuration or a change in schema structures or a change in the way records are deleted.

Note that our tables should not be partitioned and are well-indexed. Standby database should be logical to enable querying for report generation.

Thanks for sharing your thoughts.

Answer 1

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.