Oracle英文版培训课件之Implement Streams：les14

《Oracle英文版培训课件之Implement Streams：les14》由会员分享，可在线阅读，更多相关《Oracle英文版培训课件之Implement Streams：les14（44页珍藏版）》请在装配图网上搜索。

1、Administering a Streams EnvironmentObjectivesAfter completing this lesson,you should be able to do the following:Alter a Streams environmentRemove Streams componentsAdminister rules that are used by Streams componentsTroubleshoot common apply failuresManaging the Capture ProcessThe DBMS_CAPTURE_ADM

2、package has the following procedures for managing the capture process:START_CAPTURESTOP_CAPTUREDROP_CAPTURESET_PARAMETERINCLUDE_EXTRA_ATTRIBUTEALTER_CAPTURE,which enables you to:Specify or remove a rule set Set the start SCNSpecifying Extra Attributes for CaptureBEGIN DBMS_CAPTURE_ADM.INCLUDE_EXTRA_

3、ATTRIBUTE(capture_name =CAPTURE1,attribute_name =username);DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(capture_name =CAPTURE1,attribute_name =tx_name);END;/Modifying the Capture ProcessCapture process parameters control the way in which a capture process operates.Set parameters with the SET_PARAMETER p

4、rocedure of DBMS_CAPTURE_ADM.BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER(capture_name=capture1,parameter=parallelism,value=4);END;/Managing Capture Process SCNsCAPTUREFirstSCNRequiredcheckpointSCNMaximumcheckpointSCN9248909579921025868105310941117StartSCNModifying FIRST_SCN and START_SCNIncrease the value

5、of first_scn for a capture process to:Remove unneeded LogMiner checkpoint information from the LogMiner dictionary Ensure that old archived redo logs can be safely removed from diskSet the start_scn for a capture process to a time in the past to recapture changes from the redo logs.EXEC DBMS_CAPTURE

6、_ADM.ALTER_CAPTURE(-capture_name=CAPTURE1,-first_scn=2524278);Modifying FIRST_SCN and START_SCNIncrease the value of first_scn for a capture process to:Remove unneeded LogMiner checkpoint information from the LogMiner dictionary Ensure that old archived redo logs can be safely removed from diskSet t

7、he start_scn for a capture process to a time in the past to recapture changes from the redo logs.EXEC DBMS_CAPTURE_ADM.ALTER_CAPTURE(-capture_name=CAPTURE1,-first_scn=2524278);Altering FIRST_SCN for a Capture ProcessFirstSCNStartSCNRequiredcheckpointSCNMaximumcheckpointSCN1FirstSCNStartSCNRequiredch

8、eckpointSCNMaximumcheckpointSCN2ABCDEFGHIJK11222Removing Unnecessary Archived Log FilesThe DBA_LOGMNR_PURGED_LOG data dictionary view lists the archived redo log files that are no longer needed by any capture process.SQL SELECT*FROM DBA_LOGMNR_PURGED_LOG;FILE_NAME-/u01/app/oracle/oradata/amer/archiv

9、e/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_.arcPurging the Staging QueueThe DBMS_AQADM.PURGE_QUEUE_TABLE procedure:Removes messages from persistent queuesIs useful for b

10、oth single-consumer and multiconsumer queuesCan be customized to purge only messages that meet specific conditionsPurging the Staging QueueWhen purging events from a queue,you can specify:A purge conditionWhether an exclusive lock is obtained during the purge operationDECLARE purge_opt DBMS_AQADM.aq

11、$_purge_options_t;BEGIN purge_opt.block:=TRUE;DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table=STRMADMIN.STREAMS_QUEUE_TABLE,purge_condition=queue=STREAMS_QUEUE,purge_options=purge_opt);END;Purge Conditions:ExamplesPurge all events in a queue tablePurge all events for a queue in a queue tablePurge all event

12、s in a particular state for a queuePurge all messages for a consumerPurge messages based on enqueue timepurge condition:NULLpurge condition:queue=streams_queuepurge condition:queue=hr_queue and msg_state=READYpurge condition:consumer_name=ORDERS_DEQpurge condition:enq_time 30-MAR-06Managing Propagat

13、ionsUse the DBMS_PROPAGATION_ADM package methods to manage propagation:DBMS_PROPAGATION_ADM.START_PROPAGATION method starts a propagation.DBMS_PROPAGATION_ADM.STOP_PROPAGATION stops a propagation.Altering the Propagation ScheduleBEGIN DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE(queue_name =strmadmin.hr_qu

14、eue,destination=SITE2.NET,duration =300,next_time =SYSDATE+900/86400,latency =30);END;/Managing Apply ProcessesThe DBMS_APPLY_ADM package contains the following procedures:START_APPLYALTER_APPLY,which you can use to:Specify or remove a rule set Specify or remove a message,DDL,or precommit handler Sp

15、ecify an apply user Specify or remove a tag for apply eventsSTOP_APPLYDROP_APPLYModifying the Apply ProcessSet parameters with the SET_PARAMETER procedure of DBMS_APPLY_ADM.The value for a parameter is always entered as a VARCHAR2,regardless of the parameters data type.EXEC DBMS_APPLY_ADM.SET_PARAME

16、TER(-apply_site1_lcrs,disable_on_error,N);Starting Streams ProcessesThe capture and apply processes are disabled after creation.Use the START_APPLY or START_CAPTURE procedures to enable the processes:BEGIN DBMS_APPLY_ADM.START_APPLY(apply_name=apply_site1_msg);END;/Stopping Streams ProcessesUse the

17、STOP_APPLY or STOP_CAPTURE procedures to disable the processes:The process status is maintained across database shutdowns.An apply process stops and restarts automatically if parallelism is altered.Run the STOP_PROPAGATION procedure to stop an existing propagation.EXECUTE DBMS_CAPTURE_ADM.STOP_CAPTU

18、RE(-capture_name=hr_capture);Managing Streams Process Rule SetsSpecify or remove a rule set for a process:DBMS_CAPTURE_ADM.ALTER_CAPTURE DBMS_APPLY_ADM.ALTER_APPLY DBMS_PROPAGATION_ADM.ALTER_PROPAGATIONAdd rules to a rule set for a process:DBMS_STREAMS_ADM.ADD_*_RULES DBMS_STREAMS_ADM.ADD_*_PROPAGAT

19、ION_RULES DBMS_RULE_ADM.ADD_RULERemove a rule from a rule set used by a Streams process:DBMS_STREAMS_ADM.REMOVE_RULE DBMS_RULE_ADM.REMOVE_RULEAdding New Rules:ExampleBEGIN DBMS_STREAMS_ADM.ADD_SUBSET_PROPAGATION_RULES(table_name=HR.EMPLOYEES,dml_condition=job_id LIKE SA%,streams_name=prop_to_site3,s

20、ource_queue_name=strmadmin.hr_queue,destination_queue_name=ix.streams_,source_database=NULL);END;/Managing Rules and Rule SetsBy using the DBMS_RULE_ADM package,you can:Alter a rule Change a rule condition Change or remove the rule evaluation context Change or remove the rules action context Change

21、or remove the comment for a ruleRemove a rule from a rule setDrop a rule from the databaseDrop a rule set from the databaseAltering a RuleBEGIN DBMS_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()=DB

22、MS_STREAMS.COMPATIBLE_9_2,evaluation_context=NULL);END;/Dropping Rule SetsUse the drop_unused_rule_sets parameter of:DBMS_CAPTURE_ADM.DROP_CAPTURE DBMS_APPLY_ADM.DROP_APPLY DBMS_PROPAGATION_ADM.DROP_PROPAGATIONDrops the rule sets along with the Streams clientEXECUTE DBMS_CAPTURE_ADM.DROP_CAPTURE(-ca

23、pture_name=hr_capture,-drop_unused_rule_sets=TRUE);Removing a Staging QueueEXECUTE DBMS_STREAMS_ADM.REMOVE_QUEUE(-queue_name=STRMADMIN.streams_queue,-drop_unused_queue_table=TRUE,-cascade=TRUE);CascadeRemoving All Streams ComponentsEXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();Managing Str

24、eams with EMNotesTroubleshooting Apply FailuresChecklist:Check the state of the processes.DisabledWas the apply process stopped by another DBA?Was the capture process ever started?AbortedCheck the appropriate trace file for messages.Are there errors in the error queue?Check apply process latency for

25、 newly started apply processes.Verify capture,propagation,and apply rules.Check for process error messages.Is the Apply Process Current?Query dictionary views to determine latency and activity:V$STREAMS_APPLY_COORDINATORDBA_APPLY_PROGRESSSELECT(HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 Latency in Seco

26、nds,HWM_MESSAGE_CREATE_TIME Event Creation,HWM_TIME Apply Time,HWM_MESSAGE_NUMBER Applied Message#FROM V$STREAMS_APPLY_COORDINATOR;Checking for RulesConfirm that the Streams rule sets are not empty and that all the rules are enabled by querying DBA_STREAMS_RULES and DBA_RULE_SET_RULES.Check the actu

27、al rule conditions.SELECT streams_name,streams_type,sr.rule_set_name,sr.rule_nameFROM dba_rule_set_rules rsr,dba_streams_rules srWHERE sr.rule_name=rsr.rule_nameAND rsr.rule_set_rule_enabled!=ENABLED;SELECT rule_name,streams_type,rule_condition FROM DBA_STREAMS_RULES WHERE rule_name=ORDERS%;ORA-2668

28、7:Instantiation SCN Not SetFor apply to execute captured change events,the target database object must be instantiated.If apply fails with ORA-26687,search for missing instantiation SCNs:DBA_APPLY_INSTANTIATED_OBJECTS(table DML)DBA_APPLY_INSTANTIATED_SCHEMAS(schema DDL)DBA_APPLY_INSTANTIATED_GLOBAL(

29、global DDL)Correct the error by:Setting the instantiation SCN with Data Pump,or the Export and Import utilities Executing the SET_*_INSTANTIATION_SCN procedures to manually set the instantiation SCNStreams Dictionary InformationStreams dictionary information must be available at each destination sit

30、e:Contains object numbers,object names,object types,column names,and other critical information for each source object Stored in LogMiner tablesStreams dictionary information is generated at the source site through:DBMS_CAPTURE_ADM.PREPARE_*_INSTANTIATION DBMS_STREAMS_ADM.ADD_*_RULE DBMS_CAPTURE_ADM

31、.BUILDChecking for Apply Reader Trace FilesIf the destination Streams dictionary does not contain information for an object referenced in a captured event,then it generates an error.Search the parallel execution server process trace file for the instance for the phrase:MISSING Streams multi-version

32、data dictionary Determine what object is missing from the Streams dictionary.Missing dictionary information is not an apply error.grep MISSING *p0*Missing MultiversionData Dictionary InformationTo populate the Streams dictionary information at the apply site:Determine the scope of missing informatio

33、n(table,schema,or global)at the apply siteAt the source database,prepare for instantiation:DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(HR);SummaryIn this lesson,you should have learned how to:Alter a Streams environmentRemove Streams componentsAdminister rules that are used by Streams componentsTr

34、oubleshoot common apply failuresPractice 14-1 Overview:Altering Streams ProcessesThis practice covers the following topics:Altering parameters for applyAltering the propagation scheduleDetermining the current parameter settings for capture,apply,and propagationPractice 14-2 Overview:Starting Streams

35、 ProcessesThis practice covers the following topics:Starting apply processesEnabling a propagation scheduleStarting the capture processQuerying the status of each processDetermining capture process latencyPractice 14-3 Overview:Replicating Data Changes with StreamsThis practice covers the following

36、topics:Verifying that the capture process is currentQuerying propagation statisticsInserting new rows into a shared table at the source siteVerifying that the changes were captured and propagated by querying data dictionary viewsVerifying that DML changes were applied to the shared table at the destination siteVerifying that the apply handler audited an order updateQuerying the error queue on the destination site