Conflict Management in Oracle Active DataGuard 11gR2
In PostgreSQL, I found the following;
The primary and standby servers are in many ways loosely connected. Actions on the primary will have an effect on the standby. An example of the problem situation is an administrator on the primary server running DROP TABLE on a table that is currently being queried on the standby server. Clearly the standby query cannot continue if the DROP TABLE is applied on the standby. If this situation occurred on the primary, the DROP TABLE would wait until the other query had finished. But when DROP TABLE is run on the primary, the primary doesn’t have information about what queries are running on the standby, so it will not wait for any such standby queries. The WAL change records come through to the standby while the standby query is still running, causing a conflict. The standby server must either delay application of the WAL records (and everything after them, too) or else cancel the conflicting query so that the DROP TABLE can be applied.
The test below is to find out if this applies to Oracle as well. I could not find any information for Oracle Active DataGuard.
1. Execute on primary
SQL> select count(*) from test; COUNT(*) ---------- 2052000
2. Execute on secondary
SQL> select * from (select * from (select * from test));
3. Execute on primary
SQL> truncate table test;
4. Ensure that changes have been replicated to standby
Primary SQL> alter system switch logfile; SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#; <THREAD# LAST_APPLIED_LOG ---------- ---------------- 1 71
Standby SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# SEQUENCE# APPLIED ---------- --------- 71 YES
After about 3 minutes, the standby will hit the following error;
ERROR: ORA-08103: object no longer exists
Using oerr utility will show us the following;
[oracle@primary ~]$ oerr ora 08103 08103, 00000, "object no longer exists" // *Cause: The object has been deleted by another user since the operation // began, or a prior incomplete recovery restored the database to // a point in time during the deletion of the object. // *Action: Delete the object if this is the result of an incomplete // recovery.
I know the above error is not too intuitive… but hey, at least it’s a different error from ORA-01555 Snapshot Too Old
Interestingly, I also tested the same scenario with DROP and DELETE command. DROP command behaves exactly the same as TRUNCATE command. However, DELETE command is very different. The standby was allowed to complete the SELECT query. I believe the reason is because of UNDO tablespace in the primary. DROP and TRUNCATE does not generate UNDO information for the database to rollback. However, DELETE will generate UNDO information in the primary which is REPLICATED via REDO stream to the UNDO tablespace in standby. That is how it is able to maintain the read consistency in standby.
However, the above is just my educated guess. If you have a different opinion, do share it with me. I’m definitely all ears!