• 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
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Office Space
    • Virtual Space
    • Co-working Space
    • Private Space
    • Meeting Room
    • Locations
    • Add Listing
    • Dashboard
  • Blogs
  • Careers
    • Jobseeker
    • Employer
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8148383856
    info@rayafeel.com
    Login
    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
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Office Space
      • Virtual Space
      • Co-working Space
      • Private Space
      • Meeting Room
      • Locations
      • Add Listing
      • Dashboard
    • Blogs
    • Careers
      • Jobseeker
      • Employer
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • Oracle to Postgres Database Migration

      Oracle to Postgres Database Migration

      • Posted by bushra.rayafeel
      • Categories Blog
      • Date May 28, 2022
      • Comments 0 comment

      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

      Tag:#databasemigration, DB migration, ora2pg, postgresql

      • Share:
      bushra.rayafeel

      Previous post

      PostgreSQL Hotline Support : Ensure your data Integrity & Security
      May 28, 2022

      Next post

      Postgres Invalid size memory allocation
      May 28, 2022

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      HOW VIRTUAL OFFICE IS GOING TO CHANGE YOUR BUSINESS STRATEGIES
      21Jun2022
      Take the best advantage of your Virtual Office
      21Jun2022
      Let’s discuss how corporates can improve their productivity through virtual office spaces?
      21Jun2022
      Virtual Office Space & Address Services
      21Jun2022
      HOW VIRTUAL OFFICES ARE TRANSFORMING BUSINESSES
      21Jun2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      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

      Connect with

      Login with Google

      Login with your site account

      Connect with



      Lost your password?