Oracle Zero Downtime Migration (ZDM) is the free-to-use Oracle Maximum Availability Architecture (MAA)-recommended solution to migrate Oracle Databases to the Oracle Cloud Infrastructure.
ZDM provides a number of migration options – Physical offline,Physical online, Logical offline, Logical online.
In a Logical Online migration, users are still connected to a live database while ZDM is executing in the background.
Internally ZDM uses Oracle GoldenGate technology to enable the ‘zero-downtime’ migration as well as Oracle Data Pump.
These series of posts provide a step-by-step procedure of how to do an Online Logical Migration of an Oracle Database 12c to Oracle Database 21c.
In Part 1, we discussed how to create and configure the ZDM hub.
In Part 2, we discussed how to create and configure the GoldenGate hub.
In Part 3, we discussed the process of configuring connectivity from the OCI console to both the ZDM and GoldenGate hubs.
In Part 4, we discussed how to configure the source Oracle 12c Release 2 database and the target Oracle 21c database for Zero Downtime Migration.
In Part 5, we discussed how to execute ZDM in Evaluation Mode via zdmcli followed by the actual execution of ZDM as part of a ‘live’ online migration.
In the concluding part of the series, in this post we will perform a ‘live’ online migration of the source Oracle 12c database to Oracle 21c and will complete all the phases of the ZDM migration.
On source Oracle 12c database, execute a DELETE transaction.
Note the count of rows in the DEMO.MYOBJECTS table.
[oracle@ora12c ~]$ sqlplus system/xxxxx##@10.0.0.50:1521/ora12c_pdb1.sub12020731300.soormavcn.oraclevcn.com
SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 9 06:27:59 2023
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Thu Feb 09 2023 06:04:02 +00:00
Connected to:
Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production
SQL> select count(*) from demo.myobjects;
COUNT(*)
----------
73528
SQL> delete from demo.myobjects where owner='SYSTEM';
459 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from demo.myobjects;
COUNT(*)
----------
73069
Verify that the GoldenGate Extract process has captured the DELETE statement on the DEMO.MYOBJECTS table.
Connect to the target Oracle 21c database. Note the count of rows in the table.
The DELETE statement has been applied by the GoldenGate Replicat process on the target table as the count of rows in the table (73069) matches that of the source Oracle 12c database table.
[oracle@ora21c ~]$ sqlplus system/xxx@10.0.0.234:1521/ora21c_pdb1.sub12020731300.soormavcn.oraclevcn.com
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Feb 14 06:55:15 2023
Version 21.8.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Tue Feb 14 2023 06:54:51 +00:00
Connected to:
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.8.0.0.0
SQL> select count(*) from demo.myobjects;
COUNT(*)
----------
73069
Let us now simulate some active in-flight transactions in progress on the source 12c database while the ZDM online migration is in progress.
On source 12c database issue an INSERT statement.
<<< DO NOT COMMIT!>>>
SQL> insert into demo.myobjects select * from dba_objects where owner='SYSTEM'; 459 rows created.
The ZDM job which was earlier paused at the phase ZDM_MONITOR_GG_LAG is resumed.
Note the status of the phase ZDM_MONITOR_GG_LAG changes to COMPLETED and the phase ZDM_SWITCHOVER_APP commences.
[zdmuser@zdm-hub bin]$ ./zdmcli resume job -jobid 12 zdm-hub.sub12020731300.soormavcn.oraclevcn.com: Audit ID: 54 [zdmuser@zdm-hub bin]$ ./zdmcli query job -jobid 12 zdm-hub.sub12020731300.soormavcn.oraclevcn.com: Audit ID: 56 Job ID: 12 User: zdmuser Client: zdm-hub Job Type: "MIGRATE" Scheduled job command: "zdmcli migrate database -sourcedb ora12c_syd126 -sourcenode ora12c.sub12020731300.soormavcn.oraclevcn.com -srcauth zdmauth -srcarg1 user:opc -srcarg2 identity_file:/home/zdmuser/.ssh/id_rsa -srcarg3 sudo_location:/usr/bin/sudo -targetnode ora21c.sub12020731300.soormavcn.oraclevcn.com -rsp /home/zdmuser/logical_migration.rsp -tgtauth zdmauth -tgtarg1 user:opc -tgtarg2 identity_file:/home/zdmuser/.ssh/id_rsa -tgtarg3 sudo_location:/usr/bin/sudo -pauseafter ZDM_MONITOR_GG_LAG" Scheduled job execution start time: 2023-02-12T03:10:18Z. Equivalent local time: 2023-02-12 03:10:18 Current status: EXECUTING Current Phase: "ZDM_SWITCHOVER_APP" Result file path: "/u02/zdmuser/zdmbase/chkbase/scheduled/job-12-2023-02-12-03:10:34.log" Excluded objects file path: "/u02/zdmuser/zdmbase/chkbase/scheduled/job-12-filtered-objects-2023-02-12T03:11:09.407.json" Job execution start time: 2023-02-12 03:10:34 Job execution end time: 2023-02-12 03:24:35 Job execution elapsed time: 14 minutes 0 seconds ZDM_VALIDATE_TGT ...................... COMPLETED ZDM_VALIDATE_SRC ...................... COMPLETED ZDM_SETUP_SRC ......................... COMPLETED ZDM_PRE_MIGRATION_ADVISOR ............. COMPLETED ZDM_VALIDATE_GG_HUB ................... COMPLETED ZDM_VALIDATE_DATAPUMP_SETTINGS_SRC .... COMPLETED ZDM_VALIDATE_DATAPUMP_SETTINGS_TGT .... COMPLETED ZDM_PREPARE_DATAPUMP_SRC .............. COMPLETED ZDM_DATAPUMP_ESTIMATE_SRC ............. COMPLETED ZDM_PREPARE_GG_HUB .................... COMPLETED ZDM_ADD_HEARTBEAT_SRC ................. COMPLETED ZDM_ADD_SCHEMA_TRANDATA_SRC ........... COMPLETED ZDM_CREATE_GG_EXTRACT_SRC ............. COMPLETED ZDM_PREPARE_DATAPUMP_TGT .............. COMPLETED ZDM_PARALLEL_EXPORT_IMPORT ............ COMPLETED ZDM_POST_DATAPUMP_SRC ................. COMPLETED ZDM_POST_DATAPUMP_TGT ................. COMPLETED ZDM_ADD_HEARTBEAT_TGT ................. COMPLETED ZDM_ADD_CHECKPOINT_TGT ................ COMPLETED ZDM_CREATE_GG_REPLICAT_TGT ............ COMPLETED ZDM_MONITOR_GG_LAG .................... COMPLETED ZDM_SWITCHOVER_APP .................... STARTED ZDM_RM_GG_EXTRACT_SRC ................. PENDING ZDM_RM_GG_REPLICAT_TGT ................ PENDING ZDM_DELETE_SCHEMA_TRANDATA_SRC ........ PENDING ZDM_RM_HEARTBEAT_SRC .................. PENDING ZDM_RM_CHECKPOINT_TGT ................. PENDING ZDM_RM_HEARTBEAT_TGT .................. PENDING ZDM_CLEAN_GG_HUB ...................... PENDING ZDM_POST_ACTIONS ...................... PENDING ZDM_CLEANUP_SRC ....................... PENDING
Because the INSERT transaction has not yet been committed, we can see ZDM is waiting for all in-flight transactions to complete before completing the remaining phases of the migration.
[zdmuser@zdm-hub bin]$ tail -f /u02/zdmuser/zdmbase/chkbase/scheduled/job-12-2023-02-12-03:10:34.log zdm-hub: 2023-02-14T07:02:04.340Z : "RRF1Y" is processing trail file "SV" with sequence number "0" at offset "4065795" zdm-hub: 2023-02-14T07:02:05.106Z : "RRF1Y" is reading from same trail file that "EXTIAMXP" is writing to zdm-hub: 2023-02-14T07:02:05.888Z : Status of extract process "EXTIAMXP" : RUNNING zdm-hub: 2023-02-14T07:02:05.890Z : "EXTIAMXP" is processing trail file "SV" with sequence number "0" at offset "4065795" zdm-hub: 2023-02-14T07:02:05.891Z : Extract process "EXTIAMXP" lag : 1 seconds (age : 6 seconds) zdm-hub: 2023-02-14T07:02:06.690Z : Status of Oracle GoldenGate replicat process "RRF1Y" : RUNNING zdm-hub: 2023-02-14T07:02:06.693Z : "RRF1Y" is processing trail file "SV" with sequence number "0" at offset "4065795" zdm-hub: 2023-02-14T07:02:07.382Z : Replication throughput: 0.0 GBPH zdm-hub: 2023-02-14T07:02:08.668Z : End-to-end lag: 1.06 seconds (age: 36.51 seconds) zdm-hub: 2023-02-14T07:02:10.530Z : waiting for 1 transactions to complete zdm-hub: 2023-02-14T07:03:11.027Z : waiting for 1 transactions to complete
On the source database, the ‘in-flight’ transaction is now completed.
So at this time there are no ‘active’ transactions on the source database and ZDM can complete the remaining phases of the migration process.
In a real-life situation, here is where the migration outage will occur.
On the source database all client application connections will have to be closed/disconnected and client application connection configuration changed to now point to the ‘new upgraded live database’!
SQL> insert into demo.myobjects select * from dba_objects where owner='SYSTEM';
459 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from demo.myobjects;
COUNT(*)
----------
73528
SQL> exit
Disconnected from Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production
Connect to the target 21c database and verify that the last transaction has also been applied.
[oracle@ora21c ~]$ sqlplus system/xxxxxx@10.0.0.234:1521/ora21c_pdb1.sub12020731300.soormavcn.oraclevcn.com
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Feb 14 06:55:15 2023
Version 21.8.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Tue Feb 14 2023 06:54:51 +00:00
Connected to:
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production
Version 21.8.0.0.0
SQL> select count(*) from demo.myobjects;
COUNT(*)
----------
73528
The source Oracle 12c database now has no active transactions and ZDM reports it as ‘idle’.
So ZDM will begin its final phases of executions in which it confirms that the trail files have been fully processed by the Replicat process and that the Extract process is not writing to any trail files as well.
ZDM enters its final ‘cleanup ‘ phases where the Extract and Replicat processes are stopped and then deleted along with the Heartbeat and Checkpoint tables.
zdm-hub: 2023-02-14T08:59:38.756Z : waiting for 1 transactions to complete zdm-hub: 2023-02-14T09:00:39.712Z : waiting for source database to become idle zdm-hub: 2023-02-14T09:00:40.686Z : "EXTQZUH0" is processing trail file "JL" with sequence number "0" at offset "401918" zdm-hub: 2023-02-14T09:01:41.184Z : "EXTQZUH0" is processing trail file "JL" with sequence number "0" at offset "403165" zdm-hub: 2023-02-14T09:02:41.736Z : "EXTQZUH0" is processing trail file "JL" with sequence number "0" at offset "404412" zdm-hub: 2023-02-14T09:03:42.328Z : "EXTQZUH0" is processing trail file "JL" with sequence number "0" at offset "405660" zdm-hub: 2023-02-14T09:04:42.703Z : "EXTQZUH0" is processing trail file "JL" with sequence number "0" at offset "406908" zdm-hub: 2023-02-14T09:05:43.301Z : "EXTQZUH0" is processing trail file "JL" with sequence number "0" at offset "408155" zdm-hub: 2023-02-14T09:05:43.309Z : source database is idle zdm-hub: 2023-02-14T09:05:47.746Z : stopped Oracle GoldenGate extract process "EXTQZUH0" zdm-hub: 2023-02-14T09:06:18.268Z : Status of extract process "EXTQZUH0" : STOPPED zdm-hub: 2023-02-14T09:06:18.645Z : "EXTQZUH0" is processing trail file "JL" with sequence number "0" at offset "408155" zdm-hub: 2023-02-14T09:06:18.962Z : "RVRT3" is processing trail file "JL" with sequence number "0" at offset "408155" zdm-hub: 2023-02-14T09:06:52.058Z : stopped Oracle GoldenGate replicat process "RVRT3" zdm-hub: 2023-02-14T09:07:22.618Z : Status of Oracle GoldenGate replicat process "RVRT3" : STOPPED zdm-hub: 2023-02-14T09:07:22.630Z : Execution of phase ZDM_SWITCHOVER_APP completed zdm-hub: 2023-02-14T09:07:24.041Z : Executing phase ZDM_RM_GG_EXTRACT_SRC zdm-hub: 2023-02-14T09:07:24.894Z : stopped Oracle GoldenGate extract process "EXTQZUH0" zdm-hub: 2023-02-14T09:07:32.424Z : deleted Oracle GoldenGate integrated extract process "EXTQZUH0" zdm-hub: 2023-02-14T09:07:32.427Z : Execution of phase ZDM_RM_GG_EXTRACT_SRC completed zdm-hub: 2023-02-14T09:07:32.874Z : Executing phase ZDM_RM_GG_REPLICAT_TGT zdm-hub: 2023-02-14T09:07:33.439Z : stopped Oracle GoldenGate replicat process "RVRT3" zdm-hub: 2023-02-14T09:07:35.949Z : deleted Oracle GoldenGate parallel replicat process "RVRT3" zdm-hub: 2023-02-14T09:07:35.954Z : Execution of phase ZDM_RM_GG_REPLICAT_TGT completed zdm-hub: 2023-02-14T09:07:36.450Z : Executing phase ZDM_DELETE_SCHEMA_TRANDATA_SRC zdm-hub: 2023-02-14T09:07:38.417Z : stopped schema-level supplemental logging for source schema "DEMO" zdm-hub: 2023-02-14T09:07:38.420Z : Execution of phase ZDM_DELETE_SCHEMA_TRANDATA_SRC completed zdm-hub: 2023-02-14T09:07:38.935Z : Executing phase ZDM_RM_HEARTBEAT_SRC zdm-hub: 2023-02-14T09:07:38.950Z : Execution of phase ZDM_RM_HEARTBEAT_SRC completed zdm-hub: 2023-02-14T09:07:39.275Z : Executing phase ZDM_RM_CHECKPOINT_TGT zdm-hub: 2023-02-14T09:07:40.731Z : deleted the Oracle GoldenGate checkpoint table for database connection "domain1676364167565.tgtalias1676364167565" zdm-hub: 2023-02-14T09:07:40.752Z : Execution of phase ZDM_RM_CHECKPOINT_TGT completed zdm-hub: 2023-02-14T09:07:41.372Z : Executing phase ZDM_RM_HEARTBEAT_TGT zdm-hub: 2023-02-14T09:07:42.732Z : removed the heartbeat table for database connection "domain1676364167565.tgtalias1676364167565" zdm-hub: 2023-02-14T09:07:42.734Z : Execution of phase ZDM_RM_HEARTBEAT_TGT completed zdm-hub: 2023-02-14T09:07:43.101Z : Executing phase ZDM_CLEAN_GG_HUB zdm-hub: 2023-02-14T09:07:43.715Z : purged Oracle GoldenGate trail files zdm-hub: 2023-02-14T09:07:45.522Z : Execution of phase ZDM_CLEAN_GG_HUB completed zdm-hub: 2023-02-14T09:07:45.815Z : Executing phase ZDM_POST_ACTIONS zdm-hub: 2023-02-14T09:07:45.840Z : Execution of phase ZDM_POST_ACTIONS completed zdm-hub: 2023-02-14T09:07:46.141Z : Executing phase ZDM_CLEANUP_SRC zdm-hub: 2023-02-14T09:07:48.290Z : Cleaning up ZDM on the source node ora12c.sub12020731300.soormavcn.oraclevcn.com ... zdm-hub: 2023-02-14T09:07:49.554Z : Execution of phase ZDM_CLEANUP_SRC completed
If we query the ZDM job, we can see that all the phases have been marked as ‘COMPLETED’.
The online database migration and upgrade from Oracle 12c Release 2 to Oracle 21c using ZDM has been successfully completed!
[zdmuser@zdm-hub bin]$ ./zdmcli query job -jobid 14 zdm-hub.sub12020731300.soormavcn.oraclevcn.com: Audit ID: 74 Job ID: 14 User: zdmuser Client: zdm-hub Job Type: "MIGRATE" Scheduled job command: "zdmcli migrate database -sourcedb ora12c_syd126 -sourcenode ora12c.sub12020731300.soormavcn.oraclevcn.com -srcauth zdmauth -srcarg1 user:opc -srcarg2 identity_file:/home/zdmuser/.ssh/id_rsa -srcarg3 sudo_location:/usr/bin/sudo -targetnode ora21c.sub12020731300.soormavcn.oraclevcn.com -rsp /home/zdmuser/logical_migration.rsp -tgtauth zdmauth -tgtarg1 user:opc -tgtarg2 identity_file:/home/zdmuser/.ssh/id_rsa -tgtarg3 sudo_location:/usr/bin/sudo -pauseafter ZDM_MONITOR_GG_LAG" Scheduled job execution start time: 2023-02-14T08:35:53Z. Equivalent local time: 2023-02-14 08:35:53 Current status: SUCCEEDED Result file path: "/u02/zdmuser/zdmbase/chkbase/scheduled/job-14-2023-02-14-08:36:12.log" Excluded objects file path: "/u02/zdmuser/zdmbase/chkbase/scheduled/job-14-filtered-objects-2023-02-14T08:36:55.004.json" Job execution start time: 2023-02-14 08:36:13 Job execution end time: 2023-02-14 09:07:50 Job execution elapsed time: 24 minutes 0 seconds ZDM_VALIDATE_TGT ...................... COMPLETED ZDM_VALIDATE_SRC ...................... COMPLETED ZDM_SETUP_SRC ......................... COMPLETED ZDM_PRE_MIGRATION_ADVISOR ............. COMPLETED ZDM_VALIDATE_GG_HUB ................... COMPLETED ZDM_VALIDATE_DATAPUMP_SETTINGS_SRC .... COMPLETED ZDM_VALIDATE_DATAPUMP_SETTINGS_TGT .... COMPLETED ZDM_PREPARE_DATAPUMP_SRC .............. COMPLETED ZDM_DATAPUMP_ESTIMATE_SRC ............. COMPLETED ZDM_PREPARE_GG_HUB .................... COMPLETED ZDM_ADD_HEARTBEAT_SRC ................. COMPLETED ZDM_ADD_SCHEMA_TRANDATA_SRC ........... COMPLETED ZDM_CREATE_GG_EXTRACT_SRC ............. COMPLETED ZDM_PREPARE_DATAPUMP_TGT .............. COMPLETED ZDM_PARALLEL_EXPORT_IMPORT ............ COMPLETED ZDM_POST_DATAPUMP_SRC ................. COMPLETED ZDM_POST_DATAPUMP_TGT ................. COMPLETED ZDM_ADD_HEARTBEAT_TGT ................. COMPLETED ZDM_ADD_CHECKPOINT_TGT ................ COMPLETED ZDM_CREATE_GG_REPLICAT_TGT ............ COMPLETED ZDM_MONITOR_GG_LAG .................... COMPLETED ZDM_SWITCHOVER_APP .................... COMPLETED ZDM_RM_GG_EXTRACT_SRC ................. COMPLETED ZDM_RM_GG_REPLICAT_TGT ................ COMPLETED ZDM_DELETE_SCHEMA_TRANDATA_SRC ........ COMPLETED ZDM_RM_HEARTBEAT_SRC .................. COMPLETED ZDM_RM_CHECKPOINT_TGT ................. COMPLETED ZDM_RM_HEARTBEAT_TGT .................. COMPLETED ZDM_CLEAN_GG_HUB ...................... COMPLETED ZDM_POST_ACTIONS ...................... COMPLETED ZDM_CLEANUP_SRC ....................... COMPLETED
