标签
PostgreSQL , Oracle , ora_migrator , oracle_fdw
背景
ora_migrator是一个插件，提供了一系列内置函数，使用这些函数接口以及oracle_fdw插件（PostgreSQL访问oracle的fdw接口），可以将Oracle的schema（包括table,view,sequence,function,procedure, trigger,等）连同数据，迁移到Oracle。
oracle_fdw
https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html
https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
下载Oracle客户端 sqlplus以及头文件等依赖包 zip或rpm任选
Base - one of these packages is required
Basic Package - All files required to run OCI, OCCI, and JDBC-OCI applications
Download instantclient-basic-linux.x64-18.5.0.0.0dbru.zip (72,902,980 bytes) (cksum - 2491300686)
Download oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm (51,528,664 bytes) (cksum - 261970379)
Tools - optional packages
SQL*Plus Package - The SQL*Plus command line tool for SQL and PL/SQL queries
Download instantclient-sqlplus-linux.x64-18.5.0.0.0dbru.zip (918,469 bytes) (cksum - 2922931520)
Download oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64.rpm (709,112 bytes) (cksum - 129366285)
Development and Runtime - optional packages
SDK Package - Additional header files and an example makefile for developing Oracle applications with Instant Client
Download instantclient-sdk-linux.x64-18.5.0.0.0dbru.zip (929,988 bytes) (cksum - 4087610828)
Download oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64.rpm (609,896 bytes) (cksum - 4231710329)
JDBC Supplement Package - Additional support for Internationalization under JDBC
Download instantclient-jdbc-linux.x64-18.5.0.0.0dbru.zip (1,578,269 bytes) (cksum - 2654413816)
Download oracle-instantclient18.5-jdbc-18.5.0.0.0-3.x86_64.rpm (1,525,064 bytes) (cksum - 1799995524)
ODBC Package - Additional libraries for enabling ODBC applications
Download instantclient-odbc-linux.x64-18.5.0.0.0dbru.zip (651,470 bytes) (cksum - 2317417013)
Download oracle-instantclient18.5-odbc-18.5.0.0.0-3.x86_64.rpm (257,264 bytes) (cksum - 2624592622)
以ZIP为例，解压
unzip instantclient-basic-linux.x64-18.5.0.0.0dbru.zip
unzip instantclient-sqlplus-linux.x64-18.5.0.0.0dbru.zip
unzip instantclient-odbc-linux.x64-18.5.0.0.0dbru.zip
unzip instantclient-jdbc-linux.x64-18.5.0.0.0dbru.zip
cd instantclient_18_5/
[root@pg11-test instantclient_18_5]# ll
-rwxr-xr-x 1 root root 42K Apr 3 15:42 adrci
-r-xr-xr-x 1 root root 5.7K Apr 3 15:42 BASIC_LICENSE
-rw-r--r-- 1 root root 1.6K Apr 3 15:42 BASIC_README
-rwxr-xr-x 1 root root 66K Apr 3 15:42 genezi
-rw-r--r-- 1 root root 342 Apr 3 15:42 glogin.sql
drwxrwxr-x 4 root root 4.0K Feb 27 04:04 help
-r-xr-xr-x 1 root root 5.7K Feb 27 04:04 JDBC_LICENSE
-rw-r--r-- 1 root root 1.6K Feb 27 04:04 JDBC_README
-rwxr-xr-x 1 root root 8.0M Apr 3 15:42 libclntshcore.so.18.1
lrwxrwxrwx 1 root root 17 Apr 3 15:42 libclntsh.so -> libclntsh.so.18.1
-rwxr-xr-x 1 root root 75M Apr 3 15:42 libclntsh.so.18.1
-r-xr-xr-x 1 root root 19K Feb 27 04:04 libheteroxa18.so
-r-xr-xr-x 1 root root 3.4M Apr 3 15:42 libipc1.so
-r-xr-xr-x 1 root root 457K Apr 3 15:42 libmql1.so
-r-xr-xr-x 1 root root 6.4M Apr 3 15:42 libnnz18.so
lrwxrwxrwx 1 root root 15 Apr 3 15:42 libocci.so -> libocci.so.18.1
-r-xr-xr-x 1 root root 2.2M Apr 3 15:42 libocci.so.18.1
-rwxr-xr-x 1 root root 122M Apr 3 15:42 libociei.so
-r-xr-xr-x 1 root root 158K Apr 3 15:42 libocijdbc18.so
-r-xr-xr-x 1 root root 386K Apr 3 15:42 libons.so
-r-xr-xr-x 1 root root 115K Apr 3 15:42 liboramysql18.so
-rwxr-xr-x 1 root root 1.6M Apr 3 15:42 libsqlplusic.so
-rwxr-xr-x 1 root root 1.6M Apr 3 15:42 libsqlplus.so
-rwxr-xr-x 1 root root 1.1M Feb 27 04:04 libsqora.so.18.1
drwxr-xr-x 3 root root 4.0K Apr 3 15:42 network
-r-xr-xr-x 1 root root 5.7K Feb 27 04:04 ODBC_LICENSE
-rw-r--r-- 1 root root 7.5K Feb 27 04:04 ODBC_README
-rwxr-xr-x 1 root root 4.9K Feb 27 04:04 odbc_update_ini.sh
-r--r--r-- 1 root root 4.0M Apr 3 15:42 ojdbc8.jar
-r--r--r-- 1 root root 1.6M Feb 27 04:04 orai18n.jar
-r--r--r-- 1 root root 88K Feb 27 04:04 orai18n-mapping.jar
drwxrwxr-x 5 root root 4.0K Feb 27 04:04 sdk
-r-xr-xr-x 1 root root 5.7K Feb 27 04:04 SDK_LICENSE
-rw-rw-r-- 1 root root 1.6K Feb 27 04:04 SDK_README
-rwxr-xr-x 1 root root 23K Apr 3 15:42 sqlplus
-r-xr-xr-x 1 root root 5.7K Apr 3 15:42 SQLPLUS_LICENSE
-rw-r--r-- 1 root root 1.7K Apr 3 15:42 SQLPLUS_README
-r--r--r-- 1 root root 1.4M Apr 3 15:42 ucp.jar
-rwxr-xr-x 1 root root 237K Apr 3 15:42 uidrvci
-rw-r--r-- 1 root root 73K Apr 3 15:42 xstreams.jar
测试sqlplus 是否正常使用
export LD_LIBRARY_PATH=.:$LD_LIBRARY_PATH
[root@pg11-test instantclient_18_5]# ./sqlplus --help
SQL*Plus: Release 18.0.0.0.0 - Production
Version 18.5.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.
Usage 1: sqlplus -H | -V
-H Displays the SQL*Plus version and the
usage help.
-V Displays the SQL*Plus version.
Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ]
<option> is: [-AC] [-C <version>] [-F] [-L] [-M "<options>"] [-NOLOGINTIME]
[-R <level>] [-S]
-AC Enable Application Continuity.
-C <version> Sets the compatibility of affected commands to the
version specified by <version>. The version has
the form "x.y[.z]". For example, -C 10.2.0
-F This option improves performance in general. It changes
the default values settings.
See SQL*Plus User's Guide for the detailed settings.
-L Attempts to log on just once, instead of
reprompting on error.
-M "<options>" Sets automatic HTML or CSV markup of output. The options
have the form:
{HTML html_options|CSV csv_options}
See SQL*Plus User's Guide for detailed HTML and CSV options.
-NOLOGINTIME Don't display Last Successful Login Time.
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
disables all user commands interacting with the
file system.
-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
commands.
<logon> is: {<username>[/<password>][@<connect_identifier>] | / }
[AS {SYSDBA | SYSOPER | SYSASM | SYSBACKUP | SYSDG
| SYSKM | SYSRAC}] [EDITION=value]
Specifies the database account username, password and connect
identifier for the database connection. Without a connect
identifier, SQL*Plus connects to the default database.
The AS SYSDBA, AS SYSOPER, AS SYSASM, AS SYSBACKUP, AS SYSDG,
AS SYSKM and AS SYSRAC options are database administration privileges.
<connect_identifier> can be in the form of Net Service Name
or Easy Connect.
@[<net_service_name> | [//]Host[:Port]/<service_name>]
<net_service_name> is a simple name for a service that resolves
to a connect descriptor.
Example: Connect to database using Net Service Name and the
database net service name is ORCL.
sqlplus myusername/mypassword@ORCL
Host specifies the host name or IP address of the database
server computer.
Port specifies the listening port on the database server.
<service_name> specifies the service name of the database you
want to access.
Example: Connect to database using Easy Connect and the
Service name is ORCL.
sqlplus myusername/mypassword@Host/ORCL
The /NOLOG option starts SQL*Plus without connecting to a
database.
The EDITION specifies the value for Session Edition.
<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]
Runs the specified SQL*Plus script from a web server (URL) or the
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.
When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run. The files may
contain SQL*Plus commands.
Refer to the SQL*Plus User's Guide and Reference for more information.
拷贝到PG OS用户下面
cp -r instantclient_18_5 /home/digoal/
安装oracle_fdw外部表插件
su - digoal
wget http://api.pgxn.org/dist/oracle_fdw/2.1.0/oracle_fdw-2.1.0.zip
unzip oracle_fdw-2.1.0.zip
cd oracle_fdw-2.1.0/
export ORACLE_HOME=/home/digoal/instantclient_18_5
USE_PGXS=1 make clean
USE_PGXS=1 make
USE_PGXS=1 make install
配置环境变量，PG启动时依赖ORACLE包
vi ~/.bash_profile
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=8001
export PGDATA=/data01/digoal/pg_root$PGPORT
export LANG=en_US.utf8
export PGHOME=/home/digoal/pgsql11.1
export ORACLE_HOME=/home/digoal/instantclient_18_5
export LD_LIBRARY_PATH=$ORACLE_HOME:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$ORACLE_HOME:$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi
配置postgresql.conf，加载oracle_fdw
vi $PGDATA/postgresql.conf
shared_preload_libraries = 'oracle_fdw,pg_hint_plan' # (change requires restart)
重启数据库
pg_ctl restart -m fast
使用oracle_fdw
digoal@pg11-test-> psql
psql (11.1)
Type "help" for help.
postgres=# create extension oracle_fdw ;
CREATE EXTENSION
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//xxx.xxx.xxx.xxx:1521/prod');
GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
CREATE USER MAPPING FOR postgres SERVER oradb
OPTIONS (user 'system', password 'oracle');
ORACLE
SQL> create user digoal identified by "DIGOAL123321_";
User created.
create table digoal.tbl123 (id int primary key, info varchar2(512), crt_time date);
SQL> grant dba to digoal;
Grant succeeded.
PG
create schema ora;
postgres=# import foreign schema "DIGOAL" from server oradb into ora;
IMPORT FOREIGN SCHEMA
postgres=# select * from ora.tbl123 ;
id | info | crt_time
----+------+----------
(0 rows)
postgres=# explain verbose select * from ora.tbl123 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ora.tbl123 (cost=10000.00..20000.00 rows=1000 width=556)
Output: id, info, crt_time
Oracle query: SELECT /*2f7156b3ee8d44022c295635d2320ba7*/ r1."ID", r1."INFO", r1."CRT_TIME" FROM "DIGOAL"."TBL123" r1
Oracle plan: SELECT STATEMENT
Oracle plan: TABLE ACCESS FULL TBL123
(5 rows)
postgres=# explain verbose insert into ora.tbl123 select generate_series(1,100000), md5(random()::Text),clock_timestamp();
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Insert on ora.tbl123 (cost=0.00..25.03 rows=1000 width=556)
Oracle statement: INSERT INTO "DIGOAL"."TBL123" ("ID", "INFO", "CRT_TIME") VALUES (:p1, :p2, CAST (:p3 AS TIMESTAMP))
-> Subquery Scan on "*SELECT*" (cost=0.00..25.03 rows=1000 width=556)
Output: "*SELECT*".generate_series, "*SELECT*".md5, "*SELECT*".clock_timestamp
-> ProjectSet (cost=0.00..5.03 rows=1000 width=44)
Output: generate_series(1, 100000), md5((random())::text), clock_timestamp()
-> Result (cost=0.00..0.01 rows=1 width=0)
(7 rows)
简单用法
1、创建oracle_fdw插件
CREATE EXTENSION oracle_fdw;
2、创建连接ORACLE的server，用户映射
CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//dbserver.mydomain.com/ORADB');
GRANT USAGE ON FOREIGN SERVER oracle TO migrator;
CREATE USER MAPPING FOR migrator SERVER oracle
OPTIONS (user 'orauser', password 'orapwd');
3、创建ora_migrator插件，并使用以上oracle foreign server访问ORACLE，将LAURENZ,SOCIAL这两个oracle中的schema连同数据迁移到PG。
注意这些吐出来的notice，记录了整个迁移过程。
CREATE EXTENSION ora_migrator;
SELECT oracle_migrate(server => 'oracle', only_schemas => '{LAURENZ,SOCIAL}');
NOTICE: Creating staging schemas "ora_stage" and "pgsql_stage" ...
NOTICE: Creating Oracle metadata views in schema "ora_stage" ...
NOTICE: Copy definitions to PostgreSQL staging schema "pgsql_stage" ...
NOTICE: Creating schemas ...
NOTICE: Creating sequences ...
NOTICE: Creating foreign tables ...
NOTICE: Migrating table laurenz.log ...
NOTICE: Migrating table laurenz.ft_speed_sa ...
NOTICE: Migrating table laurenz.badstring ...
WARNING: Error loading table data for laurenz.badstring
DETAIL: invalid byte sequence for encoding "UTF8": 0x80:
NOTICE: Migrating table laurenz.datetest ...
NOTICE: Migrating table laurenz.department ...
NOTICE: Migrating table laurenz.hasnul ...
WARNING: Error loading table data for laurenz.hasnul
DETAIL: invalid byte sequence for encoding "UTF8": 0x00:
NOTICE: Migrating table social.blog ...
NOTICE: Migrating table laurenz.employee ...
NOTICE: Migrating table laurenz.identity ...
NOTICE: Migrating table laurenz.req_lot ...
NOTICE: Migrating table social.email ...
NOTICE: Migrating table laurenz.numbers ...
NOTICE: Creating UNIQUE and PRIMARY KEY constraints ...
WARNING: Error creating primary key or unique constraint on table laurenz.badstring
DETAIL: relation "laurenz.badstring" does not exist:
WARNING: Error creating primary key or unique constraint on table laurenz.hasnul
DETAIL: relation "laurenz.hasnul" does not exist:
NOTICE: Creating FOREIGN KEY constraints ...
NOTICE: Creating CHECK constraints ...
NOTICE: Creating indexes ...
NOTICE: Setting column default values ...
NOTICE: Dropping staging schemas ...
NOTICE: Migration completed with 4 errors.
oracle_migrate
----------------
4
(1 row)
DROP EXTENSION ora_migrator;
日志中表明，有数据迁移错误（由于编码问题）。
详细用法
请参考代码与README
Only sequences and normal tables with their constraints and indexes will be migrated, all objects containing PL/SQL code (triggers, functions, procedures and packages) will have to be migrated by hand.
In addition to that, the extension can be used to create foreign tables and views that allow convenient access to Oracle metadata from PostgreSQL.
https://github.com/cybertec-postgresql/ora_migrator/blob/master/ora_migrator--0.9.1.sql
https://github.com/cybertec-postgresql/ora_migrator/blob/master/README.md
支持手工操作。
You can either perform the migration by calling oracle_migrate, or you do it step by step:
Call oracle_migrate_prepare to create the Oracle staging schema with the Oracle metadata views and the PostgreSQL staging schema with metadata copied and translated from the Oracle stage.
After this step, you can modify the data in the PostgreSQL stage, from which the PostgreSQL tables are created. This is useful if you want to modify data types, indexes or constraints.
Be aware that you cannot rename the schemas.
Also, if you want to rename tables, make sure that the new name is used in all tables consistently.The tables tables and functions in the PostgreSQL staging schema have a boolean attribute migrate that should be set to TRUE to include the object in the migration. Since functions will always require editing, the flag is initially set to FALSE for functions.
If the Oracle definitions change while you are working, you can refresh the tables in the PostgreSQL stage with oracle_migrate_refresh.
Call oracle_migrate_mkforeign to create the PostgreSQL schemas and sequences and foreign tables.
Call oracle_migrate_tables to replace the foreign tables with real tables and migrate the data from Oracle.
Alternatively, you can use oracle_materialize to do this step for Each table individually. This has the advantage that you can migrate several tables in parallel in multiple database sessions, which may speed up the migration process.
Call oracle_migrate_functions to migrate functions.
Call oracle_migrate_triggers to migrate triggers.
Call oracle_migrate_views to migrate views.
Call oracle_migrate_constraints to migrate constraints and indexes from Oracle.
Call oracle_migrate_finish to remove the staging schemas and complete the migration.
Hint: When developing a migration, it will be useful to set the parameter with_data to false, so that only the object definitions are migrated, but not the data. This will make the migration much faster and is useful for debugging problems that are not connected with the table data.
Apart from migration, you can use the function create_oraviews to create foreign tables and views that allow convenient access to Oracle metadata from PostgreSQL.
This is used by oracle_migrate_prepare to populate the staging schema, but it may be useful for other tools.
These foreign tables can be used in arbitrary queries, e.g.
SELECT table_name,
constraint_name,
column_name,
remote_table,
remote_column
FROM foreign_keys
WHERE schema = 'LAURENZ'
AND remote_schema = 'LAURENZ'
ORDER BY table_name, position;
The additional conditions will be pushed down to Oracle whenever that is possible for oracle_fdw, so the queries should be efficient.
All Oracle object names will appear like they are in Oracle, which is usually in upper case.
参考
https://github.com/cybertec-postgresql/ora_migrator
免费领取阿里云RDS PostgreSQL实例、ECS虚拟机
