目录

• • 概述
    • • 重要
      • 前置条件
  • 配置环境
    • • Win环境配置
      • linux环境配置
• 定义ORACLE_HOME环境变量
  • Ora2Pg使用方法
    • • 配置文件ora2pg_table.conf
      • 配置文件解释：Oracle数据库连接配置项
        • ORACLE_HOME
        • ORACLE_DSN
        • ORACLE_USER et ORACLE_PWD
        • USER_GRANTS
        • TRANSACTION*
  • 基本原理
    • 数据类型变化(参考Ora2PG源码定义)
    • 常见的Oracle对象到Postgresql数据库的转换
      • 数据库链接(Database Link)
        • Oracle
        • Postgresql
      • 外部表(External Tables)
        • Oracle
        • Postgresql
      • 同义词(Synonyms)
        • Oracle
        • Postgresql
      • 全局临时表(Global Temporary Tables)
        • Oracle
        • PostgreSQL
        • Oracle
        • PostgreSQL
      • Virtual Column
        • Oracle
        • PostgreSQL
      • Connect By – Hierarchical query
        • Oracle
        • PostgreSQL
      • 反向索引(Reverse Index)
        • Oracle
        • PostgreSQL
      • 索引组织表(Index Organized Table)
        • Oracle
        • PostgreSQL
      • job梳理与改造
      • Known Unsupported

概述 本问将介绍如何使用 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

3. 利用perl解释器，解释makefile.pl文件，配置环境并安装ora2pg。win下使用gmake指令

cd ora2pg perl Makefile.PL gmake && gmake install

4. DBI的安装需要下载源码包，通过源码安装(在win下，可以使用gmake指令)

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

5. 安装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. 【数据库上云实践（使用Ora2pg进行数据库迁移）】安装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_DSNdbi:Oracle:host=xx.xx.x.xxx; service_name=xxx; port=xx ORACLE_USER xxxxxx ORACLE_PWD###### USER_GRANTS 1 SCHEMAxxxx TYPE TABLE PG_NUMERIC_TYPE0 PG_INTEGER_TYPE1 DEFAULT_NUMERIC float #SKIPfkeys pkeys ukeys indexes checks NLS_LANGAMERICAN_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 () 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 set search_path = "other_schema"; --@database_namealter role 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 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

推荐阅读

• 

  非洲草原所有动物名单 非洲大草原上的动物
• 

  荣耀V20和荣耀20有什么区别,哪个性价比更高？
• 

  如何救助被泥石流伤害的人员
• 

  河北师范大学招生办公室 河北师范大学招生办电话号码
• 

  Word如何固定表格表名，word怎么固定表格
• 

  我是特种兵之利刃出鞘大结局我是特种兵之利刃出鞘大结局剧情大结局完整播放
• 

  秋天喝凉茶伤脾伤胃伤肠道
• 

  [RK3288][Android6.0] RTC模块RX8010SJ驱动添加及改动
• 

  土豆怎么保存不发芽
• 

  创业直播录屏，直播录屏算原创吗
• 

  如何在牛仔裤上 磨烂皮肤 如何在牛仔裤上 磨烂，如何在牛仔裤上 磨烂布
• 

  服务器怎么查看磁盘内存 服务器怎么查看磁盘
• 

  想买一个单反,请问索尼有什么推荐的产品？
• 

  如何在魅族手机上登录云服务器？ 魅族手机怎么登录云服务器
• 

  请问鲍鱼买回家可以养多久
• 

  小开头的三个字网名搞笑 小开头的三个字网名
• 

  夫妻债务新司法解释 夫妻债务新司法解释2018溯及力
• 

  灶台风水朝向西最吉利 灶台风水朝向西是不是最吉利
• 

  我的世界fill怎么玩？
• 

  《汉字找茬王》找出最近的热梗通关攻略

• 大咖说｜网易数帆论道|大咖说｜网易数帆论道 PolarDB 数据库开源 & 存储生态
• 让计算机教育因云而变，阿里云正式发布云原生人才培养合作计划
• c++|Open3D点云库 C++学习笔记
• 历史上的今天|【历史上的今天】3 月 28 日（EPROM 的发明者出生；计算机进入艾滋病研究领域）
• 软件的使用|MAC 上的 PostMan 汉化
• DevSecOps邂逅云原生（云原生时代下的持续安全）
• 2022钉钉发布会｜云钉低代码新模式、新能力、新机遇
• 数据库篇（mysql日志类型之|数据库篇：mysql日志类型之 redo、undo、binlog）
• 好云推荐官丨飞天加速之星怎样选择云服务器ECS（）
• 不是华为手机可以装鸿蒙,不是华为手机，也能用上鸿蒙系统