Sachi's Oracle to Greenplum Data Migration

To get the script email us gpdba@greenplumdba.com

Step1 : Get the information about source schema.

Step2 : Generate DDL for Greenplum schema from Oracle schema

Step3: Generate CSV data dump for oracle tables.

Step 4: Load the database using GPFDists

step 5: Validate the data

==Option Menu==

##############################################################################################

Please read the menu options carefully before selection as our menu option may have changed. #

##############################################################################################

Choose one of the following option:

*********Unloading Schema DDL and Data from Oracle**********

1 - Test Oracle Database Connectivity

2 - Oracle Database Information Report

3 - Oracle Table Rows Count Report

4 - Oracle Table Checksum Report

5 - Generate Greenplum Schema Table DDL corresponding to Oracle Schema

6 - Generate Greenplum External Table DDL corresponding to Oracle Schema

7 - Generate Load data insert table scripts to insert data into Greenplum table

8 - Generate Select count DML scripts to count no of rows in greenplum internal and external tables

9 - Export Oracle Table Data in CSV Format consumed by Greenplum External Table

10 - **Export very large partitioned tables data in parallel and store in different location

11 - **Generate External table DDL of large partitioned tables

21 - Test Greenplum Database Connectivity

22 - Create table in Greenplum using DDL generated from option 5

23 - Create external table in Greenplum using DDL generated from option 6 or option 10

24 - Load Data in Greenplum

25 - Generate table counts DML script

26 - Create Checksum Report of Migrated data in Greenplum

27 - Compare Oracle and Greenplum Checksum Report

41 - Schema Manager - Rebuild, Upgrade, Clone Schema

42 - Users and Roles Management - Create/Modify/Delete users

43 - Grants and Privileges - Database Access Control

70 - Call a calculator

71 - Display files in user directory

72 - Display Disk Usage

73 - Display CPU Utilization

74 - Display Memory Utilization

75 - Find and Replace

76 - Find files for a particular date (example APR 19)

77 - List files in MB size

##############################################################################################

97 - Help and Support

98 - Show configuration setting

99 - Logout

**Functionality not vailable in this version

##############################################################################################

Enter your option:

References:

http://www.pgcon.org/2011/schedule/attachments/205_Oracle_to_Postgres_Migration.pdf

http://www.emc.com/collateral/software/white-papers/h8869-emc-greenplum-dca-oracle-gdw-wp.pdf

http://www.slideshare.net/emcacademics/h8869-emcgreenplumdcaoraclegdwwp-12045737

Compiling Pro C program in RHEL6 Oracle

[oracle@sachi ~]$ cd /u01/app/oracle/product/11.2.0/db_1/precomp/admin/

[oracle@sachi admin]$ ls

ottcfg.cfg pcscfg.cfg pcscfg.cfg.old pcscfg.cfg.orig shrept.lst

[oracle@sachi admin]$ cp pcscfg.cfg pcscfg.cfg.orig

edit the pcscfg.cfg file and remove the unwanted entry. After editing my cfg file looks like.

[oracle@sachi admin]$ cat pcscfg.cfg

sys_include=($ORACLE_HOME/precomp/public,/usr/include,/usr/lib/gcc/x86_64-redhat-linux/4.4.7/include)

ltype=short

define=__x86_64__

For fedora 32 bit

[sachi@dbaref SchemaUnload]$ cat /u01/app/oracle/product/11.2.0/db_1/precomp/admin/pcscfg.cfg

sys_include=($ORACLE_HOME/precomp/public,/usr/include,/usr/lib/gcc/i686-redhat-linux/4.6.3/include)

ltype=short

compile the program

[oracle@sachi admin]$ gmake -f sachi64rhel.mk array_flat2

[10:58 sachi@sachi SchemaUnload] > rm -f array_flat2

[10:58 sachi@sachi SchemaUnload] > rm -f array_flat2.c

[10:58 sachi@sachi SchemaUnload] > rm -f array_flat2.o

[10:59 sachi@sachi SchemaUnload] > gmake -f sachi64rhel.mk array_flat2

gmake: Circular array_flat2.pc <- array_flat2.o dependency dropped.

( proc include=. mode=oracle dbms=V8 lines=yes ltype=none dynamic=ansi code=ANSI_C SQLCHECK=SEMANTICS unsafe_null=yes iname=array_flat2;)

Pro*C/C++: Release 11.2.0.1.0 - Production on Wed May 22 10:59:04 2013

System default option values taken from: /u01/app/oracle/product/11.2.0/db_1/precomp/admin/pcscfg.cfg

(/usr/bin/gcc -m64 -O2 -g -L/u01/app/oracle/product/11.2.0/db_1/lib -c array_flat2.c -o array_flat2.o ;)

#(rm array_flat2.c;)

(/usr/bin/gcc -m64 -O2 -g -lc -lnsl -L/u01/app/oracle/product/11.2.0/db_1/lib -lclntsh array_flat2.o -o array_flat2 ;)

rm array_flat2.o

[10:61 sachi@sachi SchemaUnload] > gmake -f sachi64rhel.mk array_flat2

gmake: Circular array_flat2.pc <- array_flat2.o dependency dropped.

gmake: `array_flat2' is up to date.

usage

[sachi@dbaref SchemaUnload]$export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/usr/lib

[sachi@dbaref SchemaUnload]$ ./array_flat2 userid=sachi/sachi@dbaref sqlstmt='select * from hr.employees' arraysize=1024>hremp.csv

Connected to ORACLE as user: sachi

Unloading 'select * from hr.employees'

Array size = 1024

EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID

107 rows extracted

[sachi@dbaref SchemaUnload]$