Enterprise Oracle Database Alternative_ A Technical

Prerequisites: Validating the ‘Commercial Parity’ Gap Before Migration

A common failure mode in enterprise migrations involves deploying a non-Oracle database that lacks specific PL/SQL extensions required by legacy applications. Consider a scenario where an enterprise attempts to migrate a complex financial reporting system. The application relies on NEW initialization for nested tables, %ROWTYPE parameter matching, and DETERMINISTIC function declarations. Standard migration attempts often fail because the target database treats these as unsupported syntax, forcing a complete rewrite of stored procedures.

This tutorial assumes you are evaluating KingbaseES as a commercial alternative to Oracle database. To proceed with a migration that minimizes code refactoring, you must verify that your target environment supports the specific compatibility layer required for your workload. The following prerequisites are mandatory for the V009R002C012 version, which introduces the necessary enhancements for Oracle compatibility.

Environment and Version Checklist

• Target Product Version: KingbaseES V009R002C012. Earlier versions may lack support for LISTAGG with WITH GROUP, ANYDATASET collection types, or specific package expansion capabilities.
• Source System: Oracle Database (any supported version running the legacy PL/SQL code).
• Support Model: A valid commercial support contract is required. KingbaseES is commercial software, not open-source. This contract defines the terms for incident response and migration assistance.
• Hardware Requirements: Servers must meet the general performance specifications for KingbaseES V009R002C012. Ensure CPU and memory resources are sufficient for running both the Oracle primary and KingbaseES backup in a dual-track parallel running configuration.
• Network Configuration: Low-latency connectivity between the Oracle and KingbaseES instances is required for FlySync (KFS) real-time synchronization.
• Migration Tools: Access to the KingbaseES migration toolkit, specifically KDMS (assessment) and KDTS (one-click migration).

Critical Compatibility Constraints

Before initiating the technical steps, verify that your application logic does not rely on Oracle-specific features outside the supported compatibility list. While KingbaseES V009R002C012 supports a wide range of Oracle features, unsupported extensions will still require manual intervention.

• Supported PL/SQL Enhancements:
  • NEW initialization for nested tables and variable arrays.
  • Automatic matching of %ROWTYPE parameters.
  • DETERMINISTIC keyword for function optimization.
  • PARALLEL_ENABLE clause for concurrent function execution.
  • Package expansion supporting nearly 10,000 functions.
• Supported SQL Enhancements:
  • LISTAGG with WITH GROUP clause.
  • ANYDATASET collection type.
  • CONCAT function with multiple parameters.
  • TIMESTAMPADD and multi-format TO_TIMESTAMP.
• Supported System Views:
  • V$VERSION, V$SESSION, V$LOCKED_OBJECT.
  • ALL_PART_INDEXES, DBA__PART_INDEXES, USER_PART_INDEXES.

If your workload depends on features not listed above, the migration will require code modification. This checklist ensures you are evaluating a commercial-grade solution capable of handling the specific architectural guarantees of your Oracle environment.

Step 1: Automated Assessment with KDMS to Quantify Refactoring Risk

Manual review of thousands of stored procedures is prone to error and time-consuming. The first technical step is to use the Kingbase Database Migration Assessment System (KDMS) to generate an objective complexity report. This tool analyzes the Oracle source database and identifies syntax incompatibilities, helping you estimate the effort required for the migration.

Prerequisites for KDMS Execution

• Install the KDMS assessment tool on a management server with network access to the Oracle source.
• Ensure you have read-only database credentials for the Oracle source schema.
• Verify that the KDMS version is compatible with the KingbaseES target version (V009R002C012).

Execution Procedure

1. Initialize the Assessment: Launch the KDMS interface. Select "Oracle" as the source database type and "KingbaseES" as the target.
2. Configure Connection: Enter the Oracle connection details (host, port, service name, username, password).
3. Select Objects: Choose the specific schemas, tables, or stored procedures you wish to assess. For a full migration, select all relevant objects.
4. Run Analysis: Start the assessment job. KDMS will parse the PL/SQL code and compare it against the KingbaseES V009R002C012 compatibility matrix.
5. Review the Report: The tool generates a report categorizing objects into:
   • Compatible: Objects that can be migrated with zero code changes.
   • Warning: Objects that require minor adjustments or have unsupported syntax.
   • Error: Objects that cannot be migrated automatically and require manual refactoring.

Interpreting the Output

The report will highlight specific issues, such as a stored procedure using a syntax not yet supported in the target version. For example, if your code uses NEW initialization, the report should confirm compatibility under V009R002C012. If the report flags an error, note the specific object name and the nature of the incompatibility.

Validation Check

• Verify that the assessment report lists the specific PL/SQL features you rely on (e.g., DETERMINISTIC, %ROWTYPE) as "Compatible."
• Confirm that the tool recognizes the Oracle system views used by your monitoring applications.

This step provides the baseline data needed to plan the migration. It prevents the assumption that a migration is seamless without verifying the specific syntax dependencies of your application.

Step 2: Configuring FlySync (KFS) for Real-Time Dual-Track Synchronization

To minimize downtime and risk, you must implement a dual-track parallel running architecture. In this phase, the Oracle database remains the primary system for writes, while KingbaseES acts as a backup that receives real-time data updates. This ensures data consistency and allows for an immediate rollback if issues arise.

Architecture Overview

• Phase 1: Oracle is the Primary (Read/Write). KingbaseES is the Backup (Read/Write for queries, Sync from Oracle).
• Tool: FlySync (KFS) handles the data synchronization.

Configuration Steps

1. Deploy FlySync (KFS): Install the FlySync agent on both the Oracle and KingbaseES servers.
2. Define Source and Target:
   • Source: Oracle Database instance.
   • Target: KingbaseES instance.
3. Configure Synchronization Rules:
   • Select the schemas to synchronize.
   • Define the data types to be mapped. FlySync supports heterogeneous migration scenarios, including cross-region setups.
   • Ensure that the synchronization mode is set to "Real-Time" to maintain consistency.
4. Initialize Synchronization: Start the initial data sync. This copies the existing data from Oracle to KingbaseES to establish a baseline.
5. Enable Incremental Sync: Once the initial copy is complete, enable incremental synchronization. This captures changes made to the Oracle database and applies them to KingbaseES with minimal latency.

Verification of Data Consistency

After configuration, verify that the data is synchronized correctly:

• Run a row count comparison on key tables between Oracle and KingbaseES.
• Insert a test record into the Oracle primary system.
• Query the corresponding table in KingbaseES to confirm the record appears within the expected latency window.

Critical Consideration

FlySync (KFS) ensures data consistency during the migration window. However, it does not replace the need for application-level validation. Ensure that your application can handle read-only queries against the KingbaseES backup without violating transaction isolation levels.

Step 3: Executing the One-Click Migration with KDTS and Handling PL/SQL Edge Cases

Once the assessment is complete and the dual-track synchronization is stable, you can proceed to migrate the schema and stored procedures using the Kingbase Database Transformation System (KDTS). This tool automates the conversion of Oracle objects to KingbaseES syntax.

Pre-Migration Check

• Ensure FlySync (KFS) is running and data is fully synchronized.
• Confirm that the KDTS version matches the target KingbaseES version (V009R002C012).
• Backup the KingbaseES target database to a safe state before running the migration.

Execution Procedure

1. Launch KDTS: Open the KDTS interface and select the "One-Click Migration" workflow.
2. Select Source and Target: Point the tool to the Oracle source and the KingbaseES target.
3. Configure Migration Options:
   • Enable "PL/SQL Conversion" to handle stored procedures and packages.
   • Select the specific PL/SQL features supported in V009R002C012, such as NEW initialization, %ROWTYPE, and DETERMINISTIC.
   • Enable "System View Mapping" to ensure V$VERSION and V$SESSION are created.
4. Run Migration: Execute the migration job. KDTS will convert the schema objects and attempt to translate the PL/SQL code.
5. Review Conversion Report: Check the output for any conversion warnings or errors.
   • Success: Objects converted successfully.
   • Warning: Objects converted but may require manual tuning.
   • Failure: Objects that failed conversion.

Handling PL/SQL Edge Cases

The V009R002C012 version includes specific enhancements to handle complex Oracle logic. During the migration, verify the following:

• Nested Tables: Ensure NEW initialization is preserved. The tool should automatically map this to the KingbaseES equivalent.
• Row Types: Check that %ROWTYPE parameters are correctly matched to the target table structure.
• Function Optimization: Verify that DETERMINISTIC and PARALLEL_ENABLE clauses are retained in the converted function definitions.
• Package Expansion: Confirm that packages with a high number of functions (up to nearly 10,000) are migrated without truncation.
• SQL Functions: Check that LISTAGG with WITH GROUP, ANYDATASET, and multi-parameter CONCAT functions are correctly translated.

Validation Check

• Execute a sample query against the migrated stored procedures in KingbaseES.
• Compare the output with the results from the Oracle system to ensure logic parity.

Validation: Verifying Oracle System View Compatibility and Query Parity

A critical requirement for a seamless migration is that monitoring tools and applications relying on Oracle system views continue to function without modification. KingbaseES V009R002C012 supports a set of Oracle-compatible system views to ensure application transparency.

Supported System Views

The following views are supported in the target environment:

Verification Procedure

1. Connect to KingbaseES: Use a client tool to connect to the KingbaseES database.
2. Query System Views: Execute the following queries to verify availability and output format:

   SELECT * FROM V$VERSION;
   SELECT * FROM V$SESSION WHERE username = 'YOUR_USER';
   SELECT * FROM ALL_PART_INDEXES WHERE table_name = 'YOUR_TABLE';
   

3. Compare Output: Compare the column names and data types with the output from the Oracle system.
4. Test Application: Run your monitoring or management application against the KingbaseES instance. Verify that it successfully retrieves session and version data without errors.

Troubleshooting

If a system view returns an error or missing columns:

• Check the KingbaseES version to ensure it is V009R002C012.
• Verify that the view was created during the schema migration phase.
• Consult the KingbaseES documentation for any specific mapping rules for unsupported views.

The Cutover: Swapping Roles in the Dual-Track Architecture

Once the migration is validated and the data is synchronized, you can proceed to the cutover phase. This involves transitioning the primary role from Oracle to KingbaseES while maintaining the ability to revert if necessary.

Cutover Procedure

1. Freeze Write Operations: Stop all write operations on the Oracle primary system to ensure data consistency.
2. Verify Final Sync: Run a final check with FlySync (KFS) to ensure all changes have been replicated to KingbaseES.
3. Switch Primary Role:
   • Update the application connection strings to point to the KingbaseES instance.
   • Enable write operations on the KingbaseES system.
   • Verify that the application can read and write data successfully.
4. Monitor Performance: Monitor the KingbaseES system for performance issues, error logs, and query latency.
5. Reconfigure FlySync: If needed, reconfigure FlySync (KFS) to synchronize from KingbaseES to Oracle (if Oracle is to remain as a backup) or disable it if Oracle is being decommissioned.

Safety Net

During this phase, the Oracle system remains available as a backup. If KingbaseES encounters critical failures, you can revert to the Oracle system immediately.

Rollback Protocol: Executing a Safe Reversion to Oracle

Despite rigorous testing, issues may arise after cutover. The dual-track architecture ensures that a safe rollback is possible by keeping the Oracle system as a viable backup.

Rollback Procedure

1. Identify Failure: Detect the issue in the KingbaseES primary system.
2. Stop Writes: Immediately stop all write operations on the KingbaseES system.
3. Revert Application Connections: Update the application connection strings to point back to the Oracle primary system.
4. Resume Write Operations: Enable write operations on the Oracle system.
5. Verify Data Consistency: Check that the Oracle system has all the data required for business operations.
6. Analyze and Fix: Investigate the root cause of the failure in KingbaseES.
7. Retry Migration: Once the issue is resolved, re-run the migration process and attempt cutover again.

Key Considerations

• Data Loss: Ensure that any data written to KingbaseES during the cutover window is accounted for. If the rollback occurs immediately, data loss is minimal.
• Synchronization State: Verify the state of FlySync (KFS) before re-enabling it for a future migration attempt.
• Documentation: Document the failure and the rollback steps for future reference and process improvement.

FAQ

What specific PL/SQL features in KingbaseES V009R002C012 allow for zero-code changes during migration?

KingbaseES V009R002C012 supports NEW initialization for nested tables, automatic matching of %ROWTYPE parameters, DETERMINISTIC and PARALLEL_ENABLE function attributes, and package expansion up to nearly 10,000 functions. These features allow complex stored procedures to be migrated without manual code refactoring.

How does the dual-track parallel running architecture ensure data consistency during the switch-over?

The architecture uses FlySync (KFS) to maintain real-time data synchronization between the Oracle primary and KingbaseES backup. During the cutover, the final data state is verified to ensure both systems are identical before switching the primary role. This allows for an immediate rollback to Oracle if the KingbaseES system fails.

What are the prerequisites for using FlySync (KFS) to synchronize Oracle and KingbaseES in real-time?

You need a valid commercial support contract, the FlySync (KFS) tool installed on both servers, low-latency network connectivity, and compatible versions of Oracle and KingbaseES (V009R002C012). The tool must be configured to handle the specific data types and schemas of your workload.

Can KingbaseES V009R002C012 fully replace Oracle system views like V$SESSION and V$VERSION?

Yes, KingbaseES V009R002C012 supports a set of Oracle-compatible system views including V$VERSION, V$SESSION, V$LOCKED_OBJECT, and partitioned index views. These views allow monitoring tools and applications to function without modification.

What is the step-by-step process for rolling back a migration if KingbaseES fails during the cutover?

Stop all write operations on KingbaseES, update application connections to point to Oracle, resume write operations on Oracle, and verify data consistency. The Oracle system remains a backup during the dual-track phase, ensuring a safe reversion path.

How does the KDMS tool assess the complexity of migrating a specific Oracle stored procedure?

KDMS analyzes the PL/SQL code against the KingbaseES compatibility matrix. It categorizes objects as compatible, warning, or error based on syntax support. The report provides a detailed breakdown of unsupported features and estimated refactoring effort.

💡 More Resources

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:

• Kingbase Community: A one-stop interactive platform for technical exchanges, Q&A, and experience sharing—join forces with fellow DBAs and developers.
• Kingbase Solutions: 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.
• Kingbase Case Studies: Real-world user scenarios and implementation outcomes, showcasing KingbaseES’s outstanding capabilities in high availability, high performance, and IT adaptation.
• Kingbase Documentation: Authoritative and comprehensive product manuals and technical guides, covering the entire lifecycle from installation and deployment to development, programming, and operations management.
• Free Download: Get the latest installation packages, drivers, tools, and patches, supporting multiple platforms and domestic chip architectures.
• Digital Construction Encyclopedia: Covers digital strategy planning, data integration, metrics management, database visualization applications, and more to empower enterprise digital transformation.

Open Source Resources:

• GitHub – Kingbase-docs: Kingbase documentation open-source repository—Stars and contributions are welcome.
• Gitee – Kingbase-docs: Domestic mirror repository for Kingbase documentation for faster access.

Welcome to explore the resources above and begin your Kingbase journey!