We recommend ORA2PG - http://ora2pg.darold.net/ - for data instantiation from Oracle database to Postgres. There are server other tools for this purpose. However, those aren't free of charge. You can find them at https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle. This tool can be used on Unix based OS or Windows.

Pre-

...

requisites

...

• PostgreSQL server or PostgreSQL client is pre-installed on your server. Else download it using yum (yum install postgresql-server or yum install postgresql) or from "https://www.postgresql.org/download/linux/redhat/" if you want use specific version. 

  Code Block
  [root@target11g data]# yum install postgresql

  
  

ORA2PG installation

1. Connect to the server as root.

2. Download and install ora2pg:

   Code Block
   [root@target11g ~]# mkdir download; cd download [root@target11g ~]# wget https://sourceforge.net/projects/ora2pg/files/18.2/ora2pg-18.2.tar.bz2 [root@target11g ~]# bzip2 -d ora2pg*.bz2 [root@target11g ~]# tar xvf ora2pg* [root@target11g ~]# cd ora2pg* [root@target11g ~]# perl Makefile.PL [root@target11g ~]# make && make install [root@target11g ~]# cd ../..; rm -rf download

   
   

3. Install DBD::Oracle (if not already installed)

   Code Block
   [root@target11g ~]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/ [root@target11g ~]# export LD_LIBRARY_PATH =/u01/app/oracle/product/11.2.0/dbhome_1/lib [root@target11g ~]# perl -MCPAN -e shell cpan> get DBD::Oracle cpan> quit [root@target11g ~]# cd ~/.cpan/build/DBD-Oracle* [root@target11g ~]# perl Makefile.PL [root@target11g ~]# make [root@target11g ~]# make install

   
   

4. Create the default configuration file and grant the privileges to the non-root user

   Code Block
   [root@target11g ~]# cp /etc/ora2pg/ora2pg.conf* /etc/ora2pg/ora2pg.conf [root@target11g ~]# chown oracle:oinstall /etc/ora2pg/ora2pg.conf

   
   

5. Disconnect from the root user.

...

Steps above are just an example how to do the installation. They may differ a little bit in your case. For example you might want to preserve the download directory.

ORA2PG configuration

...

1. Find the following four Oracle related parameters and change them accordingly:

   Code Block
   ORACLE_HOME /u01/app/oracle/product/12.1/dbhome_1 ORACLE_DSN dbi:Oracle:host=localhost;sid=sd8eOM9L;port=1521 ORACLE_USER system ORACLE_PWD oracle

   
   

2. Find the following parameters and set them as descibed:

   Code Block
   TYPE TABLE VIEW GRANT TABLESPACE TYPE PARTITION DISABLE_SEQUENCE 1 DISABLE_TRIGGERS USER TRUNCATE_TABLE 1 DROP_FKEY 1

   
   

3. Set parameter OUTPUT to the desired value.
4. Set parameter SCHEMA to the source DB schema(s)
5. Set parameter PG_SCHEMA to the target schema name

6. Add the following line to the configuration file:

   Code Block
   ORA_INITIAL_COMMAND commit

   
   

7. connect back to non-root OS user

Copying the data from Oracle to Postgres

To copy the data from Oracle to Postgres you need to follow these steps: 

1. Export tables DDL from Oracle
2. Import DDL to Postgres
3. Copy the data

1. Export DDL from Oracle

...

In the previous example ora2pg uses the default configuration file /etc/ora2pg/ora2pg.conf.
Also notice that ora2pg only exports DDL for the schema SCOTT as we defined this schema within the configuration file.

2. Import DDL to Postgres

...

3. Copy the data

1. Pause APPLY process

2. Prepare the table you want to replicate:

   Code Block

   dbvrep> prepare table SCOTT.EMP Prepare enabled supplemental logging, waiting for SCN advance enough to prevent ORA-01466 during consistent export....Waited 1 seconds until scn_to_timestamp changed. Connecting to running apply [scott.emp]: [Apply table removed (0 metadata record(s)). Apply table added (1 metadata record(s)).] Connecting to running mine [SCOTT.EMP]: [Table prepared (1 internal records).] Table SCOTT.EMP instantiated at SCN 3601476

   
   

3. Add the following line to your ora2pg configuration file. Use the SCN number you got as a prepare command output

   Code Block
   ORA_INITIAL_COMMAND call dbms_flashback.enable_at_system_change_number(3601476)

   
   

4. Copy the data:

...

When finished resume the apply process again.

Final steps

• Disable triggers
• Drop delete cascade constraints.
• Disable deffered constraints

Related articles

• How to install ORA2PG on Windows - https://stackoverflow.com/questions/47345373/how-to-install-ora2pg-on-windows-7.
• https://blog.dbi-services.com/migrating-from-oracle-to-postgresql-with-ora2pg/
• http://ora2pg-samrat.blogspot.com/

...