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)

) ;

......省略

[========&