55,19 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Oracle Goldengate 11g Complete Cookbook is your complete guide to all aspects of Goldengate administration. The recipes in this book will teach you how to setup Goldengate configurations for simple and complex environments requiring various filtering and transformations. It also covers various aspects of tuning and troubleshooting the replication setups using exception handling, custom fields, and logdump utility.The book begins by explaining some basic tasks like Installation and Process groups setup. You will then be introduced to some further topics including DDL replication and various options to perform Initial Loads. You will then learn some advanced administration tasks such as Multi Master replication setup and conflict resolution. Further recipes, contain the cross platform replication and high availability options for Goldengate.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 401
Veröffentlichungsjahr: 2013
Ähnliche
Table of Contents
Oracle GoldenGate 11g Complete Cookbook
Oracle GoldenGate 11g Complete Cookbook
Copyright © 2013 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 author, 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: September 2013
Production Reference: 2200913
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-614-3
www.packtpub.com
Cover Image by Karl Moore (<[email protected]>)
Credits
Author
Ankur Gupta
Reviewers
Judy (Shuxuan) Nie
Jos van den Oord
Gavin Soorma
Michael Verzijl
Acquisition Editor
Antony Lowe
Lead Technical Editor
Ankita Shashi
Technical Editors
Ruchita Bhanshali
Vrinda A. Bhosale
Kapil Hemnani
Mrunmayee Patil
Project Coordinators
Anurag Banerjee
Abhijit Suvarna
Proofreader
Joanna McMahon
Indexer
Priya Subramani
Graphics
Ronak Dhruv
Production Coordinator
Melwyn D'sa
Cover Work
Melwyn D'sa
About the Author
Ankur Gupta is an Oracle Database Consultant based in London. He has a Master's degree in Computer Science. He started his career as an Oracle developer and later on moved into database administration. He has been working with Oracle Technologies for over 11 years in India and the UK. Over the last 6 years, he has worked as an Oracle Consultant with some of the top companies in the UK in the areas of investment banking, retail, telecom and media.
He is an Oracle Certified Exadata, GoldenGate Specialist, and OCP 11g DBA. His main areas of interest are Oracle Exadata, GoldenGate, Dataguard, RAC, and Linux.
Outside the techie world, he is an avid cook, photographer, and enjoys travelling.
I would like to thank my wife for putting up with my long writing sessions over late nights and weekends that I spent working on this book. Without her love and support, this book would not have been possible.
My deepest gratitude to my parents for allowing me to realize my own potential. I would also like to thank my mentors Dr. Prateek Bhatia and Bikramjit Singh whose excellent teaching methods built my interest in the field of databases.
About the Reviewers
Judy (Shuxuan) Nie is a Senior SOA Consultant specializing in SOA and Java technologies. He has 14 years of experience in the IT industry that includes SOA technologies such as BPEL, ESB, SOAP, XML, and Enterprise Java technologies, Eclipse plug-ins, and other areas such as C++ cross-platform development.
Since 2010, he has been working at Rubicon Red, helping customers resolve integration issues, and design and implement highly available infrastructure platforms on Oracle VM and Exalogic.
From 2007 to 2010, he had been working in the Oracle Global Customer Support Team and focused on helping customers solve their middleware/SOA integration problems.
Before joining Oracle, he had been working for the IBM China Software Development Lab for four years as a staff software engineer, participated in several complex products on IBM Lotus Workplace, WebSphere, and Eclipse platform; and then joined the Australia Bureau of Meteorology Research Center, responsible for implementation of the Automated Thunderstorm Interactive Forecast System for Aviation and Defense.
He holds an MS in Computer Science from Beijing University of Aeronautics and Astronautics.
Jos van den Oord is an Oracle Consultant/DBA for Transfer-Solutions in the Netherlands. He has specialized in Oracle Database Management Systems since 1998, with his main interest being in Oracle RDBMS Maximum Availability Manageable Architecture Environments (Real Application Cluster, DataGuard, MAA, and Automatic Storage Management). He is a proud member of the Oracle Certified Master community, having successfully passed the exam for Database 11g. He prefers to work in the field of advising, implementing, and problem-solving with regards to the more difficult issues and HA topics.
Gavin Soorma is an Oracle Certified Master with over 17 years of experience. He is also an Oracle Certified Professional (versions 7.3, 8i, 9i, 10g, and 11g) as well as an Oracle Certified Expert in 10g RAC.
He is a regular presenter at various Oracle conferences and seminars, having presented several papers at the IOUG, South African Oracle User's Group, Oracle Open World, and the Australian Oracle User Group. Recently, at the 2013 AUSOUG held in Melbourne and Perth, he presented a paper on Oracle GoldenGate titled "Real Time Access to Real Time Information".
He is currently employed as a Senior Principal Consultant for an Oracle solution provider, OnCall DBA based in Perth, Western Australia. Prior to this, he held the position of Senior Oracle DBA and Team Lead with Bank West in Perth. Before migrating to Australia, he worked for the Emirates Airline Group IT in Dubai for over 15 years where he held the position of Technical Team Manager, Databases.
He has also written a number of tutorials and notes on Oracle GoldenGate which can be accessed via his personal blog website http://gavinsoorma.com.
Michael Verzijl is a Business Intelligence Consultant, specializing in Oracle Business Intelligence, Oracle Data Warehousing and Oracle GoldenGate.
He has a wide range of experience in the financial, utilities, telecom, and government industries that include BI technologies such as Oracle, Informatica, IBM Cognos, and SAP Business Objects.
Currently he is employed as a BI Consultant for Accenture in the Netherlands, specializing in Business Intelligence and Data Warehousing.
www.PacktPub.com
Support files, eBooks, discount offers, and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at <[email protected]> for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
Preface
The availability, performance, and accessibility demands of the business IT systems are increasing day-by-day. The amount of data stored by the business has grown manifolds over the years. Companies want their management information systems to be up to date with the live data in order to analyze the latest trends in customer behavior. The data replication technologies that are used to replicate the data between the systems need to be robust, high-performing, resilient, and must have minimal impact on the production systems. Oracle GoldenGate is one of the key products in the data replication industry. Oracle recently declared shifting its focus on enhancing Oracle GoldenGate as its key data replication product. Ever since this announcement was made, there has been a lot of focus on Oracle GoldenGate and companies are using it for various purposes.
There is no dearth of the material explaining the architecture and concepts of Oracle GoldenGate replication. This cookbook is a practical guide, which provides you with the steps to perform various activities in a GoldenGate environment.
The book is designed to cover the GoldenGate tasks of various complexity levels. Whether you are looking to know the process of setting up a simple GoldenGate replication, implement it in a clustered environment or planning to just perform a one-time data migration from one database environment to another, you can follow the detailed steps in the recipes in this book for that.
The purpose of this book is to provide the reader with a ready, step-by-step approach to perform various GoldenGate Administration tasks. With these recipes in hand, you will easily be able to implement and manage Oracle GoldenGate in an efficient way.
What this book covers
Chapter 1, Installation and Initial Setup, introduces Oracle GoldenGate and covers the steps in installing GoldenGate binaries. It also goes through the steps in setting up a simple GoldenGate replication.
Chapter 2, Setting up GoldenGate Replication, explains the GoldenGate setup in a more complex environment and also goes through some of the options which one would use in a production environment.
Chapter 3, DDL Replication and Initial Load, goes through the various options that are available to instantiate a target environment and also goes through the steps that one needs to follow to set up DDL replication through Oracle GoldenGate.
Chapter 4, Mapping and Manipulating Data, describes various options that one can use for performing a variety of transformations in a GoldenGate replication. It also explains how to capture errors and how to perform various mappings between different table columns in the source and target environments.
Chapter 5, Oracle GoldenGate High Availability, covers various recipes to implement GoldenGate in high availability configurations. It also includes some failover scenarios that should be followed in those configurations.
Chapter 6, Monitoring, Tuning, and Troubleshooting GoldenGate, focuses on the management of GoldenGate environments. It contains the recipes to perform a health check, measure throughput, and monitor a GoldenGate environment. It also includes a few options to enhance the performance of GoldenGate replication.
Chapter 7, Advanced Administration Tasks – I, covers some advanced maintenance tasks such as patching and upgrading GoldenGate binaries that a GoldenGate administrator would need to do at some point. It also covers how to propagate table structure changes in GoldenGate environments. We also discuss some utilities that are available in GoldenGate binaries using which you can view the contents of the extracted records and also undo the applied changes.
Chapter 8, Advanced Administration Tasks – Part II, focuses on additional advanced tasks around the setup and migration of replication to GoldenGate. It explains a few options to set up replication from the production environment without impacting the performance or jeopardizing the risk of replicating erroneous transactions to the target environment. We also cover the process of migrating an Oracle Streams replication environment to Oracle GoldenGate. It also explains the process of replicating data from MS SQL Server environments to Oracle databases using GoldenGate.
Chapter 9, GoldenGate Veridata, Director, and Monitor, focuses on the installation and configuration of some additional tools that one can buy to manage the GoldenGate environments.
What you need for this book
In order to practice the recipes in this book, you would need various machines/virtual machines which should have at least the following configuration:
You would also need the following software to perform the setups in various recipes:
Who this book is for
Whether you are handling Oracle GoldenGate environments on a day-to-day basis or using it just for migration, this book provides the necessary information for most of the administration tasks.
The book is for Database Administrators, Architects, and Middleware Administrators who are keen to learn about various setups in Oracle GoldenGate. It also targets the Solution Architects who want to explore various high availability options in Oracle GoldenGate for Oracle RAC environments. The reader is expected to have some knowledge of Oracle databases.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "We can include other contexts through the use of the include directive."
A block of code is set as follows:
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
Any command-line input or output is written as follows:
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "clicking the Next button moves you to the next screen".
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the erratasubmissionform link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Chapter 1. Installation and Initial Setup
The following recipes will be covered in this chapter:
Introduction
Database replication is always an interesting challenge. It requires a complex setup and strong knowledge of the underlying infrastructure, databases, and the data held in them to replicate the data efficiently without much impact on the enterprise system. Oracle GoldenGate gains a lot of its popularity from the simplicity in its setup. In this chapter we will cover the basic steps to install GoldenGate and set up various processes.
Installing Oracle GoldenGate in a x86_64 Linux-based environment
This recipe will show you how to install Oracle GoldenGate in a x86_64 Linux-based environment.
Getting ready
In order to install Oracle GoldenGate, we must have downloaded the binaries from the Oracle Technology Network website for your Linux platform. We have downloaded Oracle GoldenGate Version 11.2.0.1.0.1 in this recipe. Ensure that you check the checksum of the file once you have downloaded it.
Tip
You can find the Oracle GoldenGate binaries for x86_64 Linux at http://www.oracle.com/technetwork/middleware/GoldenGate/downloads/index.html?ssSourceSiteId=ocomen.
How to do it...
Oracle GoldenGate binaries are installed in a directory called GoldenGate Home. This directory should be owned by the OS user (ggate) which will be the owner of GoldenGate binaries. This user must be a member of the dba group. After you have downloaded the binaries, you need to uncompress the media pack file by using the unzip utility as given in the following steps:
Note
You must have Oracle database libraries added to the shared library environment variable, $LD_LIBRARY_PATH before you run ggsci. It is also recommended to have $ORACLE_HOME & $ORACLE_SID set to the correct Oracle instance.
How it works...
Oracle provides GoldenGate binaries in a compressed format. In order to install the binaries you unzip the compressed file, and then expand the archive file into a required directory. This unpacks all the binaries. However, GoldenGate also requires some important subdirectories under GoldenGate Home which are not created by default. These directories are created using the CREATE SUBDIRS command. The following is the list of the subdirectories that get created with this command:
Subdirectory
Contents
dirprm
It contains parameter files
dirrpt
It contains report files
dirchk
It contains checkpoint files
dirpcs
It contains process status files
dirsql
It contains SQL scripts
dirdef
It contains database definitions
dirdat
It contains trail files
dirtmp
It contains temporary files
dirout
It contains output files
Note
Oracle GoldenGate binaries need to be installed on both the source and target systems. The procedure for installing the binaries is the same in both environments.
Installing Oracle GoldenGate in a Windows environment
In this recipe we will go through the steps that should be followed to install the GoldenGate binaries in the Windows environment.
Getting ready
In order to install Oracle GoldenGate, we must have downloaded the binaries from the Oracle Technology Network website for your Windows platform. We have downloaded GoldenGate Version 11.2.0.1.0.1 in this recipe. Ensure that you check the checksum of the file once you have downloaded it.
Tip
You can find the Oracle GoldenGate binaries for x86_64 Windows at http://www.oracle.com/technetwork/middleware/GoldenGate/downloads/index.html?ssSourceSiteId=ocomen.
How to do it...
Oracle GoldenGate binaries are installed in a directory called GoldenGate Home. After you have downloaded the binaries, you need to uncompress the media pack file by using the unzip utility:
How it works...
Oracle provides GoldenGate binaries in a compressed format. The installation involves unzipping the file into a required directory. This unpacks all the binaries. However, GoldenGate also requires some important subdirectories under GoldenGate Home which are not created by default. These directories are created using the CREATE SUBDIRS command. The following is the list of the subdirectories that get created with this command:
Subdirectory
Contents
dirprm
It contains parameter files
Dirrpt
It contains report files
Dirchk
It contains checkpoint files
dirpcs
It contains process status files
dirsql
It contains SQL scripts
dirdef
It contains database definitions
dirdat
It contains trail files
dirtmp
It contains temporary files
dirout
It contains output files
Enabling supplemental logging in the source database
Oracle GoldenGate replication can be used to continuously replicate the changes from the source database to the target database. GoldenGate mines the redo information generated in the source database to extract the changes. In order to update the correct rows in the target database, Oracle needs sufficient information to be able to identify them uniquely. Since it relies on the information extracted from the redo buffers, it requires extra information columns to be logged into the redo records generated in the source database. This is done by enabling supplemental logging in the source database. This recipe explains how to enable supplemental logging in the source database.
Getting ready
We must have a list of the tables that we want to replicate between two environments.
How to do it…
Oracle GoldenGate requires supplemental logging to be enabled at the database level and table level. Use the following steps to enable the required supplemental logging:
How it works…
Supplemental logging enables the database to add extra columns in the redo data that is required by GoldenGate to correctly identify the rows in the target database. We must enable database-level minimum supplemental logging before we can enable it at the table level. When we enable it at the table level, a supplemental log group is created for the table that consists of the columns on which supplemental logging is enabled. The columns which form a part of this group are decided based on the key constraints present on the table. These columns are decided based on the following priority order:
GoldenGate only considers unique keys which don't have any virtual columns, any user-defined types, or any function-based columns. We can also manually specify which columns we want to be a part of the supplemental log group.
Tip
You can enable supplemental logging on all tables of a schema using the following single command:
If possible, do create a primary key in each source and target table that is part of the replication. The pseudo key consisting of all columns, created by GoldenGate, can be quite inefficient.
There's more…
There are two ways to enable supplemental logging. The first method is to enable it using GGSCI, using the ADDTRANDATA command. The second method is to use sqlplus and run the ALTER TABLE ADD SUPPLEMENTAL LOG DATA command. The latter method is more flexible and allows a person to specify the name of the supplemental log group. However, when you use Oracle GoldenGate to add supplemental logging it creates supplemental log group names using the format, GGS_<TABLE_NAME>_<OBJECT_NUMBER>. If the overall supplemental log group name is longer than 30 characters, GoldenGate truncates the table name as required. Oracle support recommends that we use the first method for enabling supplemental logging for objects to be replicated using Oracle GoldenGate. The GGS_* supplemental log group format enables GoldenGate to quickly identify the supplemental log groups in the database.
If you are planning to use GoldenGate to capture all transactions in the source database and convert them into INSERT for the target database, for example, for reporting/auditing purposes, you'll need to enable supplemental logging on all columns of the source database tables.
See also
Supported datatypes in Oracle GoldenGate
Oracle GoldenGate has some restrictions in terms of what it can replicate. With every new release, Oracle is adding new datatypes to the list of what is supported. The list of the datatypes of the objects that you are planning to replicate should be checked against the list of supported datatypes for the GoldenGate version that you are planning to install.
Getting ready
You should have identified the various datatypes of the objects that you plan to replicate.
How to do it…
The following is a high-level list of the datatypes that are supported by Oracle GoldenGate v11.2.1.0.1:
How it works…
There are some additional details that one needs to consider while evaluating the supported datatypes for a GoldenGate version. For example, the user-defined datatypes are only supported if the source and target tables have the same structures. Both Classic and Integrated Capture modes support XML types which are stored as XML, CLOB, and XML binary. However, XML type tables stored as Object Relational are only supported in Integrated Capture mode.
There's more…
The support restrictions apply to a few other factors apart from the datatypes. Some of these are as Manipulating Data:
Preparing the source database for GoldenGate setup
Oracle GoldenGate architecture consists of Extract process in the source database. This process mines the redo information and extracts the changes occurring in the source database objects. These changes are then written to the trail files. There are two types of Extract processes – Classic Capture and Integrated Capture. The Extract process requires some setup to be done in the source database. Some of the steps in the setup are different depending on the type of the Extract process. GoldenGate requires a database user to be created in the source database and various privileges to be granted to this user. This recipe explains how to set up a source database for GoldenGate replication.
Getting ready
You must select a database user ID for the source database setup. For example, GGATE_ADMIN.
How to do it…
Run the following steps in the source database to set up the GoldenGate user as follows:
The following steps are only required for Integrated Capture Extract (Version 11.2.0.2 or higher):
The following steps are only required for Integrated Capture Extract (Version 11.2.0.1 or earlier):
Set up a TNS Entry for the source database in $ORACLE_HOME/network/admin/tnsnames.ora.
How it works…
The preceding commands can be used to set up the GoldenGate user in the source database. The Integrated Capture required some additional privileges as it needs to interact with the database log mining server.
You will notice that in the previous commands, we have granted SELECT ANY TABLE to the GGATE_ADMIN user. In production environments, where least required privileges policies are followed, it is quite unlikely that such a setup would be approved by the compliance team. In such cases, instead of granting this privilege, you can grant the SELECT privilege on individual tables that are a part of the source replication configuration. You can use dynamic SQL to generate such commands.
In our example schema database, we can generate the commands for all tables owned by the user SCOTT as follows:
There's more…
In this recipe we saw the steps required to set up a the GoldenGate user in the database. The Extract process required various privileges to be able to mine the changes from the redo data. At this stage it's worth discussing the two types of Extract processes and the differences between both.
The Classic Capture mode
The Classic Capture mode is the traditional Extract process that has been there for a while. In this mode, GoldenGate accesses the database redo logs (also, archive logs for older transactions) to capture the DML changes occurring on the objects specified in the configuration files. For this, at the OS level, the GoldenGate user must be a part of the same database group which owns the database redo logs. If the redo logs of the source database are stored in an ASM diskgroup this capture method reads it from there. This capture mode is available for other RDBMS as well. However, there are some datatypes that are not supported in Classic Capture mode. One of the biggest limitations of the Classic Capture mode is its inability to read data from the compressed tables/tablespaces.
The Integrated Capture mode
In case of the Integrated Capture mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs). An LCR is a message with a specific format that describes a database change. This mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC. This feature is only available for databases on Version 11.2.0.3 or higher. This Capture mode supports extracting data from source databases using compression. It also supports various object types which were previously not supported by Classic Capture.
Integrated Capture can be configured in an online or downstream mode. In the online mode, the log miner database is configured in the source database itself. In the downstream mode, the log miner database is configured in a separate database which receives archive logs from the source database. This mode offloads the log mining load from the source database and is quite suitable for very busy production databases. If you want to use the Integrated Capture mode with a source database Version 11.2.0.2 or earlier, you must configure the Integrated Capture mode in downstream capture topology, and the downstream mining database must be on Version 11.2.0.3 or higher.
Tip
You will need to apply a Bundle Patch specified in MOS Note 1411356.1 for full support of the datatypes offered by Integrated Capture.
See also
Preparing the target database for GoldenGate setup
On the target side of the GoldenGate architecture, the collector processes receive the trail files shipped by the Extract/Datapump processes from the source environment. The collector process receives these files and writes them locally on the target server. For each row that gets updated in the source database, the Extract process generates a record and writes it to the trail file. The Replicat process in the target environment reads these trail files and applies the changes to the target database using native SQL calls. To be able to apply these changes to the target tables, GoldenGate requires a database user to be set up in the target database with some privileges on the target objects. The Replicat process also needs to maintain its status in a table in the target database so that it can resume in case of any failures. This recipe explains the steps required to set up a GoldenGate user in the target database.
Getting ready
You must select a database user ID for a target database setup. For example, GGATE_ADMIN, because the GoldenGate user also requires a table in the target database to maintain its status. It needs some quota assigned on a tablespace to be able to create a table. You might want to create a separate tablespace, grant quota and assign it as default for the GGATE_ADMIN user. We will assign a GGATE_ADMIN_DAT tablespace to the GGATE_ADMIN user in this recipe.
How to do it…
Run the following steps in the target database to set up a GoldenGate user:
How it works…
You can use these commands to set up a GoldenGate user in the target database. The GoldenGate user in the target database requires access to the database plus update/insert/delete privileges on the target tables to apply the changes. In the preceding commands, we have granted SELECT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, and INSERT ANY TABLE privileges to the GGATE_ADMIN user. However, if for production database reasons your organization follows the least required privileges policy, you will need to grant these privileges on the replicated target tables individually. If the number of replicated target tables is large, you can use dynamic SQL to generate such commands. In our example demo database, we can generate these commands for the SCOTT schema objects as follows:
There's more…
The replicated changes are applied to the target database on a row-by-row basis. The Replicat process needs to maintain its status so that it can be resumed in case of failure. The checkpoints can be maintained in a database table or in a file on disk. The best practice is to create a Checkpoint table and use it to maintain the replicat status. This also enhances the performance as the replicat applies the changes to the database using asynchronous COMMIT with the NOWAIT option. If you do not use a Checkpoint table, the replicat maintains the checkpoint in a file and applies the changes to the databases using a synchronous COMMIT with the WAIT option.
