When migrating with Oracle Zero Downtime Migration (ZDM) and the logical online, you use Data Pump for the initial load and Oracle GoldenGate to keep the database in sync. When using Oracle GoldenGate, attention is needed on your sequences; otherwise, you will probably end up with a lot of
ORA-00001 unique constraint violated.
I think it is best to illustrate with an example:
|Source Database||Target Database|
|Sequence s1 currently has a value of 100.|
|ZDM starts and performs the export for the initial load.|
|The Data Pump import creates sequence s1 with current value 100.|
|A user inserts a row and uses the sequence:|
|s1.nextval takes the next number from the sequence, and the statement now looks like this:|
|GoldenGate replicates the statement. It does not take a new value from the sequence on the target. Doing so would corrupt the data because there is no guarantee that the same number would be taken. Instead, it uses the statement as it was executed on the source:|
|Now it is time to complete the migration. Users are disconnected.|
|GoldenGate synchronizes the final changes before users are now connecting to the target database.|
|A user inserts another record into orders:|
|s1.nextval takes the next number from the sequence on the target database. It was created with current value 100, so next value is 101:|
|101 has already been used, and typically, there is a primary key or unique key constraint on such a column.|
I talked to our GoldenGate experts, and it is a common pitfall. During migrations, sequences are often forgotten, and soon after the migration they start seeing
ORA-00001 unique constraint violated.
After the switchover, you must ensure that the sequences have a proper value. The sequences must be forwarded or advanced. You can do that in many ways. Here are some ideas:
Right after the migration has been completed and GoldenGate has done the final synchronization, but before users are connecting, you can recreate the sequences.
- Drop the sequences on the target database.
- Recreate the sequences either using
DBMS_METADATAor Data Pump.
- Ensure to adjust the privileges on the sequences.
If you can wrap all those changes into a shell script, you can have ZDM execute it automatically as part of the migration flow. Check out Pro Tip 5: Adding Custom Scripts from a previous blog post.
Forward or advance the sequence
Another approach is to forward or advance the sequence. Increment the sequence on the target database until the next value is high enough. What do I mean by high enough?
- The value of the same sequence on the source database.
- If you know the sequence is only used to generate numbers for one column (let’s call it
t1.c1), then get the highest value from that column
SELECT max(c1) FROM t1. Typically, such a column is a primary key column with a unique index underneath, so a
SELECT maxshould go very fast.
Now that you have the desired, new value for the sequence, you can calculate how much to increment by. Example: The source database sequence (
DBA_SEQUENCES.LAST_NUMBER) is 1500
and target database sequence is 1000, then you need to increment by 500:
alter sequence seq1 increment by 500; select seq1.nextval from dual; alter sequence seq1 increment by 1;
If you use the approach of
SELECT max then you need to increment by 501. In any case, better increment by too much than too little.
Similar to the former option, you can put the commands into a shell script and have ZDM execute it
Replicate sequence changes
GoldenGate also has the possibility of replicating the changes to the sequences. If you decide to use this with ZDM, it will require additional configuration of GoldenGate, which is why I prefer any of the former options. If you want to more about it, check out Oracle GoldenGate 19.1 – Using Oracle GoldenGate for Oracle Database, chapter 8.1 Installing Support for Oracle Sequences.
If your database uses sequences and you are using Oracle GoldenGate as part of your migration, ensure to handle your sequences properly. Otherwise, you will fall into a typical pitfall of GoldenGate migrations.
Other Blog Posts in This Series
- Install And Configure ZDM
- Physical Online Migration
- Physical Online Migration to DBCS
- Physical Online Migration to ExaCS
- Physical Online Migration and Testing
- Physical Online Migration of Very Large Databases
- Logical Online Migration
- Logical Online Migration to DBCS
- Logical Offline Migration to Autonomous Database
- Logical Online Migration and Testing
- Logical Online Migration of Very Large Databases
- Logical Online and Sequences (this post)
- Logical Migration and Statistics
- Logical Migration and the Final Touches
- Create GoldenGate Hub
- Monitor GoldenGate Replication
- The Pro Tips