去O--ora2pg使用

关于ora2pg

Ora2Pg是一个免费工具，用于将Oracle或MySQL数据库迁移到PostgreSQL兼容模式。它连接您的Oracle数据库，自动对其进行扫描并提取其结构或数据，然后生成可加载到PostgreSQL数据库中的SQL脚本。

Or2pg特性

导出整个数据库模式（表、视图、序列、索引），以及唯一性，主键、外键和检查约束。
导出用户和组的授权/权限。
导出筛选的表（通过制定表明）。
导出Oracle模式到一个PostgreSQL（7.3以后）模式中。
导出预定义函数、触发器、程序、包和包体。
导出范围和列表分区。
导出所有的数据或跟随一个WHERE子句。
充分支持Oracle BLOB对象作为PG的BYTEA。
导出Oracle视图作为PG表。
导出定义的Oracle用户格式。
提供关于转换PLSQL码为PLPGSQL的基本帮助（仍然需要手工完成）。
可在任何平台上工作。
Ora2Pg尽力将Oracle数据库转换到PostgreSQL中，但是仍需一部分的手动工作。Oracle特定的PL/SQL代码生成函数、过程 和触发器时必须进行审查，以便匹配PostgreSQL的语法。

Ora2pg支持的导出对象

对象	说明
table	提取所有包括索引、主键、唯一键、外键和检查约束的表。
view	提取视图。
grant	提取在所有对象中转换为pg组、用户和权限的用户。
sequence	提取所有的序列以及上一个位置。
trigger	提取通过动作触发的被指定的触发器。
function	提取函数。
proceduers	提取存储过程。
package	提取包和包主体。
data	提取数据，生成insert语句。
copy	提取数据，生成copy语句。
partition	提取范围和列表分区。
type	提取oracle用户自定义的格式。
fdw	提取外部数据封装表

ora2pg安装

oracle客户端安装

oracle-instantclient19.8-basic-19.8.0.0.0-1.x86_64.rpm
oracle-instantclient19.8-devel-19.8.0.0.0-1.x86_64.rpm
oracle-instantclient19.8-sqlplus-19.8.0.0.0-1.x86_64.rpm
下载地址：https://www.oracle.com/database/technologies/instant-client/downloads.html

配置环境变量

export ORACLE_HOME=/usr/lib/oracle/19.8/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
19.8 是安装的oracle客户端的版本号

DBI安装

下载: https://download.csdn.net/download/darking123456789/18660478.

tar -zxvf DBI-1.635.tar.gz
cd DBI-1.635
perl Makefile.PL -l
make && make test
make install

DBD-Oracle客户端安装

下载: https://download.csdn.net/download/darking123456789/18660500.

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

DBD-PG客户端安装

下载: https://download.csdn.net/download/darking123456789/18660513.

tar -zxvf DBD-Pg-2.17.2.tar.gz
cd DBD-Pg-2.17.2
perl Makefile.PL -l
make && make test
make install

安装Ora2pg客户端

下载: https://download.csdn.net/download/darking123456789/18660537.

tar -zxvf ora2pg-20.0.tar.gz
cd ora2pg-20.0
perl Makefile.PL -l
make && make test
make install

Ora2pg命令执行参数说明

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.conf_all

RACLE_HOME /usr/lib/oracle/19.8/client64 ORACLE_DSN dbi:Oracle:host=数据库地址;sid=sid;port=端口号 
ORACLE_USER oracle数据库用户名 
ORACLE_PWD oracle数据库密码 
SCHEMA NEW_AUTH 
USER_GRANTS 1 
DEBUG 0
ORA_INITIAL_COMMAND commit 
EXPORT_SCHEMA 1 
CREATE_SCHEMA 0 
COMPILE_SCHEMA 0 
TYPE TABLE,VIEW,GRANT,SEQUENCE,TABLESPACE,PROCEDURE,TRIGGER,FUNCTION,PACKAGE,PARTITION,TYPE,MVIEW,QUERY,DBLINK,SYNONYM,DIRECTORY #需要转化的类型
SKIP fkeys ukeys checks 
DISABLE_COMMENT 0 
EXTERNAL_TO_FDW 1 
TRUNCATE_TABLE 0 
USE_TABLESPACE 0 
REORDERING_COLUMNS 0 
CONTEXT_AS_TRGM 0 
FTS_INDEX_ONLY 1 
USE_UNACCENT 0 
USE_LOWER_UNACCENT 0 
DATADIFF 0 
DATADIFF_UPDATE_BY_PKEY 0 
DATADIFF_DEL_SUFFIX _del 
DATADIFF_UPD_SUFFIX _upd 
DATADIFF_INS_SUFFIX _ins 
DATADIFF_WORK_MEM 256 MB 
DATADIFF_TEMP_BUFFERS 512 MB 
KEEP_PKEY_NAMES 0 
PKEY_IN_CREATE 0 
FKEY_ADD_UPDATE never 
FKEY_DEFERRABLE 0 
DEFER_FKEY 0 
DROP_FKEY 0 
DISABLE_SEQUENCE 0 
DISABLE_TRIGGERS 1 
PRESERVE_CASE 0 
INDEXES_RENAMING 0 
USE_INDEX_OPCLASS 0 
PREFIX_PARTITION 0 
PREFIX_SUB_PARTITION 1 
DISABLE_PARTITION 0 
WITH_OID 0 
ORA_RESERVED_WORDS audit,comment,references 
USE_RESERVED_WORDS 0 
DISABLE_UNLOGGED 1 
PG_DSN dbi:Pg:dbname=PG数据库名;host=数据库地址;port=端口号 
PG_USER PG数据库用户名 
PG_PWD PG数据库密码 
OUTPUT NEW_AUTH_all.sql 
OUTPUT_DIR /data/o2p/ora2pg_data 
FILE_PER_CONSTRAINT 0 
FILE_PER_INDEX 0 
FILE_PER_FKEYS 0 
PG_VERSION 12

执行模型转化命令

ora2pg -c ora2pg.conf_all -o new_auth_all.sql

执行成功后将生成的脚本通过数据库工具导入到pgsql中

数据导入

编写Ora2pg数据迁移配置文件ora2pg.conf_data

RACLE_HOME /usr/lib/oracle/19.8/client64 
ORACLE_DSN dbi:Oracle:host=oracle数据库地址;sid=sid;port=端口号 
ORACLE_USER oracle数据库用户名 
ORACLE_PWD oracle数据库密码
SCHEMA NEW_AUTH
USER_GRANTS 1 
DEBUG 0 
ORA_INITIAL_COMMAND commit 
EXPORT_SCHEMA 1 
CREATE_SCHEMA 0 
COMPILE_SCHEMA 0 
TYPE COPY 
DISABLE_COMMENT 0 
EXTERNAL_TO_FDW 1 
TRUNCATE_TABLE 0 
USE_TABLESPACE 0 
REORDERING_COLUMNS 0 
CONTEXT_AS_TRGM 0 
FTS_INDEX_ONLY 1 
USE_UNACCENT 0 
USE_LOWER_UNACCENT 0 
DATADIFF 0 
DATADIFF_UPDATE_BY_PKEY 0 
DATADIFF_DEL_SUFFIX _del 
DATADIFF_UPD_SUFFIX _upd 
DATADIFF_INS_SUFFIX _ins 
DATADIFF_WORK_MEM 256 MB 
DATADIFF_TEMP_BUFFERS 512 MB 
KEEP_PKEY_NAMES 0 
PKEY_IN_CREATE 0 
FKEY_ADD_UPDATE never 
FKEY_DEFERRABLE 0 
DEFER_FKEY 0 
DROP_FKEY 0 
DISABLE_SEQUENCE 0 
DISABLE_TRIGGERS 1 
PRESERVE_CASE 0 
INDEXES_RENAMING 0 
USE_INDEX_OPCLASS 0 
PREFIX_PARTITION 0 
PREFIX_SUB_PARTITION 1 
DISABLE_PARTITION 0 
WITH_OID 0 
ORA_RESERVED_WORDS audit,comment,references 
USE_RESERVED_WORDS 0 
DISABLE_UNLOGGED 1 
PG_DSN dbi:Pg:dbname=pg数据库名;host=pg数据库地址;port=端口号 
PG_USER pg数据库用户名 
PG_PWD pg数据库密码
PG_SCHEMA 模式
OUTPUT data.sql 
OUTPUT_DIR /data/o2p/ora2pg_data
BZIP2 
FILE_PER_CONSTRAINT 0 
FILE_PER_INDEX 0 
FILE_PER_FKEYS 0 
FILE_PER_TABLE 0 
FILE_PER_FUNCTION 0 
STOP_ON_ERROR 1 
COPY_FREEZE 0 
CREATE_OR_REPLACE 1 
PG_NUMERIC_TYPE 0 
PG_INTEGER_TYPE 1 
DEFAULT_NUMERIC float 
SKIP fkeys ukeys checks 
ENABLE_MICROSECOND 1 
TO_NUMBER_CONVERSION numeric 
GEN_USER_PWD 0 
FORCE_OWNER 0 
FORCE_SECURITY_INVOKER 0 
DATA_LIMIT 10000 
NOESCAPE 0 
TRANSACTION serializable 
STANDARD_CONFORMING_STRINGS 1 
NO_LOB_LOCATOR 1 
XML_PRETTY 0 
LOG_ON_ERROR 1 
TRIM_TYPE BOTH 
INTERNAL_DATE_MAX 49 
FUNCTION_CHECK 1 
NO_BLOB_EXPORT 0 
DATA_EXPORT_ORDER name
JOBS 1 
ORACLE_COPIES 1 
PARALLEL_TABLES 1 
DEFAULT_PARALLELISM_DEGREE 0 
PARALLEL_MIN_ROWS 100000 
DROP_INDEXES 0 
SYNCHRONOUS_COMMIT 0 
EXPORT_INVALID 0 
PLSQL_PGSQL 1 
NULL_EQUAL_EMPTY 0 
EMPTY_LOB_NULL 0 
PACKAGE_AS_SCHEMA 1 
REWRITE_OUTER_JOIN 1 
FUNCTION_STABLE 1 
COMMENT_COMMIT_ROLLBACK 0 
COMMENT_SAVEPOINT 0 
USE_ORAFCE 0 
AUTONOMOUS_TRANSACTION 1 
ESTIMATE_COST 0 
COST_UNIT_VALUE 5 
DUMP_AS_HTML 0 
TOP_MAX 10 
HUMAN_DAYS_LIMIT 5 
PG_VERSION 11 
BITMAP_AS_GIN 1 
PG_BACKGROUND 0 
PG_SUPPORTS_SUBSTR 1 
AUTODETECT_SPATIAL_TYPE 1 
CONVERT_SRID 1 
DEFAULT_SRID 4326 
GEOMETRY_EXTRACT_TYPE INTERNAL 
MYSQL_PIPES_AS_CONCAT 0 
MYSQL_INTERNAL_EXTRACT_FORMAT 0 
PG_VERSION 12

执行数据迁移命令

ora2pg -d -c ora2pg.conf_data > import.log &

优化数据迁移并发命令示例如下
所有表数据迁移命令

ora2pg -d -L 100000 -P 10 -j 10 -c ora2pg.conf_data > import.log & 

数据迁移过程将比较大的日志表剔除，从而提高数据迁移效率（对生产环境无影响）。

ora2pg.conf_data -e TABLE'[A,B]' > import.log & 

注：要确认数据表中数据为清空状态