Oracle GoldenGate in the AWR report?–A bit misleading

Today while I was doing reviews of AWR reports on a database, I came across a wait event that I was a bit surprised to see.  The wait event in question is “Streams miscellaneous even”.   Since I have been reviewing this environment for a customer for about two weeks, I know the customer is not using Oracle Streams.  In fact, this customer is using Oracle GoldenGate 11G (11.2.0.1.0). 

Since I have established that the environment is not using Streams but Oracle GoldenGate, what about this wait event?  On one hand, this wait event provides some insight on the direction of Oracle GoldenGate.  It appears that Oracle is integrating GoldenGate with Streams on some level.  On the other hand, Oracle GoldenGate is not directly integrated with the database in this environment; the customer is using Classic Captures processes with GoldenGate.  Makes you wonder what is going on?

In researching this wait event (Streams miscellaneous even), I was directed to MOS Note 1317122.1.  In reading the note, it is identified that this wait event is actually a bug Oracle GoldenGate (11.1.1.0.0) or later.  The note also specifies that the bug is fixed in Oracle GoldenGate 12c (12.1.2.0.0).  What exactly is the cause of this error/bug in Oracle GoldenGate?

Cause

The cause for this bug has been identified as being incorrectly associated with the wait for a redo log with the “Streams miscellanous event” rather than the IDLE wait event “Streams capture: waiting for archive log”.   Additionally, this bug is triggered by using the TRANLOGOPTIONS DBLOGREADER in your extract parameter files.

Solution

The simple solution to this problem is to upgrade to Oracle GoldenGate 12c (12.1.2.0.0); however, that is not always feasible without planning.   Before upgrading, you should also make sure that you have this bug within your environment.  You can check for this issue/bug by tracing the SPID for the GoldenGate extract processed.  Perform the following:

1.  As SYSDBA get the operating system process id associated with the “Streams miscellaneous event’.

select s.sid, s.serial#, s.process, p.spid, p.pid, p.program from v$session s, v$session_wait sw, v$process p where s.sid=sw.sid and sw.event = ‘Streams miscellaneous event’ and s.paddr=p.addr; 

2. Attach to the processes and generate a SQL trace file.

oradebug setospid <spid>;
oradebug TRACEFILE_NAME
oradebug event 10046 trace name context forever, level 12
==> Trace for 5 minutes <==
oradebug event 10046 trace name context off

Once you have the trace file and review it, you will see something similar to the waits listed below.  These waits are associated with the SPID of the extract that was waiting on the “Streams miscellaneous event”. 

WAIT #0: nam=’log file sequential read’ ela= 295 log#=0 block#=1 blocks=1 obj#=-1 tim=1386701225296657
WAIT #0: nam=’log file sequential read’ ela= 394 log#=0 block#=1038323 blocks=55 obj#=-1 tim=1386701225297159
WAIT #0: nam=’log file sequential read’ ela= 456 log#=0 block#=1038323 blocks=55 obj#=-1 tim=1386701225297651
WAIT #0: nam=’Streams miscellaneous event’ ela= 496159 TYPE=16 p2=0 p3=0 obj#=-1 tim=1386701225793859

Conclusion

If you start to see “Streams miscellaneous event” showing up as a top wait event in the AWR reports, you may be hitting this bug within Oracle GoldenGate.  Once you go through the steps outlined in the MOS Note or the steps above; if you determine that this bug is not the cause of the wait event, than you may have another issue to address. 

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like: