\n| Oracle 19c (Proprietary Packages)<\/td>\n | KingbaseES V9<\/td>\n | None<\/td>\n | Complete Rewrite<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Note: The specific version compatibility matrix must be verified against the official KingbaseES documentation prior to planning. The table above illustrates general trends; exact version pairs and support levels require validation against the official matrix.<\/em><\/p>\nData Type Translation: Mapping Oracle Loopholes to KingbaseES Strictness<\/h2>\nOne of the most insidious risks in an Oracle database migration<\/strong> lies in data type mismatches. Oracle is historically permissive, allowing implicit conversions that can mask data loss or precision errors. KingbaseES, while highly compatible, enforces stricter type safety in many contexts. A direct schema dump often results in runtime errors or silent data truncation if these types are not explicitly mapped.<\/p>\nThe following table outlines the critical data type mappings. Developers must review these mappings during the schema conversion phase to ensure that precision, scale, and length parameters are preserved or adjusted appropriately.<\/p>\n \n\n\n| Oracle Data Type<\/th>\n | KingbaseES Equivalent<\/th>\n | Migration Consideration<\/th>\n<\/tr>\n<\/thead>\n | \n\nVARCHAR2(n)<\/code><\/td>\nVARCHAR(n)<\/code><\/td>\nDirect mapping. Ensure n<\/code> does not exceed KingbaseES limits.<\/td>\n<\/tr>\n\nNVARCHAR2(n)<\/code><\/td>\nVARCHAR(n)<\/code> (UTF-8)<\/td>\n| Ensure the target database character set is configured for UTF-8 to handle multibyte characters correctly.<\/td>\n<\/tr>\n | \nNUMBER(p, s)<\/code><\/td>\nNUMERIC(p, s)<\/code> or DECIMAL(p, s)<\/code><\/td>\nPrecision and scale must be preserved. Oracle’s NUMBER<\/code> without scale behaves differently than KingbaseES defaults in some arithmetic contexts.<\/td>\n<\/tr>\n\nCLOB<\/code><\/td>\nTEXT<\/code> or CLOB<\/code><\/td>\n| Direct mapping, but verify LOB storage parameters and chunk sizes for large object performance.<\/td>\n<\/tr>\n | \nBLOB<\/code><\/td>\nBYTEA<\/code><\/td>\nOracle BLOB<\/code> maps to BYTEA<\/code>. Ensure the application layer handles binary data correctly, as byte handling can differ.<\/td>\n<\/tr>\n\nDATE<\/code><\/td>\nTIMESTAMP<\/code><\/td>\nOracle DATE<\/code> includes time. KingbaseES TIMESTAMP<\/code> is the safer direct mapping to avoid time truncation.<\/td>\n<\/tr>\n\nTIMESTAMP WITH TIME ZONE<\/code><\/td>\nTIMESTAMPTZ<\/code><\/td>\n| Direct mapping, but verify timezone handling logic in the application layer.<\/td>\n<\/tr>\n | \nRAW(n)<\/code><\/td>\nBYTEA<\/code><\/td>\nEnsure the application does not rely on Oracle-specific RAW<\/code> string functions.<\/td>\n<\/tr>\n\nROWID<\/code><\/td>\nTEXT<\/code> or OID<\/code><\/td>\nROWID<\/code> is not directly supported as a primary key in KingbaseES. It must be converted to a string or a surrogate key.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nExample of Implicit Casting Risk:<\/strong>
\nIn Oracle, assigning a string '123.45'<\/code> to a NUMBER<\/code> column often succeeds with implicit conversion. In KingbaseES, if the column is strictly typed as INT<\/code>, this assignment will fail. During the schema conversion phase, run a validation script to identify columns where implicit casts are frequently used in the source application.<\/p>\nThe Refactoring Phase: Automating PL\/SQL Conversion and Identifying Manual Gaps<\/h2>\nThe core of a successful Oracle database migration<\/strong> is the handling of stored procedures, functions, and triggers. While KingbaseES provides a compatibility assessment tool to automate the translation of PL\/SQL code, it is not a magic wand. The tool handles standard syntax well but often flags complex logic, specific package bodies, or non-standard SQL constructs for manual review.<\/p>\nStep-by-Step Refactoring Procedure:<\/strong><\/p>\n\n- \n
Run Compatibility Assessment:<\/strong>
\nExecute the KingbaseES compatibility analysis tool against the exported Oracle DDL and PL\/SQL source code. This generates a report highlighting unsupported syntax, deprecated functions, and logic that requires manual intervention.<\/p>\n\n- Command Syntax:<\/em> Run the KingbaseES compatibility assessment tool using the syntax defined in the official documentation:
[Command Syntax]<\/code>.<\/li>\n- Note:<\/em> Verify the specific command flags and tool name in the official KingbaseES documentation, as tool naming conventions may vary.<\/li>\n<\/ul>\n<\/li>\n
- \n
Analyze the Conversion Report:<\/strong>
\nCategorize the findings into:<\/p>\n\n- Auto-Resolved:<\/strong> Standard
SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, DELETE<\/code>, and basic IF\/THEN<\/code> blocks.<\/li>\n- Semi-Auto:<\/strong> Functions requiring minor syntax adjustments (e.g.,
SYSDATE<\/code> to NOW()<\/code> or CURRENT_TIMESTAMP<\/code>).<\/li>\n- Manual Refactoring Required:<\/strong> Complex cursor logic, Oracle-specific packages (e.g.,
DBMS_OUTPUT<\/code>, UTL_FILE<\/code>), and custom exception handling.<\/li>\n<\/ul>\n<\/li>\n- \n
Manual Code Rewriting:<\/strong>
\nFor items flagged as "Manual Refactoring," developers must rewrite the logic. Common areas requiring attention include:<\/p>\n\n- Exception Handling:<\/strong> Oracle’s
EXCEPTION<\/code> block syntax is similar, but specific exception names and error codes may differ.<\/li>\n- Packages:<\/strong> Oracle packages often encapsulate state. KingbaseES uses functions and procedures without persistent package state in the same way.<\/li>\n
- Synonyms:<\/strong> Oracle synonyms must be replaced with KingbaseES views or direct schema references.<\/li>\n<\/ul>\n<\/li>\n
- \n
Compile and Test in Staging:<\/strong>
\nDeploy the converted code to a staging environment. Run the full suite of unit tests. Do not proceed to production until all compilation errors are resolved and logic tests pass.<\/p>\n<\/li>\n<\/ol>\nKey Distinction:<\/strong>
\nAutomated tools can translate syntax, but they cannot translate business logic intent. If a stored procedure relies on Oracle-specific optimizer hints or proprietary functions (e.g., DBMS_LOB<\/code> specific operations), a manual rewrite is mandatory.<\/p>\nData Movement Architecture: Offline Bulk Load vs. Incremental Sync<\/h2>\nOnce the schema and code are validated, the next critical decision is the data movement strategy. The choice between an offline bulk load and an online incremental sync depends on the acceptable downtime window and the volume of data.<\/p>\n Strategy A: Offline Bulk Load (Native Utilities)<\/strong>
\nThis approach involves stopping the source application, performing a full export\/import, and then restarting.<\/p>\n\n- Pros:<\/strong> Simplest to implement, lowest risk of data inconsistency during the transfer, no need for complex synchronization tools.<\/li>\n
- Cons:<\/strong> Requires a maintenance window (downtime).<\/li>\n
- Use Case:<\/strong> Ideal for systems with low transaction frequency or where a maintenance window is acceptable.<\/li>\n
- Tools:<\/strong> KingbaseES native utilities (e.g.,
kdb<\/code> or ksql<\/code> based import tools).<\/li>\n- Performance:<\/strong> Highly optimized for large data sets; throughput rates depend on hardware and network conditions.<\/li>\n<\/ul>\n
Strategy B: Online Incremental Sync<\/strong>
\nThis approach involves an initial full load followed by continuous synchronization of changes (CDC) until the cutover.<\/p>\n\n- Pros:<\/strong> Minimal downtime; the application can remain online until the final switch.<\/li>\n
- Cons:<\/strong> Higher complexity, requires additional infrastructure (log capture, sync agents), and introduces a risk of data divergence if the sync process fails.<\/li>\n
- Use Case:<\/strong> Critical systems requiring minimal downtime.<\/li>\n
- Tools:<\/strong> Third-party ETL tools or standard Postgres-based replication methods. Note: Native replication features for CDC vary by version and licensing; verify availability in the official documentation or consider third-party solutions.<\/em><\/li>\n<\/ul>\n
Decision Matrix:<\/strong><\/p>\n\n\n\n| Factor<\/th>\n | Offline Bulk Load<\/th>\n | Online Incremental Sync<\/th>\n<\/tr>\n<\/thead>\n | \n\nDowntime<\/strong><\/td>\n| High (Maintenance Window)<\/td>\n | Minimal (depending on data volume and network conditions)<\/td>\n<\/tr>\n | \nComplexity<\/strong><\/td>\n| Low<\/td>\n | High<\/td>\n<\/tr>\n | \nData Consistency<\/strong><\/td>\n| High (Atomic)<\/td>\n | Requires rigorous validation<\/td>\n<\/tr>\n | \nCost<\/strong><\/td>\n| Low<\/td>\n | Higher (Tooling\/Infrastructure)<\/td>\n<\/tr>\n | \nRisk<\/strong><\/td>\n| Low (Simple)<\/td>\n | Moderate (Sync lag, conflicts)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n For enterprises, a hybrid approach is often recommended: perform a full offline load during a scheduled maintenance window, then use incremental sync for a short period to capture final changes before the final cutover.<\/p>\n Validation Protocol: Ensuring Data Integrity<\/h2>\nBefore the final cutover, you must mathematically verify data integrity between the source Oracle and the target KingbaseES. Relying on row counts alone is insufficient; you must validate content, checksums, and data types.<\/p>\n Validation Checklist:<\/strong><\/p>\n\n- \n
Row Count Verification:<\/strong>
\nExecute a COUNT(*)<\/code> on all major tables in both Oracle and KingbaseES. Discrepancies must be investigated immediately.<\/p>\n-- Oracle\nSELECT COUNT(*) FROM employees;\n-- KingbaseES\nSELECT COUNT(*) FROM employees;\n<\/code><\/pre>\n<\/li>\n- \n
Checksum Validation:<\/strong>
\nFor critical tables, calculate checksums of the data content.<\/p>\n-- KingbaseES (Example using aggregate functions)\n-- Note: Use KingbaseES-specific hash functions as documented in the official reference.\nSELECT [HASH_FUNCTION](CAST(col1 AS TEXT) || CAST(col2 AS TEXT)) FROM critical_table;\n<\/code><\/pre>\n<\/li>\n- \n
Data Type and Precision Check:<\/strong>
\nSample random rows from both systems and compare the raw values. Pay special attention to NUMBER<\/code> precision, DATE<\/code> formats, and CLOB<\/code> content.<\/p>\n-- Compare a specific record\nSELECT id, col1, col2 FROM critical_table WHERE id = 12345;\n<\/code><\/pre>\n<\/li>\n- \n
Null vs. Empty String:<\/strong>
\nVerify how the system handles NULL<\/code> values versus empty strings (''<\/code>). Oracle treats NULL<\/code> and ''<\/code> differently in some contexts, while KingbaseES may normalize them. Ensure the application logic handles this correctly.<\/p>\n<\/li>\n- \n
Index and Constraint Validation:<\/strong>
\nVerify that all indexes are created and constraints (Primary Key, Foreign Key, Unique) are active in KingbaseES.<\/p>\n-- KingbaseES\nSELECT indexname FROM pg_indexes WHERE tablename = 'critical_table';\n<\/code><\/pre>\n<\/li>\n- \n
Application Integration Test:<\/strong>
\nRun a subset of the application’s read\/write operations against the KingbaseES database in a staging environment. Ensure that the application can successfully read and write data without errors.<\/p>\n<\/li>\n<\/ul>\nFailure Analysis: Troubleshooting Common KingbaseES Migration Errors<\/h2>\nEven with rigorous planning, migration failures can occur. A "break-fix" approach is essential. Below are common error scenarios encountered during an Oracle database migration<\/strong> and their typical remediation steps.<\/p>\nError 1: PL\/SQL<\/code> Compilation Failure (Invalid Identifier)<\/strong><\/p>\n\n- Symptom:<\/strong> Stored procedure fails to compile with
ORA-00942: table or view does not exist<\/code> or similar.<\/li>\n- Cause:<\/strong> Schema object names (e.g., synonyms) were not correctly mapped, or the object resides in a different schema in KingbaseES.<\/li>\n
- Remediation:<\/strong>\n
\n- Check the schema ownership of the referenced objects.<\/li>\n
- Update the PL\/SQL code to use fully qualified names (
schema_name.object_name<\/code>).<\/li>\n- Re-create synonyms if necessary.<\/li>\n<\/ol>\n<\/li>\n<\/ul>\n
Error 2: Sequence Generation Failure<\/strong><\/p>\n\n- Symptom:<\/strong>
ORA-08004<\/code> equivalent or sequence does not exist<\/code> errors.<\/li>\n- Cause:<\/strong> Oracle sequences may have different increment or cache settings that are not supported or configured correctly in KingbaseES.<\/li>\n
- Remediation:<\/strong>\n
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
![\"Abstract](\"https:\/\/kingbase-bbs.oss-cn-beijing.aliyuncs.com\/qywx\/blogImage\/eac46a6e-8844-4538-83f6-877cdcdd3ba0.png\")
<\/p>\n