备案控制台
探索云世界
开发者社区 数据库 文章 正文

<<卸甲笔记>>-Oracle线下迁移包PPAS

2016-06-14 2610
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Oracle线下迁移包PPAS

一 环境准备

Oracle环境

a) IP:192.168.1.113，管理员用户 ：sys，system，SID：prod
b) 普通用户scott，密码 scott
c) Oralce 源数据库分析
ali2

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               md5

c) 重新启动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 successfully

d) 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.jar

e) 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/java

f) 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 DATABASE

MTK配置文件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.log

PPAS数据验证

-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.
文章标签：
关系型数据库
Oracle
Java
数据库连接
SQL
关键词：
Oracle数据库一键上云笔记
Oracle数据库一键上云迁移
Oracle数据库一键上云包
Oracle数据库一键上云ppas
卸甲笔记Oracle数据库一键上云迁移ppas
syj
目录
相关文章
穆雄雄.
|
25天前
|
Oracle 关系型数据库
oracle基本笔记整理及案例分析2
oracle基本笔记整理及案例分析2
穆雄雄.
12 0
穆雄雄.
|
25天前
|
Oracle 关系型数据库
oracle基本笔记整理及案例分析1
oracle基本笔记整理及案例分析1
穆雄雄.
16 0
穆雄雄.
|
25天前
|
SQL Oracle 关系型数据库
oracle笔记整理2
oracle笔记整理2
穆雄雄.
11 0
穆雄雄.
|
25天前
|
SQL Oracle 关系型数据库
oracle基本笔记整理
oracle基本笔记整理
穆雄雄.
12 0
q7s2kces74wvy
|
7月前
|
Oracle 关系型数据库 分布式数据库
如何从Oracle迁移到PolarDB(ADAM)（二）
如何从Oracle迁移到PolarDB(ADAM)（二）
q7s2kces74wvy
128 0
南葵思暖
|
8月前
|
SQL Oracle 关系型数据库
Oracle笔记1
Oracle笔记1
南葵思暖
100 0
姚远OracleACE
|
10月前
|
Oracle 关系型数据库 Linux
使用rpm包在Linux上安装Oracle软件和创建数据库
在Linux上安装Oracle软件的过程相当复杂，让很多初学者望而却步。但在Oracle 18c以后Oracle推出了使用rpm包安装Oracle数据库软件的安装方式，安装两个（预安装包和数据库安装包）rpm包即可完成整个Oracle数据库软件的安装。
姚远OracleACE
258 0
使用rpm包在Linux上安装Oracle软件和创建数据库
小小工匠
|
11月前
|
存储
Oracle-AWR管理包DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
Oracle-AWR管理包DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
小小工匠
56 0
小小工匠
|
11月前
|
存储 Oracle 关系型数据库
Oracle优化07-分析及动态采样-DBMS_STATS 包
Oracle优化07-分析及动态采样-DBMS_STATS 包
小小工匠
81 0
Oracle优化07-分析及动态采样-DBMS_STATS 包
小小工匠
|
11月前
|
Oracle 关系型数据库 数据库
Oracle实例迁移_真实场景实操
Oracle实例迁移_真实场景实操
小小工匠
78 0

热门文章

最新文章

  • 1
    Oracle 面试题及答案整理，最新面试题
  • 2
    Seata常见问题之oracle 数据库 报 just support mysql如何解决
  • 3
    PolarDB常见问题之PolarDB(Oracle兼容版) 执行命令报错如何解决
  • 4
    Dataphin实时集成Oracle CDC相关问题排查
  • 5
    【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
  • 6
    【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
  • 7
    【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
  • 8
    struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
  • 9
    oracle基本笔记整理及案例分析1
  • 10
    struts+hibernate+oracle+easyui实现lazyout组件的简单案例——EmpDao层代码
  • 1
    服务器Centos7 静默安装Oracle Database 12.2
    71
  • 2
    SpringBoot整合Mybatis连接Oracle数据库
    52
  • 3
    避坑，Oracle中rownum与order by的执行顺序
    25
  • 4
    避坑，Oracle中left join 与 (+) 的区别
    19
  • 5
    Oracle临时表详解
    17
  • 6
    Oracle insert数据时字符串中有‘单引号问题
    25
  • 7
    【Bug记录】Oracle中小数点前面的0不显示的问题
    37
  • 8
    Oracle中merge Into的用法
    17
  • 9
    Flink CDC产品常见问题之使用cdc-Oracle连接器报错如何解决
    48
  • 10
    Oracle系列十八：Oracle RAC
    47

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 推荐镜像

    更多
  • oracle
  • arch4edu
  • debian-archive
    • 下一篇
    阿里云oss简介和使用流程