Posted by You would think that (with the exception of the V$ tables which are predominantly memory structures reflecting the state of various parts of the database instance) a query on a read-only standby database would have absolutely no interaction with the primary. After all, the standby database needs to be able to run independently of the primary should that primary database be down, or destroyed.
But there’s an exception to the rule. Consider the following example. I create a sequence on my primary database.
SQL> create sequence seq;
Sequence created.
SQL> select seq.nextval from dual;
NEXTVAL
----------
1
1 row selected.
SQL> select last_number from user_sequences where sequence_name = 'SEQ';
LAST_NUMBER
-----------
21
1 row selected.
You can see that that it’s just a plain old sequence with the default cache value of 20. Now let’s pop over to my standby (which was in recovery mode, so it now contains the same definition) and flick it out into read only mode.
SQL> alter database open read only;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select seq.nextval from dual;
NEXTVAL
----------
21
On the standby as well, I can query the same sequence. Notice that because it is a different instance to the primary (obviously), it picked up it’s value from the next cache boundary, much like multiple RAC instances can do. But that suggests a potential issue – what happens when the primary keeps on selecting sequence values. Will it collide with the sequence values obtained via queries on the standby ? Let’s look back on the primary database.
SQL> select last_number from user_sequences where sequence_name = 'SEQ';
LAST_NUMBER
-----------
41
1 row selected.
SQL> select seq.nextval from dual;
NEXTVAL
----------
2
1 row selected.
The primary is still using it’s “cached” values from 2 to 20, but notice the LAST_NUMBER has been incremented to 41. The query on the standby database has sent a message to the primary to get the LAST_NUMBER column on USER_SEQUENCES (or more accurately, the internal table SEQ$) updated to avoid sequence number collisions. If we continue to consume sequence values on the primary, you will see it skip over those numbers that are now “allocated” to the standby database in read only mode.
SQL> select seq.nextval from dual
2 connect by level <= 30;
NEXTVAL
----------
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
41
42
43
44
So there you go …. Something I didn’t know was possible. A query on the standby may enact an physical update on the primary.
Learning is not a spectator sport 
I’m feeling lazy so I won’t run up the test, but my first thought was:
What happens when the network fails, or slows down, temporarily and the standby can’t send a needed update to the primary (in time) ? What’s the mechanism and sequence of locking to ensure that the standby and primary don’t try to do the same update to seq$ at the same time ?
I wrote about the unavailability potential in my ora mag article
http://www.oracle.com/technetwork/issue-archive/2016/16-jul/o46dba-mcdonald-3075286.html
I’ve yet to explore the intricacies of the locking involved.
Interesting. Is it fair to say that you have just required an Active Dataguard License by issuing the command ‘alter database open read only;’ on a standby database.
Hi John,
I work for Oracle …. so I’m pretty sure I’m covered on this one 🙂
Totally agree but it worth pointing out how easily it can be to fall in licensing troubles
indeed interesting.
some more questions arise:
–
“…The query on the standby database has sent a message to the primary to get the LAST_NUMBER column on USER_SEQUENCES (or more accurately, the internal table SEQ$) updated to avoid sequence number collisions. …”
which process on the primary receives this message ? which process does the update on SEQ$ ?
–
you get “ORA-03179: NOCACHE or ORDER sequences cannot be accessed from Active Data Guard standby” when trying to select nextval on a NOCACHE sequence or on an ORDER sequence.
why ? could you shed some light onto that ?
“…when trying to select nextval on a NOCACHE sequence or on an ORDER sequence. …”
meant to say
“…when trying to select nextval on a NOCACHE sequence or on an ORDER sequence from the active standby”
Mathias,
That answers a question I was thinking about asking. Too many people use nocache and order because they think it protects them from gaps; the same people often have serious recovery requirements – being able to get nextval from the primary with order or nocache would be a real performance killer on the primary, like having RAC without the interconnect.