Poster exit on ddl error due to ORA-14760: ADD PARTITION is not permitted on Interval partitioned ob (4331024)

The following Post errors are observed when a partition is added on the source partitioned table which happens to be a regular Oracle partitioned table:

Notice   2016-06-23 16:43:04.354612 120115 3149158144 s:9 Poster: ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects.  (posting from name1, queue queue_name, to NAME2) [module osp]

Error    2016-06-23 16:43:04.354878 120115 3149158144 s:9 Poster: que/seq = 9/ 4065566292 seqno/offset = 13259/2888302448 Poster exit on ddl error due to ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects. on ALTER TABLE "OWNER"."PARTITIONED_TABLE" ADD PARTITION...  (posting from name1, queue queue_name, to NAME2) [module opo]

The target table is an Interval partitioned table. 

When the source table is an Oracle partitioned table but not Interval partitioned table and the target table is an Interval partitioned table, the ORA-14760 will occur on target table if doing an ALTER TABLE to add partition on the source table. Interval partitioned table only allows the first partition to be created manually and subsequent partitions are created based on the data being applied to it and not by ALTER TABLE to add partition.

This error will occur for the above DDL replication in case of both replicating tables (where SP_OCT_REPLICATE_DDL is set to 3) or for non-replicating tables (where SP_OCT_REPLICATE_ALL_DDL is set to 1). One of the following workaround can be done to get around the problem (where para 1 has the most desired workaround since this type of DDL on a partitioned table where target table is an interval partitioned table will always cause the error and DDL  filtering will be of no use if the table is a replicated object):

1.      You can ignore the DDL error and let Post move on. To ignore the DDL error, on target set the following parameter:

sp_ctrl>set param SP_OPO_STOP_ON_DDL_ERR 0

sp_ctrl>start post

But keep in mind that all subsequent DDL errors including ORA-14760 will be ignored from this point on. If you only wish to get around this specific occurrence and would like to have Post stop due to error when encountering a DDL error so that you can analyze the error first before trying the workaround,  you can unset the above parameter by:

sp_ctrl>reset param SP_OPO_STOP_ON_DDL_ERR

sp_ctrl>stop post

sp_ctrl>start post

2.      If the error was due to a DDL on a non-replicated Oracle partitioned table and if you do not wish to replicate DDL on non-replicating objects for future database activity, then set the following parameter on source. But keep in mind that you are disabling DDL replication for all non-replicated objects including the problem table. Here are the steps:

sp_ctrl>set param SP_OCT_REPLICATE_ALL_DDL 0

sp_ctrl>stop capture  (make sure Capture is “stopped by user” and not “stopping” when issuing “status” on source sp_ctrl and keep issuing “status” till it is “stopped by user”)

sp_ctrl>start capture

3.      If the error was due to a DDL on a replicated Oracle partitioned table and if you do not wish to replicate DDL on replicating objects for future database activity, then set the following parameter on source. But keep in mind that you are disabling DDL replication for all replicated objects including the problem table. Here are the steps:

sp_ctrl>set param SP_OCT_REPLICATE_ DDL 0

sp_ctrl>stop capture  (make sure Capture is “stopped by user” and not “stopping” when issuing “status” on source sp_ctrl and keep issuing “status” till it is “stopped by user”)

sp_ctrl>start capture

4.      The offending DDL can be removed via SharePlelx’ qview utility. But you will need to call Support to be walked thru the procedure as running qview incorrectly can result in data loss or loss of replication.

5.      If the object in question is a non-replicated table and if you do not wish to disable DDL replication for all non-replicating objects but only want to selectively disable such DDL replication for some, then the DDL filtering feature can be used.

Starting with Shareplex 7.6, there is a new feature named DDL filtering that allows you to selectively disable the DDL which are replicated when SP_OCT_REPLICATE_ALL_DDL is set to 1. The Admin Guide section titled “Filter DDL Replication” delves on this and provides an excellent reference.

You can filter the objects for which DDL is replicated when the SP_OCT_REPLICATE_ALL_DDL parameter is enabled. This is the DDL that is not in the replication configuration.

NOTE: DDL filtering is only allowed for objects outside the replication configuration. All DDL performed on objects inside the replication configuration must be replicated to keep the source and target metadata consistent so that DML succeeds.

DDL filtering is controlled in the SHAREPLEX_DDL_CONTROL table that is installed in the SharePlex schema.

Name

Type

-------------

------------

DDL_PARAMETER

NUMBER

DDL_CODE

NUMBER

SCHEMA_FILTER

VARCHAR2(32)

OBJECT_FILTER

VARCHAR2(32)

Each row in the SHAREPLEX_DDL_CONTROL table defines a filter based on what you specify in each of the following columns:

l

SCHEMA_FILTER filters DDL by a schema name. l

OBJECT_FILTER filters DDL by an object name.

l

DDL_CODE filters by the code number of a DDL type. See DDL codes.

A null value in the DDL_CODE column means that the filter applies to all of the DDL types A null in the SCHEMA_FILTER or OBJECT_FILTER column means that the filter applies to any schema or object name.

NOTE: The DDL_PARAMETER column is not an active column as of this release of SharePlex.

To filter DDL

Insert a row into the table with the desired values in the active columns.

Examples

The following filters out of replication the DDL for ALTER TABLE:

INSERT INTO SPLEX.SHAREPLEX_DDL_CONTROL (DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (‘0x0F’,null,null);

The following filters out of replication all DDL for all objects with names that begin with TEST_ in any schema:

INSERT INTO SPLEX.SHAREPLEX_DDL_CONTROL (DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (null,null,'TEST_%');

The following filters out of replication the DDL for CREATE TABLE for the "Sales" schema and objects with names that begin with "TEST_":

INSERT INTO SPLEX.SHAREPLEX_DDL_CONTROL (DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (‘0x01’,'Sales','TEST_%');

Here are some of the DDL codes. The list is quite comprehensive and is listed in the Admin Guide:

DDL codes

DDL Type                                           DDL_CODE

CREATE TABLE                                   0x01

ALTER TABLE                                      0x0F

.

.