3002 - Replicating Oracle Sequences

ORACLE SEQUENCES

Oracle sequences are not treated as tables and cannot be replicated like tables. Internally, Oracle stores sequence values as rows in a system table, and these values can be read through system defined views. The sequence system table cannot be modified, therefore the only way to change a sequence value is by calling NEXTVAL.

To replicate Oracle sequences in Syniti Replicate, we propose using a script (attached below) in a recurrent-refresh replication.

The script works by:

• Reading the last value of the sequences you want to replicate from source
• Reading the last value of the sequences from target
• Comparing the values
• If a sequence value on the target is less than that on the source, NEXTVAL is called until the value matches

There is one important requirement:

If you want the sequence numbers on the SOURCE and TARGET to be exactly the same, you must turn off caching in the sequence. This has to be done because Oracle, by default, will cache sequences so we cannot know the exact number.

For example: ALTER SEQUENCE SCOTT.SEQ01 NOCACHE

STEPS TO REPLICATE A SEQUENCE:

1. Turn off the Syniti Replication Agent.
2. In Oracle, make sure NOCACHE is used by sequences on both source and target.
3. We recommend creating a simple dummy table on both sides: CREATE TABLE REFRESHSEQ (ID INTEGER).
4. Define a refresh replication of the dummy tables.
5. Go into the Replication Properties.
6. On the Scheduler tab, in the Refresh Schedule, select Run Recurrently.
7. Specify a recurrent interval (1 minute, for example), this is how often you want to update the sequences.
8. On the General tab, click in the Use Script check box to enable scripting.
9. Click the button Script… to open the Replication Script Editor.
10. Copy and paste the attached script.
11. In the script, locate the variable selectCondition and edit it to specify the sequences you want to replicate. In this example, we replicate all the sequences with name starting with ‘SEQ’ owned by user SCOTT using the WHERE clause of a SQL select statement to identify the sequences.
    
    The SELECT statement looks like this:
    
    SELECT SEQUENCE_OWNER, SEQUENCE_NAME, INCREMENT_BY, LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = 'SCOTT' AND SEQUENCE_NAME LIKE 'SEQ%' ORDER BY 1,2
    
    The AddLog statements provide debug information and script progress in the log file to verify everything is working. You can comment out the AddLog statements after everything is working.
    
    
12. Turn on the Syniti Replication Agent.
13. Test to see if sequences are replicated properly.