04.02. Data Migration from Oracle to Postgresql

                                                   <<< Back   Next >>>

02.01. Generate migration cost report
[app-ora2pg] 
# cd /tmp/instance1/ora2pg/migr01/
# ora2pg -t show_report  --estimate_cost -c config/ora2pg.conf --dump_as_html > /tmp/instance1/ora2pg.html




02.02. Generate DDL scripts from Oracle
[app-ora2pg] 
# cd /tmp/instance1/ora2pg/migr01/
# ./export_schema.sh



02.03. Extract data
[app-ora2pg] 
# cd /tmp/instance1/ora2pg/migr01/
# ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf --jobs 4 --copies 4 --parallel 4

options:
-J   | --copies num   : Number of parallel connections to extract data from Oracle.
-j   | --jobs num       : Number of parallel process to send data to PostgreSQL.
-P  | --parallel num  : Number of parallel tables to extract at the same time.
-L  | --limit num      : Number of tuples extracted from Oracle and stored in memory before writing, default: 10000.


02.04. Create user for data migration
[postgres@pgsql-db-quik ~]$ psql
> CREATE USER stock WITH password '*******';
> alter user stock WITH SUPERUSER;
> alter user stock WITH createdb;


02.05. Create DDL objects and load data in PostgreSQL
[app-ora2pg] 
# cd /tmp/instance1/ora2pg/migr01/
# ./import_all.sh -h pgsql-db-quik -U stock -d dbquik -p 5432 -o stock -j 4 -P 4 -y

options:
-d dbname      : database name for import
-h hostname   : hostname of the PostgreSQL server (default: unix socket)
-j cores           : number of connection to use to import data or indexes into PostgreSQL
-o username   : owner of the database to create
-P cores          : number of tables to process at same time for data import
-U username  : username to connect to PostgreSQL (default: peer username)
-s                    : import schema only, do not try to import data
-y                    : reply Yes to all questions for automatic import

                                                   <<< Back   Next >>>