Oracle 11g Streams Implementer's Guide
By Ann L. R. McKinnell and Eric Yen
()
About this ebook
From smaller businesses through to huge enterprises - users all over the world often require access to data 24 hours a day. Distributed database systems proliferate the world of data sharing providing an ability to access real-time data anywhere, anytime. Oracle Streams, a built-in feature of the Oracle database, is a data replication and integration feature critical to the success and wellbeing of enterprises in today's fast moving economy. This book provides the reader with solid techniques to master Oracle Streams technology and successfully deploy distributed database systems.
This book quickly goes over the basics and gets you up and running with a simple Oracle 11g Streams environment. It will serve as an excellent companion to the Oracle Streams Administration Guide. It is intended for Oracle database architects and administrators, and provides in-depth discussion on must-know information for the design, implementation, and maintenance of an Oracle Streams environment.
The book does not attempt to regurgitate all the information in the Oracle Streams Administration Guides, but rather provides additional clarification and explanation of design, implementation, and troubleshooting concepts that are often elusive in Streams documentation. It also identifies helpful tools and Oracle resources to add to your knowledge base, as well as tried and tested tricks and tips to help you tame Oracle Streams.
The book starts by introducing and explaining the components of Oracle Streams and how they work together. It then moves on logically, helping you to determine your distributed environment requirements and design your Streams implementation to meet those requirements. Once these concepts are discussed, the book moves to configuration and basic implementation examples to help solidify those concepts. It then addresses advanced features such as tags, down-stream capture, and conflict resolution. You then move on to maintenance techniques such as documenting the environment, effectively planning and implementing changes to the environment, and monitoring and troubleshooting the environment. When you have studied the techniques and completed the hands-on examples, you will have an understanding of Oracle Streams' core concepts and functionally that will allow you to successfully design, implement, and maintain an Oracle Streamed environment.
ApproachThis book has a tutorial approach that provides both theoretical explanations and practical step-by-step examples using familiar Oracle demo schemas. Step-by-step examples include use of both Enterprise Manager and the SQLPLUS command-line interface. The authors have combined their experiences of working with customers in Oracle Support, Oracle training and education, and Oracle consulting, to identify the most common areas of confusion and misinterpretation experienced by Oracle Streams users. This book highlights these areas and focuses on bringing clarity and understanding to the reader.
Who this book is forThis book is for users who have implemented, or are about to implement, a distributed database environment using Oracle Streams replication. The reader is expected to have an intermediate understanding of Oracle database technology.
This book is mainly intended for use by:
- Oracle Database Administrators responsible for the implementation and maintenance of an Oracle Streamed environment
- System and Database Architects responsible for the design of distributed database environments using Oracle Streams
Readers with limited knowledge of basic Oracle database technology can benefit from Chapters 1 and 2, which cover Oracle Streams Components and Design Considerations.
Ann L. R. McKinnell
Ann McKinnell is currently a Senior Principal Consultant with APG Technologies, LLC and has been an OCP since Oracle 7.3.4. She came to APG with over 8 years as a senior technical member of Oracle Global Support, specializing in Replication and Distributed System technologies. She was a recognized global technical expert for Oracle Distributed Systems; earning the internal nickname "The Replication Goddess". Ann has trained Oracle Support and Consulting personnel from many countries in Advanced Replication and Distributed System Internals and Problem Solving techniques. She has authored and co-authored many of the Oracle Advanced Replication notes found on Oracle Metalink, and was a technical reviewer for the Oracle University Pilot 9i Steams course material, as well as various Oracle Replication and Database Administration user manuals. Ann continues to specialize in practical implementation strategies and the development of distributed Oracle database systems, database architecture, and software and database design and integration.
Related to Oracle 11g Streams Implementer's Guide
Related ebooks
Oracle Data Guard 11gR2 Administration Beginner's Guide Rating: 0 out of 5 stars0 ratingsLearning Apache Cassandra Rating: 0 out of 5 stars0 ratingsOracle GoldenGate 12c Implementer's Guide Rating: 0 out of 5 stars0 ratingsOracle GoldenGate 11g Implementer's guide Rating: 5 out of 5 stars5/5Getting Started with Oracle Data Integrator 11g: A Hands-On Tutorial Rating: 5 out of 5 stars5/5Oracle Information Integration, Migration, and Consolidation Rating: 0 out of 5 stars0 ratingsGetting Started with Oracle WebLogic Server 12c: Developer’s Guide Rating: 0 out of 5 stars0 ratingsOracle Recovery Appliance Handbook: An Insider’S Insight Rating: 0 out of 5 stars0 ratingsOCA Oracle Database 11g Database Administration I: A Real-World Certification Guide Rating: 4 out of 5 stars4/5PostgreSQL Server Programming - Second Edition Rating: 0 out of 5 stars0 ratingsOracle Database 11g - Underground Advice for Database Administrators: Beyond the basics Rating: 0 out of 5 stars0 ratingsOCA Oracle Database 11g Administration I Exam Guide (Exam 1Z0-052) Rating: 0 out of 5 stars0 ratingsOracle SOA Suite 12c Administrator's Guide Rating: 0 out of 5 stars0 ratingsMastering JBoss Enterprise Application Platform 7 Rating: 0 out of 5 stars0 ratingsMaster The Configuration Of Apache Tomcat On Linux Rating: 0 out of 5 stars0 ratingsSQL Server 2008 Administration in Action Rating: 0 out of 5 stars0 ratingsOracle SOA BPEL Process Manager 11gR1 A Hands-on Tutorial Rating: 5 out of 5 stars5/5Learning Hadoop 2 Rating: 4 out of 5 stars4/5PostgreSQL 9.0 High Performance Rating: 4 out of 5 stars4/5Oracle Database 12c Backup and Recovery Survival Guide Rating: 5 out of 5 stars5/5Oracle Database 12c Release 2 New Features Rating: 0 out of 5 stars0 ratingsHDInsight Essentials - Second Edition Rating: 0 out of 5 stars0 ratingsOracle 10g/11g Data and Database Management Utilities Rating: 0 out of 5 stars0 ratingsInstant Oracle GoldenGate Rating: 0 out of 5 stars0 ratingsPostgreSQL 9 Administration Cookbook - Second Edition Rating: 0 out of 5 stars0 ratingsPostgreSQL 9 High Availability Cookbook Rating: 5 out of 5 stars5/5EDB Postgres Third Edition Rating: 0 out of 5 stars0 ratingsAWS Organizations Second Edition Rating: 0 out of 5 stars0 ratingsOracle Exalytics Revealed: E-Book Rating: 0 out of 5 stars0 ratingsOracle Data Guard A Clear and Concise Reference Rating: 0 out of 5 stars0 ratings
Computers For You
The Invisible Rainbow: A History of Electricity and Life Rating: 5 out of 5 stars5/5Slenderman: Online Obsession, Mental Illness, and the Violent Crime of Two Midwestern Girls Rating: 4 out of 5 stars4/5Elon Musk Rating: 4 out of 5 stars4/5The Innovators: How a Group of Hackers, Geniuses, and Geeks Created the Digital Revolution Rating: 4 out of 5 stars4/5The Professional Voiceover Handbook: Voiceover training, #1 Rating: 5 out of 5 stars5/5Uncanny Valley: A Memoir Rating: 4 out of 5 stars4/5Standard Deviations: Flawed Assumptions, Tortured Data, and Other Ways to Lie with Statistics Rating: 4 out of 5 stars4/5Procreate for Beginners: Introduction to Procreate for Drawing and Illustrating on the iPad Rating: 0 out of 5 stars0 ratingsUltimate Guide to Mastering Command Blocks!: Minecraft Keys to Unlocking Secret Commands Rating: 5 out of 5 stars5/5Mastering ChatGPT: 21 Prompts Templates for Effortless Writing Rating: 5 out of 5 stars5/5101 Awesome Builds: Minecraft® Secrets from the World's Greatest Crafters Rating: 4 out of 5 stars4/5The ChatGPT Millionaire Handbook: Make Money Online With the Power of AI Technology Rating: 4 out of 5 stars4/5Alan Turing: The Enigma: The Book That Inspired the Film The Imitation Game - Updated Edition Rating: 4 out of 5 stars4/5The Hacker Crackdown: Law and Disorder on the Electronic Frontier Rating: 4 out of 5 stars4/5How to Create Cpn Numbers the Right way: A Step by Step Guide to Creating cpn Numbers Legally Rating: 4 out of 5 stars4/5SQL QuickStart Guide: The Simplified Beginner's Guide to Managing, Analyzing, and Manipulating Data With SQL Rating: 4 out of 5 stars4/5Deep Search: How to Explore the Internet More Effectively Rating: 5 out of 5 stars5/5Dark Aeon: Transhumanism and the War Against Humanity Rating: 5 out of 5 stars5/5Everybody Lies: Big Data, New Data, and What the Internet Can Tell Us About Who We Really Are Rating: 4 out of 5 stars4/5People Skills for Analytical Thinkers Rating: 5 out of 5 stars5/5Grokking Algorithms: An illustrated guide for programmers and other curious people Rating: 4 out of 5 stars4/5CompTIA IT Fundamentals (ITF+) Study Guide: Exam FC0-U61 Rating: 0 out of 5 stars0 ratingsCompTIA Security+ Get Certified Get Ahead: SY0-701 Study Guide Rating: 5 out of 5 stars5/5
Reviews for Oracle 11g Streams Implementer's Guide
0 ratings0 reviews
Book preview
Oracle 11g Streams Implementer's Guide - Ann L. R. McKinnell
Table of Contents
Oracle 11g Streams Implementer's Guide
Credits
About the Authors
About the Reviewers
Preface
Replication in general
Distributed database systems
What is Data Replication?
How do Replication
and Distributed Systems
interact?
Why would we want to replicate?
Replication architecture
Replication methods
Replication configurations
Oracle Streams
What this book is (and is NOT)
Who this book is for
Conventions
Reader feedback
Customer support
Errata
Piracy
Questions
1. All the Pieces: The Parts of an Oracle 11g Streams Environment
Streams architecture overview
Topology configurations
Single source
Single database
Uni-directional
Cascaded/directed network
Hub-and-Spoke
Multiple source
Bi-directional/N-way/Master-to-Master
Uni-directional Spokes-to-Hub
Bi-directional Spoke-to-Hub
Hybrid
Heterogeneous
Simultaneous versus Synchronous replication
Oracle's Streams replication process flow
Streams components
About those Queues
Capture process—what are we supposed to stream?
Downstream Capture
Synchronous Capture
Instantiation
What sets the instantiation SCN and when?
Propagate process
The Network: COMLINK
Propagation success/failure
Propagation Stream Split and Merge
Apply process
Trigger firing and Apply
Combined Capture and Apply
SCN Coordination—keeps it flowing smoothly
The SCNs of Capture
FIRST_SCN
START_SCN
REQUIRED_CHECKPOINT_SCN
CAPTURED_SCN
APPLIED_SCN
MAXIMUM_SCN
LAST_ENQUEUED_SCN
SOURCE_RESETLOGS_SCN
MAX_CHECKPOINT_SCN
The SCNs of Propagation
The SCNs of Apply
IGNORE_SCN
MAXIMUM_SCN
OLDEST_SCN_NUM
Low-watermark SCN
SCN SYNC-hronization
Capture checkpointing
Archive Log availability
LCRs—what they are and how they work
Extracting data from an LCR
Conflict detection and the LCR
Controlling conflict detection
Types of LCRs and how they get created
Oracle 11g memory and storage architecture (basic) relating to Streams
A word on performance
Streams Change tables
Oracle GoldenGate XSTREAMS
Summary
2. Plot Your Course: Design Considerations
Why?
What?
Where?
Who and How?
When and How?
Other factors to consider
Network capabilities
Transaction sizes
Potential queue growth
Additional hardware resource requirements
Administration and maintenance costs
Third party application requirements
Security
Change auditing
Platform and version compatibility
KISS
Design aid: Streams site matrix
The Matrix template
Summary
3. Prepare the Rafts and Secure Your Gear: The pre-work before configuring Oracle 11g Streams
Network connectivity
Check the waterways
Configure the Oracle Net Current
Configure the database
Initialization parameters
Logging features
Archive logging
Supplemental logging
Forced logging
Separate tablespaces
LogMiner tablespace
Streams Administration tablespace
Streams users and privileges
Trusted Streams Administrator user configuration
Untrusted Streams capture, propagation, and apply user configuration
Streams Administration user
Capture user
Propagation user
Apply user
Database links
Trusted versus untrusted configurations
Understanding your Instantiation tools
Using Data Pump to Instantiate
Setting Instantiation SCN manually
Oracle Demo Schemas
Summary
4. Single-Source Configuration
The stream flows one way: Downhill
The Enterprise Manager
Setup options
Configure replication
Processes
Directory objects
Options
Object selection
Review
Schedule Streams setup job
Verify
The code behind the curtain
Checking the waters
Diving in
The proof is in the pudding (or propagation in this case)
Sequences and triggers and Apply
Other levels at which to replicate
The beauty of DBMS_STREAMS_ADM.MAINTAIN_*
Summary
5. N-Way Replication
Pre-planning for N-way replication
Avoiding conflict
The setup
Preliminary setup
Streaming STRM1 to STRM2
Streaming STRM2 to STRM1
Conflict resolution
Extending the example
Rinse and repeat
Summary
6. Get Fancy with Streams Advanced Configurations
Synchronous Capture—straight to the Queue
Subsetting — the micro side of replication
Tag! — you're it
The default behaviour of tags
Making tags work for you
Setting the tag value
Evaluating tags at the replication process rule level
INCLUDE_TAGGED_LCR
INCLUSION_RULE
AND_CONDITION
LCR subprograms
Tag usage
Capture at the Hub
Capture, Propagate, and Apply at each spoke
Apply at the Hub
Propagation at the Hub
RULES — they're what we live by
Rule components
Rule conditions
Rule evaluation context
Action context
Creating your own rules
Rule creation
Rule Sets
Event context
How it all comes together
Rule based transformation — eat your heart out transformers!
Declarative versus User Created
Declarative transformation
User created Rule Based Transformations (UCRBT)
Transformation order of execution
How the transformation is processed
At the Capture (basic and synchronous)
At the Propagation
At the Apply
Transformation errors
At the Capture (basic and synchronous)
At the Propagation
At the Apply
Things to remember when working with Rules
Downstream Capture — avoid white water at the source
Setting up the redo log transport
Configuring the Streams part of DSC
Streams change tables — just tracking the Facts
Ma'am
Automatic propagation split and merge — redirecting the current
Basic Heterogeneous Configuration
Configuring a Heterogeneous Apply process
Data Transfer via Queue Messaging
Basic XSTREAMS Configuration
XSTREAMS Servers
Configuring the Database
Configuring XSTREAMS Out
Steps for configuring an XSTREAMS Outbound Server
Subsetting Rules for an Outbound Server
Configuring XSTREAMS In
Steps for configuring an XSTREAMS inbound server
Summary
7. Document What You Have and How It Is Working
Mapping the Stream
The Stream without a map
DBMS_STREAMS_ADVISOR_ADM
Making the map
Basic Streams views
UTL_SPADV
Automating the collection of Streams performance data
Summary
8. Dealing with the Ever Constant Tides of Change
Affecting expected change effectively
Changing States: Starting and stopping processes
Database changes
Structure changes to existing objects
Data changes — beware the bulk load!
Expanding your Streamed environment
Example: Adding a Master Site
Time based values used for comparision and time zones.
Example: Adding a table to a replicated schema
Shrinking the Streamed environment
Removing table, scheme, and tablespace level replication from Streams
Removing a site from a Streamed environment
Troubleshooting unexpected changes and resulting Streams errors
Failure Points and Most Likely Causes (a.k.a. FPs and MLCs)
Failure Point 1: DML/DDL statement commit logging
Successful commit
NOLOGGING option
Archive Log mode
Failure Point 2: LogMiner
Supplemental logging
_LOG_PARALLELISM
LOGMNR_MAX_PERSISTENT_SESSIONS
Failure Point 3: Capture process and rules
Memory allocation
Capture process status
Capture queue designation
Capture Rules and Rule Sets
Failure Point 4: Capture enqueue
Capture user privileges
Failure Point 5: Propagation dequeue from Capture queue
Propagation Status
Propagation source configuration
Propagation Job
Failure Point 6: Propagation Rules
Failure Point 7: Database link configuration
Failure Point 8: Network connectivity and stability
Failure Point 9: Propagation enqueue to the Apply queue
Destination availability
Propagation destination configuration
Failure Point 10: Apply dequeue
Apply process status
Apply user privileges
Failure Point 11: Apply Rules
Apply latency
Failure Point 12: Conflict detection and resolution rules
Failure Point 13: Apply Errors
Troubleshooting tools
Enterprise Manager: Streams management
Streams Overview
Command line packages and scripts
Compare and Converge divergent data.
Comparing data
Oracle HealthCheck
Custom QuickCheck
Extract LCRs.
Tricks and tips
Keep propagation going on an unstable network
How to change a Streams process Rule
Summary
9. Appendix and Glossary
Oracle Streams Commander
Streams and Oracle RAC
Oracle GoldenGate
Glossary
Summary
Index
Oracle 11g Streams Implementer's Guide
Eric Yen
Ann L. R. McKinnell
Oracle 11g Streams Implementer's Guide
Copyright © 2010 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: January 2010
Production Reference: 1130110
Published by Packt Publishing Ltd. 32 Lincoln Road Olton Birmingham, B27 6PA, UK.
ISBN 978-1-847199-70-6
www.packtpub.com
Cover Image by Ann L.R. McKinnell (<[email protected]> )
Credits
Authors
Ann L. R. McKinnell
Eric Yen
Reviewer
Shekar Kadur
Lavanya Kompella
Acquisition Editor
James Lumsden
Development Editor
Dilip Venkatesh
Technical Editors
Neha Damle
Arani Roy
Copy Editor
Sanchari Mukherjee
Indexer
Rekha Nair
Production Editorial Manager
Abhijeet Deobhakta
Editorial Team Leader
Gagandeep Singh
Project Team Leader
Lata Basantani
Project Coordinator
Poorvi Nair
Proofreader
Andie Scothern
Production Coordinator
Aparna Bhagat
Cover Work
Aparna Bhagat
Graphics
Nilesh R. Mohite
Geetanjali Sawant
About the Authors
Ann L.R. McKinnell, a Colorado native, has been an OCP since Oracle 7.3.4. She has more than 16 years of IT experience; with over 8 years as a senior technical member of Oracle Global Support, specializing in Replication and Distributed system technologies. She was a recognized global technical expert for Oracle replication; earning the internal nickname Replication Goddess
. Ann has trained Oracle Support and Consulting personnel from many countries in Advanced Replication and Distributed System Internals and problem solving techniques. Ann has authored and co-authored many of the Oracle Advanced Replication notes found on Oracle Metalink, and was a technical reviewer for the Oracle University Pilot 9i Streams course material, as well as various Oracle Replication and Database Administration user manuals. Ann continues to specialize in practical implementation strategies and the development of distributed Oracle database systems, database architecture, and software and database design and integration, and is currently a Senior Principal Consultant with APG Technologies, LLC.
As we go through life, our paths are often greatly influenced by even the slightest of touches from others. Whether knowingly or not, near or far, well-known or acquaintances, you have all produced far reaching ripples in my life's stream.
Mike Pomphrey, for giving me my break into the IT business and introducing me to Oracle (not to mention providing tongue-in-cheek bragging rights that Lockheed-Martin waited at the door after hours for me!). Little did I know of the journey of opportunity that began that day at the Job Fair so long ago. Thank you, not only for recognizing a diamond in the rough, but for your friendship and support over these many years. Though we don't see each other often, I have come to believe that when our paths cross, it is God's way of telling me that great opportunities are right around the corner.
Andy Taylor, my greatest professional supporter. Friend, you never wavered in helping me attain what I myself never thought attainable. Your own talents and abilities have inspired me to reach far beyond my comfort zone and broaden my horizons. Whenever you saw the opportunity, you not only showed me the door, but opened it and pulled me through. Also, to Chip Brown, for so often handing Andy the keys
to open those doors and do it
. It is these doors of opportunity that have led me here.
To my Replication mentors: Rhonda, David, and Janet.
Rhonda Cordonnier (the original Replication Goddess of Oracle Support). For taking advantage of my technical naivety so many years ago and convincing me that I DID want to learn Replication. David Russell …of the UK
for inviting me to join the Replication PA team at Oracle and mentoring me in the best practices of technical writing for all those Metalink notes. Janet Stern, though it has been many years, the document and beta review invitations, and phone mentoring marathons are still fresh in my mind (it's been almost a decade since you sat on the phone with me for 5 hours explaining heterogeneous gateways, while 7 months pregnant and no break — I am STILL in awe!). Perhaps without even knowing it, you were my most instrumental technical mentor.
To the illustrious Mr. Yen for calling me up and asking Have you ever thought of writing a book?
Wow! What a ride! Thank you for all your help and support, I never would have done this if it weren't for you. Can't wait for the next adventure!
To Rodger, and Tony of APG Technologies for your Johnny on the spot
IT support and helping us with the test bed. Also to Eric Amberge, for giving us the thumbs-up to pursue this opportunity to push our personal boundaries and expand our horizons.
To the Oracle 11gR2 beta team for allowing us the opportunity to play
with the latest and greatest incarnation. Thank you for your support and assistance.
To our publishing team at Packt Publishing for making this all possible. Also, to our editors and reviewers for all their hard work and dedication in bringing this book to intelligible print. Your support, patience, expertise, and assistance have been invaluable. And to Lavanya, for stepping in at the eleventh hour to help us with the final reviews.
To my friends and family who have been so supportive and understanding throughout the writing of this book. Thank you for not forgetting that I exist, and pulling me out of my cave
every so often to remind me that the sun still shines in the sky and in the hearts of those close to me. To Renee, Patrick, and the helpful and handsome, blue-eyed cowboy (whose name is unknown) at the dude ranch at the end of the white fence; for helping me find my way back to one of my favorite places on earth to take the cover picture. And to God for giving me that incredible place all to myself that beautiful day.
To my parents, for life; and the brains, encouragement, and sense of humor to live it. I hope your life's choices have brought you the happiness and peace you sought with them, as mine have me.
To Rachel and Jacob, the greatest gifts and loves of my life. For the unwavering support, encouragement, and unconditional love. My strength and my joy. It is because of you that I am who I have become. You ARE the best of me.
Most of all, to Him and His; through whom all things are possible. For bringing each and every one of you into my life to help me come to this point.
Eric Yen began working with Oracle Databases at the time of version 7.3.4. Over the next 14 years, he obtained his Oracle DBA Certification starting with version 8 and maintaining it up to the current release and also earned the (ISC)2 CISSP certification. He began working with Oracle Streams with Oracle 9i Streams beta. As a Senior Principal Consultant with APG Technologies, LLC, Eric's work includes designing and implementing Streams solutions for Government clients using the more recent versions of Streams in Oracle 10 and Oracle 11. In his little spare time, you can find Eric exercising and tinkering around with Oracle products.
On occasion I have moments where I wonder How did I get here?
Well, as we finish this book, now is the time to pause and reflect. I would like to thank the Professor who first taught me about Oracle, Professor Hutch
. Professor Hutch
always challenged the students with the statement go ahead and try that, see if it works
, never giving us the easy way out and forcing us to learn through our actual experiences. To the friends and managers that were part of the Oracle SCHOLAR program where good memories were made being in the crucible. The Oracle SCHOLAR program was an unforgettable experience, for it set the foundations for what I am now with regards to Oracle.
Thanks to the Replication Goddess
for saying sure that sounds exciting
when I asked her to co-author this book. Ann, it's been one interesting and exciting journey and I could not have done it without you.
To the members of APG Technology, it's a pleasure to work with all of you. This is the best group of talent and personalities I have ever seen. To Mike Janeway and Eric Amberge, things have definitely changed since the meeting at the Proud Bird. Thanks to both of you for bringing me on board and providing support.
To the team at Packt Publishing, thanks for providing this platform for us. I never knew the amount of behind-the-scenes work and editing done to get a book published. This team rocks!
To Yvonne Yu for being part of my life in a way only you can be.
To B and Turtle, thanks for adding perspective outside of my work. Turtle thanks for more than you could ever know.
To my parents, thanks for always doing your best for my sister and I, even when I was not doing my best.
To Richard Rose, Connie Yen-Rose, Carlie Rose and Emma Rose love you all.
About the Reviewers
Shekar Kadur has over 23 years of experience in Information Systems specifically designing, developing, and managing complete system development lifecycle of projects involving Databases, Data warehousing, Business Intelligence, OLAP, SAP, and Enterprise Management Reporting applications in the automotive, finance, utility, retail, and healthcare industries.
He is a certified PMP (Project Management Professional), a certified Hyperion instructor and a consultant proficient with all Oracle and Hyperion toolsets (Essbase, Planning, and so on). He is extremely proficient in project/program management of applications using Oracle, Hyperion, SAP, SAPBW, Business Objects, and web-based technologies. He has consulted, deployed, and managed IT projects at Ford Motor Company, Ford Motor Credit Corporation (Ford Credit), General Motors, Daimler Chrysler Financial Corporation, Daimler Chrysler, Consumers Energy, Guardian Industries, Oakwood Health Systems, General Dynamics, Management Technologies Inc, TRW, Constellation Brands Inc, Johnson Controls Inc, Deloitte Consulting, and Capgemini Inc.
He has delivered lectures on Data warehousing, Datamarts, Oracle, and Hyperion toolset in Michigan, USA and London, UK.
He has also been a technical reviewer of the Oracle Essbase 9 Implementation Guide book published by Packt in 2009.
Lavanya Kompella is an experienced Oracle DBA who started her Oracle career on V6. Her areas of expertise include Advanced Replication, Streams, and AQ. She is an Oracle Certified DBA (OCP) from V7 through to V11.
Her previous employers include Tata Consultancy Services and Oracle USA. She is currently part of the DBA team of WELLSFARGO in India.
I would like to thank my wonderful husband Chandra, who always wanted nothing but the best for me. Without his encouragement and cooperation I wouldn't be where I am today.
Preface
This Preface and the entire book are a little bit different — and that is by design. Both authors wrote this book understanding that our target audience often does not have time to read a whole book, or the Oracle documentation, from cover to cover. As such, we wrote this book with the idea that the table of contents and headings should tell you exactly what is being covered. Bullet lists will be used to quickly highlight key points where appropriate. Where concepts need to be explained in more detail, a supporting narrative is supplied. Another difference is that we make multiple references to Oracle documentation rather than attempting to rewrite everything. This is also by design. Having seen Oracle documentation evolve over the years, both authors, and our publisher, recognize the intrinsic value of getting specific detailed information straight from the horse's mouth
. To promote the development of overall expertise, we focus on helping our readers effectively use all the tools available. The Oracle documentation is one of your most valuable tools. At times, Oracle documentation can be difficult to follow or find information within, but once you develop an expertise in using the documentation, the expertise in the functionality is not far behind. The focus of this book is not to replace the Oracle documentation, but rather to be a quick reference companion to the Oracle documentation.
Replication in general
The concept of replication is simply to duplicate. Birds do it, bees do it, and even cells do it. However, replication is not limited to the biological world. Accurately duplicating data from which information is derived is the foundation of human communication. Whether that data be the words or gestures used to convey a story that is handed down from generation to generation, or the numbers used to quantify the quantifiable, or the grouping of on/off bits stored in a computer file; humans have been replicating data since they discovered the need to communicate.
Now that we have evolved into the wonderful age of computerized technology, we recognize the limitless advantages of sharing data, and the need to accurately and efficiently duplicate and distribute that data.
Distributed database systems
We all know that a database is a collection of data objects that are typically accessed through a client/server architecture, and where the database is the server.
We also know that client/server architecture uses a network communication channel that allows the client to send or get data to/from the database. The client can be local (on the same computer as the database), or it can be remote (on a different computer than the database). Either way, the client uses some type of network connection to access the database.
The sharing of data between two or more databases constitutes a distributed database system (even if the databases reside on the same computer). Distributed database systems can be homogeneous (all on the same platform, such as Oracle) or heterogeneous (two or more platforms, such as Oracle, MS SQL Server, SYBASE, and so on.) These systems can utilize a number of data distribution methods (unidirectional, bidirectional, read-only, synchronous, and asynchronous). The glue that holds this all together is the network and database links between the various databases.
A database link is a one-way communication channel from one database (source) to another database (target) that allows the source database to access the objects in the target database.
Key terms that have been discussed and should be understood here are: database link, communication channel, and network connections. These all work to provide connectivity in a distributed system. It is very important to understand that network connectivity makes or breaks a distributed system. No network connection means no data distribution. An unstable network means unstable data distribution.
Now that you have a distributed database system, add client applications that access one or more databases in that distributed database system, and voila! You have a full-blown distributed system.
What is Data Replication?
Data Replication is literally the act of accomplishing data object changes throughout a distributed system. Period. Replication can be manual, or it can be automated. Automated is the preferred mode of 3 out of 4 DBA's surveyed (we do not really count the 4th, he's semi-retired and has nothing better to do).
How do Replication
and Distributed Systems
interact?
Replication makes data located in different databases available to all databases within the distributed system. So replication is the method behind a distributed system. It moves the data around to different sites.
Tip
Databases within a distributed system are often referred to as sites. As mentioned earlier, databases can be physically co-located on the same computer, but the databases themselves could still be referred to as separate sites. The term 'site' is more of a logical distinction, than a physical distinction.
Why would we want to replicate?
There are a number of reasons to replicate data, but it is a good bet that they all boil down to increased availability. This means that the same data is available at different sites, and the flow of data between those sites is automated. Replication supports increased availability by providing the following:
Change consistency: Ensures that all sites get the same change.
Mass deployment/disconnected computing: Data can be sent to secondary computers (laptops, desktops) so that it is available when these devices might be offline.
Faster access: Load balancing is the art of distributing client connections over multiple databases. This comes in really handy when the system has a large number of users, and even more so if those users are geographically separated from the system databases. The user just connects the geographically closest database. Network load can also be reduced by directing traffic over different routers for different database sites.
Survivability: Data is still accessible if one site fails.
Tip
When not to use replication for survivability purposes
If the need is to only support survivability and data changes made at a single site, there are better tools to use to support survivability that require a little less configuration, maintenance, and monitoring. For example: Data Guard!
Replication architecture
Replication architecture refers to the overall structure of the replicated environment. This includes what is replicated between the sites and the role of each site. The following terms are used to make these distinctions:
Master table/object: A table or object that is replicated to another database. A replicated table can be