Issue with sequence id/FK constraint during table load

[skizilbash]

Hello,

I am seeing a lot of these errors when trying to load data.

$ psql ph_tst01 < CREDENTIAL***_data.sql SET SET SET ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "cred**" references "credential**map". HINT: Truncate table "cred" at the same time, or use TRUNCATE ... CASCADE. ERROR: duplicate key value violates unique constraint "credentialmap_pkey" DETAIL: Key (credentialingseq_nbr)=(3494) already exists. CONTEXT: COPY credentialing_map, line 1

[darold]

The problem is that you have imported the constraints before the data. This is possible to proceed like that but you have to enable DROP_FKEY in ora2pg.conf. In this case you have all SQL orders to drop the FK before loading the data and recreate them after data import. These SQL orders are stored in the main file data.sql if you have used export_schema.sh.

[skizilbash]

Yes i noticed that i created the constraints before data load. I was able to drop PK and FK and load data. After that i was able to create PK but getting similar error pointing to missing seq_id upon recreating FK.

ph_tst01=# ALTER TABLE amnd_prcttnr** ADD PRIMARY KEY (amnd__seq_nbr); ALTER TABLE phycon_tst01=# ALTER TABLE amnd_prcttnr* ADD CONSTRAINT sys_c0019751 FOREIGN KEY (amnd__seq_nbr) REFERENCES amnd_prcttnr*(amnd__seq_nbr) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE; ERROR: insert or update on table "amnd_prcttnr" violates foreign key constraint "sys_c0019751" DETAIL: Key (amnd__seq_nbr)=(16242) is not present in table "amnd_prcttnr".

[darold]

This is because there is write activity on the database you are trying to export data and you probably not use serializable transactions. You can lost consistency between table export. You can try to use command line option --cdc_ready to try to export data, this will export all table data per the current SCN at first connection time.

[skizilbash]

i see a unique issue as below. i am exporting these two tables and it shows all the rows came through successfully but upon importing it only brings in a few rows and doesn't throw any errors either. Any idea why ora2pg is behaving this way?

$ ora2pg -t COPY -a EIM_EX*** -o data.sql -b ./data -c $HOME/config/ora2pg.conf [========================>] 1/1 tables (100.0%) end of scanning.
[> ] 15637/1013787 rows (1.5%) Table EIM_EX*** (75 recs/sec) [> ] 15637/1013787 total rows (1.5%) - (208 sec., avg: 75 recs/sec).
[========================>] 1013787/1013787 rows (100.0%) on total estimated data (208 sec., avg: 4873 recs/sec)

$ ora2pg -t COPY -a EIM_EX2*** -o data.sql -b ./data -c $HOME/config/ora2pg.conf [========================>] 1/1 tables (100.0%) end of scanning.
[> ] 15511/12057676 rows (0.1%) Table EIM_EX2*** (86 recs/sec) [> ] 15511/12057676 total rows (0.1%) - (181 sec., avg: 85 recs/sec).
[========================>] 12057676/12057676 rows (100.0%) on total estimated data (181 sec., avg: 66616 recs/sec) $ psql ph01 < EIM_EX_data.sql SET SET SET TRUNCATE TABLE COPY 10001 COPY 5636 $ psql ph01 < EIM_EX2_data.sql SET SET SET TRUNCATE TABLE COPY 10001 COPY 5510

[darold]

Ora2Pg use Oracle statistics to get the number of row available in a table, maybe they are not up to date. What does a count(*) on the Oracle table return?

[skizilbash]

Oracle

select count(*) from eim_ext**; 1013787

select count() from eim_ex2**; 12057676

Postgres

select count() from eim_ex**; 15637

select count(*) from eim_ex2**; 15511

[darold]

Well it is difficult to know the reason. I could be a WHERE clause set in ora2pg.conf, some RLS defined at Oracle side, or some other Oracle rules. Normally there is strictly no reason for Ora2Pg to limit the number of rows it a WHERE clause is not set. Have you set ORACLE_COPIES and on which operating system it is running?

[skizilbash]

Since i have not defined any column i am not sure how the below directive will help fix my issue. Anyway, i believe this may have to do with xml values as this table has an entire index for that column which obviously did not get created in postgres.

Multiprocess support. This directive should defined the number of parallel connection to Oracle when extracting data. The limit is the number of cores on your machine. This is useful if Oracle is the bottleneck. Take care that this directive can only be used if there is a column defined in DEFINED_PK. ORACLE_COPIES 1

[darold]

Ora2Pg is able to find the right column to use to split the data when there is a PK or UK defined on a numeric column. Commit fc3e2fc fixes the documentation.

# Multiprocess support. This directive should defined the number of parallel
# connection to Oracle when extracting data. The limit is the number of cores
# on your machine. This is useful if Oracle is the bottleneck. Take care that
# this directive can only be used if there is a primary / unique key defined
# on a numeric column or that a column is defined in DEFINED_PK.
ORACLE_COPIES   1