Oracle数据库迁移到AntDB和PostgreSQL

很多时候我们会涉及到数据库备份与迁移，本章节将介绍Oracle数据库如何迁移至PostgreSQL和AntDB数据库。以下主要介绍以命令的方式迁移。

一、登录oracle数据库

sqlplus 登录:

sqlplus username/'password'@10.19.82.196/testdb

二、Oracle侧对象统计：

select OBJECT_TYPE,count(*) from user_objects group by OBJECT_TYPE order by 2;

OBJECT_TYPE COUNT(*)

---

TYPE 3
TRIGGER 8
FUNCTION 14
PROCEDURE 24
LOB 152
VIEW 167
SEQUENCE 173
TABLE 335
INDEX 338

查看表大小：

select sum(b.bytes)/1024/1024/1024 as table_size
from user_tables a ,user_segments b
where 1=1
and a.table_name=b.segment_name;

TABLE_SIZE：
.11730957

三、导出表、分区、序列和视图对象

cat > /home/oracle/mvtemp/conf/test_ddl.conf  << EOF
ORACLE_HOME /data/oracle/db 
ORACLE_DSN  dbi:Oracle:host=10.19.82.196;service_name=testdb
ORACLE_USER testdb
ORACLE_PWD  testdb123  
SCHEMA  testdb
TYPE TABLE,PARTITION,SEQUENCE,VIEW
USER_GRANTS 1
DISABLE_SEQUENCE 1
INDEX_RENAMING 0
STOP_ON_ERROR 0
DATA_TYPE NUMBER,DATE:oracle.date
EOF

执行 ora2pg 可以通过 ora2pg --help 获取命令行帮助信息。

// 导出表、分区、序列和视图对象
nohup ora2pg -c /home/oracle/mvtemp/conf/test_ddl.conf  -o test_ddl.sql -d  > /home/oracle/mvtemp/test_ddl.log 2>&1 & 

检查对象：

grep "CREATE TABLE"  TABLE_test_ddl.sql|wc -l
grep "PARTITION OF"  PARTITION_test_ddl.sql |wc -l
grep "CREATE SEQUENCE" SEQUENCE_test_ddl.sql |wc -l
grep "CREATE OR REPLACE VIEW " VIEW_test_ddl.sql |wc -l

四、导入对象到PostgreSQL或AntDB

在部署PostgreSQL或AntDB数据库的主机上执行如下命令。

psql -p 8432 -d testdb -U testdb -f /home/testdb /ora2pg/TABLE_test_ddl.sql
psql -p 8432 -d testdb -U testdb -f /home/testdb /ora2pg/PARTITION_test_ddl.sql
psql -p 8432 -d testdb -U testdb -f /home/testdb /ora2pg/SEQUENCE_test_ddl.sql

五、导出导入存储过程和函数

导出Oracle原始的存储过程和函数定义，在PostgreSQL或AntDB中直接创建。

#!/bin/bash 
# https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#BGBJBFGE
# https://*.com/questions/22018804/oracle-sql-developer-4-0-how-to-remove-double-quotes-in-scripts
# https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534496000346133031
# https://blog.csdn.net/itmyhome1990/article/details/50380718

log_print()
{
    echo "--"`date "+%Y-%m-%d %H:%M:%S"` -- $1
}


get_object_ddl ()
{
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
sqlplus -silent /nolog <<EOF > /dev/null 2>${logfile}
conn ${ora_conn}
set echo off;
set heading off;
set feedback off;
set verify off;
set trimspool on;
set long 90000000;
col ddl_sql for a999;
set pagesize 0;
set linesize 20000;
set serveroutput off;
set longchunksize 20000;
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false); 
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false); 
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'FORCE', false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EMIT_SCHEMA',false);
spool $outfile
select 
case when object_type in ('FUNCTION','PROCEDURE') then REGEXP_REPLACE(dbms_metadata.get_ddl(object_type,object_name) ,'${object_type}[[:space:]]? "(\w+?)"','${object_type} \1')
else
   replace(dbms_metadata.get_ddl(object_type,object_name),'"')
end
from  user_objects
where object_type=upper('$object_type');
spool off
EOF
}

check_type ()
{
object_type_lower=`echo $object_type |tr '[A-Z]' '[a-z]'`
object_type=`echo $object_type |tr '[a-z]' '[A-Z]'`
outfile=orcl_${username}_${object_type_lower}.sql
t_cnt=`sqlplus -S /nolog <<EOF
set heading off feedback off pagesize 0 verify off echo off
conn ${ora_conn}
select  count(*) 
from user_objects
where object_type=upper('$object_type');
exit
EOF`
if [ $t_cnt -eq 0 ]; then
    log_print "the count of $object_type in $username  is: $t_cnt,export process will continue to next type!"  
elif  [ $t_cnt -gt 0 ];then
    log_print "the count of $object_type in $username  is: $t_cnt,export file is ${outfile}!"  
    get_object_ddl
fi
}


get_all_objects ()
{
for object_type in ${object_types[@]}
do 
  check_type
done
}


check_ora_conn()
{
sqlplus -silent /nolog <<EOF  |grep antdb
conn ${ora_conn}
select 'antdb' from dual;
EOF
if [ $? -eq 0 ];then
   log_print 'source oracle connect is ok !'
else
   log_print 'source oracle connect not ok ,please check!'
   exit 1
fi
}

orahost=$1
#orasid='orcl'
orasid=$2
oraport=1521
username=$3
password=$4
object_type=$5
object_types=(PROCEDURE function view)

ora_conn=${username}/${password}@${orahost}:${oraport}/${orasid}

logfile=ora_object_data_${username}.log

check_ora_conn

if [ "x$object_type" == "x" ];then
   log_print "because you can not input object type,so export ${object_types[@]}   default "
   get_all_objects
else
   check_type
fi

导出：

// sh get_ora_ddl.sh 你的IP 你的数据库名称  你的账号  你的密码
demo: sh get_ora_ddl.sh 127.0.0.1 testdb testdb testdb123

导出的文件为： orcl_eom_srv_procedure.sql
导入：

// 导入存储过程和函数
psql -p 8432 -d testdb -U testdb -f /home/testdb/ora2pg/orcl_eom_srv_procedure.sql;

修改文件

在文件头添加：

set grammar to oracle;
\set PLSQL_MODE on

在文件尾部添加：

\set PLSQL_MODE off

六、迁移数据

可以通过不落地的方式，将数据从Oracle迁移到AntDB。

编写配置文件

cat > /home/testdb/ora2pg/conf/testdb_data.conf << EOF
ORACLE_HOME /data/oracle/db 
ORACLE_DSN  dbi:Oracle:host=10.19.82.196;service_name=testdb
ORACLE_USER testdb
ORACLE_PWD  testdb123  
SCHEMA  testdb
TYPE COPY 
OUTPUT_DIR  /home/testdb/ora2pg/data/
USER_GRANTS 1
FILE_PER_TABLE 1
STOP_ON_ERROR 0
DATA_LIMIT  100000
SPLIT_FILE 1
SPLIT_LIMIT  10000000
DROP_FKEY 1
TRUNCATE_TABLE 1
PG_DSN dbi:Pg:dbname=testdb;host=10.19.82.198;port=8432
PG_USER testdb
PG_PWD  testdb123  
EOF

执行迁移操作：

ora2pg -c /home/testdb/ora2pg/conf/testdb_data.conf  -d

本文地址：https://blog.csdn.net/qq_36735986/article/details/107669919