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.
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
| 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 |
Continue to Post When there is a DML Error
SharePlex provides a way for Post to continue processing after it encounters a DML error, rather than stop.
Continue posting on Oracle and SharePlex errors
Valid for Oracle targets
When SharePlex posts to an Oracle target, you can configure Post to ignore specific Oracle DML errors and specific SharePlex error messages and continue processing. Post determines which messages to ignore based on the list in the oramsglist file. The file is installed with a small list of errors by default, but you can remove any of them as desired.
When Posts ignores an error, it writes the error to the SharePlex Event Log. Post also logs the error and the SQL statements that caused the error to the Error log. This log is named SID_errlog.sql log file and is stored in the data directory in the SharePlex variable-data directory. For more information, see View Events and Errors.
Note: There are certain errors that Post will not ignore, and it will stop for those messages even if they are listed in the oramsglist file.
Important: Use caution when using this feature. It could result in hidden out-of-sync conditions. Enable this parameter only if your target users cannot tolerate replication lag and it is acceptable to have some out-of-sync data. Check the SID_errlog.sql log frequently to see if there were errors that could cause replication problems.
To configure Post to continue on errors:
-
On the target system, change directories to the data sub-directory of the SharePlex variable-data directory.
- Find the oramsglist file.
-
If replication is not active, open the file in a text editor. If replication is active, make a copy of the file and then open the copy in the editor.
-
Increase the number on the first line by the number of errors that you are adding. This number must be equal to the total number of errors that are in the file. For example, in the following file there are 10 errors listed.
ora@sys1dad > vi oramsglist
10
604
900
902
908
909
910
911
932
960
1026
-
Starting at the end of the file, add the number of each Oracle or SharePlex error, one per line as shown in the preceding example. The messages need not be in numerical order.
- Save and close the file.
-
Stop Post (if running).
- If you edited a copy of the oramsglist file, save the copy to the original name of oramsglist.
-
Change the value of the SP_OPO_CONT_ON_ERR parameter to 1. Or change the value to 2 to also continue posting on table errors listed in the oramsglist file. See the SharePlex Reference Guide for a description of the SP_OPO_CONT_ON_ERR parameter.
sp_ctrl> set param SP_OPO_CONT_ON_ERR 1
-
Start Post.
sp_ctrl> start post
Continue posting on ODBC errors
Valid for Open Target
When SharePlex posts to an Open Target target, you can configure Post to ignore ODBC errors and continue processing. Post writes the error to the SharePlex Event Log. Post also logs the error and the SQL statements that caused the error to the Error log. This log is named ID_errlog.sql log file, where ID is the database identifier. The file is stored in the data directory in the SharePlex variable-data directory. For more information, see View Events and Errors.
Important: Use caution when using this feature. It could result in hidden out-of-sync conditions. Enable this parameter only if your target users cannot tolerate replication lag and it is acceptable to have some out-of-sync data. Check the SID_errlog.sql log frequently to see if there were errors that could cause replication problems.
To configure Post to continue on errors:
-
On the target system, change directories to the data sub-directory of the SharePlex variable-data directory.
-
Look for one of the following files, depending on the database. These files are installed empty.
|
postgresmsglist |
Postgres |
| sqlservermsglist |
Microsoft SQL Server |
| mysqlmsglist |
Oracle MySQL |
Note: There are certain errors for which Post will stop, even if you list those errors in the message file.
-
If replication is not active, open the file in a text editor. If replication is active, make a copy of the file and then open the copy in the editor.
-
Starting at the end of the file, add the number of each error, one per line as shown in the example. The messages need not be in numerical order.
Example:
sqlservermsglist:
8102
8180
544
2627
3621
- Save and close the file.
-
Stop Post (if running).
- If you copied the original file, save it back to its original name.
-
Change the value of the SP_OPX_CONT_ON_ERR parameter to 1.
sp_ctrl> set param SP_OPX_CONT_ON_ERR 1
-
Start Post.
sp_ctrl> start post
