## 使用 ora_migrator + oracle_fdw 迁移Oracle 到 PostgreSQL  
                                      
### 作者                                      
digoal                                      
                                      
### 日期                                      
2019-03-11                                      
                                      
### 标签                                      
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/database/database-technologies/instant-client/downloads/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  
  
      
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
#### [PostgreSQL 许愿链接](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216")
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点.  针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。[开不开森](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216").   
  
  
#### [9.9元购买3个月阿里云RDS PostgreSQL实例](https://www.aliyun.com/database/postgresqlactivity "57258f76c37864c6e6d23383d05714ea")
  
  
#### [PostgreSQL 解决方案集合](https://yq.aliyun.com/topic/118 "40cff096e9ed7122c512b35d8561d9c8")
  
  
#### [德哥 / digoal's github - 公益是一辈子的事.](https://github.com/digoal/blog/blob/master/README.md "22709685feb7cab07d30f30387f0a9ae")
  
  
![ digoal's wechat](../pic/digoal_weixin.jpg "f7ad92eeba24523fd47a6e1a0e691b59")
  
