[大数据]ora2pg 导出Oracle数据

当要将 Oracle 中的数据迁移到 PG中时，我们选择 ora2pg，经过实测，ora2pg导出和转换Oracle的数据，效果是很好的，如果配置适当，速度很快，也没有错误。

但是，我们不用ora2pg导出Oracle的表定义，或者其它对象的定义，因为不太好控制。

用ora2pg导出Oracle表数据时，每个表的数据导出为一个文件。

安装：

ora2pg是用perl编写的开源软件，我们使用编译安装的方式（难以制作可移植的perl+ora2pg二进制安装包），以root用户编译安装。

sudo yum install perl-devel

sudo yum install perl-DBI

ora2pg需要Oracle客户端库：

sudo rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm

sudo rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm

sudo rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm

sudo rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

然后设置环境变量，也可以放到~/.bash_profile中：

export ORACLE_HOME=/usr/lib/oracle/12.2/client64

export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib:$LD_LIBRARY_PATH

export PATH=/usr/lib/oracle/12.2/client64/bin:$PATH

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

DBD-Oracle-1.80是Perl连接Oracle的模块，包含动态链接库，安装ora2pg之前先安装它，采用编译安装：

cd DBD-Oracle-1.80

perl Makefile.PL

make

sudo make install

然后安装ora2pg，也是以编译的方式安装：

cd ora2pg

perl Makefile.PL

make

sudo make install

ora2pg目录下有一个ora2pg.conf，这是ora2pg的配置文件，需要关系的设置如下：

这些参数注释掉，我们会在调用ora2pg时以参数形式传入。?

以右边为准

??DATA_LIMIT和LONGREADLEN根据需要调整，DATA_LIMIT x LONGREADLEN 是读取一次占用的内存。LONGREADLEN越小读取速度越快，在700多个记录大小不同的表中测试，LONGREADLEN设为1M，10个线程select读取速度可以达到6万条记录每秒，2M时1700记录每秒，30M时50记录每秒。

但导出表时，LONGREADLEN太小会失败，LONGREADLEN应大于表中数据量最大的那条记录。

LOB_CHUNK_SIZE设为4M据说能提高速度，但是在我的场景中实测没有什么效果。

USE_LOB_LOCATOR ?1

ENABLE_BLOB_EXPORT ? ? ?1

使用：

安装好ora2pg后，使用脚本ora2pg_dump.sh导出Oracle数据，这个脚本需要输入：

1. 连接Oracle数据库的信息。
2. 包含所有要导出表名的文本文件，表名不包括schema部分。
3. 表所在的schema名。
4. 导出的数据文件存放目录。
5. 并行导出的线程数。

例如命令：

ora2pg_dump.sh -s "dbi:Oracle:host=172.32.150.13;sid=nlpass01;port=1521" -u paastest -w Paas1015 -b output -i table_list.txt -n paastest -P 10
-s是连接oracle的dsn信息
-u是连接oracle的用户名
-w是oracle数据库用户的密码
-b是导出数据所在目录
-i是要导出的表名列表
-n是表的schema或用户名
-P并行导出的线程数

ora2pg_dump.sh文件内容如下：
其中GROUP变量是每次调用ora2pg处理的表的个数，实测700个表调用一次ora2pg用10个线程处理，导出数据的速度也较快，可以达到6万条记录每秒，ora2pg是使用select从oracle中读取数据的，使用select之前将事务隔离级别设置为serializable。

#!/bin/sh

tables_file=
parallel=
dsn=
ora_user=
ors_pw=
output=
schema=

usage="
Usage:
      $(basename $0) -s \"dbi:Oracle:host=172.32.150.13;sid=nlpass01;port=1521\" -u ora-user -w 123456 -b output -i tablelist.txt -n ora-schema -P 10

Options:
       -s oracle dsn

       -u oracle user name

       -w oracle password

       -b output dir
       
       -n oracle schema

       -i table names file

       -P parallel threads

       -h show help
"

while test $# -ne 0; do
  case $1 in
    -s) dsn=$2;shift;;

    -u) ora_user=$2;shift;;

    -w) ora_pw=$2;shift;;

    -b) output=$2;shift;;
   
    -n) schema=$2;shift;;

    -i) tables_file=$2;shift;;

    -P) parallel=$2;shift;;

    -h) echo "$usage";exit $?;;

    *) echo "$usage";exit $?;;
  esac
  shift
done

if test -z "$tables_file"||test -z "$parallel"||test -z "$output"||test -z "$dsn"||test -z "$ora_user"||test -z "$ora_pw"||test -z "$schema";then
  echo "$usage"
  exit 1
fi

echo "$tables_file,$parallel,$dsn,$ora_user,$ora_pw" > $LOGDIR/ora2pg_dump.log
echo "$(date)" >> $LOGDIR/ora2pg_dump.log

GROUP=800   #tables per call to ora2pg
table_list=''
list_size=0
code=0
for line in $(cat $tables_file)
do
    if test -n "$table_list";then
        table_list+=','
        table_list+=$line
    else
        table_list+=$line
    fi

    list_size=$(expr $list_size + 1)
    if test $list_size -eq $GROUP;then
        code=$(expr $code + 1)
        ora2pg -c ora2pg.conf --count_rows -s "$dsn" -u "$ora_user" -w "$ora_pw" -a "TABLE[$table_list]" -o $(printf %06d $code).sql -b $output -n $schema -P $parallel
        list_size=0
        table_list=''
    fi
done

# last call to ora2pg
if test $list_size -lt $GROUP && test -n "$table_list";then
    code=$(expr $code + 1)
    ora2pg -c ora2pg.conf --count_rows -s "$dsn" -u "$ora_user" -w "$ora_pw" -a "TABLE[$table_list]" -o $(printf %06d $code).sql -b $output -n $schema -P $parallel
fi

echo "$(date)" >> $LOGDIR/ora2pg_dump.log

导出结果示例：
数据导出完成后，使用脚本ora2pg_check_dump.sh检查导出结果：
ora2pg_check_dump.sh -d output
ora2pg_check_dump.sh文件内容如下：

#!/bin/sh

tables_file=
data_dir=

usage="\
Usage:
      $(basename $0) -d dump_data_dir

Options:

       -d <dump data dir>

       -h show help
"

while test $# -ne 0; do
  case $1 in
    -d) data_dir=$2;shift;;

    -h) echo "$usage";exit $?;;

    *) echo "$usage";exit $?;;
  esac
  shift
done

if test -z "$data_dir";then
  echo "$usage"
  exit 1
fi


for f in $(find $data_dir -name "000*.sql"|xargs awk '/\\i /'|awk '{print $2}');do
  if test ! -f $f;then
    ls $f
  fi
done

它检查有没导出失败的表，失败则打印，否则没有任何打印：
可再次执行ora2pg_dump.sh以失败的表名为输入。
实测可能存在导出失败的表，是因为Oracle的CLOB、BLOB字段太大，报错：

ORA-24345: A Truncation or null fetch error occurred (DBD SUCCESS_WITH_INFO: OCIStmtFetch, LongReadLen too small and/or LongTruncOk not set)

?解决方法：
查看出错的表，确定CLOB或BLOB字段数据量最大值，例如：SELECT MAX(DBMS_LOB.GETLENGTH(ERR_DETAIL)) FROM EE_EXEC_JOB_ERR;然后修改ora2pg.conf里的LONGREADLEN选项，将它加大，计算所有记录每个字段的最大值，将这些最大值加起来就等于LONGREADLEN。
注意不要盲目扩大LONGREADLEN，LONGREADLEN越大导出速度越慢。

PG导入数据：

每个表的数据对应一个sql文件，使用generate_import_scripts.sh为psql生成总的导入脚本：
generate_import_scripts.sh -b /data3/pg_migrate/dump -s 800
-b参数是数据文件所在目录，也是生成导入脚本的目录，-s指定每多少个表生成一个导入脚本，这样导入时可以多个psql并行导入，下面的例子是800个表生成一个导入脚本，就是把所有表的导入放到一个脚本里，用一个psql会话导入所有表。
session_000001.sql 将每个sql文件包含在 BEGIN 和 COMMIT 语句块中。用psql运行session_000001.sql，这样，一个表的导入就是一个原子的事务，如果失败，查找原因只需重新导入失败的表。
psql -v ON_ERROR_STOP=1 -p1921 -h10.33.249.91 -Upaasapp -d paas -f?session_000001.sql
-v ON_ERROR_STOP=1表示遇到错误就退出，错误后面的语句将不被执行。
实测700个表24G数据用一个psql会话导入用时1小时。

为了加快PG大数据量的一次性导入，先不创建表的主键、索引、外键、约束，待全量数据导入完后再创建。