duplicate after restore

[Roshan]

Oracle 19c
Exadata

Hello Erman,

after performing an export, import of a table from 12.2.0 to 19.0 database, we had to reset the sequence number after import.


Some 814 records had duplicate sequence numbers – is that possible?

The DDL statement for the table mentions creating the sequence from 1 whereas it should have been 201021 to continue the previous sequence

The DDL statement for the MU_AT table was changed to start at 255000

alter table "BPMDATAPRODMU"."MU_AT" modify ID generated as always as identity (start with 255000);

Kindly advise.

[Roshan]

Hello,

here is my import script

nohup impdp \'/ as sysdba\'  directory=MDP1 dumpfile=MUDCATTACH_0.dmp logfile=imp_MUDCATTACH_0.log  DATA_OPTIONS=DISABLE_APPEND_HINT CONTENT=DATA_ONLY STREAMS_CONFIGURATION=N &
nohup impdp \'/ as sysdba\'  directory=MDP1 dumpfile=MUDCATTACH_1.dmp logfile=imp_MUDCATTACH_1.log  DATA_OPTIONS=DISABLE_APPEND_HINT CONTENT=DATA_ONLY STREAMS_CONFIGURATION=N &
nohup impdp \'/ as sysdb

[Roshan]

I checked the doc 2525896.1. Do i need to exclude the tables instead of exporting the table structure?

[ErmanArslansOracleBlog]

I couldn't clearly understand your case.

Is it duplicate records after export-import?
Is it duplicate sequence number (with overwritten sequence numbers) for some of the records?
Please clarify.

If your issue is aligned with the one given in "DataPump Import (IMPDP) Inserts Data Twice Using PARTITION_OPTIONS=MERGE and TABLE_EXISTS_ACTION=SKIP (Doc ID 2525896.1)", then of course you can follow Oracle Support.  

[Roshan]

The count of records were same in source and target but ID were same for few records.

[ErmanArslansOracleBlog]

3 questions and  some requests:

1)So the imported data is okay. No problem with that. Tere are no duplicate rows, but the values of some ID columns (which are populated by the sequences) are the same as the source.

-send me an example just 2 records. (mask the sensitive info)

2)Did you interrupt the impdp (once or twice)? I mean did you get any error during your first import session for instance?

3)What does the impdp log says? Anything weird about the affected tables or sequences there?

4)When you check the expdp dmp file and search for a problematic sequence, what do you see for the start values? (I mean the creation script itself)

Please update this thread with your findings.

[Roshan]

1)So the imported data is okay. No problem with that. There are no duplicate rows, but the values of some ID columns (which are populated by the sequences) are the same as the source.

only few IDs were duplicated after import. But the count of souce and target are same. Export/Import was OK aS per logs. Also, we had to alter the sequence to start with last value from source. Is this a normal behaviour for identity columns?

[ErmanArslansOracleBlog]

Okay.. It may de due to consistency.. Your application was running while you were getting that export right.. That may be the cause of those few duplicated Ids..

This issue can be quickly addressed in an on-hands investigation.
With these in mind, you can make a deep dive and find the actual cause Roshan..

Also see -> https://community.oracle.com/tech/apps-infra/discussion/2577225/sequence-behavior-after-importing-via-datapump

[Roshan]

Please have a look at the document

http://www.grassroots-oracle.com/2016/10/synchronise-sequence-value-with-12c-identity-column.html

I was getting same issue.

[ErmanArslansOracleBlog]

It depends the case. and I guess I still couldn't understand the issue clearly.
Are those columns defined AS IDENTITY? What is the definition of a problematic table? Do you have data in the target table (in those ID columns) before your import?
These questions should be answered..
If your case is similar to the one given in that blog, having START WITH LIMIT VALUE on target may work for you. (I guess you don't want to touch the source) But is that your issue? Do you import data into a table that already has some row in it? But note that, this modifies the values of ID columns.. Just note it.