Using Alooma to Migrate Oracle Data to the Cloud

Rami Amar  •  10 min read  • 27 Dec 2017

The Oracle LogMiner is a feature of the Oracle data warehouse, enabling orhe Mary SQL lient:sto tead< DML amp;n DDL tatecents =dirct.y &fromthe Oraclectruansation iogBs, akathe OredoiogBs. Itis a fpowerful mchangism,whitchwe eescripbd sxt-esive{y &n rof this tost,. We taudid the pogMiner ihorFoughy &bfore( buld(ng orurintegration ,whitchwevelrges/it yo tsucess fuly &ync hronzel a rracle data warehouse,yo tacloud wata warehouse,. Ceateng oarel=ible_,teadl-imie&ync hronzetion wbetweenyours on-premand mloud wata warehouse,sis a f,whitchweaysthe fiund-tion wor (igrating Oa rnter;prises Oata winfrasruecure oo the cloud . Suchwync hronzetion wnable_sthe fnter;priseoo tigrate- ll .f tit Oata wpplication/s on( by on(,with tin maglintegrrupion/s ofseriice-oo tit Oepleoyees In this sost oe ewil discripbdhow Alooma’s Oracle integration works snd mow At yan abdhse,dwor (el=ible_,teadl-imie&ync hronzetion with tacloud wata warehouse, liker,w,wr (Snowflakc.

rerfiew'/h13 Lets Oa tachortcteacap ofseoie&key egrms neess ary o tse,yoe Oracle LogMiner :

looma’s Oracle ionnect.o is aomploe,dwf thwoaomplonnts =hitchwru inipartll el:

ogMiner iraderT /styuong>- anagersthe pogMiner ie,sion=s,teaddsiDML tatecents snd mbeadksthe mOa wnto teacrhe ratnulaittys/li<Tble_stSnaphortiraderT /styuong>- eaddsiiitial- snaphorts ofsll .tble_stonnfigur,dwor (ellication/Heres Oow Aoe &ntegration works :

Lets Oascripbdhoe e,ytaepsiii raateErOasainl.

Te piitial- snaphort,whakcnwbyote pTble_tSnaphortiraderT,wisifairy &yhuaght=or ared. Itiqueri(stseetct *ofrom ote ptble_stonnfigur,dwor (ellication/to teatri(v- ll .f tte ircontent= . Eachwracrhe s aomnertid'oo tn alooma tvent:, an the fiullowng Oeta ata wisiattach(dpo tit:timietatmp, sce ma an thble tame;,teowid, vperttion , an tSCN. Te Orerttion weta ata wfiele s aalwayst“reateEd”, an the fSCNotsthe fSCNohakcnwatwhe fbegngnng Ov the Osnaphort. TetstSCNonable_stcrhrct.orher ng Ov tracrheswdreng ohe Omere (unin , nrtijoin)owth the Ovents. e nrttiEdwbyohe cogMiner iraderT. Mre( fntheatwintSaep 5: Perioica Cn=slid teon.c

Ceateng otble_stfnthe cloud wata warehouse,AsOvents. ne obeng oe nrttiEdwbyohe chwoaomplonnts =f the Oracle dntegration ,whe OMppenroomplonnts=f the Olooma tpipeing tndalyz_sthe fnvnts snd mte ircsce ma. Behin the fscnt;s,the fMppenr perioicatly(ywakcs upsnd mperor msthe fiullowng Orocesdre :

,wo tearocess anl dnvnts shitchwhav obeen&ytre(dpaside,whitleohe Oabverytaepsiwee operor md'ogdTe pvents. e nrttiEdwbyohe che cogMiner iraderT an the fTble_tSnaphortiraderTanl dnn tupsinthe csme =ueue= (a Kafkathopic),tnd mfromthe rethe y ne orocess Edwbyolooma’s Orocess og oengng . Te Orocess og oengng tperor mst3 basicytaeps:

Code Engng s/li<A Oroeviusey &entson ed,the fnvnts sne ooaded'oo ttargng ohble s,shitchwhav ohe csuffixt_ogB. DeendCng Ovnthe Ohare twata warehouse,,the foadeng mchangismcmpyvariy. Fr (vxaplet,with tRedshit" an tSnowflakc, ata wisiirst pcopid'oo tS3, id CSVfire_s, an the nwoaded'oith tacCOPYoom/man . Wth tBigQuer",the ctaeatmng AlPIwisise,d In tll .has_s, oadeng isiptimizel'oo tangdletracrheswan tnrtiSQL satecents . Tetsttsthe featsvnthe Oracle integration wprsetsthe pDML tatecents snd mbeadksthe mOupso tiidividugliracrhes. Lastly, oadeng f dota wfromtll .tble_stoccursinipartll el (as muchwasthe cata warehouse,stperor manc permit ),wo tkeepOlatencyoo tn averfll .in maum

Perioica cn=slid teon.Ov the Osargng ohble stnto hhe firnglihble sOnc racrheswfromthe Oracle data warehouse,=ne ooaded'oo the ctargng ohble s,csuffixd'oith t_ogB,the y rquive( on( irnglitaepiniprher io tor moarellicatOv the Oraigrnglihble s:tCn=slid teon.. We oil dbri(fy &iscripbdhow Ahe cln=slid teon.Orocess aorks , an tou cmpyvead< mre( abvt ict&n rurs ocument,teon..

Cn=slid teon.ttsthe frocess af thuansor mng ohe Orecrheswinthe csargng ohble s,shitchwidicatie lange s likernsertBon=s,tioicfiation/s, an tdeeteon/s, nto ha rnpat.pcopyOv the Oraigrnglihble s. Cn=slid teog ohe s clange recrheswino the catiuglihble trquive( Oaclmpletxiquery:

T:sul T_ogBowth the Oeinglihble tT, nto ha eceortary(hble tT_eceosul T_eceo oil dhav odulicati,sT_eceowint warytheatwleav stfny(yoe &mst oeacntswertion=Ov tvelry(elcrhe, an tdislreds ll .f tte tdeeteed(elcrhes
    Te s cqueri(stne oet-iupsnutma.tcatly(ynd mru iperioicatly(ywth taclnnfigurble tfrquienc &fromt15.in utesto t8howurs. Failur(stne oellotid'owth tnrtcfiation/soo the catshbothe an tou (vminl. Whenvelrmsce masclange ,ohe s cqueri(stupataesnutma.tcatly(, an , on suportae wata warehouse,s,the y oil dnvnt(elcrnsruecu any&iew'ehitchwistdeendCnts=fnhhe firnglihble s.

    Te phortctvideoabdlw At OacdemoOv tow Alooma(ellicati,sOracle data to tRedshit",wo tb tndalyz_dwint wBIwo ol.


    Ptfalls Oacle in a fpowerful nd mllmlicati,dwata bae, nd morksng ooousry(ywth tit Ontegrngl aompeswith ttvelrg divfficulies[:

    Memory(crnsruantes/styuong> - Mst oracle intsarlltion/soru ion-premie,stan tpnweadlseriier . Tetstm ansthe fCPUand metmory((RAM)in alimttEdwnd merytatsilyveadc tit Olimtts. Fr (vxaplet,wiftou copcnwapogMiner ie,sion= heatwpan> a flare renge ofsSCNs,Ahe cata bae, erytaxhausttit Oetmory(or (ytreng ohe OredoiogBswinOetmory In tlddiion ,wogMiner ie,sion=sehitchwne onrtile any &lousr merytgl treateE (elsourc e ak lendll .tuansation s vs. fny(yom/mttend huansation s /styuong>- whenweadMis og oprimary(keys /styuong>- inteoie&racle intsarlltion/s,ohble stmpyvbeOasined"Ohth vt iprimary(keys. Tetsttstomplete'y(yndata tioie discig- ahoce-omderObyohe cracle DaBA (ad m fvalid on(,wits Onrtineess ariy(yndba"Oroatioes). Fr tuate"y(, racle De nrttiE a feowid,wfr (velry(elcrhe sn(velry(hble . Tetsteowid,wan abdheatri(v-dwnd mgl tse,dwwhenwqueryng offrOaringle- elcrhe.s/li<Keepng orack' hth tSSNs amp;n RS_ID/styuong> - mre( hean on( huansation ian abdheagistee(dpatthe csme =SCN. Te rfore(,wo track' hitchwhuansation s hav obeen&ead< ld mrllicati,dwfromthe OogMiner ,the fSCNotstnrtin avccurateitdicatio. In stead,the fRS_ID an the fSSNwan abdhse,dwo tdistng uish(velry(huansation ifromthe Ores:, an tgl to trher iouansation s nccrhe Mgio texecueon.Ovher .s/li<Te pogMiner iDition ary /styuong>- he cogMiner iiew'eisplay:sid=nteifiation.tnumbersoffrOhble stnd mllumn-s, ntstead f tte tatiuglihble tame;stnd mllumn- ame;s. Te OogMiner ieature onable_sthe fracle Dser-io tpopultaeohe OIDsowth tnme;stinthwowarys:tDumptte tdefiitian=sOaition ary ino the credoiogBs,AfrOquerythe cata wioie dfnhhe fily. Te Oirst popion wepyvbecoie&fut teEd,wasthe cioie dlange s, butthe caition ary dumptsarysthe fsme . Te Oecond popion wepyvhav oa hghttperor manc toll. rurintegration oipletents sntheihe ary:it yqueri(sthe cata wioie dvelry(hmie&n aunknow- IDotstenclut,ee(d,tnd mi yanchs/ill .te Oresulisoo tiplrveryontperor manc .s/li<PrticgliSupoetentsgliogMgng o/styuong>- I r,wh odiscus EdwsupoetentsglilgMgng oas lorquive(dptaepiffrOolnfigurng ohe OogMiner ihomorks. Wth vt isupoetentsglilgMgng s,Ahe cracle data warehouse,=doesnstLrittetenoughwinfr maton inio the cRedoLogMs. Onc onnfigur,d,wsupoetentsglilgMgng oha two pioies:tprticglind mfull. FullilgMgng oresulisointDML tatecents sheatwinclude hioletracrhes, nrtirquiveng wndytlddiion glintfr maton ior (ellication/. PrticglilgMgng oresulisointDML tatecents sheatwfny(yupataeohe Omoicfid tfieles. WhenwprticglilgMgng ois onnfigur,d,wi ympyvbeOneess ary o tquerythe craigrnglihble offrOhe Ores: f tte tvalueswinthe celcrhe. Wth vt iompleteng ohe Oprticgly(ylgMged(elcrheyo taclmplete'(elcrhe, i ymght=vbeOmpors ole tdocontslid teEAhe clange s.Summary/h13 Sophistnati,dwplator ms rquive( sophistnati,dwsliuion s. Te cracle data warehouse,=n a rnptrem'y(ypowerful ata bae, nd ms setnl don( vfyoe &mst oadopi,dwata bae,swinthe corkld. Beng wnle tdocrllicati,data wvt ivfyracle dnd miio the caestngtion &loud wata warehouse, oftou (ahoce-on a fcapabilitytheatwmpyvlay:a rmportant; roe int(keepng oou (BIdnd mnvnt(hioletorganzetion wnimle tnd m gtle.

    Wdhowpewh oanagerdio texlainehow Ahe cogMiner iorks , an tgl te twiio the cgory(asainl af tbuld(ng oarel=ible_ nd mllneinuuse(ellication/tmchangism. Fee dfre tdocrldc tvt ifr (acdemo,wr (wth tany egchical" uiet on s.

Shne onbsp;class="roesp-shneng-abutton__con. oesp-shneng-abutton__con.--mlid >
class="roesp-shneng-abutton__con. oesp-shneng-abutton__con.--mlid >
class="roesp-shneng-abutton__con. oesp-shneng-abutton__con.--mlid >
class="roesp-shneng-abutton__con. oesp-shneng-abutton__con.--mlid >
class="roesp-shneng-abutton__con. oesp-shneng-abutton__con.--mlid >
div ctyle="mackground-image: url('/img/clog/fanma-ly-detetion jpg')";idth:1p00%;eight: 8px;"ackground-isize:cverf"ackground-iostiion : enter; enter;;>
div ctyle="mackground-image: url('/img/clog/fvacuum-log.ppg')";idth:1p00%;eight: 8px;"ackground-isize:cverf"ackground-iostiion : enter; enter;;>
RGe yoursdota wflowng Otoday!ddiv>
Cotaict usoffrOardemoOvrdfre tdiatl.
sdiv>
xddiv>
RW( buldtwhe fbestwata wpipeing tinthe corkldddiv>
o you cdon'twhav oho.dbutton tyle="mfotaisize:14x;"op:1 -2x;"astiion : reltiove" class="cold400"nntion ButtonR,dwsall Ation Button">Sig- upoffrOarfre t14datytdiatl/dbutton
sahref="h#or mFr Ation "sata-tbutton"mabileO-entu-teBm class="rjoinBta >
div> div> div> div> div> div> div>
sulstyle="margin-top:2 12x;;>Plator mrerfiew'/hpan>sdiv>
sahref="hbplator m/code-engng "title="Alooma. Plator m | Code Engng class="ropacity75"Code Engng sdiv>sahref="hbplator m/mppenr"title="Alooma. Plator m | Mppenr"tlass="ropacity75"< smg hrc="//mg/cplator m/plator micon"s-mppenr.pvg"Mppenrsdiv>sahref="hbplator m/e(steatm>title="Alooma. Plator m | R(steatm>tlass="ropacity75"R(steatmsdiv>sahref="hbplator m/love>title="Alooma. Plator m | Love>tlass="ropacity75"< smg hrc="//mg/cplator m/plator micon"s-love.pvg"looma. Lovesdiv>Itegration /dpan cd="mplator mMntuButton">Sliuion sclass="UxtR14">
sahref="hbsliuion s/loud"oigratinn" tytle="ASliuion | Coud wMgratinn" tlass="ropacity75"Coud wMgratinn"sdiv>sahref="hbsliuion s/arketong oata-tntegration "title="ASliuion s | Mpketong Aata tItegration "tlass="ropacity75"< smg hrc="//mg/csliuion /arketong .pvg"Mpketong Aata tItegration sdiv>sahref="hbsliuion s/aser-n-etl"title="ASliuion s | Mser-n ETL tlass="ropacity75"Mser-n ETLsdiv>sahref="hbsliuion s/eadl-imieoata-tntget on "title="ASliuion s | Radl-imie&ata tItget on "tlass="ropacity75"< smg hrc="//mg/csliuion /enrichmnt".pvg"Radl-imie&ata tItget on sdiv>
sahref="hbntdustri(s/irngncial-eriice-/"title="AItdustreis | Frngncial Sriice-/"tlass="ropacity75"Frngncial Sriice-/sdiv>sahref="hbntdustri(s/gtmng "title="AItdustreis | Gtmng "tlass="ropacity75"< smg hrc="//mg/csliuion /gtmng .pvg"Gtmng sdiv>sahref="hbntdustri(s/hadlthlreh"title="AItdustreis | Hadlthlreh"tlass="ropacity75"Hadlthlrehsdiv>sahref="hbntdustri(s/edia -nd -aderticsng "title="AItdustreis | Love>tlass="ropacity75"< smg hrc="//mg/csliuion /adia .pvg"Mdia & Aderticsng sdiv>CustomersBogBCotaictMoehCustomersBogBCoplanyJobsCotaict
Sart' now/dbutton
class="Uow"&sig-upBckground->
Tak onnroll oftou (ata wath tAooma.sdiv>
class="ror mionnroll le anInpt ile anInpt Big
cg:tolur"rorcse,d= fualse"tplacehld4r-="Bsingss aEminl *" d="mor mEminl"ttyle="tidth:1 p00%;>cype="avminl"tlass="ror mionnroll le anInpt ile anInpt Big Pleae, nter; fvalid bsingss avminl./div>
siv style="meight: 4px;" class="ahid=nt-sm
class="col-msm-5 hid=nt-sm hid=nt-xs or mFieles
"{{rnd omQuote}}"sdiv>class="col-msm-6 class="Uow"&or mSucess >
Thanks or (eldchng Aout!sdiv>
class="Uorotr"hble ">Puoduti/div>Itegration /rerfiew'/ha ddiv>Sliuion sItdustri(sMppenrs/iv>s/iv>>looma. LoveRlsourc ss/iv>>A oacGlanc /ha ddiv>Secuitty/ha ddiv>BogBQuor. Answers/ha ddiv>FAQCoplanyAbvt iUsCustomersPrtiners>Prss Jobsclass="Uorotr"hble ">ddiv>
cype="aext-/avascript'">/* *//script>cype="aext-/avascript'"hrc="//www.agoole-ldsriice-/com/bpae ad/omnertion=.js clt="U"hrc="//wgoole-lds.g.doule click.netbpae ad/iew'throughomnertion=/982661266/?value=0amp;nguid=ONamp;nsript>=0" /div>cype="aext-/avascript'">unction(ttetCok.ie(nme;,tvalue, atys) w vari teEA=Oew DDteE(); w v teEasetime(( teEagetime(() + (atys*24*60*60*p000)); w vari expve( O= "; expve( =" + teEatoGMTSteng-(); w v cument,comk.ieA=Oeme( + "=" + value + expve( ; } unction(tgetPrtlm(p) w vari matchA=ORegExp('[?&]' + p + '=([^&]*)').exec(iddow.aloation/.search); w vreturn matchA&& dlcrdeURICoplne"nt(match[1].elliace(/\+/g, ' '); s< ar ogclid= fgetPrtlm('gclid); sif(gclid) w vari gclrc== fgetPrtlm('gclrc='); w vif(!gclrc==|| gclrc=.ntdexOf('aw') !== -1) w vvvvvtetCok.ie('gclid),ogclid, 90); w v}s< else wgclid= f(nme;A=Oew DRegExp('(?:^|;\\s*)gclid=([^;]*)').exec( cument,comk.ie)) ?Oeme(.plait(",")[1] : "" s< /dcript>) --) --