Oracle support v2
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:
All other data connections must be PostgreSQL.
Here's a simple example of comparison between an Oracle database and 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
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:
schema_qualified_table_names = off, you can also use non-qualified table names in
Row Filter, and
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
replication_sets features (described in PGD support), then you can point the last data connection to Oracle, like this:
You also can compare a whole PGD cluster against a single Oracle database, for example:
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.
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.
You need the Python module 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 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:
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.
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
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:
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:
On Oracle, generally the following initialization parameters are 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
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:
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.
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
- If there are any mismatches when using
block_hash, LiveCompare falls back to
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_rowallows 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:
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.
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: