• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • CoWorking/Virtual Office Space Solution
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • CoWorking/Virtual Office Space Solution
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Oracle to Postgres Database Migration

      • Home
      • Oracle to Postgres Database Migration

      Oracle to Postgres Database Migration

      • Posted by Nijamutheen J
      • Date August 8, 2019

      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

      • Share:
      Admin bar avatar
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      Postgres Invalid size memory allocation
      August 8, 2019

      Search

      ADVERTISEMENT

      Latest Posts

      Rayafeel Business proposal
      18Apr2021
      24 Best Coworking Spaces in Chennai You Should Know
      13Apr2021
      Easy to get a Virtual Address in Chennai
      12Apr2021
      Easy to get a Virtual Office in Chennai
      12Apr2021
      What is a Virtual Office? And How to get in chennai ?
      11Apr2021

      Latest Courses

      PostgreSQL

      PostgreSQL

      $600.00 $500.00
      Greenplum

      Greenplum

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres
      • postgres script for finding queries more than 1 hours ?
      • How to take the tables backup in postgres
      • what are the extensions required to be in instance for performance

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now