· sample code available on otn: • anonymous notification • integration with aq application...
TRANSCRIPT
![Page 1: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/1.jpg)
![Page 2: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/2.jpg)
<Insert Picture Here>
Unleashing the Power of StreamsPatricia McElroyPrincipal Product Manager, Oracle Streams
![Page 3: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/3.jpg)
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remain at the sole discretion of Oracle.
![Page 4: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/4.jpg)
This Talk…
• Background• Streams Semantics• Streams Customizations• Sample Code Descriptions
![Page 5: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/5.jpg)
Oracle Streams
• Flexible solution for asynchronous information sharing
• Provides• message queuing• uniquely flexible replication• data warehouse loading and ETL• database migration• application upgrade• event management and notification
• …and more
![Page 6: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/6.jpg)
Think Outside the Box
Combine Streams with other database features
Sample Code available on OTN:• Anonymous Notification• Integration with AQ application• Procedural Replication• Asynchronous Triggers• Streams driven Materialized View Refresh• Streams driven Continuous Query
![Page 7: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/7.jpg)
The Power of Streams
• Highly Flexible• Custom Configurations• Asynchronous Processing• Transactional• Push, rather than Pull• Commit-time Processing• Event-driven Processing• Notification
![Page 8: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/8.jpg)
Apply
Propagation
Capture
Apply2
SQL OCIPL/SQLJDBCJMS
Replication: Capture and Apply
Redo Log
Sybase
ACK
DB platform and release independent
Schema structure can differ between DBs
Flexible configuration: Hub&spoke, N-way, …
SQL OCIPL/SQLJDBCJMS
![Page 9: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/9.jpg)
Oracle Streams
Rules and Transformations
Queues
Auto-Capture Auto-Apply
OCIPL/SQLJDBCJMS
SOAP
Web services
Rules and Transformation
s
Queues
Auto-Capture Auto-Apply
Internet
Intranet
MQ/SeriesTibco
Event Monitor
Event Monitor
DB2 Sybase
SQLServer
![Page 10: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/10.jpg)
Streams Customizations
• Efficient Rules Engine• Rule-based Transformations
• Apply Directives• DML Handlers• DDL Handlers• Pre-Commit Handlers
![Page 11: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/11.jpg)
Streams Semantics - Advantages
• Asynchronous event processing• Commit-time Processing• Transactional
![Page 12: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/12.jpg)
Synchronous Processing
• Examples:• Traditional triggers• Materialized View Logs• On-Commit MV
• Impacts user transaction
![Page 13: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/13.jpg)
Asynchronous Event Processing
• Outside user transaction• Offload to downstream database, if no “join back”• Good for middle tier coordination• Asynchronous, but low latency• Good for Auditing
![Page 14: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/14.jpg)
Commit-time Processing
• Triggers are not commit time• May not see other concurrent transaction results
• But… with Pre-Commit Handler• Possible to see full effects of changes using flashback query
![Page 15: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/15.jpg)
Transactional
• LCRs ordered within transaction• Committed transactions only• Transactions applied in order
![Page 16: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/16.jpg)
Why Generic Sample Code?
• Learn by example• Demonstrate techniques
• Rule generation• DML generation• Tag Setting
• Separate DBA and App Developer tasks
![Page 17: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/17.jpg)
Sample Code
• Each example is stand-alone• Just an example… not the only way• Not a feature…
• “AS IS” – OTN for questions/corrections
• Intended to inspire• Customize based on your application requirements
![Page 18: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/18.jpg)
Sample Code Highlights
• LCR Modification• Anonymous Notification• AQ Integration• Apply Directives• Tag Setting• DDL Triggers • Dynamic Rule Generation
• MV Integration• Dynamic SQL• Flashback Integration • Transaction Caching • DDL Handlers• DML Handlers• Pre-Commit Handlers
![Page 19: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/19.jpg)
Logical Change Record(LCR)
• Database change = LCR• DML
• LCR = row change • Attributes
• Object name, owner, Type of DML• OLD, NEW values• System Change Number (SCN)
• Optional attributes: username, session, thread,…• DDL
• LCR= DDL Statement• Attributes
• Object name, owner, Type of DDL• System Change Number (SCN)• DDL Text
• Optional attributes: username, session, thread,…
• Example usage in all of the sample code
![Page 20: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/20.jpg)
AQ Integration
• Configure so LCRs available for AQ applications• For notification• For mid-tier processing (e.g., JMS)
• For heterogeneous messaging (e.g., Tibco )
![Page 21: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/21.jpg)
AQ Integration
• Sets up Streams
• Configures Apply Directives• Persistent Storage of LCRs in
QueueDBMS_APPLY_ADM.SET_DESTINATIONDBMS_APPLY_ADM.SET_EXECUTE
• Example Streams AQ application
Local Capture
Redo Log
Capture
Apply
Queue2
Directives
![Page 22: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/22.jpg)
Anonymous Notification
• LCR conversion to XML• Direct access to LCRs
• No apply to persistent queue
• No ordering requirement?• Set COMMIT_SERIALIZATION apply parameter to NONE
![Page 23: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/23.jpg)
Asynchronous Notification
• Sets up Streams• With DML Handler
• Registers Subscription • Pink.Anonstr:Admin
• OCI client to receive notification
Local Capture
Redo Log
Capture
ApplyDML Handler
Post
Emon
OCI Client
Pink.Anonstr
![Page 24: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/24.jpg)
DML Handlers using Dynamic SQL
• Generic DML handlers that handle user-level queries• Examples of query generation based query
• Materialized Views• Continuous Query
• Trigger example • Anonymous block• Sent as DDL
![Page 25: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/25.jpg)
Handlers with Transaction Caching
• DML handler –• Rowid, SCN, Table • Caches info on LCR basis
• Pre-Commit handler• Perform Flashback query for each table
• Examples:• Streams MV and Notification
![Page 26: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/26.jpg)
Pre-Commit Handler with Flashback
• Pre-commit handler has Commit SCN• Uses rowids cached from DML Handler to drive
Flashback query performance• See changes to a result set of query by comparing
• “scn” minus “scn-1” => New values, now part of query results• “scn-1” minus “scn” => Old values, no longer part of query
results
![Page 27: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/27.jpg)
DDL Handler
• DDL trigger runs as part of DDL transaction• Async Trigger example
• DDL acts as barrier or serialization point for apply parallelism
![Page 28: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/28.jpg)
Setting Tags
• Prevent replication of changes• Dbms_streams.set_tag(’99’)
• Example:• Procedural Replication
![Page 29: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/29.jpg)
Streams Considerations
• Sample Code Performance• Per-row dynamic sql• Generated rules to filter out uninteresting tables• Entire redo log processed • Asynchronous – may not keep up with source events
• Security• Capture mines whole log & can see all changes• Customization may require additional privileges for apply
• Data type support• LOBs
• use lob assembly=> TRUE• ADTs, XMLType, etc.
![Page 30: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/30.jpg)
Sample Code
• Procedural Replication• Asynchronous Triggers• Streams driven Materialized View Refresh• Streams driven Continuous Query
![Page 31: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/31.jpg)
Procedural Replication Motivation
Example: 10% raise to 10,000 employeesUpdate Statement Procedural Replication
10,000 LCRsSource Target 1 LCRSource Target
![Page 32: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/32.jpg)
Procedural Replication Considerations
• Unidirectional• Deterministic functions
• Example: procedure should not call SYSDATE
• Sync Point• User guarantees• Locks
![Page 33: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/33.jpg)
Procedural Replication Implementation
• Example:• Packaged Streams configuration• Rule creation• DDL trigger• DDL Handler• Dynamic SQL• DDL LCR manipulation• Procedure execution as part of DDL trigger• Procedure invocation replicated as DDL LCR• Remote execution invoked in DDL Handler
• 9.2 or 10g
![Page 34: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/34.jpg)
Example: Bulk DML Replication
Propagation
ACK
Create or replace procedure
Streams_replicate_procedures as
BEGIN; mybulkdml(''USER1'',''CUST''); END;
Execute_call
BEGIN;mybulkdml(''USER1'',''CUST'‘)
END;
DDL Handler:
Redo Log
Capture Apply
System Trigger
‘After Create’
Set tag(99)Exec mybulkdml(.Set tag(NULL)
DDL Rule: STRPROCADM
![Page 35: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/35.jpg)
Asynchronous Trigger Motivation
• Commit-time processing• Perform after the commit has occurred
• Asynchronous event processing • Separate from user transaction
• Separation of DBA and Application development roles
![Page 36: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/36.jpg)
Asynchronous Triggers
• Example• Packaged Streams configuration• Dynamic SQL
• Trigger-like syntax• DDL LCR modification• DDL Handler
• 9.2 or 10g
![Page 37: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/37.jpg)
Example: Async Trigger
Insert into ACCOUNTS values(100,’ALICE’,...);
Redo Log
Capture
Apply DML Handler
Acct_history
Insert into ACCT_HISTORY values(‘INSERT’,’10/04/06’,’ALICE’,...);
![Page 38: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/38.jpg)
Why Streams MV Refresh?
• Traditional Materialized View Refresh • Fast, efficient
• Materialized Views affects user transaction• MV Log• On-commit MVs
• Trigger on fast refreshable MV (like a CQ)
![Page 39: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/39.jpg)
Why Streams-based MV Refresh?
• Push, not Pull• Transactional, not bulk• Just want changes used to maintain MV• MJV especially useful, but can do more• Avoids locking of MV
• But…. Using a streams-based refresh is slower than MV refresh
![Page 40: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/40.jpg)
Streams–Based MV Refresh
• MV integration• Fast refresh MV provides query-analysis for efficient implementation• Never Refresh
• Example of:• Packaged Streams configuration• Flashback integration • Dynamic SQL• Asynchronous• Transactional• Caching of transaction information• Commit-time processing
• 10g
![Page 41: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/41.jpg)
Example: Streams-based MV Refresh
Insert into Order_Lines
values(100,’OL30’,...);
Redo Log
Capture
ApplyDML
Handler
Oline_mv
Store rowid, table_name in memory
PreCommit
Generate INSERTs/DELETEs for transaction using FLASHBACK as of SCN relative to commit_scn
![Page 42: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/42.jpg)
Streams Continuous Query
• More generic query capability• Bypass MV restrictions• Union MVs
• User responsible for:• “Correctness” guarantees• Flashback performance
![Page 43: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/43.jpg)
Continuous Query Implementation
• Similar to Streams-based MV• Not MV integration• Extends Streams-based MV functionality• Plus anonymous notification
• Per table query• SCN • Rowid
![Page 44: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/44.jpg)
Example: Continuous Query
Insert into SALES
values(‘EMEA’,3000,...);
Redo Log
Capture
ApplyDML
Handler
Sales_Reg_qry
Store rowid, table_name in memory
PreCommit
Generate INSERTs/DELETEs for transaction using FLASHBACK as of SCN relative to commit_scn
![Page 45: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/45.jpg)
Streams Summary
• Features:• Log-based Change Capture• Customizable Apply Engine
• Versatile:• Replicate Data• Consolidate Information• Provide High Availability during database migration,
upgrade• Combine other DB Features with Streams
• Flexible Database Feature
![Page 46: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/46.jpg)
More on Streams….
• Visit Oracle Streams booth in Exhibit Hall, Moscone West D20
• Other Streams presentations:• Overstock.Com: Streams Advanced Queuing (S281180)
• Hilton Hotel Continental Parlor 3 on Tuesday 11:15am• Implementing Replication with Streams (S281220)
• Moscone South - Room 306 South on Thursday 8:00am
• Look for Sample Code for Streams on OTN
• Bookmark OTN page Oracle Information Integration features:http://otn.oracle.com/products/dataint/
![Page 47: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/47.jpg)
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S
![Page 48: · Sample Code available on OTN: • Anonymous Notification • Integration with AQ application • Procedural Replication • Asynchronous Triggers •](https://reader033.vdocuments.us/reader033/viewer/2022060608/605e6fef1ef08a2a7461fa6b/html5/thumbnails/48.jpg)