how to install ora2pg on windows 7?

Question

i downloaded the zip file which was available online for free download but i a not able to install the downloaded file and run the file, can any one please help me installing the ora2pg on my windows machine please.

Answer 1

Summary: I had to migrate few tables from Oracle database to PostgreSQL for my local development team to work on few modules of the application.

How? To save time, I did some research in postgreSQL blogs and found a tool ora2pg tool. This tool works best on UNIX as described in the blog. But there are quite good challenges when your PostgreSQL is on windows system.

Challenge:

• Install Perl 5.0 on windows
• Install Oracle DBD libraries to perl on windows
• Tables structure should exists prior running the tool
• Oracle client "Administrator" pack to be installed on windows.

Action:

Pre-requisites:

• Download and install perl on your windows from "http://strawberryperl.com/"
• Install oracle client on your windows
• Download ora2pg software and untar to a known location on windows from "http://sourceforge.net/projects/ora2pg/files/13.0/ora2pg-13.0.tar.bz2/download"
• PostgreSQL is pre-installed on your windows machine. Else download it from "http://www.enterprisedb.com/products-services-training/pgdownload#windows"

Installation:

In cmd on your windows. Make sure you have internet connection:

cd <un tarred location>\ora2pg-15.2
perl Makefile.PL

dmake && dmake install

Set the environment variables:
Set ORACLE_HOME=<ORACLE_CLIENT_SOFTWARE_LOCATION>

Set LD_LIBRARY_PATH=<ORACLE_CLIENT_HOME>/lib

Install DBD::Oracle libraries - Internet is must

perl -MCPAN -e "install DBD::Oracle"

• Configuration file is created by default in C:\ora2pg
• Copy the template config file to original file name and edit
• Copy ora2pg_dist.conf to ora2pg.conf

Edit config file as in the example I used for one table:

ORACLE_HOME          C:\oracle\app\yaddanap\product\11.2.0\client_1
ORACLE_DSN  dbi:Oracle:host=172.31.232.253;sid=SUPLINUX
ORACLE_USER            system
ORACLE_PWD 1qaz2wsx!
USER_GRANTS     0
DEBUG                        0
EXPORT_SCHEMA       0
SCHEMA                      DB000001
CREATE_SCHEMA       0
COMPILE_SCHEMA     0
EXPORT_INVALID        0
TYPE                TABLE
ALLOW                        SL_01_DEPOS
DATA_LIMIT    150000
PG_DSN                       dbi:Pg:dbname=Drive1;host=localhost;port=5432
PG_USER                     kcc
PG_PWD                      kcc123
PRESERVE_CASE         0
BZIP2
GEN_USER_PWD         0
FKEY_DEFERRABLE     0
DEFER_FKEY    0
DROP_FKEY     0
DROP_INDEXES           0
PG_NUMERIC_TYPE    1
PG_INTEGER_TYPE     1
DEFAULT_NUMERIC bigint
KEEP_PKEY_NAMES 0
DISABLE_TRIGGERS 0
NOESCAPE       0
DISABLE_SEQUENCE   0
PLSQL_PGSQL 1
ORA_RESERVED_WORDS       audit,comment
FILE_PER_CONSTRAINT          0
FILE_PER_INDEX                     0
FILE_PER_TABLE         0
TRANSACTION committed
PG_SUPPORTS_WHEN                        1
PG_SUPPORTS_INSTEADOF    1
FILE_PER_FUNCTION  0
TRUNCATE_TABLE      0
FORCE_OWNER          0
STANDARD_CONFORMING_STRINGS 1
JOBS                1
ORACLE_COPIES         1
PARALLEL_TABLES      1
ALLOW_CODE_BREAK            1
XML_PRETTY   0
DISABLE_COMMENT         0
USE_RESERVED_WORDS        0
PKEY_IN_CREATE                    0
NULL_EQUAL_EMPTY 1
EXTERNAL_TO_FDW               1
ESTIMATE_COST                     0
COST_UNIT_VALUE                 5
DUMP_AS_HTML                    0
STOP_ON_ERROR                   1
TOP_MAX                                10
ALLOW_PARTITION                PARTNAME
USE_TABLESPACE                   0
PG_SUPPORTS_MVIEW          1
REORDERING_COLUMNS       0
SYNCHRONOUS_COMMIT      0
PG_SUPPORTS_CHECKOPTION           0
AUTODETECT_SPATIAL_TYPE 1
CONVERT_SRID                       1
DEFAULT_SRID                        4326
GEOMETRY_EXTRACT_TYPE  WKT
PREFIX_PARTITION     0
LOG_ON_ERROR                    0
PG_SUPPORTS_IFEXISTS         1
WITH_OID                   0

run the ora2pg now:

c:\ora2pg>ora2pg -c ora2pg.conf

Run the generated DDL statement in PostgreSQL to create structure of the table Now edit ora2pg.conf file at one field as below again to copy the data now.

 TYPE                TABLE,INSERT

Re-run ora2pg tool now and you will see the data pooled.

c:\ora2pg>ora2pg -c ora2pg.conf

[========================>] 953/906 rows (105.2%) Table SL_01_DEPOS (952.9 recs/sec)

[========================>] 906/906 rows (100.0%) on total data (avg: 905.9 rec/sec

Answer 2

I have shared step by step of LINUX installation, Please go through only respected command of windows, it will definitely work. Pre-installation steps:

1. ORACLE Client must be installed and ORACLE_HOME must be set.
2. Install perl (version 5.6 and above)

Installation steps:

1. Install DBI (database interface module)

   apt-get install cpanminus (for ubuntu)
   yum install cpanminus (for Linux)
   cpanm DBI
   

2. Install DBD::Oracle and DBD::Pg

   cpanm DBD::Oracle
   cpanm DBD::Pg
   

3. Download the latest version of ora2pg from http://sourceforge.net/projects/ora2pg/ and run the following commands:

   tar -xvf ora2pg-18.0.tar
   cd ora2pg-18.0/
   perl Makefile.PL
   su root
   make
   make install
   

4. Do the changes as per req By default Ora2Pg will look to ora2pg.conf configuration file into /etc/ora2pg/ directory.

   ORACLE_HOME /app/oracle/product/11.2.0
   ORACLE_DSN dbi:Oracle:host=[hostname];sid=[SID name]
   ORACLE_USER [SYSTEM]
   ORACLE_PWD [password]
   USER_GRANTS 1
   

5. Check the version of pra2pg:

   ora2pg SHOW_VERSION
   

6. Run the below command

   ora2pg -c /etc/ora2pg/ora2pg.conf
   ora2pg -c /etc/ora2pg/ora2pg.conf -p -P 10 -J 10 -L 1000000
   

7. .sql file will be created in the current directory have converted data into PostgreSql

Hope this will work.