{"id":1278,"date":"2026-09-18T01:23:21","date_gmt":"2026-09-18T01:23:21","guid":{"rendered":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/automated-oracle-to-kingbase-migration-step-by-step-guide\/"},"modified":"2026-09-18T01:23:21","modified_gmt":"2026-09-18T01:23:21","slug":"automated-oracle-to-kingbase-migration-step-by-step-guide","status":"publish","type":"post","link":"https:\/\/www.kingbaseglobal.com\/blog\/tech-blog\/automated-oracle-to-kingbase-migration-step-by-step-guide\/","title":{"rendered":"Automated Oracle to Kingbase Migration_ Step-by-Step Guide"},"content":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/kingbase-bbs.oss-cn-beijing.aliyuncs.com\/qywx\/blogImage\/3513d461-7131-46b9-b08f-e23f8c67f7c4.webp\" alt=\"A precision torque wrench resting beside a blank checklist on a linen surface, illuminated by soft side light, symbolizing the rigorous validation and manual verification steps req\" \/><\/p>\n<h2>Prerequisites: Version Constraints and Commercial Licensing<\/h2>\n<p>Successful execution of an <strong>automated Oracle to KingbaseES migration<\/strong> begins with a rigorous assessment of the target environment. The process relies on specific commercial software versions and a clear understanding of the tooling landscape. Misidentifying the product nature or version compatibility is a primary cause of project failure.<\/p>\n<h3>Environment Checklist<\/h3>\n<ul>\n<li><strong>Target Database:<\/strong> Install <strong>KingbaseES<\/strong> V8 or the upcoming V009R002C012 release (scheduled for July 31, 2025). The V009 release specifically addresses complex PL\/SQL compatibility issues found in earlier versions.<\/li>\n<li><strong>Source Database:<\/strong> Oracle 11g, 12c, or 19c. While <strong>KingbaseES<\/strong> V8 offers high compatibility, specific version mapping requires verification against the official compatibility matrix.<\/li>\n<li><strong>Migration Tooling:<\/strong> Utilize the <strong>KingbaseES<\/strong> intelligent data migration tool included in the V8 distribution. For real-time synchronization, configure <strong>KFS<\/strong> (FlySync).<\/li>\n<li><strong>Software Identity:<\/strong> Acknowledge that <strong>KingbaseES<\/strong> is commercial software. It is not open-source or community-supported. This distinction impacts licensing, support channels, and the availability of proprietary migration utilities.<\/li>\n<li><strong>Network:<\/strong> Ensure bidirectional network connectivity between the Oracle source and <strong>KingbaseES<\/strong> target for schema extraction and data replication.<\/li>\n<li><strong>Permissions:<\/strong> The migration user requires <code>SELECT<\/code> privileges on source system views and <code>CREATE<\/code>, <code>ALTER<\/code>, <code>INSERT<\/code> privileges on the target database.<\/li>\n<li><strong>Regulatory Compliance (Malaysia):<\/strong> Verify that the target <strong>KingbaseES<\/strong> deployment location complies with Malaysia&#8217;s Personal Data Protection Act (PDPA) regarding data residency. Ensure data remains in approved regions or jurisdictions as required by local counsel.<\/li>\n<\/ul>\n<p>Do not proceed without verifying the specific version of <strong>KingbaseES<\/strong> against the source Oracle version. The V009R002C012 update introduced enhancements for <code>ANYDATASET<\/code>, <code>CONCAT<\/code>, and <code>TIMESTAMPADD<\/code> functions, which directly impact the success rate of PL\/SQL conversion.<\/p>\n<h2>The Dual-System Architecture: Leveraging FlySync (KFS) for High Availability<\/h2>\n<p>A &quot;big bang&quot; cutover poses significant risk of data loss and extended downtime. The recommended strategy for an <strong>automated Oracle to KingbaseES migration<\/strong> is a phased Dual-System Architecture. This approach utilizes <strong>KFS<\/strong> (FlySync) to maintain real-time data consistency before the final switch.<\/p>\n<h3>Phase 1: Parallel Run and Synchronization<\/h3>\n<ol>\n<li>Deploy <strong>KingbaseES<\/strong> as a secondary system alongside the existing Oracle production environment.<\/li>\n<li>Configure <strong>KFS<\/strong> to replicate historical data and incremental changes from Oracle to <strong>KingbaseES<\/strong> in real time.<\/li>\n<li>Allow the system to run in parallel for a defined period. This validates the data synchronization stability and the performance of the target database under load.<\/li>\n<li>During this phase, application read traffic can be directed to <strong>KingbaseES<\/strong> to test query performance without affecting the write-heavy Oracle primary.<\/li>\n<\/ol>\n<h3>Phase 2: Cutover Preparation<\/h3>\n<ol>\n<li>Monitor the replication lag between Oracle and <strong>KingbaseES<\/strong>. Ensure the lag is within an acceptable threshold (e.g., &lt; 1 second) for the duration of the cutover window. <em>Note: Specific lag thresholds should be validated against your workload requirements.<\/em><\/li>\n<li>Stop write operations on the Oracle source to achieve a consistent state.<\/li>\n<li>Verify that the final delta has been fully synchronized to <strong>KingbaseES<\/strong>.<\/li>\n<li>Switch the application write traffic to <strong>KingbaseES<\/strong>.<\/li>\n<\/ol>\n<p>This architecture minimizes downtime to the duration required for the final delta replication and cutover. It also provides a safety net; if issues arise in <strong>KingbaseES<\/strong>, the Oracle system remains fully operational with up-to-date data.<\/p>\n<h2>Automated Schema Conversion: Navigating PL\/SQL Compatibility<\/h2>\n<p>The <strong>KingbaseES<\/strong> migration tool automates the conversion of standard SQL and basic PL\/SQL structures. The remaining portion involves complex logic that requires manual intervention. Understanding this boundary prevents unrealistic expectations of a fully automated process.<\/p>\n<h3>Step 1: Schema Extraction and Analysis<\/h3>\n<ol>\n<li>Connect the migration tool to the Oracle source.<\/li>\n<li>Execute the schema extraction command. <em>Note: Specific CLI syntax, flags, and parameters must be verified against the official KingbaseES documentation as they are not provided in this guide.<\/em><\/li>\n<li>Review the compatibility report generated by the tool. This report highlights features that may not have direct equivalents in <strong>KingbaseES<\/strong>.<\/li>\n<\/ol>\n<h3>Step 2: Handling Complex PL\/SQL<\/h3>\n<p><strong>KingbaseES<\/strong> V009R002C012 has enhanced support for specific Oracle features, reducing the manual workload.<\/p>\n<ul>\n<li><strong>Supported Features:<\/strong> The tool now supports <code>NEW<\/code> initialization for nested tables and variable arrays. Functions declared with <code>PARALLEL_ENABLE<\/code> are also supported.<\/li>\n<li><strong>Manual Refactoring Required:<\/strong> Complex Oracle-specific functions, advanced nested table manipulations, or proprietary system views may still fail automated conversion.<\/li>\n<li><strong>Action:<\/strong> Identify the flagged procedures in the compatibility report. Refactor these specific blocks manually to ensure syntax correctness in <strong>KingbaseES<\/strong>.<\/li>\n<\/ul>\n<h3>Step 3: Data Type Mapping Validation<\/h3>\n<ol>\n<li>Verify the mapping of Oracle data types (e.g., <code>VARCHAR2<\/code>, <code>NUMBER<\/code>, <code>CLOB<\/code>) to <strong>KingbaseES<\/strong> equivalents.<\/li>\n<li>Check for any precision or scale mismatches in <code>NUMBER<\/code> types.<\/li>\n<li>Validate <code>DATE<\/code> and <code>TIMESTAMP<\/code> handling, noting that <strong>KingbaseES<\/strong> V009 supports <code>TIMESTAMPADD<\/code> for improved date arithmetic compatibility.<\/li>\n<\/ol>\n<h3>Step 4: Execution of Conversion<\/h3>\n<ol>\n<li>Run the automated conversion job. <em>Note: Specific command syntax must be verified against vendor documentation.<\/em><\/li>\n<li>Inspect the generated DDL scripts in <strong>KingbaseES<\/strong>.<\/li>\n<li>Compile the converted objects to check for syntax errors. Resolve any compilation failures before proceeding to data loading.<\/li>\n<\/ol>\n<h2>The Validation Gate: Vendor-Neutral Integrity Checks Before Cutover<\/h2>\n<p>Before authorizing the final cutover, you must verify data integrity. Relying solely on the migration tool&#8217;s success message is insufficient. A rigorous validation protocol using vendor-neutral methods ensures zero data loss.<\/p>\n<h3>Data Integrity Validation Checklist<\/h3>\n<ul>\n<li><strong>Row Count Comparison:<\/strong>\n<ul>\n<li>Execute a <code>SELECT COUNT(*)<\/code> on every table in the Oracle source.<\/li>\n<li>Execute the same query on the corresponding table in <strong>KingbaseES<\/strong>.<\/li>\n<li>The counts must match exactly.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Checksum Validation:<\/strong>\n<ul>\n<li>Generate a checksum for critical data columns using a standard hashing function (e.g., MD5 or SHA-256) on the source.<\/li>\n<li>Replicate the hash generation logic in <strong>KingbaseES<\/strong> for the target columns. <em>Note: Ensure the hashing function syntax is compatible with the KingbaseES dialect.<\/em><\/li>\n<li>Compare the resulting hash values.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Sample Data Verification:<\/strong>\n<ul>\n<li>Randomly select 1,000 records from the source.<\/li>\n<li>Verify the content, data types, and nullability of these records in the target.<\/li>\n<li>Pay special attention to special characters, large objects (LOBs), and binary data.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Constraint and Index Verification:<\/strong>\n<ul>\n<li>Confirm that Primary Keys, Foreign Keys, and Unique Constraints are active and enforced in <strong>KingbaseES<\/strong>.<\/li>\n<li>Verify that indexes are created and populated correctly.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Trigger and Sequence Validation:<\/strong>\n<ul>\n<li>Test triggers by inserting a test record in <strong>KingbaseES<\/strong> and verifying the expected side effects.<\/li>\n<li>Verify that sequence values are continuous and not reset.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>If any discrepancy is found, halt the cutover process immediately. Investigate the root cause using the migration logs and <strong>KingbaseES<\/strong> system views.<\/p>\n<h2>Failure Scenarios: Rollback Procedures for Syntax Errors and Data Mismatches<\/h2>\n<p>Despite rigorous planning, automated conversion can generate syntax errors or data mismatches. A defined rollback strategy is mandatory to revert to the Oracle source system without data corruption.<\/p>\n<h3>Rollback Procedure<\/h3>\n<ol>\n<li><strong>Identify the Failure Point:<\/strong>\n<ul>\n<li>Determine if the failure is in schema conversion (syntax errors) or data integrity (mismatched values).<\/li>\n<li>If the failure occurs during the cutover window, the application must be switched back to Oracle immediately.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Revert Application Traffic:<\/strong>\n<ul>\n<li>Update the application connection strings to point back to the Oracle source.<\/li>\n<li>Verify that the application can write to Oracle without latency or errors.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Data Recovery:<\/strong>\n<ul>\n<li>If the <strong>KingbaseES<\/strong> migration introduced incorrect data, do not attempt to sync it back to Oracle.<\/li>\n<li>Use the Oracle backup taken prior to the cutover to restore the database to a consistent state.<\/li>\n<li>If the failure was due to schema errors, revert the application to use the original Oracle stored procedures.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Log Analysis:<\/strong>\n<ul>\n<li>Collect all migration logs and <strong>KingbaseES<\/strong> error logs.<\/li>\n<li>Document the specific error codes and the affected objects.<\/li>\n<li>This analysis is critical for the next migration attempt.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Re-evaluate the Plan:<\/strong>\n<ul>\n<li>If the error was due to unsupported PL\/SQL features, allocate time for manual refactoring.<\/li>\n<li>If the error was due to version incompatibility, consider upgrading to <strong>KingbaseES<\/strong> V009R002C012 or later.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p><strong>Critical Note:<\/strong> There is no single &quot;rollback command&quot; for the entire migration. Rollback involves restoring the Oracle database from backup and reconfiguring the application connection.<\/p>\n<h2>Final Cutover: The Go\/No-Go Decision Matrix<\/h2>\n<p>The final decision to switch from Oracle to <strong>KingbaseES<\/strong> as the primary system must be based on objective validation results. Use the following matrix to authorize the cutover.<\/p>\n<table>\n<thead>\n<tr>\n<th style=\"text-align:left\">Decision Criteria<\/th>\n<th style=\"text-align:left\">Condition for &quot;Go&quot;<\/th>\n<th style=\"text-align:left\">Condition for &quot;No-Go&quot;<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align:left\"><strong>Data Integrity<\/strong><\/td>\n<td style=\"text-align:left\">Row counts and checksums match across all critical tables.<\/td>\n<td style=\"text-align:left\">Any discrepancy in row counts or checksums.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Schema Compilation<\/strong><\/td>\n<td style=\"text-align:left\">All converted stored procedures, triggers, and functions compile without errors.<\/td>\n<td style=\"text-align:left\">Any compilation errors in critical business logic.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Replication Lag<\/strong><\/td>\n<td style=\"text-align:left\">Replication lag is within the defined threshold for 24 hours.<\/td>\n<td style=\"text-align:left\">Replication lag exceeds the threshold or is unstable.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Application Testing<\/strong><\/td>\n<td style=\"text-align:left\">Load testing shows acceptable performance and no application errors.<\/td>\n<td style=\"text-align:left\">Performance degradation or application errors during load testing.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>Rollback Drill<\/strong><\/td>\n<td style=\"text-align:left\">Successful rollback to Oracle was performed and verified within the defined window.<\/td>\n<td style=\"text-align:left\">Rollback drill failed or took longer than the allowed maintenance window.<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align:left\"><strong>PL\/SQL Coverage<\/strong><\/td>\n<td style=\"text-align:left\">Manual refactoring is complete for all flagged complex PL\/SQL objects.<\/td>\n<td style=\"text-align:left\">Unresolved PL\/SQL logic remains in the target system.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Authorization:<\/strong><br \/>\nProceed with the cutover only if all criteria in the &quot;Go&quot; column are met. If any &quot;No-Go&quot; condition is present, pause the project, address the specific issue, and repeat the validation steps.<\/p>\n<h2>FAQ<\/h2>\n<h3>What are the specific prerequisites and version constraints for running an automated Oracle to KingbaseES migration?<\/h3>\n<p>You need <strong>KingbaseES<\/strong> V8 or V009R002C012 installed. The source must be a supported Oracle version (11g, 12c, or 19c). Verify the specific compatibility matrix with the vendor, as V009R002C012 offers enhanced support for complex PL\/SQL features like <code>ANYDATASET<\/code> and <code>PARALLEL_ENABLE<\/code>.<\/p>\n<h3>How can I verify data integrity and handle rollback scenarios if the automated conversion fails?<\/h3>\n<p>Verify integrity using row counts, checksums, and sample data comparisons. Do not rely on vendor-specific commands unless documented. For rollback, switch application traffic back to Oracle and restore the Oracle database from a pre-cutover backup. There is no automated &quot;undo&quot; command for the entire migration.<\/p>\n<h3>What is the expected level of manual refactoring required for complex Oracle PL\/SQL stored procedures?<\/h3>\n<p>While the tool automates a significant portion of standard SQL, a portion of complex logic often requires manual intervention. Features like nested tables, variable arrays, and parallel functions have improved support in V009, but proprietary Oracle functions may still need rewriting.<\/p>\n<h3>What are the common failure modes in automated schema conversion and how are they mitigated?<\/h3>\n<p>Common failures include syntax errors in converted PL\/SQL, data type mismatches, and unsupported system views. Mitigation involves running the compatibility report, manually refactoring flagged code, and validating data types before loading.<\/p>\n<h3>Does KingbaseES support real-time synchronization with Oracle to ensure high availability?<\/h3>\n<p>Yes, <strong>KingbaseES<\/strong> supports real-time data synchronization with Oracle using <strong>KFS<\/strong> (FlySync). This allows for a dual-system architecture where data is replicated in real time, enabling a cutover with minimal downtime.<\/p>\n<hr \/>\n<p><strong>\ud83d\udca1 More Resources<\/strong><\/p>\n<p>If you would like to dive deeper into KingbaseES and its application practices across various industries, we have compiled the following official resources to help you get started quickly and develop and operate with efficiency:<\/p>\n<ul>\n<li><a href=\"https:\/\/bbs.kingbase.com.cn\/\">Kingbase Community<\/a>: A one-stop interactive platform for technical exchanges, Q&amp;A, and experience sharing\u2014join forces with fellow DBAs and developers.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Solution-Oracle.html\">Kingbase Solutions<\/a>: One-stop full-stack database migration and cloud-native solutions, supporting smooth migration of multi-source heterogeneous data, ensuring high availability, real-time integration, and sustained high performance.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Customers.html\">Kingbase Case Studies<\/a>: Real-world user scenarios and implementation outcomes, showcasing KingbaseES&#8217;s outstanding capabilities in high availability, high performance, and IT adaptation.<\/li>\n<li><a href=\"https:\/\/docs.kingbase.com.cn\/en\">Kingbase Documentation<\/a>: Authoritative and comprehensive product manuals and technical guides, covering the entire lifecycle from installation and deployment to development, programming, and operations management.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/Download.html\">Free Download<\/a>: Get the latest installation packages, drivers, tools, and patches, supporting multiple platforms and domestic chip architectures.<\/li>\n<li><a href=\"https:\/\/www.kingbaseglobal.com\/blog\/\">Digital Construction Encyclopedia<\/a>: Covers digital strategy planning, data integration, metrics management, database visualization applications, and more to empower enterprise digital transformation.<\/li>\n<\/ul>\n<p><strong>Open Source Resources:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/github.com\/hgsandy\/Kingbase-docs\">GitHub &#8211; Kingbase-docs<\/a>: Kingbase documentation open-source repository\u2014Stars and contributions are welcome.<\/li>\n<li><a href=\"https:\/\/gitee.com\/hgsandy\/kingbase-docs\">Gitee &#8211; Kingbase-docs<\/a>: Domestic mirror repository for Kingbase documentation for faster access.<\/li>\n<\/ul>\n<p>Welcome to explore the resources above and begin your Kingbase journey!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Prerequisites: Version Constraints and Commercial Licensing Successful execution of an automated Oracle to KingbaseES migration begins with a rigorous assessment of the target environment. The process relies on specific commercial&#8230;<\/p>\n","protected":false},"author":295,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"meta_description":"","_kingbase_seo_description":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-1278","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/1278","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/users\/295"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/comments?post=1278"}],"version-history":[{"count":0,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/posts\/1278\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/media?parent=1278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/categories?post=1278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kingbaseglobal.com\/blog\/wp-json\/wp\/v2\/tags?post=1278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}