《卸甲笔记》-Oracle线下迁移到PPAS
2023-09-14 09:02:12 时间
1、尽量保持Oracle与PPAS一致,这会使得日后应用程序迁移更为简单
2、迁移前检查PPAS中是否有同名帐号及同名的Schema
a)如果有,建议考虑删除或改名 b)如果没有,先手工建立与Oracle同名的帐号及Schema
3、用MTK迁移时,配置文件中目标数据库建议使用与Oracle同名的帐号(如本案例中使用scott),这样可以保持目标数据库中所有表和对象的主属用户都和Oracle中一致
一 环境准备Oracle环境(OS:Linux)
a) IP:192.168.1.113,管理员用户 :sys,system,SID:prod
b) 普通用户scott,密码 scott
c) Oralce 源数据库分析
PPAS环境(OS:Linux,IP:192.168.1.116)
a) 安装PPAS
[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$ psql psql.bin (9.3.1.3) Type "help" for help. edb=# create user scott password scott createrole createuser createdb login; CREATE ROLE edb=# create database scott owner scott; 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/scott TARGET_DB_USER=scott TARGET_DB_PASSWORD=scott
运行/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/scott user =scott 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.04 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.371 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 Error Creating User SCOTT (由于已经在目标端数据库创建了一个名为scott的用户,所以重新创建同名Scott用户时出错,如果目标端没有scott用户则不会出现错误。) One or more schema objects could not be imported during the migration process. Please review the migration output for more details. Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs ******************** Migration Summary ******************** Tables: 4 out of 4 Constraints: 3 out of 3 Users: 0 out of 1 Total objects: 8 Successful count: 7 Failure count: 1 List of failed objects ====================== Users -------------------- 1. SCOTT *************************************************************
查看迁移日志
[root@test06 etc]# ll /root/.enterprisedb/migration-toolkit/logs/* -rw-r--r--. 1 root root 2229 Jun 15 11:13 /root/.enterprisedb/migration-toolkit/logs/mtk_20160615111238.log
PPAS数据验证
-bash-4.1$ psql -h localhost -p 5444 -U scott -d scott Password for user scott: psql.bin (9.3.1.3) Type "help" for help. scott=# \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 "emp" CONSTRAINT "fk_deptno" FOREIGN KEY (deptno) REFERENCES 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 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 | scott=# 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 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 7499 ALLEN SALESMAN 7698 20-2? -81 1600 300 7521 WARD SALESMAN 7698 22-2? -81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4? -81 2975 7654 MARTIN SALESMAN 7698 28-9? -81 1250 1400 7698 BLAKE MANAGER 7839 01-5? -81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-6? -81 2450 7788 SCOTT ANALYST 7566 19-4? -87 3000 7839 KING PRESIDENT 17-11?-81 5000
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-9? -81 1500 0 7876 ADAMS CLERK 7788 23-5? -87 1100 7900 JAMES CLERK 7698 03-12?-81 950
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-12?-81 3000 7934 MILLER CLERK 7782 23-1? -82 1300
相关文章
- 数据迁移完美迁移:MySQL数据到Oracle的挑战。(mysql到oracle)
- 简易搞定:Oracle数据库迁移工具指南(oracle数据库迁移工具)
- Oracle表数据去重实现方法(oracle表去重)
- Oracle中描述表字段的实践步骤(oracle描述表字段)
- 解锁Oracle数据库:一种有效的方式(oracle锁定解锁)
- Oracle SQL中的全文索引实现(oracle全文索引)
- Oracle三思笔记:解读复杂系统的智慧(oracle三思笔记)
- 实现Oracle数据文件高效迁移(oracle数据文件迁移)
- 效率Oracle索引:提高查询时间效率(oracle索引时间)
- Oracle技巧:学会向下取整,避免数据精度丢失的问题(oracle向下取整)
- 从Mysql到Oracle:如何成功迁移数据库(mysql迁移oracle)
- 构建Oracle数据库中的关联视图(oracle 关联视图)
- 精通Oracle系统中的全部分析函数(oracle全部分析函数)
- 实现Oracle数据库下NC账套的迁移(nc账套迁移oracle)
- 无缝迁移从32位到64位Oracle上的cxoracle(64位cx_oracle)
- Oracle用主键关联索引的运用(oracle 主键关键字)
- 重新设定Oracle主机密码(oracle主机密码忘了)
- Oracle中的死锁排除之道(oracle中的死锁)
- Oracle RAC迁移指导安全高效切换至新环境(oracle rac迁移)
- Oracle AWWR性能报告分析(oracle awwr)
- oracle数据库数据迁移解决方案