Ora2pg Installation

Ora2pg Installation

Install following dependencies on your system before installing the ora2pg

1)         Perl distribution (perl 5.6 and above)

2)         DBI (database interface module)

3)         DBD::Oracle Perl module

4)         DBD::Pg  Perl module (if you don’t have postgres installed on your system ).

Install Oracle Client On CentOS

First Download the packages from net:

            oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

            oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

            oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

Go to the location and run the Following comments:

            rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

            rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

            rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

Then configure the TNSNames.ora file:

            tnsnames.ora file is used to store database connection information.

            This file must be created and configured in your current client installation.

To do this, first create your network/admin directory

            [root@dinesh ~]# mkdir /usr/lib/oracle/11.2/client64/network/admin -p

Using the following touch command we create the TNSNames.ora file in the Destination:

            cd /usr/lib/oracle/11.2/client64/network/admin

            touch tnsnames.ora

Edit the file using vi file name:

            vi tnsnames.ora

The edit session opens and give 'I' to edit, and give the following details:

            ORATST=

            (description=

              (address_list=

               (address = (protocol = TCP)(host = 172.16.12.244)(port = 1522))

             )

            (connect_data =

              (service_name=orcl1)

            )

            )

Then press esc and ':wq' for write and exit.

Then we open the file and check using the following command:

            cat tnsnames.ora

Then Configure the ORACLE Environment Variables:

Create file /etc/profile.d/client.sh with the following file content

            cd /etc/profile.d/

            touch client.sh

            vi client.sh

Then give the details

            export ORACLE_HOME=/usr/lib/oracle/11.2/client64

            export PATH=$PATH:$ORACLE_HOME/bin

            export LD_LIBRARY_PATH=$ORACLE_HOME/lib

            export TNS_ADMIN=$ORACLE_HOME/network/admin

and write and exit from the file.

After the ORACLE client installation we going to Install DBD::Oracle:

            Download DBD::Oracle from http://search.cpan.org/ (Give DBD::Oracle in the search tab and download the recent updated file)

Following the steps in the file location and do the installation steps:

            cd cd /home/dinesh/Downloads/DBD-Oracle-1.74/

            ls

            perl Makefile.PL

            make

            make install

If there is no errors or problems, the DBD::Oracle was successfully installed.

Then Install ora2pg:

            Download the latest version of ora2pg from http://sourceforge.net/projects/ora2pg/

Then go to the file location and run the following steps:

            perl Makefile.PL

            make

            make install

ora2pg Configuration:

By default Ora2Pg will look to ora2pg.conf configuration file into /etc/ora2pg/ directory.

For standalone machine just change the following parameters in ora2pg.conf.

In the .config file, we give the following details:

            ORACLE_HOME    /usr/lib/oracle/11.2/client64

            ORACLE_DSN      dbi:Oracle:host=[172.16.12.244];sid=[orcl1]

            ORACLE_USER     [ee]

            ORACLE_PWD      [mhQBrIXOPYEoWjK9tkVd]

            USER_GRANTS     0

After the configuration we have to create a folder for our Project.

            mkdir -p /home/git/tmp

Then we create the project inside the folder.

            ora2pg --project_base /home/git/tmp --init_project test_project

Then we go to the project path and edit the export_schema.sh file:

            cd /home/git/tmp/test_project/config/

            vi ora2pg.conf

We edit the configurations in the specified file, the changes like:

ORACLE_DSN

dbi:Oracle:host=mydb.mydom.fr(172.16.12.244);sid=SIDNAME(orcl1);port=1522    

-- (If our port is        1521 this is not mandatory, otherwise we have to mention this)

       ORACLE_USER       system(accesscontrol)

       ORACLE_PWD                     manager(rKvuaBoLsq_kwn4P8pcx)

        SCHEMA       CHANGE_THIS_SCHEMA_NAME(accesscontrol)

Write and exit.

Then we have to export schema from Server to ora2pg using this:

            ./export_schema.sh

After exporting schema we have to export the data using the following command:

            ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

Then we import the data from ora2pg to local Postgres DB using import command:

            ./import_all.sh -d healthentic(DB Schema) -o postgres(Owner) -U healthentic(User)

Then we give the needed categories 'yes' and import the data.

Migrate specific Tables:

If we want to migrate some specific tables, we do some configuration changes in the ora2pg.conf file.

In the export section, uncomment the ALLOW part and give the table names which we want to migrate separated with space or comma(,).

(i.e)

ALLOW  CUSTOMER,APP_USER APPLICATION,CUSTOM_COLOR

Then we run the command, the given tables only exported:

 ./export_schema.sh