Saturday, March 05, 2011

Oracle GoldenGate, ASM and DBLOGREADER option

Some time ago I did a write up detailing how Oracle GoldenGate interacts with Oracle ASM. The technology stack where the Extract process establishes a connection with the ASM instance and then reads file contents using dbms_diskgroup package is quite cumbersome to say the least. When compared to how GG reads redo from a filesystem (detailed here), the way GG has to deal with ASM is quite inefficient to say the least. In fact, as part of my original article, the recommendation was to use a bequeath connection when possible to cut on the amount of overhead. Nowadays the use of bequeath connection became an official recommendation from Oracle outlined in Oracle GG 11G Release Notes.

DBLOGREADER

I was quite excited to see that Oracle made some progress in it's newer release of GoldenGate (11.1). Here is what the documentation says about the new option:

A new DBLOGREADER option was added to TRANLOGOPTIONS to enable Extract to use a
newer API for capturing from an Oracle ASM instance. This feature is available as of Oracle
10.2.0.5.

This was indeed promising, however, what exactly has changed remained to be discovered.

The first change you're going to notice when using DBLOGREADER is that the Extract process establishes a connection with the RDBMS instance (not an ASM instance). If you trace the relevant server side process then you'll observer the following event being emitted each time it reads from the redo:

WAIT #0: nam='log file sequential read' ela= 3 log#=0 block#=102653 blocks=2000 obj#=-1 tim=1299345636565911

One of the advantages of the new option is the ability to use a much larger buffer (the old mechanism was limited to something around 28K which is abysmal for any system with even moderate redo generation rate). As we can see from the above, we had 2000x512=1000K worth of data read in one shot so at least that part works as advertised. But how is it done?

A stack dump reveals the following:

[oracle@ora11gr2 ggs_11g]$ pstack 4740
#1  0x00000000090bae87 in sskgpwwait ()
#2  0x00000000090b9f9a in skgpwwait ()
#3  0x0000000008d29f93 in ksliwat ()
#4  0x0000000008d29541 in kslwaitctx ()
#5  0x0000000008d269ab in kslwait ()
#6  0x00000000071f8566 in knloggRedoRead ()
#7  0x00000000071f9e05 in knloggmain ()
#8  0x0000000008eae67a in opiodr ()
#9  0x00000000090467f9 in ttcpip ()
#10 0x0000000001722f86 in opitsk ()
#11 0x0000000001727c26 in opiino ()
#12 0x0000000008eae67a in opiodr ()
#13 0x000000000171eedc in opidrv ()
#14 0x0000000001d95f5f in sou2o ()
#15 0x0000000000a07935 in opimai_real ()
#16 0x0000000001d9b408 in ssthrdmain ()
#17 0x0000000000a078a1 in main ()

Look at the functions in line #6 and #7 (knloggmain() and knloggRedoRead()). These functions follow right after OPI (Oracle Program Interface) layer which makes this code path look rather efficient. No need to deal with compilation (KK) and execution (KX) layers. Just a direct and straightforward call to the functions we need. Indeed, when I did some quick and dirty tests (using 11.2.0.2), the new code path was able to archive about 90% performance compared to just reading your redo from ext3 filesystem.

The only question I have about all this is a security paradigm behind knloggmain() calls. This function appears to be directly exposed through the OPI layer and, while this helps archive greater efficiency, how does it control who can who can not calls it? You may not necessarily want somebody connect to your database and start reading your redo stream as you may get all kinds of sensitive information right there. I haven't spent any time figuring that one out yet though.

4 comments:

  1. Hi Alex, I'm trying to get this combo working with 11.2.0.3 RAC, online and arch logs on ASM and DBLOGREADER.

    It connects to ggate account fine and establishes where the online log is, i.e it's ASM path but then throws the following and abends.

    2012-03-09 08:26:36 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ext1.prm: Opening ASM file +DG2/1_1333_768563616.arc in DBLOGREADER mode: (308) ORA-00308: cannot open archived log '+DG2/1_1333_768563616.arc' ORA-17503: ksfdopn:2 Failed to open file +DG2/1_1333_768563616.arc ORA-15173: entry '1_1333_768563616.arc' does not exist in directory '/'Not able to establish initial position for begin time 2012-03-08 10:29:42.

    Any ideas why ? or what I've missed ?

    Thanks in advance, Jim.

    ReplyDelete
  2. Jim, kind of a late reply as your comment got stuck in my spam queue but anyway... it tries to open an archived log file which does not exist?

    ReplyDelete
  3. Hi Alex,

    Can DBLOGREADER be used with NON-ASM databases?

    Regards,
    Navin

    ReplyDelete
    Replies
    1. Hello,

      yes you can use dblogreader if you do not have ASM.

      Delete