Poster exit on ddl error due to ORA-14501: object is not partitioned (206439)

The following errors are observed when the DDL to add partition fails when a partitioned table is replicated to a non-partitioned table:

Error    2016-06-23 17:09:24.927649 88376 2567804672 s:3 Poster: que/seq = 3/ 6614912322 seqno/offset = 13259/4588119152 Poster exit on ddl error due to ORA-14501: object is not partitioned. on ALTER TABLE "OWNER"."PARTITIONED_TABLE_NAME" ADD PARTITION P20...  (posting from name1, queue queue_name, to name2) [module opo]

When the source table is partitioned while target table is not, then this is bound to result in DDL errors when Post attempts to replicate a DDL to add a partition to the target table. Here are the ways to deal with the DDL errors:

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 3). 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 a non-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-14501 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

.

.