At times a source database may be accessed by multiple instances of Shareplex. This solution delves on how to ignore a transaction for multiple instances of Shareplex.
General information.
There are two ways to deal with this and each has its own pros and cons.
A. Using the SHAREPLEX_IGNORE_TRANS feature:
It is possible to have the same transaction ignored by multiple instances of Shareplex. Please refer to Shareplex Administrator Guide chapter titled “Shareplex Utilities section titled “Preventing DML transactions from replicating with
create_ignore.sql” for details. The procedure described in the Guide is for ignoring transaction for one Shareplex instance. The following is an extension to that procedure:
It so happens that when one executes the procedure SHAREPLEX_IGNORE_TRANS, it is done for a particular schema. This is what one can do to execute it for multiple schema:
1. Within the SQL window executing the transaction, execute the procedure SHAREPLEX_IGNORE_TRANS for each of the Shareplex schema for the Shareplex instance which needs to ignore this transaction. For example:
SQL>execute shareplex_schema_1.SHAREPLEX_IGNORE_TRANS;
SQL>execute shareplex_schema_2.SHAREPLEX_IGNORE_TRANS;
.
.
and so on.
2. Execute the transaction.
3. Once the COMMIT/ROLLBACK is encountered, the transaction will be ignored by all the Shareplex instances in question.
B. Configuring SP_OCT_DENIED_USERID parameter for Capture (available only for Shareplex 7.0 or up):
If you are on Shareplex 7.0 or up, you can also use another feature to force multiple Shareplex instances to ignore the transaction. Here is the procedure as taken from Solution # SOL55971. You need to configure the parameter SP_OCT_DENIED_USERID for all Shareplex instances in question.
If you are on Shareplex 7.0 or up, you can also use another feature to force multiple Shareplex instances to ignore the transaction. Here is the procedure derived from Solution # SOL55971. You need to configure the parameter SP_OCT_DENIED_USERID for all Shareplex instances in question. The following are the steps:
On Source sp_ctrl issue the following command prior to specified user executing the transaction in the source database:
sp_ctrl>set param SP_OCT_DENIED_USERID <user_id of the user session in the database which is to be ignored>
The parameter is live and from this point on, all DML/DDL activity executed on source database by the said user will be ignored, till such time when the parameter is unset. To unset the parameter:
sp_ctrl>reset param SP_OCT_DENIED_USERID
It is important that soon after the transaction that needs to be ignored has been executed, one may want to unset the parameter at all Shareplex instances in question otherwise this user’s action will be ignored by any Shareplex instance where the parameter is still configured.
The following points about this parameter are worth noting:
When the parameter is set, the source database will keep receiving changes from that user session whereas the target database will be devoid of these changes. Consequently, the target database will need to be resync'd with the source database with respect to the # of tables that were modified by the user session.
Unlike the feature SHAREPLEX_IGNORE_TRANS which will stop ignoring a transaction once a COMMIT/ROLLBACK occurs, the parameter SP_OCT_DENIED_USERID will ignore all DML/DDL executed by a specific user. So, while the former feature can never be used to ignore a DDL (since DDL is associated with an implicit COMMIT), the latter can very well ignore the DDL. Consequently one may have to worry about not only the out of sync due to missing DMLs but also the missing DDLs.
While it is easier to control one specific transaction that is ignored by SHAREPLEX_IGNORE_TRANS, the same cannot be said for the SP_OCT_DENIED_USERID feature since enabling the parameter would result in all activity ignored for that user, regardless of different sessions opened by that user. Unless a user/session level auditing is enabled, it is not easy to keep track of the missed changes. When ignoring a transaction with SHAREPLEX_IGNORE_TRANS functionality, the customers are able to run the transaction separately on target to prevent out of sync issues and also avoid high replication traffic. They have more control over what is to be ignored and be able to use the appropriate technique to sync it.
The parameter only takes effect once it is activated. Any open transactions from the specific user prior to activating the parameter will not be ignored and the ignoring will only be done for the actions henceforth.
Limitation: This replication control feature is supported only on Oracle 10g or later, due to the missing user ID information from the redo_log record generated by pre-10g Oracle database.
Related solution: SOL55971
