ora2pg使用記錄

ora2pg使用記錄

---

前言

這篇文章是我在學習使用ora2pg過程中的學習記錄，以便日後遺忘查閱；

諸君也可跟隨我的步伐瞭解一下ora2pg，或可移步如下官方文件參考學習：Ora2Pg : Migrates Oracle to PostgreSQL (darold.net)

本文的ora2pg安裝和使用記錄是在win10系統下遷移遠端Oracle資料庫到遠端PostgreSQL資料庫的應用環境下進行的，如果你的應用場景不同，可簡閱做思路參考！

---

目錄

目錄

• ora2pg使用記錄
  • 前言
  • 目錄
  • 初識ora2pg
    • 一、ora2pg是什麼？
      • （一）、Ora2Pg能做的遷移動作如下
      • （二）、功能包括
    • 二、ora2pg下載安裝及環境配置
      • （一）、Perl下載安裝
      • （二）、Oracle客戶端或完整的Oracle安裝配置
        • 1.1 安裝
        • 1.2 配置
      • （三）、Ora2Pg安裝配置
        • 1.1 安裝
        • 1.2 配置（win10下配置）
          • 1.2.1 配置前說明
          • 1.2.2 Oracle遷移到PostgreSQL環境配置
    • 三、ora2pg引數說明
      • （一）、常用命令
      • （二）、引數幫助說明（直譯）
    • 四、ora2pg配置項說明
      • （一）、常用配置項
      • （二）、配置項幫助文件
  • 需求實驗
    • 實驗一：批次匯出Oracle中特定表到PostgreSQL的一個特定schema下
      • （1）需求詳細說明
      • （2）實驗過程
      • （3）問題總結

---

初識ora2pg

一、ora2pg是什麼？

Ora2Pg - Oracle to PostgreSQL database schema converter（Oracle到PostgreSQL資料庫模式轉換器）

Ora2Pg是一個開源免費的資料庫遷移工具，用於將Oracle資料庫遷移到PostgreSQL相容的模式。它連線您的Oracle資料庫，自動掃描並提取其結構或資料，然後生成可以載入到PostgreSQL資料庫中的SQL指令碼。Ora2Pg非常易於使用，除了提供連線到Oracle資料庫所需的引數外，不需要任何Oracle資料庫知識。

（一）、Ora2Pg能做的遷移動作如下

TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT or COPY, FDW, QUERY, KETTLE, SYNONYM

（二）、功能包括

- Export full database schema (tables, views, sequences, indexes), with unique, primary, foreign key and check constraints.
【匯出完整的資料庫架構（表、檢視、序列、索引），具有唯一、主鍵、外來鍵和檢查約束。】
- Export grants/privileges for users and groups.
【匯出使用者和組的授予/許可權。】
- Export range/list partitions and sub partitions.
【匯出範圍/列表分割槽和子分割槽。】
- Export a table selection (by specifying the table names).
【匯出選擇的表格（透過指定表格名稱）。】
- Export Oracle schema to a PostgreSQL 8.4+ schema.
【將Oracle模式匯出為PostgreSQL 8.4+模式。】
- Export predefined functions, triggers, procedures, packages and package bodies.
【匯出預定義的函式、觸發器、過程、包和包體。】
- Export full data or following a WHERE clause.
【匯出完整資料或遵循WHERE子句。】
- Full support of Oracle BLOB object as PG BYTEA.
【完全支援Oracle BLOB物件作為PG BYTEA。】
- Export Oracle views as PG tables.
【將Oracle檢視匯出為PG表。】
- Export Oracle user defined types.
【匯出Oracle使用者定義的型別。】
- Provide some basic automatic conversion of PLSQL code to PLPGSQL.
【將Oracle表匯出為外部資料包裝表。】
- Works on any platform.
【適用於任何平臺。】
- Export Oracle tables as foreign data wrapper tables.
【將Oracle表匯出為外部資料包裝表。】
- Export materialized view.
【匯出具體化檢視。】
- Show a  report of an Oracle database content.
【顯示Oracle資料庫內容的報告。】
- Migration cost assessment of an Oracle database.
【Oracle資料庫的遷移成本評估。】
- Migration difficulty level assessment of an Oracle database.
【Oracle資料庫的遷移難度級別評估。】
- Migration cost assessment of PL/SQL code from a file.
【從檔案遷移PL/SQL程式碼的成本評估。】
- Migration cost assessment of Oracle SQL queries stored in a file.
【儲存在檔案中的Oracle SQL查詢的遷移成本評估。】
- Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
【生成要與Penthalo Data Integrator（Kettle）一起使用的XML ktr檔案】
- Export Oracle locator and spatial geometries into PostGis.
【將Oracle定位器和空間幾何圖形匯出到PostGis中。】
- Export DBLINK as Oracle FDW.
【將DBLINK匯出為Oracle FDW。】
- Export SYNONYMS as views.
【將SYNONYMS匯出為檢視。】
- Export DIRECTORY as external table or directory for external_file extension.
【將DIRECTORY匯出為external_file副檔名的外部表或目錄。】
- Dispatch a list of SQL orders over multiple PostgreSQL connections
【透過多個PostgreSQL連線排程SQL訂單列表】
- Perform a diff between Oracle and PostgreSQL database for test purpose.
【透過多個PostgreSQL連線排程SQL訂單列表】
- MySQL/MariaDB and Microsoft SQL Server migration.
【MySQL/MariaDB和Microsoft SQL Server遷移。】

需要注意的是：Ora2Pg盡最大努力將您的Oracle資料庫自動轉換為PostgreSQL，但仍有手動工作要做。為函式、procedures、包和觸發器生成的Oracle特定PL/SQL程式碼必須經過審查，以匹配PostgreSQL語法。

二、ora2pg下載安裝及環境配置

（一）、Perl下載安裝

首先因為Ora2Pg是Perl語言編寫的，所以要使用這個工具，則必須為其搭建Perl執行環境。注意：安裝版本要大於等於5.10

Perl下載地址：Strawberry Perl for Windows

下載好如下：

傻瓜式安裝即可，沒有特殊操作。

（二）、Oracle客戶端或完整的Oracle安裝配置

1.1 安裝

必須在系統上安裝Oracle Instant Client或完整的Oracle安裝程式。

進行資料庫遷移工作這些應該都已經安裝了，這裡不做贅述。

如果確實沒有安裝，這裡推薦幾篇相關博文供參考：

1. Oracle客戶端安裝教程（圖文）
2. Oracle Client（客戶端） 安裝與配置
3. oracle資料庫安裝（全步驟詳解）
4. Oracle下載安裝教程—Oracle19c下載安裝(每一步)

注意：這裡由於我的資料庫伺服器都不在本機，這裡我使用的是Oracle客戶端進行的接下來的操作。

如果你的Oracle資料庫在本機，這裡也推薦一篇文章以作參考！

[Windows下Ora2Pg（Oracle到PostgreSQL遷移工具）的安裝配置及使用教程](https://blog.csdn.net/qq_34272470/article/details/128128287#:~:text=使用Ora2Pg 1 1.cmd定位到解壓後的Ora2Pg目錄中，執行下面的命令使用Perl解析Ora2Pg perl Makefile.PL 2 2.上面的命令執行完成後，會有提示，按照提示執行下面的命令即可 dmake,get DBD%3A%3AOracle 8 8.安裝DBD%3A%3AOracle驅動 install DBD%3A%3AOracle ... 更多專案)

1.2 配置

在使用ora2pg時，我們需要配置Oracle客戶端（或Oracle）的ORACLE_HOME環境變數，值為客戶端安裝目錄。（這是必須的）

官方文件：You need to have the Oracle client libraries installed and the ORACLE_HOME environment variable must be defined.

（三）、Ora2Pg安裝配置

Ora2Pg下載地址

1. SOURCEFORGE：ora2pg download | SourceForge.net
2. GitHub：Releases · darold/ora2pg (github.com)

下載好如下：

1.1 安裝

解壓到任意目錄即可！

1.2 配置（win10下配置）

1.2.1 配置前說明

1. Ora2Pg連線資料庫並進行遷移動作，DBIPerl模組是必須安裝的，並且其版本要大於1.614。

2. Ora2Pg不僅能夠遷移Oracle資料庫，還可以遷移MySQL、SQL Server等，只需要安裝對應的Perl模組即可。例如：

   遷移Oracle - install DBD::Oracle

   遷移MySQL - install DBD::MySQL

   遷移SQL Server - install DBD::ODBC

   有些ora2pg發行版可能還需要安裝Time::HiResPerl模組

3. Ora2Pg可以匯出sql指令碼供pg資料庫匯入執行，也可以直接匯入到遠端pg資料庫。

   要匯入sql指令碼你還需要安裝PostgreSQL客戶端（psql）

   如果你想動態直接匯入到PostgreSQL，你至少還需要安裝DBD::Pg和Compress::ZlibPerl模組

   預設情況下，Ora2Pg生成sql指令碼供PostgreSQL客戶端匯入執行

1.2.2 Oracle遷移到PostgreSQL環境配置

1. 透過cmd進入到Ora2Pg安裝目錄，檢視Perl版本（Perl版本要大於5.10）

   

2. 將把Ora2Pg.pm安裝到Perl庫

   perl Makefile.PL
   gmake && gmake install
   

   

3. 安裝DBIPerl模組

   cpan
   get DBI
   install DBI
   

   

4. 安裝DBD::OraclePerl模組

   官方文件：Installing DBD::Oracle require that the three Oracle packages: instant-client, SDK and SQLplus are installed as well as the libaio1 library.

   可以看到，安裝DBD::Oracle還需要Oracle SDK和SQLplus，如果你和我一樣安裝的是精簡版Oracle客戶端，那麼你還需要去官網下載安裝Oracle SDK和SQLplus。

   下載地址：Instant Client for Microsoft Windows (x64) 64-bit (oracle.com)

   直接點選這裡下載的是最新版本的精簡版客戶端、SQL*Plus、SDK

   

   下載好如下三個壓縮包：

   

   SQL*Plus、SDK的安裝是建立在精簡版客戶端的基礎上的，你需要先安裝好精簡版客戶端。

   精簡版客戶端安裝：精簡版客戶端客戶端的安裝很簡單，解壓到任意目錄即可！配置ORACLE_HOME環境變數。注意！注意！注意！安裝路徑上一定不要有空格或中文字元！否則在安裝DBD::Oracle的時候，會報錯！

   SQL*Plus和SDK安裝：直接將壓縮包檔案解壓精簡版客戶端安裝目錄即可！

   

   安裝好之後，還需要配置LD_LIBRARY_PATH環境變數（注意，精簡版客戶端不安裝SDK是沒有sdk\lib的，安裝DBD::Oracle時會載入lib）

   官方文件：If you are using Instant Client from ZIP archives, the LD_LIBRARY_PATH and ORACLE_HOME will be the same and must be set to the directory where you have installed the files. For example: /opt/oracle/instantclient_12_2/

   

   做好這一切開始安裝DBD::Oracle

   cpan
   get DBD::Oracle
   install DBD::Oracle
   

   

   安裝完成之後再執行一下：install DBD::Oracle，確定是否安裝成功，如下表示安裝成功！

   

三、ora2pg引數說明

ora2pg可以使用命令列的方式進行資料遷移，但是這種方式我暫未實驗，瞭解一些常用命令即可！

（一）、常用命令

ora2pg -c | --conf file : 設定非預設的配置檔案，預設配置檔案為 /etc/ora2pg/ora2pg.conf。（指定自定義配置檔案路徑）

ora2pg -d | --debug : 使用除錯模式，輸出更多詳細資訊。

（二）、引數幫助說明（直譯）

# ora2pg --help
Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]
-a | --allow str : 指定允許匯出的物件列表，使用逗號分隔。也可以與 SHOW_COLUMN 選項一起使用。
-b | --basedir dir: 設定預設的匯出目錄，用於儲存匯出結果。
-c | --conf file : 設定非預設的配置檔案，預設配置檔案為 /etc/ora2pg/ora2pg.conf。
-d | --debug : 使用除錯模式，輸出更多詳細資訊。
-D | --data_type STR : 透過命令列設定資料型別轉換。
-e | --exclude str: 指定匯出時排除的物件列表，使用逗號分隔。也可以與 SHOW_COLUMN 選項一起使用。
-h | --help : 顯示幫助資訊。
-g | --grant_object type : 匯出指定型別的物件上的授權資訊，取值參見 GRANT_OBJECT 配置項。
-i | --input file : 指定要匯入的 Oracle PL/SQL 程式碼檔案，匯入檔案時不需要連線到 Oracle 資料庫。
-j | --jobs num : 設定用於傳送資料到 PostgreSQL 的併發程式數量。
-J | --copies num : 設定用於從 Oracle 匯出資料的併發連線數量。
-l | --log file : 設定日誌檔案，預設為 stdout。
-L | --limit num : 匯出資料時，每次寫入磁碟之前在記憶體中緩衝的記錄數量，預設值為 10000。
-m | --mysql : 匯出 MySQL 資料庫。
-n | --namespace schema : 設定需要匯出的 Oracle 模式。
-N | --pg_schema schema : 設定 PostgreSQL 中的搜尋路徑 search_path。
-o | --out file : 設定匯出的 SQL 檔案的儲存路徑。預設值為當前目錄下的 output.sql 檔案。
-p | --plsql : 啟用 PLSQL 程式碼到 PLPGSQL 程式碼的轉換。
-P | --parallel num: 同時匯出多個表，設定併發數量。
-q | --quiet : 不顯示進度條。
-s | --source DSN : 設定 Oracle DBI 資料來源。
-t | --type export: 設定匯出型別。該引數將會覆蓋配置檔案中的匯出型別（TYPE）。
-T | --temp_dir DIR: 為多個同時執行的 ora2pg 指令碼指定不同的臨時儲存目錄。
-u | --user name : 設定連線 Oracle 資料庫連線的使用者名稱。也可以使用 ORA2PG_USER 環境變數。
-v | --version : 顯示 Ora2Pg 版本資訊並退出。
-w | --password pwd : 設定連線 Oracle 資料庫的使用者密碼。也可以使用 ORA2PG_PASSWD 環境變數。
--forceowner : 匯入資料時，強制 ora2pg 將匯入 PostgreSQL 的表和序列的擁有者設定為連線 Oracle 資料庫時的使用者。如果設定為指定的使用者名稱，所有匯入的物件屬於該使用者。預設情況下，物件的擁有者為連線 Pg 資料庫的使用者。
--nls_lang code: 設定 Oracle 客戶端的 NLS_LANG 編碼。
--client_encoding code: 設定 PostgreSQL 客戶端編碼。
--view_as_table str: 將檢視匯出為表，多個檢視使用逗號分隔。
--estimate_cost : 在 SHOW_REPORT 結果中輸出遷移成本評估資訊。
--cost_unit_value minutes: 成本評估單位，使用分鐘數表示。預設值為 5 分鐘，表示一個 PostgreSQL 專家遷移所需的時間。如果是第一次遷移，可以設定為 10 分鐘。
 --dump_as_html : 生成 HTML 格式的遷移報告，只能與 SHOW_REPORT 選項一起使用。預設的報告是一個簡單的文字檔案。
 --dump_as_csv : 與上個引數相同，但是生成 CSV 格式的報告。
 --dump_as_sheet : 生成遷移評估時，為每個資料庫生成一行 CSV 記錄。
 --init_project NAME: 建立一個ora2pg 專案目錄結構。專案的頂級目錄位於根目錄之下。
 --project_base DIR : 定義ora2pg 專案的根目錄，預設為當前目錄。
 --print_header : 與 --dump_as_sheet 一起使用，輸出 CSV 標題資訊。
 --human_days_limit num : 設定遷移評估級別從 B 升到 C 所需的人工日數量。預設值為 5 人工日。
 --audit_user LIST : 設定查詢 DBA_AUDIT_TRAIL 表時需要過濾的使用者名稱，多個使用者使用逗號分隔。該引數只能用於 SHOW_REPORT 和 QUERY 匯出型別。
 --pg_dsn DSN : 設定線上匯入時的 PostgreSQL 資料來源。
 --pg_user name : 設定連線 PostgreSQL 的使用者名稱。
 --pg_pwd password : 設定連線 PostgreSQL 的使用者密碼。
 --count_rows : 在 TEST 方式下執行真實的資料行數統計。
 --no_header : 在匯出檔案中不新增 Ora2Pg 頭部資訊。
 --oracle_speed : 用於測試 Oracle 傳送資料的速度。不會真的處理或者寫入資料。
 --ora2pg_speed : 用於測試 Ora2Pg 傳送轉換後的資料的速度。不會寫入任何資料。

四、ora2pg配置項說明

ora2pg不僅可以使用命令列的方式進行資料遷移，還可以透過自定義配置檔案進行資料遷移配置。

ora2pg安裝目錄下有一個預設配置檔案ora2pg.conf.dist，你可以透過學習配置項，來修改此配置檔案的預設值，來達到你想要的業務需求。

該檔案是可自定義進行配置項覆蓋的，也就是說你可以在任意位置建立一個自定義配置檔案，來增加自己的配置項，而不修改預設配置檔案。

例如這樣：

（一）、常用配置項

注意：配置項和值之間用tab鍵隔開

#設定Oracle主目錄：Oracle的安裝目錄（如果說是Oracle客戶端，就是客戶端的路徑）
ORACLE_HOME	D:\test\instantclient_21_11
#設定Oracle資料庫連線（資料來源、使用者、密碼）連線遠端的，需要配置遠端資料庫ip和埠
ORACLE_DSN	dbi:Oracle:host=127.0.0.1;sid=orcl;port=1521
#填入Oracle使用者名稱、密碼
ORACLE_USER	username
ORACLE_PWD	pwd***

#配置你想遷移的內容，需要遷移什麼內容，就配置什麼內容，之間用“,”隔開
TYPE TABLE,VIEW,SEQUENCE,TRIGGER,FUNCTION,PROCEDURE

#匯出SQL目標檔名稱
OUTPUT	user.sql
#匯出SQL目標目錄
OUTPUT_DIR	C:\test

#配置你要匯出的Oracle資料庫 不配置的情況下預設匯出所有資料庫所有表
SCHEMA MY_Oracle_SCHEMA

#配置允許留下的表，這裡可以使用正規表示式，示例表示遷移表名包含“BI_”的資料庫表
ALLOW	.*BI_.*

#預設情況下，Oracle模式不會匯出到PostgreSQL資料庫中，所有物件都是在預設的Pg名稱空間下建立的。
#如果還要匯出此架構並在此名稱空間下建立所有物件，請將export_schema指令設定為1。
#這將使用預設的pg_catalog模式將匯出SQL檔案頂部的模式搜尋路徑設定為schema指令中設定的模式名稱。
#如果要更改此路徑，請使用指令PG_SCHEMA。
EXPORT_SCHEMA	0
#在輸出檔案開始時啟用/禁用CREATE SCHEMA SQL順序。它在預設情況下是啟用的，並關係TABLE匯出型別。
CREATE_SCHEMA	1

#限制匯出到哪一個架構
PG_SCHEMA	MY_PG_SCHEMA

#設定匯出目標pg資料庫
PG_DSN	dbi:Pg:dbname=my_db;host=127.0.0.1;port=5432
#填入pg使用者名稱、密碼
PG_USER	pg_username
PG_PWD	pg_pwd***

（二）、配置項幫助文件

更多配置項詳細說明，請詳見Ora2Pg官方文件！

Ora2Pg : Migrates Oracle to PostgreSQL (darold.net)

---

需求實驗

實驗一：批次匯出Oracle中特定表到PostgreSQL的一個特定schema下

（1）需求詳細說明

匹配Oracle資料庫中所有表名包含"BI_"的資料表遷移到PostgreSQL的test_schema下。（遷移表結構和資料）

（2）實驗過程

編寫ora2pg.conf.dist配置檔案：

ORACLE_HOME	D:\test\instantclient_21_11
ORACLE_DSN	dbi:Oracle:host=127.0.0.1;sid=orcl;port=1521
ORACLE_USER	username
ORACLE_PWD	pwd***

TYPE	TABLE,INSERT

ALLOW	.*BI_.*

PG_SCHEMA	test_schema

OUTPUT	user.sql
OUTPUT_DIR	C:\test

執行遷移命令：

ora2pg -c C:\Users\zhao-XH\Desktop\ora2pg.conf.dist -d

匯出sql指令碼成功

在PostgreSQL客戶端分別執行表結構和資料插入sql指令碼即可！

（3）問題總結

遷移過程中要注意，欄位的型別對映是否符合要求。官方給出的型別對映關係如下：

oracle型別	postgresql型別
date	timestamp
long	text
long raw	bytea
clob	text
nclob	text
blob	bytea
bfile	bytea
raw	bytea
rowid	oid
float	double precision
dec	decimal
decimal	decimal
double precision	double precision
int	integer
integer	integer
real	real
smallint	smallint
binary_float	double precision
binary_double	double precision
tinestamp	timestamp
xmltype	xml
binary_integer	integer
pls_integer	integer
timestamp with time zone	timestamp with time zone
timestamp with local time zone	timestamp with time zone

如果型別對映不符合我們的要求，例如本次實驗，我想要NUMBER(1,0)型別進行精度對映，而ora2pg工具考慮到效率問題，預設關閉NUMBER(p,s) -> numeric(p,s)的對映關係。採用了取整型的方式，這一點很顯然不是我想要的。查閱官方文件，我們可以看出，要想開啟NUMBER(p,s) -> numeric(p,s)的對映關係，需要將PG_INTEGER_TYPE配置項的值置為0即可！

官方文件：

PG_NUMERIC_TYPE

If set to 1 replace portable numeric type into PostgreSQL internal type. Oracle data type NUMBER(p,s) is approximatively converted to real and float PostgreSQL data type. If you have monetary fields or don't want rounding issues with the extra decimals you should preserve the same numeric(p,s) PostgreSQL data type. Do that only if you need exactness because using numeric(p,s) is slower than using real or double.

PG_INTEGER_TYPE

If set to 1 replace portable numeric type into PostgreSQL internal type. Oracle data type NUMBER(p) or NUMBER are converted to smallint, integer or bigint PostgreSQL data type following the value of the precision. If NUMBER without precision are set to DEFAULT_NUMERIC (see below).

DEFAULT_NUMERIC

NUMBER without precision are converted by default to bigint only if PG_INTEGER_TYPE is true. You can overwrite this value to any PG type, like integer or float.

我們也可以設定配置項來進行手動對映。方法如下：

官方文件：

If you're experiencing any problem in data type schema conversion with this directive you can take full control of the correspondence between Oracle and PostgreSQL types to redefine data type translation used in Ora2pg. The syntax is a comma-separated list of "Oracle datatype:Postgresql datatype". Here are the default list used:

DATA_TYPE       VARCHAR2:varchar,NVARCHAR2:varchar,NVARCHAR:varchar,NCHAR:char,DATE:timestamp(0)...

The directive and the list definition must be a single line.

看到上述說明，我們關注以下幾點：

1. 設定配置項時，你沒有必要全部貼上預設對映再進行更改，只需要修改不符合你對映要求的資料型別即可！
2. 所有型別對映配置必須在一行上宣告，中間用","隔開。

示範，例如官方文件預設將DATE:timestamp(0)，而我需要DATE:timestamp。同時我需要開啟精度對映。

那麼上述配置檔案更改如下：

ORACLE_HOME	D:\test\instantclient_21_11
ORACLE_DSN	dbi:Oracle:host=127.0.0.1;sid=orcl;port=1521
ORACLE_USER	username
ORACLE_PWD	pwd***

TYPE	TABLE,INSERT

ALLOW	.*BI_.*

PG_SCHEMA	test_schema

OUTPUT	user.sql
OUTPUT_DIR	C:\test

PG_INTEGER_TYPE	0
DATA_TYPE	DATE:timestamp