Oracle Support¶

LiveCompare can be used to compare data from an Oracle database against anynumber of PostgreSQL or BDR databases.

For example, you can define technology = oracle in a data connection. Othersettings can then be used to define the connection to Oracle:

```
`host`
```
```
`port`
```
```
`service`
```
```
`user`
```
```
`password`
```

All other data connections are required to be PostgreSQL.

Here is a simple example of comparison between an Oracle database versus aPostgreSQL database:

[General Settings]
logical_replication_mode = off
full_comparison_mode = on
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 areschema-qualified by default:

Postgres: `<schema_name> . <table_name>`

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

It is possible to disable schema-qualified table names by settingschema_qualified_table_names = off. This can be done only iforacle_user_tables_only = on, which means that LiveCompare will search only ontables that belong to the Oracle user that is connected. When schema-qualifiedtable names is disabled, then on Postgres you need to have set a defaultsearch_path on your role or configuration, or you can use the connectionstart_query parameter to set an appropriate search_path, for example:

[General Settings]
logical_replication_mode = off
full_comparison_mode = on
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, then you can use non-qualified tablenames in the Table Filter, Row Filter and Column Filter too.

Also please note that the Output Connection is required to write progress andreporting information from LiveCompare.

If you need to compare a BDR database against Oracle, and you want to takeadvantage about Initial Connection, node_name and replication_setsfeatures (as explained earlier), then you can point the last data connection toOracle, like this:

[General Settings]
logical_replication_mode = bdr
full_comparison_mode = on
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'

It is also possible to compare a whole BDR cluster against a single Oracledatabase, for example:

[General Settings]
logical_replication_mode = bdr
full_comparison_mode = on
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¶

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 additionalsoftware:

Oracle Instant Client¶

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

**MacOSX** : Download Oracle Instant Client

(64-bit) and extract in ~/lib;

```
**Linux** : Download Oracle Instant Client
```
(32-bit) (64-bit) and install it on your system, then set LD_LIBRARY_PATH;
```
**Windows** : Download Oracle Instant Client
```
(32-bit) (64-bit) and extract it into the LiveCompare folder.

cx_Oracle Python module¶

The Python module cx_Oracle isrequired to be installed and available on your system so that LiveCompare isable to connect to an Oracle Database.

Currently cx_Oracle is not installable from Linux distribution repositories, soplease followthe instructions in cx_Oracle websiteto install it on your system.

As LiveCompare is recommended to be executed under the postgres operatingsystem user, then it is possible to install the cx_Oracle module through PIPonly for the postgres user, with the following command:

pip3 install --user cx_Oracle --upgrade

Differences¶

If LiveCompare finds any difference, it will generate a DML script only to beapplied on the PostgreSQL connections. A DML script to be applied in the Oracleconnection is not generated.

BLOB and CLOB Data Types¶

LiveCompare is able to compare CLOB fields from Oracle, provided that theequivalent field in PostgreSQL is of type text. The same goes for BLOBfields from Oracle, the equivalent in PostgreSQL should be of type bytea.

However, by default LiveCompare does not handle BLOB/CLOB fields if they are inthe primary key, or if the table has no primary key. If that’s the case, thenthe table will be ignored, and in LiveCompare logs you will see a message likethis:

ORA-00932: inconsistent datatypes: expected - got BLOB

It is possible to workaround this behaviour by telling LiveCompare to ignoreBLOB/CLOB fields if table has no primary key, by enabling these 2 settings inthe General Settings section:

oracle_ignore_unsortable = on
column_intersection = on

Incompatible Collation¶

On Oracle, generally we have the following initialization parameters set:

NLS_COMP = BINARY
NLS_SORT = BINARY

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

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

To workaround that, it is possible to force a collation (say, the C collation)in Postgres, so it matches the same sort behavior from Oracle:

force_collate = C

If LiveCompare detects that the comparison session involves Oracle and PostgreSQL,then LiveCompare already sets force_collate = C, unless the user has set it toany other value.

Common Hash¶

By default, LiveCompare has comparison_algorithm = block_hash, even when comparingPostgreSQL 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 `comparison_algorithm` set to `block_hash` and `row_hash`;

```
If there are any mismatches when using `block_hash`, LiveCompare will automatically
```
fall back to row_hash and then 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

only (comparison_algorithm = full_row allows 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 abortthe comparison for that specific table, and you will see the following error messagein the logs:

ORA-01489: result of string concatenation is too long

Further LiveCompare versions will fall back to full_row comparison on these specifictables. For now, a workaround would be to configure a separate comparison sessions onthese tables only, using comparison_algorithm = full_row.

Also note that the Common Hash requires the standard_hash function on Oracle,which is available only on Oracle 12c and newer. On Oracle 10g and 11c, please usecomparison_algorithm = full_row.