Oracle 19c数据库迁移到IvorySQL 4.6实战

关注

原创最新推荐文章于 2026-11-14 14:38:32 发布 439 阅读 AI 写同款· GEO 优化 ›

SpringBoot+Vue+MySQL 高校学科竞赛平台完整可运行版（源码+文档）本项目基于SpringBoot+Vue+MySQL构建高校学科竞赛管理平台，覆盖学生、教师、领队教师、管理员四大角色，实现竞赛发布、报名、刷题、评分、公示等全流程闭环管理。系统架构清晰，技术主流，配套完整源码、数据库脚本、万字论文、开题报告、答辩PPT及部署文档，支持一键运行，适用于毕业设计、课程设计及院系二次开发，真正实现“开箱即用”。阅读详情

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/linux-x86-64-downloads.html

#安装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

执行过程如下：

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

如有任何疑问，欢迎大家留言，共同探讨~~~