How to exclude certain DDL operations from replication. (4274059)

How to use SHAREPLEX_DDL_CONTROL to exclude certain DDL operations on NON-REPLICATION objects.

RESOLUTION

New feature since version 7.6.

SHAREPLEX_DDL_CONTROL table has DDL_CODE column which can be used to exclude certain ddl operations.

On source SP_OCT_REPLICATE_ALL_DDL should be set to 1 to replicate ddls for objects not in replication.

A wild card or null can be used to exclude the ddl operation on all schema and all objects.

Use the decimal value  to populate the DDL_CODE column.

Procedure:

1. Log into source sqlplus as the shareplex user.
2. Insert into <shareplex_owner>.SHAREPLEX_DDL_CONTROL(DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (<DDL_CODE>, '<SCHEMA_NAME>', '<OBJECT_NAME>' );
3. **** MAKE SURE to enter SCHEMA_NAME and OBJECT_NAME in UPPER case*****.
4. stop capture
5. start capture.

For example:

• To exclude the create index operation from all objects in all schemas,

insert into <shareplex_owner>.SHAREPLEX_DDL_CONTROL(DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (9, '%', '%' );

• To exclude the alter index operation from all objects in all schemas that start with TEST.

insert into <shareplex_owner>.SHAREPLEX_DDL_CONTROL(DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (11, 'TEST_%', '%' );

Starting in 8.6.2, SharePlex allows DDL filtering on a particular DDL on a range of objects based on schema or object combination.

• Each row in the SHAREPLEX_DDL_CONTROL table defines a filter based on DDL code AND schema/name specifications.
• A null value in DDL code column is equivalent to ALL DDL code, whereas a null value in schema or name column is equivalent to '%'.

For example, if you want to exclude only create table for tony.TEST123.

SQL> insert into shareplex_ddl_control (ddl_code, schema_filter, object_filter) values (1,'TONY','TEST123');
1 row created.
SQL> commit;

ocap_ddl 2016-03-26 07:32:09.774590 22446 4209776064 User Name: "TONY"  Status: Filtered
ocap_ddl 2016-03-26 07:32:09.774590 22446 4209776064 DDL = create table TEST123 (id number)
ocap_ddl 2016-03-26 07:32:09.774590 22446 4209776064 Oracle Time: 03/26/16 07:32:05 object=TONY.TEST123
ocap_ddl 2016-03-26 07:32:31.809128 22446 4209776064 User Name: "TONY"  Status: Replicated
ocap_ddl 2016-03-26 07:32:31.809128 22446 4209776064 DDL = alter table TEST123 add primary key(id)
ocap_ddl 2016-03-26 07:32:31.809128 22446 4209776064 Oracle Time: 03/26/16 07:32:29 object=TONY.TEST123
ocap_ddl 2016-03-26 07:34:18.950209 22446 4209776064 User Name: "TONY"  Status: Replicated
ocap_ddl 2016-03-26 07:34:18.950209 22446 4209776064 DDL = create table test12345 (id number)
ocap_ddl 2016-03-26 07:34:18.950209 22446 4209776064 Oracle Time: 03/26/16 07:34:16 object=TONY.TEST12345
ocap_ddl 2016-03-26 07:34:39.972560 22446 4209776064 User Name: "TONY"  Status: Replicated
ocap_ddl 2016-03-26 07:34:39.972560 22446 4209776064 DDL = create index test12345_idx on test12345(id)
ocap_ddl 2016-03-26 07:34:39.972560 22446 4209776064 Oracle Time: 03/26/16 07:34:37 object=TONY.TEST12345_IDX

Another example, if you want to exclude alter index for TONY schema.

SQL> insert into shareplex_ddl_control (ddl_code, schema_filter, object_filter) values (11,'TONY',NULL);
1 row created.
Commit complete.

ocap_ddl 2016-03-26 07:49:57.579567 22446 4209776064 User Name: "TONY"  Status: Replicated
ocap_ddl 2016-03-26 07:49:57.579567 22446 4209776064 DDL = alter table test123 add (name varchar2(10))
ocap_ddl 2016-03-26 07:49:57.579567 22446 4209776064 Oracle Time: 03/26/16 07:49:55 object=TONY.TEST123
ocap_ddl 2016-03-26 07:50:08.597282 22446 4209776064 User Name: "TONY"  Status: Replicated
ocap_ddl 2016-03-26 07:50:08.597282 22446 4209776064 DDL = create index test123_idx on test123(name)
ocap_ddl 2016-03-26 07:50:08.597282 22446 4209776064 Oracle Time: 03/26/16 07:50:06 object=TONY.TEST123_IDX
ocap_ddl 2016-03-26 07:51:43.710705 22446 4209776064 User Name: "TONY"  Status: Filtered
ocap_ddl 2016-03-26 07:51:43.710705 22446 4209776064 DDL = alter index test123_idx rebuild online
ocap_ddl 2016-03-26 07:51:43.710705 22446 4209776064 Oracle Time: 03/26/16 07:51:41 object=TONY.TEST123_IDX

DDL code in decimal    

Please use only decimal when you specify the DDL Filter value.