oraclecdc提取歷史數(shù)據(jù)的方法變化數(shù)據(jù)省公開課一等獎全國示范課微課金獎?wù)n件

CHANGEDATACAPTURE(CDC)

INORACLE

VenkiKrishnababuSeniorOracleDBANordstromIT

11/28AGENDACDCINTRODUCTIONCDCCONCEPTSCDCCASESTUDYCDCPROCESSFLOWCDCPUBLISHER/SUBSCRIBERSETUPCDCBESTPRACTICEDEMOQ&A22/28INTRODUCTIONCDCisanoracletoolwhichcanhelptomanagedatachangesandcapturetheminconsistentmannerwithpredefinedAPIs.CDCisnotadevelopmentsolutiontoperformanyvalidationsortransformationorprovideanyapplicationspecificchecksetc.CDCdoesn’trequireanychangestotheexistingdatamodel.CDCmostcommonlyusedtocapturetransactionalchangesfromanOLTPsystemandpublishthechangestooneormoresubscriptionsystems.33/28CONVENTIONALMETHODTOCAPTUREDATACHANGESCAPTURINGDATACHANGETableDifferencingHeavyresourceintensiveSQLsIntermediatechangevaluescannotbecapturedMultiplechangesononetransactioncannotbecapturedChangeValueBasedonTimestampPotentiallyexpensivequeriesagainstSourceTables.IntermediatechangevaluescannotbecapturedMultiplechangesononetransactioncannotbecapturedPossibilityofmissingachangedrecordduringextractSourcesystemhavetobedesigngivingconsiderationtothisapproach.CustomBuiltTriggersCustomDevelopmentwork.Costassociatedwithextensivedevelopmentandtesting.Costproportionaltothecomplexityoftheproject.Ifnotdesignedproperlycanpotentiallycauseperformanceissuestosourcesystem.44/28WHATCDCCANOFFERCDCofferscostsavingsbysimplifyingtheextractionofchangedatafromdatabaseasitspartofOracle9idatabaseandlaterversions.CDCCaptureschangedataresultantofDMLoperationsincludingthebeforeandafterupdatevaluesofanupdateoperation.Datachangesarecapturedautomaticallytochangetable.VeryfriendlysimpletouseAPIstopublishandsubscribetothechanges.Canbescriptedwithverylittleeffort.AsynchronousCDCcapturesdatawithverylittleperformanceimpact.Bestofbothworlds.Automaticpurgeofconsumedorobsoletechangedatacapturedinchangetable.CDCensuresthateverysubscriberseesallchanges.Efficienttrackingofmultiplesubscribersandprovidesasharedaccesstothechangeddata.55/28WHATCDCCANNOTDO?CDCpurelyworkedbasedonloggedoperations,soanynonloggedDMLoperationsarenotcaptured.CDCdoesn’tsupportdirectloadinsert.CDCcannotbeimplementedontablewithTDE(TransparentDataEncryption)enabled.Asynchronousmodecapturewontworkwithoutsupplementallogging.Althoughdirectselectispossibleonchangetablebuttheextractionofthechangeddataisvalid/supportedonlyviasubscriberviews.66/28Changes#1Changes#2Table#1Table#2PUBLISHERSUBSCRIBERSubscription#1Subscription#2CDCCONCEPTSPUBLISHER/SUBSRIBERMODEL77/28SYNCHRONOUSCDCBasedonTriggersSupportedinOracle9iandlaterversionsTriggersonsourcedatabasecapturesthechangeimmediately.Captureddataismadepartofthesourcesystemtransaction.AvailablewithStandardandenterpriseedition.Addsoverheadtothesourcesystemduringthecapturetime.Built-intriggersareautomaticallycreatedbyinvokingtheCDCAPIs.88/28ASYNCHRONOUSCDC(HOTLOGMODE)ChangesarecapturedfromredologfilesaftertheDMLtransactioniscompleted.Changeddataisnotpartofthesourcetransaction.Minimallatencyinvolved.MinimalPerformanceoverheadtosourcesystem.Logwriterrecordsthecommittedtransactionstoonlineredologs.LocalOracleStreamprocessreadstheredologfilesandcapturesthechangestochangetable.99/28ASYNCHRONOUSCDC(AUTOLOGMODE)Changesarecapturedfromsetofredologfilesmanagedbyredotransportservice.(PartofDataGuardFramework).AutologOnlineMode:Changesarecapturedfromredologfiles.AutologArchiveMode:Changesarecapturedfromarchivelogfiles.Changeddataisnotpartofthesourcetransaction.Minimallatencyinvolved.MinimalPerformanceoverheadtosourcesystem.IfthechangesareextractedtoachangetableinastagingthedataistransferredviaLANusingOracleNet.SourceandstagingdatabaseshouldrunsameOSandOracleVersion.1010/28CDCTERMINOLOGYCHANGESOURCELogicalrepresentationofSourceDatabase.CHANGESETLogicalgroupingofChangedata.Thisgroupingenablestoprovidetransactionconsistentimagesofmultiplechangetablesinthesameset.Changetableswithinachangesetcanbejoined.CHANGETABLEChangedataresultingofDMLoperationarestoredinthetable.Thistableactsacontainer/stagingareatostagechangeddata.SubscriptionviewsarebuiltbasedonChangetable.PUBLISHERPersonwhocapturesandpublisheschangeddata.DBAcreatesandmaintainsschemaobjectsmakeuppartofCDC.Usuallyonepublisherpersourcesystem.1111/28CDCTERMINOLOGY(Contd..)SUBSCRIBERApplicationsandindividualswhoconsumethechangeddata.Multipleapplicationscansubscribetothesamesetofchanges.STAGINGDATABASEDatabasetowhichthecapturedchangedataisapplied.SourceDatabasecanbestagingdatabase.SUBCRIBERVIEWViewthatspecifiesthechangedatafromaspecificpublicationinasubscription.SUBSCRIPTIONWINDOWRangeofrowsinapublicationthatthesubscribercanviewthroughsubscriberviews.1212/28CDCCaseStudyCaptureSupplierinformationchangesfromInventorysystem.NearrealtimeSupplierinformationupdate.Averagefewhundredsupplierinformationchangesperday.Verylittlecodingeffort.Scopeistojustcapturethechangesonsuppliermastertable.CDCImplementationMode:SynchronousPublisher:1ChangeSet:1Subscriber:11313/28CDCCaseStudy(Contd..)PL/SQLtoextract/transformchangedataPublish/subscribeparadigmParalleltransformationofdataStorefinalprocessedchangeddatainstagingtable.OrextractthechangeinatransformedformthechangetableOLTPDBChangeTableBasedOnTriggerOracle9iFinal/DWTablesPL/SQLTransform1414/28CDCCASESTUDY(Contd..)POSSIBLEFUTUREENHANCEMENTSUpgradetoOracle10gRelease2.TurnonSupplementalloggingonSupplierMaster.PerformAsynchronousmodedatachangecaptureusing(HotlogMode).Disablesynchronousmodedatachangecapture.ImplementAsynchronousCDCtoestablishCIM(CommonInformationModel)forproduct.1515/28CDCSETUPOUTLINEPUBLISHERSETUP:

Identifythesourcetables.Setupapublisher.Createchangetables.Optionallysetupdedicatedpublisherandsubscriberaccounts.1616/28CDCSETUPOUTLINE(CONTD.)SUBSCRIBERONETIMESETUP:Setupasubscriber.Subscribetothesourcetables.Activatethesubscription.CYCLICSUBSCRIPTIONPROCESS:SetuptheCDCwindowandextendthewindow.Consumethechangeddatausingsubscriberviews.Purgetheconsumeddatawindow.Repeatthestepsincycle.1717/28CDCPROCESSFLOW(OVERVIEW)1818/28SUBSCRIPTIONWINDOWMOVEMENT1919/28PUBLISHERSETUP--Step1:CreateChangeSetforcdc_demopublishbegindbms_cdc_publish.create_change_set(change_set_name=>'DEMO_DAILY',description=>'ChangeSetforemp_demotable',change_source_name=>'SYNC_SOURCE');end;/--Step2:CreateChangeTableforcdc_demopublishbegindbms_cdc_publish.create_change_table(owner=>'cdc_pub',change_table_name=>'emp_demo_changes',change_set_name=>'DEMO_DAILY',source_schema=>'HR',source_table=>'EMP_DEMO',column_type_list=>'EMPLOYEE_IDNUMBER,FIRST_NAMEVARCHAR2(35),LAST_NAMEVARCHAR2(35),SALARYNUMBER(8,2)',capture_values=>'BOTH',RS_ID=>'Y',ROW_ID=>'Y',USER_ID=>'Y',TIMESTAMP=>'N',OBJECT_ID=>'N',SOURCE_COLMAP=>'Y',TARGET_COLMAP=>'Y',OPTIONS_STRING=>'TABLESPACECDC_DATApctfree5pctused95');end;/grantselectoncdc_pub.emp_demo_changestocdc_sub;2020/28SUBSCRIBERONETIMESETUP--Step1:CreateSubscriptionbegindbms_cdc_subscribe.create_subscription(change_set_name=>'DEMO_DAILY',description=>'ChangedataforWH',subscription_name=>'EMP_DEMO_SUB');end;/--Step2:Subscribetorequiredcolumnsofsourcetablebegindbms_cdc_subscribe.subscribe(subscription_name=>'EMP_DEMO_SUB',source_schema=>'HR',source_table=>'EMP_DEMO',column_list=>'EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY',subscriber_view=>'v_emp_demo_changes');end;/--Step3:ActivateSubscriptionbegindbms_cdc_subscribe.activate_subscription(subscription_name=>'EMP_DEMO_SUB');end;/--Step4:ShowCDCSubscriberViewDefinition.(Optional)descv_emp_demo_changes2121/28SETUPCYCLICSUBSCRITPION--Step1Getthechange(extendthewindow).begindbms_cdc_subscribe.extend_window(subscription_name=>'EMP_DEMO_SUB');end;/--Step2ReadfromtheCDCview(capturethechange)selectemployee_id,first_name,last_name,salaryfromv_emp_demo_changes;--Step3Purgethewindowofconsumeddatabegindbms_cdc_subscribe.purge_window(subscription_name=>'EMP_DEMO_SUB');end;/2222/28SUBSCRIBERVIEWSAMPLEDEFINITIONCREATEORREPLACEFORCEVIEW"CDC_SUB"."V_EMP_DEMO_CHANGES"("OPERATION$","CSCN$","COMMIT_TIMESTAMP$","ROW_ID$","RSID$","SOURCE_COLMAP$","TARGET_COLMAP$","USERNAME$","EMPLOYEE_ID","FIRST_NAME","LAST_NAME","SALARY")ASSELECTOPERATION$,CSCN$,COMMIT_TIMESTAMP$,ROW_ID$,RSID$,SOURCE_COLMAP$,TARGET_COLMAP$,USERNAME$,"EMPLOYEE_ID","FIRST_NAME","LAST_NAME","SALARY"FROM"CDC_PUB"."EMP_DEMO_CHANGES"WHERECSCN$>=538180ANDCSCN$<=538179WITHREADONLY2323/28CDCSOMEBESTPRACTICECaptureoverheadisproportionaltoamountofdatawecapture,socaptureonlyrequire/relevantcolumnswhilecreatingchangetable.CreatededicatedpublisheraccounttoadministerCDCpublications.Splitpublicationstotwosubsetstoprovidesecuredsubsettoonesetofsubscribersandanothersubsettoanothersetofsubscribers.Ifoldvaluesarenotrequireensuretocaptureonlynewvalues.(parameterCAPTURE_VALUES=>’NEW’).Useforceloggingoptiontocaptureeventhechangesoutofdirectloadinsertorinsertswithnologging.Usethisforceloggingwithcautionasitmayintroduceperformanceoverhead.Tominimizeperformanceimpactoptionallyyoucanmovethesourcetabletoaseparatetablespaceandturnonforceloggingattablespacelevelinsteadofdatabaselevel.UseDBMS_CDC_PUBLISH.PURGE…proceduretopurgeobsoletedatafromchangetable.GettheauditinformationaspartoftheCDCcapture.Captureonlyselective/relevantcontrolcolumnsonthechangetable.Useoptions_stringclausetospecifystorageclauseandparameters.Donotspecifyany