Oracle to Postgres Database Migration

Oracle migration:

For migration first we need to install oracle database and ora2pg software and DBD oracle packages.

=>first install oracle database in server and note down the paths,username,password needed

 

For installing the oracle database follow the below steps:

From the root user:

create group "groupadd oinstall"
create group "dba"
create user "useradd oracle -G dba -g oinstall
create directory "mkdir /home/oracle/software"

Change ownership permissions to software directory

chown oracle:oinstall /home/oracle/software

copy the downloaded files to /home/oracle/software and untar the files

After untaring we can find “database” directory in /home/oracle/software

In database directory we find runinstaller file we need to run that file

./runinstaller

and finish the oracle database installation.

=======================================================================

After finishing the installation switch to oracle user

export the paths in .bash_profile of the oracle user:

PATH=/home/oracle/app/oracle/product/11.2.0/dbhome_2/bin:$PATH:$HOME/.local/bin:$HOME/bin
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=orcl

save and exit

execute the .bash_profile

To find the oracle_sid  ps -ef | grep pmon

we will get the running processes in that:

oracle  12929   1 0  18:89  ? 00:00:00 ora_pmon_orc1

in the above proces oracle_sid is orac1, sid means “system identifier”

For logining into oracle database we have three ways:

1.sqlplus / as sysdba
2.sqlplus system/oracle
3.lsnrctl status

=======================================================================

Create user in oracle server: 

create user migration identified by migration;

give grant priviliges on migration: 

grant dba to migration;

logout of the server: 

quit

login with migration user:

sqlplus migration/migration

--create table: 
create table test(id int,name varchar2(200),sex char(1),text1 clob);
insesrt into table: insert into test values(1,'raags','M','dzlbvrzvbvbk');

--after inserting we need do commit: commit;

==================================================================
For installing ora2pg package go to this link and download: 

https://github.com/darold/ora2pg/releases

after downloading go to root user and move the ora2pg package to /home/postgres  and give the postgres ownership to the ora2pg package.

untar the package and go to the ora2pg-18.2 directory and run the below commands to innstall the package

perl Makefile.sh
make
make install

========================================================================

ora2pg need perl module DBD::ORACLE for connectivity to an oracle database from perl DBI::Oracle get it from CPAN a perl repository, After setting oracle_home and ld_library_path environment variables as root user.Install DBD::Oracle.Proceed as follows

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export LD_LIBRARY_PATH=/home/oracle/app/oracle/product/11.2.0/dbhome_2/lib

perl -MCPAN -e 'install DBD::ORACLE'(IF WE HAVE INTERNET TO THE SERVER WE CAN COMPLETE THE INSTALLATION OTHERWISE WE NEED TO DO MANUALLY).
export PATH=/home/postgres/software/bin:$PATH(IN ROOT USER WE NEED TO EXPORT THE PATH).

ora2pg --version
cd /etc/ora2pg
ls -ltr
cp ora2pg.conf.dist ora2pg.conf

In the above conf file we need to do configuration copy the below lines at the end of the conf file:

ORACLE_DSN      dbi:Oracle:host=localhost;sid=orcl
ORACLE_USER     migration
ORACLE_PWD      migration
SCHEMA          migration
TYPE       TABLE  PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION
OUTPUT          rc_output.sql

and we  need to change the oracle_home in the above conf file:

ORACLE_HOME   /home/oracle/app/oracle/product/11.2.0/dbhome_2

save and exit from the conf file

and run ora2pg after running the above command we will get a plain text named in the conf file like (rc_output.sql)

cd /etc/ora2pg in this location we can find the (rc_output.sql).

Give the executive permissions to the file (chmod 777 rc_output.sql).

========================================================================

Now login to the postgres server and execute the rc_output.sql file

\i rc_output.sql

After executing the file the database is successfully migrated from the oracle to postgres.

Written BY :- Raghavan Rao