Oracle Support v2
LiveCompare can be used to compare data from an Oracle database against any number of PostgreSQL or BDR databases.
For example, you can define
technology = oracle in a data connection. Other
settings can then be used to define the connection to Oracle:
All other data connections are required to be PostgreSQL.
Here is a simple example of comparison between an Oracle database versus a PostgreSQL database:
schema_name in Oracle is the user table sandbox. All table names are
schema-qualified by default:
<schema_name> . <table_name>
<user> . <table_name
It is possible to disable schema-qualified table names by setting
schema_qualified_table_names = off. This can be done only if
oracle_user_tables_only = on, which means that LiveCompare will search only on
tables that belong to the Oracle user that is 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:
schema_qualified_table_names = off, then you can use non-qualified table
names in the
Row Filter and
Column Filter too.
Also please note that the
Output Connection is required to write progress and
reporting information from LiveCompare.
If you need to compare a BDR database against Oracle, and you want to take
features (as explained earlier), then you can point the last data connection to
Oracle, like this:
It is also possible to compare a whole BDR cluster against a single Oracle database, for example:
Please note that LiveCompare works on PostgreSQL databases out-of-the-box, without needing to install any additional software.
But in order to be able to connect to Oracle, LiveCompare requires additional software:
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
and extract in
- Linux: Download Oracle Instant Client
and install it on your system, then set
- Windows: Download Oracle Instant Client (32-bit) (64-bit) and extract it into the LiveCompare folder.
The Python module cx_Oracle is required to be installed and available on your system so that LiveCompare is able to connect to an Oracle Database.
Currently cx_Oracle is not installable from Linux distribution repositories, so please follow the instructions in cx_Oracle website to install it on your system.
As LiveCompare is recommended to be executed under the
system user, then it is possible to install the
cx_Oracle module through PIP
only for the
postgres user, with the following command:
If LiveCompare finds any difference, it will generate a DML script only to be applied on the PostgreSQL connections. A DML script to be applied in the Oracle connection is not generated.
LiveCompare is able to compare
CLOB fields from Oracle, provided that the
equivalent field in PostgreSQL is of type
text. The same goes for
fields from Oracle, the equivalent in PostgreSQL should be of type
However, by default LiveCompare does not handle BLOB/CLOB fields if they are in the primary key, or if the table has no primary key. If that's the case, then the table will be ignored, and in LiveCompare logs you will see a message like this:
It is possible to workaround this behaviour by telling LiveCompare to ignore
BLOB/CLOB fields if table has no primary key, by enabling these 2 settings in
General Settings section:
On Oracle, generally we have the following initialization parameters set:
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
collation. If your Postgres database was initialized in a different collation,
then by default LiveCompare might find issues when sorting PK values, which can
lead to false positives.
To workaround that, it is possible to force a collation (say, the
in Postgres, so it matches the same sort behavior from Oracle:
If LiveCompare detects that the comparison session involves Oracle and PostgreSQL,
then LiveCompare already sets
force_collate = C, unless the user has set it to
any other value.
By default, LiveCompare has
comparison_algorithm = block_hash, even when comparing
PostgreSQL versus Oracle. However, a "common hash" is built following these rules:
- The row is fully represented as text, by concatenating all column values;
- On 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
- If there are any mismatches when using
block_hash, LiveCompare will automatically fall back to
full_row, as it would on a Postgres versus Postgres comparison.
- The BLOB, CLOB and NCLOB fields on Oracle are limited to the first 2000 characters
comparison_algorithm = full_rowallows comparison of the entire BLOB and CLOB);
- On Oracle, the full row representation should not be wider than 4000 characters.
If the full row representation is wider than 4000 characters, LiveCompare will abort the comparison for that specific table, and you will see the following error message in the logs:
Further LiveCompare versions will fall back to
full_row comparison on these specific
tables. For now, a workaround would be to configure a separate comparison sessions on
these tables only, using
comparison_algorithm = full_row.
The Common Hash uses the
standard_hash function on Oracle 12c and newer. On Oracle
standard_hash function is not available, so LiveCompare tries to use the
dbms_crypto.hash function instead, but it might require additional privileges for
the user on Oracle side, for example: