GGS_Fund_10.4_WinUXnullOracle® GoldenGate:
Fundamentals for Windows/UNIX
Oracle® GoldenGate:
Fundamentals for Windows/UNIXPresented by Ka Heng Jew
ka.heng.jew@oracle.comAgenda
AgendaDay 1Day 1Presentations
Oracle GoldenGate Overview
Company and Solutions
Products
Technology ...
nullOracle® GoldenGate:
Fundamentals for Windows/UNIX
Oracle® GoldenGate:
Fundamentals for Windows/UNIXPresented by Ka Heng Jew
ka.heng.jew@oracle.comAgenda
AgendaDay 1Day 1Presentations
Oracle GoldenGate Overview
Company and Solutions
Products
Technology Overview
Oracle GoldenGate Architecture
Configuring Oracle GoldenGate:
Prepare the Environment
Change Capture
Initial Load
Change Delivery
Student LabsDay 2Day 2Presentations
Data Pumps
Extract Trails and Files
Parameters
Data Mapping and Transformation
Student LabsDay 3Day 3Presentations
Data Mapping and Transformation (continued)
Configuration Options
Managing Oracle GoldenGate
Student Labs
Questions and Answers
About Oracle® GoldenGateAbout Oracle® GoldenGateOur BusinessOur BusinessWe enable real-time, continuous movement of transactional data across Operational and Analytical business systems.
Real-Time Access
to
Real-Time InformationOracle GoldenGate SuccessOracle GoldenGate SuccessTransactional Data Management (TDM)Transactional Data Management (TDM)nullOracle GoldenGate SolutionsOracle GoldenGate SolutionsHigh Availability & Disaster Tolerance
Live Standby
Active-Active
Zero-Downtime Operations for:
Upgrades
Migrations
Maintenance
Real-Time Data Integration
Real-Time Data Warehousing
Live Reporting
Transactional Data Integration
Oracle GoldenGate for High Availability & Disaster ToleranceOracle GoldenGate for High Availability & Disaster ToleranceHigh Availability & Disaster Tolerance
Live Standby
Active-Active
Zero-Downtime Operations for:
Upgrades
Migrations
Maintenance
High Availability: Live Standby High Availability: Live StandbyBenefits:
Eliminate unplanned downtime
Reduce data loss and isolate corrupt data
Re-synchronize backup and primary systems
Remove distance constraints
Automate switchovers
Improve ROI with active standby available for reportingHigh Availability: Active-ActiveHigh Availability: Active-ActiveBenefits:
Achieve continuous availability
Enable transaction load distribution (with built-in conflict resolution)
Improve performance
Lower TCOZero-Downtime Upgrades and MigrationsZero-Downtime Upgrades and MigrationsBenefits:
Eliminate “planned downtime” during hardware, database, OS and/or application upgrades and migrations
Minimize risk with fail-back contingency
Improve success with phased user migrationsOracle GoldenGate for Real-Time Data IntegrationOracle GoldenGate for Real-Time Data IntegrationReal-Time Data Integration
Live Reporting
Operational Business Intelligence
Transactional Data Integration
Data Integration: Live ReportingData Integration: Live ReportingBenefits:
Use real-time data for better, faster decision making
Remove reporting overhead on source system
Reduce cost-to-scale as user demands and data volumes grow
Leverage cost-effective systems for reporting needsOperational Business Intelligence* Operational Business IntelligenceBenefits:
Use real-time data for better, faster decision making
Eliminate batch window dependency
Reduce overhead on source system
Maintain referential integrity for data quality
Leverage its flexibility for transformations and integration with ETLTransactional Data Integration* Transactional Data IntegrationBenefits:
Easily integrate large volumes of real-time data between transaction processing systems
Reduce overhead; Eliminate batch windows
Improve scalability
Enhance SOA and EDA environments (delivery to JMS-based messaging systems)Oracle® GoldenGate
Technology OverviewOracle® GoldenGate
Technology OverviewHow Oracle GoldenGate Works: Modular “Building Blocks”How Oracle GoldenGate Works: Modular “Building Blocks”Source
Database(s) Target
Database(s) Oracle GoldenGate Supports Applications Running On…Oracle GoldenGate Supports Applications Running On…Oracle GoldenGate AdvantagesOracle GoldenGate AdvantagesOracle GoldenGate DirectorOracle GoldenGate DirectorManages, defines, configures, and reports on Oracle GoldenGate components
Key features:
Centralized management of Oracle GoldenGate modules
Rich-client and Web-based interfaces
Alert notifications and integration with 3rd-party monitoring products
Real-time feedback
Zero-impact implementationOracle GoldenGate VeridataOracle GoldenGate VeridataA high-speed, low impact data comparison solution
Identifies and reports data discrepancies between two databases without interrupting those systems or the business processes they support
Supports Oracle, Teradata, SQL Server, NonStop SQL/MP and Enscribe
Supports homogeneous and heterogeneous compares
Benefits:
Reduce financial/legal risk exposure
Speed and simplify IT work in comparing data sources
No disruption to business systems
Improved failover to backup systems
Confident decision-making and reporting
Oracle® GoldenGate Architecture
Oracle® GoldenGate ArchitectureOracle GoldenGate Data Capture and DeliveryOracle GoldenGate Data Capture and DeliveryOracle GoldenGate Transactional Data Management:
Primarily used for change data capture and delivery from database transaction logs
Can optionally be used for initial load directly from database tables
Especially useful for synchronizing heterogeneous databases
Database-specific methods may be preferable for homogeneous configurationsnullChange Data Capture & DeliverynullChange Data Capture & Delivery using a Data PumpnullInitial LoadExtract
Replicat
Source
Database
TablesTarget
DatabaseNetwork
(TCP/IP)GoldenGate initial load methods:
Direct Load (Extract sends data directly to Replicat to apply using SQL)
Direct Bulk Load (Replicat uses Oracle SQL*Loader API)File to Replicat (Extract writes to a file that Replicat applies using SQL)
File to database utility (Extract writes to a file formatted for a DB bulk load utility)Online versus BatchOnline versus BatchChange data capture & delivery can be run either continuously (online) or as a special run (batch run) to capture changes for a specific period of time.
Initial load is always a special run (batch run).
Checkpointing - ExtractCheckpointing - ExtractFor change data capture, Extract and Replicat save checkpoints to a checkpoint file so they can recover in case of failure
Extract maintains:
2 input checkpoints
1 output checkpoint for each trail it writes toCheckpointing - ReplicatCheckpointing - ReplicatBest practice is to create a checkpoint table in the target database
Checkpoints are maintained in both the checkpoint table (if it exists) and a checkpoint file
Replicat maintains 2 input checkpoints:Parameters, Process Groups and CommandsParameters, Process Groups and CommandsGoldenGate processes are configured by ASCII parameter files.
A process group consists of:
An Extract or Replicat process
Associated parameter file
Associated checkpoint file
Any other files associated with that process
Each process group on a system must have a unique group name.
Processes are added and started using the GoldenGate Software Command Interface (GGSCI) with the group name.
GGSCI commands also add trails, check process status, etc.
Solutions and Architecture – Discussion PointsSolutions and Architecture – Discussion PointsHow is Oracle GoldenGate different from simply replicating database operations?
What are some use cases for Oracle GoldenGate software?
What is the purpose of checkpointing?Configuring Oracle® GoldenGate*Configuring Oracle® GoldenGateConfiguring Oracle GoldenGate Configuring Oracle GoldenGate Oracle GoldenGate can be deployed quickly and easily in four steps:
Prepare the Environment
Change Capture
Initial Load
Change DeliveryConfiguring Oracle GoldenGateConfiguring Oracle GoldenGate
1. Prepare the EnvironmentConfiguring Oracle GoldenGate:
Step 1. Prepare the Environment*Configuring Oracle GoldenGate:
Step 1. Prepare the EnvironmentStep 1. Prepare the EnvironmentStep 1. Prepare the Environment
Step 1. Prepare the EnvironmentStep 1. Prepare the EnvironmentSet up each system:
Install Oracle GoldenGate software on source and target
Configure and start GoldenGate Manager on source and target
If heterogeneous source/target, generate source definitions and copy to target
Prepare the database. For example:
Ensure database access by GoldenGate
Enable transaction loggingPrepare the Environment:
Install Oracle GoldenGate SoftwarePrepare the Environment:
Install Oracle GoldenGate SoftwarePrepare Environment: Installation – Access the Media PackPrepare Environment: Installation – Access the Media PackAccess the product media pack (software and documentation) at edelivery.oracle.com
Identify the proper release of GoldenGate for your source and target environments
Database and version
Operating system and versionPrepare Environment: Installation - WindowsPrepare Environment: Installation - Windows Download .zip file to C:\GGS
Unzip .zip file into C:\GGS folder
Configure a Windows Service Name for Manager process in a GLOBALS parameter file (required only if multiple Managers on the server)
C:\GGS> INSTALL ADDSERVICE ADDEVENTS
GGSCI> CREATE SUBDIRSPrepare Environment: Installation – Windows INSTALL ProgramPrepare Environment: Installation – Windows INSTALL ProgramOn Windows, an INSTALL program performs the following functions:
Installs GoldenGate event messages into the system registry
Installs the Manager as a Windows service
Syntax:
INSTALL [ …]
Example:
C:\GGS> INSTALL ADDEVENTS ADDSERVICE
Note: The uninstall command is:
INSTALL DELETESERVICE DELETEEVENTS
Prepare Environment: Installation - Multiple Manager ServicesPrepare Environment: Installation - Multiple Manager ServicesGoldenGate supports running multiple Manager services on Windows
For two or more GoldenGate instances, or
GoldenGate with a Veridata C Agent (which uses a Manager)
Each Manager service must be assigned a unique name
Before installing the service, you can specify the name
Create a GLOBALS parameter file for each Manager
Specify the one-word name of the service using the MGRSERVNAME parameter
INSTALL ADDSERVICE
Reads the GLOBALS MGRSERVNAME for the service name
If no GLOBALS setting, uses default service name GGSMGR
Prepare Environment: Installation - UNIX, Linux or z/OSPrepare Environment: Installation - UNIX, Linux or z/OS Download .gz file to /ggs
gzip –d {filename}.tar.gz
tar -xvof {filename}.tar
GGSCI> CREATE SUBDIRSPrepare Environment: Installation – NonStop SQL/MXPrepare Environment: Installation – NonStop SQL/MXFor a SQL/MX source, install Oracle GoldenGate on OSS running on the NonStop source system:
Download .gz file to /ggs
gzip –d {filename}.tar.gz
tar -xvf {filename}.tar
GGSCI> CREATE SUBDIRS
Run the ggmxinstall script
For a SQL/MX target, install Oracle GoldenGate
Either on OSS running on the NonStop target system (as described above)
Or on an intermediate Windows system(as described earlier)Prepare Environment: Installation - GoldenGate DirectoriesPrepare Environment: Installation - GoldenGate DirectoriesLab Exercise
Installation on Windows and/or UNIX Lab Exercise
Installation on Windows and/or UNIX Prepare Environment: Oracle GoldenGate DocumentationPrepare Environment: Oracle GoldenGate DocumentationQuick Install Guide
Installation and Setup Guides (by database)
Administration Guide
Reference Guide
Troubleshooting and Tuning Guide
Note: You can download the documentation from http://www.oracle.com/technology/documentation/index.html
Workshop
Using GoldenGate Documentation Workshop
Using GoldenGate Documentation Prepare the Environment:
Configure and Start ManagerPrepare the Environment:
Configure and Start ManagerPrepare Environment: Manager - OverviewPrepare Environment: Manager - OverviewPerforms system management and monitoring tasks
Starting GoldenGate processes
Starting dynamic Server Collector, Replicat, or GGSCI processes
Error and lag reporting
GoldenGate trail management
Parameter file
mgr.prm file in GGS ./dirprm directory
Event information written to ggserr.log filePrepare Environment: Manager - ConfigurationPrepare Environment: Manager - ConfigurationCreate the parameter file using GGSCI
GGSCI> EDIT PARAM MGR
Start the Manager using GGSCI
GGSCI> START MGR
Note: To determine which port Manager is using
GGSCI> INFO MGRPrepare Environment: Manager – Sample MGR Parameter FilePrepare Environment: Manager – Sample MGR Parameter FilePORT 7809
DYNAMICPORTLIST 8001, 8002, 9500–9520
PURGEOLDEXTRACTS /ggs/dirdat/aa*, USECHECKPOINTS
PURGEOLDEXTRACTS /ggs/dirdat/bb*, & USECHECKPOINTS, MINKEEPDAYS 5
AUTOSTART ER *
AUTORESTART EXTRACT *, WAITMINUTES 2, RETRIES 5
LAGREPORTHOURS 1
LAGINFOMINUTES 3
LAGCRITICALMINUTES 5
Prepare the Environment:
Generate Source Definitions (Heterogeneous Source/Target)
Prepare the Environment:
Generate Source Definitions (Heterogeneous Source/Target)Prepare Environment: Source Definitions - OverviewPrepare Environment: Source Definitions - OverviewThe problem
Understanding source and target layouts across disparate systems and databases
The solution – the DEFGEN utility program
DEFGEN produces a file containing layout definitions of the source files and tables
This source definition file is used to interpret layouts for data stored in GoldenGate trails
At start up Replicat reads the definition file specified with the SOURCEDEFS parameter
Server Collector uses the –d argument to specify which definition file to read at startup
Can also capture target definitions on target system and copy to source system for Extract to usePrepare Environment: Source Definitions – Run DEFGENPrepare Environment: Source Definitions – Run DEFGENDEFGEN is initiated from the command prompt:
defgen paramfile [ reportfile ]
Unix Example:
defgen paramfile /ggs/dirprm/defgen.prm reportfile /ggs/dirrpt/defgen.rpt
Windows Example:
defgen paramfile c:\ggs\dirprm\defgen.prm reportfile c:\ggs\dirrpt\defgen.rpt
Definitions are saved to the file specified in the parameter file
This file needs to be transferred to the target system as a text filePrepare Environment: Source Definitions - Sample DEFGEN ParametersPrepare Environment: Source Definitions - Sample DEFGEN ParametersDEFSFILE /ggs/dirdef/source.def, PURGE
SOURCEDB mydb, USERID ggs, PASSWORD ggs
TABLE SALES.ACCOUNT;
TABLE SALES.PRODUCT;
Prepare the Environment:
Prepare Source Database
Prepare the Environment:
Prepare Source Database
Prepare Environment: Source Database – OverviewPrepare Environment: Source Database – OverviewSet up the database to:
Ensure access by GoldenGate
Enable transaction logging
Note: the exact steps depend on the databasePrepare Environment: Source DatabasePrepare Environment: Source DatabaseOracle
Add minimal supplemental logging at database level
ADD TRANDATA to mark tables for replication
DB2
Enter DATA CAPTURE CHANGES at the column for LOB data type
ADD TRANDATA to mark tables for replication
Sybase
Set the secondary truncation point in the logs
ADD TRANDATA to mark tables for replication
NonStop SQL/MX
Special installation steps but no special database preparationPrepare Environment: Source Database – SQL ServerPrepare Environment: Source Database – SQL ServerTo prepare the SQL Server source environment for GoldenGate:
Create the ODBC data source
GoldenGate connects to a SQL Server database through an ODBC connection
Extract and Replicat require an established data source name (dsn)
Set up transaction logging
Log truncation and non-logged bulk copy must be turned off
The SQL Server database must be set to full recovery mode
Before GoldenGate processes are started, at least one full database backup must be done
ADD TRANDATA to mark tables for replicationPrepare Environment: Source Database – SQL Server 2005Prepare Environment: Source Database – SQL Server 2005Additional considerations for SQL Server 2005 database:
Either install Microsoft Cumulative Update package 6 for SQL Server 2005 Service Pack 2 (or later)
Set TRANLOGOPTIONS to MANAGESECONDARYTRUNCATIONPOINT
Or install SQL Server replication components
Create a distribution database
Add a replication publication
Set transaction retention to zero
Disable replication alerts
Log full before and after images (no compressed)
Set TRANLOGOPTIONS to NOMANAGESECONDARYTRUNCATIONPOINTLab Exercise
Prepare the EnvironmentLab Exercise
Prepare the EnvironmentPrepare Environment – Discussion PointsPrepare Environment – Discussion PointsWhere do you download Oracle GoldenGate software from?
What are the roles and responsibilities of the Manager process?Configuring Oracle GoldenGate:
GoldenGate Command Interface
Configuring Oracle GoldenGate:
GoldenGate Command InterfaceGGSCI – Starting and HelpGGSCI – Starting and HelpStart the command interface from the GoldenGate install directory:
Shell> cd
Shell> GGSCI
For Help Summary page:
GGSCI> HELP
For Help on a specific command:
GGSCI> HELP