ora2pg是一款免費遷移工具，能將oracle遷移到pg，簡單使用并記錄程序如下
官方檔案：http://ora2pg.darold.net/
環境：
虛擬機 oracle linux 7.7
oracle 11GR2 11.2.0.4
pg 13.1
Ora2Pg v21.0
安裝ora2pg
安裝perl依賴
ora2pg是Perl語言撰寫的，所以需要先安裝相關環境依賴
[root@localhost ~]$ yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package perl.x86_64 4:5.16.3-294.el7_6 will be updated ---> Package perl.x86_64 4:5.16.3-297.el7 will be an update (省略中間...) Dependency Installed: gdbm-devel.x86_64 0:1.10-8.el7 libdb-devel.x86_64 0:5.3.21-25.el7 perl-ExtUtils-Install.noarch 0:1.58-297.el7 perl-ExtUtils-Manifest.noarch 0:1.61-244.el7 perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7 perl-IPC-Cmd.noarch 1:0.80-4.el7 perl-Locale-Maketext.noarch 0:1.23-3.el7 perl-Locale-Maketext-Simple.noarch 1:0.21-297.el7 perl-Module-CoreList.noarch 1:2.76.02-297.el7 perl-Module-Load.noarch 1:0.24-3.el7 perl-Module-Load-Conditional.noarch 0:0.54-3.el7 perl-Module-Metadata.noarch 0:1.000018-2.el7 perl-Params-Check.noarch 1:0.38-2.el7 perl-Perl-OSType.noarch 0:1.003-3.el7 perl-Test-Harness.noarch 0:3.28-3.el7 perl-devel.x86_64 4:5.16.3-297.el7 perl-version.x86_64 3:0.99.07-6.el7 pyparsing.noarch 0:1.5.6-9.el7 systemtap-sdt-devel.x86_64 0:4.0-13.0.1.el7 Updated: perl.x86_64 4:5.16.3-297.el7 Dependency Updated: perl-libs.x86_64 4:5.16.3-297.el7 Complete! [root@localhost ~]$
安裝DBI模塊
DBI，Database Independent Interface，是Perl語言連接資料庫的介面
下載地址https://metacpan.org/release/DBI 下載出DBI-1.643.tar.gz
然后解壓安裝
[root@localhost /usr/local]$ tar -xzvf DBI-1.643.tar.gz [root@localhost /usr/local/DBI-1.643]$ cd DBI-1.643/ [root@localhost /usr/local/DBI-1.643]$ perl Makefile.PL [root@localhost /usr/local/DBI-1.643]$ make [root@localhost /usr/local/DBI-1.643]$ make install
安裝DBD::Oracle模塊
安裝DBD-Oracle驅動，下載地址https://metacpan.org/pod/release/PYTHIAN/DBD-Oracle-1.74/lib/DBD/Oracle.pm，下載出DBD-Oracle-1.74.tar.gz
[root@localhost /usr/local/DBD-Oracle-1.74]$ tar -zxvf DBD-Oracle-1.74.tar.gz [root@localhost /usr/local/DBD-Oracle-1.74]$ cd DBD-Oracle-1.74/ [root@localhost /usr/local/DBD-Oracle-1.74]$ perl Makefile.PL [root@localhost /usr/local/DBD-Oracle-1.74]$ make [root@localhost /usr/local/DBD-Oracle-1.74]$ make install
安裝DBD::Pg模塊
安裝DBD-Pg驅動，下載地址https://metacpan.org/release/DBD-Pg，下載出DBD-Pg-3.14.2.tar.gz
[root@localhost /usr/local]$ cd DBD-Pg-3.14.2/ [root@localhost /usr/local]$ perl Makefile.PL [root@localhost /usr/local]$ make [root@localhost /usr/local]$ make install
安裝ORA2PG
下載地址https://sourceforge.net/projects/ora2pg/
[root@localhost /usr/local]$ cd ora2pg-21.0/ [root@localhost /usr/local/ora2pg-21.0]$ ls changelog doc INSTALL lib LICENSE Makefile.PL MANIFEST packaging README scripts [root@localhost /usr/local/ora2pg-21.0]$ perl Makefile.PL Checking if your kit is complete... Looks good Writing Makefile for Ora2Pg Done... ------------------------------------------------------------------------------ Please read documentation at http://ora2pg.darold.net/ before asking for help ------------------------------------------------------------------------------ Now type: make && make install [root@localhost /usr/local/ora2pg-21.0]$ make cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm cp lib/Ora2Pg/GEOM.pm blib/lib/Ora2Pg/GEOM.pm cp lib/Ora2Pg/PLSQL.pm blib/lib/Ora2Pg/PLSQL.pm cp lib/Ora2Pg/MySQL.pm blib/lib/Ora2Pg/MySQL.pm cp scripts/ora2pg blib/script/ora2pg /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg cp scripts/ora2pg_scanner blib/script/ora2pg_scanner /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg_scanner Manifying blib/man3/ora2pg.3 [root@localhost /usr/local/ora2pg-21.0]$ make install Installing /usr/local/share/perl5/Ora2Pg.pm Installing /usr/local/share/perl5/Ora2Pg/GEOM.pm Installing /usr/local/share/perl5/Ora2Pg/PLSQL.pm Installing /usr/local/share/perl5/Ora2Pg/MySQL.pm Installing /usr/local/share/man/man3/ora2pg.3 Installing /usr/local/bin/ora2pg Installing /usr/local/bin/ora2pg_scanner Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg Appending installation info to /usr/lib64/perl5/perllocal.pod [root@localhost /usr/local/ora2pg-21.0]$
查看軟體是否安裝成功
[root@localhost ~]$ cat 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;[root@localhost ~]$ perl check.pl
DBD::Oracle -- 1.74
DBD::Pg -- 3.14.2
DBI -- 1.643
Ora2Pg -- 21.0
Perl -- 5.16.3
添加環境變數
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib
測驗一下
[root@localhost ~]$ cat ora2pg_table.conf ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 ORACLE_DSN dbi:Oracle:host=192.168.168.3;sid=orcl ORACLE_USER sys ORACLE_PWD oracle SCHEMA scott TYPE TABLE PG_NUMERIC_TYPE 0 PG_INTEGER_TYPE 1 DEFAULT_NUMERIC float SKIP fkeys pkeys ukeys indexes checks NLS_LANG AMERICAN_AMERICA.UTF8 OUTPUT /root/ora2pg/table.sql [root@localhost ~]$ ora2pg -t SHOW_VERSION -c ora2pg_table.conf WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 [root@localhost ~]$
簡單使用
oracle以scott這個schema為例，做遷移
遷移表
撰寫組態檔
創建ora2pg_table.conf，匯出表結構的組態檔,內容見測驗部分
創建ora2pg_data.conf，匯出資料，內容如下
[root@localhost ~]$ cat ora2pg_data.conf ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 ORACLE_DSN dbi:Oracle:host=192.168.168.3;sid=orcl ORACLE_USER system ORACLE_PWD oracle SCHEMA scott TYPE COPY PG_NUMERIC_TYPE 0 PG_INTEGER_TYPE 1 DEFAULT_NUMERIC float SKIP fkeys pkeys ukeys indexes checks NLS_LANG AMERICAN_AMERICA.UTF8 OUTPUT /root/ora2pg/data.sql [root@localhost ~]$
匯出資料
執行匯出命令，匯出表結構和資料
[root@localhost ~]$ ora2pg -c ora2pg_table.conf WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 [========================>] 4/4 tables (100.0%) end of scanning. [========================>] 4/4 tables (100.0%) end of table export. Fixing function calls in output files... [root@localhost ~]$ ora2pg -c ora2pg_data.conf WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 [========================>] 4/4 tables (100.0%) end of scanning. [========================>] 0/0 rows (100.0%) Table BONUS (0 recs/sec) [> ] 0/23 total rows (0.0%) - (0 sec., avg: 0 recs/sec). [========================>] 4/4 rows (100.0%) Table DEPT (4 recs/sec) [====> ] 4/23 total rows (17.4%) - (0 sec., avg: 4 recs/sec). [========================>] 14/14 rows (100.0%) Table EMP (14 recs/sec) [==================> ] 18/23 total rows (78.3%) - (0 sec., avg: 18 recs/sec). [========================>] 5/5 rows (100.0%) Table SALGRADE (5 recs/sec) [========================>] 23/23 total rows (100.0%) - (0 sec., avg: 23 recs/sec). [========================>] 23/23 rows (100.0%) on total estimated data (1 sec., avg: 23 recs/sec) Fixing function calls in output files...
查看匯出的sql
[root@localhost ~]$ cd ora2pg/
[root@localhost ~/ora2pg]$ ls
data.sql table.sql
[root@localhost ~/ora2pg]$ cat table.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 21.0
-- Copyright 2000-2020 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=192.168.168.3;sid=orcl
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
SET check_function_bodies = false;
CREATE TABLE bonus (
ename varchar(10),
job varchar(9),
sal float,
comm float
) ;
CREATE TABLE dept (
deptno smallint NOT NULL,
dname varchar(14),
loc varchar(13)
) ;
CREATE TABLE emp (
empno smallint NOT NULL,
ename varchar(10),
job varchar(9),
mgr smallint,
hiredate timestamp,
sal decimal(7,2),
comm decimal(7,2),
deptno smallint
) ;
CREATE TABLE salgrade (
grade float,
losal float,
hisal float
) ;
[root@localhost ~/ora2pg]$ cat data.sql
BEGIN;
COPY bonus (ename,job,sal,comm) FROM STDIN;
\.
COPY dept (deptno,dname,loc) FROM STDIN;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
\.
COPY emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) FROM STDIN;
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 \N 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 \N 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 \N 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 \N 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 \N 20
7839 KING PRESIDENT \N 1981-11-17 00:00:00 5000 \N 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 \N 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 \N 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 \N 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 \N 10
\.
COPY salgrade (grade,losal,hisal) FROM STDIN;
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
\.
COMMIT;
[root@localhost ~/ora2pg]$
匯入到pg
[root@localhost ~/ora2pg]$ psql postgres postgres -f table.sql SET SET CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE [root@localhost ~/ora2pg]$ psql postgres postgres -f data.sql BEGIN COPY 0 COPY 4 COPY 14 COPY 5 COMMIT
...
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/238471.html
標籤：其他