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

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

1、Apply Concepts and ConfigurationObjectivesAfter completing this lesson,you should be able to do the following:Describe the apply-process architectureList which events can and cannot be appliedConfigure one or more apply processes for a databaseQuery the data dictionary for information about the appl

2、y-process configurationWhat Is Apply?Apply is:The automatic dequeuing and processing of events from a Streams queue or queue buffer Implemented as one or more Oracle background processesEach apply process has an apply user.SGAStreams poolProcessing Streams EventsA single apply process dequeues eithe

3、r captured events or user-enqueued events from a specific queue.For captured events,the apply process can:Execute the DML or DDL event directly Pass the event as a parameter to a user-defined procedureUser-enqueued events in a SYS.AnyData queue can also be dequeued explicitly through a user-created

4、application.Captured events cannot be explicitly dequeued.Applying DDL EventsSuccessful application of DDL events requires that the source and destination targets have matching:Storage parameters for CREATE statementsTablespace names and specificationsPartitioning names and specificationsConstraint

5、and index names and specificationsCREATE TABLE range_sales (prod_id NUMBER(6)PRIMARY KEY,time_id DATE,amount_sold NUMBER(10,2)TABLESPACE usersPARTITION BY RANGE(time_id)(PARTITION sales_q1_2000 VALUES LESS THAN (TO_DATE(01-APR-2000,DD-MON-YYYY),.);DDL Events That Are Not AppliedAn apply process does

6、 not support the following types of DDL events:ALTER|CREATE|DROP MATERIALIZED VIEWALTER|CREATE|DROP MATERIALIZED VIEW LOGCREATE|DROP DATABASE LINKCREATE TABLE AS SELECT on a clustered tableCREATE SCHEMA AUTHORIZATIONRENAMEOther Considerations for Applying DDLThe user who is specified as the current_

7、schema in the DDL LCR must exist at the destination database for the DDL LCR to be applied.Avoid using system-generated names in schemas where DDL is captured.CREATE TABLE projects(proj_id NUMBER CONSTRAINT proj_id_pk PRIMARY KEY,projname VARCHAR2(30)CONSTRAINT pname_nn NOT NULL,dept_id NUMBER REFER

8、ENCES HR.departments(department_id);ALTER TABLE projects DROP CONSTRAINT sys_c005844;Applying EventsEvents are applied by an apply user.A transaction is considered applied or consumed when an apply process does one of the following:Commits a completed transaction Places a transaction in the error qu

9、eue and commits the dequeue transactionError QueueStores information about transactions that could not be successfully applied on the local databaseIs created automaticallyContains all LCRs for each transaction not appliedStores error transactions for all apply processes in the databaseRequired Appl

10、y User PrivilegesFor a user to be able to apply events,the following privileges are required:Secure queue user privileges for the queueDequeue privileges for the queueEXECUTE privileges for:Rule sets used by the apply process Apply handler PL/SQL procedures called by the apply process Rule-based tra

11、nsformation functions called by the apply processDML and DDL privileges on the database objects and schemasConfiguring Apply ProcessesYou must configure:A separate apply process for each source site Separate apply processes for captured and user-enqueued eventsEach apply process works independently

12、of other apply processes in the same database.NYAP_L1AP_AUAP_L2LCRsUser-enqueuedeventsLCRsLondonSydneyApply Process ComponentsThe apply engine consists of:One reader server process One coordinator process One or more apply server processes Parallelization of apply maximizes concurrency.ReaderApply c

13、oordinatorApplyApplyApplyStaging areaApply RulesDetermine which events are appliedCan be positive or negativeAre specified at the table,schema,or global levelInclude subset rulesCan be customized to further restrict the data that is appliedEnqueue Destination During ApplyBEGIN DBMS_APPLY_ADM.SET_ENQ

14、UEUE_DESTINATION(rule_name=strmadmin.orders5,destination_queue_name=ix.order_evt_queue);END;/enqueueStagingQDestQueueSGAStreams poolShared poolLarge poolExecution Directives During ApplyBEGIN DBMS_APPLY_ADM.SET_EXECUTE(rule_name=strmadmin.orders5,execute=TRUE);END;/enqueueStagingQDestQueueSGAStreams

15、 poolShared poolLarge poolApply Process and Column DiscrepanciesThe apply engine supports column discrepancies between the source and destination tables:Column data type mismatch Use a transformation or DML handler on applyFewer columns at the destination Use a DML handler or transformation to disca

16、rd changes to extra columnsExtra columns at the destination Use default value or NULL extra columns If extra columns are used for dependency evaluations,the transaction is placed in error queue.Column DependenciesDependencies may exist:Among LCRs within a single transaction Between different transac

17、tionsThe following types of columns are required for dependency computations:All key columns(For INSERT and DELETE statements)All constraint-related columns (For UPDATE statements)All columns related to a constraint if that constraints column is changedVirtual Dependency:DefinitionAssists the apply

18、process in computing transaction dependencies beyond the existing constraintsEnables apply handlers to process transformed LCRs correctly,avoiding errorsCan improve performance by allowing the apply handlers to process LCRs in parallelDataApplicationcodeTableIntegrityconstraintVirtual Dependency Def

19、initionDependency TypesValue dependency:Creates a virtual foreign key that can involve more than two tables Uses LCRs from only one source Requires supplemental loggingObject dependency:Defines a parent-child relationship Requires a parent row change with a lower SCN to be applied before a child row

20、 change with a higher SCN Should be used only if the relationship is not defined by constraints at the target databaseDefining DependenciesValue dependencies:Object dependencies:DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(DEMO.EMPLOYEES,HR.DEPARTMENTS);DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(EMP_VPK,DEMO.EM

21、PLOYEES,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);Defining DependenciesCreating an Apply ProcessYou can create an apply process:Automatically by using the ADD_*_RU

22、LES procedure of DBMS_STREAMS_ADM Manually by using the CREATE_APPLY procedure of DBMS_APPLY_ADMA SYS.AnyData queue must exist before creating the apply process.The apply process is created in the disabled state.Creating an Apply ProcessExample:Creating an apply process for captured eventsBEGIN DBMS

23、_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;/Creating an Apply ProcessExample:Manually creating an apply process for applying user-enqueued LCR eventsBEGIN DBMS_APPL

24、Y_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;Apply Process ParametersALLOW_DUPLICATE_ROWSCOMMIT_SERIALIZATIONDISABLE_ON_ERRORDISABLE_ON_LIM

25、ITMAXIMUM_SCNPARALLELISMTIME_LIMITTRACE_LEVELTRANSACTION_LIMITTXN_LCR_SPILL_THRESHOLDWRITE_ALERT_LOGMonitoring ApplyALL_APPLYALL_APPLY_ENQUEUEALL_APPLY_ERRORALL_APPLY_EXECUTEALL_APPLY_KEY_COLUMNSALL_APPLY_PARAMETERSALL_APPLY_PROGRESSDBA_APPLY_OBJECT_DEPENDENCIESDBA_APPLY_VALUE_DEPENDENCIESDBA_APPLY_

26、SPILL_TXNApply Process:StatisticsV$STREAMS_APPLY_COORDINATORV$STREAMS_APPLY_READERV$STREAMS_APPLY_SERVERV$STREAMS_TRANSACTIONV$BUFFERED_SUBSCRIBERSApply Process StatisticsChecking the Apply ProcessVerify apply process configuration:SELECT apply_name,apply_captured CAP,status,status_change_time,rule_

27、set_name,negative_rule_set_name FROM DBA_APPLY;APPLY_NAME CAP STATUS STATUS_CH-RULE_SET_NAME NEGATIVE_RULE_SET_NAME-APPLY_SITE1_LCRS YES ENABLED STREAM1_RS STREAM1_NEG_RSSummaryIn this lesson,you should have learned how to:Describe the apply process architectureList which events can and cannot be ap

28、pliedConfigure one or more apply processes for a databaseQuery the data dictionary for information about the apply process configurationPractice 11 Overview:Configuring ApplyThis practice covers the following topics:Creating an apply process by using the DBMS_STREAMS_ADM packageQuerying the data dictionary for apply process informationQuerying the rules and rule conditions that are used by the apply processAdding new rules to the apply process rule set by using ADD_TABLE_RULES