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 
Got some thoughts? Leave a comment