Oracle support
==============

You can use LiveCompare to compare data from an Oracle database against
any number of PostgreSQL or PGD databases.

For example, you can define ``technology = oracle`` in a data
connection. You can then use other settings to define the connection to
Oracle:

-  ``host``

-  ``port``

-  ``service``

-  ``user``

-  ``password``

All other data connections must be PostgreSQL.

Here’s a simple example of comparison between an Oracle database and a
PostgreSQL database:

.. code:: ini

   [General Settings]
   logical_replication_mode = off
   max_parallel_workers = 4
   oracle_user_tables_only = on
   oracle_ignore_unsortable = on
   column_intersection = on
   force_collate = C
   difference_tie_breakers = oracle

   [Oracle Connection]
   technology = oracle
   host = 127.0.0.1
   port = 1521
   service = XE
   user = LIVE
   password = live

   [Postgres Connection]
   technology = postgresql
   dsn = dbname=liveoracle user=william

   [Output Connection]
   dsn = dbname=liveoutput user=william

   [Table Filter]
   schemas = schema_name = live

Here, the ``schema_name`` in Oracle is the user table sandbox. All table
names are schema qualified by default:

-  Postgres: ``<schema_name> . <table_name>``

-  Oracle: ``<user> . <table_name``

You can disable schema-qualified table names by setting
``schema_qualified_table_names = off`` . You can do this only if
``oracle_user_tables_only = on`` . This setting tells LiveCompare to
search only on tables that belong to the Oracle user that’s connected.
When schema-qualified table names is disabled, then on Postgres you need
to have set a default ``search_path`` on your role or configuration. Or,
you can use the connection ``start_query`` parameter to set an
appropriate ``search_path`` , for example:

.. code:: ini

   [General Settings]
   logical_replication_mode = off
   max_parallel_workers = 4
   oracle_user_tables_only = on
   oracle_ignore_unsortable = on
   column_intersection = on
   force_collate = C
   difference_tie_breakers = oracle
   schema_qualified_table_names = off

   [Oracle Connection]
   technology = oracle
   host = 127.0.0.1
   port = 1521
   service = XE
   user = LIVE
   password = live

   [Postgres Connection]
   technology = postgresql
   dsn = dbname=liveoracle user=william
   start_query = SET search_path = myschema1, myschema2, public

   [Output Connection]
   dsn = dbname=liveoutput user=william

   [Table Filter]
   tables = table_name in (mytable1, mytable2)

When ``schema_qualified_table_names = off`` , you can also use
non-qualified table names in ``Table Filter`` , ``Row Filter`` , and
``Column Filter`` .

..  Note::
   The `Output Connection`  is required to write progress and reporting information from LiveCompare.

If you need to compare a PGD database against Oracle, and you want to
take advantage of ``Initial Connection`` , ``node_name`` , and
``replication_sets`` features (described in :ref:`PGD support <EDB Postgres Distributed support>`  ), then you
can point the last data connection to Oracle, like this:

.. code:: ini

   [General Settings]
   logical_replication_mode = bdr
   max_parallel_workers = 4
   oracle_user_tables_only = on
   oracle_ignore_unsortable = on
   column_intersection = on
   force_collate = C
   difference_tie_breakers = oracle

   [Initial Connection]
   dsn = port=5432 dbname=live user=postgres

   [BDR Connection]
   node_name = node1

   [Oracle Connection]
   technology = oracle
   host = 127.0.0.1
   port = 1521
   service = XE
   user = LIVE
   password = live

   [Output Connection]
   dsn = port=5432 dbname=liveoutput user=postgres

   [Table Filter]
   replication_sets = set_name = bdrgroup

You also can compare a whole PGD cluster against a single Oracle
database, for example:

.. code:: ini

   [General Settings]
   logical_replication_mode = bdr
   max_parallel_workers = 4
   oracle_user_tables_only = on
   oracle_ignore_unsortable = on
   column_intersection = on
   force_collate = C
   difference_tie_breakers = oracle
   all_bdr_nodes = on

   [Initial Connection]
   dsn = port=5432 dbname=live user=postgres

   [Oracle Connection]
   technology = oracle
   host = 127.0.0.1
   port = 1521
   service = XE
   user = LIVE
   password = live

   [Output Connection]
   dsn = port=5432 dbname=liveoutput user=postgres

   [Table Filter]
   replication_sets = set_name = bdrgroup

Requirements
------------

LiveCompare works on PostgreSQL databases out-of-the-box. You don’t need
to install any additional software. But to be able to connect to Oracle,
LiveCompare does requires additional software.

Oracle Instant Client
^^^^^^^^^^^^^^^^^^^^^

You need to download and install Oracle Instant Client (or extract it to
a specific folder, depending on the operating system you use):

-  **MacOSX**: Download Oracle Instant Client ( `64-bit <http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html>`_  ) and
   extract in ``~/lib`` ;

-  **Linux**: Download Oracle Instant Client ( `32-bit <http://www.oracle.com/technetwork/topics/linuxsoft-082809.html>`_  ) (
   `64-bit <http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html>`_  ) and install it on your system, then set
   ``LD_LIBRARY_PATH`` ;

-  **Windows**: Download Oracle Instant Client ( `32-bit <http://www.oracle.com/technetwork/topics/winsoft-085727.html>`_  ) (
   `64-bit <http://www.oracle.com/technetwork/topics/winx64soft-089540.html>`_  ) and extract it into the LiveCompare folder.

cx_Oracle Python module
^^^^^^^^^^^^^^^^^^^^^^^

You need the Python module `cx_Oracle <https://oracle.github.io/python-cx_Oracle/>`_  installed and available on your
system so that LiveCompare can connect to an Oracle database.

Currently, cx_Oracle isn’t installable from Linux distribution
repositories, so follow `the instructions on the cx_Oracle website <https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html>`_  to install it on your system.

We recommend executing LiveCompare under the postgres operating system
user. Then you can install the ``cx_Oracle`` module through PIP only for
the ``postgres`` user, using the following command:

::

   pip3 install --user cx_Oracle --upgrade

Differences
-----------

If LiveCompare finds any difference, it generates a DML script to apply
only on the PostgreSQL connections. No DML script to apply on the Oracle
connection is generated.

BLOB and CLOB data types
------------------------

LiveCompare can compare CLOB fields from Oracle, provided that the
equivalent field in PostgreSQL is of type ``text`` . The same goes for
BLOB fields from Oracle. The equivalent in PostgreSQL is of type
``bytea`` .

However, by default, LiveCompare doesn’t handle BLOB/CLOB fields if
they’re in the primary key or if the table has no primary key. If that’s
the case, then the table is ignored, and LiveCompare logs has a message
like this:

::

   ORA-00932: inconsistent datatypes: expected - got BLOB

You can work around this behavior by telling LiveCompare to ignore
BLOB/CLOB fields if the table has no primary key. Enable these two
settings in the ``General Settings`` section:

::

   oracle_ignore_unsortable = on
   column_intersection = on

Incompatible collation
----------------------

On Oracle, generally the following initialization parameters are set:

::

   NLS_COMP = BINARY
   NLS_SORT = BINARY

This means that, regardless of the ``NLS_LANG`` and other language
settings, all ``ORDER BY`` operations in Oracle are performed using the
character binary code.

In Postgres, the equivalent collation that shows the same behavior is
the ``C`` collation. If your Postgres database was initialized in a
different collation, then by default LiveCompare might find issues when
sorting PK values. This can lead to false positives.

To work around that, you can force a collation (say, the ``C``
collation) in Postgres so it matches the same sort behavior from Oracle:

.. code:: ini

   force_collate = C

If LiveCompare detects that the comparison session involves Oracle and
PostgreSQL, then LiveCompare already sets ``force_collate = C`` , unless
you set it to another value.

Common hash
-----------

By default, LiveCompare has ``comparison_algorithm = block_hash`` , even
when comparing PostgreSQL to Oracle. However, a *common hash* is built
following these rules:

-  The row is fully represented as text by concatenating all column
   values.

-  On the Postgres side, timestamp, numeric, and bytea data types are
   handled to mimic Oracle.

-  This way, the full row representation is then hashed using MD5 on
   both sides.

-  This allows using ``comparison_algorithm`` set to ``block_hash`` and
   ``row_hash`` .

-  If there are any mismatches when using ``block_hash`` , LiveCompare
   falls back to ``row_hash`` and then ``full_row`` , as it does in a
   Postgres versus Postgres comparison.

-  The BLOB, CLOB, and NCLOB fields on Oracle are limited to only the
   first 2000 characters. ``comparison_algorithm = full_row`` allows
   comparison of the entire BLOB and CLOB.

-  On Oracle, the full row representation must not be wider than 4000
   characters. If the full row representation is wider than 4000
   characters, LiveCompare aborts the comparison for that specific
   table, and the following error message is added to the logs:

::

   ORA-01489: result of string concatenation is too long

Later LiveCompare versions will fall back to ``full_row`` comparison on
these specific tables. For now, a workaround is to configure a separate
comparison sessions only on these tables, using
``comparison_algorithm = full_row`` . Using LiveCompare with Oracle 10g
always requires ``comparison_algorithm = full_row`` be set.

The common hash uses the ``standard_hash`` function on Oracle 12c and
later. On Oracle 11g, the ``standard_hash`` function isn’t available, so
LiveCompare tries to use the ``dbms_crypto.hash`` function instead.
However, it might require additional privileges for the user on the
Oracle side, for example:

.. code:: sql

   GRANT EXECUTE ON sys.dbms_crypto TO testuser;