Ora2Pg tool introduction

Ora2Pg is a free tool for migrating Oracle or MySQL databases to PostgreSQL. It connects to the Oracle database, performs automatic scanning and extracts the structure and data of the database, and then generates the corresponding SQL script. These scripts can be used to import database structure and data into PostgreSQL.

Ora2Pg can be used in a variety of scenarios, from reverse engineering of Oracle databases to migration of large enterprise databases, or simply copying some Oracle data to a PostgreSQL database. It is very simple and easy to use, does not even require any knowledge of the Oracle database, only need to provide configuration parameters to connect to the Oracle database.

Ora2Pg consists of a Perl script (ora2pg) and a Perl module ( Ora2Pg.pm ). The only thing that needs to be done is to modify its configuration file ora2pg.conf, set the DSN to connect to the Oracle database and an optional schema name. After completion, only need to set the type of export: TABLE (including constraints), VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT or COPY, FDW, QUERY, KETTLE and SYNONYM.

By default, Ora2Pg exports a file, which can be loaded into the PostgreSQL database through the psql client; however, you can also set a database DSN in the configuration file and directly import it into the PostgreSQL database. Through the configuration options in ora2pg.conf, you can control the content and method of export.

The tool supports the following functions:

• Export the complete database schema (tables, views, sequences, indexes), including unique builds, primary keys, foreign keys, and check constraints.
• Import user/user group authorizations/authorities.
• Export range/list/hash partitions and their subpartitions.
• Export the specified table (by specifying the table name).
• Export Oracle mode to PostgreSQL 8.4+ mode.
• Export predefined functions, triggers, procedures, packages and package bodies.
• Export the data in the table, support adding WHERE condition to export the specified data.
• Support for exporting Oracle BLOB objects as PG BYTEA data.
• Export the Oracle view as a table in PG.
• Export Oracle user-defined types.
• Provide some basic PLSQL code to PLPGSQL code conversion.
• Support various platforms.
• Export Oracle data tables as FDW external tables.
• Export materialized views.
• Display detailed report about Oracle database.
• Oracle database migration cost assessment.
• Oracle database migration difficulty assessment.
• Cost estimates for migrating PL/SQL code from files.
• Cost estimates for migrating Oracle SQL queries from files.
• Generate ktr files in XML format for Pentaho Data Integrator (Kettle).
• Import Oracle Locator and spatial geometry data into PostGIS.
• Export DBLINK to Oracle FDW.
• Export SYNONYMS as a view.
• Export DIRECTORY as a directory for external tables or external_file plugins.
• A complete MySQL export similar to an Oracle database.
• Process multiple SQL commands concurrently through multiple PostgreSQL connections.
• Perform the difference test between Oracle and PostgreSQL database.

Ora2Pg tries to automatically convert the Oracle database to a PostgreSQL database, but it still needs to do some work manually. Oracle specific PL/SQL code, including functions, procedures, packages, and triggers, need to be checked to confirm that they can conform to the PostgreSQL syntax standard after conversion. You can find some useful information about porting Oracle PL/SQL code to PostgreSQL PL/PGSQL code in the Oracle section of the “ Converting from other Databases to PostgreSQL “ page.

Use Ora2Pg

By default, Ora2Pg will look for the configuration file /etc/ora2pg/ora2pg.conf; if this file exists, you only need to execute the following command:

/usr/local/bin/ora2pg

For Windows™ systems, you can run the ora2pg.bat file in the bin directory of the perl program. Windows™ users can find a configuration template file in the C:\ora2pg directory.

If you want to use other configuration files, you only need to specify them on the command line:

/usr/local/bin/ora2pg -c /etc/ora2pg/new_ora2pg.conf

The following are all command line parameters allowed when using ora2pg:

Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]
a | --allow str: Specify a list of objects allowed to be exported, separated by commas. Can also be used with the SHOW_COLUMN option.
b | --basedir dir: Set the default export directory for storing export results.
c | --conf file: Set a non-default configuration file. The default configuration file is /etc/ora2pg/ora2pg.conf.
-d | --debug: Use debug mode to output more detailed information.
-D | --data_type STR: Set the data type conversion through the command line.
-e | --exclude str: Specify a list of objects to be excluded during export, separated by commas. Can also be used with the SHOW_COLUMN option.
-h | --help: Display help information.
-g | --grant_object type: Export the authorization information on the object of the specified type. For the value, see GRANT_OBJECT configuration item.
-i | --input file: Specify the Oracle PL/SQL code file to be imported. You do not need to connect to the Oracle database when importing the file.
-j | --jobs num: Set the number of concurrent processes used to send data to PostgreSQL.
-J | --copies num: Set the number of concurrent connections used to export data from Oracle.
-l | --log file: Set the log file, the default is stdout.
-L | --limit num: When exporting data, the number of records buffered in memory each time before writing to disk, the default value is 10000.
-m | --mysql: Export MySQL database.
-n | --namespace schema: Set the Oracle schema to be exported.
-N | --pg_schema schema: Set the search path search_path in PostgreSQL.
-o | --out file: Set the storage path of the exported SQL file. The default value is the output.sql file in the current directory.
-p | --plsql: Enable conversion of PLSQL code to PLPGSQL code.
-P | --parallel num: Export multiple tables at the same time, set the number of concurrent.
-q | --quiet: Do not display the progress bar.
-s | --source DSN: Set Oracle DBI data source.
-t | --type export: Set the export type. This parameter will override the export type (TYPE) in the configuration file.
-T | --temp_dir DIR: Specify different temporary storage directories for multiple concurrently running ora2pg scripts.
-u | --user name: Set the user name to connect to the Oracle database. You can also use the ORA2PG_USER environment variable.
-v | --version: Display Ora2Pg version information and exit.
-w | --password pwd: Set the user password to connect to the Oracle database. You can also use the ORA2PG_PASSWD environment variable.
--forceowner: When importing data, force ora2pg to set the owner of the tables and sequences imported into PostgreSQL as the user when connecting to the Oracle database. If set to the specified user name, all imported objects belong to the user. By default, the owner of the object is the user connected to the Pg database.
--nls_lang code: Set the NLS_LANG encoding of the Oracle client.
--client_encoding code: Set the PostgreSQL client encoding.
--view_as_table str: Export the view as a table, multiple views are separated by commas.
--estimate_cost: Output migration cost evaluation information in SHOW_REPORT result.
--cost_unit_value minutes: Cost evaluation unit, expressed in minutes. The default value is 5 minutes, which represents the time required for a PostgreSQL expert to migrate. If it is the first migration, it can be set to 10 minutes.
   --dump_as_html: Generate a migration report in HTML format, which can only be used with the SHOW_REPORT option. The default report is a simple text file.
   --dump_as_csv: Same as the previous parameter, but generate a report in CSV format.
   --dump_as_sheet: When generating a migration assessment, generate a row of CSV records for each database.
   --init_project NAME: Create an ora2pg project directory structure. The top-level directory of the project is located under the root directory.
   --project_base DIR: Define the root directory of the ora2pg project. The default is the current directory.
   --print_header: Used with --dump_as_sheet to output CSV header information.
   --human_days_limit num: Set the number of labor days required for the migration evaluation level to be raised from B to C. The default value is 5 labor days.
   --audit_user LIST: Set the user name to be filtered when querying the DBA_AUDIT_TRAIL table. Multiple users are separated by commas. This parameter can only be used for the SHOW_REPORT and QUERY export types.
   --pg_dsn DSN: Set PostgreSQL data source for online import.
   --pg_user name: Set the user name to connect to PostgreSQL.
   --pg_pwd password: Set the user password to connect to PostgreSQL.
   --count_rows: Perform real data row count in TEST mode.
   --no_header: Do not add Ora2Pg header information in the exported file.
   --oracle_speed: Used to test the speed at which Oracle sends data. Does not actually process or write data.
   --ora2pg_speed: Used to test the speed of Ora2Pg to send the converted data. No data will be written.

If the execution is successful, ora2pg returns 0; if an error occurs, it returns 1. If a child process is interrupted, and the user receives a warning message: “WARNING: an error occurs during data export.

Please check what’s happen.”, ora2pg will return 2. In most cases, it is a memory overflow (OOM) problem, you can first try to reduce the value of the DATA_LIMIT parameter.

For developers, you can add custom options in the ora2pg Perl script; because all configuration options in the ora2pg.conf file will be passed to the newly created Ora2Pg object instance in lower case. Refer to the ora2pg code to add custom options.

Note that updating the statistics of the Oracle database may improve the performance of data migration:

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS
  DBMS_STATS.GATHER_DATABASE_STATS 
  DBMS_STATS.GATHER_DICTIONARY_STATS
END;

Generate migration template

Use the options — project_base and — init_project to create a project template for exporting an Oracle database, including various directory structures, a configuration file, and a script for exporting all objects. The following is an example of usage:

ora2pg --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.

The example generates a general configuration file, only need to specify the Oracle database connection information; also generated a script file export_schema.sh. The directory sources/ is used to store the Oracle source code, and the schema/ is used to store the code imported into PostgreSQL. The directory reports/ is used to store html reports generated by migration cost assessment.

If you want to make a custom default configuration file, use the -c option to specify the path of the file. Rename it with .dist suffix if you want ora2pg to apply the generic configuration values ​​otherwise, the configuration file will be copied untouched.

After configuring the Oracle database connection, you can execute the export_schema.sh script to export various object types and write the output DDL file to a subdirectory under schema/. After the export is complete, the script will give commands for exporting the data; you can export the corresponding data after completing and verifying the import of the pattern object.

You can import the generated DDL file manually, or you can use another script import_all.sh to perform an interactive import.

Oracle database connection configuration

The following 5 configuration options can be used to set up an Oracle database connection.

ORACLE_HOME is
used to set the environment variable ORACLE_HOME, which is used by the DBD::Oracle module to find the Oracle shared library.

ORACLE_DSN
sets the data source name in standard DBI DSN format. E.g:

dbi:Oracle:host=oradb_host.myhost.com;sid=DB_SID;port=1521

or:

dbi:Oracle:DB_SID

For the second method, the SID needs to be defined in the tnsnames.ora file in the directory specified by the $ORACLE_HOME/network/admin/tnsnames.ora file or the environment variable TNS_ADMIN.

For MySQL, DSN is defined as follows:

dbi:mysql:host=192.168.1.103;database=pankajconnect;port=3306

The’sid’ is replaced with’database’.

The two options ORACLE_USER and ORACLE_PWD
are used to specify the user name and password to connect to the Oracle database. It should be noted that if possible, it is best to use the Oracle super administrator to connect to avoid problems with access permissions during database scanning and ensure that nothing is missed.

If USER_GRANTS uses a normal user to connect to the Oracle database, and the user does not have permission to access the DBA_… table, set this parameter to 1. The script will use the ALL_… table.

Warning: If you use the GRANT export type, you must set this option to 0 to deny the script cannot be exported.

TRANSACTION
This option is used to modify the default isolation level of data export transactions. The default setting is the serialized transaction isolation level, which is used to ensure the consistency of the exported data. Here are some supported settings:

readonly: 'SET TRANSACTION READ ONLY',
readwrite: 'SET TRANSACTION READ WRITE',
serializable: 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
committed: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED',

The default isolation level before 6.2 is READ ONLY, but this setting will destroy data consistency in some cases, so the default setting is SERIALIZABLE.

INPUT_FILE
This parameter is not used to set up an Oracle database connection. It specifies an input file and disables the Oracle database connection. Setting this parameter to a file containing Oracle PL/SQL code (such as functions, procedures, or packages) can prevent Ora2Pg from connecting to the Oracle database, but from this file and just apply his conversion tool to the content of the file. Options can be used for most export types: TABLE, TRIGGER, PROCEDURE, VIEW, FUNCTION, or PACKAGE, etc.

ORA_INITIAL_COMMAND
This option is used to execute an initialization command after establishing an Oracle database connection. For example, turn off the access restriction policy before reading the object information, or set certain session parameters. This option supports multiple settings.

Oracle data encryption transmission

If the Oracle client is configured with an encrypted connection, DBD:Oracle will also use this encryption method for connection and data transmission. For example, add the following to the Oracle client configuration file (sqlnet.or or .sqlnet):

# Configure encryption of connections to Oracle
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, RC4_256)
SQLNET.CRYPTO_SEED = 'should be 10-70 random characters'

If the above encrypted transmission is set, any tool that uses the Oracle client to connect to the database will use this encryption method.

For example, Perl’s DBI module connects to an Oracle database via DBD-Oracle, which uses an Oracle client to connect to the database. If the Oracle client is configured with an encrypted connection, Perl also uses an encrypted method to connect to the Oracle database.

Connection test

After setting the DSN of the Oracle database, you can execute the following command to test the database connection:

ora2pg -t SHOW_VERSION -c config/ora2pg.conf

The above command will display the version of the Oracle database.

Trouble shooting

If only the header and tail information about Pg transactions is exported in the output.sql file, there are two possible reasons. The ora2pg script outputs an ORA-XXX error, which means that the DSN or username/password is wrong. Check the relevant settings and run the script again. The script does not have any error messages, but the output file is empty: the user lacks permission to access database objects. Try to use super user to connect to Oracle, or check the USER_GRANTS option, and the SCHEMA option below.

LOGFILE
By default, all messages are sent to standard output. If you specify a file path for this option, all output information will be appended to the file.

Thanks for reading this post.