Oracle Migration Consulting_ Assessing TCO and Risk

Prerequisites and Environment Setup

Before initiating the migration from Oracle to KingbaseES, ensure the following prerequisites are met. KingbaseES is a commercial database software; verify that appropriate licensing is in place.

1. Target Environment: Ensure KingbaseES V009R002C013 or later is installed and accessible. This version provides enhanced compatibility for syntax, functionality, and performance.
2. Source Access: Ensure read/write access to the Oracle source database is available for the assessment tools.
3. Network Connectivity: Verify network connectivity between the assessment/migration servers and both the Oracle source and KingbaseES target databases.
4. Backup: Create a full backup of the Oracle database to enable rollback in case of migration failure.

Phase 1: Migration Assessment with KDMS

The assessment phase quantifies migration effort and identifies compatibility gaps. KingbaseES provides the Kingbase Database Migration System (KDMS) for this purpose.

Step 1.1: Configure Assessment Parameters

Prepare a configuration file (e.g., assessment.xml) defining the source Oracle database connection and the target KingbaseES parameters.

Step 1.2: Execute Assessment Scan

Run the KDMS assessment tool. While specific command-line arguments may vary by deployment, the general procedure involves pointing the tool to the source database and configuration file.

• Vendor-Neutral Procedure: Execute the assessment utility against the Oracle source.

• Verification: If using KingbaseES tools, a typical command structure might resemble:

  kdms_scan --source=oracle --target=kingbase --config=assessment.xml
  

  Note: Verify exact command syntax and available flags in the KingbaseES KDMS documentation.

Step 1.3: Analyze Compatibility Report

Review the generated report to identify:

• Unsupported SQL data types.
• PL/SQL constructs requiring refactoring (e.g., unsupported packages or procedures).
• Schema objects that need manual conversion.

Phase 2: Code Refactoring and Preparation

Based on the KDMS report, manually refactor unsupported code. KingbaseES V009R002C012 and V009R002C013 have enhanced compatibility, reducing but not eliminating manual effort.

Key Compatibility Enhancements to Verify

• PL/SQL: Check for support of NEW initialization for nested tables, %ROWTYPE parameter matching, DETERMINISTIC keyword simplification, and PARALLEL_ENABLE subclauses.
• SQL: Verify support for ANYDATASET, optimized CONCAT, TIMESTAMPADD, and LISTAGG with WITH GROUP.
• System Views: Confirm availability of views such as V$VERSION, V$SESSION, V$LOCKED_OBJECT, and partition index views (ALL_PART_INDEXES, DBA__PART_INDEXES, USER_PART_INDEXES).

Phase 3: Automated Migration with KDTS

Once code is refactored, use the Kingbase Database Migration Tool (KDTS) for schema and data conversion.

Step 3.1: Execute One-Click Migration

Run the KDTS tool to convert the schema and migrate data.

• Vendor-Neutral Procedure: Initiate the migration tool with the pre-configured source and target parameters.

• Example Command:

  kdts_migrate --config=migration_config.xml
  

  Note: Verify exact command syntax in the KingbaseES KDTS documentation.

Step 3.2: Monitor Migration Progress

Monitor the tool’s output for errors or warnings. Ensure that data types and constraints are correctly mapped.

Phase 4: Verification and Validation

Post-migration verification is critical to ensure data integrity and functional correctness.

Step 4.1: Data Integrity Checks

• Row Count Comparison: Compare row counts between Oracle and KingbaseES for key tables.
• Checksum Validation: Run checksums on critical data sets to ensure no corruption occurred during transfer.

Step 4.2: Functional Compatibility Verification

• System View Verification: Run queries against KingbaseES to verify the presence and correctness of compatible system views.

  SELECT * FROM V$VERSION;
  SELECT * FROM V$SESSION;
  

• PL/SQL Execution: Execute refactored stored procedures and functions to verify logical correctness.

Phase 5: Dual-Track Parallel Running

To mitigate risk, run the application in parallel against both Oracle and KingbaseES.

Step 5.1: Configure Data Synchronization

Use KingbaseES Heterogeneous Data Synchronization Software to synchronize data changes from Oracle to KingbaseES in real-time or near-real-time. This allows both systems to remain consistent during the validation period.

Step 5.2: Validate Application Behavior

• Run application workloads against both databases.
• Compare results to ensure identical behavior.
• Monitor performance metrics to identify any regression.

Phase 6: Cutover and Rollback

Step 6.1: Pre-Cutover Final Validation

Ensure all data is synchronized and application validation is complete.

Step 6.2: Execute Cutover

Redirect application traffic to the KingbaseES database.

Step 6.3: Rollback Procedure

If critical issues are detected post-cutover:

1. Immediately switch application traffic back to the Oracle database.
2. The dual-track setup ensures Oracle remains a viable backup.
3. Investigate the root cause in KingbaseES before attempting another cutover.

Post-Migration Performance Tuning with KSH

After cutover, use Kingbase Session History (KSH) and the sys_ksh plugin (available in KingbaseES V9 and later) to tune performance.

Step 7.1: Generate KSH Reports

Use the sys_ksh plugin to generate session history reports.

• Example Command:

  -- Example syntax; verify exact plugin usage in KingbaseES documentation
  SELECT * FROM sys_ksh_report();
  

Step 7.2: Analyze and Optimize

• Identify Bottlenecks: Review wait events and top SQL statements.
• Optimize Queries: Rewrite inefficient SQL or adjust indexes based on KSH insights.
• Re-test: Validate performance improvements with subsequent benchmark runs.

Security and High Availability Configuration

Security Hardening

• Access Control: Configure user-group based autonomous access control strategies to optimize permissions.
• Encryption: Enable transparent encryption with supported encryption devices to secure data at rest.

High Availability (HA) Design

Design the HA architecture based on Recovery Time Objective (RTO) and Recovery Point Objective (RPO). Common configurations include Active-Standby or Active-Active setups. Consult with engineering teams to select the appropriate HA mode for your workload.

FAQ

How do I verify KingbaseES compatibility with my Oracle PL/SQL code?

Run the KDMS assessment tool to generate a compatibility report. Specifically check for support of NEW initialization, %ROWTYPE matching, and DETERMINISTIC keywords in KingbaseES V009R002C012 or later.

What commands are used to run the KDMS assessment and KDTS migration?

While specific commands depend on the deployment, typical usage involves running kdms_scan with a configuration file for assessment and kdts_migrate for conversion. Verify exact syntax in the KingbaseES documentation.

How can I validate data integrity after migration?

Perform row count comparisons and checksum validations between the Oracle source and KingbaseES target. Use the KDTS validation features if available.

What is the rollback procedure if KingbaseES migration fails?

If critical issues arise post-cutover, switch application traffic back to the Oracle database. Ensure the dual-track parallel setup was maintained to keep Oracle as a viable backup.

How do I tune performance after migrating to KingbaseES?

Use the sys_ksh plugin to generate Kingbase Session History (KSH) reports. Analyze wait events and top SQL statements to identify and resolve performance bottlenecks.

💡 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!