目錄
概述
本問將介紹如何使用 Oracle到postgresql的遷移使用ora2pg工具。配置環境後，你將瞭解如何編寫配置檔案，進行資料庫遷移任務。
重要
- 在本教程中，你將瞭解如何執行以下操作：
- 編寫ora2pg配置檔案
- 通過配置檔案將Oracle中的表、序列、檢視、同義詞、函式過程包進行遷移
前置條件
- perl直譯器
- perl 模組 DBD(DataBase Description) *DBI(DataBase Interface)
- ora2pg原始碼
配置環境
Win環境配置
- perl直譯器安裝: strawberry-perl-5.32.1.1-64bit.msi
- 在win下 strawberry-perl(version 5.32以後)自帶
DBD::Oracle和`DBD::Pg.
perl -MCPAN -e install DBD::Oracle
perl -MCPAN -e install DBD::Pg
- 利用perl直譯器，解釋makefile.pl檔案，配置環境並安裝ora2pg。win下使用gmake指令
cd ora2pg
perl Makefile.PL
gmake && gmake install
- DBI的安裝需要下載原始碼包，通過原始碼安裝(在win下，可以使用gmake指令)
tar -zxvf DBI-1.641.tar.gz
cd DBI-1.641
perl Makefile.PL
gmake && gmake install
- 安裝Oracle客戶端
linux環境配置
-
perl版本5.10以上，安裝依賴包
perl-ExtUtils-CBuilderperl-ExtUtils-MakeMaker(注：win下的strawberry-perl自帶這兩個包)yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -
安裝DBI
tar -zxvf DBI-1.641.tar.gz cd DBI-1.641 perl Makefile.PL make && make install -
安裝Oracle客戶端(略)
rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm -
安裝DBD-Oracle
定義ORACLE_HOME環境變數
tar -zxvf DBD-Oracle-1.74.tar.gz cd DBD-Oracle-1.74 perl Makefile.PL make && make install -
安裝DBD:PG
#使用官方提供的yum安裝方式 #Linux系統版本centos7 64位版本 #Pg版本為12 yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install postgresql12 yum install postgresql12-server yum install postgresql12-contrib #配置開機啟動與啟動 systemctl enable postgresql-12 systemctl start postgresql-12 #修改配置檔案 #開啟並編輯檔案“/var/lib/pgsql/12/data/postgresql.conf”。將“#listen_addresses = ‘localhost’”改為“listen_addresses = ‘*’” #開啟並編輯檔案“/var/lib/pgsql/12/data/pg_hba.conf”。在檔案的末尾新增“host all all 0.0.0.0/0 md5” #設定pgsql的環境變數 -
安裝Ora2PG
tar -zxvf ora2pg-22.1.tar.bz2cd ora2pg-22.1/perl Makefile.PLmake && make install
Ora2Pg使用方法
編寫conf檔案，執行指令將Oracle轉化為Postgresql
ora2pg -c ora2pg_table.conf
配置檔案ora2pg_table.conf
PG_VERSION 12
ORACLE_HOME /home/oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_DSN dbi:Oracle:host=xx.xx.x.xxx;service_name=xxx;port=xx
ORACLE_USER xxxxxx
ORACLE_PWD ######
USER_GRANTS 1
SCHEMA xxxx
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 /home/ora2pg/xxxxx.sql
配置檔案解釋：Oracle資料庫連線配置項
ORACLE_HOME
使用ORACLE_HOME作為環境變數，ora2pg可以通過該引數找到Perl的DBD模組連線ORACLE資料庫所需要的依賴包。
- 若在Linux環境下，該變數可能為：
/home/oracle/app/oracle/product/11.2.0/dbhome_1 - 若在Windows環境下，該變數可能為：
D:\app\Administrator\product\11.2.0\dbhome_1
ORACLE_DSN
該引數與Oracle資料庫具體配置有關，主要為資料庫的
host,sid,port等引數
dbi:Oracle:host=oradb_host.myhost.com;sid=DB_SID;port=1521dbi:Oracle:DB_SID
#On 18c this could be for example:dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521
#for the second notation the SID should be declared in the well known
#file $ORACLE_HOME/network/admin/tnsnames.ora or in the path given to#the TNS_ADMIN environment variable.
#For MySQL the DSN will lool like this:dbi:mysql:host=192.168.1.10;database=sakila;port=3306
the 'sid' part is replaced by 'database'.
ORACLE_USER et ORACLE_PWD
USER和PWD表示Oracle資料庫的使用者名稱和密碼，選用賬號的時候儘量選擇DBA許可權的賬號，以防止資料庫掃描時出現的許可權問題。
- 若配置檔案*.conf未設定該引數，可安裝perl的Term::ReadKey模組，在執行指令時的互動式命令列輸入賬號密碼
- 連線本地的資料據庫例項如果為"as sysdba"，那麼可以將ORACLE_USER設定為""，密碼為空
USER_GRANTS
- 如果連線Oracle資料庫為普通，該值設定為1，此時沒有許可權抽取DBA 表格，此時將會使用ALL 表格
- 若匯出型別為
GRANT，該值必須設定為0，否者將無效。
TRANSACTION*
事務隔離型別，如果需要改變匯出資料的預設隔離級別，需要設定該引數，總共由4中事務隔離型別：
- READ ONLY
- READ WRITE
- ISOLATION LEVEL SERIALIZABLE
- ISOLATION LEVEL READ COMMITTED
基本原理
在Oarcle資料庫和Postgresql資料庫中間，使用一臺機器，這臺機器實現將Oracle資料庫的後設資料匯出，並根據資料庫中的表格、序列、檢視等內容生成符合Postgresql語法的DDL(data definition language)，並儲存為相應的 *.sql檔案按
資料型別變化(參考Ora2PG原始碼定義)
our %TYPE = ( 'NUMBER' => 'numeric',
# CHAR types limit of 2000 bytes with defaults to 1 if no length
# is specified. PG char type has max length set to 8104 so it
# should match all needs
'CHAR' => 'char',
'NCHAR' => 'char',
# VARCHAR types the limit is 2000 bytes in Oracle 7 and 4000 in
# Oracle 8. PG varchar type has max length iset to 8104 so it
# should match all needs
'VARCHAR' => 'varchar',
'NVARCHAR' => 'varchar',
'VARCHAR2' => 'varchar',
'NVARCHAR2' => 'varchar',
'STRING' => 'varchar',
# The DATE data type is used to store the date and time
# information. PG type timestamp should match all needs.
'DATE' => 'timestamp',
# Type LONG is like VARCHAR2 but with up to 2Gb. PG type text
# should match all needs or if you want you could use blob
'LONG' => 'text',
# Character data of variable length
'LONG RAW' => 'bytea',
# Types LOB and FILE are like LONG but with up to 4Gb. PG type
# text should match all needs or if you want you could use blob
# (large object)
'CLOB' => 'text',
# A large object containing single-byte characters
'NCLOB' => 'text',
# A large object containing national character set data
'BLOB' => 'bytea',
# Binary large object
# The full path to the external file is returned if destination type is text.
# If the destination type is bytea the content of the external file is returned.
'BFILE' => 'bytea',
# Locator for external large binary file
# The RAW type is presented as hexadecimal characters. The
# contents are treated as binary data. Limit of 2000 bytes
# PG type text should match all needs or if you want you could
# use blob (large object)、
'RAW' => 'bytea',
'ROWID' => 'oid',
'UROWID' => 'oid',
'FLOAT' => 'double precision',
'DEC' => 'decimal',
'DECIMAL' => 'decimal',
'DOUBLE PRECISION' => 'double precision',
'INT' => 'numeric',
'INTEGER' => 'numeric',
'BINARY_INTEGER' => 'integer',
'PLS_INTEGER' => 'integer',
'REAL' => 'real',
'SMALLINT' => 'smallint',
'BINARY_FLOAT' => 'double precision',
'BINARY_DOUBLE' => 'double precision',
'TIMESTAMP' => 'timestamp',
'BOOLEAN' => 'boolean',
'INTERVAL' => 'interval',
'XMLTYPE' => 'xml',
'TIMESTAMP WITH TIME ZONE' => 'timestamp with time zone',
'TIMESTAMP WITH LOCAL TIME ZONE' => 'timestamp with time zone',
'SDO_GEOMETRY' => 'geometry'
);
常見的Oracle物件到Postgresql資料庫的轉換
| ORACLE | POSTGRESQL |
|---|---|
| Database Link | Foreign Data Wrapper |
| External Table | Foreign Table |
| Synonym | View / Set search_path |
| Global Temporary Table | Unlogged Table / Temp Table |
| Virtual column | View / Function / Trigger |
| Connect by | With Recursive |
| Reverse Index | Functional Index |
| Index Organized Table (IOT table) | Cluster the table according to an Index |
資料庫連結(Database Link)
Oracle
CREATE PUBLIC DATABASE LINK remote_service USING 'remote_db';
SELECT * FROM employees@remote_service;
Postgresql
CREATE SERVER remote_service FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'remote_db');
CREATE USER MAPPING FOR current_user SERVER remote_service OPTIONS (user 'scott', password 'tiger');
CREATE FOREIGN TABLE employees_fdw (<columns_list>) SERVER remote_service OPTIONS(schema 'HR',table 'EMPLOYEES');
外部表(External Tables)
假設有這麼一張表
cat /data/ext/file_ext.csv
1234,ALBERT,GRANT,21
1235,ALFRED,BLUEOS,26
1236,BERNY,JOLYSE,34
Oracle
CREATE OR REPLACE DIRECTORY ext_dir AS '/data/ext/';
CREATE TABLE ext_table (
empno VARCHAR2(4),
firstname VARCHAR2(20),
lastname VARCHAR2(20),
age VARCHAR2(2)
) ORGANIZATION EXTERNAL (DEFAULT DIRECTORY ext_dir ACCESS PARAMETERS (…LOCATION ('file_ext.csv')));
Postgresql
CREATE FOREIGN TABLE ext_table(
empno VARCHAR(4),
firstname VARCHAR(20),
lastname VARCHAR(20),
age VARCHAR(2)
)SERVER ext_dir OPTIONS (filename '/data/ext/file_ext.csv', format 'csv', delimiter ',');
同義詞(Synonyms)
Synonyms are not supported in PostgreSQL
Oracle
CREATE PUBLIC SYNONYM emp_table FOR hr.employees [@ dblink];
Postgresql
有兩種方式search path和view
--search path – session level – no permanent effect, it needs to be set for every connectionSET search_path TO other_schema;
--search path – role or database level – it takes permanent effect--@postgresqlalter database <database_name> set search_path = "other_schema";--@database_namealter role <role_name> set search_path = "other_schema";--view:CREATE VIEW public.emp_table AS SELECT * FROM hr.employees;ALTER VIEW public.emp_table OWNER TO hr;GRANT ALL ON public.emp_table TO PUBLIC;
全域性臨時表(Global Temporary Tables)
Option 1: Unlogged Table:
Oracle
CREATE GLOBAL TEMPORARY TABLE MY_CONTRACT_MONTH(
ID NUMBER(10),
CMONTH DATE
)ON COMMIT DELETE ROWS;
PostgreSQL
CREATE UNLOGGED TABLE MY_CONTRACT_MONTH(
ID number,
CMONTH timestamp,
pid bigint default pg_backend_pid()
);
ALTER TABLE MY_CONTRACT_MONTH ENABLE ROW LEVEL SECURITY;
ALTER TABLE MY_CONTRACT_MONTH FORCE ROW LEVEL SECURITY;
CREATE POLICY cm_pid ON MY_CONTRACT_MONTH TO <role_name>
USING (pid = (selectpg_backend_pid()));
在Postgresql中Role即User，若表格不儲存日誌，對於 Unlogged 表，必須實現行級安全，以防止會話查詢其他會話資料。還需要實施一項作業，以消除非活動會話的未記錄表上的資料
DELETE FROM smdr.contract_months cm
WHERE not exists (select 1 from pg_stat_activity psa where psa.pid = cm.pid);
Option 2: Temp Table:
Oracle
CREATE GLOBAL TEMPORARY TABLE MY_CONTRACT_MONTH(
ID NUMBER(10),
CMONTH DATE
)ON COMMIT DELETE ROWS;
--Oracle 永久儲存臨時表的定義，就像常規表的定義一樣。
PostgreSQL
CREATE GLOBAL TEMPORARY TABLE MY_CONTRACT_MONTH(
ID NUMERIC,
CMONTH TIMESTAMP
)ON COMMIT DELETE ROWS;
CREATE TEMPORARY TABLE 語句建立一個臨時表，該表在會話或當前事務結束時自動刪除（ON COMMIT DROP 選項。在轉換過程中，您需要從應用程式程式碼中提取 CREATE TEMPORARY TABLE 語句，儲存過程、觸發器等並執行它們一次以建立臨時表定義。Oracle 不支援 ON COMMIT DROP，因此如果需要此選項，則需要明確每次提交後執行 DROP TABLE 語句 ON COMMIT PRESERVE ROWS 是 PostgreSQL 中的預設值，ON COMMIT DELETE ROWS 是 Oracle 中的預設值
Virtual Column
Oracle
CREATE TABLE VIRT_COL_TABLE (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL
);
PostgreSQL
CREATE TABLE virt_col_table (
id bigint NOT NULL,
first_name varchar(10),
last_name varchar(10),
salary double precision,
comm1 smallint,
comm2 smallint,
salary1 bigint,
salary2 bigint);
Ora2pg通過一個包含觸發器的定義的額外檔案 VIRTUAL_COLUMNS_(...).sql，來實現原始Virtual Column的預設值
DROP TRIGGER IF EXISTS virt_col_VIRT_COL_TABLE_trigger ON VIRT_COL_TABLE CASCADE;
CREATE OR REPLACE FUNCTION fct_virt_col_VIRT_COL_TABLE_trigger()
RETURNS trigger
AS $BODY$
BEGINNEW.SALARY2 = ROUND(NEW.SALARY*(1+NEW.COMM2/100),2);
NEW.SALARY1 = ROUND(NEW.SALARY*(1+NEW.COMM1/100),2);
RETURN NEW;
end
$BODY$ LANGUAGE 'plpgsql' SECURITY DEFINER;
CREATE TRIGGER virt_col_VIRT_COL_TABLE_trigger
BEFORE INSERT OR UPDATE ON VIRT_COL_TABLE FOR EACH ROWEXECUTE PROCEDURE fct_virt_col_VIRT_COL_TABLE_trigger();
Connect By – Hierarchical query
在Oracle通過connect by (子句) 以實現層級遍歷，而在PostgreSQL中使用WITH RECURSIVE (子句)
Oracle
CREATE TABLE taxonomy (
key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY,
value VARCHAR2(255),taxHier NUMBER(11)
);
ALTER TABLE taxonomy ADD CONSTRAINT taxTaxFkey FOREIGN KEY (taxHier) REFERENCEStax(key);
SELECT value FROM taxonomy CONNECT BY PRIOR key = taxHier START WITH key = 0;
PostgreSQL
WITH RECURSIVE cte AS (
SELECT key, value, 1 AS level
FROM taxonomy
WHERE key = 0
UNION ALL
SELECT t.key, t.value, c.level + 1
FROM cte c
JOIN taxonomy t ON t.taxHier = c.key
)
SELECT value FROM cte ORDER BY level;
反向索引(Reverse Index)
當反向索引應用於 TEXT 列時，此解決方法有效。
Oracle
CREATE TABLE REV_TEMP (
Id NUMBER(10) NOT NULL PRIMARY KEY,
Description VARCHAR2(512) NOT NULL
);
CREATE INDEX REV_TEMP_N1 ON REV_TEMP(Description) REVERSE;
PostgreSQL
CREATE TABLE REV_TEMP (
Id NUMERIC(10) NOT NULL PRIMARY KEY,
Description VARCHAR(512) NOT NULL
);
CREATE INDEX REV_TEMP_N1 ON REV_TEMP(REVERSE(Description));
索引組織表(Index Organized Table)
Oracle 資料庫預設使用堆表。 可以使用 ORGANIZATION INDEX 子句建立索引組織表，Oracle 資料庫始終使用主鍵作為叢集鍵。
Oracle
CREATE TABLE IOT_TEMP (
Id NUMBER(10) NOT NULL PRIMARY KEY,
Description VARCHAR2(512) NOT NULL
) ORGANIZATION INDEX;
PostgreSQL
PostgreSQL 只使用堆表。 但是，使用 CLUSTER 子句將堆表的內容與索引對齊。
CREATE TABLE IOT_TEMP (
Id NUMERIC(10) NOT NULL PRIMARY KEY,
Description VARCHAR(512) NOT NULL
);
CREATE INDEX IOT_TEMP_N1 ON IOT_TEMP(ID);
job梳理與改造
SELECT JOB,WHAT,INTERVAL,SCHEMA_USER FROM ALL_JOBS WHERE SCHEMA_USER='SJCK'
Known Unsupported
- Type inheritance and type with member method are not supported
- Global indexes over partitions are not supported
- Compound triggers are not supported