Postgres Pro
Downloads
Chapter 4. Configuration
Prev   Next

Chapter 4. Configuration

Table of Contents

Usage
Oracle Database Connection
Data Encryption with Oracle Server
Testing Connection
Troubleshooting
Oracle Schema to Export
Export Type
Limiting Objects to Export
Control of Full Text Search Export
Modifying Object Structure
Oracle Spatial to PostGIS
Postgres Pro Import
Column Type Control
Taking Export Under Control
Special Options to Handle Character Encoding
PL/SQL-to-PL/pgSQL Conversion
Other Configuration Directives

ora2pgpro configuration can be as simple as choosing the Oracle database to export and choosing the export type.

The full control of the Oracle database migration is managed in a single configuration file named ora2pgpro.conf. The format of this file consists of a directive name in upper case followed by a tab character and a value. Comments are lines beginning with a #. There is no specific order to place the configuration directives, they are set at the time they are read in the configuration file.

For configuration directives that just take a single value, you can use them multiple times in the configuration file but only the last occurrence found in the file will be used. For configuration directives that allow a list of values, you can use it multiple times, the values will be appended to the list. If you use the IMPORT directive to load a custom configuration file, directives defined in this file will be stored from the place the IMPORT directive is found, so it is better to put it at the end of the configuration file.

Values set in the command line options override values from the configuration file.

Usage

First of all, make sure that libraries and binaries path include the Oracle Instant Client installation: 

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export PATH="/usr/lib/oracle/11.2/client64/bin:$PATH"

By default, ora2pgpro looks for /etc/ora2pgpro/ora2pgpro.conf configuration file, if the file exists, you can simply execute: 

/usr/local/bin/ora2pgpro

If you want to call another configuration file, give the path as the command-line argument: 

/usr/local/bin/ora2pgpro -c /etc/ora2pgpro/new_ora2pgpro.conf

Here are all command-line parameters available when using ora2pgpro: 

ora2pgpro [-dhpqv --estimate_cost --dump_as_html] [--option	value]
-a, --allow

Comma-separated list of objects to allow from export. Can be used with SHOW_COLUMN too.

-b, --basedir

Set the default output directory where files resulting from exports will be stored.

-c, --conf

Set an alternative configuration file other than the default /etc/ora2pgpro/ora2pgpro.conf.

-C, --cdc_file

File used to store/read SCN per table during export. Default: TABLES_SCN.log in the current directory. This is the file written by the --cdc_ready option.

-d, --debug

Enable verbose output.

-D, --data_type

Allow custom type replacement in command line.

-e, --exclude

Comma-separated list of objects to exclude from export. Can be used with SHOW_COLUMN too.

-h, --help

Print this short help.

-g, --grant_object

Extract privilege for the given object type. See possible values in GRANT_OBJECT configuration.

-i, --input

File containing Oracle PL/SQL code to convert with no Oracle database connection initiated.

-j, --jobs

Number of parallel processes to send data to Postgres Pro.

-J, --copies

Number of parallel connections to extract data from Oracle.

-l, --log

Set a log file. Default is stdout.

-L, --limit

Number of tuples extracted from Oracle and stored in memory before writing, default: 10000.

-n, --namespace

Set the Oracle schema to extract from.

-N, --pg_schema

Set Postgres Pro search_path.

-o, --out

Set the path to the output file where SQL will be written. Default: output.sql in running directory.

-p, --plsql

Enable PL/SQL to PL/pgSQL code conversion.

-P, --parallel

Number of parallel tables to extract at the same time.

-q, --quiet

Disable progress bar.

-r, --relative

Use \ir instead of \i in the psql scripts generated.

-s, --source

Set the Oracle DBI data source.

-S, --scn

Set the Oracle System Change Number (SCN) to use to export data. It will be used in the WHERE clause to get the data. It is used with COPY or INSERT.

-t, --type

Set the export type. It will override the one given in the configuration file (TYPE).

-T, --temp_dir

Set a distinct temporary directory when two or more ora2pgpro instances run in parallel.

-u, --user

Set the Oracle database connection user. ORA2PG_USER environment variable can be used instead.

-v, --version

Show ora2pgpro version and exit.

-w, --password

Set the password of the Oracle database user. ORA2PG_PASSWD environment variable can be used instead.

-W, --where

Set the WHERE clause to apply to the Oracle query to retrieve data. Can be used multiple times.

--forceowner

Force ora2pgpro to set tables and sequences owner like in Oracle database. If the value is set to a username, this one will be used as the objects owner. By default it is the user used to connect to the Postgres Pro database that will be the owner.

--nls_lang

Set the Oracle NLS_LANG client encoding.

--client_encoding

Set the Postgres Pro client encoding.

--view_as_table

Comma-separated list of views to export as table.

--estimate_cost

Activate the migration cost evaluation with SHOW_REPORT.

--cost_unit_value

Number of minutes for a cost evaluation unit. Default: 5 minutes, corresponds to a migration conducted by a Postgres Pro expert. Set it to 10 if this is your first migration.

--dump_as_html

Force ora2pgpro to dump report in HTML, used only with SHOW_REPORT. Default is to dump report as simple text.

--dump_as_csv

As above but force ora2pgpro to dump report in CSV.

--dump_as_sheet

Report migration assessment with one CSV line per database.

--init_project

Initialize a typical ora2pgpro project tree. Top directory will be created under the project base directory.

--project_base

Define the base directory for ora2pgpro project trees. Default is the current directory.

--print_header

Used with --dump_as_sheet to print the CSV header, especially for the first run of ora2pgpro.

--human_days_limit

Set the number of man-days limit where the migration assessment level switches from B to C. Default is set to 5 man-days.

--audit_user

Comma-separated list of usernames to filter queries in the AUDIT_USER table. Used only with SHOW_REPORT and QUERY export type.

--pg_dsn

Set the data source to Postgres Pro for direct import.

--pg_user

Set the Postgres Pro user to be used.

--pg_pwd

Set the Postgres Pro password to be used.

--count_rows

Force ora2pgpro to perform a real row count in TEST, TEST_COUNT, and SHOW_TABLE actions.

--no_header

Do not append ora2pgpro header to output file.

--oracle_speed

Use to know at which speed Oracle is able to send data. No data will be processed or written.

--ora2pg_speed

Use to know at which speed ora2pgpro is able to send transformed data. Nothing will be written.

--blob_to_lo

Export BLOBs as large objects, can only be used with action SHOW_COLUMN, TABLE, and INSERT.

--cdc_ready

Use current SCN per table to export data and register them into a file named TABLES_SCN.log by default. It can be changed using -C|--cdc_file.

--lo_import

Use psql \lo_import command to import BLOBs as large objects. Can be used to import data with COPY and import the large object manually in a second pass. It is required for BLOBs larger than 1GB.

--mview_as_table

Comma-separated list of materialized views to export as regular tables.

--drop_if_exists

Drop the object before creation if it exists.

--offline

Convert exported data without the connection to the Oracle database.

ora2pgpro returns 0 on success, 1 on error. It returns 2 when a child process has been interrupted and you received the warning message: “WARNING: an error occurs during data export. Please check what's happen.” Most of the time this is an OOM issue, first try reducing DATA_LIMIT value.

Note that performance might be improved by updating stats on Oracle: 

DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_DATABASE_STATS
DBMS_STATS.GATHER_DICTIONARY_STATS

The two options --project_base and --init_project, when used, indicate that ora2pgpro has to create a project template with a work tree, a configuration file and a script to export all objects from the Oracle database. Here is a sample of the command usage: 

ora2pgpro --project_base /app/migration/ --init_project test_project
	Creating project test_project.
	/app/migration/test_project/
		schema/
			dblinks/
			directories/
			functions/
			grants/
			mviews/
			packages/
			partitions/
			procedures/
			sequences/
			synonyms/
			tables/
			tablespaces/
			triggers/
			types/
			views/
		sources/
			functions/
			mviews/
			packages/
			partitions/
			procedures/
			triggers/
			types/
			views/
		data/
		config/
		reports/

	Generating generic configuration file
	Creating script export_schema.sh to automate all exports.
	Creating script import_all.sh to automate all imports.

It creates a generic configuration file where you define the Oracle database connection and a shell script called export_schema.sh. The sources/ directory will contain the Oracle code, the schema/ will contain the code ported to Postgres Pro. The reports/ directory will contain the HTML reports with the migration cost assessment.

If you want to use your own default configuration file, use the -c option to give the path to that file. Rename it with the .dist suffix if you want ora2pgpro to apply the generic configuration values, the configuration file will be copied untouched otherwise.

Once you have set the connection to the Oracle database, you can execute the script export_schema.sh that will export all object type from your Oracle database and output DDL files into the schema subdirectories. At end of the export, it will give you the command to export data later when the import of the schema will be done and verified.

You can choose to load the DDL files generated manually or use the second script import_all.sh to import those files interactively. If this kind of migration is not currently in progress for you, it is recommended you to use those scripts.

Prev   Next
Supported Distributions Home Oracle Database Connection
pdf