一 环境准备
Oracle环境
a) IP:192.168.1.113，管理员用户 ：sys，system，SID：prod
b) 普通用户scott，密码 scott
c) Oralce 源数据库分析
PPAS环境（Linux ）
a) 安装PPAS(IP:192.168.1.116)
[root@test06 PPAS9.3GA]#tar zxvf ppasmeta-9.3.1.3-linux-x64.tar.gz
[root@test06 PPAS9.3GA]# cd ppasmeta-9.3.1.3-linux-x64
[root@test06 ppasmeta-9.3.1.3-linux-x64]# setenforce Permissive
[root@test06 ppasmeta-9.3.1.3-linux-x64]# ./ppasmeta-9.3.1.3-linux-x64.run --mode text
Language Selection
Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] :
----------------------------------------------------------------------------
Welcome to the Postgres Plus Advanced Server Setup Wizard.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Please specify the directory where Postgres Plus Advanced Server will be installed.
Installation Directory [/opt/PostgresPlus/9.3AS]:
----------------------------------------------------------------------------
Select the components you want to install.
Database Server [Y/n] :Y
Connectors [Y/n] :Y
Infinite Cache [Y/n] :Y
Migration Toolkit [Y/n] :Y
Postgres Enterprise Manager Client [Y/n] :Y
pgpool-II [Y/n] :Y
EDB*Plus [Y/n] :Y
Slony Replication [Y/n] :Y
PgBouncer [Y/n] :Y
Is the selection above correct? [Y/n]: Y
----------------------------------------------------------------------------
Additional Directories
Please select a directory under which to store your data.
Data Directory [/opt/PostgresPlus/9.3AS/data]:
Please select a directory under which to store your Write-Ahead Logs.
Write-Ahead Log (WAL) Directory [/opt/PostgresPlus/9.3AS/data/pg_xlog]:
----------------------------------------------------------------------------
Configuration Mode
Postgres Plus Advanced Server always installs with Oracle(R) compatibility features and maintains full PostgreSQL compliance. Select your style preference for installation defaults and samples.
The Oracle configuration will cause the use of certain objects (e.g. DATE data types, string operations, etc.) to produce Oracle compatible results, create the same Oracle sample tables, and have the database match Oracle examples used in the documentation.
Configuration Mode
[1] Oracle Compatible
[2] PostgreSQL Compatible
Please choose an option [1] :
----------------------------------------------------------------------------
……
----------------------------------------------------------------------------
Pre Installation Summary
Following settings will be used for installation:
Installation Directory: /opt/PostgresPlus/9.3AS
Data Directory: /opt/PostgresPlus/9.3AS/data
WAL Directory: /opt/PostgresPlus/9.3AS/data/pg_xlog
Database Port: 5444
Database Superuser: enterprisedb
Operating System Account: enterprisedb
Database Service: ppas-9.3
PgBouncer Listening Port: 6432
Press [Enter] to continue :
----------------------------------------------------------------------------
Setup is now ready to begin installing Postgres Plus Advanced Server on your computer.
Do you want to continue? [Y/n]: Y
----------------------------------------------------------------------------
Please wait while Setup installs Postgres Plus Advanced Server on your computer.
Installing Postgres Plus Advanced Server
0% ______________ 50% ______________ 100%
#########################################
Installing Database Server ...
Installing pgAgent ...
Installing Connectors ...
Installing Migration Toolkit ...
Installing EDB*Plus ...
Installing Infinite Cache ...
Installing Postgres Enterprise Manager Client ...
Installing Slony Replication ...
Installing pgpool-II ...
Installing PgBouncer ...
Installing StackBuilder Plus ...
----------------------------------------------------------------------------
Setup has finished installing Postgres Plus Advanced Server on your computer.
[root@test06 ppasmeta-9.3.1.3-linux-x64]# ps hf -u enterprisedb -o cmd
/opt/PostgresPlus/9.3AS/bin/pgbouncer -d /opt/PostgresPlus/9.3AS/share/pgbouncer.ini
/opt/PostgresPlus/9.3AS/bin/pgagent -l 1 -s /var/log/ppas-agent-9.3.log hostaddr=localhost port=5444 dbname=edb user=enterprisedb
/opt/PostgresPlus/9.3AS/bin/edb-postgres -D /opt/PostgresPlus/9.3AS/data
\_ postgres: logger process
\_ postgres: checkpointer process
\_ postgres: writer process
\_ postgres: wal writer process
\_ postgres: autovacuum launcher process
\_ postgres: stats collector process
\_ postgres: enterprisedb edb ::1[36172] idle b) PPAS pg_hba.conf文件基础配置
-bash-4.1$ vim /opt/PostgresPlus/9.3AS/data/pg_hba.conf
host all all 0.0.0.0/0 md5c) 重新启动PPAS服务
[root@test06 data]# /etc/init.d/ppas-9.3 restart
Restarting Postgres Plus Advanced Server 9.3:
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
Postgres Plus Advanced Server 9.3 restarted successfullyd) Oracle Client安装
[root@test06 ppasmeta-9.3.1.3-linux-x64]# rpm -ql oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64
package oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64 is not installed
[root@test06 oralceclient]# ls
oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.rpm
[root@test06 oralceclient]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]
[root@test06 oralceclient]# rpm -ql oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64
/usr/lib/oracle/11.2/client64/bin/adrci
/usr/lib/oracle/11.2/client64/bin/genezi
/usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
/usr/lib/oracle/11.2/client64/lib/libnnz11.so
/usr/lib/oracle/11.2/client64/lib/libocci.so.11.1
/usr/lib/oracle/11.2/client64/lib/libociei.so
/usr/lib/oracle/11.2/client64/lib/libocijdbc11.so
/usr/lib/oracle/11.2/client64/lib/ojdbc5.jar
/usr/lib/oracle/11.2/client64/lib/ojdbc6.jar
/usr/lib/oracle/11.2/client64/lib/xstreams.jare) JDK确认（1.6以上）
[root@test06 ppasmeta-9.3.1.3-linux-x64]# rpm -qa|grep java
java-1.7.0-openjdk-devel-1.7.0.45-2.4.3.3.el6.x86_64
java-1.7.0-openjdk-1.7.0.45-2.4.3.3.el6.x86_64
tzdata-java-2013g-1.el6.noarch
gcc-java-4.4.7-4.el6.x86_64
java_cup-0.10k-5.el6.x86_64
java-1.5.0-gcj-1.5.0.0-29.1.el6.x86_64
java-1.6.0-openjdk-1.6.0.0-1.66.1.13.0.el6.x86_64
libvirt-java-0.4.9-1.el6.noarch
java-1.6.0-openjdk-devel-1.6.0.0-1.66.1.13.0.el6.x86_64
libvirt-java-devel-0.4.9-1.el6.noarch
[root@test06 ppasmeta-9.3.1.3-linux-x64]# which java
/usr/bin/java
[root@test06 ppasmeta-9.3.1.3-linux-x64]# ll /usr/bin/java
lrwxrwxrwx. 1 root root 22 Jun 13 2016 /usr/bin/java -> /etc/alternatives/javaf) JDBC配置
[root@test06 ext]# cp /usr/lib/oracle/11.2/client64/lib/ojdbc6.jar /usr/lib/jvm/jre-1.7.0/lib/ext/
[root@test06 ext]# ls
dnsns.jar localedata.jar ojdbc6.jar sunjce_provider.jar zipfs.jar
gnome-java-bridge.jar meta-index pulse-java.jar sunpkcs11.jar二、迁移测试
在PPAS中建立测试用户及数据库
[root@test06 ppasmeta-9.3.1.3-linux-x64]# su - enterprisedb
-bash-4.1$ -bash-4.1$ psql
psql.bin (9.3.1.3)
Type "help" for help.
edb=# create user migration password 'migration' createrole createdb login;
CREATE ROLE
edb=# create database migration_test owner migration;
CREATE DATABASEMTK配置文件toolkit.properties
/opt/PostgresPlus/9.3AS/etc/toolkit.properties
[root@test06 etc]# vim toolkit.properties
SRC_DB_URL=jdbc:oracle:thin:@192.168.1.113:1521:prod
SRC_DB_USER=scott
SRC_DB_PASSWORD=scott
TARGET_DB_URL=jdbc:edb://localhost:5444/mgt_testdb
TARGET_DB_USER=mgt_test
TARGET_DB_PASSWORD=mgt_test
/opt/PostgresPlus/9.3AS/bin/runMTK.sh创建测试用户及测试数据库
-bash-4.1$ psql
psql.bin (9.3.1.3)
Type "help" for help.
edb=# create user mgt_test password 'mgt_test' createrole createuser createdb login;
CREATE ROLE
edb=# create database mgt_testdb owner mgt_test;
CREATE DATABASE运行/runMTK.sh进行数据迁移
[root@test06 etc]# cd /opt/PostgresPlus/9.3AS/bin/
[root@test06 bin]# ./runMTK.sh scott
Source database connectivity info...
conn =jdbc:oracle:thin:@192.168.1.113:1521:prod
user =scott
password=******
Target database connectivity info...
conn =jdbc:edb://localhost:5444/mgt_testdb
user =mgt_test
password=******
Connecting with source Oracle database server...
Connecting with target EnterpriseDB database server...
Importing redwood schema scott...
Creating Schema...scott
Creating Tables...
Creating Table: BONUS
Creating Table: DEPT
Creating Table: EMP
Creating Table: SALGRADE
Created 4 tables.
Loading Table Data in 8 MB batches...
Loading Table: BONUS ...
[BONUS] Table Data Load Summary: Total Time(s): 0.039 Total Rows: 0
Loading Table: DEPT ...
[DEPT] Migrated 4 rows.
[DEPT] Table Data Load Summary: Total Time(s): 0.01 Total Rows: 4
Loading Table: EMP ...
[EMP] Migrated 14 rows.
[EMP] Table Data Load Summary: Total Time(s): 0.049 Total Rows: 14
Loading Table: SALGRADE ...
[SALGRADE] Migrated 5 rows.
[SALGRADE] Table Data Load Summary: Total Time(s): 0.008 Total Rows: 5
Data Load Summary: Total Time (sec): 0.433 Total Rows: 23 Total Size(MB): 0.0
Creating Constraint: PK_DEPT
Creating Constraint: PK_EMP
Creating Constraint: FK_DEPTNO
Schema scott imported successfully.
Creating User: SCOTT
Migration process completed successfully.
Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs
******************** Migration Summary ********************
Tables: 4 out of 4
Constraints: 3 out of 3
Users: 1 out of 1
Total objects: 8
Successful count: 8
Failure count: 0
*************************************************************查看迁移日志
[root@test06 ~]# ll /root/.enterprisedb/migration-toolkit/logs/*
-rw-r--r--. 1 root root 8059 Jun 14 09:41 /root/.enterprisedb/migration-toolkit/logs/mtk_20160614094135.log
-rw-r--r--. 1 root root 2652 Jun 14 10:00 /root/.enterprisedb/migration-toolkit/logs/mtk_20160614095957.logPPAS数据验证
-bash-4.1$ psql -h localhost -U mgt_test -d mgt_testdb
Password for user mgt_test:
psql.bin (9.3.1.3)
Type "help" for help.
mgt_testdb=# \d scott.*
Table "scott.bonus"
Column | Type | Modifiers
--------+-----------------------+-----------
ename | character varying(10) |
job | character varying(9) |
sal | numeric |
comm | numeric |
Table "scott.dept"
Column | Type | Modifiers
--------+-----------------------+-----------
deptno | numeric(2,0) | not null
dname | character varying(14) |
loc | character varying(13) |
Indexes:
"pk_dept" PRIMARY KEY, btree (deptno)
Referenced by:
TABLE "scott.emp" CONSTRAINT "fk_deptno" FOREIGN KEY (deptno) REFERENCES scott.dept(deptno) MATCH FULL
Table "scott.emp"
Column | Type | Modifiers
----------+-----------------------------+-----------
empno | numeric(4,0) | not null
ename | character varying(10) |
job | character varying(9) |
mgr | numeric(4,0) |
hiredate | timestamp without time zone |
sal | numeric(7,2) |
comm | numeric(7,2) |
deptno | numeric(2,0) |
Indexes:
"pk_emp" PRIMARY KEY, btree (empno)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES scott.dept(deptno) MATCH FULL
Index "scott.pk_dept"
Column | Type | Definition
--------+--------------+------------
deptno | numeric(2,0) | deptno
primary key, btree, for table "scott.dept"
Index "scott.pk_emp"
Column | Type | Definition
--------+--------------+------------
empno | numeric(4,0) | empno
primary key, btree, for table "scott.emp"
Table "scott.salgrade"
Column | Type | Modifiers
--------+---------+-----------
grade | numeric |
losal | numeric |
hisal | numeric |
mgt_testdb=# select * from scott.emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
(14 rows)Oracle端数据
SQL> select segment_name from dba_segments where owner='SCOTT';
SEGMENT_NAME
--------------------------------------------------------------------------------
PK_EMP
PK_DEPT
SALGRADE
EMP
DEPT
SQL> conn scott/scott
Connected.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12?-80 800
20
7499 ALLEN SALESMAN 7698 20-2? -81 1600 300
30
7521 WARD SALESMAN 7698 22-2? -81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4? -81 2975
20
7654 MARTIN SALESMAN 7698 28-9? -81 1250 1400
30
7698 BLAKE MANAGER 7839 01-5? -81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6? -81 2450
10
7788 SCOTT ANALYST 7566 19-4? -87 3000
20
7839 KING PRESIDENT 17-11?-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9? -81 1500 0
30
7876 ADAMS CLERK 7788 23-5? -87 1100
20
7900 JAMES CLERK 7698 03-12?-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12?-81 3000
20
7934 MILLER CLERK 7782 23-1? -82 1300
10
14 rows selected.