1 / 647

Introduction to Oracle Database 11 g : Implement Streams

Introduction to Oracle Database 11 g : Implement Streams. Objectives. After completing this lesson, you should be able to describe: The overall course objectives The curriculum context Your learning aids. Course Objectives. After completing this course, you should be able to:

clarencem
Download Presentation

Introduction to Oracle Database 11 g : Implement Streams

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Oracle Database 11g: Implement Streams

  2. Objectives • After completing this lesson, you should be able to describe: • The overall course objectives • The curriculum context • Your learning aids

  3. Course Objectives • After completing this course, you should be able to: • Configure an Oracle Streams environment  • Alter the Oracle Streams environment to add, modify, and drop new sites or objects  • Configure conflict handling for data replication  • Transform the data being replicated between two sites • Enqueue and dequeue messages by using Oracle Streams Advanced Queueing • Monitor the capture, propagate, and apply of messages  • Perform basic troubleshooting of an Oracle Streams environment

  4. Suggested Schedule Topic Lessons Day Streams Fundamentals 1 1, 2, 3, 4 Streams Concepts and Architecture (Manual configuration, concluded by the end of the day) 5, 6, 7, 8, 9 2 Streams Concepts and Architecture (Additional capture, transformation and apply topics) 10, 11, 12, 13 3 Managing and Extending Streams 14, 15, 16, 17, 18 4 Operational Tips and Considerations 19, 20, 21 5 Streams Messaging 22, 23 5

  5. Oracle UniversityCurriculum Overview Oracle Database 11g: Implement Streams Oracle Database 11g: Administration Workshop II Oracle Database 11g: Administration Workshop I

  6. Oracle Streams Documentation • Oracle Database 2 Day + Data Replication and Integration Guide for a task-oriented overview into Oracle Streams, materialized views, and other distributed database functionality • Oracle Streams Concepts and Administration for general information about Oracle Streams • Oracle Streams Replication Administrator's Guide for information about using Oracle Streams for replication • Oracle Streams Advanced Queuing User's Guide for information about using Oracle Streams for message queuing • Oracle Database PL/SQL Packages and Types Reference forinformation about the packages that you can use to configure and manage Oracle Streams • Other documentation

  7. Additional Resources • To continue your learning: • Oracle University (OU)http://education.oracle.com • Oracle Technology Network (OTN) http://www.oracle.com/technology • Data Replication and Integration home page http://www.oracle.com/technology/products/dataint/index.html • Oracle by Example (OBE) http://www.oracle.com/technology/obe • Technical Support: Oracle MetaLink http://metalink.oracle.com

  8. Data Replication • Information Sharing • Oracle Streams • Data recovery and high availability • Oracle Data Guard • Load balancing, failover • Oracle Real Application Clusters • Network load reduction • Materialized views

  9. Oracle Streams: Overview

  10. Objectives • After completing this lesson, you should be able to: • Define Oracle Streams • List the three basic Streams elements: capture, propagation, and apply of messages • Describe examples of Oracle Streams implementation • Configure databases for Streams, including: • Setting database initialization parameters • Configuring memory and database storage • Configuring archive and supplemental logging • Creating a Streams administrator • Configuring communication between your databases • Replicate a table by using the MAINTAIN_* procedure

  11. What Is Oracle Streams? • Simple solution for information sharing • Providing: • Data replication • Data warehouse loading • Data provisioning in distributed and grid environments • High availability during database upgrade, platform migration, and application upgrade • Message queuing • Message management and notification • Interfaces: • PL/SQL API commands • Enterprise Manager graphical user interface (GUI)

  12. Streams: Overview Source database Target database Messages in queue Messages in queue Propagate Changed database object Changed database object Capture Apply Redo logs

  13. Capture Capture • Multiple modes of capture: • Implicit, asynchronous capture • Redo-based capture of DML and DDL changes either locally or remotely at a downstream database • Extracting changes from the redo (as it is written) • Capture from log buffer, online redo, or archived log files • Implicit, synchronous capture • Capture of DML changes for specific tables as part of the executing transaction (always locally) • Stored persist on disk • Explicit capture: Direct enqueue of user messages

  14. Logical Change Record • Reformatting changes into a logical change record (LCR) • Row change: Old and new values; tag, transaction ID, SCN • Object name, owner, command type, source database name • Optional attributes: Username, session, thread, and so on • Object name, owner, type, tag, transaction ID, SCN • Logon user, current schema, base table owner and name • DDL text, command type, source database name • Optional attributes: Username, session, thread, and so on • Multiple LCRs per LOB or LONG column • Piecewise chunks • Enqueuing messages (with the LCR) to a single queue for redo-based capture or to a queue table for synchronous capture DML DDL LOB

  15. Staging messages in the SYS.AnyData queue Capture process > buffered queue (can spill from memory to disk) Synchronous capture > persistent queue table User-enqueued (also non-LCRs) > disk (by default) or buffered queue, if configured Propagating messages from one queue to another Consuming messages by subscribers Placing messages in error queue Staging and Propagating Messages Instance SGA Streams pool Buffered queue Spill Capture Redo logs

  16. Apply Applying Messages • Directly applying the DML or DDL changes represented in the LCR (default) • Explicit dequeuing via open interface applications, such as JMS, C, OCI, or PL/SQL • Automatic conflict detection with optional resolution: • Unresolved conflicts are placed in an error queue. • Transactions can be reapplied or deleted from an error queue. • Customizable apply processing with handlers

  17. Rules Rules Rules Propagate Apply Rules • Are evaluated rules by a rules engine to perform actions • Limit which messages are captured, propagated, or applied • Are similar to the condition in the WHERE clause of a SQL query • Are grouped into a positive set (inclusion) and a negative set (exclusion of objects) Rule Sets Capture

  18. BEGIN DBMS_STREAMS_ADM.RENAME_SCHEMA( rule_name => 'STRMADMIN.HR51', from_schema_name => 'HR', to_schema_name => 'HR_REPL', operation => 'ADD'); END; Rule-Based Transformations • Transformations can be performed during: • Capture to format messages for all destination databases • Propagation to subset data before it is sent to a remote site • Apply to format messages for a specific database • Declarative Transformations: • Rename a schema • Rename a table or column • Add or delete a column • Custom Transformations: • User-supplied PL/SQL function

  19. Oracle Streams Database Configuration • Set database initialization parameters. • Ensure that the Oracle Streams memory requirements are met (Set STREAMS_POOL_SIZEto 200 MBor higher). • Configure archive logging. • Configure supplemental logging. • Configure database storage in an Oracle Streams database: • Configure a separate tablespace for the Oracle Streams administrator. • Use a separate queue for capture and apply Oracle Streams clients. • Grant user privileges to the Oracle Streams administrator. • Configure communication between your databases.

  20. Configuring Database Parameters

  21. Memory Requirements for Streams • Ensure that the Oracle Streams memory requirements are met to start Streams components. Each queue: 10+ MB Each queue: 10+ MB Each propagation: 1+ MB Each capture process parallelism: 10+ MB Each apply process parallelism: 1+ MB Streams Pool SGA Instance • Add Streams storage requirements.

  22. Configuring Archive Logging • Must be enabled at all source sites • Is used for all redo-based capture processes: local and downstream (not for synchronous capture) • Provides seamless transitions in reading • Requires availability of logs on disk (until they are no longer needed by any capture process) SGA Redo log buffer 1 Local capture process Online Redo Log 2 Archived Redo Log 3 Example: Configure log destination ALTER SYSTEM SET log_archive_dest_2 = . . .; ALTER SYSTEM SET log_archive_dest_state_2 = 'ENABLE'; ALTER SYSTEM SET log_archive_format='<SID>_%t_%s_%r.arc'

  23. Configuring Supplemental Logging • Places additional column data into a redo log whenever a DML operation is performed • Must be configured at the source site • For applying destination columns (details in notes) • Can be specified at the database level or table level • Database and table level: Identification key logging for primary key columns, unique index columns, foreign key columns, or all columns • Table level: For specific nonkey columns of a particular table with a user-defined supplemental log group UPDATE orders SET order_status=5 WHERE order_id=2457; col1(PK) :2457 col5(old): 4 col5(new): 5 Redo logs

  24. Database Supplemental Logging • Two types of database logging: • Minimal supplemental logging • Identification key logging • Databasewide supplemental logging: • Logs extra redo for all tables in the database • Is easier to manage and maintain than table-level supplemental logging • Database-level identification key logging methods: • FOREIGN KEY • ALL ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

  25. Table Supplemental Logging • Can be configured by: • Using table-level identification key clauses to generate either unconditional or conditional log groups • Creating a supplemental log group and specifying individual column names ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG GROUP dept_cols (department_id, department_name) ALWAYS; ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  26. Determining Enabled Supplemental Logging Methods • Database level: • Table level: SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN, SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI, SUPPLEMENTAL_LOG_DATA_FK FK, SUPPLEMENTAL_LOG_DATA_ALL "ALL" FROM V$DATABASE; SELECT owner, table_name, log_group_type FROM DBA_LOG_GROUPS;

  27. Streams Administrator (and Database Storage) • Performs administrative functions in a Streams environment • Must exist at all Streams sites • Needs a default and temporary tablespace other than SYSTEM • Requires the DBA role • May be granted other privileges explicitly or with the DBMS_STREAMS_AUTH package CREATE USER strmadmin IDENTIFIED BY streams DEFAULT TABLESPACE STRM_TBS1 TEMPORARY TABLESPACE TEMP; CREATE DIRECTORY scripts AS '/oracle/scripts'; GRANT DBA TO strmadmin; EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( - 'STRMADMIN', TRUE);

  28. Managing Streams Administrators SELECT * FROM DBA_STREAMS_ADMINISTRATOR; USERNAME LOCAL_PRIVILEGES ACCESS_FROM_REMOTE -------------- ---------------- ------------------ STRMADMIN YES NO EXEC DBMS_STREAMS_AUTH.REVOKE_ADMIN_PRIVILEGE( - grantee => 'STRMADMIN');

  29. Configuring CommunicationBetween Databases • Sending data or messages between sites requires network configuration at the source site. • Bi-directional data replication requires network configuration at both sites. • You must configure the following: • Network connectivity (for example, tnsnames.ora) • Database links CREATE DATABASE LINK remote_global_name CONNECT TO strmadmin IDENTIFIED BY streams USING 'connect_string_for_remote_db';

  30. Streams BFILE BFILE SOURCE_DB DEST_DB Externalfile Externalfile Directory Directory Additional Preparation for File Propagation

  31. Apply Capture Propagate Ways to Set Up Oracle Streams • Enterprise Manager • Simplified MAINTAIN scripts • Detailed PL/SQL API

  32. Simple Streams ConfigurationTable Replication BEGIN DBMS_STREAMS_ADM.MAINTAIN_TABLES( table_names => 'OE.PROMO_TEST1', source_directory_object => 'SRC_EXP_DIR', destination_directory_object => 'DST_EXP_DIR', source_database => 'AMER.US.ORACLE.COM', destination_database => 'EURO.US.ORACLE.COM', perform_actions => TRUE, bi_directional=> TRUE, instantiation=>DBMS_STREAMS_ADM.INSTANTIATION_TABLE); END; /

  33. Using the MAINTAIN_* Procedures Example Configurations Propagation Apply Redo transport Capture Apply Capture Redo logs Redo logs Redo logs Offload production: Downstream capture Report: Local capture Update Multiple sources Single source Consolidate Cascade Bi-directional Disseminate

  34. Queue LCR LCR User msg LCR User msg LCR LCR . . . Queue LCR LCR User msg LCR User msg LCR LCR . . . Queue LCR LCR User msg LCR User msg LCR LCR . . . Redo Log User User Identifying Streams Processes Destination Propagate messages Jnnn Source Enqueue LCRs Capture process amer_CP01 Enqueue LCRs Dequeue LCRs Apply process euro_AP01 MS01 Capture changes Synchronous capture Part of transaction: Capture changes Apply changes Log changes Database objects Database objects Database objects User changes User changes

  35. Configuring Multiple Streams Sites • If your Streams configuration consists of multiple sites, you must diagram the components to be configured at each site. Be sure to indicate how the following components interact with other sites. • Source queues and destination queues • Capture and apply processes • Propagation processes and message routing • Configuration requirements for all Streams processes at each site

  36. CP01 AP01 AP01 AP02 CP02 AP03 AP04 CP04 Spoke4 Spoke2 Hub1 CP03 AP01 Spoke3 Hub-and-Spoke Configuration

  37. CP01 AP02 AP03 EMEA CP02 CP03 AP01 AP01 AP03 AP02 AMER APAC N-Way Multi-Master Replication

  38. Summary • In this lesson, you should have learned how to: • Define Oracle Streams • List the three basic Streams elements: capture, propagation, and apply of messages • Provide examples of Oracle Streams implementation • Configure databases for Streams, including: • Setting database initialization parameters • Configuring memory and database storage • Configuring archive and supplemental logging • Creating a Streams administrator • Configuring communication between your databases • Replicate a table by using the MAINTAIN_* procedure

  39. Practice 1 Overview: Viewing the Database Configurations • In this practice, you confirm that the basic setup tasks have been performed and that the AMER and the EURO database are configured as Streams source and destination databases. 1. Gather configuration information. 2. Verify the network configuration. 3. Verify the source database configuration for AMER. 4. Verify the destination database configuration for EURO. 5. Create a test table and enable supplemental logging. 6. Use the MAINTAIN_TABLES procedure to replicate the table. 7. Test your configuration: Insert a row on AMER and view it on EURO. 8. Insert a row on EURO and view it on AMER.

  40. Result of Practice 1:Bi-directional Table Replication AMER database EURO database AMER$CAPQ AMER$APPQ AMER$CAP PROPAGATION$6 APPLY$AMER_2 OE. PROMO_ TEST1 OE. PROMO_ TEST1 APPLY_AMER_2 PROPAGATION$5 EURO$CAP EURO$APPQ EURO$CAPQ

  41. Using Enterprise Manager for Oracle Streams

  42. Objectives • After completing this lesson, you should be able to: • Use wizards to configure Oracle Streams for data replication • Manage Oracle Streams processes and components, such as: • Capture process • Rules • Propagation • Apply process and apply process errors • Queues, queue subscribers, and queue tables • Queue messages and message transformations

  43. Configuring Streams with EM Setup Wizard • Enterprise Manager > Data Movement > Setup (in the Streams section)

  44. Global, Schema, Table, andSubset Replication Wizard

  45. Global, Schema, Table, and Subset Replication Wizard • Customizing your database objects • on the Configure Replication page On the Object Selection page

  46. Streams Tablespace Replication Wizard • Configuring Streams • Cloning tablespaces • Starting apply • Starting capture

  47. Messaging Setup a349

  48. Managing Streams • Streams Overview page • Managing the capture process • Managing rules • Managing propagation • Managing the apply process • Managing queues, queue subscribers, and queue tables • Managing queue messages and message transformations

  49. Streams Overview Page

  50. Managing the Capture Process

More Related