pg库导出为oracle脚本,ora2pg工具完成oracle 到 postgresql迁移过程

一、ORA2PG初始化数据

1.1、安装ora2pg

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

tar -jxf ora2pg-20.0.tar.bz2

cd ora2pg-20.0/

perl Makefile.PL

make && make install

1.2、安装DBD:ORACLE驱动

yum -y  install perl-Time-HiRes

export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1/

export LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/dbhome_1/lib/

perl -MCPAN -e shell

install DBI

tar -zxvf DBD-Oracle-1.76.tar.gz

perl Makefile.PL

make

make test

make install

1.3、ora2pg配置文件

vi /etc/ora2pg/ora2pg.conf

ORACLE_DSN dbi:Oracle:host=192.168.0.101;sid=ora11g;port=1521

ORACLE_USER steven

ORACLE_PWD oracle

SCHEMA BT_PROD

TYPE TABLE DATA VIEW SEQUENCE TRIGGER FUNCTION PROCEDURE PACKAGE

OUTPUT bethune.sql

1.4、开始导出

[root@ora11g ora2pg-20.0]# ora2pg

WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11

[========================>] 175/175 tables (100.0%) end of scanning.

[>                        ]   0/175 tables (0.0%) end of scanning.

[========================>] 175/175 tables (100.0%) end of table export.

Wide character in print at /usr/local/share/perl5/Ora2Pg.pm line 12529.

-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0

-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.

-- DATASOURCE: dbi:Oracle:host=192.168.0.101;sid=ora11g;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

SET check_function_bodies = false;

CREATE TABLE ad_alert_error_descr (

db_version bigint NOT NULL,

error_code varchar(16) NOT NULL,

type varchar(16),

message varchar(4000)

) ;

......省略

[========================>] 203/1 rows (20300.0%) Table REPORTER_CONFIG (203 recs/sec)

[========================>] 1329861/175 total rows (759920.6%) - (180 sec., avg: 7388 recs/sec).

[========================>] 175/175 rows (100.0%) on total estimated data (180 sec., avg: 0 recs/sec)

[========================>] 1/1 views (100.0%) end of output.

[========================>] 18/18 sequences (100.0%) end of output.

[========================>] 0/0 triggers (100.0%) end of output.

[========================>] 1/1 functions (100.0%) end of functions export.

[========================>] 39/39 procedures (100.0%) end of procedures export.

[========================>] 6/6 packages (100.0%) end of output.

[root@ora11g ora2pg-20.0]# ls -l *.sql

-rw-r--r--. 1 root root      4139 Jun 26 05:28 FUNCTION_bethune.sql

-rw-r--r--. 1 root root 407321480 Jun 26 05:28 INSERT_bethune.sql

-rw-r--r--. 1 root root    422079 Jun 26 05:31 PACKAGE_bethune.sql

-rw-r--r--. 1 root root       292 Jun 26 05:28 PROCEDURE_bethune.sql

-rw-r--r--. 1 root root      1858 Jun 26 05:28 SEQUENCE_bethune.sql

-rw-r--r--. 1 root root    227890 Jun 26 05:24 TABLE_bethune.sql

-rw-r--r--. 1 root root       325 Jun 26 05:28 TRIGGER_bethune.sql

-rw-r--r--. 1 root root      1489 Jun 26 05:28 VIEW_bethune.sql

1.5、导入数据

-bash-4.2$ psql -d testdb -U sr_o2p

\i /target/TABLE_bethune.sql

\i /target/INSERT_bethune.sql

\i /target/VIEW_bethune.sql

\i /target/TRIGGER_bethune.sql

\i /target/SEQUENCE_bethune.sql

\i /target/FUNCTION_bethune.sql

\i /target/PROCEDURE_bethune.sql

\i /target/PACKAGE_bethune.sql

二、问题记录

1、索引中字段有#号

psql:/target/TABLE_bethune.sql:2070: ERROR:  syntax error at or near "#"

LINE 1: ...wr_segobj_bid_sid ON rd_db_awr_segobj (batchid, ts#, obj#, d...

^

2070 CREATE INDEX idx_r_db_awr_segobj_bid_sid ON rd_db_awr_segobj (batchid, ts#, obj#, dataobj#);

将ora2pg生成的脚本中ts#加上双引号。

2、索引中包含||字符连接

psql:/target/TABLE_bethune.sql:3055: ERROR:  syntax error at or near "||"

LINE 1: ...id_own_name ON rd_db_indpart (batchid, index_owner||index_na...

CREATE INDEX idx_r_db_indp_bid_own_name ON rd_db_indpart (batchid, index_owner, index_name);

PG不支持||。

3、ORA-04098

ERROR at line 1:

ORA-04098: trigger 'SR_O2P.SR_DDL_TRIGGER_AFTER' is invalid and failed

re-validation

SQL> alter system set "_system_trig_enabled"=false;

System altered.

SQL> drop user sr_o2p cascade;

User dropped.

SQL> alter system set "_system_trig_enabled"=true;

System altered.

4、ERROR:  relation does not exist

ize[628] apply_state[2] - (sr_apply.c:544)

[2019-06-26 15:19:30:622851]   [ERROR] sr_db_libpq_execute_stmt(): errcode[42P01] errbuf[ERROR:  relation "SR_O2P.BX_RPT_SUGGESTIONS" does not exist

LINE 1: INSERT INTO "SR_O2P"."BX_RPT_SUGGESTIONS"("SUGGESTION_ID","T...

^

] - (sr_db_libpq_ex.c:1136)

testdb=> select count(*) from "SR_O2P.BX_RPT_SUGGESTIONS";

ERROR:  relation "SR_O2P.BX_RPT_SUGGESTIONS" does not exist

LINE 1: select count(*) from "SR_O2P.BX_RPT_SUGGESTIONS";

^

testdb=> select count(*) from SR_O2P."BX_RPT_SUGGESTIONS";

ERROR:  relation "sr_o2p.BX_RPT_SUGGESTIONS" does not exist

LINE 1: select count(*) from SR_O2P."BX_RPT_SUGGESTIONS";

^

testdb=> select count(*) from SR_O2P.BX_RPT_SUGGESTIONS;

count

-------

0

(1 row)

将write_obj.list中表加上双引号。

ize[628] apply_state[2] - (sr_apply.c:544)

[2019-06-26 15:32:36:294734]   [ERROR] sr_db_libpq_execute_stmt(): errcode[42703] errbuf[ERROR:  column "SUGGESTION_ID" of relation "bx_rpt_suggestions" does not exist

LINE 1: INSERT INTO "sr_o2p"."bx_rpt_suggestions"("SUGGESTION_ID","T...

将所有字段名称改为大写：

select 'ALTER TABLE sr_o2p.'||c.relname ||' RENAME '||a.attname||' TO "'||upper(a.attname)||'";'   FROM pg_class c,pg_attribute a WHERE c.relname in('bx_rpt_suggestions','pd_db_info','rd_os_info','rd_db_inst','pd_db_ts','pd_db_awr_dbtime','pd_db_awr_sysstat','pd_db_logswitch')   and a.attnum > 0 and a.attrelid = c.oid ORDER BY 1;

5、invalid input syntax for integer:

86]   [ERROR] sr_db_libpq_execute_stmt(): errcode[22P02] errbuf[ERROR:  invalid input syntax for integer: "1533.18"

] - (sr_db_libpq_ex.c:1136)

[2019-06-26 16:16:19:913437]   [ERROR] sr_apply_execute_tx_partial(): Error(rc=-1) in sr_db_tar_execute_stmt(): xid[a.a.875] scn[2750939.1] sql[INSERT INTO "sr_o2p"."pd_db_awr_dbtime"("BATCHID","INSTANCE_NAME","SNAP_TIME","CLASS","TIME_SEC") VALUES($1,$2,$3,$4,$5),($6,$7,$8,$9,$10),($11,$12,$13,$14,$15),($16,$17,$18,$19,$20),($21,$22,$23,$24,$25),($26,$27,$28,$29,$30),($31,$32,$33,$34,$35),($36,$37,$38,$39,$40),($41,$42,$43,$44,$45),($46,$47,$48,$49,$50),($51,$52,$53,$54,$55),($56,$57,$58,$59,$60),($61,$62,$63,$64,$65),($66,$67,$68,$69,$70),($71,$72,$73,$74,$75),($76,$77,$78,$79,$80),($81,$82,$83,$84,$85),($86,$87,$88,$89,$90),($91,$92,$93,$94,$95),($96,$97,$98,$99,$100),($101,$102,$103,$104,$105),($106,$107,$108,$109,$110),($111,$112,$113,$114,$115),($116,$117,$118,$119,$120),($121,$122,$123,$124,$125),($126,$127,$128,$129,$130),($131,$132,$133,$134,$135),($136,$137,$138,$139,$140),($141,$142,$143,$144,$145),($146,$147,$148,$149,$150),($151,$152,$153,$154,$155),($156,$157,$158,$159,$160),($161,$162,$163,$164,$165),($166,$167,$168,$169,$170),($171,$172,$173,$174,$175),($176,$177,$178,$179,$180),($181,$182,$183,$184,$185),($186,$187,$188,$189,$190),($191,$192,$193,$194,$195),($196,$197,$198,$199,$200),($201,$202,$203,$204,$205),($206,$207,$208,$209,$210),($211,$212,$213,$214,$215),($216,$217,$218,$219,$220),($221,$222,$223,$224,$225),($226,$227,$228,$229,$230),($231,$232,$233,$234,$235),($236,$237,$238,$239,$240),($241,$242,$243,$244,$245),($246,$247,$248,$249,$250)] type[2008] - (sr_apply.c:617)

ora2pg生成的脚本问题，需要将bigint改为double，小数。

ALTER TABLE sr_o2p.pd_db_awr_dbtime

ALTER COLUMN "TIME_SEC" TYPE double precision ;

6、ERROR:  no partition of relation found for row

[2019-06-26 16:22:24:528724]   [ERROR] sr_db_libpq_execute_stmt(): errcode[23514] errbuf[ERROR:  no partition of relation "pd_db_awr_dbtime" found for row

DETAIL:  Partition key of the failing row contains ("BATCHID") = (431).

--sourge oracle

CREATE TABLE "BT_PROD"."PD_DB_AWR_DBTIME"

("BATCHID" NUMBER,

"INSTANCE_NAME" VARCHAR2(16 BYTE),

"SNAP_TIME" TIMESTAMP (6),

"CLASS" VARCHAR2(64 BYTE),

"TIME_SEC" NUMBER,

SUPPLEMENTAL LOG DATA (ALL) COLUMNS

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "BAYMAX"

PARTITION BY RANGE ("BATCHID") INTERVAL (1000)

--target postgres

CREATE TABLE sr_o2p.pd_db_awr_dbtime

(

"BATCHID" bigint,

"INSTANCE_NAME" character varying(16) COLLATE pg_catalog."default",

"SNAP_TIME" timestamp without time zone,

"CLASS" character varying(64) COLLATE pg_catalog."default",

"TIME_SEC" double precision

) PARTITION BY RANGE ("BATCHID")

WITH (

OIDS = FALSE

)

TABLESPACE pg_default;

需要创建子分区

3. Create partition child table

CREATE TABLE pd_db_awr_dbtime_100 PARTITION OF pd_db_awr_dbtime FOR VALUES FROM (0) TO (100);