Chat now with support
Chat with Support

SharePlex 11.3 - Administration Guide

About this Guide Conventions used in this guide Revision History Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl Set SharePlex parameters Configure data replication Configure replication to and from a container database Configure named queues Configure partitioned replication Configure replication to a change history target Configure a replication strategy Configure DDL replication Configure error handling Configure data transformation Configure security features Assign SharePlex users to security groups Start replication on your production systems Monitor SharePlex Prevent and solve replication problems Repair out-of-sync data Tune the Capture process Tune the Post process Recover replication after Oracle failover Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Troubleshooting Tips Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Filter DDL Replication

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:

  • SCHEMA_FILTER filters DDL by a schema name.

  • OBJECT_FILTER filters DDL by an object name.
  • 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 (15,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 (1,'Sales','TEST_%');

DDL codes

DDL Type DDL_CODE
CREATE TABLE 1
ALTER TABLE 15
DROP TABLE 12
ASSOCIATE STATISTICS 168
DISASSOCIATE STATISTICS 169
COMMENT TABLE, COMMENT ON COLUMNS 29
TRUNCATE 85
   
CREATE INDEX 9
ALTER INDEX 11
DROP INDEX 10
   
CREATE SEQUENCE 13
ALTER SEQUENCE 14
DROP SEQUENCE 16
   
CREATE CLUSTER 4
DROP CLUSTER 8
   
CREATE USER 51
ALTER USER 43
DROP USER 53
   
CREATE_ROLE 52
ALTER_ROLE 79
DROP_ROLE 54
GRANT 17
REVOKE 18
   
CREATE SYNONYM 19
DROP SYNONYM 20
   
CREATE VIEW 21
ALTER VIEW 88
DROP VIEW 22
   
   
CREATE TYPE 77
ALTER TYPE 80
DROP TYPE 78
CREATE TYPE BODY 81
DROP TYPE BODY 83
   
CREATE FUNCTION 91
ALTER FUNCTION 92
DROP FUNCTION 93
   
CREATE PROCEDURE 24
ALTER PROCEDURE 25
DROP PROCEDURE 68
   
CREATE PACKAGE 94
ALTER PACKAGE 95
DROP PACKAGE 96
CREATE PACKAGE BODY 97
ALTER PACKAGE BODY 98
DROP PACKAGE BODY 99
   
CREATE DIRECTORY 157
DROP DIRECTORY 158

Best practices for ALTER TABLE DDL

Best Practices for Alter Table DDL

The following are best practices for the replication of Oracle ALTER TABLE operations.

Tables with VARRAY or ABSTRACT types

Do not add a VARRAY column or abstract data type column if you plan to issue an ALTER TABLE to drop a column or set a column unused soon thereafter. SharePlex must query the database to obtain information about this data type. If the second DDL was performed before SharePlex was able to process the first DDL, the query will fail because the metadata is already changed.

Tables with system-specific metadata

If some metadata is system-specific, such as the storage parameters of database objects, there may be unexpected results when DDL on that metadata is replicated. For example, SharePlex replicates all of the storage parameters for a source Oracle object, even though only some of them were changed with the ALTER TABLE command. If the source and target objects were not created with the same storage parameters, one of two things can happen: either the target table will assume the storage of the source table or, if the DDL is not supported by the target, an error will be generated.

Example: consider a source table with MAXEXTENTS 525 and MINEXTENTS 20, and a target table with MAXEXTENTS 505 and MINEXTENTS 4. If the MAXEXTENTS of the source object is changed to unlimited, SharePlex will replicate both the MAXEXTENTS change and the non-changed MINEXTENTS of 20. This causes Oracle error 01570, because MINEXTENTS cannot be larger than the extents currently allocated. Alternatively, if the MINEXTENTS is changed to 1 on the source, but MAXEXTENTS is not changed, SharePlex replicates both, which results in target parameters of MAXEXTENTS 525 and MINEXTENTS 1.

Tables that are renamed

When ALTER TABLE RENAME is issued on a source table that is currently in replication, SharePlex changes the name of the table in the active configuration file by commenting out the old configuration line and adding a new line at the end of the configuration file. If the source and target table names are the same, both are changed to the new name. Otherwise, just the source name is changed. The following is an example:When ALTER TABLE RENAME is issued on a source table that is currently in replication, SharePlex changes the name of the table in the active configuration file by commenting out the old configuration line and adding a new line at the end of the configuration file. If the source and target table names are the same, both are changed to the new name. Otherwise, just the source name is changed.

The following is an example:

# Table scott.table1 renamed to scott.table2 August 5, 2003 10:14

scott.table2 scott.table2 sysA@o.ora555

Whether the Post process stops on RENAME operations or not depends on the setting of the SP_OPO_STOP_ON_DDL_ERR parameter.

Tables with system-generated interval partitions/subpartitions

Because the database generates the names of system-generated interval partitions/subpartitions, the names of those partitions on the source will not match the names of their corresponding partitions on the target. Set the SP_OCT_TRUNC_PARTITION_BY_ID parameter to 1 to ensure that SharePlex truncates the correct partition when it replicates an ALTER TABLE to TRUNCATE a system-generated interval partition. This setting directs SharePlex to identify the partition by using the partition ID, rather than by using the partition name that is specified in the original ALTER TABLE command. Post maps the partition ID to the correct partition name on the target table. For more information, see SP_OCT_TRUNC_PARTITION_BY_ID .

To support the replication of system-named interval partitions/subpartitions, both the source and target must be SharePlex version 8.6.4 or later.

SharePlex does not support TRUNCATE of a system-generated sub-partition if the sub-partition is empty.

ALTER TABLE...MOVE

ALTER TABLE DDL commands that change the rowid of a table can affect subsequent DML operations if the primary or unique keys of the tables in replication are not being logged. When the keys are not logged, SharePlex fetches their values based on the rowid. Any operation that changes the rowid, such as ALTER TABLE...MOVE, can cause the wrong key values to be used for subsequent DML operations.

DDL logging and error handling

Both Capture and Post log the DDL that they process. SharePlex also prints replicated DDL to the SharePlex Event Log, but it may be truncated. Only the Post DDL log contains complete DDL statements. SharePlex stores the DDL logs in the log subdirectory of the variable-data directory on the source and target systems.

By default, Post stops on DDL errors. An error usually indicates that the database component for which the DDL was executed on the source system does not exist in the target database. The default setting of the SP_OPO_STOP_ON_DDL_ERR parameter stops Post on DDL errors, so that subsequent DML on that object does not fail. This enables you to correct the problem and keep the databases synchronized. For more information about this parameter, see theSharePlex Reference Guide.

Table 5: SharePlex DDL log naming conventions

DDL log type Naming convention Example
Capture o.ORACLE_SID_ocap_ddl_log_number.log o.ora12_ocap_ddl_01.log
Oracle target o.ORACLE_SID_machine_name_opo_ddl_log_number.log o.ora12_server2_opo_ddl_01.log
Open Target target r.database_name_machine_name_xpst_ddl_log_number.log r.mssdb1_server3_xpst_ddl_01.log

Configure error handling

This chapter contains an overview of the tools that SharePlex provides to handle errors that are returned by the Post process.

Contents
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating