使用Ora2Pg工具把数据从Oracle导入到PostgreSQL

本文只介绍如何使用Ora2Pg从Oracle导出数据到PostgreSQL，但是在操作前需要先安装先决软件DBD::Oracle、DBI、Ora2Pg。

安装参考：Linux下安装DBD::Oracle、DBI和Ora2Pg

安装Ora2Pg完成会在/etc目录下生成一个ora2pg目录里面有使用Ora2Pg的配置文件。

1、在Oracle上创建测试用户并创建测试表

sys@ORCL>create user zhaoxu identified by zhaoxu;User created.sys@ORCL>grant dba to zhaoxu;Grant succeeded.sys@ORCL>conn zhaoxu/zhaoxu;Connected.zhaoxu@ORCL>create table emp as select * from  scott.emp;Table created.zhaoxu@ORCL>select * from emp;     EMPNO ENAME                    JOB                              MGR HIREDATE             SAL       COMM     DEPTNO---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH                       CLERK                      7902 1980-12-17 00:00:00          800                   20      7499 ALLEN                       SALESMAN                        7698 1981-02-20 00:00:00         1600        300          30      7521 WARD                           SALESMAN                        7698 1981-02-22 00:00:00         1250        500          30      7566 JONES                       MANAGER                         7839 1981-04-02 00:00:00         2975                20      7654 MARTIN                      SALESMAN                        7698 1981-09-28 00:00:00         1250       1400       30      7698 BLAKE                       MANAGER                         7839 1981-05-01 00:00:00         2850                30      7782 CLARK                       MANAGER                         7839 1981-06-09 00:00:00         2450                10      7788 SCOTT                       ANALYST                         7566 1987-04-19 00:00:00         3000                20      7839 KING                           PRESIDENT                            1981-11-17 00:00:00     5000                10      7844 TURNER                      SALESMAN                        7698 1981-09-08 00:00:00         1500      0    30      7876 ADAMS                       CLERK                      7788 1987-05-23 00:00:00         1100                20      7900 JAMES                       CLERK                      7698 1981-12-03 00:00:00          950                   30      7902 FORD                           ANALYST                         7566 1981-12-03 00:00:00         3000                20      7934 MILLER                      CLERK                      7782 1982-01-23 00:00:00         1300                1014 rows selected.

2、修改参数文件

[oracle@rhel6 ora2pg]$ cp /etc/ora2pg/ora2pg.conf /home/oracle/ora2pg/[oracle@rhel6 ora2pg]$ cd /home/oracle/ora2pg/[oracle@rhel6 ora2pg]$ vi ora2pg.conf [oracle@rhel6 ora2pg]$ cat ora2pg.conf ORACLE_HOME     /u02/app/oracle/product/11.2.4/db1ORACLE_DSN      dbi:Oracle:host=192.168.56.2;sid=orclORACLE_USER     zhaoxuORACLE_PWD      zhaoxuSCHEMA          zhaoxuUSER_GRANTS     0DEBUG           0ORA_INITIAL_COMMANDEXPORT_SCHEMA   0CREATE_SCHEMA   1COMPILE_SCHEMA  0TYPE            TABLE,INSERTOUTPUT          output.sql

3、使用上面修改的参数导出数据

[oracle@rhel6 ora2pg]$ ora2pg -c ora2pg.conf [========================>] 1/1 tables (100.0%) end of scanning.  [>                        ] 0/1 tables (0.0%) end of scanning.    [========================>] 1/1 tables (100.0%) end of table export.[========================>] 14/1 rows (1400.0%) Table EMP (14 recs/sec)[========================>] 14/1 total rows (1400.0%) - (0 sec., avg: 14 recs/sec).[========================>] 1/1 rows (100.0%) on total estimated data (1 sec., avg: 1 recs/sec)[oracle@rhel6 ora2pg]$ cat output.sql -- Generated by Ora2Pg, the Oracle database Schema converter, version 17.6b-- Copyright 2000-2016 Gilles DAROLD. All rights reserved.-- DATASOURCE: dbi:Oracle:host=192.168.56.2;sid=orclSET client_encoding TO 'UTF8';\set ON_ERROR_STOP ONCREATE TABLE emp (        empno smallint,        ename varchar(10),        job varchar(9),        mgr smallint,        hiredate timestamp,        sal decimal(7,2),        comm decimal(7,2),        deptno smallint) ;-- Generated by Ora2Pg, the Oracle database Schema converter, version 17.6b-- Copyright 2000-2016 Gilles DAROLD. All rights reserved.-- DATASOURCE: dbi:Oracle:host=192.168.56.2;sid=orclSET client_encoding TO 'UTF8';\set ON_ERROR_STOP ONBEGIN;INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7369,E'SMITH',E'CLERK',7902,'1980-12-17 00:00:00',800,NULL,20);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,E'ALLEN',E'SALESMAN',7698,'1981-02-20 00:00:00',1600,300,30);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7521,E'WARD',E'SALESMAN',7698,'1981-02-22 00:00:00',1250,500,30);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7566,E'JONES',E'MANAGER',7839,'1981-04-02 00:00:00',2975,NULL,20);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7654,E'MARTIN',E'SALESMAN',7698,'1981-09-28 00:00:00',1250,1400,30);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7698,E'BLAKE',E'MANAGER',7839,'1981-05-01 00:00:00',2850,NULL,30);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7782,E'CLARK',E'MANAGER',7839,'1981-06-09 00:00:00',2450,NULL,10);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7788,E'SCOTT',E'ANALYST',7566,'1987-04-19 00:00:00',3000,NULL,20);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7839,E'KING',E'PRESIDENT',NULL,'1981-11-17 00:00:00',5000,NULL,10);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7844,E'TURNER',E'SALESMAN',7698,'1981-09-08 00:00:00',1500,0,30);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7876,E'ADAMS',E'CLERK',7788,'1987-05-23 00:00:00',1100,NULL,20);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7900,E'JAMES',E'CLERK',7698,'1981-12-03 00:00:00',950,NULL,30);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7902,E'FORD',E'ANALYST',7566,'1981-12-03 00:00:00',3000,NULL,20);INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7934,E'MILLER',E'CLERK',7782,'1982-01-23 00:00:00',1300,NULL,10);COMMIT;

4、把生成的output.sql传到PostgreSQL服务器上

[oracle@rhel6 ora2pg]$ scp output.sql pguser@192.168.56.25:/home/pguser/pguser@192.168.56.25's password: output.sql                                                                                                                                                                      100% 2599     2.5KB/s   00:00

5、在PostgreSQL数据库上创建对应的数据库、用户和Schema

#创建数据库 zhaoxupostgres=# create database zhaoxu;CREATE DATABASEpostgres=# \l                               List of databases   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges -----------+--------+----------+-------------+-------------+------------------- postgres  | pguser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |  template0 | pguser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pguser        +           |        |          |             |             | pguser=CTc/pguser template1 | pguser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pguser        +           |        |          |             |             | pguser=CTc/pguser zhaoxu    | pguser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |  zx        | pguser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | (5 rows)#创建用户 zhaoxu postgres=# create user zhaoxu superuser;CREATE ROLEpostgres=# \dg                                   List of roles Role name |                         Attributes                         | Member of -----------+------------------------------------------------------------+----------- lx        | Superuser, Cannot login                                    | {} pguser    | Superuser, Create role, Create DB, Replication, Bypass RLS | {} sq        | Superuser, Create role, Create DB                          | {} zhaoxu    | Superuser                                                  | {} zx        | Superuser                                                  | {}#在zhaoxu库下创建Schema zhaoxupostgres=# \c zhaoxu zhaoxuYou are now connected to database "zhaoxu" as user "zhaoxu".zhaoxu=# create schema zhaoxu;CREATE SCHEMAzhaoxu=# \dn List of schemas  Name  | Owner  --------+-------- public | pguser zhaoxu | zhaoxu(2 rows)

6、使用output.sql导入从Oracle导出的数据

[pguser@rhel7 ~]$ psql zhaoxu zhaoxu < output.sql SETCREATE TABLESETBEGININSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1COMMIT[pguser@rhel7 ~]$ psql zhaoxu zhaoxu psql (9.6.1)Type "help" for help.zhaoxu=# \d       List of relations Schema | Name | Type  | Owner  --------+------+-------+-------- zhaoxu | emp  | table | zhaoxu(1 row)zhaoxu=# select * from emp; empno | ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno -------+--------+-----------+------+---------------------+---------+---------+--------  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |         |     20  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |         |     20  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |         |     20  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |         |     10(14 rows)

数据导入完成。