18 December 2007

Sequence In Oracle RAC System might not generate numbers In a sequence!

Today, after migration to RAC system, i realized that sequence numbers(usally for generating primary keys) are not generated in a sequence. Applications that read historical data would fail because of that.  Before RAC system ;
ID    INSERT_DATE
1     16.12.2007 16:21:00
2     16.12.2007 16:21:10
3     16.12.2007 16:23:49

Note that ID field is generated with an Oracle SEQUENCE.

After migration to RAC it became as;
ID    INSERT_DATE
81     16.12.2007 19:43:23
82     16.12.2007 16:39:00
83     16.12.2007 16:46:08

As you see, insert date of 82 is before than insert date of 81. There is a mismatch with insertion dates and sequences. Then i read Oracle documentation:

ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

So, in order to keep numbers in an order, i altered the sequence and the problem was solved:
SQL>ALTER SEQUENCE SEQ_ID ORDER;

No comments: