We have migrated to new Google sites!
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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]$