4. Executing DDL Statements in a Replication Environment#

Overview#

Altibase synchronizes data by transferring transaction logs generated on the local server to the remote server using a network-based replication mechanism. However, transaction logs generated by DDL operations are not transmitted. This is because DDL statements modify metadata on replication targets, which can lead to data inconsistency between servers.

Therefore, in a typical replication environment, to execute a DDL statement, the target object must first be excluded from replication. Then, the DDL operation must be executed independently on each replication node. For more details on standard DDL execution procedures, refer to Appendix B. Standard DDL Execution Procedure.

This chapter introduces a method for executing DDL statements safely and efficiently in an Altibase replication environment—without removing the replication target.

Properties for Executing DDL Statements#

By default, DDL statements cannot be executed on replication targets, with a few exceptions. Therefore, to run DDL statements, the relevant settings must be enabled.

The following are the key properties that must be configured to allow DDL execution on replication targets.

Property	Description	Default
REPLICATION_DDL_ENABLE	Enables the execution of DDL statements. When set to `1`, DDL can be executed on replication targets.	0
REPLICATION_DDL_ENABLE_LEVEL	Specifies the types of DDL statements allowed on replication targets.	0
REPLICATION_SQL_APPLY_ENABLE	Enables SQL Apply Mode.	0

Restrictions#

Even when the relevant settings are enabled, DDL statements cannot be executed on replication targets that belong to the following types of replication objects:

Replication objects with recovery options enabled

In this case, delete the replication object first, perform the DDL operation, and then recreate the replication object.
Replication objects operating in EAGER mode

For such cases, refer to Appendix B. Standard DDL Execution Procedure.

Cautions#

Executing a DDL statement locks the target table. If a primary transaction occurs during this time, the receiver thread cannot apply the replicated transaction to the locked table.
You must clear any replication gap before executing a DDL statement. Running a DDL with an active replication gap can negatively affect replication performance.
If the column range is being increased, execute the DDL first on the server not generating primary transactions.
If the column range is being decreased, execute the DDL first on the server generating primary transactions.

Allowed DDL Statements#

이중화 대상에 수행할 수 있는 DDL 문은 REPLICATION_DDL_ENABLE_LEVEL 프로퍼티의 설정 값에 따라 달라지며, 이 값은 0 또는 1로 설정할 수 있다.

사용자는 수행하려는 DDL 문에 적합하게 이 프로퍼티를 설정해야 한다.

DDL Level 1#

The following DDL statements can be executed when REPLICATION_DDL_ENABLE_LEVEL is set to 1.

Info

To execute statements under DDL Level 1, SQL Apply Mode must be enabled. SQL Apply Mode is performed on the replication receiver side.

Tip

When REPLICATION_DDL_ENABLE_LEVEL is set to 1, all statements under DDL Level 0 can also be executed.

Adding and Modifying Columns#

You can add columns with NOT NULL and unique key constraints, or modify the constraints and data types of existing columns.

ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE NOT NULL );
ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE UNIQUE );
ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE LOCALUNIQUE );

ALTER TABLE table_name ALTER COLUMN ( column_name NOT NULL );
ALTER TABLE table_name ALTER COLUMN ( column_name NULL );

ALTER TABLE table_name MODIFY COLUMN ( column_name NOT NULL );
ALTER TABLE table_name MODIFY COLUMN ( column_name NULL );
ALTER TABLE table_name MODIFY COLUMN ( column_name DATA_TYPE );

Deleting Columns#

You can delete columns that have constraints or are used in function-based indexes.

ALTER TABLE table_name DROP COLUMN column_name;

The following types of columns cannot be deleted:

Primary key
Compressed columns

Partition Operations#

You can split, merge, or drop partitions. Partitions created or removed through partition operations are automatically added to or removed from the replication target partitions.

ALTER TABLE table_name SPLIT PARTITION .....;
ALTER TABLE table_name MERGE PARTITIONS .....;
ALTER TABLE table_name DROP PARTITION partiton_name;

When performing partition operations, take the following precautions:

Replication must be stopped on both the local and remote servers.
The partition names used in the DDL statement must be identical on both the local and remote servers.
Partition merging can only be performed when all target partitions are part of the replication target.
Partition deletion is allowed only if the partitioned table contains at least two partitions.

Adding, Modifying, and Dropping Table Constraints#

You can add, modify, or drop table constraints.

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE ( column_name );
ALTER TABLE table_name ADD CONSTRAINT constraint_name LOCALUNIQUE ( column_name ) ;
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK ( check_condition );
ALTER TABLE table_name RENAME CONSTRAINT constraint_name TO constraint_name;
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Creating and Dropping Unique Key Indexes and Function-Based Indexes#

You can create or drop unique key indexes and function-based indexes.

CREATE UNIQUE INDEX index_name ON table_name ( column_name );
CREATE INDEX index_name ON table_name ( expression );
DROP INDEX index_name;

DDL Level 0#

The following DDL statements can be executed when REPLICATION_DDL_ENABLE_LEVEL is set to 0 — that is, under DDL Level 0.

Adding Columns Without Constraints#

ALTER TABLE table_name ADD COLUMN ( column_name DATA_TYPE );

The following types of columns cannot be added:

Compressed columns
Foreign keys
Columns with constraints

Deleting Columns Without Constraints#

ALTER TABLE table_name DROP COLUMN column_name;

The following types of columns cannot be deleted:

Compressed columns
Primary keys
Columns with constraints
Columns used in function-based indexes

Setting and Removing Default Values for Columns#

ALTER TABLE table_name ALTER COLUMN ( column_name SET DEFAULT value ); 
ALTER TABLE table_name ALTER COLUMN ( column_name DROP DEFAULT);

Changing the Tablespace of a Table or Partition#

ALTER TABLE table_name ALTER TABLESPACE tablespace_name;
ALTER TABLE table_name ALTER PARTITION partition_name TABLESPACE;

Deleting All Data from a Table or Partition#

ALTER TABLE table_name TRUNCATE PARTITION partition_name;
TRUNCATE TABLE table_name;

However, data cannot be deleted from tables that contain the following type of column:

Compressed columns

Creating and Dropping Indexes#

CREATE INDEX index_name ON table_name ( column_name );
DROP INDEX index_name;

The following types of indexes cannot be dropped:

Unique key indexes
Function-based indexes

Other Statements#

The following statement can be executed on replication targets regardless of the REPLICATION_DDL_ENABLE property setting:

Disk Index AGING#

ALTER INDEX index_name AGING;

Returning an Empty Page#

ALTER TABLE table_name COMPACT;
ALTER TABLE table_name COMPACT PARTITION partition_name;

Rebuilding Index Partitions#

ALTER INDEX REBUILD PARTITION index_partition_name;

Granting and Revoking Privileges#

GRANT OBJECT ...;
REVOKE OBJECT ...;

Creating and Dropping Triggers#

CREATE TRIGGER ...;
DROP TRIGGER ...;

Executing DDL Statements#

This section describes the procedure for executing DDL statements that fall under DDL Level 1 and DDL Level 2 on replication targets. If the steps are not followed in the recommended order, data inconsistency may occur.

Step 1. Service Migration#

Migrate all services to the local server where the DDL statement will be executed.
Confirm that all services have been successfully migrated to the local server.

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

Step 2. Configure Properties#

On both the local and remote servers, update the property settings according to the DDL statement you intend to execute.

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

Step 3. Set the Session Replication Mode#

Set the session's replication mode¹ to DEFAULT on both the local and remote servers so that it follows the configuration of the replication object.

ALTER SESSION SET REPLICATION = DEFAULT;

Step 4. Execute the DDL Statement#

Execute the same DDL statement on both the local and remote servers.

1. Resolve any remaining replication gaps before executing the DDL statement.
```
ALTER REPLICATION replication_name FLUSH;
```
1. Stop replication before executing the DDL (for partitioned targets).
```
ALTER REPLICATON replication_name STOP;
```
1. Execute the DDL statement.
1. Restart replication after executing the DDL (for partitioned targets).
```
ALTER REPLICATON replication_name START;
```
1. Resolve the replication gap after executing the DDL (if SQL Apply Mode is enabled).
```
ALTER REPLICATION replication_name FLUSH;
```

Step 5. Verify SQL Apply Mode Operation#

On the remote server, check whether SQL Apply Mode is active for any replication object.

If the DDL statements have been executed correctly on the replication target server, there should be no replication objects currently operating in SQL Apply Mode.

Execute the following query to confirm that the SQL_APPLY_TABLE_COUNT value is 0.

SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;

This procedure is performed only when SQL Apply Mode is enabled.

Step 6. Restore Property Settings#

Immediately after completing the DDL operation, revert the values of the properties modified in Step 2 back to their default settings.

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

Step 7. Service Distribution#

Distribute the services back to their original configuration.

Operation Procedure Table#

DDL Level 1 Execution Example#

Example 1: Changing the Data Type of a Column — CHAR(5) to CHAR(10)#

The following example demonstrates how to change the data type of column c2 in replication target table T1, which belongs to the replication object rep1, from CHAR(5) to CHAR(10).

This case covers changes to a wider data type than the original.

Info

When changing to a wider data type, the DDL statement must be executed first on the server where no primary transactions are occurring.

Active-Standby environment#

Step		Active	Standby
Step 1. Service Migration		There are no services to migrate since this is an Active-Standby environment.
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;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Execute the DDL Statement		ALTER TABLE T1 MODIFY COLUMN ( c2 CHAR(10) );
	Resolve Replication Gap Before Executing the DDL Statement	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	ALTER TABLE T1 MODIFY COLUMN ( c2 CHAR(10) );
	Resolve Replication Gap After Executing the DDL Statement(When SQL Apply Mode is Enabled)	ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation			SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 6. 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;
Step 7. Service Distribution		There are no services to migrate since this is an Active-Standby environment.

Active-Active environment#

Assume that Active1 is the local server where the DDL statement is executed and primary transactions are occurring.

Step		Active1	Active2
Step 1. Service Migration	Service Migration		Migrate the service to Active1.
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;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;	ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap Before Executing the DDL Statement		ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement		ALTER TABLE t1 MODIFY COLUMN ( C2 CHAR(10) );
	Resolve Replication Gap After Executing the DDL Statement(When SQL Apply Mode is Enabled)		ALTER REPLICATION rep1 FLUSH;
	Resolve Replication Gap Before Executing the DDL Statement	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	ALTER TABLE t1 MODIFY COLUMN ( C2 CHAR(10) );
	Resolve Replication Gap After Executing the DDL Statement(When SQL Apply Mode is Enabled)	ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation		SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;	SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 6. 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;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;
Step 7. Service Distribution		Distribute the service to Active2.

Example 2: Adding a Table Constraint – CHECK#

The following example demonstrates how to add a CHECK constraint to column c2 of the replication target table T1, which belongs to the replication object rep1.

This case deals with restricting the allowed value range of a column more narrowly than before.

Info

When narrowing the value range of a column, the DDL statement must be executed first on the server where primary transactions are occurring.

Active-Standby Environment#

Step		Active	Standby
Step 1. Service Migration		There are no services to migrate since this is an Active-Standby environment.
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;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	ALTER TABLE T1 ADD CONSTRAINT T1_CHECK CHECK ( c2 < 10 );
	Execute the DDL Statement		ALTER TABLE T1 ADD CONSTRAINT T1_CHECK CHECK ( c2 < 10 );
	Resolve Replication Gap(When SQL Apply Mode Is Enabled)	ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation			SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 6. 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;
Step 7. Service Distribution		There are no services to migrate since this is an Active-Standby environment.

Active-Active Environment#

Assume that Active1 is the local server where DDL statements are executed and primary transactions are occurring.

Step		Active1	Active2
Step 1. Service Migration	Service Migration		Migrate the service to Active1.
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;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;	ALTER SESSION SET REPLICATION = DEFAULT;
Step. 4. Execute the DDL Statement	Resolve Replication Gap Before Executing the DDL Statement	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	ALTER TABLE t1 ADD CONSTRAINT T1_CHECK CHECK ( C2 < 10 );
	Resolve Replication Gap After Executing the DDL Statement(When SQL Apply Mode Is Enabled)	ALTER REPLICATION rep1 FLUSH;
	Resolve Replication Gap Before Executing the DDL Statement		ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement		ALTER TABLE t1 ADD CONSTRAINT T1_CHECK CHECK ( C2 < 10 );
	Resolve Replication Gap After Executing the DDL Statement(When SQL Apply Mode Is Enabled)		ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation		SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;	SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 6. 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;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;
Step 7. Service Distribution		Distribute the service to Active2.

Example 3: Partition Split#

The following example demonstrates how to split partition P2 of the replication target table T1, which belongs to the replication object rep1, into two partitions: P3 and P4.

Active-Standby Environment#

Step		Active	Standby
Step 1. Service Migration		There are no services to migrate since this is an Active-Standby environment.
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;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
	Stop Replication	ALTER REPLICATION rep1 STOP;
	Execute the DDL Statement	ALTER TABLE T1 SPLIT PARTITION P2 INTO (PARTITION P3, PARTITION P4);	ALTER TABLE T1 SPLIT PARTITION P2 INTO (PARTITION P3, PARTITION P4);
	Start Replication	ALTER REPLICATION rep1 START;
	Resolve Replication Gap(When SQL Apply Mode Is Enabled)	ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation			SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 6. 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		REPLICATION_SQL_APPLY_ENABLE = 0;
Step 7. Service Distribution		There are no services to migrate since this is an Active-Standby environment.

Active-Active Environment#

Assume that Active1 is the local server where DDL statements are executed and primary transactions are occurring.

Step		Active1	Active2
Step 1. Service Migration	Service Migration		Migrate the service to Active1.
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;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;	ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;	ALTER REPLICATION rep1 FLUSH;
	Stop Replication	ALTER REPLICATION rep1 STOP;	ALTER REPLICATION rep1 STOP;
	Execute the DDL Statement	ALTER TABLE T1 SPLIT PARTITION P2 INTO (PARTITION P3, PARTITION P4);	ALTER TABLE T1 SPLIT PARTITION P2 INTO (PARTITION P3, PARTITION P4);
	Start Replication	ALTER REPLICATION rep1 START;	ALTER REPLICATION rep1 START;
	Resolve Replication Gap(When SQL Apply Mode Is Enabled)	ALTER REPLICATION rep1 FLUSH	ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation		SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;	SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 6. 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;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;
Step 7. Service Distribution		Distribute the service to Active2.

DDL Level 0 Execution Example#

Example 1: Adding a Column#

The following example demonstrates how to add column i3 to the replication target table T1, which belongs to the replication object rep1.

Active-Standby Environment#

Step		Active	Standby
Step 1. Service Migration		There are no services to migrate since this is an Active-Standby environment.
Step 2. Configure Properties	Enable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Execute the DDL Statement		ALTER TABLE T1 ADD COLUMN ( i3 INTEGER );
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	ALTER TABLE T1 ADD COLUMN ( i3 INTEGER );
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation		-	-
Step 6. Restore Property Settings	Disable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;
Step 7. Service Distribution		There are no services to migrate since this is an Active-Standby environment.

Active-Active Environment#

Assume that Active1 is the local server where primary transactions occur during DDL statement execution.

Step		Active1	Active2
Step 1. Service Migration	Service Migration		Migrate the service to Active1.
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 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION = DEFAULT;	ALTER SESSION SET REPLICATION = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap		ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement		ALTER TABLE T1 ADD COLUMN ( i3 INTEGER );
	Resolve Replication Gap		ALTER REPLICATION rep1 FLUSH;
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
	Execute the DDL Statement	ALTER TABLE T1 ADD COLUMN ( i3 INTEGER );
	Resolve Replication Gap	ALTER REPLICATION rep1 FLUSH;
Step 5. Verify SQL Apply Mode Operation		-	-
Step 6. Restore Property Settings	Disable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;
Step 7. Service Distribution		Distribute the service to Active2.

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). ↩

Step		DDL Level 0	DDL Level 1 : Table or Column Targets	DDL Level 1 : Partition Targets
Step 1. Service Migration	Service Migration	Migrate the service to the local server where the DDL statement will be executed.	Migrate the service to the local server where the DDL statement will be executed.	Migrate the service to the local server where the DDL statement will be executed.
Step 1. Service Migration	Verify the service migration	SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID();	SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID();	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;	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;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
Step 3. Set Session Replication Mode		ALTER SESSION SET REPLICATION_MODE = DEFAULT;	ALTER SESSION SET REPLICATION_MODE = DEFAULT;	ALTER SESSION SET REPLICATION_MODE = DEFAULT;
Step 4. Execute the DDL Statement	Resolve Replication Gap	ALTER REPLICATION replication_name FLUSH;	ALTER REPLICATION replication_name FLUSH;	ALTER REPLICATION replication_name FLUSH;
	Stop Replication (Partition Targets)	-	-	ALTER REPLICATON replication_name STOP;
	Execute the DDL Statement	Execute the DDL Statement	Execute the DDL Statement	Execute the DDL Statement
	Start Replication (Partition Targets)	-	-	ALTER REPLICATON replication_name START;
	Resolve Replication Gap (When SQL Apply Mode Is Enabled)	-	ALTER REPLICATION replication_name FLUSH;	ALTER REPLICATION replication_name FLUSH;
Step 5. Verify SQL Apply Mode Operation		-	SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;	SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 6. Restore Property Settings	Disable DDL Statement Execution	ALTER SYSTEM SET REPLICATION_DDL_ENABLE = 0;	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;	ALTER SYSTEM SET REPLICATION_DDL_ENABLE_LEVEL = 0;
	Disable SQL Apply Mode	-	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;	ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;
Step 7. Service Distribution		Redistribute the Service to Its Original Configuration.	Redistribute the Service to Its Original Configuration.	Redistribute the Service to Its Original Configuration.