Oracle英文版培訓(xùn)課件之Implement Streams：les11

ApplyConceptsandConfigurationObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:Describetheapply-processarchitectureListwhicheventscanandcannotbeappliedConfigureoneormoreapplyprocessesforadatabaseQuerythedatadictionaryforinformationabouttheapply-processconfigurationWhatIsApply?Applyis:TheautomaticdequeuingandprocessingofeventsfromaStreamsqueueorqueuebufferImplementedasoneormoreOraclebackgroundprocessesEachapplyprocess

hasanapplyuser.SGAStreamspoolProcessingStreamsEventsAsingleapplyprocessdequeueseithercapturedeventsoruser-enqueuedeventsfromaspecificqueue.Forcapturedevents,theapplyprocesscan:ExecutetheDMLorDDLeventdirectlyPasstheeventasaparametertoauser-definedprocedureUser-enqueuedeventsinaSYS.AnyDataqueuecanalsobedequeuedexplicitlythroughauser-createdapplication.Capturedeventscannotbeexplicitlydequeued.ApplyingDDLEventsSuccessfulapplicationofDDLeventsrequiresthatthesourceanddestinationtargetshavematching:StorageparametersforCREATEstatementsTablespacenamesandspecificationsPartitioningnamesandspecificationsConstraintandindexnamesandspecificationsCREATETABLErange_sales

(prod_idNUMBER(6)PRIMARYKEY,

time_idDATE,

amount_soldNUMBER(10,2))

TABLESPACEusersPARTITIONBYRANGE(time_id)(PARTITIONsales_q1_2000VALUESLESSTHAN

(TO_DATE('01-APR-2000','DD-MON-YYYY')),...);DDLEventsThatAreNotAppliedAnapplyprocessdoesnotsupportthefollowingtypesofDDLevents:[ALTER|CREATE|DROP]MATERIALIZEDVIEW[ALTER|CREATE|DROP]MATERIALIZEDVIEWLOG[CREATE|DROP]DATABASELINKCREATETABLE…ASSELECTonaclusteredtableCREATESCHEMAAUTHORIZATIONRENAMEOtherConsiderationsforApplyingDDLTheuserwhoisspecifiedasthecurrent_schemaintheDDLLCRmustexistatthedestinationdatabasefortheDDLLCRtobeapplied.Avoidusingsystem-generatednamesinschemaswhereDDLiscaptured.CREATETABLEprojects(proj_idNUMBERCONSTRAINTproj_id_pkPRIMARYKEY,projnameVARCHAR2(30)CONSTRAINTpname_nnNOTNULL,dept_idNUMBERREFERENCESHR.departments(department_id));ALTERTABLEprojectsDROPCONSTRAINTsys_c005844;ApplyingEventsEventsareappliedbyanapplyuser.Atransactionisconsideredappliedorconsumedwhenanapplyprocessdoesoneofthefollowing:CommitsacompletedtransactionPlacesatransactionintheerror

queueandcommitsthedequeue

transactionErrorQueueStoresinformationabouttransactionsthatcouldnotbesuccessfullyappliedonthelocaldatabaseIscreatedautomaticallyContainsallLCRsforeachtransactionnotappliedStoreserrortransactionsforallapplyprocessesinthedatabaseRequiredApplyUserPrivilegesForausertobeabletoapplyevents,thefollowingprivilegesarerequired:SecurequeueuserprivilegesforthequeueDequeueprivilegesforthequeueEXECUTEprivilegesfor:RulesetsusedbytheapplyprocessApplyhandlerPL/SQLprocedurescalledbytheapplyprocessRule-basedtransformationfunctionscalledbytheapplyprocessDMLandDDLprivilegesonthedatabaseobjectsandschemasConfiguringApplyProcessesYoumustconfigure:AseparateapplyprocessforeachsourcesiteSeparateapplyprocessesforcapturedand

user-enqueuedeventsEachapplyprocessworksindependentlyofotherapplyprocessesinthesamedatabase.NYAP_L1AP_AUAP_L2LCRsUser-enqueuedeventsLCRsLondonSydneyApplyProcessComponentsTheapplyengineconsistsof:OnereaderserverprocessOnecoordinatorprocessOneormoreapplyserverprocessesParallelizationofapplymaximizesconcurrency.ReaderApplycoordinatorApplyApplyApplyStagingareaApplyRulesDeterminewhicheventsareappliedCanbepositiveornegativeArespecifiedatthetable,schema,orgloballevelIncludesubsetrulesCanbecustomizedtofurtherrestrictthedatathatisappliedEnqueueDestinationDuringApplyBEGINDBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(rule_name=>'strmadmin.orders5',destination_queue_name=>'ix.order_evt_queue');END;/enqueueStagingQDestQueueSGAStreams

poolSharedpoolLargepoolExecutionDirectivesDuringApplyBEGINDBMS_APPLY_ADM.SET_EXECUTE(rule_name=>'strmadmin.orders5',execute=>TRUE);END;/enqueueStagingQDestQueueSGAStreams

poolSharedpoolLargepoolApplyProcessandColumnDiscrepanciesTheapplyenginesupportscolumndiscrepanciesbetweenthesourceanddestinationtables:ColumndatatypemismatchUseatransformationorDMLhandleronapplyFewercolumnsatthedestinationUseaDMLhandlerortransformationtodiscardchangestoextracolumnsExtracolumnsatthedestinationUsedefaultvalueorNULLextracolumnsIfextracolumnsareusedfordependencyevaluations,thetransactionisplacedinerrorqueue.ColumnDependenciesDependenciesmayexist:AmongLCRswithinasingletransactionBetweendifferenttransactionsThefollowingtypesofcolumnsarerequiredfordependencycomputations:Allkeycolumns(ForINSERTandDELETEstatements)Allconstraint-relatedcolumns(ForUPDATEstatements)Allcolumnsrelatedtoaconstraintifthatconstraint’scolumnischangedVirtualDependency:DefinitionAssiststheapplyprocessincomputingtransactiondependenciesbeyondtheexistingconstraintsEnablesapplyhandlerstoprocesstransformedLCRscorrectly,avoidingerrorsCanimproveperformancebyallowingtheapplyhandlerstoprocessLCRsinparallelDataApplication

codeTableIntegrity

constraintVirtualDependencyDefinitionDependencyTypesValuedependency:CreatesavirtualforeignkeythatcaninvolvemorethantwotablesUsesLCRsfromonlyonesourceRequiressupplementalloggingObjectdependency:Definesaparent-childrelationshipRequiresaparentrowchangewithalowerSCNtobeappliedbeforeachildrowchangewithahigherSCNShouldbeusedonlyiftherelationshipisnotdefinedbyconstraintsatthetargetdatabaseDefiningDependenciesValuedependencies:Objectdependencies:DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY('DEMO.EMPLOYEES','HR.DEPARTMENTS');DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY('EMP_VPK','DEMO.EMPLOYEES',

'LAST_NAME,HIRE_DATE,JOB_ID');DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY('EMP_MGR_VFK','DEMO.EMPLOYEES','EMPLOYEE_ID');DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY('EMP_MGR_VFK','HR.DEPARTMENTS','MANAGER_ID');DefiningDependenciesCreatinganApplyProcessYoucancreateanapplyprocess:AutomaticallybyusingtheADD_*_RULESprocedureofDBMS_STREAMS_ADMManuallybyusingtheCREATE_APPLYprocedureofDBMS_APPLY_ADMASYS.AnyDataqueuemustexistbeforecreatingtheapplyprocess.Theapplyprocessiscreatedinthedisabledstate.CreatinganApplyProcessExample:

CreatinganapplyprocessforcapturedeventsBEGINDBMS_STREAMS_ADM.ADD_SCHEMA_RULES(schema_name=>'HR',streams_type=>'apply',streams_name=>'apply_site1_lcrs',queue_name=>'ix.streams_queue',include_dml=>TRUE,include_ddl=>FALSE,source_database=>'');END;/CreatinganApplyProcessExample:

Manuallycreatinganapplyprocessforapplying

user-enqueuedLCReventsBEGINDBMS_APPLY_ADM.CREATE_APPLY(queue_name=>'strmadmin.streams_queue',apply_name=>'apply_site1_lcrs',rule_set_name=>'ix.stream1_rs',negative_rule_set_name=>'ix.stream1_neg_rs',apply_user=>'IX',apply_tag=>NULL,apply_captured=>FALSE);END;ApplyProcessParametersALLOW_DUPLICATE_ROWSCOMMIT_SERIALIZATIONDISABLE_ON_ERRORDISABLE_ON_LIMITMAXIMUM_SCNPARALLELISMTIME_LIMITTRACE_LEVELTRANSACTION_LIMITTXN_LCR_SPILL_THRESHOLDWRITE_ALERT_LOGMonitoringApplyALL_APPLYALL_APPLY_ENQUEUEALL_APPLY_ERRORALL_APPLY_EXECUTEALL_APPLY_KEY_COLUMNSALL_APPLY_PARAMETERSALL_APPLY_PROGRESSDBA_APPLY_OBJECT_DEPENDENCIESDBA_APPLY_VALUE_DEPENDENCIESDBA_APPLY_SPILL_TXNApplyProcess:StatisticsV$STREAMS_APPLY_COORDINATORV$STREAMS_APPLY_READERV$STREAMS_APPLY_SERVERV$STREAMS_TRANSACTIONV$BUFFERED_SUBSCRIBERSApplyProcessStatisticsCheckingtheApplyProcessVerifyapplyprocessconfiguration:SELECTapply_name,apply_capturedCAP,

status,status_change_time,

rul