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

AdministeringaStreamsEnvironmentObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:AlteraStreamsenvironmentRemoveStreamscomponentsAdministerrulesthatareusedbyStreamscomponentsTroubleshootcommonapplyfailuresManagingtheCaptureProcessTheDBMS_CAPTURE_ADMpackagehasthefollowingproceduresformanagingthecaptureprocess:START_CAPTURESTOP_CAPTUREDROP_CAPTURESET_PARAMETERINCLUDE_EXTRA_ATTRIBUTEALTER_CAPTURE,whichenablesyouto:SpecifyorremovearulesetSetthestartSCNSpecifyingExtraAttributesforCaptureBEGINDBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(capture_name=>'CAPTURE1',attribute_name=>'username');DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(capture_name=>'CAPTURE1',attribute_name=>'tx_name');END;/ModifyingtheCaptureProcessCaptureprocessparameterscontrolthewayinwhichacaptureprocessoperates.SetparameterswiththeSET_PARAMETERprocedureofDBMS_CAPTURE_ADM.BEGINDBMS_CAPTURE_ADM.SET_PARAMETER(capture_name=>'capture1',parameter=>'parallelism',value=>'4');END;/ManagingCaptureProcessSCNsCAPTUREFirst

SCNRequired

checkpoint

SCNMaximum

checkpoint

SCN9248909579921025868105310941117Start

SCNModifyingFIRST_SCNandSTART_SCNIncreasethevalueoffirst_scnforacaptureprocessto:RemoveunneededLogMinercheckpointinformationfromtheLogMinerdictionaryEnsurethatoldarchivedredologscanbesafelyremovedfromdiskSetthestart_scnforacaptureprocesstoatimeinthepasttorecapturechangesfromtheredologs.EXECDBMS_CAPTURE_ADM.ALTER_CAPTURE(-capture_name =>'CAPTURE1',-first_scn =>2524278);ModifyingFIRST_SCNandSTART_SCNIncreasethevalueoffirst_scnforacaptureprocessto:RemoveunneededLogMinercheckpointinformationfromtheLogMinerdictionaryEnsurethatoldarchivedredologscanbesafelyremovedfromdiskSetthestart_scnforacaptureprocesstoatimeinthepasttorecapturechangesfromtheredologs.EXECDBMS_CAPTURE_ADM.ALTER_CAPTURE(-capture_name =>'CAPTURE1',-first_scn =>2524278);AlteringFIRST_SCNforaCaptureProcessFirst

SCNStart

SCNRequired

checkpoint

SCNMaximum

checkpoint

SCN1First

SCNStart

SCNRequired

checkpoint

SCNMaximum

checkpoint

SCN2ABCDEFGHIJK11222RemovingUnnecessary

ArchivedLogFilesTheDBA_LOGMNR_PURGED_LOGdatadictionaryviewliststhearchivedredologfilesthatarenolongerneededbyanycaptureprocess.SQL>SELECT*FROMDBA_LOGMNR_PURGED_LOG;FILE_NAME---------------------------------------------------/u01/app/oracle/oradata/amer/archive/amer_1_415_531060081.dbf/u01/app/oracle/oradata/amer/archive/amer_1_416_531060081.dbf/u01/app/oracle/flash_recovery_area/ED_AMER/archivelog/2004_07_27/o1_mf_1_417_0jdz18wo_.arcPurgingtheStagingQueueTheDBMS_AQADM.PURGE_QUEUE_TABLEprocedure:RemovesmessagesfrompersistentqueuesIsusefulforbothsingle-consumerandmulticonsumerqueuesCanbecustomizedtopurgeonlymessagesthatmeetspecificconditionsPurgingtheStagingQueueWhenpurgingeventsfromaqueue,youcanspecify:ApurgeconditionWhetheranexclusivelockisobtainedduringthepurgeoperationDECLAREpurge_optDBMS_AQADM.aq$_purge_options_t;BEGINpurge_opt.block:=TRUE;DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table=>'STRMADMIN.STREAMS_QUEUE_TABLE',purge_condition=>'queue=''STREAMS_QUEUE''',purge_options=>purge_opt);END;PurgeConditions:ExamplesPurgealleventsinaqueuetable

Purgealleventsforaqueueinaqueuetable

Purgealleventsinaparticularstateforaqueue

PurgeallmessagesforaconsumerPurgemessagesbasedonenqueuetime

purgecondition:NULLpurgecondition:queue='streams_queue'purgecondition:queue='hr_queue'andmsg_state='READY'purgecondition:consumer_name='ORDERS_DEQ'purgecondition:enq_time>'30-MAR-06'ManagingPropagationsUsetheDBMS_PROPAGATION_ADMpackagemethodstomanagepropagation:DBMS_PROPAGATION_ADM.START_PROPAGATIONmethodstartsapropagation.DBMS_PROPAGATION_ADM.STOP_PROPAGATIONstopsapropagation.AlteringthePropagationScheduleBEGINDBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(queue_name=>'strmadmin.hr_queue',destination=>'SITE2.NET',duration=>300,next_time=>'SYSDATE+900/86400',latency=>30);END;/ManagingApplyProcessesTheDBMS_APPLY_ADMpackagecontainsthefollowingprocedures:START_APPLYALTER_APPLY,whichyoucanuseto:SpecifyorremovearulesetSpecifyorremoveamessage,DDL,orprecommithandlerSpecifyanapplyuserSpecifyorremoveatagforapplyeventsSTOP_APPLYDROP_APPLYModifyingtheApplyProcessSetparameterswiththeSET_PARAMETERprocedureofDBMS_APPLY_ADM.ThevalueforaparameterisalwaysenteredasaVARCHAR2,regardlessoftheparameter’sdatatype.EXECDBMS_APPLY_ADM.SET_PARAMETER(-'apply_site1_lcrs','disable_on_error','N');StartingStreamsProcessesThecaptureandapplyprocessesaredisabledaftercreation.UsetheSTART_APPLYorSTART_CAPTUREprocedurestoenabletheprocesses:BEGINDBMS_APPLY_ADM.START_APPLY(apply_name=>'apply_site1_msg');END;/StoppingStreamsProcessesUsetheSTOP_APPLYorSTOP_CAPTUREprocedurestodisabletheprocesses:Theprocessstatusismaintainedacrossdatabaseshutdowns.Anapplyprocessstopsandrestartsautomaticallyifparallelismisaltered.RuntheSTOP_PROPAGATIONproceduretostopanexistingpropagation.EXECUTEDBMS_CAPTURE_ADM.STOP_CAPTURE(-capture_name=>'hr_capture');ManagingStreamsProcessRuleSetsSpecifyorremovearulesetforaprocess:DBMS_CAPTURE_ADM.ALTER_CAPTUREDBMS_APPLY_ADM.ALTER_APPLYDBMS_PROPAGATION_ADM.ALTER_PROPAGATIONAddrulestoarulesetforaprocess:DBMS_STREAMS_ADM.ADD_*_RULESDBMS_STREAMS_ADM.ADD_*_PROPAGATION_RULESDBMS_RULE_ADM.ADD_RULERemovearulefromarulesetusedby

aStreamsprocess:DBMS_STREAMS_ADM.REMOVE_RULEDBMS_RULE_ADM.REMOVE_RULEAddingNewRules:ExampleBEGINDBMS_STREAMS_ADM.ADD_SUBSET_PROPAGATION_RULES(table_name=>'HR.EMPLOYEES',dml_condition=>'job_idLIKE''SA%''',streams_name=>'prop_to_site3',

source_queue_name=>'strmadmin.hr_queue',

destination_queue_name

=>

'ix.streams_queue@',source_database=>NULL);END;/ManagingRulesandRuleSetsByusingtheDBMS_RULE_ADMpackage,youcan:AlteraruleChangearuleconditionChangeorremovetheruleevaluationcontextChangeorremovetherule’sactioncontextChangeorremovethecommentforaruleRemovearulefromarulesetDroparulefromthedatabaseDroparulesetfromthedatabaseAlteringaRuleBEGINDBMS_RULE_ADM.ALTER_RULE(rule_name=>'strmadmin.hr_emp_dml',condition=>':dml.get_object_owner()=''HR''AND:dml.get_object_name()=''EMPLOYEES''

AND:dml.get_compatible()=

DBMS_STREAMS.COMPATIBLE_9_2',evaluation_context=>NULL);END;/DroppingRuleSetsUsethedrop_unused_rule_setsparameterof:DBMS_CAPTURE_ADM.DROP_CAPTUREDBMS_APPLY_ADM.DROP_APPLYDBMS_PROPAGATION_ADM.DROP_PROPAGATIONDropstherulesetsalongwiththeStreamsclientEXECUTEDBMS_CAPTURE_ADM.DROP_CAPTURE(-capture_name=>'hr_capture',-drop_unused_rule_sets=>TRUE);RemovingaStagingQueueEXECUTEDBMS_STREAMS_ADM.REMOVE_QUEUE(-queue_name=>'STRMADMIN.streams_queue',-drop_unused_queue_table=>TRUE,-cascade=>TRUE);CascadeRemovingAllStreamsComponentsEXECUTEDBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();ManagingStreamswithEMNotesTroubleshootingApplyFailuresChecklist:Checkthestateoftheprocesses.DisabledWastheapplyprocessstoppedbyanotherDBA?Wasthecaptureprocesseverstarted?AbortedChecktheappropriatetracefileformessages.Arethereerrorsintheerrorqueue?Checkapplyprocesslatencyfornewlystartedapplyprocesses.Verifycapture,propagation,andapplyrules.Checkforprocesserrormessages.IstheApplyProcessCurrent?Querydictionaryviewstodeterminelatencyandactivity:V$STREAMS_APPLY_COORDINATORDBA_APPLY_PROGRESSSELECT(HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400"LatencyinSeconds",HWM_MESSAGE_CREATE_TIME"EventCreation",HWM_TIME"ApplyTime",HWM_MESSAGE_NUMBER"AppliedMessage#"FROMV$STREAMS_APPLY_COORDINATOR;CheckingforRulesConfirmthattheStreamsrulesetsarenotemptyandthatalltherulesareenabledbyqueryingDBA_STREAMS_RULESandDBA_RULE_SET_RULES.Checktheactualruleconditions.SELECTstreams_name,streams_type,sr.rule_set_name,sr.rule_nameFROMdba_rule_set_rulesrsr,

dba_streams_rulessrWHEREsr.rule_name=rsr.rule_nameANDrsr.rule_set_rule_enabled!='ENABLED';SELECTrule_name,streams_type,rule_conditionFROMDBA_STREAMS_RULES

WHERErule_name='ORDERS%';ORA-26687:InstantiationSCNNotSetForapplytoexecutecapturedchangeevents,thetargetdatabaseobjectmustbeinstantiated.IfapplyfailswithORA-26687,searchformissinginstantiationSCNs:DBA_APPLY_INSTANTIATED_OBJECTS(tableDML)DBA_APPLY_INSTANTIATED_SCHEMAS(schemaDDL)DBA_APPLY_INSTANTIATED_GLOBAL(globalDDL)Correcttheerrorby:SettingtheinstantiationSCNwithDataPump,ortheExportandImportutilitiesExecutingtheSET_*_INSTANTIATION_SCNprocedurestomanuallysettheinstantiationSCNStreamsDictionaryInformationStreamsdictionaryinformationmustbeavailableateachdestinationsite:Containsobjectnumbers,objectnames,objecttypes,columnnames,andothercriticalinformationforeachsourceobjectStoredinLogMinertablesStreamsdictionaryinformationisgeneratedatthesourcesitethrough:DBMS_CAPTURE_ADM.PREPARE_*_INSTANTIATIONDBMS_STREAMS_ADM.ADD_*_RULEDBMS_CAPTURE_ADM.BUILDCheckingforApplyReaderTraceFilesIfthedestinationStreamsdictionarydoesnotcontaininformationforanobjectreferencedinacapturedevent,thenitgeneratesanerror.Searchtheparallelexecutionserverprocesstracefilefortheinstanceforthephrase:

MISSINGStreamsmulti-versiondatadictionaryDeterminewhatobjectismissingfromtheStreamsdictionary.Missingdictionaryinformationisnotanapplyerror.grepMISSING*p0*MissingMultiversion

DataDictionaryInformationTopopulatetheStreamsdictionaryinformationattheapplysite:Determinethescopeofmissinginformation(table,schema,orglobal)attheapplysiteAtthesourcedatabase,prepareforinstantiation:DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION('HR');SummaryInthislesson,