Conflict Management in Oracle Active DataGuard 11gR2

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

Summary

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!

Cheers,
Wei Shan

Advertisements

One thought on “Conflict Management in Oracle Active DataGuard 11gR2”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s