Stories

Detail Return Return

Oracle 19c數據庫遷移到IvorySQL 4.6實戰 - Stories Detail

1. 背景

在國家數字化轉型與信創產業加速推進的背景下，數據庫作為信息系統的核心基礎設施，其國產化替代已成為企業數字化建設的重要戰略方向。Oracle 數據庫憑藉成熟的技術體系長期佔據市場主導地位，但在自主可控、成本優化及適配本土生態等需求驅動下，基於開源技術演進的國產數據庫逐漸成為遷移替代的優選方案。

IvorySQL 作為一款兼容 PostgreSQL 生態的國產數據庫，不僅繼承了 PostgreSQL 的開源特性與擴展性，還針對企業級場景增強了兼容性與穩定性，尤其在兼容 Oracle 語法、數據類型及存儲過程等方面進行了優化，成為 Oracle 遷移的理想目標之一。

本次實踐旨在通過 Ora2Pg 工具（一款專注於 Oracle 到 PostgreSQL 生態的遷移工具），完整演示從 Oracle 19c 到 IvorySQL 4.6 的全量遷移過程，包括環境搭建、對象轉換、數據遷移、兼容性處理及驗證等關鍵環節。通過構建模擬業務場景的測試數據（含表、自定義類型、存儲過程、觸發器等典型對象），還原真實遷移中的技術細節與問題解決思路，為企業級數據庫國產化遷移提供可複用的實踐參考。

2. 環境説明

主機名 ip 地址 OS 版本 內存、CPU 安裝軟件 用途
node1 192.*.*.60 Centos7.9 4G 、 1 個雙核 IvorySQL 4.6 數據庫 IvorySQL 4.6 數據庫
node1 192.*.*.60 Centos7.9 4G 、 1 個雙核 ora2pg 軟件 遷移數據庫 oracle->IvorySQL
node3 192.*.*.64 Centos7.9 4G 、 1 個雙核 Oracle19c 數據庫 Oracle19c 數據庫

3. 遷移工具 Ora2Pg 部署

Ora2Pg 是一款免費工具，用於將 Oracle 數據庫遷移到與 PostgreSQL 數據庫中。它會連接 Oracle 數據庫，自動掃描並提取其結構或數據，然後生成可加載到 PostgreSQL 數據庫中的 SQL 腳本。

3.1 在遷移主機上安裝 Ora2Pg 工具

3.1.1 安裝依賴包

yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN

3.1.2 下載並安裝 Oracle 客户端

下載地址：https://www.oracle.com/database/technologies/instant-client/l...

#安裝oracle客户端
rpm -ivh oracle-instantclient19.28-basic-19.28.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.28-devel-19.28.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.28-jdbc-19.28.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.28-sqlplus-19.28.0.0.0-1.x86_64.rpm

安裝好 Oracle 客户端後，配置 tnsnames.ora 文件，以便能正常連接到 Oracle 19c 數據庫。

vi /usr/lib/oracle/19.28/client64/network/admin/tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.*.*.64)(PORT = 1539))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

3.1.3 下載並安裝 DBI

下載地址：https://www.cpan.org/modules/by-module/DBI/

tar -zxvf DBI-1.647.tgz
cd DBI-1.647
perl Makefile.PL
make && make install

3.1.4 下載並安裝 DBD::Oracle

下載地址：https://www.cpan.org/modules/by-module/DBD/

#設置環境變量
export ORACLE_HOME=/usr/lib/oracle/19.28/client64
export PATH=$ORACLE_HOME/bin:/data/ivory-4/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
#解壓DBD::Oracle
tar -zxvf DBD-Oracle-1.90.tar.gz
#編譯安裝DBD::Oracle
cd DBD-Oracle-1.90
perl Makefile.PL
make && make install

3.1.5 下載並安裝 DBD::Pg

下載地址：https://www.cpan.org/modules/by-module/DBD/

#解壓DBD::Pg
tar -zxvf DBD-Pg-3.18.0.tar.gz
#編譯安裝DBD::Pg
cd DBD-Pg-3.18.0
perl Makefile.PL
make && make install

3.1.6 下載並安裝 Ora2Pg

下載地址：https://github.com/darold/ora2pg/releases/tag/v25.0/ora2pg-25.0.tar.gz

tar -zxvf ora2pg-25.0.tar.gz
cd ora2pg-25.0
perl Makefile.PL
make && make install

Ora2Pg 工具安裝完之後，在 /etc/ora2pg 目錄下會生成 ora2pg.conf.dist 配置文件。

[root@node1 ora2pg]# pwd
/etc/ora2pg
[root@node1 ora2pg]# ll
total 72
-rw-r--r-- 1 root root 71836 Sep 20 20:34 ora2pg.conf.dist

3.1.7 檢查 DBI，DBD::Oracle，DBD::Pg ，Ora2Pg 組件是否已經安裝完成

vi check.pl 加入：
#!/usr/bin/perl
use strict;
use ExtUtils::Installed;
my $inst=ExtUtils::Installed->new();
my @modules = $inst->modules();
foreach(@modules){
       my $ver = $inst->version($_) || "???";
       printf("%-12s -- %s\n",$_,$ver);
       }
exit;
perl /check.pl

執行結果如下：

[root@node1 ~]# perl /check.pl
DBD::Oracle  -- 1.90
DBD::Pg      -- 3.18.0
DBI          -- 1.647
Ora2Pg       -- 25.0
Perl         -- 5.16.3
[root@node1 ~]#

至此，Ora2Pg 工具正常安裝完成。

3.2 Ora2Pg 工具使用

3.2.1 生成配置文件 ora2pg.conf

#去掉ora2pg.conf.dist的註釋和空行
cd /etc/ora2pg
grep -v -E '^\s*(#|$)' ora2pg.conf.dist >ora2pg.conf

3.2.2 編輯配置文件 ora2pg.conf

根據 Oracle 19c 數據庫信息，在 ora2pg.conf 中修改下面信息，如下：

ORACLE_HOME     /data/app/oracle/product/19.3.0/db
ORACLE_DSN      dbi:Oracle:host=192.*.*.64;sid=orcl;port=1539
ORACLE_USER     system
ORACLE_PWD      ******

3.2.3 測試是否可以正常連接 Oracle 19c 數據庫

[root@node1 ora2pg]# ora2pg -t SHOW_VERSION -c /etc/ora2pg/ora2pg.conf
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

4. 遷移前準備

4.1 數據庫對象構造腳本

由於生產環境的數據庫對象有敏感數據，因此本次實驗全部使用自己構造的測試腳本來進行，如有雷同純屬巧合。

-- 創建用户
CREATE USER ywuser IDENTIFIED BY ******;
GRANT CONNECT,RESOURCE,CREATE SYNONYM TO ywuser;
ALTER USER ywuser QUOTA UNLIMITED ON USERS;

conn ywuser/pass

-- 創建自定義類型
CREATE OR REPLACE TYPE address_type AS OBJECT (
    street VARCHAR2(100),
    city VARCHAR2(50),
    zip_code VARCHAR2(10)
);
/

-- 創建表
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(100) NOT NULL,
    location address_type
);

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100),
    hire_date DATE DEFAULT SYSDATE,
    department_id NUMBER REFERENCES departments(department_id),
    salary NUMBER(10,2)
);

CREATE TABLE audit_log (
    log_id NUMBER PRIMARY KEY,
    table_name VARCHAR2(100),
    action VARCHAR2(10),
    change_date TIMESTAMP,
    user_name VARCHAR2(30)
);

CREATE TABLE salary_history (
    history_id NUMBER PRIMARY KEY,
    employee_id NUMBER REFERENCES employees(employee_id),
    old_salary NUMBER(10,2),
    new_salary NUMBER(10,2),
    change_date DATE
);

CREATE TABLE timestamp_demo (
    id NUMBER PRIMARY KEY,
    description VARCHAR2(100),
    created_time TIMESTAMP,  -- 精確到小數秒的時間戳
    last_updated TIMESTAMP WITH TIME ZONE  -- 帶時區信息的時間戳
);

-- 創建索引
CREATE INDEX IDX_salary_history ON salary_history(employee_id);

-- 創建序列
CREATE SEQUENCE employees_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE departments_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE salary_history_seq START WITH 1 INCREMENT BY 1;

-- 插入 departments 表數據
INSERT INTO departments (department_id, department_name, location) VALUES
(departments_seq.NEXTVAL, '人力資源', address_type('人民路100號', '北京', '100000'));
INSERT INTO departments (department_id, department_name, location) VALUES
(departments_seq.NEXTVAL, '財務部', address_type('金融大街88號', '上海', '200000'));
INSERT INTO departments (department_id, department_name, location) VALUES
(departments_seq.NEXTVAL, '技術研發', address_type('科技園路1號', '深圳', '518000'));
INSERT INTO departments (department_id, department_name, location) VALUES
(departments_seq.NEXTVAL, '市場營銷', address_type('商業中心廣場', '廣州', '510000'));
INSERT INTO departments (department_id, department_name, location) VALUES
(departments_seq.NEXTVAL, '客户服務', address_type('服務大道5號', '杭州', '310000'));

-- 插入 employees 表數據
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id, salary) VALUES
(employees_seq.NEXTVAL, '張', '明', 'zhang.ming@example.com', TO_DATE('2020-01-15', 'YYYY-MM-DD'), 1, 8000);
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id, salary) VALUES
(employees_seq.NEXTVAL, '李', '華', 'li.hua@example.com', TO_DATE('2019-03-20', 'YYYY-MM-DD'), 2, 9500);
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id, salary) VALUES
(employees_seq.NEXTVAL, '王', '強', 'wang.qiang@example.com', TO_DATE('2021-05-10', 'YYYY-MM-DD'), 3, 12000);
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id, salary) VALUES
(employees_seq.NEXTVAL, '趙', '雪', 'zhao.xue@example.com', TO_DATE('2018-11-05', 'YYYY-MM-DD'), 4, 8500);
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id, salary) VALUES
(employees_seq.NEXTVAL, '陳', '亮', 'chen.liang@example.com', TO_DATE('2022-02-28', 'YYYY-MM-DD'), 5, 7800);

-- 插入 salary_history 表數據
INSERT INTO salary_history (history_id, employee_id, old_salary, new_salary, change_date) VALUES
(salary_history_seq.NEXTVAL, 1, 7500, 8000, TO_DATE('2023-01-01', 'YYYY-MM-DD'));
INSERT INTO salary_history (history_id, employee_id, old_salary, new_salary, change_date) VALUES
(salary_history_seq.NEXTVAL, 2, 9000, 9500, TO_DATE('2023-01-01', 'YYYY-MM-DD'));
INSERT INTO salary_history (history_id, employee_id, old_salary, new_salary, change_date) VALUES
(salary_history_seq.NEXTVAL, 3, 11000, 12000, TO_DATE('2023-01-01', 'YYYY-MM-DD'));
INSERT INTO salary_history (history_id, employee_id, old_salary, new_salary, change_date) VALUES
(salary_history_seq.NEXTVAL, 4, 8000, 8500, TO_DATE('2023-01-01', 'YYYY-MM-DD'));
INSERT INTO salary_history (history_id, employee_id, old_salary, new_salary, change_date) VALUES
(salary_history_seq.NEXTVAL, 5, 7000, 7800, TO_DATE('2023-01-01', 'YYYY-MM-DD'));

-- 插入 timestamp_demo 表數據
INSERT INTO timestamp_demo VALUES (1, '第一條記錄',TO_TIMESTAMP('2023-10-01 08:00:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF6'),SYSTIMESTAMP);

INSERT INTO timestamp_demo VALUES (2, '第二條記錄',
SYSTIMESTAMP,  -- 使用系統當前時間戳
CURRENT_TIMESTAMP  -- 帶時區的當前時間戳
);

INSERT INTO timestamp_demo VALUES (3, '第三條記錄',
TIMESTAMP '2023-10-03 14:30:45.789123',  -- 時間戳字面量
SYSTIMESTAMP AT TIME ZONE 'UTC'  -- 指定UTC時區
);

commit;

-- 創建同義詞
CREATE SYNONYM emp_syn FOR employees;

-- 創建函數（使用自定義類型）
CREATE OR REPLACE FUNCTION get_department_address(
    p_department_id IN NUMBER
) RETURN address_type
IS
    v_address address_type;
BEGIN
  SELECT location INTO v_address  FROM departments WHERE department_id = p_department_id ;
  RETURN v_address;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- 創建存儲過程
CREATE OR REPLACE PROCEDURE increase_salary (
    p_emp_id IN NUMBER,
    p_percent IN NUMBER
)
IS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent/100)
    WHERE employee_id = p_emp_id;
    COMMIT;
END;
/

-- 創建包規範
CREATE OR REPLACE PACKAGE employee_pkg AS
    TYPE emp_cursor IS REF CURSOR;
    PROCEDURE get_employees(dept_id IN NUMBER, emp_list OUT emp_cursor);
    FUNCTION get_avg_salary(dept_id IN NUMBER) RETURN NUMBER;
END employee_pkg;
/

-- 創建包體
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
    PROCEDURE get_employees(dept_id IN NUMBER, emp_list OUT emp_cursor) IS
    BEGIN
        OPEN emp_list FOR
        SELECT * FROM employees
        WHERE department_id = dept_id;
    END;

    FUNCTION get_avg_salary(dept_id IN NUMBER) RETURN NUMBER IS
        v_avg_salary NUMBER;
    BEGIN
        SELECT AVG(salary) INTO v_avg_salary
        FROM employees
        WHERE department_id = dept_id;
        RETURN v_avg_salary;
    END;
END employee_pkg;
/

-- 創建觸發器
CREATE OR REPLACE TRIGGER salary_audit_trigger
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_history
    (history_id, employee_id, old_salary, new_salary, change_date)
    VALUES
    (employees_seq.NEXTVAL, :OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

CREATE OR REPLACE TRIGGER dml_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
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 audit_log (log_id, table_name, action, change_date, user_name)
    VALUES (employees_seq.NEXTVAL, 'EMPLOYEES', v_action, SYSTIMESTAMP, USER);
END;
/

4.2 查看 Oracle 19c 數據庫字符集和 sid 信息

SQL> SELECT value FROM nls_database_parameters  WHERE parameter = 'NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
ZHS16GBK

SQL> show parameter instance_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name                        string                            orcl
SQL>

4.3 遷移前評估成本

在評估過程中，Ora2Pg 會檢查所有數據庫對象，包括所有函數和存儲過程，以檢測是否仍有一些對象和 PL / SQL 代碼無法由 Ora2Pg 自動轉換，最終，通過內容分析模式，生成有關 Oracle 數據庫包含的內容和無法導出的內容的文本報告。

生成評估報告：

ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf

執行過程如下：

[root@node1 ora2pg]# ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf
[2025-09-21 10:20:54] [========================>] 5/5 tables (100.0%) end of scanning.
[2025-09-21 10:21:01] [========================>] 13/13 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v25.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Schema  YWUSER
Size    0.56 MB

-------------------------------------------------------------------------------
Object  Number  Invalid Estimated cost  Comments        Details
-------------------------------------------------------------------------------
DATABASE LINK   0       0       0.00    Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
FUNCTION        1       0       4.00    Total size of function code: 458 bytes. get_department_address: 3.
GLOBAL TEMPORARY TABLE  0       0       0.00    Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior.
INDEX   6       0       1.10    1 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es). Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns.     1 b-tree index(es).
JOB     0       0       0.00    Job are not exported. You may set external cron job with them.
PACKAGE BODY    1       0       12.00   Total size of package code: 504 bytes. Number of procedures and functions found inside those packages: 2.       employee_pkg.get_avg_salary: 3. employee_pkg.get_employees: 3.
PROCEDURE       1       0       4.00    Total size of procedure code: 339 bytes.        increase_salary: 3.
SEQUENCE        3       0       1.00    Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
SYNONYM 1       0       1.00    SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema.    emp_syn is an alias to YWUSER.EMPLOYEES.
TABLE   5       0       1.00            Total number of rows: 18. Top 10 of tables sorted by number of rows:. salary_history has 5 rows. departments has 5 rows. employees has 5 rows. timestamp_demo has 3 rows. Top 10 of largest tables:.
TRIGGER 2       0       8.00    Total size of trigger code: 0 bytes.    dml_audit_trigger: 3. salary_audit_trigger: 3.
TYPE    1       0       1.00    1 type(s) are concerned by the export, others are not supported. Note that Type inherited and Subtype are converted as table, type inheritance is not supported.        1 object type.
-------------------------------------------------------------------------------
Total   21      0       33.10   33.10 cost migration units means approximatively 1 person-day(s). The migration unit was set to 5 minute(s)

-------------------------------------------------------------------------------
Migration level : A-3
-------------------------------------------------------------------------------

Migration levels:
    A - Migration that might be run automatically
    B - Migration with code rewrite and a human-days cost up to 5 days
    C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
    1 = trivial: no stored functions and no triggers
    2 = easy: no stored functions but with triggers, no manual rewriting
    3 = simple: stored functions and/or triggers, no manual rewriting
    4 = manual: no stored functions but with triggers or views with code rewriting
    5 = difficult: stored functions and/or triggers with code rewriting
-------------------------------------------------------------------------------

Details of cost assessment per function
Function get_department_address total estimated cost: 3
        TEST => 2
        SIZE => 1
-------------------------------------------------------------------------------

Details of cost assessment per procedure
Function increase_salary total estimated cost: 3
        TEST => 2
        SIZE => 1
-------------------------------------------------------------------------------

Details of cost assessment per package function
Function employee_pkg.get_employees total estimated cost: 3
        TEST => 2
        SIZE => 1
Function employee_pkg.get_avg_salary total estimated cost: 3
        TEST => 2
        SIZE => 1
-------------------------------------------------------------------------------

Details of cost assessment per trigger
Trigger salary_audit_trigger total estimated cost: 3
        TEST => 2
        SIZE => 1
Trigger dml_audit_trigger total estimated cost: 3
        TEST => 2
        SIZE => 1
-------------------------------------------------------------------------------
[root@node1 ora2pg]#

可以從評估報告中看到遷移過程中哪些對象會發生轉換，哪些對象不被支持，遷移大概花費時間、成本等信息。

5. 數據庫遷移

5.1 創建項目

Ora2pg 可以針對每次遷移任務創建一個項目，項目下存放着遷移過程中生成的各個類型的對象創建腳本及數據。

ora2pg --project_base /data/migration --init_project test_project

執行過程如下：

[root@node1 ~]# ora2pg --project_base /data/migration --init_project test_project
Creating project test_project.
/data/migration/test_project/
        schema/
                dblinks/
                directories/
                functions/
                grants/
                mviews/
                packages/
                partitions/
                procedures/
                sequences/
                sequence_values/
                synonyms/
                tables/
                tablespaces/
                triggers/
                types/
                views/
        sources/
                functions/
                mviews/
                packages/
                partitions/
                procedures/
                triggers/
                types/
                views/
        data/
        config/
        reports/

Generating generic configuration file
Creating script export_schema.sh to automate all exports.
Creating script import_all.sh to automate all imports.
[root@node1 migration]# cd /data/migration/test_project/
[root@node1 test_project]# ll
total 24
drwxr-xr-x  2 root root    25 Sep 21 22:39 config
drwxr-xr-x  2 root root     6 Sep 21 22:39 data
-rwx------  1 root root  2216 Sep 21 22:39 export_schema.sh
-rwx------  1 root root 17201 Sep 21 22:39 import_all.sh
drwxr-xr-x  2 root root     6 Sep 21 22:39 reports
drwxr-xr-x 18 root root   268 Sep 21 22:39 schema
drwxr-xr-x 10 root root   131 Sep 21 22:39 sources
[root@node1 test_project]#

5.2 開始遷移

5.2.1 查看 oracle 數據庫中有哪些類型的對象

SQL> select distinct object_type from dba_objects WHERE owner='YWUSER';

OBJECT_TYPE
---------------------------------------------------------------------
INDEX
SYNONYM
PACKAGE BODY
TRIGGER
PROCEDURE
PACKAGE
FUNCTION
TYPE
SEQUENCE
TABLE

10 rows selected.

5.2.2 所有對象一次遷移

5.2.2.1 Oracle 數據庫對象定義（DDL）全部導出
cd /data/migration/test_project
cp /etc/ora2pg/ora2pg.conf /data/migration/test_project/config/
sh export_schema.sh

執行過程如下：

[root@node1 test_project]# cp /etc/ora2pg/ora2pg.conf /data/migration/test_project/config/
cp: overwrite ‘./ora2pg.conf’? y
[root@node1 test_project]# sh export_schema.sh
[2025-09-21 22:49:58] [========================>] 5/5 tables (100.0%) end of scanning.
Aborting export...
Running: ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf
[2025-09-21 22:49:59] [========================>] 3/3 sequences (100.0%) end of output.
Running: ora2pg -p -t SEQUENCE_VALUES -o sequence_value.sql -b ./schema/sequence_values -c ./config/ora2pg.conf
[2025-09-21 22:50:00] [========================>] 3/3 sequences (100.0%) end of output.
Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
[2025-09-21 22:50:41] [========================>] 5/5 tables (100.0%) end of scanning.
[2025-09-21 22:50:43] [========================>] 5/5 tables (100.0%) end of table export.
Running: ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf
[2025-09-21 22:50:44] [========================>] 1/1 packages (100.0%) end of output.
Running: ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf
[2025-09-21 22:50:46] [========================>] 0/0 views (100.0%) end of output.
Running: ora2pg -p -t GRANT -o grant.sql -b ./schema/grants -c ./config/ora2pg.conf
Running: ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf
[2025-09-21 22:51:14] [========================>] 2/2 triggers (100.0%) end of output.
Running: ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf
[2025-09-21 22:51:15] [========================>] 1/1 functions (100.0%) end of functions export.
Running: ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf
[2025-09-21 22:51:16] [========================>] 1/1 procedures (100.0%) end of procedures export.
Running: ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces -c ./config/ora2pg.conf
Running: ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf
[2025-09-21 22:52:56] [========================>] 0/0 partitions (100.0%) end of output.
Running: ora2pg -p -t TYPE -o type.sql -b ./schema/types -c ./config/ora2pg.conf
[2025-09-21 22:52:57] [========================>] 1/1 types (100.0%) end of output.
Running: ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews -c ./config/ora2pg.conf
[2025-09-21 22:52:58] [========================>] 0/0 materialized views (100.0%) end of output.
Running: ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf
[2025-09-21 22:52:59] [========================>] 0/0 dblink (100.0%) end of output.
Running: ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms -c ./config/ora2pg.conf
[2025-09-21 22:53:00] [========================>] 1/1 synonyms (100.0%) end of output.
Running: ora2pg -p -t DIRECTORY -o directorie.sql -b ./schema/directories -c ./config/ora2pg.conf
[2025-09-21 22:53:02] [========================>] 0/0 directory (100.0%) end of output.
Running: ora2pg -t PACKAGE -o package.sql -b ./sources/packages -c ./config/ora2pg.conf
[2025-09-21 22:53:03] [========================>] 1/1 packages (100.0%) end of output.
Running: ora2pg -t VIEW -o view.sql -b ./sources/views -c ./config/ora2pg.conf
[2025-09-21 22:53:06] [========================>] 0/0 views (100.0%) end of output.
Running: ora2pg -t TRIGGER -o trigger.sql -b ./sources/triggers -c ./config/ora2pg.conf
[2025-09-21 22:53:07] [========================>] 2/2 triggers (100.0%) end of output.
Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions -c ./config/ora2pg.conf
[2025-09-21 22:53:08] [========================>] 1/1 functions (100.0%) end of functions export.
Running: ora2pg -t PROCEDURE -o procedure.sql -b ./sources/procedures -c ./config/ora2pg.conf
[2025-09-21 22:53:09] [========================>] 1/1 procedures (100.0%) end of procedures export.
Running: ora2pg -t PARTITION -o partition.sql -b ./sources/partitions -c ./config/ora2pg.conf
[2025-09-21 22:53:35] [========================>] 0/0 partitions (100.0%) end of output.
Running: ora2pg -t TYPE -o type.sql -b ./sources/types -c ./config/ora2pg.conf
[2025-09-21 22:53:36] [========================>] 1/1 types (100.0%) end of output.
Running: ora2pg -t MVIEW -o mview.sql -b ./sources/mviews -c ./config/ora2pg.conf
[2025-09-21 22:53:37] [========================>] 0/0 materialized views (100.0%) end of output.


To extract data use the following command:

ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

[root@node1 test_project]#
5.2.2.2 Oracle 數據庫中的數據導出
ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

執行過程如下：

[root@node1 test_project]# ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf
[2025-09-22 00:04:47] [========================>] 5/5 tables (100.0%) end of scanning.
[2025-09-22 00:05:10] [========================>] 0/0 rows (100.0%) Table AUDIT_LOG (0 recs/sec)
[2025-09-22 00:05:10] [>                        ]  0/18 total rows (0.0%) - (0 sec., avg: 0 recs/sec).
[2025-09-22 00:05:10] [========================>] 5/5 rows (100.0%) Table DEPARTMENTS (5 recs/sec)
[2025-09-22 00:05:10] [======>                  ]  5/18 total rows (27.8%) - (0 sec., avg: 5 recs/sec).
[2025-09-22 00:05:10] [========================>] 5/5 rows (100.0%) Table EMPLOYEES (5 recs/sec)
[2025-09-22 00:05:10] [=============>           ] 10/18 total rows (55.6%) - (0 sec., avg: 10 recs/sec).
[2025-09-22 00:05:10] [========================>] 5/5 rows (100.0%) Table SALARY_HISTORY (5 recs/sec)
[2025-09-22 00:05:10] [====================>    ] 15/18 total rows (83.3%) - (0 sec., avg: 15 recs/sec).
[2025-09-22 00:05:11] [========================>] 3/3 rows (100.0%) Table TIMESTAMP_DEMO (3 recs/sec)
[2025-09-22 00:05:11] [========================>] 18/18 total rows (100.0%) - (1 sec., avg: 18 recs/sec).
[2025-09-22 00:05:11] [========================>] 18/18 rows (100.0%) on total estimated data (1 sec., avg: 18 recs/sec)

Schema Export Complete
5.2.2.3 IvorySQL 數據庫中創建數據庫、用户、schema
ivorysql=# create database ywdb;
CREATE DATABASE
ivorysql=# \c ywdb
You are now connected to database "ywdb" as user "ivorysql".
ywdb=# create schema ywuser;
CREATE SCHEMA
ywdb=# create user ywuser with password 'ywuser';
CREATE ROLE
ywdb=# alter user ywuser with superuser;
ALTER ROLE
5.2.2.4 Oracle 數據庫對象及數據導入 Ivorysql 數據庫
 sh import_all.sh -h 127.0.0.1 -p 5432 -d ywdb -o ywuser -U ivorysql  -n ywuser

執行過程如下：

[root@node1 test_project]# sh import_all.sh -h 127.0.0.1 -p 5432 -d ywdb -o ywuser -U ywuser  -n ywuser
Database owner ywuser already exists, skipping creation.
Would you like to drop the database ywdb before recreate it? [y/N/q] y
Running: dropdb -h 127.0.0.1 -p 5432 -U ywuser ywdb
Running: createdb -h 127.0.0.1 -p 5432 -U ywuser -E UTF8 --owner ywuser ywdb
Would you like to create schema ywuser in database ywdb? [y/N/q] y
Running: psql -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -c "CREATE SCHEMA ywuser;"
CREATE SCHEMA
Would you like to change search_path of the database owner? [y/N/q] y
Running: psql -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -c "ALTER ROLE ywuser SET search_path TO ywuser,public;"
ALTER ROLE
Would you like to import TYPE from ./schema/types/type.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/types/type.sql
SET
CREATE TYPE
Would you like to import SEQUENCE from ./schema/sequences/sequence.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/sequences/sequence.sql
SET
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
Would you like to import SEQUENCE_VALUES from ./schema/sequence_values/sequence_value.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/sequence_values/sequence_value.sql
SET
ALTER SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
Would you like to import TABLE from ./schema/tables/table.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/tables/table.sql
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
CREATE INDEX
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
Would you like to import PACKAGE from ./schema/packages/package.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/packages/package.sql
SET
psql:schema/packages/package.sql:13: NOTICE:  schema "employee_pkg" does not exist, skipping
DROP SCHEMA
CREATE SCHEMA
psql:schema/packages/package.sql:16: ERROR:  syntax error at or near "TYPE"
LINE 1: CREATE OR REPLACE TYPE employee_pkg.emp_cursor AS REFCURSOR;
                          ^
ERROR: an error occurs when importing file ./schema/packages/package.sql.
[root@node1 test_project]#

package 導入錯誤，修改一下 package.sql 代碼，解決辦法如下：

vi ./schema/packages/package.sql
#註釋掉這行
--CREATE OR REPLACE TYPE employee_pkg.emp_cursor AS REFCURSOR;
#下面的代碼修改成這樣：
CREATE OR REPLACE FUNCTION employee_pkg.get_employees(dept_id INT)
RETURNS SETOF employees
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM employees WHERE department_id = dept_id;
END;
$$;


CREATE OR REPLACE FUNCTION employee_pkg.get_avg_salary(dept_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    avg_salary NUMERIC;
BEGIN
    SELECT AVG(salary) INTO avg_salary
    FROM employees
    WHERE department_id = dept_id;

    RETURN avg_salary;
END;
$$;

重新導入數據庫對象和數據，如下：

[root@node1 test_project]# sh import_all.sh -h 127.0.0.1 -p 5432 -d ywdb -o ywuser -U ywuser  -n ywuser
Database owner ywuser already exists, skipping creation.
Would you like to drop the database ywdb before recreate it? [y/N/q] y
Running: dropdb -h 127.0.0.1 -p 5432 -U ywuser ywdb
Running: createdb -h 127.0.0.1 -p 5432 -U ywuser -E UTF8 --owner ywuser ywdb
Would you like to create schema ywuser in database ywdb? [y/N/q] y
Running: psql -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -c "CREATE SCHEMA ywuser;"
CREATE SCHEMA
Would you like to change search_path of the database owner? [y/N/q] y
Running: psql -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -c "ALTER ROLE ywuser SET search_path TO ywuser,public;"
ALTER ROLE
Would you like to import TYPE from ./schema/types/type.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/types/type.sql
SET
CREATE TYPE
Would you like to import SEQUENCE from ./schema/sequences/sequence.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/sequences/sequence.sql
SET
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
Would you like to import SEQUENCE_VALUES from ./schema/sequence_values/sequence_value.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/sequence_values/sequence_value.sql
SET
ALTER SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
Would you like to import TABLE from ./schema/tables/table.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/tables/table.sql
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
CREATE INDEX
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
Would you like to import PACKAGE from ./schema/packages/package.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/packages/package.sql
SET
psql:schema/packages/package.sql:13: NOTICE:  schema "employee_pkg" does not exist, skipping
DROP SCHEMA
CREATE SCHEMA
CREATE FUNCTION
CREATE FUNCTION
Would you like to import TRIGGER from ./schema/triggers/trigger.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/triggers/trigger.sql
SET
psql:schema/triggers/trigger.sql:9: NOTICE:  trigger "dml_audit_trigger" for relation "employees" does not exist, skipping
DROP TRIGGER
CREATE FUNCTION
CREATE TRIGGER
psql:schema/triggers/trigger.sql:39: NOTICE:  trigger "salary_audit_trigger" for relation "employees" does not exist, skipping
DROP TRIGGER
CREATE FUNCTION
CREATE TRIGGER
Would you like to import FUNCTION from ./schema/functions/function.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/functions/function.sql
SET
CREATE FUNCTION
Would you like to import PROCEDURE from ./schema/procedures/procedure.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/procedures/procedure.sql
SET
CREATE PROCEDURE
Would you like to import SYNONYM from ./schema/synonyms/synonym.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/synonyms/synonym.sql
SET
CREATE VIEW
Would you like to process indexes and constraints before loading data? [y/N/q] y
Would you like to import TRIGGER from ./schema/triggers/trigger.sql? [y/N/q] y
Running: psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/triggers/trigger.sql
SET
DROP TRIGGER
CREATE FUNCTION
CREATE TRIGGER
DROP TRIGGER
CREATE FUNCTION
CREATE TRIGGER
Would you like to import TABLESPACE from ./schema/tablespaces/tablespace.sql? [y/N/q] n
Would you like to import data from ./data/data.sql? [y/N/q] y
Running: psql -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./data/data.sql
BEGIN
SET
SET
COPY 0
SET
SET
psql:data/data.sql:21: ERROR:  malformed record literal: "({"(?oo?°?è·ˉ100??·,????o?,100000)"})"
DETAIL:  Too few columns.
CONTEXT:  COPY departments, line 1, column location: "({"(?oo?°?è·ˉ100??·,????o?,100000)"})"
psql:data/data.sql:23: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:data/data.sql:24: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:data/data.sql:27: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:data/data.sql:33: error: invalid command \.
psql:data/data.sql:35: ERROR:  syntax error at or near "1"
LINE 1: 1 張 明 zhang.ming@example.com 2020-01-15 00:00:00 1 8000
        ^
psql:data/data.sql:36: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:data/data.sql:39: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:data/data.sql:45: error: invalid command \.
psql:data/data.sql:47: ERROR:  syntax error at or near "1"
LINE 1: 1 1 7500 8000 2023-01-01 00:00:00
        ^
psql:data/data.sql:48: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:data/data.sql:51: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:data/data.sql:55: error: invalid command \.
psql:data/data.sql:57: ERROR:  syntax error at or near "1"
LINE 1: 1 第一條記錄 2023-10-01 08:00:00.123456 2025-09-20 16:07:00....
        ^

Ora2Pg ending
[root@node1 test_project]#

導入數據時報錯，因為涉及到自定義 type 類型，解決辦法：

--ORACLE端導出數據
SET pagesize 0
SET feedback off
SET linesize 1000
SPOOL departments.csv
SELECT
    department_id || ',' ||
    '"' || REPLACE(department_name, '"', '""') || '"' || ',' ||
        '"' || REPLACE(d.location.street, '"', '""') || '"' || ',' ||
        '"' || REPLACE(d.location.city, '"', '""') || '"' || ',' ||
        '"' || d.location.zip_code || '"'
FROM departments d;
SPOOL OFF
--departments.csv傳輸到遷移工具所在主機上
scp departments.csv 192.*.*.60:/data/migration/test_project/data/

--Ivorysql端導入數據
--1、先創建臨時表
ywdb=# CREATE TABLE temp_departments (
ywdb(#     department_id INTEGER,
ywdb(#     department_name VARCHAR(100),
ywdb(#     street VARCHAR(100),
ywdb(#     city VARCHAR(100),
ywdb(#     zip_code VARCHAR(20)
ywdb(# );
CREATE TABLE
--2、把csv文件中的數據先導入臨時表
ywdb=# COPY temp_departments(department_id, department_name, street, city, zip_code)
ywdb-# FROM '/data/migration/test_project/data/departments.csv'
ywdb-# WITH (FORMAT CSV);
COPY 5
--3、把臨時表中的數據經過格式轉換後插入正式表
ywdb=# INSERT INTO departments(department_id, department_name, location)
ywdb-# SELECT
ywdb-#     department_id,
ywdb-#     department_name,
ywdb-#     (street, city, zip_code)::address_type
ywdb-# FROM temp_departments;
INSERT 0 5
ywdb=# SELECT * FROM departments;
 department_id | department_name |             location
---------------+-----------------+----------------------------------
             1 | 人力資源        | (人民路100號,北京,"100000    ")
             2 | 財務部          | (金融大街88號,上海,"200000    ")
             3 | 技術研發        | (科技園路1號,深圳,"518000    ")
             4 | 市場營銷        | (商業中心廣場,廣州,"510000    ")
             5 | 客户服務        | (服務大道5號,杭州,"310000    ")
(5 rows)
--4、刪除臨時表
ywdb=# DROP TABLE temp_departments;
DROP TABLE

修改 ./data/data.sql，去掉 departments 表數據後，繼續導入其他表數據：

psql -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./data/data.sql

執行過程如下：

[root@node1 test_project]# psql -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./data/data.sql
BEGIN
SET
SET
COPY 0
SET
SET
SET
SET
COPY 5
SET
SET
COPY 5
SET
SET
COPY 3
COMMIT
[root@node1 test_project]#

至此，Oracle 中的全部對象和數據全部導入 IvorySQL 數據庫。

5.3 遷移後校驗

5.3.1 對象校驗

--Oracle數據庫中對象
SQL>  Select object_name,object_type from dba_objects where owner='YWUSER'

OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- ------------------------------
ADDRESS_TYPE                                       TYPE
DEPARTMENTS                                        TABLE
SYS_C008164                                        INDEX
EMPLOYEES                                          TABLE
SYS_C008166                                        INDEX
AUDIT_LOG                                          TABLE
SYS_C008168                                        INDEX
SALARY_HISTORY                                     TABLE
SYS_C008169                                        INDEX
TIMESTAMP_DEMO                                     TABLE
SYS_C008171                                        INDEX
EMPLOYEES_SEQ                                      SEQUENCE
DEPARTMENTS_SEQ                                    SEQUENCE
SALARY_HISTORY_SEQ                                 SEQUENCE
EMP_SYN                                            SYNONYM
GET_DEPARTMENT_ADDRESS                             FUNCTION
INCREASE_SALARY                                    PROCEDURE
EMPLOYEE_PKG                                       PACKAGE BODY
EMPLOYEE_PKG                                       PACKAGE
SALARY_AUDIT_TRIGGER                               TRIGGER
DML_AUDIT_TRIGGER                                  TRIGGER
IDX_SALARY_HISTORY                                 INDEX

22 rows selected.
--Ivorysql數據庫中對象
ywdb=# SELECT n.nspname, 'TABLE',       c.relname, pg_get_userbyid(c.relowner)
ywdb-# FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
ywdb-# WHERE c.relkind = 'r'
ywdb-# and n.nspname='ywuser'
ywdb-# UNION ALL
ywdb-# SELECT n.nspname, 'INDEX',       c.relname, pg_get_userbyid(c.relowner)
ywdb-# FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
ywdb-# WHERE c.relkind = 'i'
ywdb-# and n.nspname='ywuser'
ywdb-# UNION ALL
ywdb-# SELECT n.nspname, 'SEQUENCE',    c.relname, pg_get_userbyid(c.relowner)
ywdb-# FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
ywdb-# WHERE c.relkind = 'S'
ywdb-# and n.nspname='ywuser'
ywdb-# UNION ALL
ywdb-# SELECT n.nspname, 'VIEW',        c.relname, pg_get_userbyid(c.relowner)
ywdb-# FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
ywdb-# WHERE c.relkind = 'v'
ywdb-# and n.nspname='ywuser'
ywdb-# UNION ALL
ywdb-# SELECT n.nspname, 'MATERIALIZED_VIEW', c.relname, pg_get_userbyid(c.relowner)
ywdb-# FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
ywdb-# WHERE c.relkind = 'm'
ywdb-# and n.nspname='ywuser'
ywdb-# UNION ALL
ywdb-# SELECT n.nspname, 'FUNCTION',    p.proname, pg_get_userbyid(p.proowner)
ywdb-# FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
ywdb-# WHERE p.prokind = 'f'
ywdb-# and n.nspname='ywuser'
ywdb-# UNION ALL
ywdb-# SELECT n.nspname, 'PROCEDURE',   p.proname, pg_get_userbyid(p.proowner)
ywdb-# FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
ywdb-# WHERE p.prokind = 'p'
ywdb-# and n.nspname='ywuser'
ywdb-# UNION ALL
ywdb-# SELECT n.nspname, 'TRIGGER',     t.tgname, pg_get_userbyid(c.relowner)
ywdb-# FROM pg_trigger t
ywdb-# JOIN pg_class c ON c.oid = t.tgrelid
ywdb-# JOIN pg_namespace n ON n.oid = c.relnamespace
ywdb-# WHERE NOT t.tgisinternal
ywdb-# and n.nspname='ywuser';
 nspname | ?column?  |             relname              | pg_get_userbyid
---------+-----------+----------------------------------+-----------------
 ywuser  | TABLE     | audit_log                        | ywuser
 ywuser  | TABLE     | timestamp_demo                   | ywuser
 ywuser  | TABLE     | departments                      | ywuser
 ywuser  | TABLE     | employees                        | ywuser
 ywuser  | TABLE     | salary_history                   | ywuser
 ywuser  | INDEX     | audit_log_pkey                   | ywuser
 ywuser  | INDEX     | timestamp_demo_pkey              | ywuser
 ywuser  | INDEX     | departments_pkey                 | ywuser
 ywuser  | INDEX     | employees_pkey                   | ywuser
 ywuser  | INDEX     | idx_salary_history               | ywuser
 ywuser  | INDEX     | salary_history_pkey              | ywuser
 ywuser  | SEQUENCE  | departments_seq                  | ywuser
 ywuser  | SEQUENCE  | employees_seq                    | ywuser
 ywuser  | SEQUENCE  | salary_history_seq               | ywuser
 ywuser  | VIEW      | emp_syn                          | ywuser
 ywuser  | FUNCTION  | get_department_address           | ywuser
 ywuser  | FUNCTION  | trigger_fct_dml_audit_trigger    | ywuser
 ywuser  | FUNCTION  | trigger_fct_salary_audit_trigger | ywuser
 ywuser  | PROCEDURE | increase_salary                  | ywuser
 ywuser  | TRIGGER   | salary_audit_trigger             | ywuser
 ywuser  | TRIGGER   | dml_audit_trigger                | ywuser
(21 rows)

ywdb=# \dT
         List of data types
 Schema |     Name     | Description
--------+--------------+-------------
 ywuser | address_type |
(1 row)

ywdb=#

經比對，Oracle 數據庫中的對象和 IvorySQL 中的對象一致，都是 22 個。但是需要注意的是，Oracle 數據庫中的同義詞遷移到 IvorySQL 數據庫之後是以 view 的方式存在的。

5.3.2 數據校驗

--Oracle數據庫中的數據
SQL> Select 'DEPARTMENTS',count(*) from YWUSER.DEPARTMENTS
  2  union all
  3  Select 'EMPLOYEES',count(*) from YWUSER.EMPLOYEES
  4  union all
  5  Select 'AUDIT_LOG',count(*) from YWUSER.AUDIT_LOG
  6  union all
  7  Select 'SALARY_HISTORY',count(*) from YWUSER.SALARY_HISTORY
  8  union all
  9  Select 'TIMESTAMP_DEMO',count(*) from YWUSER.TIMESTAMP_DEMO;

'DEPARTMENTS'                                COUNT(*)
------------------------------------------ ----------
DEPARTMENTS                                         5
EMPLOYEES                                           5
AUDIT_LOG                                           0
SALARY_HISTORY                                      5
TIMESTAMP_DEMO                                      3

SQL>
--Ivorysql數據庫中的數據
ywdb=# Select 'DEPARTMENTS',count(*) from YWUSER.DEPARTMENTS
ywdb-# union all
ywdb-# Select 'EMPLOYEES',count(*) from YWUSER.EMPLOYEES
ywdb-# union all
ywdb-# Select 'AUDIT_LOG',count(*) from YWUSER.AUDIT_LOG
ywdb-# union all
ywdb-# Select 'SALARY_HISTORY',count(*) from YWUSER.SALARY_HISTORY
ywdb-# union all
ywdb-# Select 'TIMESTAMP_DEMO',count(*) from YWUSER.TIMESTAMP_DEMO;
    ?column?    | count
----------------+-------
 DEPARTMENTS    |     5
 EMPLOYEES      |     5
 AUDIT_LOG      |     1   <----這裏不一致，是因為Oracle數據庫中是先在表中插入數據，最後創建的觸發器，IvorySQL數據庫中是先創建的觸發器，最後導入的數據，觸發器生效了，這個完全可以忽略。
 SALARY_HISTORY |     5
 TIMESTAMP_DEMO |     3
(5 rows)

經比對，Oracle 數據庫中的對象和 IvorySQL 中的數據記錄數一致。

在生產中如果需要更詳細的數據一致性校驗，需要業務同事配合完成。

也可以按照對象類型單獨遷移對象及，可以自行嘗試下。下面是簡單示例：

（1）、導出表 ddl

ora2pg -t TABLE -b /data/migration/test_project/schema/tables -o ddl.sql -c /etc/ora2pg/ora2pg.conf

執行過程如下：

[root@node1 ora2pg]# ora2pg -t TABLE -b /data/migration/test_project/schema/tables -c /etc/ora2pg/ora2pg.conf
[2025-09-21 20:52:46] [========================>] 5/5 tables (100.0%) end of scanning.
[2025-09-21 20:53:39] [========================>] 5/5 tables (100.0%) end of table export.
[root@node1 ora2pg]#

遷移後在/data/migration/test_project/schema/tables 目錄下生成 ddl.sql 文件，裏面保存着表結構、主外鍵、約束、index 信息。

（2）、導出表數據

ora2pg -t copy -b /data/migration/test_project/schema/tables -o data.sql -c /etc/ora2pg/ora2pg.conf

執行過程如下：

[root@node1 tables]# ora2pg -t copy -b /data/migration/test_project/schema/tables -o data.sql -c /etc/ora2pg/ora2pg.conf
[2025-09-21 22:17:49] [========================>] 5/5 tables (100.0%) end of scanning.
[2025-09-21 22:19:19] [========================>] 0/0 rows (100.0%) Table AUDIT_LOG (0 recs/sec)
[2025-09-21 22:19:19] [>                        ]  0/18 total rows (0.0%) - (0 sec., avg: 0 recs/sec).
[2025-09-21 22:19:20] [========================>] 5/5 rows (100.0%) Table DEPARTMENTS (5 recs/sec)
[2025-09-21 22:19:20] [======>                  ]  5/18 total rows (27.8%) - (1 sec., avg: 5 recs/sec).
[2025-09-21 22:19:20] [========================>] 5/5 rows (100.0%) Table EMPLOYEES (5 recs/sec)
[2025-09-21 22:19:20] [=============>           ] 10/18 total rows (55.6%) - (1 sec., avg: 10 recs/sec).
[2025-09-21 22:19:20] [========================>] 5/5 rows (100.0%) Table SALARY_HISTORY (5 recs/sec)
[2025-09-21 22:19:20] [====================>    ] 15/18 total rows (83.3%) - (1 sec., avg: 15 recs/sec).
[2025-09-21 22:19:20] [========================>] 3/3 rows (100.0%) Table TIMESTAMP_DEMO (3 recs/sec)
[2025-09-21 22:19:20] [========================>] 18/18 total rows (100.0%) - (1 sec., avg: 18 recs/sec).
[2025-09-21 22:19:20] [========================>] 18/18 rows (100.0%) on total estimated data (1 sec., avg: 18 recs/sec)

Schema Export Complete

[root@node1 tables]#

遷移後在/data/migration/test_project/schema/tables 目錄下生成 data.sql 文件，裏面保存着表數據。

（3）、導出其他對象

ora2pg -t SEQUENCE,TRIGGER,FUNCTION,PROCEDURE,PROCEDURE,PACKAGE -b /data/migration/test_project/schema/ -c /etc/ora2pg/ora2pg.conf

（4）、導入 ivorysql

psql --single-transaction  -h 127.0.0.1 -p 5432 -U ywuser -d ywdb -f ./schema/******.sql

6. 總結

Oracle 數據庫的表中有自定義類型時，數據導入 IvorySQL 數據庫時需要手工處理。Oracle 19c 數據庫遷移到 IvorySQL 4.6 的整個過程還是比較簡單的。

參考文章：https://ora2pg.darold.net/documentation.html

關於作者

  • 網名：飛天，墨天輪 2024 年度優秀原創作者，擁有 Oracle 10g OCM 認證、PGCE 認證、MySQL 8.0 OCP 認證以及 OBCA、KCP、KCSM、ACP、YCP、磐維等眾多國產數據庫認證證書，目前從事 Oracle、Mysql、PostgresSQL、磐維數據庫管理運維工作，喜歡結交更多志同道合的朋友，熱衷於研究、分享數據庫技術。
  • 微信公眾號：飛天 online
  • 墨天輪：https://www.modb.pro/u/15197

如有任何疑問，歡迎大家留言，共同探討~~~

oracle
user avatar u_15591470 Avatar CuiPengJu Avatar binghe001 Avatar kerrycode Avatar junyidedalianmao Avatar ruyadekabuqinuo Avatar zlt2000 Avatar aixiaodekaomianbao_ddkwvd Avatar nocobase Avatar
Favorites 9 users favorite the story!
Favorites

Add a new Comments

Some HTML is okay.