Sachi's Oracle to Greenplum Data Migration

To get the script email us gpdba@greenplumdba.com
The gadget spec URL could not be found
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
The gadget spec URL could not be found
==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: 
The gadget spec URL could not be found



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]$


ċ
array_flat2.c
(33k)
Sachchida Ojha,
May 22, 2013, 7:51 AM
ċ
array_flat2.pc
(6k)
Sachchida Ojha,
May 22, 2013, 7:51 AM
ċ
sachi32fedora.mk
(2k)
Sachchida Ojha,
May 22, 2013, 8:06 AM
ċ
sachi64rhel.mk
(2k)
Sachchida Ojha,
May 22, 2013, 7:51 AM
Comments