Appendix B. Standard DDL Execution Procedure#
This section explains how to perform DDL statements on replication targets without setting any specific replication properties.
Prerequisites#
This procedure assumes the following conditions:
- All replication properties related to DDL execution are set to their default values.
- Replication has been started on both servers.
Cautions#
- It is essential to stop services on the Active server.
- If the service on the Active server is not stopped before executing the DDL statement, data inconsistency may occur if the primary transaction is triggered simultaneously with the execution of the DDL statement.
This section describes how to execute DDL statements based on whether it is possible or impossible to stop the service on the replication target server.
Environment Where Service Stop is Possible#
In environments where the operation of all replication target servers can be halted, the following steps can be followed to execute DDL statements.
Step 1: Stop the Service#
-
Stop the service on the Active server.
-
Verify that the service has been stopped by checking the database session on the Active server.
SELECT COUNT(*) FROM V$SESSION WHERE ID<>SESSION_ID();:
Step 2: Set Admin Mode#
Change the Active server to administrator mode to prevent other transactions from entering.
ALTER SYSTEM SET ADMIN_MODE = 1;
Step 3: Stop Replication#
- Resolve the replication gap on the Active server.
ALTER REPLICATION replication_name FLUSH;
- Verify that the replication gap has been resolved. The value of REP_GAP should be 0.
SELECT REP_NAME, REP_GAP FROM V$REPGAP;
- Stop Replication.
ALTER REPLICATION replication_name STOP;
Step 4: Remove Replication Target#
Remove the DDL statement execution target from all replication objects on the replication servers.
ALTER REPLICATION replication_name
DROP TABLE FROM user_name.table_name TO user_name.table_name;
Step 5: Execute DDL Statement#
Execute the DDL statement on all replication servers.
Step 6: Add Replication Target#
Re-add the DDL statement execution target to the replication objects on all replication servers.
ALTER REPLICATION replication_name
ADD TABLE FROM user_name.table_name TO `user_name.table_name;
Step 7: Start Replication#
Start replication on the Active server.
ALTER REPLICATION replication_name START;
Step 8: Remove Admin Mode#
Remove administrator mode on the Active server.
ALTER SYSTEM SET ADMIN_MODE = 0;
Step 9: Start Service#
Restart the service on the Active server.
Active-Standby Environment Execution Procedure#
| Step | Active | Standby | |
|---|---|---|---|
| Step 1. Stop Service | Stop Service | Stop Service | - |
| Session Check | SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID(); | ||
| Step 2. Set Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 1; | ||
| Step 3. Stop Replication | Resolve Replication Gap | ALTER REPLICATION replication_name FLUSH; | |
| Verify the replication gap resolution | SELECT REP_NAME, REP_GAP FROM V$REPGAP; | ||
| Stop Replication | ALTER REPLICATION replication_name STOP; | ||
| Step 4. Remove Replication Target | ALTER REPLICATION replication_name DROP TABLE FROM user_name.table_name TO user_name.table_name; | ALTER REPLICATION replication_name DROP TABLE FROM user_name.table_name TO user_name.table_name; | |
| Step 5. Execute DDL Statement | Execute DDL Statement | Execute DDL Statement | |
| Step 6. Add Replication Target | ALTER REPLICATION replication_name ADD TABLE FROM user_name.table_name TO user_name.table_name; | ALTER REPLICATION replication_name ADD TABLE FROM user_name.table_name TO user_name.table_name; | |
| Step 7. Start Replication | ALTER REPLICATION replication_name START; | ||
| Step 8. Remove Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 0; | ||
| Step 9. Start Service | Start Service | - | |
Active-Active Environment Execution Procedure#
| Step | Active1 | Active2 | |
|---|---|---|---|
| Step 1. Stop Service | Stop Service | Stop Service | Stop Service |
| Session Check | SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID(); | SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID(); | |
| Step 2. Set Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 1; | ALTER SYSTEM SET ADMIN_MODE = 1; | |
| Step 3. Stop Replication | Resolve Replication Gap | ALTER REPLICATION replication_name FLUSH; | ALTER REPLICATION replication_name FLUSH; |
| Verify the replication gap resolution | SELECT REP_NAME, REP_GAP FROM V$REPGAP; | SELECT REP_NAME, REP_GAP FROM V$REPGAP; | |
| Stop Replication | ALTER REPLICATION replication_name STOP; | ALTER REPLICATION replication_name STOP; | |
| Step 4. Remove Replication Target | ALTER REPLICATION replication_name DROP TABLE FROM user_name.table_name TO user_name.table_name; | ALTER REPLICATION replication_name DROP TABLE FROM user_name.table_name TO user_name.table_name; | |
| Step 5. Execute DDL Statement | Execute DDL Statement | Execute DDL Statement | |
| Step 6. Add Replication Target | ALTER REPLICATION replication_name ADD TABLE FROM user_name.table_name TO user_name.table_name; | ALTER REPLICATION replication_name ADD TABLE FROM user_name.table_name TO user_name.table_name; | |
| Step 7. Start Replication | ALTER REPLICATION replication_name START; | ALTER REPLICATION replication_name START; | |
| Step 8. Remove Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 0; | ALTER SYSTEM SET ADMIN_MODE = 0; | |
| Step 9. Start Service | Start Service | Start Service | |
Environment Where Service Cannot Be Stopped#
In environments where the operation of replication servers cannot be stopped simultaneously, DDL statements must be executed sequentially on each server, and SQL Apply Mode must be used.
Cautions
In this procedure, there is a possibility that some data may not be transmitted at certain points in time. Therefore, it is recommended to choose the time with the least transaction activity for performing the operation. Additionally, before the service migration point, it is advised to temporarily stop the service until the replication gap reaches 0.
Step 1: Service Migration#
Migrate the service from Active1 to Active2.
-
Resolve the replication gap on Active1.
ALTER REPLICATION replication_name FLUSH; -
Verify that the replication gap is resolved by checking that the REP_GAP value is 0.
SELECT REP_NAME, REP_GAP FROM V$REPGAP; -
Migrate the service to Active2.
-
Check the database session to verify that the service has been successfully migrated to Active2.
SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID();
Step 2: Set Admin Mode#
Change Active1 to administrator mode to prevent new transactions from entering.
ALTER SYSTEM SET ADMIN_MODE = 1;
Step 3: Stop Replication#
Stop replication on all servers.
ALTER REPLICATION replication_name STOP;
Step 4: Remove Replication Target#
Remove the DDL Statement execution target on Active1.
ALTER REPLICATION replication_name
DROP TABLE FROM user_name.table_name TO user_name.table_name;
Step 5: Execute DDL Statement#
Execute DDL Statement on Active1
Step 6: Add Replication Target#
Re-add the DDL Statement execution target to the replication object on Active1.
ALTER REPLICATION replication_name
ADD TABLE FROM user_name.table_name TO user_name.table_name;
Step 7: Enable SQL Apply Mode#
Enable SQL Apply Mode on all replication servers.
ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1;
Step 8: Start Replication#
Restart replication on all servers.
ALTER REPLICATION replication_name START;
Step 9: Remove Admin Mode#
Remove administrator mode from Active1.
ALTER SYSTEM SET ADMIN_MODE = 0;
Step 10: Perform DDL Statement on Active2#
Repeat Steps 1 through 9 on Active2.
ALTER REPLICATION replication_name START;
Step 11: Verify SQL Apply Mode Operation#
Verify that SQL Apply Mode is working correctly across all replication objects. If the DDL statement was successfully executed on the replication target server, there should be no replication objects still operating in SQL Apply Mode.
Execute the following query to confirm that the SQL_APPLY_TABLE_COUNT is 0:
SELECT REP_NAME, SQL_APPLY_TABLE_COUNT FROM V$REPRECEIVER;
Step 12: Disable SQL Apply Mode#
ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0;
Step 13: Service Distribution#
Redistribute the services on Active1 and Active2 back to their original configuration.
Active-Active Environment Execution Procedure#
| Step | Active1 | Active2 | |
|---|---|---|---|
| Migrate the Service from Active1 | Resolve Replication Gap | ALTER REPLICATION replication_name FLUSH; | |
| Verify the replication gap resolution | SELECT REP_NAME, REP_GAP FROM V$REPGAP; | ||
| Service Migration | Migrate the service from Active1 to Active2. | ||
| Verify Service Migration | SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID(); | SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID(); | |
| Set Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 1; | ||
| Stop Replication | ALTER REPLICATION replication_name STOP; | ALTER REPLICATION replication_name STOP; | |
| Remove Replication Target | ALTER REPLICATION replication_name DROP TABLE FROM user_name.table_name TO user_name.table_name; | ||
| Execute DDL Statement | Execute DDL Statement | ||
| Add Replication Target | ALTER REPLICATION replication_name ADD TABLE FROM user_name.table_name TO user_name.table_name; | ||
| Enable SQL Apply Mode | ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1; | ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 1; | |
| Start Replication | ALTER REPLICATION replication_name START; | ALTER REPLICATION replication_name START; | |
| Remove Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 0; | ||
| Migrate the Service from Active2 | Resolve Replication Gap | ALTER REPLICATION replication_name FLUSH; | |
| Verify the replication gap resolution | SELECT REP_NAME, REP_GAP FROM V$REPGAP; | ||
| Migrate service | Migrate the service from Active1 to Active2. | ||
| Verify Service Migration | SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID(); | SELECT COUNT(*) FROM V$SESSION WHERE ID <> SESSION_ID(); | |
| Set Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 1; | ||
| Stop Replication | ALTER REPLICATION replication_name STOP; | ALTER REPLICATION replication_name STOP; | |
| Remove Replication Target | ALTER REPLICATION replication_name DROP TABLE FROM user_name.table_name TO user_name.table_name; | ||
| Execute DDL Statement | Execute DDL Statement | ||
| Add Replication Target | ALTER REPLICATION replication_name ADD TABLE FROM user_name.table_name TO user_name.table_name; | ||
| Start Replication | ALTER REPLICATION replication_name START; | ALTER REPLICATION replication_name START; | |
| 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; | |
| Disable SQL Apply Mode | ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0; | ALTER SYSTEM SET REPLICATION_SQL_APPLY_ENABLE = 0; | |
| Remove Admin Mode | ALTER SYSTEM SET ADMIN_MODE = 0; | ||
| Service Distribution | Redistribute the service back to its original configuration | ||