Starting with Shareplex 7.0 and up, there is a new feature by which one can ignore DML/DDL activities performed by a specified user. This requires configuring a parameter SP_OCT_DENIED_USERID on source Shareplex instance.
General information.
Starting with Shareplex 7.0 and up, it is possible to direct Shareplex to ignore DML/DDL activities performed by a specified user. The way to achieve this is as below:
First identify the user whose actions need to be ignored by the Capture process. Then query the view DBA_USERS or ALL_USERS to find out the value of the field USER_ID corresponding to the field USERNAME for the user for whom you want to ignore the activities.
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 <source USER_ID >
NOTE: 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. The SP_OCT_DENIED_USERID is a live parameter and can be changed any time
To unset the parameter:
sp_ctrl>reset param SP_OCT_DENIED_USERID
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. This may result in DML applied to target without a consequent commit and therefore, the Post will have some residual messages with 0 backlog. Such messages may need to be removed eventually from the Post queue. Contact Support if you see any residual messages in Post queue after this parameter has been activated.
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.
Note : Do not use this to ignore Shareplex database user, this can cause undesired results.
