資料庫上雲實踐：使用Ora2pg進行資料庫遷移

adminmttt發表於2022-03-28
資料庫

目錄

概述

本問將介紹如何使用 Oracle到postgresql的遷移使用ora2pg工具。配置環境後，你將瞭解如何編寫配置檔案，進行資料庫遷移任務。

重要

  • 在本教程中，你將瞭解如何執行以下操作：
  • 編寫ora2pg配置檔案
  • 通過配置檔案將Oracle中的表、序列、檢視、同義詞、函式過程包進行遷移

前置條件

  • perl直譯器
  • perl 模組 DBD(DataBase Description) *DBI(DataBase Interface)
  • ora2pg原始碼

配置環境

Win環境配置

  1. perl直譯器安裝: strawberry-perl-5.32.1.1-64bit.msi
  2. 在win下 strawberry-perl(version 5.32以後)自帶DBD::Oracle 和`DBD::Pg.
perl -MCPAN -e install DBD::Oracle
perl -MCPAN -e install DBD::Pg
  1. 利用perl直譯器，解釋makefile.pl檔案，配置環境並安裝ora2pg。win下使用gmake指令
cd ora2pg 
perl Makefile.PL
gmake && gmake install
  1. DBI的安裝需要下載原始碼包，通過原始碼安裝(在win下，可以使用gmake指令)
tar -zxvf DBI-1.641.tar.gz
cd DBI-1.641
perl Makefile.PL
gmake && gmake install
  1. 安裝Oracle客戶端

linux環境配置

  1. perl版本5.10以上，安裝依賴包 perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker(注：win下的strawberry-perl自帶這兩個包)

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

  2. 安裝DBI

    tar -zxvf DBI-1.641.tar.gz
cd DBI-1.641
perl Makefile.PL
make && make install

  3. 安裝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

  4. 安裝DBD-Oracle

    定義ORACLE_HOME環境變數

    tar -zxvf DBD-Oracle-1.74.tar.gz
cd DBD-Oracle-1.74
perl Makefile.PL
make && make install

  5. 安裝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的環境變數

  6. 安裝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
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

相關文章