SharePlex 9.2.6 - Installation and Setup for Oracle Source

Network factors can cause data to transfer to a target system too slowly, or not at all.

Excessive chaining reduces the performance of the source database because Oracle must read multiple blocks for a single row. Excessive chaining reduces Post performance on the target system because block fragmentation in the database decreases the speed at which Post can apply SQL statements.

Capture reads archive logs if the redo logs wrap before Capture is finished with them, but this can slow down replication and consume disk space. In most cases, the redo logs should be configured so that SharePlex can avoid using the archive logs.

If Capture, Read, or both slow down during hot backups or other Oracle-intensive operations, view the process-priority settings on the system to determine if SharePlex can be assigned more resources.

If PK/UK logging is not enabled for the tables in replication, SharePlex must query the database to get the key values for update and delete operations.

When Capture detects a log wrap and the archive logs are not available, Capture stops and returns a “Log wrap detected” error. It waits a certain amount of time and then starts again, continuing this process until the logs are restored.

Capture cannot read compressed archive logs.

Capture stops if it cannot locate the archive logs it needs. When the redo logs wrap, Capture looks for the archives in Oracle’s archive-log list, and if the logs are not there, Capture looks in the location specified by the SP_OCT_ARCH_LOC parameter.

Capture stops when it cannot read or find the logs, then it tries to read the log again.

If a target table does not have a key, Post must perform a full table scan to find the correct row. This slows the Post process.

Bitmap indexes are beneficial for queries, but they slow down the DML operations applied by Post.

Disk I/O bottlenecks on the target system are a common cause of slow Post performance. Post can spend a high percentage of its time waiting for Oracle to commit data. The effect is worse when there is a Capture process reading from the same log device in peer-to-peer replication.

High number of buffer gets

Review any table with SQL statements that cause a high number of buffer gets. There should only be two to four buffer gets on an index, depending on its size and whether or not it is a unique index.

By default, one buffer writer writes all dirty Oracle blocks to disk. Whenever that buffer writer process wakes up to write, it locks portions of shared memory and, in effect, blocks the processes that are either modifying or reading data blocks — including the Post process.

Normally, Post performs an internal read/release after it receives each COMMIT, which means it purges that data from the queue as part of the checkpoint recovery process. For smaller transactions, this can cause excessive I/O on the target system and hinder the Post process.

If the sequences in replication are not cached, they add unnecessarily to the replication volume.

An error similar to the following means that the system file descriptors setting on the target system needs to be increased:

Error 07-24-08 12:11:40.360226 8693 12345 Poster error: /var/quest/ vardir/log/event_log: Too many open files (posting from ora102, queue prodsys, to ora10b)

The repair command locks all rows that need repair, or the entire table if there are more than 1000 out-of-sync rows, throughout the repair process. This could block Post if Post tries to apply data to the table being repaired.

Difference in Capture and Post speed

SharePlex reads and processes records from the redo logs faster than it can post those operations to the target database with standard SQL statements.

Post stops for database errors that can be corrected, so that you can fix the problem without risk of the data going out of synchronization. The errors and the faulty SQL statement are logged to the databaseID_errlog.sql file in the log sub-directory of the variable-data directory on the target system.

Some database errors, such as out-of-sync conditions, cannot be corrected. In that case, SharePlex reports the error to the Event Log, writes the error and SQL statement to the databaseID_errlog.sql file, and continues processing. Sometimes, errors that cannot be corrected cause Post to stop.

If a target table is locked, the Post process cannot apply a SQL statement and generates an error message. This message could mean that a user, application, or job is accessing the table and might have caused an out-of-sync condition. Or, for an Oracle target, it could mean that a repair command has locked the table.

When you are using named export queues, and you deactivate the configuration, Post could stop with the following error instead of posting the remaining data from the queues:

sp_opst_mt (for o.qa920-o.qa920 queue q5) 15007 - Can't open poster queue que_NOEXIST: Queue does not exist.

Post stops if a queue name is too long.

For an Oracle source, tables created after activation are automatically added to replication.

If you see the following error, Post has exceeded the available open cursors.

Post requires a certain number of open cursors to the target database.

If a source table is corrupted or there is another reason that you do not want the replicated data to be posted to the target database, you can prevent posting for that table without removing it from the active configuration or affecting posting for other objects.

Warning: Running out of cursors. Number of cursors opened so far is number. Will attempt to decrease SQL Cache size.

or...

Notice: Shrinking SQL Cache size to number per session.

15051 – missing column in the hint file (either table of index name)

15052 – syntax error for tablename

15053 – syntax error for indexname

The local hosts file is not configured properly.

SharePlex cannot find a parameter that it needs to reference. You have an older version of the param-defaults file than the version of SharePlex that you are running. Someone might have updated the SharePlex binaries using a downloaded patch, and may not have installed the latest param-default file.