Ora2Pg is Free Software written and maintained by Gilles Darold. Its an Ideal for large migration projects.
3 Reasons to move to PostgreSQL :
- Reduce Infra & Business Costs
- Reduction of license costs to ZERO
- Reduction of support costs by 80% or more
How it works: https://ora2pg.darold.net/start.html
Installing
pre-requisites:
- Install DBI (database interface module)
- Install DBD::Oracle and DBD::Pg
- Oracle Enterprise Linux 6.5
- Oracle 11.2 database
- ora2pg latest version
Download the latest version of ora2pg from http://sourceforge.net/projects/ora2pg/ and run the following commands
Perl module DBD::Oracle will used for connectivity to an Oracle database from Perl DBI.
Install Ora2pg
# tar -xvf ora2pg-18.0.tar # cd ora2pg-18.0/ # perl Makefile.PL # make # make install
Validate the installation / Check the version of ora2pg
[root@oracleasm1 ora2pg_mig]# ora2pg -t SHOW_VERSION -c /var/lib/pgsql/pg10/data/ora2pg_new.conf Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Configuration
for Ora2Pg
Let us go thru the four parameters and configure them.
Note : For defining which objects we want to export, we must add object type names after TYPE keyword
[root@oracleasm1 ora2pg_mig]# ora2pg -c /etc/ora2pg/ora2pg.conf vi /etc/ora2pg/ora2pg.conf
ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 ORACLE_DSN dbi::Oracle:host=oracleasm1.localdomain;sid=orcl;port=1521 ORACLE_USER system ORACLE_PWD manager TYPE TAPE PACKAGE INSERT COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION QUERY DBLINK SYNONYM LOAD
Where, ORCL is database name.
If you want to call another configuration file, just give the path as a command-line argument:
[root@oracleasm1 ora2pg_mig]# ora2pg -c /etc/ora2pg/ora2pg.conf
To Initialize a new project :
[root@oracleasm1 ~]# /usr/local/bin/ora2pg --init_project ora2pg_mig [root@oracleasm1 ora2pg_mig]# ls -ltr ./config/ora2pg.conf -rw-r--r-- 1 root root 58173 Sep 14 13:11 ./config/ora2pg.conf
Generate an Estimate report
[root@oracleasm1 ora2pg_mig]# ora2pg -t show_report --estimate_cost -c /etc/ora2pg/ora2pg.conf --dump_as_html > /tmp/ora2pg.html [========================>] 4/4 tables (100.0%) end of scanning. [========================>] 6/6 objects types (100.0%) end of objects auditing.
Above will generate an html report which will give an estimate.
The report looks good to me. Now, To copy the data from Oracle to PostgreSQL, first
we will Export objects under specified schema from Oracle, then Import to
PostgreSQL and finally copy the data.
There will be .sql file created in the current directory (of your project) which will have converted data into PostgreSql.
[root@oracleasm1 ora2pg_mig]# ./export_schema.sh
[========================>] 1/1 tables (100.0%) end of scanning. [========================>] 5/5 objects types (100.0%) end of objects auditing.
Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf [========================>] 1/1 tables (100.0%) end of scanning. [========================>] 1/1 tables (100.0%) end of table export.
Running: ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf [========================>] 0/0 packages (100.0%) end of output. Running: ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf [========================>] 0/0 views (100.0%) end of output.
Running: ora2pg -p -t GRANT -o grant.sql -b ./schema/grants -c ./config/ora2pg.conf WARNING: Exporting privilege as non DBA user is not allowed, see USER_GRANT Running: ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf [========================>] 0/0 sequences (100.0%) end of output.
Running: ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf [========================>] 0/0 triggers (100.0%) end of output. Running: ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf [========================>] 0/0 functions (100.0%) end of functions export. Running: ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf [========================>] 0/0 procedures (100.0%) end of procedures export. Running: ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces -c ./config/ora2pg.conf WARNING: Exporting tablespace as non DBA user is not allowed, see USER_GRANT Running: ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf [========================>] 0/0 partitions (100.0%) end of output. Running: ora2pg -p -t TYPE -o type.sql -b ./schema/types -c ./config/ora2pg.conf [========================>] 0/0 types (100.0%) end of output. Running: ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews -c ./config/ora2pg.conf [========================>] 0/0 materialized views (100.0%) end of output. Running: ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf [========================>] 0/0 dblink (100.0%) end of output. Running: ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms -c ./config/ora2pg.conf [========================>] 0/0 synonyms (100.0%) end of output.
Running: ora2pg -p -t DIRECTORY -o directorie.sql -b ./schema/directories -c ./config/ora2pg.conf [========================>] 0/0 directory (100.0%) end of output Running: ora2pg -t PACKAGE -o package.sql -b ./sources/packages -c ./config/ora2pg.conf [========================>] 0/0 packages (100.0%) end of output. Running: ora2pg -t VIEW -o view.sql -b ./sources/views -c ./config/ora2pg.conf [========================>] 0/0 views (100.0%) end of output. Running: ora2pg -t TRIGGER -o trigger.sql -b ./sources/triggers -c ./config/ora2pg.conf [========================>] 0/0 triggers (100.0%) end of output. Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions -c ./config/ora2pg.conf [========================>] 0/0 functions (100.0%) end of functions export. Running: ora2pg -t PROCEDURE -o procedure.sql -b ./sources/procedures -c ./config/ora2pg.conf [========================>] 0/0 procedures (100.0%) end of procedures export. Running: ora2pg -t PARTITION -o partition.sql -b ./sources/partitions -c ./config/ora2pg.conf [========================>] 0/0 partitions (100.0%) end of output. Running: ora2pg -t TYPE -o type.sql -b ./sources/types -c ./config/ora2pg.conf [========================>] 0/0 types (100.0%) end of output. Running: ora2pg -t MVIEW -o mview.sql -b ./sources/mviews -c ./config/ora2pg.conf [========================>] 0/0 materialized views (100.0%) end of output.
To extract the data, use the following command
[root@oracleasm1 ora2pg_mig]# ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf [========================>] 3999988/3999988 rows (100.0%) on total estimated data (5158 sec., avg: 558 recs/sec)
Load schemas to Postgres DB :
[root@oracleasm1 ora2pg_mig]# su - postgres -bash-4.1$ psql postgres=# CREATE DATABASE NEWDB ; postgres=#\q -bash-4.1$ cd ./ora2pg_mig -bash-4.1$ psql -U postgresql_username -d NEWDB < data.sql
No comments:
Post a Comment