sourCEntral - mobile manpages

pdf

Ora2Pg

NAME

Ora2Pg − Oracle to PostgreSQL database schema converter

SYNOPSIS

Ora2pg has a companion script called ora2pg. When use in conjonction with a custom version of ora2pg.conf they perform what I’m trying to explain bellow. See content of the ora2pg.conf file for more explanation on configuration directives.

        use Ora2Pg;
        # Create an instance of the Ora2Pg Perl module
        my $schema = new Ora2Pg (config => './ora2pg.conf');
        # Create a PostgreSQL representation of Oracle export
        # you've defined in ora2pg.conf.
        $schema−>export_schema();
        exit(0);

You can always overwrite any configuration option set in ora2pg.conf by passing a hash where keys are the same that in configuration file but in lower case. For example, if you only want to extract only a selection of tables:

        my @tables = ('t1', 't2', 't3');
        my $schema = new Ora2Pg (
                datasource => $dbsrc,   # Oracle DBD datasource
                user => $dbuser,        # Database user
                password => $dbpwd,     # Database password
                tables => \@tables,
        # or
        #       tables => [('tab1','tab2')],  # Tables to extract
                debug => 1                    # Verbose running.
        );

or if you only want to extract only the first 10 tables:

        my $schema = new Ora2Pg (
                datasource => $dbsrc,   # Oracle DBD datasource
                user => $dbuser,        # Database user
                password => $dbpwd,     # Database password
                max => 10               # First 10 tables to extract
        );

or if you only want to extract tables 10 to 20:

        my $schema = new Ora2Pg (
                datasource => $dbsrc,   # Database DBD datasource
                user => $dbuser,        # Database user
                password => $dbpwd,     # Database password
                min => 10,              # Begin extraction at rank 10
                max => 20               # End extraction at rank 20
        );

Setting showtableid to 1 will display a table and its ranks without any extraction. This will help you to set values of min/max options.

To choose a particular Oracle schema to export just set the following option to your schema name:

        schema => 'APPS'

This schema definition can also be needed when you want to export data. If export fails and complaining that the table doesn’t exists use this directive to prefix the table name by the schema name.

If you want to use PostgreSQL 7.4+ schema support set the init option ’export_schema’ set to 1. Default is no schema export.

To know at which ranks tables can be found during the extraction use the option:

        showtableid => 1

You can process multiple types of extractions at the same time by setting the value to a space separated list of the following keywords.

To extract all views set the type option as follows:

        type => 'VIEW'

To extract all grants set the type option as follows:

        type => 'GRANT'

To extract all sequences set the type option as follows:

        type => 'SEQUENCE'

To extract all triggers set the type option as follows:

        type => 'TRIGGER'

To extract all functions set the type option as follows:

        type => 'FUNCTION'

To extract all procedures set the type option as follows:

        type => 'PROCEDURE'

To extract all packages and packages bodies set the type option as follows:

        type => 'PACKAGE'

Default is table extraction:

        type => 'TABLE'

To extract tablespaces (PostgreSQL >= v8):

        type => 'TABLESPACE'

To extract table range or list partition (PostgreSQL >= v8.4):

        type => 'PARTITION'

To extract user defined Oracle type

        type => 'TYPE'

To extract table datas as INSERT statements use:

        type => 'DATA'

To extract table datas as COPY statements use:

        type => 'COPY'

and set data_limit => n to specify the max tuples to return. If you set this options to 0 or nothing, no limitation are used. Additional options ’table’, ’min’ and ’max’ can also be used. This is useful only when data is send to Pg backend directly, but not when when dumping to file.

Oracle export is done by calling method:

        $schema−>export_schema();

The extracted data is dumped to filename specified in the OUTPUT configuration directive or to stdout if it’s set to nothing. You can always overwrite this configuration value by specifying a filename as argument of this function.

You can also send the data directly to a PostgreSQL backend by setting PG_DSN , PG_USER and PG_PWD configuration directives. This feature is only available for COPY or DATA export types. The data will not be sent via DBD::Pg but will be loaded to the PG database using the psql command. Edit the $PSQL environment variable to specify the path of your psql command (nothing to edit if psql is in your path).

When copying tables, Ora2Pg normally exports constraints as they are; if they are non-deferrable they will be exported as non-deferrable. However, non-deferrable constraints will probably cause problems when attempting to import data to PostgreSQL. The option:

       fkey_deferrable => 1

will cause all foreign key constraints to be exported as deferrable, even if they are non-deferrable. In addition, setting:

       defer_fkey => 1

when exporting data will add a command to actually defer all constraints before importing data.

To non perl gurus, you can use the configuration file and run ora2pg as is. You will find all information into the ora2pg.conf to be able to set it correctly.

DESCRIPTION

Ora2Pg is a perl OO module used to export an Oracle database schema to a PostgreSQL compatible schema.

It simply connects to your Oracle database, extracts its structures and generates an SQL script that you can load into your PostgreSQL database.

Ora2Pg.pm dumps the database schema (tables, views, sequences, indexes, grants, etc.), with primary, unique and foreign keys into PostgreSQL syntax without need to edit the SQL code generated.

It can also dump Oracle data into a PostgreSQL database ’on the fly’. Also you can choose a selection of columns to be exported for each table.

The SQL and/or PL/SQL code generated for functions, procedures and triggers has to be reviewed to match the PostgreSQL syntax. You find some useful recommandations on porting Oracle PL/SQL code to PostgreSQL PL/PGSQL at "http://techdocs.postgresql.org/" under the topic "Converting from other Databases to PostgreSQL", Oracle.

Notice that the trunc() function in Oracle is the same for number and date types. Be carefull when porting to PostgreSQL to use trunc() for numbers and date_trunc() for dates.

ABSTRACT

The goal of the Ora2Pg Perl module is to cover everything needed to export an Oracle database to a PostgreSQL database without other thing than providing the parameters needed for connecting to the Oracle database.

Features include:

        − Exporting the database schema (tables, views, sequences, indexes),
          with unique, primary and foreign key and check constraints.
        − Exporting grants/privileges for users and groups.
        − Exporting range and list table partition.
        − Exporting a table selection (by specifying the table names or max
          tables).
        − Exporting the Oracle schema to a PostgreSQL 7.3+ schema.
        − Exporting predefined functions/triggers/procedures/packages.
        − Exporting user defined data type.
        − Exporting table data.
        − Exporting Oracle views as PG tables.
        − Providing basic help for converting PLSQL code to PLPGSQL (needs
          manual work).

See ora2pg.conf for more information on use.

My knowledge about database is really poor especially for Oracle RDBMS . Any contributions, particularly in this matter, are welcome.

REQUIREMENTS

You just need the DBI , DBD::Pg and DBD::Oracle Perl module to be installed. DBD::Pg is optional and needed only for ’on the fly’ migration. The PostgreSQL client (psql) must also be installed on the host running Ora2Pg.

If you want to compress output as a gzip file you need Compress::Zlib Perl module. And if you want to use bzip2 compression, program bzip2 must be available.

PUBLIC METHODS

new HASH_OPTIONS
Creates a new Ora2Pg object.

The only required option is:

    − config : Path to the configuration file (required).

All directives found in the configuration file can be overwritten in the instance call by passing them in lowercase as arguments. These supported options are (See ora2pg.conf for more details):

    − datasource : Oracle DBD datasource (required)
    − user : Oracle DBD user (optional with public access)
    − password : Oracle DBD password (optional with public access)
    − schema : Oracle internal schema to extract (optional)
    − type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE,
      TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE,TABLESPACE,PARTTION
      or a combinaison of these keywords separated by blanks.
    − debug : verbose mode.
    − export_schema : Export Oracle schema to PostgreSQL >7.3 schema
    − tables : Extract only the specified tables (arrayref) and set the
      extracting order
    − exclude : Exclude the specified tables from extraction (arrayref)
    − showtableid : Display only the table ranks during extraction
    − min : Table rank to begin extraction at. Defaults to 0
    − max : Table rank to end extraction at. Defaults to 0 meaning no limits
    − data_limit : Max number of tuples to return during data extraction
      (defaults to 0 meaning no limit)
    − case_sensitive: Allow to preserve Oracle object names as they are
      written. Default is not.
    − skip_fkeys : Skip foreign key constraints extraction. Defaults to 0
      (extraction)
    − skip_pkeys : Skip primary keys extraction. Defaults to 0 (extraction)
    − skip_ukeys : Skip unique column constraints extraction. Defaults to 0
      (extraction)
    − skip_indices : Skip all other index types extraction. Defaults to 0
      (extraction)
    − skip_checks : Skip check constraints extraction. Defaults to 0
      (extraction)
    − keep_pkey_names : By default, primary key names in the source database
      are ignored, and default key names are created in the target database.
      If this is set to true, primary key names are preserved.
    − bzip2: Path to the Bzip2 program to compress data export. Default
      /usr/bin/bzip2
    − gen_user_pwd : When set to 1 this will replace the default password
      'change_my_secret' with a random string.
    − fkey_deferrable: Force foreign key constraints to be exported as
      deferrable. Defaults to 0: export as is.
    − defer_fkey : Force all foreign key constraints to be deferred during
      data import. Defaults to 0: export as is.
    − pg_numeric_type: Convert the Oracle NUMBER data type to adequate PG data
      types instead of using the slow numeric(p,s) data type.
    − default_numeric: By default the NUMBER(x)type without precision is
      converted to float. You can overwrite this data type by any PG type.
    − keep_pkey_names: Preserve oracle primary key names. The default is to
      ignore and use PostgreSQl defaults.
    − pg_supports_inout: Allow PG support of in/out/inout function parameters
      Must be used with PostgreSQL > 8.1. Defaults to none support (backward
      compatibility).
    − pg_supports_role: Allow PG support of roles instead of user/group.
      Defaults to none support (backward compatibility).
    − disable_triggers: Disable triggers on all tables in COPY and
      DATA mode.
    − disable_sequence: Disables alter sequence on all tables in COPY or
      DATA mode.
    − noescape: Disable character escaping during data export.
    − datatype: Redefine Oracle to PostgreSQl data type conversion.
    − binmode: Force Perl to use the specified binary mode for output. The
      default is ':raw';
    − sysusers: Add other system users to the default exclusion list
      (SYS,SYSTEM,DBSNMP,OUTLN,PERFSTAT,CTXSYS,XDB,WMSYS,SYSMAN,SQLTXPLAIN,
      MDSYS,EXFSYS,ORDSYS,DMSYS,OLAPSYS).
    − ora_sensitive: Force the use of Oracle case sensitive table/view names.
    − plsql_pgsql: Enable plsql to plpgsql conversion.
    − pg_schema: Allow to specify a coma delimited list of PostgreSQL schema.

Beware that this list may grow longer because all initialization is performed this way.

Special configuration options to handle character encoding: −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

NLS_LANG

If you experience any issues where mutibyte characters are being substituted with replacement characters during the export try to set the NLS_LANG configuration directive to the Oracle encoding. This may help a lot especially with UTF8 encoding.

BINMODE

If you experience the Perl warning: "Wide character in print", it means that you tried to write a Unicode string to a non-unicode file handle. You can force Perl to use binary mode for output by setting the BINMODE configuration option to the specified encoding. If you set it to ’utf8’, it will force printing like this: binmode OUTFH , ":utf8"; By default Ora2Pg opens the output file in ’raw’ binary mode.

Exporting Oracle views as PostgreSQL tables: −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

Since version 4.10 you can export Oracle views as PostgreSQL tables simply by setting TYPE configuration option to TABLE and COPY or DATA and specifying your views in the TABLES configuration option. Then if Ora2Pg does not find the name in Oracle table names it automatically deduces that it must search for it in the view names, and if it finds the view it will extract its schema (if TYPE=TABLE) into a PG create table form, then it will extract the data (if TYPE=COPY or DATA ) following the view schema.

Case sensitive table names in Oracle: −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

Since version 4.10 you can extract/export Oracle databases with case sensitive table/view names. This requires the use of quoted table/view names during Oracle querying. Set the configuration option ORA_SENSITIVE to 1 to enable this feature. By default it is off.

export_data FILENAME
OBSOLETE:
you must use export_schema instead. Still here for backward compatibility. It simply callback export_schema().

export_schema FILENAME
Print SQL data output to a file name or to STDOUT if no file name is specified.

export_file FILENAME
Open a file handle to a given filename.

close_export_file FILEHANDLE
Close a file handle.

modify_struct TABLE_NAME ARRAYOF_FIELDNAME
Modify the table structure during the export. Only the specified columns will be exported.

replace_tables HASH
Modify table names during the export.

replace_cols HASH
Modify column names during the export.

set_where_clause HASH
Add a WHERE clause during data export on specific tables or on all tables

PRIVATE METHODS

_init HASH_OPTIONS
Initialize an Ora2Pg object instance with a connexion to the Oracle database.

_send_to_pgdb DEST_DATASRC DEST_USER DEST_PASSWD
Open a DB handle to a PostgreSQL database

_grants
This function is used to retrieve all privilege information.

It extracts all Oracle’s ROLES to convert them to Postgres groups (or roles) and searches all users associated to these roles.

_sequences
This function is used to retrieve all sequences information.

_triggers
This function is used to retrieve all triggers information.

_functions
This function is used to retrieve all functions information.

_packages
This function is used to retrieve all packages information.

_types
This function is used to retrieve all custom types information.

_tables
This function is used to retrieve all table information.

Sets the main hash of the database structure $self−>{tables}. Keys are the names of all tables retrieved from the current database. Each table information is composed of an array associated to the table_info key as array reference. In other way:

    $self−>{tables}{$class_name}{table_info} = [(OWNER,TYPE)];

DBI TYPE can be TABLE , VIEW , SYSTEM TABLE , GLOBAL TEMPORARY , LOCAL TEMPORARY , ALIAS , SYNONYM or a data source specific type identifier. This only extracts the TABLE type.

It also gets the following information in the DBI object to affect the main hash of the database structure :

    $self−>{tables}{$class_name}{field_name} = $sth−>{NAME};
    $self−>{tables}{$class_name}{field_type} = $sth−>{TYPE};

It also calls these other private subroutines to affect the main hash of the database structure :

    @{$self−>{tables}{$class_name}{column_info}} = $self−>_column_info($class_name, $owner);
    %{$self−>{tables}{$class_name}{unique_key}}  = $self−>_unique_key($class_name, $owner);
    @{$self−>{tables}{$class_name}{foreign_key}} = $self−>_foreign_key($class_name, $owner);
    %{$self−>{tables}{$class_name}{check_constraint}}  = $self−>_check_constraint($class_name, $owner);

_views
This function is used to retrieve all views information.

Sets the main hash of the views definition $self−>{views}. Keys are the names of all views retrieved from the current database and values are the text definitions of the views.

It then sets the main hash as follows:

    # Definition of the view
    $self−>{views}{$table}{text} = $view_infos{$table};

_tablespaces
This function is used to retrieve all Oracle Tablespaces information.

Sets the main hash $self−>{tablespaces}.

_partitions
This function is used to retrieve all Oracle partition information.

Sets the main hash $self−>{partition}.

_get_sql_data
Returns a string containing the entire PostgreSQL compatible SQL Schema definition.

_extract_sequence_info
This function retrieves the last value returned from the sequences in the Oracle database. The result is a SQL script assigning the new start values to the sequences found in the Oracle database.

_get_data TABLE
This function implements an Oracle-native data extraction.

Returns a list of array references containing the data

_sql_type INTERNAL_TYPE LENGTH PRECISION SCALE
This function returns the PostgreSQL datatype corresponding to the Oracle data type.

_column_info TABLE OWNER
This function implements an Oracle-native column information.

Returns a list of array references containing the following information elements for each column the specified table

[(
column name,
column type,
column length,
nullable column,
default value )]

_unique_key TABLE OWNER
This function implements an Oracle-native unique (including primary) key column information.

Returns a hash of hashes in the following form:
( constraintname => (type => ’ PRIMARY ’,
columns => (’a’, ’b’, ’c’)),
constraintname => (type => ’ UNIQUE ’,
columns => (’b’, ’c’, ’d’)),
etc.
)

_check_constraint TABLE OWNER
This function implements an Oracle-native check constraint information.

Returns a hash of lists of all column names defined as check constraints for the specified table and constraint name.

_foreign_key TABLE OWNER
This function implements an Oracle-native foreign key reference information.

Returns a list of hash of hash of array references. Ouf! Nothing very difficult. The first hash is composed of all foreign key names. The second hash has just two keys known as ’local’ and ’remote’ corresponding to the local table where the foreign key is defined and the remote table referenced by the key.

The foreign key name is composed as follows:

    'local_table_name−>remote_table_name'

Foreign key data consists in two arrays representing at the same index for the local field and the remote field where the first one refers to the second one. Just like this:

    @{$link{$fkey_name}{local}} = @local_columns;
    @{$link{$fkey_name}{remote}} = @remote_columns;

_get_users
This function implements an Oracle-native users information.

Returns a hash of all users as an array.

_get_roles
This function implements an Oracle-native roles information.

Returns a hash of all groups (roles) as an array of associated users.

_get_all_roles
This function retrieves all Oracle roles information.

Returns a hash of all roles as an array of associated users.

_get_all_grants
This function implements an Oracle-native user privilege information.

Returns a hash of all grants as an array of associated users.

_get_indexes TABLE OWNER
This function implements an Oracle-native indexes information.

Returns a hash of an array containing all unique indexes and a hash of array of all indexe names which are not primary keys for the specified table.

_get_sequences
This function implements an Oracle-native sequences information.

Returns a hash of an array of sequence names with MIN_VALUE , MAX_VALUE , INCREMENT and LAST_NUMBER for the specified table.

_get_views
This function implements an Oracle-native views information.

Returns a hash of view names with the SQL queries they are based on.

_alias_info
This function implements an Oracle-native column information.

Returns a list of array references containing the following information for each alias of the specified view:

[(
column name,
column id )]

_get_triggers
This function implements an Oracle-native triggers information.

Returns an array of refarray of all triggers information.

_get_functions
This function implements an Oracle-native functions information.

Returns a hash of all function names with their PLSQL code.

_get_packages
This function implements an Oracle-native packages information.

Returns a hash of all package names with their PLSQL code.

_get_types
This function implements an Oracle custom types information.

Returns a hash of all type names with their code.

_table_info
This function retrieves all Oracle-native tables information.

Returns a handle to a DB query statement.

_get_tablespaces
This function implements an Oracle-native tablespaces information.

Returns a hash of an array of tablespace names with their system file path.

_get_schema
This function returns 1 if the requested schema is found in the database, otherwise 0.

_get_partitions
This function implements an Oracle-native partitions information. Return two hash ref with partition details and partition default.

escape
This function escape all special characters in a specified string.

Returns the escaped string.

dump
This function dump data to the right output (gzip file, file or stdout).

dumptofile
This function dump data to a given file handle.

read_config
This function read the specified configuration file.

_convert_package
This function is used to rewrite Oracle PACKAGE code to PostgreSQL SCHEMA . Called only if PLSQL_PGSQL configuration directive is set to 1.

_restore_comments
This function is used to restore comments into SQL code previously remove for easy parsing

_remove_comments
This function is used to remove comments from SQL code to allow easy parsing

_convert_function
This function is used to rewrite Oracle FUNCTION code to PostgreSQL. Called only if PLSQL_PGSQL configuration directive is set to 1.

_convert_declare
This function is used to rewrite Oracle FUNCTION declaration code to PostgreSQL. Called only if PLSQL_PGSQL configuration directive is set to 1.

_format_view
This function is used to rewrite Oracle VIEW declaration code to PostgreSQL.

randpattern
This function is used to replace the use of perl module String::Random and is simply a cut & paste from this module.

logit
This function log information to STDOUT or to a logfile following a debug level. If critical is set, it dies after writing to log.

_convert_type
This function is used to rewrite Oracle PACKAGE code to PostgreSQL SCHEMA . Called only if PLSQL_PGSQL configuration directive is set to 1.

AUTHOR

Gilles Darold <gilles _AT_ darold _DOT_ net>

COPYRIGHT

Copyright (c) 2000−2010 Gilles Darold − All rights reserved.

        This program is free software: you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation, either version 3 of the License, or
        any later version.
        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.
        You should have received a copy of the GNU General Public License
        along with this program. If not, see < http://www.gnu.org/licenses/ >.

SEE ALSO

DBD::Oracle, DBD::Pg

pdf