--- title: "Oracle to HeliosDB Migration Guide" --- # Oracle to HeliosDB Migration Guide **Version:** 1.0 **Compatibility Target:** Oracle 11g, 12c, 18c, 19c, 21c, 23ai --- ## Table of Contents 1. [Introduction](#1-introduction) 2. [Compatibility Overview](#2-compatibility-overview) 3. [Pre-Migration Assessment](#3-pre-migration-assessment) 4. [Conceptual Mapping](#4-conceptual-mapping) 5. [Step-by-Step Migration](#5-step-by-step-migration) 6. [Feature Mapping](#6-feature-mapping) 7. [PL/SQL Migration](#7-plsql-migration) 8. [Application Connectivity](#8-application-connectivity) 9. [Post-Migration Validation](#9-post-migration-validation) 10. [Performance Considerations](#10-performance-considerations) 11. [Common Issues and Troubleshooting](#11-common-issues-and-troubleshooting) 12. [Rollback Procedures](#12-rollback-procedures) --- ## 1. Introduction ### 1.1 Why Migrate from Oracle to HeliosDB? Organizations are increasingly migrating from Oracle Database to HeliosDB for several compelling reasons: #### Licensing Oracle Database Enterprise Edition is licensed per processor or per named user, with options such as RAC, Partitioning, Advanced Security and Database In-Memory licensed separately, plus annual support. HeliosDB Full is commercially licensed (source-available under SSPL-1.0); contact [sales@heliosdb.com](mailto:sales@heliosdb.com) for pricing. #### Multi-Model Support HeliosDB provides native support for multiple data models through a single platform: - **Relational SQL**: Full Oracle SQL compatibility - **Document/JSON**: MongoDB-compatible document storage - **Key-Value**: Redis-compatible operations - **Time-Series**: Native time-series optimizations - **Graph**: Cypher query language support - **Vector**: AI/ML embeddings with similarity search #### Modern Architecture Advantages | Capability | Oracle | HeliosDB | |------------|--------|----------| | Cloud-Native | Partial | Full Kubernetes support | | Multi-Protocol | No | 9+ protocols | | Auto-Scaling | Manual/Licensed | Automatic | | Self-Healing | Manual intervention | AI-driven autonomous | | ML Integration | External tools | Native in-database ML | | HTAP | Separate systems | Unified workload | ### 1.2 Migration Goals This guide helps you achieve: 1. **Zero data loss** during migration 2. **Minimal downtime** through parallel operation strategies 3. **Application compatibility** with existing Oracle clients 4. **Performance parity or improvement** post-migration 5. **Reduced operational complexity** through HeliosDB automation --- ## 2. Compatibility Overview ### 2.1 Oracle Protocol Support in HeliosDB HeliosDB implements the Oracle TNS wire protocol, and its Oracle compatibility module covers a large part of Oracle's feature surface. Through the Oracle listener today, core data access runs unchanged, while Oracle-specific syntax, most built-in functions and PL/SQL need rewriting; see [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/) for results by usage area and how to assess your application. It is not a drop-in replacement: plan to test and adapt less common features. Module status by category (see the [compatibility matrix](/docs/full/reference/protocols/oracle/compatibility/)): | Category | Status | Notes | |----------|--------|-------| | **Core SQL** | Supported | SELECT, INSERT, UPDATE, DELETE, MERGE | | **PL/SQL Core** | Beta | Anonymous blocks, procedures, functions | | **DBMS Packages** | Partial | Most commonly used packages | | **Hierarchical Queries** | Supported | CONNECT BY | | **JSON Functions** | Supported | Oracle 21c+ JSON functions | | **Analytics** | Supported | Window functions | ### 2.2 Supported Oracle Features #### Fully Supported (Direct Migration) - DDL: CREATE TABLE, INDEX, VIEW, MATERIALIZED VIEW, SEQUENCE, SYNONYM - DML: SELECT, INSERT, UPDATE, DELETE, MERGE, INSERT ALL - Transactions: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION - Hierarchical: CONNECT BY, START WITH, LEVEL, SYS_CONNECT_BY_PATH - Advanced: PIVOT/UNPIVOT, MERGE, Flashback queries - JSON: JSON_VALUE, JSON_QUERY, JSON_TABLE, JSON_OBJECT - Analytics: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, LISTAGG #### Partially Supported (Requires Modification) - MODEL clause (basic support) - BFILE data type (read-only) - ORA_ROWSCN (partial tracking) - Some DBMS_STATS functions - PL/SQL procedures, functions, packages and triggers: port to PL/pgSQL (PL/SQL execution through the Oracle listener is on the roadmap) #### Not Supported (Requires Redesign) | Oracle Feature | HeliosDB Alternative | |----------------|---------------------| | Oracle RAC | HeliosDB distributed clustering | | Data Guard | HeliosDB multi-region replication | | Advanced Queuing (AQ) | Native streaming + Kafka integration | | Spatial (SDO_*) | HeliosDB geospatial indexing | | Label Security | Row-level security policies | | Oracle Text | HeliosDB full-text search | ### 2.3 Connection Compatibility HeliosDB accepts Oracle client connections via TNS protocol: ``` Connection Port: 1521 (default) Protocol: Oracle TNS Wire Protocol Version: 12.2 compatible ``` Supported drivers: - Oracle JDBC (21c+) - cx_Oracle / oracledb (Python) - ODP.NET (21c+) - oracledb (Node.js 6.x) - godror (Go) --- ## 3. Pre-Migration Assessment ### 3.1 Database Inventory Before migration, perform a comprehensive inventory of your Oracle environment. #### Schema Analysis Script ```sql -- Database size summary SELECT owner, segment_type, COUNT(*) as object_count, ROUND(SUM(bytes)/1024/1024/1024, 2) as size_gb FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP') GROUP BY owner, segment_type ORDER BY size_gb DESC; -- Table and index counts per schema SELECT owner, (SELECT COUNT(*) FROM dba_tables WHERE owner = u.username) as tables, (SELECT COUNT(*) FROM dba_indexes WHERE owner = u.username) as indexes, (SELECT COUNT(*) FROM dba_views WHERE owner = u.username) as views, (SELECT COUNT(*) FROM dba_sequences WHERE owner = u.username) as sequences, (SELECT COUNT(*) FROM dba_procedures WHERE owner = u.username) as procedures FROM dba_users u WHERE username NOT IN ('SYS', 'SYSTEM', 'OUTLN') ORDER BY username; ``` ### 3.2 PL/SQL Code Analysis Analyze your PL/SQL codebase for migration complexity: ```sql -- Count PL/SQL objects by type SELECT owner, type, COUNT(*) as object_count, SUM(CASE WHEN status = 'VALID' THEN 1 ELSE 0 END) as valid_count, SUM(CASE WHEN status = 'INVALID' THEN 1 ELSE 0 END) as invalid_count FROM dba_objects WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER', 'TYPE', 'TYPE BODY') AND owner NOT IN ('SYS', 'SYSTEM') GROUP BY owner, type ORDER BY owner, object_count DESC; -- Count lines of PL/SQL code SELECT owner, type, SUM(line) as total_lines FROM dba_source WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY owner, type ORDER BY total_lines DESC; ``` #### DBMS Package Usage Inventory Identify which DBMS packages your code uses: ```sql -- Find DBMS package dependencies SELECT DISTINCT owner, name, referenced_name as dbms_package, type FROM dba_dependencies WHERE referenced_owner = 'SYS' AND referenced_name LIKE 'DBMS_%' AND owner NOT IN ('SYS', 'SYSTEM') ORDER BY referenced_name, owner, name; -- Common patterns to search for in source code SELECT owner, name, type, line, text FROM dba_source WHERE UPPER(text) LIKE '%DBMS_OUTPUT%' OR UPPER(text) LIKE '%DBMS_LOB%' OR UPPER(text) LIKE '%DBMS_SQL%' OR UPPER(text) LIKE '%DBMS_UTILITY%' OR UPPER(text) LIKE '%DBMS_SCHEDULER%' OR UPPER(text) LIKE '%UTL_FILE%' OR UPPER(text) LIKE '%DBMS_AQ%' OR UPPER(text) LIKE '%DBMS_XMLGEN%' ORDER BY owner, name, line; ``` ### 3.3 Schema Complexity Evaluation #### Data Type Inventory ```sql -- Identify data types in use SELECT data_type, COUNT(*) as column_count, COUNT(DISTINCT owner||'.'||table_name) as table_count FROM dba_tab_columns WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY data_type ORDER BY column_count DESC; ``` **Data Type Migration Risk Assessment:** | Risk Level | Data Types | |------------|------------| | Low (Direct) | NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB, RAW | | Medium (Verify) | BFILE, XMLTYPE, INTERVAL, LONG, LONG RAW | | High (Convert) | SDO_GEOMETRY, ANYDATA, User-defined types | #### Constraint Analysis ```sql -- Foreign key relationships SELECT a.owner, a.table_name, COUNT(*) as fk_count FROM dba_constraints a WHERE a.constraint_type = 'R' AND a.owner NOT IN ('SYS', 'SYSTEM') GROUP BY a.owner, a.table_name HAVING COUNT(*) > 5 ORDER BY fk_count DESC; -- Check constraints using Oracle-specific functions SELECT owner, table_name, constraint_name, search_condition FROM dba_constraints WHERE constraint_type = 'C' AND owner NOT IN ('SYS', 'SYSTEM') AND (search_condition LIKE '%ROWNUM%' OR search_condition LIKE '%SYSDATE%' OR search_condition LIKE '%DECODE%'); ``` ### 3.4 Application Dependencies Document all applications connecting to Oracle: ```sql -- Active session analysis SELECT machine, program, module, COUNT(*) as session_count, COUNT(DISTINCT username) as user_count FROM v$session WHERE type = 'USER' GROUP BY machine, program, module ORDER BY session_count DESC; ``` ### 3.5 Migration Complexity Score Calculate your migration complexity: | Factor | Points | Your Score | |--------|--------|------------| | Tables < 50 | 1 | | | Tables 50-200 | 2 | | | Tables > 200 | 3 | | | PL/SQL LOC < 10K | 1 | | | PL/SQL LOC 10K-100K | 2 | | | PL/SQL LOC > 100K | 3 | | | No DBMS_AQ usage | 0 | | | Uses DBMS_AQ | 2 | | | No Oracle RAC | 0 | | | Uses Oracle RAC | 2 | | | No Spatial data | 0 | | | Uses SDO_GEOMETRY | 3 | | **Total Score Interpretation:** - 1-4: Simple migration (1-2 weeks) - 5-8: Medium complexity (2-6 weeks) - 9+: Complex migration (6+ weeks) --- ## 4. Conceptual Mapping ### 4.1 Oracle to HeliosDB Object Mapping | Oracle Concept | HeliosDB Equivalent | Notes | |----------------|---------------------|-------| | Database | Database | 1:1 mapping | | Tablespace | Storage Pool | Different internal management | | Schema | Schema | Direct mapping | | Table | Table | Most CREATE TABLE options | | Index | Index | B-tree, Bitmap supported | | Sequence | Sequence | Compatible syntax | | Synonym | Synonym | Public/Private supported | | View | View | Standard views supported | | Materialized View | Materialized View | Refresh options supported | | Package | Package | Port to a schema of PL/pgSQL functions today; PL/SQL execution through the Oracle listener is on the roadmap | | Procedure | Procedure | Port to PL/pgSQL today | | Function | Function | Port to PL/pgSQL today | | Trigger | Trigger | DML triggers; port the trigger body to PL/pgSQL today | | DBLink | Foreign Data Wrapper | Different implementation | | Directory | Storage Mount | File system access | ### 4.2 Tablespace to Storage Pool Mapping Oracle tablespaces map to HeliosDB storage pools with different characteristics: ```sql -- Oracle tablespace query SELECT tablespace_name, status, contents, extent_management FROM dba_tablespaces; ``` **HeliosDB Storage Pool Configuration:** ```sql -- Create storage pool (HeliosDB) CREATE STORAGE POOL sales_pool WITH REPLICATION_FACTOR = 3 COMPRESSION = 'lz4' ENCRYPTION = 'aes256'; -- Assign table to pool CREATE TABLE sales.orders ( order_id NUMBER PRIMARY KEY, order_date DATE ) STORAGE POOL sales_pool; ``` **Mapping Recommendations:** | Oracle Tablespace Type | HeliosDB Storage Pool | |------------------------|----------------------| | SYSTEM/SYSAUX | system_pool (auto-managed) | | UNDO | Not needed (MVCC internal) | | TEMP | Automatic temp management | | User data | Custom storage pools | | Index tablespace | Same pool as data (optional) | ### 4.3 User and Role Mapping ```sql -- Oracle: Create user CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; -- HeliosDB: Create user CREATE USER app_user WITH PASSWORD 'password' DEFAULT SCHEMA app_user; -- Oracle: Grant roles GRANT CONNECT, RESOURCE TO app_user; GRANT SELECT ON sales.orders TO app_user; -- HeliosDB: Grant roles (compatible syntax) GRANT CONNECT, RESOURCE TO app_user; GRANT SELECT ON sales.orders TO app_user; ``` ### 4.4 Sequence Migration ```sql -- Oracle sequence CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1 MAXVALUE 999999999 CACHE 100 CYCLE; -- HeliosDB sequence (compatible syntax) CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1 MAXVALUE 999999999 CACHE 100 CYCLE; -- Usage is identical SELECT order_seq.NEXTVAL FROM DUAL; INSERT INTO orders (id) VALUES (order_seq.NEXTVAL); ``` ### 4.5 Synonym Migration ```sql -- Oracle public synonym CREATE PUBLIC SYNONYM emp FOR hr.employees; -- HeliosDB (same syntax) CREATE PUBLIC SYNONYM emp FOR hr.employees; -- Private synonym CREATE SYNONYM my_emp FOR hr.employees; ``` --- ## 5. Step-by-Step Migration ### 5.1 Phase 1: Schema Export Using Data Pump #### Full Schema Export ```bash # Oracle Data Pump export expdp system/password@ORCL \ SCHEMAS=HR,SALES,INVENTORY \ DIRECTORY=DUMP_DIR \ DUMPFILE=schema_export_%U.dmp \ LOGFILE=schema_export.log \ PARALLEL=4 \ CONTENT=ALL \ COMPRESSION=ALL ``` #### Schema-Only Export (for translation) ```bash # Export DDL only expdp system/password@ORCL \ SCHEMAS=HR \ DIRECTORY=DUMP_DIR \ DUMPFILE=ddl_export.dmp \ LOGFILE=ddl_export.log \ CONTENT=METADATA_ONLY ``` #### Generate SQL DDL Scripts ```sql -- Using DBMS_METADATA SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET PAGESIZE 0 SET LINESIZE 1000 -- Export all tables in schema SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner) FROM dba_tables WHERE owner = 'HR'; -- Export all indexes SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) FROM dba_indexes WHERE owner = 'HR' AND index_type != 'LOB'; -- Export all procedures/functions SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, owner) FROM dba_objects WHERE owner = 'HR' AND object_type = 'PROCEDURE'; -- Export all packages SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, owner) FROM dba_objects WHERE owner = 'HR' AND object_type = 'PACKAGE'; ``` ### 5.2 Phase 2: Schema Translation for HeliosDB Most Oracle DDL works directly in HeliosDB. Common translations needed: #### Storage Clause Removal ```sql -- Oracle DDL with storage clauses CREATE TABLE employees ( employee_id NUMBER(10) NOT NULL, first_name VARCHAR2(50), hire_date DATE DEFAULT SYSDATE ) TABLESPACE users STORAGE (INITIAL 64K NEXT 64K) PCTFREE 10 PCTUSED 40; -- HeliosDB equivalent (remove storage specifics) CREATE TABLE employees ( employee_id NUMBER(10) NOT NULL, first_name VARCHAR2(50), hire_date DATE DEFAULT SYSDATE ); -- Or with storage pool: -- ) STORAGE POOL users_pool; ``` #### Partitioning Translation ```sql -- Oracle range partitioning CREATE TABLE sales_history ( sale_id NUMBER, sale_date DATE, amount NUMBER ) PARTITION BY RANGE (sale_date) ( PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'), PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'), PARTITION p_max VALUES LESS THAN (MAXVALUE) ); -- HeliosDB (compatible syntax) CREATE TABLE sales_history ( sale_id NUMBER, sale_date DATE, amount NUMBER ) PARTITION BY RANGE (sale_date) ( PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'), PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'), PARTITION p_max VALUES LESS THAN (MAXVALUE) ); ``` #### Index Translation ```sql -- Oracle bitmap index CREATE BITMAP INDEX idx_status ON orders(status); -- HeliosDB (same syntax) CREATE BITMAP INDEX idx_status ON orders(status); -- Oracle function-based index CREATE INDEX idx_upper_name ON employees(UPPER(last_name)); -- HeliosDB (same syntax) CREATE INDEX idx_upper_name ON employees(UPPER(last_name)); ``` ### 5.3 Phase 3: Data Migration Strategies #### Strategy A: Direct Data Pump Import (Recommended) HeliosDB can process Oracle Data Pump files directly: ```sql -- Connect to HeliosDB sqlplus admin/password@//heliosdb-server:1521/heliosdb -- Import from Data Pump file IMPORT DATAPUMP FROM '/path/to/schema_export.dmp' SCHEMAS = HR, SALES TRANSFORM = STORAGE:N PARALLEL = 8; ``` #### Strategy B: SQL*Loader Format Migration ```bash # Export data from Oracle using SQL*Loader sqlldr userid=system/password@ORCL \ control=export.ctl \ data=employees.csv \ log=export.log ``` **Control file (export.ctl):** ``` OPTIONS (SKIP=1) LOAD DATA CHARACTERSET UTF8 INFILE 'employees.csv' INTO TABLE employees FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( employee_id, first_name, last_name, hire_date DATE "YYYY-MM-DD", salary ) ``` **Import to HeliosDB:** ```sql -- Using COPY command COPY employees FROM '/path/to/employees.csv' WITH (FORMAT csv, HEADER true); ``` #### Strategy C: Parallel ETL with Batch Processing For large tables (>100GB), use parallel extraction: ```sql -- Oracle: Extract in chunks SELECT /*+ PARALLEL(e, 8) */ employee_id, first_name, last_name, hire_date, salary FROM employees e WHERE MOD(ROWNUM, 8) = 0; -- Run 8 parallel streams ``` **HeliosDB Batch Insert:** ```sql -- Use batch insert for performance BEGIN BATCH INSERT INSERT INTO employees VALUES (1, 'John', 'Doe', DATE '2020-01-15', 75000); INSERT INTO employees VALUES (2, 'Jane', 'Smith', DATE '2020-02-20', 82000); -- ... more rows END BATCH; ``` ### 5.4 Phase 4: PL/SQL Migration PL/SQL execution through the Oracle listener — procedures, functions, anonymous blocks and common DBMS_ packages — is on the roadmap. Today, port PL/SQL to [PL/pgSQL functions](/docs/full/migration/oracle-to-heliosdb/#step-2-convert-plsql) and run them through the PostgreSQL interface, or move the logic into the application tier. See [Section 7](#7-plsql-migration) for the constructs in scope and conversion patterns. ### 5.5 Phase 5: Application Cutover #### Parallel Running Strategy ``` Week 1-2: Read-only HeliosDB testing Week 3: Write to both, read from Oracle Week 4: Write to both, read from HeliosDB Week 5: HeliosDB primary, Oracle standby Week 6: Decommission Oracle reads ``` #### Connection String Changes ```python # Oracle connection oracle_dsn = "oracle+cx_oracle://user:pass@oracle-host:1521/ORCL" # HeliosDB connection (same driver works) helios_dsn = "oracle+cx_oracle://user:pass@heliosdb-host:1521/heliosdb" ``` --- ## 6. Feature Mapping ### 6.1 Oracle SQL Extensions in HeliosDB #### DECODE Function ```sql -- Oracle DECODE (fully supported) SELECT employee_id, DECODE(status, 'A', 'Active', 'I', 'Inactive', 'T', 'Terminated', 'Unknown') as status_desc FROM employees; ``` #### NVL and NVL2 ```sql -- NVL (replace NULL) SELECT NVL(commission, 0) FROM employees; -- NVL2 (if not null / if null) SELECT NVL2(manager_id, 'Has Manager', 'Top Level') FROM employees; ``` #### ROWNUM and ROWID ```sql -- ROWNUM for limiting SELECT * FROM employees WHERE ROWNUM <= 10; -- ROWID for row identification SELECT ROWID, employee_id FROM employees WHERE department_id = 10; ``` ### 6.2 DBMS Package Mapping #### DBMS_OUTPUT ```sql -- Fully supported BEGIN DBMS_OUTPUT.ENABLE(1000000); DBMS_OUTPUT.PUT_LINE('Processing started'); DBMS_OUTPUT.PUT('Status: '); DBMS_OUTPUT.PUT_LINE('Complete'); DBMS_OUTPUT.NEW_LINE; END; / ``` #### DBMS_LOB ```sql -- LOB operations (fully supported) DECLARE v_clob CLOB; v_length NUMBER; BEGIN SELECT document INTO v_clob FROM documents WHERE id = 1; v_length := DBMS_LOB.GETLENGTH(v_clob); DBMS_OUTPUT.PUT_LINE('Length: ' || v_length); -- Substring DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_clob, 100, 1)); -- Append DBMS_LOB.APPEND(v_clob, ' Additional text'); END; / ``` #### DBMS_SQL ```sql -- Dynamic SQL (fully supported) DECLARE v_cursor INTEGER; v_rows INTEGER; BEGIN v_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cursor, 'UPDATE employees SET salary = salary * 1.1 WHERE department_id = :dept', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept', 10); v_rows := DBMS_SQL.EXECUTE(v_cursor); DBMS_OUTPUT.PUT_LINE('Updated ' || v_rows || ' rows'); DBMS_SQL.CLOSE_CURSOR(v_cursor); END; / ``` #### DBMS_SCHEDULER ```sql -- Job scheduling (fully supported) BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'NIGHTLY_CLEANUP', job_type => 'STORED_PROCEDURE', job_action => 'cleanup_old_data', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0', enabled => TRUE ); END; / -- Manage jobs BEGIN DBMS_SCHEDULER.RUN_JOB('NIGHTLY_CLEANUP'); DBMS_SCHEDULER.DISABLE('NIGHTLY_CLEANUP'); DBMS_SCHEDULER.DROP_JOB('NIGHTLY_CLEANUP'); END; / ``` #### DBMS_METADATA ```sql -- Schema export (fully supported) SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'HR') FROM DUAL; SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'EMPLOYEES', 'HR') FROM DUAL; ``` #### UTL_FILE ```sql -- File I/O (fully supported) DECLARE v_file UTL_FILE.FILE_TYPE; v_line VARCHAR2(32767); BEGIN v_file := UTL_FILE.FOPEN('DATA_DIR', 'output.txt', 'W'); UTL_FILE.PUT_LINE(v_file, 'Header line'); FOR rec IN (SELECT * FROM employees) LOOP UTL_FILE.PUT_LINE(v_file, rec.employee_id || ',' || rec.first_name); END LOOP; UTL_FILE.FCLOSE(v_file); END; / ``` ### 6.3 Partitioning Support ```sql -- Range partitioning CREATE TABLE sales ( sale_id NUMBER, sale_date DATE ) PARTITION BY RANGE (sale_date) ( PARTITION p_q1 VALUES LESS THAN (DATE '2024-04-01'), PARTITION p_q2 VALUES LESS THAN (DATE '2024-07-01'), PARTITION p_q3 VALUES LESS THAN (DATE '2024-10-01'), PARTITION p_q4 VALUES LESS THAN (DATE '2025-01-01') ); -- List partitioning CREATE TABLE orders ( order_id NUMBER, region VARCHAR2(20) ) PARTITION BY LIST (region) ( PARTITION p_east VALUES ('NY', 'NJ', 'CT'), PARTITION p_west VALUES ('CA', 'WA', 'OR'), PARTITION p_other VALUES (DEFAULT) ); -- Hash partitioning CREATE TABLE customers ( customer_id NUMBER, name VARCHAR2(100) ) PARTITION BY HASH (customer_id) PARTITIONS 16; ``` ### 6.4 Flashback to Time Travel Mapping Oracle Flashback maps to HeliosDB Time Travel: ```sql -- Oracle Flashback Query SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- HeliosDB Time Travel (identical syntax) SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- Oracle Flashback with SCN SELECT * FROM employees AS OF SCN 12345678; -- HeliosDB (identical syntax) SELECT * FROM employees AS OF SCN 12345678; -- Versions between timestamps SELECT versions_starttime, versions_endtime, versions_operation, employee_id, salary FROM employees VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2024-01-01', 'YYYY-MM-DD') AND TO_TIMESTAMP('2024-01-31', 'YYYY-MM-DD') WHERE employee_id = 100; ``` **HeliosDB Time Travel Advantages:** - Configurable retention (7+ days default) - Delta compression (<10% storage overhead) - Sub-millisecond temporal queries - Integrated with MVCC --- ## 7. PL/SQL Migration :::note[PL/SQL roadmap] PL/SQL execution through the Oracle listener — procedures, functions, anonymous blocks and common DBMS_ packages — is on the roadmap. Today, port PL/SQL to [PL/pgSQL functions](/docs/full/migration/oracle-to-heliosdb/#step-2-convert-plsql) and run them through the PostgreSQL interface, or move the logic into the application tier. ::: ### 7.1 PL/SQL Constructs in Scope The Oracle compatibility module covers the most commonly used PL/SQL constructs, shown below; they are the planned scope for PL/SQL execution through the Oracle listener: #### Variable Declarations ```sql DECLARE -- Scalar types v_number NUMBER(10,2) := 100.50; v_string VARCHAR2(100) := 'Hello'; v_date DATE := SYSDATE; v_timestamp TIMESTAMP := SYSTIMESTAMP; v_boolean BOOLEAN := TRUE; -- Constants c_tax_rate CONSTANT NUMBER := 0.08; -- Anchored types v_salary employees.salary%TYPE; v_emp_row employees%ROWTYPE; -- Collections TYPE num_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER; v_numbers num_array; -- Nested tables TYPE name_list IS TABLE OF VARCHAR2(100); v_names name_list := name_list('John', 'Jane', 'Bob'); BEGIN -- Body NULL; END; / ``` #### Control Flow ```sql DECLARE v_grade CHAR(1); v_score NUMBER := 85; BEGIN -- IF-THEN-ELSE IF v_score >= 90 THEN v_grade := 'A'; ELSIF v_score >= 80 THEN v_grade := 'B'; ELSIF v_score >= 70 THEN v_grade := 'C'; ELSE v_grade := 'F'; END IF; -- CASE expression v_grade := CASE WHEN v_score >= 90 THEN 'A' WHEN v_score >= 80 THEN 'B' WHEN v_score >= 70 THEN 'C' ELSE 'F' END; -- Simple LOOP LOOP v_score := v_score - 10; EXIT WHEN v_score < 0; END LOOP; -- WHILE loop v_score := 100; WHILE v_score > 0 LOOP v_score := v_score - 10; END LOOP; -- FOR loop FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE('Iteration: ' || i); END LOOP; -- FOR loop with REVERSE FOR i IN REVERSE 1..10 LOOP DBMS_OUTPUT.PUT_LINE('Countdown: ' || i); END LOOP; END; / ``` #### Cursors ```sql DECLARE -- Explicit cursor CURSOR emp_cursor IS SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10; -- Cursor with parameters CURSOR dept_cursor(p_dept_id NUMBER) IS SELECT * FROM employees WHERE department_id = p_dept_id; v_emp emp_cursor%ROWTYPE; BEGIN -- Explicit cursor operations OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_emp; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.first_name); END LOOP; CLOSE emp_cursor; -- Cursor FOR loop (implicit) FOR rec IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary); END LOOP; -- Cursor with parameters FOR rec IN dept_cursor(20) LOOP DBMS_OUTPUT.PUT_LINE(rec.first_name); END LOOP; END; / ``` #### BULK COLLECT and FORALL ```sql DECLARE TYPE emp_tab IS TABLE OF employees%ROWTYPE; TYPE id_tab IS TABLE OF NUMBER; v_employees emp_tab; v_ids id_tab := id_tab(1, 2, 3, 4, 5); BEGIN -- BULK COLLECT SELECT * BULK COLLECT INTO v_employees FROM employees WHERE department_id = 10; DBMS_OUTPUT.PUT_LINE('Collected ' || v_employees.COUNT || ' rows'); -- FORALL with SAVE EXCEPTIONS FORALL i IN v_ids.FIRST..v_ids.LAST SAVE EXCEPTIONS UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_ids(i); EXCEPTION WHEN OTHERS THEN FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Error at index ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX); END LOOP; END; / ``` ### 7.2 Exception Handling ```sql DECLARE v_salary NUMBER; -- Custom exception e_salary_too_high EXCEPTION; PRAGMA EXCEPTION_INIT(e_salary_too_high, -20001); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = 999; -- May not exist IF v_salary > 1000000 THEN RAISE e_salary_too_high; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee not found'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Multiple employees returned'); WHEN e_salary_too_high THEN DBMS_OUTPUT.PUT_LINE('Salary exceeds maximum'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); RAISE; END; / ``` ### 7.3 Stored Procedures ```sql CREATE OR REPLACE PROCEDURE transfer_funds( p_from_account IN NUMBER, p_to_account IN NUMBER, p_amount IN NUMBER, p_status OUT VARCHAR2 ) AS v_from_balance NUMBER; e_insufficient_funds EXCEPTION; BEGIN -- Check source balance SELECT balance INTO v_from_balance FROM accounts WHERE account_id = p_from_account FOR UPDATE; IF v_from_balance < p_amount THEN RAISE e_insufficient_funds; END IF; -- Perform transfer UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account; UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account; COMMIT; p_status := 'SUCCESS'; EXCEPTION WHEN e_insufficient_funds THEN ROLLBACK; p_status := 'INSUFFICIENT_FUNDS'; WHEN OTHERS THEN ROLLBACK; p_status := 'ERROR: ' || SQLERRM; END transfer_funds; / ``` ### 7.4 Packages ```sql -- Package specification CREATE OR REPLACE PACKAGE employee_pkg AS -- Types TYPE emp_rec IS RECORD ( emp_id NUMBER, emp_name VARCHAR2(100), salary NUMBER ); TYPE emp_tab IS TABLE OF emp_rec; -- Constants c_max_salary CONSTANT NUMBER := 500000; -- Procedures PROCEDURE hire_employee( p_name IN VARCHAR2, p_dept_id IN NUMBER, p_salary IN NUMBER ); -- Functions FUNCTION get_employee_count(p_dept_id IN NUMBER) RETURN NUMBER; FUNCTION get_department_employees(p_dept_id IN NUMBER) RETURN emp_tab PIPELINED; END employee_pkg; / -- Package body CREATE OR REPLACE PACKAGE BODY employee_pkg AS PROCEDURE hire_employee( p_name IN VARCHAR2, p_dept_id IN NUMBER, p_salary IN NUMBER ) AS BEGIN IF p_salary > c_max_salary THEN RAISE_APPLICATION_ERROR(-20001, 'Salary exceeds maximum'); END IF; INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES (emp_seq.NEXTVAL, p_name, p_dept_id, p_salary); COMMIT; END hire_employee; FUNCTION get_employee_count(p_dept_id IN NUMBER) RETURN NUMBER AS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = p_dept_id; RETURN v_count; END get_employee_count; FUNCTION get_department_employees(p_dept_id IN NUMBER) RETURN emp_tab PIPELINED AS BEGIN FOR rec IN ( SELECT employee_id, employee_name, salary FROM employees WHERE department_id = p_dept_id ) LOOP PIPE ROW(emp_rec(rec.employee_id, rec.employee_name, rec.salary)); END LOOP; RETURN; END get_department_employees; END employee_pkg; / ``` ### 7.5 Triggers ```sql -- Row-level trigger CREATE OR REPLACE TRIGGER emp_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW DECLARE v_action VARCHAR2(10); BEGIN IF INSERTING THEN v_action := 'INSERT'; ELSIF UPDATING THEN v_action := 'UPDATE'; ELSIF DELETING THEN v_action := 'DELETE'; END IF; INSERT INTO emp_audit ( audit_id, employee_id, action, action_date, old_salary, new_salary, changed_by ) VALUES ( audit_seq.NEXTVAL, NVL(:NEW.employee_id, :OLD.employee_id), v_action, SYSDATE, :OLD.salary, :NEW.salary, USER ); END; / -- Statement-level trigger CREATE OR REPLACE TRIGGER dept_stats_trigger AFTER INSERT OR UPDATE OR DELETE ON employees DECLARE BEGIN -- Update department statistics MERGE INTO department_stats t USING ( SELECT department_id, COUNT(*) as emp_count, AVG(salary) as avg_salary FROM employees GROUP BY department_id ) s ON (t.department_id = s.department_id) WHEN MATCHED THEN UPDATE SET t.emp_count = s.emp_count, t.avg_salary = s.avg_salary WHEN NOT MATCHED THEN INSERT (department_id, emp_count, avg_salary) VALUES (s.department_id, s.emp_count, s.avg_salary); END; / -- INSTEAD OF trigger (for views) CREATE OR REPLACE TRIGGER emp_view_trigger INSTEAD OF INSERT ON emp_department_view FOR EACH ROW BEGIN INSERT INTO employees (employee_id, employee_name, department_id) VALUES (:NEW.employee_id, :NEW.employee_name, :NEW.department_id); END; / ``` ### 7.6 Conversion Patterns for Unsupported Features #### DBMS_AQ (Advanced Queuing) to HeliosDB Streaming ```sql -- Oracle AQ DBMS_AQ.ENQUEUE( queue_name => 'order_queue', message_properties => msg_props, payload => order_message ); -- HeliosDB: Use streaming table INSERT INTO order_stream (order_id, order_data, created_at) VALUES (123, '{"customer": "ABC"}', CURRENT_TIMESTAMP); -- Consumer reads from stream SELECT * FROM order_stream WHERE processed = FALSE ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 10; ``` #### Oracle Spatial to HeliosDB Geospatial ```sql -- Oracle SDO_GEOMETRY SELECT * FROM stores WHERE SDO_WITHIN_DISTANCE( location, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.98, 40.75, NULL), NULL, NULL), 'distance=10 unit=km' ) = 'TRUE'; -- HeliosDB geospatial SELECT * FROM stores WHERE ST_DWithin( location, ST_SetSRID(ST_MakePoint(-73.98, 40.75), 4326), 10000 -- meters ); ``` --- ## 8. Application Connectivity ### 8.1 TNS Configuration #### tnsnames.ora for HeliosDB ``` HELIOSDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = heliosdb-server)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = heliosdb) ) ) # High availability with multiple hosts HELIOSDB_HA = (DESCRIPTION = (LOAD_BALANCE = ON) (FAILOVER = ON) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = helios1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = helios2)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = helios3)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = heliosdb) ) ) ``` ### 8.2 JDBC Configuration ```java import java.sql.*; import oracle.jdbc.pool.OracleDataSource; public class HeliosDBConnection { public static void main(String[] args) throws SQLException { // Simple connection String url = "jdbc:oracle:thin:@//heliosdb-server:1521/heliosdb"; Connection conn = DriverManager.getConnection(url, "user", "password"); // With connection pool OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:thin:@//heliosdb-server:1521/heliosdb"); ods.setUser("user"); ods.setPassword("password"); // Pool settings ods.setConnectionCacheEnabled(true); ods.setConnectionCacheName("HELIOS_POOL"); Properties cacheProps = new Properties(); cacheProps.setProperty("MinLimit", "5"); cacheProps.setProperty("MaxLimit", "50"); cacheProps.setProperty("InitialLimit", "10"); ods.setConnectionCacheProperties(cacheProps); Connection pooledConn = ods.getConnection(); } } ``` ### 8.3 Python (oracledb) Configuration ```python import oracledb # Thin mode (no Oracle Client needed) oracledb.init_oracle_client() # Optional for thick mode # Simple connection connection = oracledb.connect( user="admin", password="password", dsn="heliosdb-server:1521/heliosdb" ) # Connection pool pool = oracledb.create_pool( user="admin", password="password", dsn="heliosdb-server:1521/heliosdb", min=5, max=50, increment=5, threaded=True, getmode=oracledb.POOL_GETMODE_WAIT ) # Get connection from pool with pool.acquire() as connection: with connection.cursor() as cursor: cursor.execute("SELECT * FROM employees WHERE department_id = :1", [10]) for row in cursor: print(row) ``` ### 8.4 Node.js (oracledb) Configuration ```javascript const oracledb = require('oracledb'); // Connection pool async function initPool() { await oracledb.createPool({ user: 'admin', password: 'password', connectString: 'heliosdb-server:1521/heliosdb', poolMin: 5, poolMax: 50, poolIncrement: 5, poolTimeout: 60 }); } // Execute query async function runQuery() { let connection; try { connection = await oracledb.getConnection(); const result = await connection.execute( `SELECT employee_id, first_name, salary FROM employees WHERE department_id = :deptId`, { deptId: 10 }, { outFormat: oracledb.OUT_FORMAT_OBJECT } ); console.log(result.rows); } finally { if (connection) { await connection.close(); } } } ``` ### 8.5.NET (ODP.NET) Configuration ```csharp using Oracle.ManagedDataAccess.Client; // Connection string string connString = @" User Id=admin; Password=password; Data Source=heliosdb-server:1521/heliosdb; Pooling=true; Min Pool Size=5; Max Pool Size=50; Connection Timeout=30"; using (OracleConnection conn = new OracleConnection(connString)) { conn.Open(); using (OracleCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT * FROM employees WHERE department_id = :dept"; cmd.Parameters.Add("dept", OracleDbType.Int32).Value = 10; using (OracleDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine($"{reader["EMPLOYEE_ID"]}: {reader["FIRST_NAME"]}"); } } } } ``` --- ## 9. Post-Migration Validation ### 9.1 Data Integrity Validation #### Row Count Verification ```sql -- Generate comparison queries SELECT 'SELECT ''' || table_name || ''' as table_name, COUNT(*) as row_count FROM ' || owner || '.' || table_name || ';' as validation_query FROM dba_tables WHERE owner = 'HR' ORDER BY table_name; -- Execute on both Oracle and HeliosDB, compare results ``` #### Checksum Validation ```sql -- Oracle: Generate row checksums SELECT table_name, ORA_HASH( LISTAGG(column_value, '|') WITHIN GROUP (ORDER BY column_name) ) as table_checksum FROM ( SELECT c.table_name, c.column_name, SUM(ORA_HASH(c.column_name)) as column_value FROM dba_tab_columns c WHERE c.owner = 'HR' GROUP BY c.table_name, c.column_name ) GROUP BY table_name; -- Compare with HeliosDB SELECT table_name, HASH(STRING_AGG(column_value::text, '|' ORDER BY column_name)) as table_checksum FROM (...) GROUP BY table_name; ``` ### 9.2 Functional Validation #### Stored Procedure Testing ```sql -- Create test harness CREATE TABLE migration_test_results ( test_id NUMBER PRIMARY KEY, test_name VARCHAR2(200), test_type VARCHAR2(50), oracle_result VARCHAR2(4000), helios_result VARCHAR2(4000), match_status VARCHAR2(20), test_date TIMESTAMP DEFAULT SYSTIMESTAMP ); -- Test procedure execution DECLARE v_oracle_result VARCHAR2(4000); v_helios_result VARCHAR2(4000); BEGIN -- Execute on Oracle v_oracle_result := test_procedure_on_oracle(); -- Execute on HeliosDB v_helios_result := test_procedure_on_heliosdb(); INSERT INTO migration_test_results ( test_id, test_name, test_type, oracle_result, helios_result, match_status ) VALUES ( test_seq.NEXTVAL, 'calculate_salary', 'FUNCTION', v_oracle_result, v_helios_result, CASE WHEN v_oracle_result = v_helios_result THEN 'PASS' ELSE 'FAIL' END ); END; / ``` ### 9.3 Performance Validation #### Query Performance Comparison ```sql -- Capture Oracle execution times SET TIMING ON SET AUTOTRACE ON SELECT /*+ Oracle baseline */ * FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 1700; -- Compare with HeliosDB EXPLAIN ANALYZE SELECT * FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 1700; ``` ### 9.4 Validation Checklist | Category | Validation Item | Result | |----------|-----------------|--------| | **Data** | Row counts match | [ ] | | **Data** | Checksums match | [ ] | | **Data** | Sample data spot checks | [ ] | | **Schema** | All tables created | [ ] | | **Schema** | All indexes created | [ ] | | **Schema** | All constraints active | [ ] | | **Schema** | All sequences created | [ ] | | **PL/SQL** | Procedures ported to PL/pgSQL compile | [ ] | | **PL/SQL** | Ported functions return correct values | [ ] | | **PL/SQL** | Ported packages compile | [ ] | | **PL/SQL** | Ported triggers fire correctly | [ ] | | **Security** | Users created | [ ] | | **Security** | Grants applied | [ ] | | **Performance** | Key queries perform acceptably | [ ] | | **Application** | Connection successful | [ ] | | **Application** | CRUD operations work | [ ] | | **Application** | Transactions commit/rollback | [ ] | --- ## 10. Performance Considerations ### 10.1 HeliosDB Performance Features HeliosDB provides automatic performance optimizations: | Feature | Description | Benefit | |---------|-------------|---------| | **Auto-Indexing** | ML-based index recommendations | 30-50% query improvement | | **Adaptive Query Execution** | Runtime plan optimization | Handles data skew | | **Global Distributed Cache** | Multi-tier caching | 10-100x repeated queries | | **Query Pattern Learning** | Learns access patterns | Proactive optimization | | **Predicate Pushdown** | Filter pushdown to storage | 3-10x faster scans | ### 10.2 Index Optimization ```sql -- HeliosDB auto-index recommendation EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND order_date > DATE '2024-01-01'; -- Review index recommendations SELECT * FROM heliosdb_index_recommendations WHERE table_name = 'orders'; -- Apply recommended indexes CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date); ``` ### 10.3 Statistics Collection > **`ANALYZE` in the current release:** `ANALYZE` (including `VACUUM ANALYZE`) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. `EXPLAIN ANALYZE`, which runs the query and reports actual rows and timing, works today. ```sql -- Analyze tables after migration ANALYZE TABLE employees COMPUTE STATISTICS; -- For large tables, use sampling ANALYZE TABLE large_orders ESTIMATE STATISTICS SAMPLE 10 PERCENT; -- Schedule regular statistics updates -- (PL/SQL block: PL/SQL execution through the Oracle listener is on the -- roadmap; until then, schedule the statement with cron or a job scheduler) BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'NIGHTLY_STATS', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN ANALYZE; END;', repeat_interval => 'FREQ=DAILY; BYHOUR=3' ); END; / ``` ### 10.4 Query Tuning ```sql -- Enable query hints (Oracle-compatible) SELECT /*+ INDEX(e idx_emp_dept) */ * FROM employees e WHERE department_id = 10; -- Parallel query execution SELECT /*+ PARALLEL(orders, 8) */ customer_id, SUM(amount) FROM orders GROUP BY customer_id; -- Use EXPLAIN PLAN EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); ``` ### 10.5 Memory and Resource Tuning ```toml # HeliosDB configuration (heliosdb.toml) [oracle.memory] pga_aggregate_target = "4GB" sort_area_size = 256000 hash_area_size = 512000 [oracle.optimizer] mode = "ALL_ROWS" cost_based = true query_rewrite = true [cache] query_result_cache_size = "1GB" metadata_cache_size = "256MB" ``` --- ## 11. Common Issues and Troubleshooting ### 11.1 Connection Issues #### Error: ORA-12154 (TNS Alias Not Found) **Cause:** TNS name not in tnsnames.ora or LDAP **Solution:** ```bash # Verify tnsnames.ora location echo $TNS_ADMIN cat $TNS_ADMIN/tnsnames.ora # Test with Easy Connect sqlplus admin/password@//heliosdb-server:1521/heliosdb ``` #### Error: ORA-12541 (No Listener) **Cause:** HeliosDB Oracle protocol not running **Solution:** ```bash # Check HeliosDB Oracle listener status heliosdb status --protocol oracle # Verify port is open netstat -an | grep 1521 telnet heliosdb-server 1521 ``` ### 11.2 SQL Compatibility Issues #### Error: Invalid Column Name/Type **Cause:** Reserved word or unsupported type **Solution:** ```sql -- Quote reserved words SELECT "LEVEL", "SIZE", "DATE" FROM my_table; -- Check for unsupported types SELECT column_name, data_type FROM user_tab_columns WHERE data_type IN ('SDO_GEOMETRY', 'ANYDATA', 'BFILE'); ``` #### PL/SQL block reports success but has no effect **Cause:** PL/SQL is not executed through the Oracle listener yet (it is on the roadmap) **Solution:** port the logic to PL/pgSQL functions and run them through the PostgreSQL interface, or move it into the application tier. On the Oracle side, find code that uses packages with no HeliosDB counterpart: ```sql -- Check for unsupported packages SELECT name, text FROM user_source WHERE UPPER(text) LIKE '%DBMS_AQ%' -- Not supported OR UPPER(text) LIKE '%SDO_%'; -- Not supported ``` ### 11.3 Data Migration Issues #### Large Object (LOB) Migration Errors **Cause:** LOB size or corruption **Solution:** ```sql -- Verify LOB integrity on Oracle SELECT table_name, column_name, segment_name FROM dba_lobs WHERE owner = 'HR'; -- Check for corrupted LOBs SELECT id, DBMS_LOB.GETLENGTH(document) as lob_length FROM documents WHERE DBMS_LOB.GETLENGTH(document) IS NULL; ``` #### Character Set Conversion Issues **Cause:** Different character sets **Solution:** ```sql -- Check Oracle character set SELECT parameter, value FROM nls_database_parameters WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); -- Configure HeliosDB to match ALTER SESSION SET NLS_CHARACTERSET = 'AL32UTF8'; ``` ### 11.4 Performance Issues #### Slow Queries After Migration **Cause:** Missing statistics or indexes **Solution:** ```sql -- Rebuild statistics ANALYZE TABLE slow_table COMPUTE STATISTICS; -- Check for missing indexes SELECT * FROM heliosdb_index_recommendations WHERE estimated_improvement > 50; -- Force index usage if needed SELECT /*+ INDEX(t idx_name) */ * FROM slow_table t WHERE ...; ``` #### High Memory Usage **Cause:** Large result sets or inefficient queries **Solution:** ```sql -- Use pagination SELECT * FROM large_table WHERE ROWNUM <= 1000 OFFSET 0 ROWS; -- Enable result set streaming SET ARRAYSIZE 100 ``` ### 11.5 Error Code Mapping | Oracle Error | HeliosDB Error | Resolution | |--------------|----------------|------------| | ORA-00001 | Unique constraint violation | Same handling | | ORA-00054 | Resource busy | Retry transaction | | ORA-00060 | Deadlock detected | Auto-resolved, retry | | ORA-01403 | No data found | Same handling | | ORA-01422 | Too many rows | Same handling | | ORA-01555 | Snapshot too old | Increase retention | | ORA-04031 | Shared pool exhausted | Increase cache size | --- ## 12. Rollback Procedures ### 12.1 Pre-Migration Rollback Preparation Before starting migration, ensure rollback capability: ```sql -- Create rollback savepoint in Oracle CREATE RESTORE POINT pre_migration GUARANTEE FLASHBACK DATABASE; -- Document current Oracle configuration SELECT name, value FROM v$parameter ORDER BY name; -- Export application connection strings -- Document all TNS entries ``` ### 12.2 During Migration Rollback If migration fails mid-process: ```bash # Stop HeliosDB data ingestion heliosdb migration pause --job-id # Verify Oracle is still primary sqlplus admin/password@ORACLE_PRIMARY # Check Oracle Data Guard sync status (if using) SELECT database_role, open_mode FROM v$database; ``` ### 12.3 Post-Migration Rollback If issues are discovered after cutover: #### Step 1: Switch Back to Oracle ```bash # Update DNS/Load balancer to point to Oracle # Update application connection strings # For applications using TNS # Edit tnsnames.ora to point back to Oracle PRODUCTION = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-server)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = ORCL)) ) ``` #### Step 2: Sync Data Back to Oracle ```sql -- If using parallel write during migration -- Data should already be in Oracle -- If not, export from HeliosDB EXPORT TABLE changed_data TO '/backup/changes.dmp' WHERE modified_date > TO_DATE('2024-01-15', 'YYYY-MM-DD'); -- Import to Oracle impdp system/password@ORCL \ DUMPFILE=changes.dmp \ DIRECTORY=DUMP_DIR \ TABLE_EXISTS_ACTION=REPLACE ``` #### Step 3: Restore Oracle from Flashback ```sql -- If Oracle Flashback was configured SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO RESTORE POINT pre_migration; ALTER DATABASE OPEN RESETLOGS; ``` ### 12.4 Rollback Checklist | Step | Action | Result | |------|--------|--------| | 1 | Stop HeliosDB writes | [ ] | | 2 | Verify Oracle connectivity | [ ] | | 3 | Update connection strings | [ ] | | 4 | Test application connectivity | [ ] | | 5 | Sync any delta data | [ ] | | 6 | Validate data integrity | [ ] | | 7 | Resume Oracle operations | [ ] | | 8 | Notify stakeholders | [ ] | ### 12.5 Rollback Decision Criteria Consider rollback if: - Data integrity issues that cannot be resolved within SLA - Performance degradation >50% on critical queries - Application functionality failures affecting users - Security vulnerabilities discovered - Unplanned extended downtime Do NOT rollback for: - Minor performance differences (optimize instead) - Cosmetic issues in monitoring/tooling - Non-critical feature differences - Issues with clear workarounds --- ## Appendix A: Quick Reference Card ### Essential Commands ```sql -- Connect to HeliosDB via Oracle protocol sqlplus admin/password@//heliosdb:1521/heliosdb -- Check Oracle compatibility mode SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL; -- View active sessions SELECT * FROM v$session WHERE type = 'USER'; -- Analyze performance EXPLAIN PLAN FOR ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); ``` ### Configuration Files | File | Location | Purpose | |------|----------|---------| | tnsnames.ora | $TNS_ADMIN/ | Connection aliases | | heliosdb.toml | /etc/heliosdb/ | Server configuration | | listener.ora | $TNS_ADMIN/ | Not needed for HeliosDB | ### Support Resources - HeliosDB Oracle Protocol Documentation: `/docs/reference/protocols/oracle/` - Migration Tools: `heliosdb migrate --help` - Support: support@heliosdb.com --- ## Appendix B: Migration Timeline Template | Week | Phase | Activities | |------|-------|------------| | 1 | Assessment | Inventory, complexity analysis | | 2 | Planning | Design migration strategy, create test plan | | 3-4 | Schema Migration | Export DDL, translate, create in HeliosDB | | 5-6 | Data Migration | Initial data load, verify | | 7-8 | PL/SQL Migration | Convert procedures, functions, packages to PL/pgSQL | | 9 | Testing | Functional testing, performance testing | | 10 | Parallel Run | Dual-write, validate | | 11 | Cutover | Switch primary to HeliosDB | | 12 | Stabilization | Monitor, optimize, close out | --- **Related Documentation:** - [Oracle Protocol Configuration](https://www.heliosdb.com/docs/full/reference/protocols/oracle/configuration/) - [Oracle Compatibility Reference](https://www.heliosdb.com/docs/full/reference/protocols/oracle/compatibility/) - [Oracle SQL Examples](https://www.heliosdb.com/docs/full/reference/protocols/oracle/examples/) - [General Migration Guide](https://www.heliosdb.com/docs/full/reference/protocols/migration_guide/)