欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle数据库迁移到AntDB和PostgreSQL

程序员文章站 2022-03-14 14:24:21
很多时候我们会涉及到数据库备份与迁移,本章节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...

很多时候我们会涉及到数据库备份与迁移,本章节将介绍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