​We are currently in the process of upgrading our Oracle database from version 11g to 12c. There are lot of challenges. One of the challenge we have is of course space. We have one test (Unix) machine that is running several Oracle instances. During the Oracle 12c, the DBA decided to upgrade only one of the instances to 12c. This meant that, whenever they refresh the database, their automatic refresh from a production standby would no longer work, because of the version differences. They will have to manually export and import from production (or standby) using datapump (expdb on the source and impdb on the target database).

While testing this new database (DB12c), we ran into a strange problem – the user started getting a lot of “Primary key violations”. After some checking, found out that the sequences were out of whack. Essentially, the sequences were not part of the export or import and thus they retained the old values before we did the refresh. This seems to be a problem Oracle is aware of. See here.

We were in the middle of a comparison testing (DB11g is a test instance in Oracle 11g and DB12c in 12c) and we needed a quick solution. I decided to attack the issue as a programming problem. A bit of Googling gave solutions to reset the sequence to 0. This won’t work for us, as we had live data in the database on which we were running tests. I considered different solutions.

1. What if we simply bump up every sequence in the database by some big number, so they will not run into trouble? Say +100,000 to each sequence?

This seemed to work OK, until someone ran a huge batch program on DB11g and 100,000 wasn’t good enough. We started seeing Primary key violations sooner. So, what # can we increase it by?

2. The second solution I considered, was to simply bump up the sequences to match those in DB11g instance by using dblink.

This worked ok too, but we hit the same snag as above. This worked as long as the DB11g was not refreshed through datapump (which means it’s sequences were not correct as well!!)

3. The 3rd option was simply a programmer’s way of thinking:

Why not just create a table, insert the sequence name and its value into this table and bring that table to target database instance and then run a script to update the sequences with the values from the table we just brought in. This works in any situation. This is what we currently use.

Here is the script for the first approach:

DECLARE
v_seq_val NUMBER;
v_max_id NUMBER;
v_adjust_value NUMBER;
v_sql VARCHAR2(4000);

BEGIN
Dbms_Output.put_line('spool sequences_fixes.log');

-- Increment by constant; change as needed
v_adjust_value := 200000;

FOR seq IN (
SELECT sequence_name, last_number FROM all_sequences WHERE sequence_owner = 'SCHEMA1'
)
LOOP

BEGIN
Dbms_Output.put_line('-- ' || seq.sequence_name);

v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY ' || v_adjust_value || ';';
Dbms_Output.put_line(v_sql);

v_sql := 'SELECT ' || seq.sequence_name || '.nextval FROM dual;';
Dbms_Output.put_line(v_sql);

-- reset "increment_by" back to 1
v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY 1;';
Dbms_Output.put_line(v_sql);
Dbms_Output.put_line('--');

EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
Dbms_Output.put_line('spool off');

END;
/

That didn’t work out for us, especially when production, source and target databases were all in a flux.

The second approach using dblink was a little more reliable, as long as the source itself was not refreshed using datapump.

FOR seq IN (
 SELECT sequence_name, last_number FROM all_sequences@LTXSYT15 WHERE sequence_owner = 'SCHEMA1'
)
LOOP

  BEGIN
   Dbms_Output.put_line('-- ' || seq.sequence_name);

   SELECT last_number
   INTO v_seq_val
   FROM all_sequences
   WHERE sequence_owner = 'SCHEMA1'
   AND sequence_name = seq.sequence_name;

   v_diff := seq.last_number - v_seq_val;

   IF (v_diff > 0) THEN
    v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY ' || v_diff || ';';
    Dbms_Output.put_line(v_sql);

    v_sql := 'SELECT ' || seq.sequence_name || '.nextval FROM dual;';
    Dbms_Output.put_line(v_sql);

    -- reset "increment_by" back to 1
    v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY 1;';
    Dbms_Output.put_line(v_sql);
  END IF;

    Dbms_Output.put_line('--');

  EXCEPTION WHEN NO_DATA_FOUND THEN
  NULL;
  END;
END LOOP;
Dbms_Output.put_line('spool off');

END;
/

And finally, the approach that worked was to bring the latest sequence values from the production instance (original source from which both DB1 and DB2 were refreshed):

On the production database (or source):

-- Run this in the Source database = production?
DROP TABLE schema1.latax_sequence_values;
CREATE TABLE schema1.latax_sequence_values (sequence_name, seq_value) AS
 SELECT sequence_name, last_number FROM all_sequences WHERE sequence_owner = 'SCHEMA1';

-- export this to a file, so it can be imported to the target database.

Then export the table just created and import into target database.

DECLARE
v_seq_new_value NUMBER;
v_max_id NUMBER;
v_diff NUMBER;
v_sql VARCHAR2(4000);

BEGIN
Dbms_Output.put_line('spool sequences_fixes_using_another_db.log');

FOR seq IN (
 SELECT sequence_name, last_number FROM all_sequences WHERE sequence_owner = 'SCHEMA1'
)
LOOP

  BEGIN
   --Dbms_Output.put_line('-- ' || seq.sequence_name);

   SELECT seq_value
   INTO v_seq_new_value
   FROM schema1.latax_sequence_values
   WHERE sequence_name = seq.sequence_name;

   v_diff := v_seq_new_value - seq.last_number;
   Dbms_Output.put_line('-- ' || seq.sequence_name || ' , curr val=' || seq.last_number || ', new val=' || v_seq_new_value || ', ' || ', diff=' || v_diff);

   IF (v_diff > 0) THEN
    v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY ' || v_diff || ';';
    Dbms_Output.put_line(v_sql);

    v_sql := 'SELECT ' || seq.sequence_name || '.nextval FROM dual;';
    Dbms_Output.put_line(v_sql);

    -- reset "increment_by" back to 1
    v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY 1;';
    Dbms_Output.put_line(v_sql);
  END IF;

    Dbms_Output.put_line('--');

  EXCEPTION WHEN NO_DATA_FOUND THEN
  NULL;
  END;
END LOOP;
Dbms_Output.put_line('spool off');

END;
/

Notes:

• ​In all the above scripts, I have left out the SQL (spooling etc) portion and show only the meat of the solution in PL/SQL.

• ​The core logic is to use ALTER SEQUENCE to bump up the sequence. Remember, Sequence only moves forward, typically by an increment of 1.​ In these scripts, we change the increment value to be the difference of the current value in the sequence and what it should be (diff new value of sequence – old value of sequence).

​But, don’t forget to reset the “increment by” value back ​to 1. Otherwise, you will be surprised by the next value you get in the database!! See my earlier post here for more about resetting the sequence.

• ​Also, note that the above values I got (either through dblink or through a table) are approximate. I am reading the data dictionary (user_sequences)​ to get the last value. Th​e value I got may be much larger than​ the Sequence.nextval, but that didn’t matter. This is probably because of caching (cache_size column in all_sequences), but this was OK for us. ​I just wanted to get the sequence.nextval out of “primary key” above the danger zone. ​
• Like I mentioned above, this was a programmer’s solution. The DBA solution would have been something they mentioned at the bottom of the link I mentioned earlier). There a DBA, I assume, is suggesting to drop all the sequences and reload them using the DDL package. That may be more efficient from a DBA point of view, but mine is poorman DBA’s solution!

• And finally, this is one case where a proper naming convention would have alleviated the problem. Suppose you have tables named A through Z. If we named every sequence as A_primary_Seq, B_primary_Seq etc to mean the sequence for the primary of table A etc, then we could have easily addressed it.

​ In fact, this was my first attempt at fixing this issue. Soon, I found out that our ​​sequence naming was all over the place.

Since the primary key violation happens because the sequence value is below the max(id) in the table, we could have simply,

sequence new value  = max(id) + 1

For e.g.,

A_primary_seq value = Max(primary_key_field) from Table A;

To get the primary key field for the table, I used something like:

SELECT i.table_name, c.column_name  FROM dba_indexes i, dba_ind_columns c
WHERE i.owner = 'SCHEMA1'
AND c.table_owner = 'SCHEMA1'
AND uniqueness = 'UNIQUE'
AND i.index_name = c.index_name
AND i.table_name = c.table_name
AND c.char_length = 0;