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.