5. Executing DDL Synchronization#

Overview#

DDL Synchronization is a feature that replicates the DDL statements performed on the replication target at the local server to the remote server in the same manner.

When DDL Synchronization is performed, transactions operate synchronously. If the DDL statement fails on either the primary transaction or the replicated transaction, the DDL statement will fail. Additionally, the replication target tables on both the local and remote servers will be locked, restricting transaction access.

DDL Synchronization Properties#

DDL Synchronization can be enabled by activating the related property.

Property	Description	Default
REPLICATION_DDL_SYNC	Enables the DDL Synchronization feature.	0

Execution Conditions#

To use the DDL Synchronization feature, the following conditions must be met:

Replication must be started on both the local and remote servers.
The replication protocol version must be the same on both the local and remote servers.
The name and owner of the replication target on both the local and remote servers must be the same.

Restrictions#

The following replication objects or targets cannot perform DDL Synchronization:

Replication objects in EAGER mode
Partitioned Table with global non-partitioned indexes
Replication objects with the PROPAGATION role specified
Replication objects with recovery options enabled

The following statements cannot be replicated through DDL Synchronization:

Rebuilding index partitions
Granting and revoking privileges
Creating and dropping triggers

How to Perform DDL Synchronization#

This section explains how to perform DDL Synchronization. For more detailed procedures, refer to the DDL Synchronization Example.

In the procedure below, we assume that the server performing the DDL statement is the local server, and the server where DDL synchronization is performed is the remote server. Additionally, we assume that replication has been started on both servers.

Step 1. Service Migration#

Migrate the service to the local server where the DDL statement will be executed.
Verify that the service has been successfully migrated.

On the remote server, execute the following query and confirm that the result is 0.
```
SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID();
```

Step 2. Configure Properties#

Set the properties required for executing DDL statements on local and remote servers.

When setting REPLICATION_DDL_SYNC, note that ALTER SESSION should be used on the local server, while ALTER SYSTEM should be used on the remote server.

Properties to be modified on the local server

ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 1;
ALTER SESSION SET REPLICATION_DDL_SYNC = 1;

Properties to be modified on the romote server

ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 1;
ALTER SYSTEM SET REPLICATION_DDL_SYNC=1;
ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;

Step 3. Set Session Replication Mode#

On the local server, set the session's replication mode¹ so that it follows the configuration of the replication object.

ALTER SESSION SET REPLICATION = DEFAULT;

Step 4. Execute the DDL Statement#

Before executing the DDL statement, run the following query on both the local and remote servers to resolve any remaining replication gaps.
```
ALTER REPLICATION replication_name FLUSH;
```
Execute the DDL statement on the local server.

Step 5. Restore Property Settings#

Once the DDL Synchronization is completed, the properties used for DDL synchronization should be reverted to their default values immediately.

Revert to default on the local server

ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0; 
ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 0;   
ALTER SESSION SET REPLICATION_DDL_SYNC = 0;

Revert to default on the remote server

ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;
ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 0;
ALTER SYSTEM SET REPLICATION_DDL_SYNC = 0;
ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;

Step 6. Service Distribution#

Redistribute the service back to its original configuration.

Operation Procedure Table#

Step		Local server	Remote server
Step 1. Service Migration	Service Migration		Migrate Service to Local Server
Step 1. Service Migration	Verify the service migration		SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID();
Step 2. Configure Properties	Enable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
	Set DDL Execution Level	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 1;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 1;
	Enable SQL Apply Mode		ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
	Enable DDL Synchronization	ALTER SESSION SET REPLICATION_DDL_SYNC = 1;	ALTER SYSTEM SET REPLICATION_DDL_SYNC = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap (Remote Server)		ALTER REPLICATION rep1 FLUSH;
	Resolve Replication Gap (Local Server)	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	Execute the DDL Statement
	Resolve Replication Gap (Local Server)	ALTER REPLICATION rep1 FLUSH;
Step 5. Restore Property Settings	Disable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;
	Disable DDL Execution Level	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 0;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 0;
	Disable SQL Apply Mode		ALTER SESSION SET REPLICATION_SQL_APPLY_ENABLE = 0;
	Disable DDL Synchronization	ALTER SESSION SET REPLICATION_DDL_SYNC = 0;	ALTER SYSTEM SET REPLICATION_DDL_SYNC = 0;
Step 6. Service Distribution		Distribute Service to Remote Server

DDL Synchronization Example#

This section introduces three examples of performing DDL statements on replication targets using the DDL Synchronization feature.

In all examples, the following terms are used:

Local Server: The server where the DDL statement is executed.
Remote Server: The server where DDL synchronization is performed.

Additionally, all examples assume that replication has already been started on both servers.

Example 1: TRUNCATE Statement Synchronization#

This example demonstrates the process of executing a TRUNCATE statement on the t1 table on the local server, and how that statement is replicated to the remote server.

Here, it is assumed that the t1 table is a replication target table included in the replication object rep1.

Step		Local server	Remote server
Step 1. Service Migration	Service Migration		Migrate Service to Local Server
Step 1. Service Migration	Verify the service migration		SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID();
Step 2. Configure Properties	Enable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Step 2. Configure Properties	Enable DDL Synchronization	ALTER SESSION SET REPLICATION_DDL_SYNC = 1;	ALTER SYSTEM SET REPLICATION_DDL_SYNC = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap		ALTER REPLICATION rep1 FLUSH;
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	TRUNCATE TABLE t1;
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
Step 5. Restore Property Settings	Disable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;
Step 5. Restore Property Settings	Disable DDL Synchronization	ALTER SESSION SET REPLICATION_DDL_SYNC = 0;	ALTER SYSTEM SET REPLICATION_DDL_SYNC = 0;
Step 6. Service Distribution		Distribute Service to Remote Server

Example 2: Replicating the Addition of a NOT NULL Constraint#

This example demonstrates the process of adding a NOT NULL constraint to column c1 of the t1 table on the local server, and how that statement is replicated to the remote server.

Here, it is assumed that the t1 table is a replication target table included in the replication object rep1.

Step		Local server	Remote server
Step 1. Service Migration	Service Migration		Migrate Service to Local Server
Step 1. Service Migration	Verify the service migration		SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID();
Step 2. Configure Properties	Enable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
	Set DDL Execution Level	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 1;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 1;
	Enable SQL Apply Mode		ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
	Enable DDL Synchronization	ALTER SESSION SET REPLICATION_DDL_SYNC = 1;	ALTER SYSTEM SET REPLICATION_DDL_SYNC = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap		ALTER REPLICATION rep1 FLUSH;
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	ALTER TABLE t1 ALTER COLUMN ( c1 NOT NULL );
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
Step 5. Restore Property Settings	Disable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;
	Set DDL Execution Level	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 0;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 0;
	Disable SQL Apply Mode		ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;
	Disable DDL Synchronization	ALTER SESSION SET REPLICATION_DDL_SYNC = 0;	ALTER SYSTEM SET REPLICATION_DDL_SYNC = 0;
Step 6. Service Distribution		Distribute Service to Remote Server

Example 3: Replicating DDL Statements in a Triple Replication Environment#

The following example demonstrates how to add a column with a NOT NULL constraint to a replication target table in a triple replication environment.

The relevant replication information used in this example is as follows:

Servers participating in the triple replication:
- Local Server: The server where the DDL statement is executed.
- Remote Server 1: The server where DDL synchronization is performed.
- Remote Server 2: The server where DDL synchronization is performed.
Replication Object Names:
- rep1: Replication object between the local server and Remote Server 1
- rep2: Replication object between the local server and Remote Server 2
- rep3: Replication object between the Remote Server 1 and Remote Server 2
Replication Target Table : t1

When the session replication mode is set to DEFAULT, replication is performed according to the mode defined in the replication object (either LAZY or EAGER). ↩

Administrator

Reference

Developer

Tools

External Tools