1641 - How Syniti Data Replication/DBMoto Uses Oracle Log Miner and Redo Logs

Syniti DR/DBMoto offers two main approaches to transactional replications from Oracle databases -- Log Reader and Log Server Agent. This article applies to replications set up using the Log Reader. Some of the issues described here can be avoided by using the Log Server Agent approach to replication where reading Redo logs/Archived logs is managed automatically. 

During mirroring or synchronization from an Oracle database using Log Reader, Syniti DR/DBMoto reads Oracle transactions through the Oracle LogMiner to identify changes in the source database recorded in Oracle redo logs. All active redo logs are opened at each mirroring session to retrieve transactions to replicate. Additionally, Syniti DR/DBMoto provides a setting to load archived logs.

Oracle logs its transactions in circular fashion, that is, it uses the redo log files in sequence. When the current file reaches its maximum size, Oracle will set its status to inactive and will keep writing to the next file. If the next file already contains data, it could be overwritten. If all online log files have changed sequence number (i.e. the writing sequence of each specific log file) in the period between one Syniti DR/DBMoto read and the next (Read Interval setting in Syniti DR/DBMoto), the log files may be underdimensioned, considering the amount of transactions to log, or there may be too few log files.

As a general guideline, the size of the log should be based on the number of transactions generated in the database and based on the retention time. For example, if you want to be sure you can recover for 72 hours, you should define your logs to contain all the transactions that can be generated in 3 days. Oracle log files can be re-dimensioned by either increasing the log file size or by adding new log files. You may want to consider creating more medium-size log files rather than increasing log file size to a value that could make it difficult to handle (i.e. not greater than 100MB.) If you organize your log files in this way, and if you keep Syniti DR/DBMoto constantly running and aligned with the replications you may be able to avoid opening the archived logs.

Note that for Oracle. 10g and above, there is a Redo Log Sizing Advisor tool that can be helpful to determine the optimal redo log size. http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin.htm. A good introduction to this feature can be found at http://www.dba-oracle.com/oracle_tips_sizing_advisor.htm. 
It works by analyzing the number of switches between one redo log and another (checkpoints), too much checkpoint activity can reduce overall database performance. If you don’t want to use the Log Sizing Advisor, the Oracle 10g documentation suggests: It may not always be possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable. Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs at most once every twenty minutes.
Contact your Oracle database administrator for more information. Your DBA has the best understanding of your Oracle environment.

If you allow the log files to fill up and begin overwriting, Syniti DR/DBMoto may not be able to locate the next transaction to process. If this occurs, you need to perform a full refresh replication before resuming the mirroring.

When Syniti DR/DBMoto reads the active redo logs, the LogMiner actually generates a view when it opens the log (it does not open a physical table or the log itself.) This does not affect Oracle operations, but it can affect Oracle performance in general, because it is making a demand on the server (especially in the initial phase of the mirroring sessions, when the log miner prepares the view to open).

When creating a source connection for an Oracle database in Syniti DR/DBMoto, nothing is installed on the Oracle server. However, Syniti DR/DBMoto requires that SUPPLEMENTAL LOGS for identification key logging are enabled and this is performed automatically during the Install operation from the Syniti DR/DBMoto connection dialog. Supplemental logs add more detailed logging information to the Oracle logs but at the same time can degrade the server performance by requiring additional data to be written into the redo logs for each DML command.

Log cleanup operations by your Oracle DBA should be performed at scheduled times when replications are idle. If, however, a log file is removed while a replication is in progress, Syniti DR/DBMoto should be able to recover from the error and pick up replication during the next mirroring session with no data loss.

Including Archived Redo Logs when Accessing Logs to Identify New Transactions

Check the Read Archived Logs option in the Setup Info dialog available from the Connection Properties dialog of the source connection. This approach is useful only if your Oracle database uses ARCHIVELOG mode. It enables Syniti DR/DBMoto to search archived redo logs if transactions cannot be found in the online redo logs.

1. Disable the replication by selecting it in the Metadata Explorer (Management Center), and clicking the Enable Replication menu item to remove the checkmark.
2. Select the source connection in the Metadata Explorer.
3. From the right mouse button menu, choose Connection Properties.
4. In the Connection Properties dialog, scroll to the Transactional Support section.
5. Click on the value for the Transaction Log Type field.
6. Click the ellipsis button.
7. In the Setup Info dialog, click  to open the Change Log Settings dialog.
8. Check the option Read Archived Logs.
9. Click OK to close the Change Log Settings dialog.
10. Click OK to close the Setup Info dialog.
11. Click OK to close the Connection Properties dialog.
12. Enable the replication by selecting it in the Metadata Explorer, and clicking the Enable Replication menu item so that it displays a checkmark.