Data validation consists in comparing data retrieved from a foreign table pointing to the source Oracle table and a local Postgres Pro table resulting from the data export.

To run data validation, you can use a direct connection like any other ora2pgpro action but you can also use the oracle_fdw extension provided that FDW_SERVER and PG_DSN configuration directives are set.

By default, ora2pgpro will extract the 10000 first rows from both sides, you can change this value using directive DATA_VALIDATION_ROWS. When it is set to zero, all rows of the tables will be compared.

Data validation requires that the table has a primary key or unique index and that the key columns is not a LOB. Rows will be sorted using this unique key. Due to differences in sort behavior between Oracle and Postgres Pro, if the collation of unique key columns in Postgres Pro is not C, the sort order can be different compared to Oracle. In this case the data validation will fail.

Data validation must be done before any data is modified.

ora2pgpro will stop comparing two tables after DATA_VALIDATION_ROWS is reached or that 10 errors has been encountered, result is dumped in a file named data_validation.log written in the current directory by default. The number of error before stopping the diff between rows can be controlled using the configuration directive DATA_VALIDATION_ERROR. All rows in errors are printed to the output file for your analyze.

It is possible to parallelize data validation by using -P option or the corresponding configuration directive PARALLEL_TABLES in ora2pgpro.conf.