Offline Capture and Replay Architecture in SQL Developer Migration Workbench

Problem and Takeaway

Organizations migrating heterogeneous databases to Oracle often require an air-gapped workflow to satisfy compliance network restrictions or maintenance windows. SQL Developer's Migration Workbench addresses this with an offline capture mode that serializes source metadata to disk enables conversion without a live source connection and produces generation scripts for later review and deployment. The practical takeaway is that the three-phase pipeline capture convert generate provides a verifiable auditable path when live access is unavailable but success depends on understanding the model's trust boundaries size constraints and conversion coverage limits.

Architecture Note

Requirements

Enable heterogeneous database migration SQL Server MySQL PostgreSQL and others to Oracle with minimal downtime preserving schema fidelity data integrity and procedural logic translation. Support both online live and offline file-based workflows. The offline mode must serialize the capture model to disk allow air-gapped conversion and audit before execution and produce generation scripts that can be reviewed and deployed without a persistent network connection to the source.

Smallest Suitable Design

A three-phase pipeline forms the minimal design: capture convert and generate. During capture source metadata is reverse-engineered into a platform-neutral XML representation. The convert phase maps source types rewrites PL/SQL equivalents and generates Oracle-ready DDL. The generate phase produces executable scripts. In offline mode the captured model is saved to a file enabling the subsequent phases to run disconnected from the source database.

Trust and Data Boundaries

Source credentials never leave the capture phase; the offline model contains only metadata not raw data. Conversion runs locally without network access. Generated scripts are reviewed before deployment. The tool signs capture models with a hash SHA-256 to detect tampering or corruption. This boundary ensures that sensitive connection details are not persisted in the portable model.

Operational Checks

1. Pre-migration validation reports that list object counts type mismatches and unsupported features.
2. Post-generation script dry-run on a target test schema to observe execution behavior.
3. Row-count reconciliation after any data move to verify that migrated rows match source expectations.
4. PL/SQL unit-test harness execution for converted packages to validate procedural logic.

Failure Modes

• Capture fails on unsupported source types such as CLR assemblies in SQL Server -- mitigated by extensible type-mapping plugins that can be extended for custom assemblies.
• Conversion produces semantically incorrect PL/SQL for complex T-SQL constructs including dynamic SQL cursors and temporary tables -- mitigated by rule-based rewrites and manual override annotations placed in the generated model.
• Offline model drift if the source schema changes after capture -- mitigated by version-stamped capture snapshots that record the source schema version and timestamp.

Design Change Triggers

Several conditions can shift the design away from the current three-phase offline model: Oracle Autonomous Database requiring cloud-only deployment which shifts capture to online-only mode. Deprecation of the JDBC-ODBC bridge forcing the use of native JDBC drivers for each source vendor. New source database versions introducing unsupported DDL requiring updates to the capture parser. Regulatory mandates for encryption-at-rest of offline models which adds an envelope-encryption layer around the serialized XML.

Concrete Example: Pilot Migration of a Sample Schema

Consider a pilot migration of the sales schema from Microsoft SQL Server 2022 to Oracle Database 21c using SQL Developer 23.4 offline mode. Capture phase: The capture connects via the Microsoft JDBC Driver 12.x reverse-engineers the source metadata and serializes it to sales-migration.xml. A SHA-256 hash is computed and stored in sales-migration.sha256. Typical file size for a moderate schema ranges from 1 MB to 5 MB; large schemas can grow to hundreds of megabytes. Convert phase: The offline model is processed locally. Type mappings translate SQL Server data types to Oracle equivalents for example NVARCHAR2 maps to VARCHAR2 DATETIME2 maps to TIMESTAMP. PL/SQL conversion applies rule-based rewrites for T-SQL constructs; approximately 80% of common patterns are automated while complex dynamic SQL and cursor logic may require manual override annotations. Generate phase: The generate phase produces Oracle-ready DDL and data-manipulation scripts. These scripts are reviewed on a staging Oracle 19c/21c test pluggable database. A dry-run execution is performed and the built-in Migration Validation report flags any unconverted objects. After the scripts run on the test schema the model's integrity is confirmed by comparing the recomputed sha256sum sales-migration.xml to the stored value in sales-migration.sha256 and the workflow proceeds to production review.

Limitations and Practical Verification

Offline capture models for large schemas can exceed available RAM during XML parsing; ensuring sufficient disk space and memory is a prerequisite before initiating capture. Automatic PL/SQL conversion covers roughly 80% of common T-SQL patterns; complex dynamic SQL temporary tables and cursor-based logic often require manual rewrite. The SQL Developer UI hides advanced tuning knobs such as parallel capture degree and custom type-map configurations which are critical for performance at scale. To verify the workflow follow these steps: Run a pilot migration of a representative schema subset using the offline capture to convert to generate flow compare object counts and DDL against the source. Execute the generated scripts on an Oracle 19c or 21c test instance and run the built-in Migration Validation report to flag unconverted objects. Verify offline model integrity by checking the SHA-256 hash stored in the capture manifest against a recomputed hash of the XML file. Confirm that the JDBC driver version used for capture matches the vendors supported matrix for example Microsoft JDBC 12.x for SQL Server 2022.