Reduce Oracle Licensing Cost_ Migration Checklist

Enterprise Reduce Oracle Licensing Cost: Technical Migration Checklist

Reducing Oracle licensing costs requires a technical strategy that addresses structural licensing metrics, such as processor core factors and virtualization rules, which inflate Total Cost of Ownership (TCO). This checklist provides a structured, executable procedure for migrating to KingbaseES, a commercial database software, focusing on compatibility verification, data integrity, and rollback procedures.

Prerequisites

• Source Environment: Oracle Database (Version to be verified against KingbaseES compatibility matrix).
• Target Environment: KingbaseES V009R002C012 or later (for enhanced Oracle compatibility features).
• Access: Administrative privileges on both Oracle and KingbaseES instances.
• Backup: Verified full backup of the Oracle source database.

Step-by-Step Migration and Verification Procedure

Step 1: Configure Oracle Compatibility Mode

KingbaseES V009R002C012 enhances Oracle compatibility in SQL, PL/SQL, and client interfaces. To enable this mode, configure the database instance parameters.

1. Set Compatibility Parameter:
   Execute the following SQL command to set the compatibility level to Oracle:

   ALTER SYSTEM SET compatibility_mode = 'oracle';
   

2. Restart Instance:
   Restart the KingbaseES instance to apply the configuration changes.

   # Example command for service restart
   systemctl restart kingbasees
   

3. Verify Configuration:
   Check the current compatibility setting:

   SHOW compatibility_mode;
   

   Expected Output: oracle

Step 2: Migrate and Validate PL/SQL Packages

KingbaseES supports specific PL/SQL features to minimize refactoring. Verify the following constructs in your source code and target environment.

• Nested Tables and VARRAYs: Ensure initialization uses the NEW keyword.
  • Verification: Run a test procedure initializing a nested table.

  -- Example PL/SQL block for verification
  DECLARE
    TYPE nested_table_type IS TABLE OF VARCHAR2(100);
    nt nested_table_type := nested_table_type();
  BEGIN
    nt := nested_table_type('A', 'B'); -- Verify NEW initialization compatibility
    DBMS_OUTPUT.PUT_LINE('Nested Table Init OK');
  END;
  

• %ROWTYPE Matching: Verify that stored procedures accepting %ROWTYPE parameters execute without type mismatch errors.
• DETERMINISTIC Functions: Ensure the DETERMINISTIC keyword is declared in the package header.

  -- Verify package header declaration
  CREATE OR REPLACE PACKAGE test_pkg IS
    FUNCTION calc_value(p_id NUMBER) RETURN NUMBER DETERMINISTIC;
  END test_pkg;
  

• Parallel Execution: Verify PARALLEL_ENABLE subclauses allow safe multi-process calls.
• Package Capacity: Confirm that packages with up to ~10,000 functions compile successfully.

Step 3: Adapt and Verify SQL Syntax

Update and verify SQL queries using compatible functions supported in KingbaseES V009R002C012.

• String Concatenation: Use CONCAT with arbitrary arguments.

  -- Verify flexible CONCAT
  SELECT CONCAT('A', 'B', 'C') FROM dual;
  

• Date/Time Handling: Verify TIMESTAMPADD and multi-format TO_TIMESTAMP.

  -- Verify TIMESTAMPADD
  SELECT TIMESTAMPADD(HOUR, 1, SYSDATE) FROM dual;
  
  -- Verify Multi-format TO_TIMESTAMP
  SELECT TO_TIMESTAMP('2023-10-27 10:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
  

• List Aggregation: Verify LISTAGG with optional WITH GROUP clause.

  -- Verify LISTAGG WITH GROUP
  SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) FROM table_name;
  

Step 4: Replace and Validate System Views

Replace Oracle-specific system view queries with KingbaseES equivalents. V009R002C012 supports the following views for migration auditing:

• V$VERSION: Database version information.
• V$SESSION: Active session details.
• V$LOCKED_OBJECT: Current lock status.
• Partition Index Views: ALL_PART_INDEXES, DBA__PART_INDEXES, USER_PART_INDEXES.

Verification Query:

-- Verify system view availability
SELECT * FROM V$VERSION;
SELECT * FROM V$SESSION WHERE ROWNUM = 1;

Step 5: Data Integrity Verification

Perform checksum comparisons to ensure data integrity between Oracle and KingbaseES.

1. Export Oracle Checksums:

   -- Oracle: Generate checksums for a table
   SELECT MD5(TO_CHAR(COL1) || TO_CHAR(COL2)) AS row_checksum
   FROM source_table;
   

2. Import and Compare in KingbaseES:

   -- KingbaseES: Generate checksums for migrated table
   SELECT MD5(COL1::TEXT || COL2::TEXT) AS row_checksum
   FROM migrated_table;
   

3. Diff Check: Compare the output sets. Any discrepancy indicates a migration error.

Rollback Procedure

In case of critical failure, revert to the Oracle environment using the following steps.

1. Stop KingbaseES Services:

   systemctl stop kingbasees
   

2. Restore Oracle Database:
   Use your standard Oracle backup restoration method (e.g., RMAN) to restore the database to the pre-migration state.

   # Example RMAN restore command
   rman target /
   RESTORE DATABASE;
   RECOVER DATABASE;
   ALTER DATABASE OPEN RESETLOGS;
   

3. Verify Oracle Availability:
   Confirm that Oracle services are running and applications can connect.

Troubleshooting Common Issues

• PL/SQL Compilation Errors: Check for unsupported Oracle-specific functions. Refer to KingbaseES documentation for the list of supported PL/SQL features.
• Performance Degradation: Analyze execution plans in KingbaseES. Adjust indexes or configuration parameters as needed.
• Data Mismatch: Re-run the data integrity verification steps. Check for character set mismatches or data type conversions.

FAQ

How do I enable Oracle compatibility mode in KingbaseES?

Set the compatibility_mode parameter to 'oracle' using ALTER SYSTEM and restart the instance. This aligns the SQL parser and PL/SQL engine with Oracle’s behavior.

What specific SQL commands verify data integrity during migration?

Use checksum functions like MD5 or CHECKSUM on both Oracle and KingbaseES to compare row-level data. Example: SELECT MD5(COL1::TEXT || COL2::TEXT) FROM table_name;

What is the rollback procedure if migration fails?

Stop KingbaseES services, restore the Oracle database from the pre-migration backup using RMAN or similar tools, and verify Oracle availability.

Does KingbaseES support Oracle system views for migration auditing?

Yes, KingbaseES V009R002C012 supports views such as V$VERSION, V$SESSION, V$LOCKED_OBJECT, and partition index views (ALL_PART_INDEXES, DBA__PART_INDEXES, USER_PART_INDEXES).

How does KingbaseES handle PL/SQL nested tables and VARRAYs?

KingbaseES supports initialization of nested tables and VARRAYs using the NEW keyword, matching Oracle’s collection variable initialization syntax.

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