Appendix C. SQL Apply Mode#
Overview#
SQL Apply Mode is used to maintain replication when metadata between the local and remote servers differs. When enabled, XLogs are converted into SQL statements to replicate the primary transaction. This feature is necessary in various situations where DDL statements are executed on replication targets.
Restrictions#
- EAGER mode replication is not supported.
- It does not work on replication target tables with encrypted columns.
Cautions#
Enabling SQL Apply Mode significantly slows down replication, so it should be disabled immediately after use.
Conditions for SQL Apply Mode Operation#
SQL Apply Mode operates in the following situations:
Column Information Mismatch#
-
Data types are different.
-
Data types are the same, but size, precision, or scale differ.
Constraint Mismatch#
-
The condition of a CHECK constraint differs.
-
The name of a CHECK constraint differs.
-
The NOT NULL / NULL constraint on a column differs.
Index Information Mismatch#
-
The configuration of a unique key index differs.
-
The unique key index includes non-replication target columns.
-
The configuration of a function-based index differs.
-
The function-based index includes non-replication target columns.
Partition Information Mismatch#
- The number of partitions in the replication pair differs.
Setting the SQL Apply Mode#
SQL Apply Mode is set on the replication receiver side.
How to Set the SQL Apply Mode#
To enable SQL Apply Mode on the receiver server, execute the following statement:
ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
Setting Verification Method#
To verify if the setting has been applied correctly, execute the following query on the receiver server:
SELECT NAME, VALUE1 FROM V$PROPERTY WHERE NAME = 'REPLICATION_SQL_APPLY_ENABLE';
Example Result:
NAME VALUE1
-------------------------------------------------------------------
REPLICATION_SQL_APPLY_ENABLE 1
1 row selected.
If the value of VALUE1 is 1, it indicates that SQL Apply Mode has been successfully activated.