一、环境配置
1.1、环境规划
1.2、oracle测试数据
1.2.1 查看hr 下对象及对象类型
SQL> SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='HR';
TABLE_NAME NUM_ROWS
---------------------------------------- ----------
REGIONS 4
COUNTRIES 25
LOCATIONS 23
DEPARTMENTS 27
JOBS 19
EMPLOYEES 107
JOB_HISTORY 10
7 rows selected.
SQL> select object_type,count(*) from dba_objects where owner='HR' group by object_type;
OBJECT_TYPE COUNT(*)
----------------------- ----------
INDEX PARTITION 4
SEQUENCE 3
TABLE PARTITION 4
PROCEDURE 2
TRIGGER 2
INDEX 19
TABLE 7
VIEW 1
8 rows selected.
.
SQL> select sum(bytes)/1024/1024 from dba_segments where owner='HR';
SUM(BYTES)/1024/1024
--------------------
1.9375
1.2.2 创建存储过程
--用户授权
conn / as sysdba
GRANT dba to hr;
GRANT CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY PROCEDURE TO hr;
GRANT ALTER ANY TABLE, ALTER ANY PROCEDURE TO hr;
GRANT DROP ANY TABLE, DROP ANY VIEW, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO hr;
CREATE OR REPLACE PROCEDURE sp_generate_report_and_diff_and_highest_paid_emp
AS
-- 定义游标变量
CURSOR c_emp IS
SELECT e.first_name,
e.last_name,
e.job_id,
d.department_name,
e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
BEGIN
-- 打印表头信息
DBMS_OUTPUT.PUT_LINE('EMPLOYEE REPORT');
DBMS_OUTPUT.PUT_LINE('----------------');
-- 创建员工报告表
EXECUTE IMMEDIATE 'CREATE TABLE emp_report AS
SELECT emp.first_name,
emp.last_name,
emp.job_id,
dept.department_name,
emp.salary
FROM employees emp
INNER JOIN departments dept ON emp.department_id = dept.department_id';
-- 创建员工薪资差异表
EXECUTE IMMEDIATE 'CREATE TABLE emp_salary_diff AS
SELECT emp.employee_id,
emp.first_name,
emp.last_name,
emp.job_id,
emp.salary,
(emp.salary - AVG(emp.salary) OVER(PARTITION BY emp.job_id)) AS salary_diff
FROM employees emp';
-- 创建薪水最高员工表
EXECUTE IMMEDIATE 'CREATE TABLE highest_paid_emp AS
SELECT emp.employee_id,
emp.first_name,
emp.last_name,
emp.job_id,
dept.department_name,
emp.salary
FROM employees emp
INNER JOIN departments dept ON emp.department_id = dept.department_id
WHERE emp.salary = (SELECT MAX(salary) FROM employees)';
-- 打印表尾信息
DBMS_OUTPUT.PUT_LINE('----------------');
DBMS_OUTPUT.PUT_LINE('END OF REPORT');
END;
1.2.3 执行存储过程
SQL> exec sp_generate_report_and_diff_and_highest_paid_emp
1.2.4 查看hr下对象及对象类型（执行完存储过程）
SQL> SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='HR';
TABLE_NAME NUM_ROWS
---------------------------------------- ----------
REGIONS 4
COUNTRIES 25
LOCATIONS 23
DEPARTMENTS 27
JOBS 19
EMPLOYEES 107
JOB_HISTORY 10
EMP_REPORT 106
EMP_SALARY_DIFF 107
HIGHEST_PAID_EMP 1
10 rows selected.
SQL> select object_type,count(*) from dba_objects where owner='HR' group by object_type;
OBJECT_TYPE COUNT(*)
----------------------- ----------
SEQUENCE 3
PROCEDURE 3
TRIGGER 2
INDEX 19
TABLE 10
VIEW 1
6 rows selected.
SQL> select sum(bytes)/1024/1024 from dba_segments where owner='HR';
SUM(BYTES)/1024/1024
--------------------
1.75
二、使用ora2pg迁移Oracle 12.2数据到PG14.6
2.1、安装Ora2Pg
为使安装能够顺利通过，在安装 Ora2Pg 之前必须先确保系统已经安装了 Perl 模块以及 DBI、DBD::Oracle 模块。若需要直接导入到 PostgreSQL 则还需要安装 DBD::Pg 模块。
## 在编译安装了PG的机器上安装ora2pg（本测试oracle和pg在同一台机器Tencent上）
2.1.1 安装依赖包
perl版本5.10以上
[root@tencent ~]# yum install -y gcc perl-DBD-Pg perl perl-devel perl-DBI perl-CPAN bzip2 \
perl-ExtUtils-eBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-tests perf cpan
2.1.2 安装DBI
官网下载地址：https://metacpan.org/release/DBI
[root@tencent ~]# tar -zxvf DBI-1.643.tar.gz
[root@tencent ~]# cd DBI-1.643
[root@tencent DBI-1.643]# perl Makefile.PL
[root@tencent DBI-1.643]#make && make install
2.1.3 安装DBD::Oracle
下载地址：https://metacpan.org/pod/DBD::Oracle
[root@tencent ~]# tar -zxvf DBD-Oracle-1.83.tar.gz
[root@tencent ~]# cd DBD-Oracle-1.83
[root@tencent DBD-Oracle-1.83]# perl Makefile.PL
[root@tencent DBD-Oracle-1.83]# make && make install
#需要先配置root环境变量，添加oracle的信息
--配置root 环境变量，添加oracle的信息
cat >> /root/.bash_profile << "EOF"
export ORACLE_HOME=/oracle/app/oracle/product/12.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
EOF
source /root/.bash_profile
2.1.4 安装ora2pg
下载地址: https://sourceforge.net/projects/ora2pg/
[root@tencent ~]#tar -zxvf ora2pg-23.2.tar.gz
[root@tencent ~]# cd ora2pg-23.2/
[root@tencent ora2pg-23.2]# perl Makefile.PL
[root@tencent ora2pg-23.2]# make && make install
--查看ora2pg安装情况
[root@tencent ~]# which ora2pg
/usr/local/bin/ora2pg
[root@tencent tmp]# ora2pg –version
Ora2Pg v23.2
2.1.5 安装DBD::Pg（可选）
如果想直接将ora2pg导出的数据直接导入pg数据库，不生成本地文件，则可以安装该插件并配置使用
下载地址：http://www.cpan.org/authors/id/T/TU/TURNSTEP/
[root@tencent ~]# tar zxvf DBD-Pg-3.16.3.tar.gz
[root@tencent ~]# cd DBD-Pg-3.16.3
[root@tencent DBD-Pg-3.16.3]# perl Makefile.PL
Path to pg_config? 输入/postgresql/pg14/bin/pg_config
[root@tencent DBD-Pg-3.16.3]# make && make install
2.1.6 检查所有软件是否已成功安装
--创建检查脚本
cat > /root/check.pl <<"EOF"
#!/usr/bin/perl
use strict;
use ExtUtils::Installed;
my $inst= ExtUtils::Installed->new();
my @modules = $inst->modules();
foreach(@modules)
{
my $ver = $inst->version($_) || "???";
printf("%-12s -- %s\n", $_, $ver);
}
exit;
EOF
--执行检查
[root@tencent DBD-Pg-3.16.3]# perl /root/check.pl
DBD::Oracle -- 1.83
DBD::Pg -- 3.16.3
DBI -- 1.643
Ora2Pg -- 23.2
2.2、创建ora2pg配置文件
配置参考：https://ora2pg.darold.net/documentation.html#CONFIGURATION
2.2.1 从模版复制一份配置文件示例
默认情况下，Ora2Pg会查找/etc/ora2pg/ora2pg.conf配置文件，如果文件存在，只需执行：/usr/local/bin/ora2pg，也可以通过-c选项指定配置文件路径，如：ora2pg -c ~/ora2pg/config/ora2pg.conf。
[root@tencent ~]# cp /etc/ora2pg/ora2pg.conf.dist /etc/ora2pg/ora2pg.conf
[root@tencent ~]# cat /etc/ora2pg/ora2pg.conf.dist | grep -v ^# | grep -v ^$ | wc -l
124
--编辑/ora2pg/config/ora2pg.conf，设置以下信息 ，可查看oracle版本
# Set Oracle database connection (datasource, user, password)
ORACLE_DSN dbi:Oracle:host= 10.0.4.16;sid=orcl;port=1521
ORACLE_USER system
ORACLE_PWD oracle
[root@tencent ora2pg]# ora2pg -t SHOW_VERSION
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
2.2.2 创建导出表结构的配置文件
-- 表结构、约束（主外键等）、索引等
cat > /etc/ora2pg/ora2pg_table_ddl.conf <<"EOF"
ORACLE_HOME /oracle/app/oracle/product/12.2.0/db_1
ORACLE_DSN dbi:Oracle:host=10.0.4.16;sid=ORCL;port=1521
#ORACLE_DSN dbi:Oracle:tns_ora12c
ORACLE_USER system
ORACLE_PWD oracle
SCHEMA HR
EXPORT_SCHEMA 1
CREATE_SCHEMA 1
TYPE TABLE
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys checks
#SKIP keys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT_DIR
OUTPUT table_ddl_output.sql
PG_VERSION 14
2.3.2 创建导出其它对象的配置文件
cat > /etc/ora2pg/ora2pg_other_ddl.conf <<"EOF"
ORACLE_HOME /oracle/app/oracle/product/12.2.0/db_1
ORACLE_DSN dbi:Oracle:host=10.0.4.16;sid=ORCL;port=1521
#ORACLE_DSN dbi:Oracle:tns_ora12c
ORACLE_USER system
ORACLE_PWD oracle
SCHEMA HR
EXPORT_SCHEMA 1
CREATE_SCHEMA 1
TYPE PACKAGE,PROCEDURE,TRIGGER,FUNCTION,VIEW,GRANT,SEQUENCE,MVIEW,TYPE,SYNONYM
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys
#SKIP keys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT_DIR
OUTPUT other_ddl_output.sql
PG_VERSION 15
2.3.3 创建导出表数据的配置文件
cat > /etc/ora2pg/ora2pg_data.conf <<"EOF"
ORACLE_HOME /oracle/app/oracle/product/12.2.0/db_1
ORACLE_DSN dbi:Oracle:host=10.0.4.16;sid=ORCL;port=1521
#ORACLE_DSN dbi:Oracle:tns_ora12c
ORACLE_USER system
ORACLE_PWD oracle
SCHEMA HR
TYPE INSERT
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys checks
#SKIP fkeys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT_DIR
OUTPUT data_output.sql
PG_VERSION 15
#PG_DSN dbi:Pg:dbname=orcl;host= 10.0.4.16;port=4519
#PG_USER hr
#PG_PWD hr
注意：若去掉PG_DSN、PG_USER和PG_PWD参数的注释，则会将数据直接导入到PG中，而不会导出到中间文件。
导出数据type 可以设置为COPY或者INSERT，本测试设置为INSERT
2.3、使用ora2pg迁移数据
2.3.1 测试连接
[root@tencent ~]# ora2pg -t SHOW_VERSION -c /etc/ora2pg/ora2pg_table_ddl.conf
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
2.3.2 迁移成本评估
[root@tencent ~]# ora2pg -t SHOW_REPORT --estimate_cost -c /etc/ora2pg/ora2pg_table_ddl.conf
2.3.3 导出表结构
[root@tencent ~]# ora2pg -c /etc/ora2pg/ora2pg_table_ddl.conf
[=====================>] 10/10 tables (100.0%) end of scanning.
[=====================>] 10/10 tables (100.0%) end of table export.
-rw-r--r-- 1 root root 8817 Aug 17 11:26 table_ddl_output.sql
#未指定导出目录，sql文件生成在执行命令时当前目录下
2.3.4 导出其他对象结构
[root@tencent ~]# ora2pg -c /etc/ora2pg/ora2pg_other_ddl.conf
[===============>] 0/0 packages (100.0%) end of output.
[===============>] 3/3 procedures (100.0%) end of procedures export.
[===============>] 1/1 triggers (100.0%) end of output.
[===============>] 0/0 functions (100.0%) end of functions export.
[===============>] 1/1 views (100.0%) end of output.
[===============>] 3/3 sequences (100.0%) end of output.
[===============>] 0/0 materialized views (100.0%) end of output.
[===============>] 0/0 types (100.0%) end of output.
[===============>] 0/0 synonyms (100.0%) end of output.
Fixing function calls in output files...
-rw-r--r-- 1 root root 348 Aug 17 11:30 PACKAGE_other_ddl_output.sql
-rw-r--r-- 1 root root 2889 Aug 17 11:30 PROCEDURE_other_ddl_output.sql
-rw-r--r-- 1 root root 796 Aug 17 11:30 TRIGGER_other_ddl_output.sql
-rw-r--r-- 1 root root 349 Aug 17 11:30 FUNCTION_other_ddl_output.sql
-rw-r--r-- 1 root root 1083 Aug 17 11:30 VIEW_other_ddl_output.sql
-rw-r--r-- 1 root root 377 Aug 17 11:30 TYPE_other_ddl_output.sql
-rw-r--r-- 1 root root 348 Aug 17 11:30 SYNONYM_other_ddl_output.sql
-rw-r--r-- 1 root root 585 Aug 17 11:30 SEQUENCE_other_ddl_output.sql
-rw-r--r-- 1 root root 31 Aug 17 11:30 MVIEW_other_ddl_output.sql
-rw-r--r-- 1 root root 348 Aug 17 11:30 GRANT_other_ddl_output.sql
2.3.5 导出表数据
执行导出表数据的时候报错Out of memory!，导出终止。是由于系统内存资源不足，需要调整ora2pg.conf配置文件下DATA_LIMIT参数，默认值是10000，可以减小该值，如果资源充足，可以增加该值。
修改ora2pg.conf不生效的话，直接修改ora2pg_data.conf导出数据配置文件，添加DATA_LIMIT参数，如下
[root@tencent ~]# vi /etc/ora2pg/ora2pg.conf
添加DATA_LIMIT 300
[root@tencent ~]# ora2pg -c /etc/ora2pg/ora2pg_data.conf
-rw-r--r-- 1 root root 70672 Aug 17 12:17 data_output.sql
2.3.6 PG实例创建orcl数据库及HR用户
[postgres@tencent postgresql]$ psql
psql (14.6)
Type "help" for help.
postgres=# create database orcl;
CREATE DATABASE
postgres=# create user hr with password 'hr';
CREATE ROLE
postgres=# alter database orcl owner to hr;
ALTER DATABASE
postgres=# \c orcl hr
You are now connected to database " orcl " as user "hr".
postgres=> create schema hr;
CREATE SCHEMA
orcl=> \dn
List of schemas
Name | Owner
--------+----------
hr | hr
public | postgres
(2 rows)
2.3.7 导入表结构
由于导出的sql文件在/root目录下，postgres用户权限不足，copy所有sql文件到/postgresql/scripts目录下，并更改权限：
[root@tencent ~]# cp /root/*.sql /postgresql/scripts/
[root@tencent ~]# chmod -R 700 /postgresql/scripts/*
[root@tencent ~]# chown -R postgres. /postgresql/scripts/
--导入表结构
[postgres@tencent scripts]$ psql -d orcl -Uhr -f /postgresql/scripts/table_ddl_output.sql
--查看表是否创建成功
注意：由于有物化视图，在TABLE_sh.sql 里包含了物化视图的索引，会创建失败。需先创建表，在创建物化视图，最后创建索引。取消物化视图索引，后面单独创建。
2.3.8 导入其他对象结构
[postgres@tencent scripts]$
psql -d orcl -Uhr -f /postgresql/scripts/PROCEDURE_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/PACKAGE_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/MVIEW_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/GRANT_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/FUNCTION_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/VIEW_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/TYPE_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/TRIGGER_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/SYNONYM_other_ddl_output.sql
psql -d orcl -Uhr -f /postgresql/scripts/SEQUENCE_other_ddl_output.sql
--导入日志如下：
有触发器缺失，所如导入失败，本次忽略。
--查看导入结果
视图：
序列
函数（存储过程）
索引
2.3.9 导入表数据
导入数据的sql默认是最后提交commit，如果前面的事务INSERT失败，后面的都会失败，所以可以在每个insert事务后面添加commit，这样保证每个事务的提交是独立的，即使报错也不影响其他事务数据的提交。编辑data_output.sql文件,为每段insert 添加commit;
--导入数据
[postgres@tencent scripts]$ psql -d orcl -Uhr -f /postgresql/scripts/data_output.sql
表数据如下：
序列数据如下：
2.3.10 导入完成后，PG查看数据
--表、视图、序列总览
--表
orcl=> select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='hr' order by pg_relation_size(relid) desc;
--索引
--序列
--函数
--存储过程
--数据库大小
2169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
