-
Title
How to determine if a replicating sequence is in synchronization across source and target? -
Description
Sequences are sometimes replicated. This is especially true in a HA or peer to peer environment. The concept of synchronization of a sequence across source and target is a subtle one and needs to be fully understood before devising a way to determine the sync of a sequence.
-
Cause
General information.
-
Resolution
The field LAST_NUMBER in DBA view DBA_SEQUENCES is the best way to determine if the source and target sequences are in sync. Query of LAST_NUMBER column of the DBA view DBA_SEQUENCES using the following SQL on both source and target will show whether sequences match:
SQL> select last_number from dba_sequences where sequence_owner = '<owner_name>' and sequence_name = '<sequence_name>';
The LAST_NUMBER has a better chance of staying at a constant value for some time until it is incremented again (at least when using the CACHE option). For this reason it is the best barometer to determine the state of sync between source and target sequences. The following points are worth noting when trying to determine the sync of the sequence in this manner:1. Depending on how infrequently the sequence is referenced, this technique may provide the true state of sync. If it is referenced too fast, then the source and target values for LAST_NUMBER may differ though this may not necessarily mean that the sequences are not in sync. It could just be that the DDL to change the target sequences have not yet arrived and will do so in due course.
2. If the queues are backlogged, then again the LAST_NUMBER may differ across source and target and would eventually be same once the backlog disappears, assuming there are no other problems. But till such time it cannot be said with certainty whether the sequences are in sync.
3. If Shareplex queue data is lost due to any reason, say queue corruption, etc, then the sequences will not be in sync and this will be reflected by the different values for LAST_NUMBER on source and target.
4. Ideally, the determination of sync by referencing LAST_NUMBER across source and target should be done when the source database is quiet or at least when user activity is down to a minimum as this allows the changes to post quickly to the target and the technique provides a true picture. Also, to compare them side by side use a script with dblink so that the command is issued at the same time on both source and target database.
Additional Information:
The following explains the mechanism behind the use of LAST_NUMBER to ascertain the synchronicity of sequences:
Sequences are typically replicated in a HA or master-master environment. They may or may not be cached. In case of former, a set of sequence numbers equal to the cache value are generated and stored in memory in the database and used whenever the pseudo column nextval or currval is referenced. Once these numbers are exhausted, a next set of numbers are generated for use by sequence. The column LAST_NUMBER in DBA view DBA_SEQUENCES keeps track of the highest number generated so far in the sequence. The only field that is stored on disk is LAST_NUMBER whereas the individual sequence numbers are only generated in memory. For example, if the sequence is referenced for the first time using the SQL statement "select <sequence_name>.NEXTVAL from dual", then the output shows the value of NEXTVAL as 1 and that of the LAST_NUMBER as 21 on source (assuming that the default value of 1 is used for START WITH clause and 20 for CACHE clause). The field LAST_NUMBER on the target, will show the value of 21 in it. This will go on until the NEXTVAL hits 21 on source, at which time the LAST_NUMBER will be 41 on both source and target respectively. The LAST_NUMBER will remain at 41 on both source and target till the NEXTVAL hits 41 on source and so on. If the sequence is not cached (by using NOCACHE clause), then every time the NEXTVAL is referenced, it is stored on disk. Also, the LAST_NUMBER field contains a value equal to the value referenced by NEXTVAL plus 1. Thus, if the NEXTVAL contains 1, then the LAST_NUMBER will store 2. The same value will exist on target for the field LAST_NUMBER.
With the above background, it is not a straightforward thing to determine if a sequence is in sync across source and target. One cannot query NEXTVAL on source and target because this causes the NEXTVAL as well as the current value referenced by the sequence (CURRVAL) to increase by 1. One can query the pseudo column CURRVAL but in a dynamic environment the sequence is referenced frequently and the value referenced across source and target may not give a correct picture about the status of the sync of the sequence since at the time the query is made, the CURRVAL may differ across source and target. The best way to determine if the sequences are in sync is to query the LAST_NUMBER column of the DBA view DBA_SEQUENCES across source and target.