Oracle Migration GP Practice

Recent migration practices for Oracle to Greenplum, steps are as follows: 1. The implementation process of implementing Oracle's data structure migration to GP using ORA2PG 2. Oracle's data migration to GP implementation process 1. ORA2PG Address: The https://github.com/darold/ora2p diagram is as follows: The DBD-ORACLE,DBD-PG,DBI module needs to be installed and the data structure of Oracle can be configured after Conf (Table,view, Package, etc.) into the data structure of the PG. You can also configure the Oracle library to be imported directly into the PG. Environmental parameters: OS RHEL6.5 64bitOracle Client 10.2.0.5.0GP The parameters of the 4.2.6.0 module are detailed on the diagram. Installation of the standard Perl installation method: Perl makefile.plmakemake testmake Install describes the configuration file:

1Oracle_home/Home/Oracle/client_12Oracle_dsn Dbi:Oracle:host=192.168.11.1; SID=ORCL3 Oracle_user Manager4 oracle_pwd Tiger5 SCHEMATest6TYPETABLE VIEWPackage COPY7Pg_numeric_type08Pg_integer_type19Default_numericfloatTen SKIP Fkeys pkeys ukeys Indexes checks One Nls_lang American_america. UTF8 APg_dsn Dbi:Pg:dbname=Easyetl;host=127.0.0.1;p ORT=5432 - Pg_user Easyetl - pg_pwd Password theOUTPUT Output.sql

1-4 Configuring the source-side Oracle Information

5 Schema values for Oracle

6 The type of data to be converted, including the copy command for the data guide

7-9 the type of number (P,S) to PG that is used to convert Oracle:

7 indicates whether to use the data type inside the PG, 0 means not to use, and 1 means to use

8 indicates that the definition of type number (p) is changed to integer if the 7 is set to 0 o'clock and if 8 is set to 1, and if 8 is set to 0, then number (p) is also converted to numeric (p)

9 means 8 is set to 1, the type number is converted to float, and 0 does not work if 8 is set to 9.

Simple settings, if 7,8 are set to 0, then number (p)--numeric (p), number (p,s)--Numeric (P,s), number---numeric

10 constraints do you need to create

11 language selection

12-14 Configuration Destination PG (GP also), if this three lines of information is not configured, it does not matter, can generate Oracle converted to PG script

15 Generating files

What happens in the migration:

(1) The table can be completely migrated past

(2) If there is no alias in the view, you also need to add the alias manually.

(3) The package needs to be modified manually. Note: The Ver13 version of the package generation needs to perform/decode the shield off, because these two points are not done, the module is plsql.pm.

Of course, the package conversion is not just this part of the thing, mainly:

A aliases need to show write-out

B implicit conversions to show write-out

C Function Differences (GP has a set of Oracle function implementations, basically enough)

D Oracle inside non-standard notation, such as: a LEFT join B written as a, a where a.xx=b.xx (+)

2. Oracle's data migration to the GP implementation process

Use SQLULDR2 to get data from Oracle unload to a named pipe, and then load the data into GP via gpload. Sqluldr URL: http://www.anysql.net/tools/sqluldr_ Comming.htmlgpload Reference article: http://www.itpub.net/thread-1423310-1-1.html dataload.sh key points have two: (1) Mr. Sqluldr into the data, into the pipe. Gpload reads the configuration file, fetches the data from the pipeline, launches the gpfdist itself, generates external table, loads the GP library (2) when the amount of data is low, that is, after the SQLULDR process is over, the gpload process is not fully started. This time, Gpload has been waiting for the data inside the pipeline to come, hang on. In order to solve this problem, deliberately add dbms_lock.sleep (2) inside the Sqluldr Presql, so as to ensure that the SQLULDR process before the end of the The gpload process has started. Or you can write C directly to specify the pipeline.

#!/bin/bashif[ $#-lt 3];thenEcho'Usage ' basename $ ' pipe tablename control'    Exit 1Fipipename=$1TableName=$2Control=$3condition=$4Mknod$pipenameP/ROOT/SOFTWARE/SQLULDR2 User=manager/tigerd@orclquery="SELECT * from $tablename where $condition"Field=0x7cfile=$pipename 
Charset=utf8 text=csv Safe=yes persql="begin Dbms_lock.sleep (2); end;"&Gpload-F$control-L gpload.LogRM-rf$pipename

Ora2gp.sh--Generates a control file, including the pipe file name. Then call the process above to implement the onboarding process.

#!/usr/bin/env python#-*-Coding:utf-8-*-ImportYamlImportSubprocessImportSysImportOs#Script starts from here paramnum=Len (SYS.ARGV) datadt=20140820condition="1=1"Tplpath="/root/template/"Pipepath="/tmp/pipe"Batname="/root/script/dataload.sh"if (ParamNum = = 1):Print‘Usage:' + sys.argv[0]+‘TableName‘Sys.exit ()elif (ParamNum = = 2): tablename=sys.argv[1]elif (ParamNum = = 3): tablename=sys.argv[1] Datadt=sys.argv[2]elif (ParamNum = = 4): tablename=sys.argv[1] Datadt=sys.argv[2] Condition=sys.argv[3]Else:Print‘Usage:' + sys.argv[0]+‘TableName Datadt condition. (Datadt condition is optional)!‘Sys.exit () pid=Os.getpid () pipename=pipepath+STR (PID) f = open (tplpath+"Gp_template_load.ctl") DataMap =Yaml.load (f) f.close () datamap[‘Gpload‘][‘INPUT'][0][‘SOURCE‘][‘FILE'][0]=pipenamedatamap[‘Gpload‘][‘OUTPUT'][0][‘TABLE']=tablenamedatamap[‘Gpload'] ['INPUT'][6]['error_table']=tablename+'_err'filename=tplpath+ tablename+'. CTL'f = open (filename,'w') yaml.dump (datamap,f) f.close () handle=subprocess. Popen ([batname,pipename,tablename,filename,condition]) handle.communicate ()      

Control File Template

Version:1.0.0.1database:dwuser:managerhost:gpport:5432gpload:   INPUT:    -SOURCE:         local_hostname:           -GP         FILE:           -/tmp/mypipe         port_range: [8001,9000]    -format:csv    -DELIMITER: ', '    -QUOTE: ' "' c11/>-header:true    -error_limit:10000    -Error_table:tablename_err   OUTPUT:    -table:tablename< c16/>-mode:insert   preload:    -truncate:true

Oracle Migration GP Practice