Written By

Introduction

We are using sequences so far in primary database so that users can generate their unique integers, Even in 11gR2 Active Data Guard there are many limitations respective to the Applications in using global temporary tables, From 12c applications are allowed to use the already created global temporary tables and can be used global or session sequences as needed. This article explains how to use global and session sequences and how it actually works.

Global Temporary tables with Active Data Guard

To work on global sequences or session sequences we must have the temporary tables, so that standby(ADG) can do perform DML's on temporary tables where standby cannot perform such DML's on regular tables. Below is the startup of Global temporary tables on how to create it and how easy to do DML's on standby, I have written one more detailed article on Global Temporary tables if in case looking for more information on it[http://www.toadworld.com/platforms/oracle/w/wiki/11084.12c-active-data-guard-dml-on-temporary-tables.aspx].

Primary

Note that, DDL's such as create temporary table statement should be issued on primary database and of course as said above DML's are allowed to do on standby database.

SQL> select * from seq_data;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

SQL> create global temporary table gtt_seq on commit preserve rows as select * from seq_data;

Table created.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

SQL>

From the above output we can see the rows which are inserted, but if you check the same query on standby and it returns with zero rows and now you can start using temporary table by adding or updating or any DML's.

Standby:

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

SQL> select * from gtt_seq;

no rows selected

SQL> insert into gtt_seq select * from seq_data;

4 rows created.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

SQL>

So from the above example, it clears that we can able to perform the DML's even in standby database. Remember that once the session disconnected then the information will no more exists with the temporary table.

Global Sequences

From Active Data Guard 12c, Sequences created using the default settings i.e. Cache and NoOrder can be used from standby database. if for example the sequence used by the standby and then it allocate itself the unique range of sequence numbers, once the complete range is used then again the new set of range will be allocated to the standby database and note that the sequence range where ever assigned then the unique stream of sequences will be maintained in across the Data Guard configuration.

There are few instructions with usage of sequences, i.e. while creating the sequences ensure it is Cache and NoOrder and the standby should have configured the remote destination(log_archive_dest_n) to the primary database back. Apart from that Oracle recommends to have big cache because it has to allocate and communicate to across all the databases of the configurations and hence the performance can be benefited.

Creating sequences can be accepted with the default values or you can configure on your own settings based on the requirements which can suffice. For example Increment by, cache size, start with value so on. As Active data guard will accept the default configuration , hence we have created the global sequence with default settings.

SQL> create sequence gseq global;

Sequence created.

SQL>

Or we can create sequence with custom values such as

create sequence gseq increment by 1 start with 1 nomaxvalue nocycle cache 100 global;

We can check the sequence settings by using the view "user_sequences"

SQL> select sequence_name,min_value,max_value,cache_size,order_flag from user_sequences where sequence_name='GSEQ';

SEQUENCE_NAME   MIN_VALUE                       MAX_VALUE CACHE_SIZE O

-------------- ---------- ------------------------------- ---------- -

GSEQ                   1   9999999999999999999999999999         20 N

SQL>

After having the global sequence created, Now we are very much to start playing with the global sequences.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

SQL>

If you look carefully the rows in above table with QID column have the values from 1 to 4, but when we try to update the column QID with the nextval of the sequence and the QID values are changed based on the sequence settings. Of course before start using sequences you are always allowed to alter any settings.

SQL> update gtt_seq set qid=gseq.nextval;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

       21 AAA

       22 BBB

       23 CCC

       24 DDD

SQL>

Now from the above output it clears that the values of QID are already updated, Next check how the values from standby database.

Standby:

From standby database of same table, the values are not updated with the sequence nextval because the sequence range was specific to primary and they are not going to visible to standby database.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

If we update the same table gtt_seq on standby with the same conditions, probably you are expecting with the cache size value? No. Because the sequence range was already assigned to primary. Hence the new set of sequence range will be allocated to standby i.e. from 40

SQL> update gtt_seq set qid=gseq.nextval;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

       41 AAA

       42 BBB

       43 CCC

       44 DDD

SQL>

Now the above output is clear enough to know how the global sequence is working. Finally we will check with one more test on primary database by updating the table.

Primary:

SQL> update gtt_seq set qid=gseq.nextval;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

       25 AAA

       26 BBB

       27 CCC

       28 DDD

SQL>

Probably you were expecting the QID value starting from 61? No... because the given sequence range is still available with primary database and it can be used. So this output illustrates how the Data Guard manages the global temporary tables.

Session Sequences

In regular sequences(global), it maintains the uniqueness of sequence range but when it comes to session sequences, it maintains unique range number of sequences with in a session. In global sequences there are limitations to configure cache and noorder but in Session sequences supports most of the combinations. The session sequences we should create them in primary database and later they can be accessed on standby databases.

Now we will walk through with the test case with Session sequences. The practice is almost same as global sequences but the results vary, we will see how. The main prerequisite is to having the Global temporary table and having sequence with "session" attribute.

Primary

SQL> create sequence sseq session;

Sequence created.

SQL>

SQL> select * from gtt_seq;

no rows selected

SQL> insert into gtt_seq select * from seq_data;

4 rows created.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

SQL>

After creating session sequence, we have inserted few rows from other table and now we will update the table with session sequence next value.

SQL> update gtt_seq set qid=sseq.nextval;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

SQL>

Based on the cache size the unique range will be allocated to the session and the values remained same as it's an initial allocation. Now we will perform same transaction over global temporary table.

Standby

SQL> select * from gtt_seq;

no rows selected

SQL> insert into gtt_seq select * from seq_data;

4 rows created.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

After inserting rows, when updating the column QID with the session sequence next value, in case of global temporary tables the series started from 21 but because with session sequence the unique range again started from 1.

SQL> update gtt_seq set qid=sseq.nextval;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         1 AAA

         2 BBB

         3 CCC

         4 DDD

SQL>

For the confirmation how the session sequence is working, below example should give clear picture after updating the same table with the session sequence next value.

SQL> update gtt_seq set qid=sseq.nextval;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from gtt_seq;

       QID QNAME

---------- ----------

         5 AAA

         6 BBB

         7 CCC

         8 DDD

SQL>

As we are performing from same session without exit, then the sequence allocated after 4 and used the values 5 to 8 as per the expectations of session sequence.

Altering Sequences

After creating sequences we can alter the session type any time, i.e. eiter from Global sequence to Session sequence or vice versa.

SQL> select sequence_name,session_flag from user_sequences where sequence_name='GSEQ';

SEQUENCE_NAME   S

--------------- -

GSEQ           N

To know the sequence type, we can describe the view "user_sequences" and for the column "session_flag".

SQL> alter sequence gseq session;

Sequence altered.

SQL> select sequence_name,session_flag from user_sequences where sequence_name='GSEQ';

SEQUENCE_NAME   S

--------------- -

GSEQ           Y

After performing the sequence type, now it shows that the sequence type is changed from global to session sequence. Likewise we can change from session sequence to global sequence as below.

SQL> alter sequence gseq global;

Sequence altered.

SQL> select sequence_name,session_flag from user_sequences where sequence_name='GSEQ';

SEQUENCE_NAME   S

--------------- -

GSEQ           N

SQL>

Conclusion

we've seen how to use global and session sequence as required to the application and great flexibility to use them with global temporary tables from the standby database of 12c Active Data Guard feature.

References:

https://docs.oracle.com/database/121/SBYDB/manage_ps.htm#SBYDB5164

https://docs.oracle.com/database/121/SQLRF/statements_6017.htm#SQLRF01314