ora2pg 测试（一）

ora2pg 测试（一）

环境

OS version: Red Hat Enterprise Linux Server release 6.5Oracle version: 19.3.0.0.0PostgreSQL version: 12.3ora2pg: v21.0

ps: 为了方便同步，在PG库中创建 scott 用户和模式。

目录结构

scott/├── conf│   ├── table_meta.conf│   └── table_sync.conf├── log├── oracle└── sql └── create_table.sql

获取表结构

先来看一下 ora2pg 配置文件的内容

[root@localhost conf]# cat table_meta.conf PG_VERSION 12ORACLE_HOME /usr/lib/oracle/12.2/client64/#Set Oracle database connection (data source, user, password)ORACLE_DSN dbi:Oracle:host=192.168.228.77;service_name=orcl;port=1521ORACLE_USER systemORACLE_PWD sys_1234SCHEMA scottDEBUG 1ORA_INITIAL_COMMANDEXPORT_SCHEMA 0CREATE_SCHEMA 1COMPILE_SCHEMA 0NLS_LANG AMERICAN_AMERICA.UTF8TYPE TABLE OUTPUT /root/shx/ora2pg/scott/sql/create_table.sql

Oracle 中 scott 的对象

OWNER OBJECT_NAME OBJECT_TYPE-------------------- ------------------------------ ----------------------------SCOTT PK_DEPT INDEXSCOTT EMP TABLESCOTT PK_EMP INDEXSCOTT BONUS TABLESCOTT SALGRADE TABLESCOTT DEPT TABLE

接下来通过上面的配置文件来获取oracle端的元数据：

[root@localhost ora2pg]# cd scott/conf/[root@localhost conf]# ora2pg -c table_meta.conf [2021-03-28 17:01:08] Ora2Pg version: 21.0[2021-03-28 17:01:08] Trying to connect to database: dbi:Oracle:host=192.168.228.77;service_name=orcl;port=1521[2021-03-28 17:01:08] Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED[2021-03-28 17:01:08] Looking forward functions declaration in schema SCOTT.[2021-03-28 17:01:08] Retrieving table information...[2021-03-28 17:01:32] Retrieving index information...[2021-03-28 17:01:47] Retrieving columns information...[2021-03-28 17:02:02] Retrieving comments information...[2021-03-28 17:02:07] Retrieving foreign keys information...[2021-03-28 17:02:27] Retrieving unique keys information...[2021-03-28 17:02:45] Retrieving check constraints information...[2021-03-28 17:02:54] [1] Scanning table BONUS (1 rows)...[2021-03-28 17:02:54] [2] Scanning table DEPT (1 rows)...[2021-03-28 17:02:54] [3] Scanning table EMP (1 rows)...[2021-03-28 17:02:54] [4] Scanning table SALGRADE (1 rows)...[2021-03-28 17:02:59] Exporting tables...[2021-03-28 17:02:59] Dumping table BONUS...[2021-03-28 17:02:59] Dumping table DEPT...[2021-03-28 17:02:59] Dumping table EMP...[2021-03-28 17:02:59] Dumping table SALGRADE...[2021-03-28 17:02:59] Dumping RI EMP...[2021-03-28 17:02:59] Fixing function calls in output files...

由上面的结果可以看出，ora2pg 会检索 oracle 库中的 table, index, cloumns, mommets and constraints，但是有些表在同步过程中可能会因为外键约束的影响，导致数据同步失败，因此如果同步的表有外键约束，最好先删掉，数据同步完成后再创建。

PostgreSQL 中创建表

[root@localhost sql]# psql -h 192.168.228.76 -p 5555 -d postgres -U scott -W -f create_table.sql Password: SETSETSETCREATE TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE TABLEALTER TABLE

同步

同步配置文件内容

ORACLE_HOME /usr/lib/oracle/12.2/client64/ORACLE_DSN dbi:Oracle:host=192.168.228.77;sid=orcl;port=1521ORACLE_USER systemORACLE_PWD sys_1234SCHEMA scottCREATE_SCHEMA 1TRUNCATE_TABLE 1STOP_ON_ERROR 0TYPE COPYPG_VERSION 12.3PG_DSN dbi:Pg:dbname=postgres;host=192.168.228.76;port=5555PG_SCHEMA scottPG_USER scottPG_PWD tigerFILE_PER_TABLE 1JOBS 2

开始同步…

[root@localhost conf]# ora2pg -c table_sync.conf [========================>] 4/4 tables (100.0%) end of scanning. [> ] 0/1 rows (0.0%) Table BONUS (0 sec., 0 recs/sec)DBD::Pg::db do failed: ERROR: cannot truncate a table referenced in a foreign key constraintess.DETAIL: Table "emp" references "dept".HINT: Truncate table "emp" at the same time, or use TRUNCATE ... CASCADE. at /usr/local/share/perl5/Ora2Pg.pm line 3598.FATAL: ERROR: cannot truncate a table referenced in a foreign key constraintDETAIL: Table "emp" references "dept".HINT: Truncate table "emp" at the same time, or use TRUNCATE ... CASCADE.Aborting export...

报错，就是上面所说的外键导致的报错，在同步的配置文件中TRUNCATE_TABLE 1 使得在表同步之前会先 truncate PG 库中的表，因此违反了外键约束。

删除掉外键约束…

[root@localhost ~]# psql -h 192.168.228.76 -p 5555 -d postgres -U scott -c "alter table emp drop constraint fk_deptno;" -WPassword: ALTER TABLE

重新开始同步…

[root@localhost conf]# ora2pg -c table_sync.conf [========================>] 4/4 tables (100.0%) end of scanning. [> ] 0/1 rows (0.0%) Table BONUS (1 sec., 0 recs/sec)[========================>] 4/1 rows (400.0%) Table DEPT (0 sec., 4 recs/sec) [========================>] 14/1 rows (1400.0%) Table EMP (0 sec., 14 recs/sec) [========================>] 5/1 rows (500.0%) Table SALGRADE (0 sec., 5 recs/sec) Fixing function calls in output files...ows (575.0%) - (1 sec., avg: 23 recs/sec), SALGRADE in progress.[========================>] 23/4 rows (575.0%) on total estimated data (1 sec., avg: 23 tuples/sec)

同步成功。

最后再把外键约束加上：

[root@localhost ~]# psql -h 192.168.228.76 -p 5555 -d postgres -U scott -c "ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;" -WPassword: ALTER TABLE

查看表的数量

postgres=> select * from pg_tables where tableowner='scott'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity ------------+-----------+------------+------------+------------+----------+-------------+------------- scott | bonus | scott | | f | f | f | f scott | dept | scott | | t | f | t | f scott | emp | scott | | t | f | t | f scott | salgrade | scott | | f | f | f | f(4 rows)

其他

目前大部分表的同步利用这个工具都可以完成，但是还有部分情况不能顺利同步。

另外，同步完成后Oracle和PG的行数对比还没想好利用什么方式进行对比。