Data Validation

Data validation

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

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

By default, Ora2Pg will extract the first 10000 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 column is not a LOB. Rows will be sorted using this unique key. Due to differences in sort behavior between Oracle and PostgreSQL, if the collation of unique key columns in PostgreSQL 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.

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

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