Oracle Streams--Simplifying Information Sharing in Oracle10 g

2. Session id: 40208 Oracle Streams--Simplifying Information Sharing in Oracle10g Patricia McElroyProduct Manager Oracle Corporation

3. Patricia McElroyProduct Manager, Distributed Systems Oracle Corporation

4. Oracle Streams--Simplifying Information Sharing in Oracle10g

5. Agenda • Oracle Streams Overview • Streams Architecture • Hints and Tips

6. Oracle Streams • Simple solution for information sharing • Provides • uniquely flexible replication • message queuing • data warehouse loading • event management and notification

7. Consumption Capture Staging Streams Basic Elements • Capture • Staging • Consumption (apply)

8. Consumption Staging Capture Consumption Staging Capture Multi-Database Streams • A stream can contain multiple elements from multiple databases • Events flow between staging areas

9. Capture Capture • Streams captures events • Implicitly: log-based capture of DMLand DDL • Explicitly: Direct enqueue of user messages • Captured events are published in the staging area • SQL and messaging APIs in multiple languages • JDBC, JMS, PL/SQL, C, SOAP

10. Log-Based Change Capture Capture • Low overhead, low latency change capture • Changes to the database are written to the online redo log • Oracle9i Streams can extract changes from the log as it is written (hot mining) • Changes are formatted as a Logical Change Record (LCR), a representation of the change

11. Logical Change Record(LCR) • Database change = LCR • DML • Object name, owner, Type of DML, SCN • Row change = LCR • OLD, NEW values • DDL • Object name, owner, Type of DDL, SCN • DDL text • LOB • Multiple LCRs per LOB • Piecewise chunks

12. Staging Staging • Streams publishes captured events into a staging area • Implemented as a queue • Supports for new type “any” datatype allows a single staging area to hold any type of data • All events, LCRs and user-messages, can be staged in the same queue • Messages remain in staging area until consumed by all subscribers

13. Staging Area Propagation • Other staging areas can subscribe to events • in same database • in a remote database • Events can be routed through a series of staging areas Staging Propagation Staging

14. Transformations Staging • Transformations can be performed • as events enter the staging area • as events leave the staging area • as events propagate between staging areas • Transformation examples • change format, data type, column name, table name

15. Consumption Consumption • Staged events are consumed by subscribers • Implicitly: Apply Process • Default Apply • User-Defined Apply • Explicitly: Application dequeue via open interfaces • JMS, C, C++, PLSQL, SOAP (XML/HTTP)

16. Default Apply Consumption • The default apply engine will directly apply the DML or DDL represented in the LCR • apply to local Oracle table • apply via DB Link to non-Oracle table • Automatic conflict detection with optional resolution • unresolved conflicts placed in exception queue • Parallel apply maximizes concurrency

17. User-defined Apply Consumption • User-written custom apply procedures • Written in PL/SQL, Java, C, C++ • Uses: • full control over apply • normalizing or denormalizing data • populating related fields or tables

18. Rule-based Configuration • Consumers subscribe to published events • Content-based subscription • Rule is expressed as SQL WHERE clause dbms_rule_adm.create_rule( rule_name=>‘scott.rule1', condition=>':dml.get_object_owner() = ''SCOTT'' AND :dml.get_object_name()=''EMP'''); • Rule sets for simplicity • Rule sets govern capture,staging, and apply • Negative, Inclusion • Dynamic rule maintenance

19. Rule-based DML, DDL content True/False Granularity Table Subset of Table Schema Database Tablespace Tailored Replication API DBMS_STREAMS_ADM ADD_TABLE_RULES ADD_SCHEMA_RULES ADD_GLOBAL_RULES ADD_SUBSET_RULES MAINTAIN_TABLESPACES ADD_MESSAGE_RULE Replication Rules

20. Propagation independent of Apply Rules-based subscription determine if event is locally applied London applies UK only WAN Friendly Send once, fan out NY-->London, London-->Milan,London-->Paris Directed Networks INSERT … VALUES (‘EUROPE’,’ ITALY’) NY (master) EUROPE London (subset) FRANCE ITALY Milan (subset) Paris (subset)

21. Automatic Conflict Detection • Automatic conflict detection with user-selectable conflict resolution routines • latest timestamp, earliest timestamp, maximum or minimum value, overwrite, discard • User-definable resolution routines • Conflict detection compares current row values at receiving site with “old” values of changed row from the originating site • if match, “new” values are applied to row • if not, conflict resolution method is used, if supplied • if still unresolved, place transaction in exception queue • Ability to disable conflict detection by column or table

22. empid|job |.. 100 | sales |… 510 | coding| ... EMP Propagation Queue ----- LCRs Queue ------ LCRs ACK Capture Apply Streams Capture and Apply Update EMP set job=‘coding’ where empid=510; EMP Redo Log

23. Queue ----- LCRs Capture Redo Log Capture • Background process, CPnn • Invokes Logminer • Staged in buffer queue (SGA) • Restartable • Automatic Flow Control Ack

24. Queue ----- LCRs Capture Redo Log Capture Restart • Every database change is in redo • Ordered by SCN • Last enqueued SCN • capture restart • Last acknowledged SCN • instance restart Ack

25. Downstream Capture • Zero impact at source site • Changes are captured by another database • Same Platform • Archive logs • Log transport services/ RFS to move logs

26. In memory Queue/ LCRs Propagation • JobQueue processes • Stream LCRs • Maintain status information • 9i:Re-propagate on instance crash • 10g: Reuse spilled messages during recovery IMPLICIT APPLY IMPLICIT CAPTURE Propagation LCRs In memory Queue/ LCRs Acknowledgements

27. Apply empid|job |.. 100 | sales |… 510 | coding| ... • Background processes • Assembles transactions • Exactly once apply • Dependency computation • Conflict detection Reader EMP Queue ------ LCRs Coordinator Ack Apply Server Server Server …..

28. ACK T Emp->Person Capture Apply Transformation Example empid|job |.. 100 | sales |… 510 |coding| ... Update EMP set job=‘coding’ where empid=510; EMP Person Propagation Queue ----- LCRs Queue ------ LCRs Redo Log • 1-1 Mapping • LCR based

29. 100 | CA |… ACK U Do not apply Changes to EMP for state=‘CA’; Maintain AUDIT table of each LCR Capture Apply Customized Apply Example empid|state|.. 95 | GA |… 105 | LA | ... Update EMP set state=‘CA’ where empid=100; EMP Emp Propagation Queue ----- LCRs Queue ------ LCRs Bit Bucket Audit Redo Log • Full Control • Transaction Based

30. empid|state|.. 100 | CA |… 205 | CA | ... Apply empid|state|.. 100 | LA |… 105 | LA | ... Capture Apply Data Subsetting Update EMP set job=‘CA’ where empid=100; CA Queue ------ LCRs EMP Insert empid 100 ACK EMP Propagation Queue ----- LCRs LA ACK Queue ------ LCRs EMP Redo Log Delete empid 100

31. Streams and RAC • Oracle9i • No hot mining • All Streams activities done on a single instance (owning instance) • Streams restart after failure not automatic • Oracle10i • Hot mining • Primary and secondary owning instance • Failover of Streams processes automatic

32. Messaging Enhancements • Streams encompasses AQ • Simplified messaging APIs • Batch enqueue/dequeue • Criteria-based purge • Gateway to Tibco

33. Oracle to non-Oracle Apply via gateway Apply process on Oracle node applies change Non-Oracle to Oracle change capture supported via explicit enqueue of LCRs Message Gateways MQ Series Heterogeneous Support LCR or user message Message Gateway Gateway Sybase MQ Series

34. Capture Apply Heterogeneous Example empid|state|.. 100 | CA |… 105 | LA | ... Update EMP set state=‘CA’ where empid=100; EMP EMP Propagation Queue ----- LCRs Queue ------ LCRs ACK Redo Log Apply2 TG4Sybs EMP Sybase

35. UK JP US Gateway DR Report Sybase Customer Example • Maintain application tables in 3 regional centers • autonomous • low overhead • low latency • Provide Special Services • Reporting Database • Disaster Recovery • Legacy Application hosted on Sybase

36. Streams Replication Features • Log-based Change Capture • Customizable Apply Engine • Directed Networks • Schema Evolution • Transformations • Heterogeneous Support • Explicit Enqueue/Dequeue

37. Streams Benefits for Replication • Flexible Configurations • Reduced Network Traffic • Low Overhead • No Downtime Requirements

38. Software Updates • 9.2.0.4 patch, minimum 9.2.0.2 • Streams Wizard patch • Watch OTN Streams website for custom software • http://otn.oracle.com/products/dataint/content.html

39. 9.2.0.4 Configuration Tips • Use separate queues • Capture • Apply • Increase shared_pool_size and max_sga_size init.ora parameters • Relocate Streams data dictionary tables from SYSTEM • Set tablespace before configuring streams • Including apply-only sites

40. Rule Tips • Spelling counts! • Eliminate duplicate rules in a rule set. • Make sure that the source_database_name is correctly specified. • At source site: SELECT global_name FROM GLOBAL_NAME; • Ensure that rules do not allow objects with invalid data types • When using GLOBAL rules, modify existing rule to eliminate the Streams Administrator schema and other schemas with queues, ADTs or tables with invalid data types.

41. Rule Management Tips • Having no rule set defined is NOT the same as an empty rule set. • Use DBMS_STREAMS_ADM package to create replication rules • Use same package to create or remove rules • DBMS_STREAMS_ADM • DBMS_RULE_ADM

42. Troubleshooting Tips • Message Number columns = source DML,DDL SCN • CAPTURE_MESSAGE_NUMBER, ENQUEUE_MESSAGE_NUMBER • DEQUEUE_MESSAGE_NUMBER, APPLIED_MESSAGE_NUMBER • ORA-26687 no instantiation scn set • Set Instantiation SCN • Exp/Imp with appropriate clauses • DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN • ORA-01031 insufficient privilege • Explicitly grant privileges to apply user • MISSING Streams data dictionary information! • DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION

43. Troubleshooting #2 • Determine the scope of the problem: • Apply not working for this site only • Check DBA_APPLY_ERROR for errors • Procedure to display error details in doc • Check trace files • Check rules at this site • Still not working? … • Apply not working for any site • Check rules for both propagation and capture • Check propagation job is working • Job queue processes • DB Link • Make sure COMMIT was performed for transaction

44. Operational Tips • Replicating DDL? • Ensure that privileges are explicitly granted to the apply user. • Modify any manual hotbackup scripts to set an apply tag before starting the backup. • Configure Instantiation SCN at next higher level • Removing Archive log files from disk? • Ensure that scripts do not automatically remove files that may be needed for capture to restart. • Implement “heartbeat” table • Update periodically.

45. Next Steps…. • Recommended sessions36637 • Data Integration with Oracle Streamsata Wed 4:30 Room 130 Integration Using Oracle St • Recommended demos and/or hands-on labs • Hands-On Lab: Integrate your Information. • See Your Business in Our Software • Visit the Streams booth in the DEMOgrounds for more information. • Relevant web sites to visit for more information • http://otn.oracle;com/products/dataint/content.html

46. Q & Q U E S T I O N S A N S W E R S A