Oracle Connection

Oracle database connection

There are 5 configuration directives to control the access to the Oracle database.

• ORACLE_HOME

  Used to set the ORACLE_HOME environment variable for the Oracle libraries required by the DBD::Oracle Perl module.

• ORACLE_DSN

  This directive is used to set the data source name in the standard DBI DSN form. For example:

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

or

dbi:Oracle:DB_SID

On 18c this could be for example:

dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521

For the second notation, the SID should be declared in the well-known file $ORACLE_HOME/network/admin/tnsnames.ora or in the path given to the TNS_ADMIN environment variable.

For MySQL the DSN will look like this:

dbi:mysql:host=192.168.1.10;database=sakila;port=3306

The 'sid' part is replaced by 'database'.

For MS SQL Server it will look like this:

dbi:ODBC:driver=msodbcsql18;server=mydb.database.windows.net;database=testdb;TrustServerCertificate=yes

• ORACLE_USER and ORACLE_PWD

  These two directives are used to define the user and password for the Oracle database connection. Note that if possible, it is better to login as Oracle super admin to avoid grant problems during the database scan and ensure nothing is missing.

  If you do not supply credentials with ORACLE_PWD and you have installed the Term::ReadKey Perl module, Ora2Pg will ask for the password interactively. If ORACLE_USER is not set it will also be asked interactively.

  To connect to a local ORACLE instance with connections "as sysdba" you have to set ORACLE_USER to "/" and an empty password.

  To make a connection using an Oracle Secure External Password Store (SEPS), first configure the Oracle Wallet and then set both the ORACLE_USER and ORACLE_PWD directives to the special value of "__SEPS__" (without the quotes but with the double underscore).

• USER_GRANTS

  Set this directive to 1 if you connect to Oracle database as a simple user and do not have enough grants to extract things from the DBA_... tables. It will use ALL_... tables instead.

  Warning: if you use export type GRANT, you must set this configuration option to 0 or it will not work.

• TRANSACTION

  This directive may be used if you want to change the default isolation level of the data export transaction. Default is now to set the level to a serializable transaction to ensure data consistency. The allowed values for this directive are:

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

Releases before 6.2 used to set the isolation level to READ ONLY transaction but in some cases this was breaking data consistency so now default is set to SERIALIZABLE.

• INPUT_FILE

  This directive does not control the Oracle database connection but rather it purely disables the use of any Oracle database by accepting a file as argument. Set this directive to a file containing PL/SQL Oracle Code like function, procedure or full package body to prevent Ora2Pg from connecting to an Oracle database and just apply its conversion tool to the content of the file. This can be used with most export types: TABLE, TRIGGER, PROCEDURE, VIEW, FUNCTION or PACKAGE, etc.

• ORA_INITIAL_COMMAND

  This directive can be used to send an initial command to Oracle, just after the connection. For example to unlock a policy before reading objects or to set some session parameters. This directive can be used multiple times.

Data encryption with Oracle server

If your Oracle Client config file already includes the encryption method, then DBD::Oracle uses those settings to encrypt the connection while extracting data. For example, if you have configured the Oracle Client config file (sqlnet.ora or .sqlnet) with the following information:

# 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'

Any tool that uses the Oracle client to communicate with the database will have encrypted connections if you setup session encryption as shown above.

For example, Perl's DBI uses DBD::Oracle, which uses the Oracle client for actual database communication. If the Oracle client installation used by Perl is setup to request encrypted connections, then your Perl connection to an Oracle database will also be encrypted.

Full details at https://kb.berkeley.edu/jivekb/entry.jspa?externalID=1005

Testing connection

Once you have set the Oracle database DSN, you can execute ora2pg to see if it works:

ora2pg -t SHOW_VERSION -c config/ora2pg.conf

will show the Oracle database server version. Take some time here to test your installation as most problems occur here. The other configuration steps are more technical.

Troubleshooting

If the output.sql file hasn't exported anything other than the PostgreSQL transaction header and footer, there are two possible reasons: 1) The perl script ora2pg dumps an ORA-XXX error, which means that your DSN or login information is wrong - check the error and your settings and try again. 2) The perl script says nothing and the output file is empty: the user lacks permissions to extract something from the database. Try to connect to Oracle as super user or review the USER_GRANTS directive above and the next section, especially the SCHEMA directive.

• LOGFILE

  By default, all messages are sent to the standard output. If you provide a file path to this directive, all output will be appended to this file.