Goldengate basics and Bounce
Goldengate basics, Components, and Bounce:
---------------------------------------------------------
GoldenGate enables us to extract and replicate data across a variety of topologies as shown
the diagram below as well as the
exchange and manipulation of data at the transactional level between a variety of database
platforms like Oracle, DB2, SQL Server, Ingres, MySQL etc.
It can support a number of different business requirements like:
Business Continuity and High Availablity
Data migrations and upgrades
Decision Support Systems and Data Warehousing
Data integration and consolidation
--
Let us know look at the differents components and processes that make up a typical GoldenGate
configuration on Oracle.
Manager
The Manager process must be running on both the source as well as target systems before the
Extract or Replicat process can be started and performs a number of functions including
monitoring and starting other GoldenGate processes, managing the trail files and also reporting.
Extract
The Extract process runs on the source system and is the data caoture mechanism of GoldenGate.
It can be configured both for initial loading of the source data as well as to synchronize
the changed data on the source with the target.
This can be configued to also propagate any DDL changes on those databases where
DDL change support is available.
Replicat
The Replicat process runs on the target system and reads transactional data changes as well
as DDL changes
and replicates then to the target database. Like the Extract process, the Replicat process
can also be configured for Initial Load as well as Change Synchronization.
Collector
The Collector is a background process which runs on the target system and is started
automatically by the Manager (Dynamic Collector)
or it can be configured to stsrt manually (Static Collector).
It receives extracted data changes that are sent via TCP/IP and writes then to the
trail files from where they are processed by the Replicat process.
Trails
Trails are series of files that GoldenGate temporarily stores on disks and these files are
written to and read from by the Extract and Replicat processes as the case may be.
Depending on the configuration chosen, these trail files can exist on the source
as well as on the target systems. If it exists on the local system,
it will be known an Extract Trail or as an Remote Trail if it exists on the target system.
Data Pumps
Data Pumps are secondary extract mechanisms which exist in the source configuration.
This is optional component and if Data Pump is not used then Extract
sends data via TCP/IP to the remote trail on the target. When Data Pump is configured,
the the Primary Extract process will write to the Local Trail and then this trail is read
by the Data Pump and data is sent over
the network to Remote Trails on the target system.
In the absence of Data Pump, the data that the Extract process extracts resides in memory
alone and there is no storage of this data anywhere on the source system.
In case of network of target failures, there could be cases where the primary extract process
can abort or abend.
Data Pump can also be useful in those cases where we are doing complex filtering and
transformation of data as well as when we are consolidating data from many sources
to a central target.
Data source
When processing transactional data changes, the Extract process can obtain data directly
from the database transaction logs
(Oracle, DB2, SQL Server, MySQL etc) or from a GoldenGate Vendor Access Module (VAM) where
the database vendor (for example Teradata) will provide the required components that will
be used by Extract to extract the data changes.
Groups
To differentiate between the number of different Extract and Replicat groups which can
potentially co-exist on a system, we can define processing groups.
For instance, if we want to replicate different sets of data in parallel, we can create
two Replicat groups.
A processing group consisits of a process which could be either a Extract or Replicat process,
a corresponding parameter file, checkpoint file or checkpoint table (for Replicat)
and other files which could be associated with the process.
--------
==================================================================================
GoldenGate Tutorial 2 – Installation (Oracle 11g on Linux)
This example will illustrate the installation of Oracle GoldenGate on an RHEL 5 platform.
We had in an earlier post discussed the architecture and various components of a GoldenGate environment.
GoldenGate software is also available on OTN but for our platform we need to download the required software from the Oracle E-Delivery web site.
Select the Product Pack “Oracle Fusion Middleware” and the platform Linux X86-64.
Then select “Oracle GoldenGate on Oracle Media Pack for Linux x86-64? and
since we are installing this for an Oracle 11g database, we download “Oracle GoldenGate V10.4.0.x for Oracle 11g 64bit on RedHat 5.0?
$ unzip V18159-01.zip
Archive: V18159-01.zip
inflating: ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar
$tar -xvof ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar
$ export PATH=$PATH:/u01/oracle/ggs
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/oracle/ggs
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (redhat346.localdomain) 1>
GGSCI (redhat346.localdomain) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/oracle/product/11.2.0/dbhome_1
Parameter files /u01/oracle/ggs/dirprm: created
Report files /u01/oracle/ggs/dirrpt: created
Checkpoint files /u01/oracle/ggs/dirchk: created
Process status files /u01/oracle/ggs/dirpcs: created
SQL script files /u01/oracle/ggs/dirsql: created
Database definitions files /u01/oracle/ggs/dirdef: created
Extract data files /u01/oracle/ggs/dirdat: created
Temporary files /u01/oracle/ggs/dirtmp: created
Veridata files /u01/oracle/ggs/dirver: created
Veridata Lock files /u01/oracle/ggs/dirver/lock: created
Veridata Out-Of-Sync files /u01/oracle/ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/oracle/ggs/dirver/oosxml: created
Veridata Parameter files /u01/oracle/ggs/dirver/params: created
Veridata Report files /u01/oracle/ggs/dirver/report: created
Veridata Status files /u01/oracle/ggs/dirver/status: created
Veridata Trace files /u01/oracle/ggs/dirver/trace: created
Stdout files /u01/oracle/ggs/dirout: created
We then need to create a database user which will be used by the GoldenGate Manager, Extract
and Replicat processes.
We can create individual users for each process or configure just a common user – in our
case we will create the one user GGS_OWNER and
grant it the required privileges.
SQL> create tablespace ggs_data
2 datafile ‘/u02/oradata/gavin/ggs_data01.dbf’ size 200m;
SQL> create user ggs_owner identified by ggs_owner
2 default tablespace ggs_data
3 temporary tablespace temp;
User created.
SQL> grant connect,resource to ggs_owner;
Grant succeeded.
SQL> grant select any dictionary, select any table to ggs_owner;
Grant succeeded.
SQL> grant create table to ggs_owner;
Grant succeeded.
SQL> grant flashback any table to ggs_owner;
Grant succeeded.
SQL> grant execute on dbms_flashback to ggs_owner;
Grant succeeded.
SQL> grant execute on utl_file to ggs_owner;
Grant succeeded.
We can then confirm that the GoldenGate user we have just created is able to connect to the Oracle database
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
AIX 5L, ppc, 64bit (optimized), Oracle 11 on Sep 17 2009 23:54:16
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (devu007) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
We also need to enable supplemental logging at the database level otherwise we will get this error when we try to start the Extract process -
2010-02-08 13:51:21 GGS ERROR 190 No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key
column is not in first row piece.
2010-02-08 13:51:21 GGS ERROR 190 PROCESS ABENDING.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Database altered
-------------------------
====================================
GoldenGate Tutorial 3 – Configuring the Manager process
The Oracle GoldenGate Manager performs a number of functions like starting the other GoldenGate processes,
trail log file management and reporting.
The Manager process needs to be configured on both the source as well as target systems
and configuration is carried out via a parameter file just as in the case of the other GoldenGate processes like Extract and Replicat.
After installation of the software, we launch the GoldenGate Software Command Interface (GGSCI) and issue the following command to edit the Manager parameter file
EDIT PARAMS MGR
The only mandatory parameter that we need to specify is the PORT which defines the port on the local system where the manager process is running.
The default port is 7809 and we can either specify the default port or some other port provided the port is available and not restricted in any way.
Some other recommended optional parameters are AUTOSTART which which automatically start the Extract and Replicat processes when the Manager starts.
The USERID and PASSWORD parameter and required if you enable GoldenGate DDL support and this is the Oracle user account that we created for the Manager(and Extract/Replicat)
as described in the earlier tutorial.
The Manager process can also clean up trail files from disk when GoldenGate has finished processing them via the PURGEOLDEXTRACTS parameter.
Used with the USECHECKPOINTS clause, it will ensure that until all processes have fnished using the data contained in the trail files, they will not be deleted.
The following is an example of a manager parameter file
[oracle@redhat346 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI 2> EDIT PARAMS MGR
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /u01/oracle/ggs/dirdat/ex, USECHECKPOINTS
The manager can be stopped and started via the GSSCI commands START MANAGER and STOP MANAGER .
Information on the status of the Manager can be obtained via the INFO MANAGER command
GGSCI (devu007) 4> info manager
Manager is running (IP port devu007.7809).
-------------------------------------------
Oracle GoldenGate Tutorial 4 – performing initial data load
This example illustrates using the GoldenGate direct load method to extract records from an Oracle 11g database on Red Hat Linux platform and load the same into an Oracle 11g target database on an AIX platform.
The table PRODUCTS in the SH schema on the source has 72 rows and on the target database the same table is present only in structure without any data. We will be loading the 72 rows in this example from the source database to the target database using GoldenGate Direct Load method.
On Source
1) Create the Initial data extract process ‘load1'
GGSCI (redhat346.localdomain) 5> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.
Since this is a one time data extract task, the source of the data is not the transaction log files of the RDBMS (in this case the online and archive redo log files) but the table data itself, that is why the keyword SOURCEISTABLE is used.
2) Create the parameter file for the extract group load1
EXTRACT: name of the extract group
USERID/PASSWORD: the database user which has been configured earlier for Extract ( this user is created in the source database)
RMTHOST: This will be the IP address or hostname of the target system
MGRPORT: the port where the Manager process is running
TABLE: specify the table which is being extracted and replicated. This can be specified in a number of ways using wildcard characters to include or exclude tables as well as entire schemas.
GGSCI (redhat346.localdomain) 6> EDIT PARAMS load1
EXTRACT load1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE sh.products;
On Target
3) Create the initial data load task ‘load2'
Since this is a one time data load task, we are using the keyword SPECIALRUN
GGSCI (devu007) 1> ADD REPLICAT load2, SPECIALRUN
REPLICAT added.
4) Create the parameter file for the Replicat group, load2
REPLICAT: name of the Replicat group created for the initial data load
USERID/PASSWORD: database credentials for the Replicat user (this user is created in the target database)
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
MAP: with GoldenGate we can have the target database structure entirely differ from that of the source in terms of table names as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which is same in this case
GGSCI (devu007) 2> EDIT PARAMS load2
“/u01/oracle/software/goldengate/dirprm/rep4.prm” [New file]
REPLICAT load2
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP sh.customers, TARGET sh.customers;
On Source
SQL> select count(*) from products;
COUNT(*)
———-
72
On Target
SQL> select count(*) from products;
COUNT(*)
———-
0
On Source
5) Start the initial load data extract task on the source system
We now start the initial data load task load 1 on the source. Since this is a one time task, we will initially see that the extract process is runningand after the data load is complete it will be stopped. We do not have to manually start the Replicat process on the target as that is done when the Extract task is started on the source system.
On Source
GGSCI (redhat346.localdomain) 16> START EXTRACT load1
Sending START request to MANAGER …
EXTRACT LOAD1 starting
GGSCI (redhat346.localdomain) 28> info extract load1
EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE
GGSCI (redhat346.localdomain) 29> info extract load1
EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE
On Target
SQL> select count(*) from products;
COUNT(*)
———-
72
-------------------------------------------------------
------------------------------------------------------
configuring online change synchronization
In our earlier tutorial, we examined how to create a GoldenGate environment for initial data capture and load.
In this tutorial, we will see how by using GoldenGate change synchronization, changes that occur on the source (Oracle 11g on Linux) are applied near
real time on the target (Oracle 11g on AIX). The table on the source is the EMP table in SCOTT schema which is being replicated to the EMP table in the target database SH schema.
These are the steps that we will take:
Create a GoldenGate Checkpoint table
Create an Extract group
Create a parameter file for the online Extract group
Create a Trail
Create a Replicat group
Create a parameter file for the online Replicat group
Create the GoldenGate Checkpoint table
GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat process will start processing after any kind of error or shutdown.
This ensures data integrity and a record of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.
We can also create a single Checkpoint table which can used by all Replicat groups from the single or many GoldenGate instances.
In one of the earlier tutorials we had created the GLOBALS file. We now need to edit that GLOBALS file and add an entry for CHECKPOINTTABLE which will include the checkpoint table name which will be available to all Replicat processes via the EDIT PARAMS command.
GGSCI (devu007) 2> EDIT PARAMS ./GLOBALS
GGSCHEMA GGS_OWNER
CHECKPOINTTABLE GGS_OWNER.CHKPTAB
GGSCI (devu007) 4> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
GGSCI (devu007) 6> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB
Successfully created checkpoint table GGS_OWNER.CHKPTAB.
apex:/u01/oracle/software/goldengate> sqlplus ggs_owner/ggs_owner
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 8 09:02:19 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc chkptab
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
Create the Online Extract Group
GGSCI (redhat346.localdomain) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.
Create the Trail
We now create a trail – note that this path pertains to the GoldenGate software location on the target system and this is where the trail files will be created having a prefix ‘rt’ which will be used by the Replicat process also running on the target system
GGSCI (redhat346.localdomain) 2> ADD RMTTRAIL /u01/oracle/software/goldengate/dirdat/rt, EXTRACT ext1
RMTTRAIL added.
Create a parameter file for the online Extract group ext1
GGSCI (redhat346.localdomain) 3> EDIT PARAMS ext1
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTRAIL /u01/oracle/software/goldengate/dirdat/rt
TABLE scott.emp;
ON TARGET SYSTEM
Create the online Replicat group
GGSCI (devu007) 7> ADD REPLICAT rep1, EXTTRAIL /u01/oracle/software/goldengate/dirdat/rt
REPLICAT added.
Note that the EXTTRAIL location which is on the target local system conforms to the RMTTRAIL parameter which we used when we created the parameter file for the extract process on the source system.
Create a parameter file for the online Replicat group, rep1
GGSCI (devu007) 8> EDIT PARAMS rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.emp, TARGET sh.emp;
ON SOURCE
Start the Extract process
GGSCI (redhat346.localdomain) 16> START EXTRACT ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (redhat346.localdomain) 17> STATUS EXTRACT ext1
EXTRACT EXT1: RUNNING
GGSCI (redhat346.localdomain) 16> INFO EXTRACT ext1
EXTRACT EXT1 Last Started 2010-02-08 14:27 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-08 14:27:48 Seqno 145, RBA 724480
ON TARGET
Start the Replicat process
GGSCI (devu007) 1> START REPLICAT rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (devu007) 2> INFO REPLICAT rep1
REPLICAT REP1 Last Started 2010-02-08 14:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/rt000001
2010-02-08 14:27:57.600425 RBA 1045
Note: the trail file has a prefix of ‘rt’ (which we had defined earlier)
LET US NOW TEST …
ON SOURCE
SQL> conn scott/tiger
Connected.
SQL> UPDATE emp SET sal=9999 WHERE ename=’KING’;
1 row updated.
SQL> COMMIT;
Commit complete.
ON TARGET
SQL> SELECT SAL FROM emp WHERE ename=’KING’;
SAL
———-
9999
--------------------------------------
-------------------------------------------
Oracle GoldenGate Tutorial 6 – configuring Data Pump process
The Data Pump (not to be confused with the Oracle Export Import Data Pump) is an optional secondary Extract group
that is created on the source system. When Data Pump is not used, the Extract process writes to a remote trail that is located on the target
system using TCP/IP. When Data Pump is configured, the Extract process writes to a local trail and from here Data Pump will read the trail and write the data over the network
to the remote trail located on the target system.
The advantages of this can be seen as it protects against a network failure as in the absence of a storage device on the local system,
the Extract process writes data into memory before the same is sent over the network. Any failures in the network could then cause the Extract process to abort (abend).
Also if we are doing any complex data transformation or filtering, the same can be performed by the Data Pump.
It will also be useful when we are consolidating data from several sources into one central target where data pump on each individual source system can write to one common trail file on the target.
Create the Extract process
GGSCI (devu007) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.
Create a local trail
Using the ADD EXTRAIL command we will now create a local trail on the source system where the Extract process will write to and which is then read by the Data Pump process. We will link this local trail to the Primary Extract group we just created, ext1
GGSCI (devu007) 3> ADD EXTTRAIL /u01/oracle/software/goldengate/dirdat/lt, EXTRACT ext1
EXTTRAIL added.
Create the Data Pump group
On the source system create the Data Pump group and using the EXTTRAILSOURCE keywork specify the location of the local trail which will be read by the Data Pump process
GGSCI (devu007) 4> ADD EXTRACT dpump, EXTTRAILSOURCE /u01/oracle/software/goldengate/dirdat/lt
EXTRACT added.
Create the parameter file for the Primary Extract group
GGSCI (devu007) 5> EDIT PARAMS ext1
“/u01/oracle/software/goldengate/dirprm/ext1.prm” [New file]
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
EXTTRAIL /u01/oracle/software/goldengate/dirdat/lt
TABLE MONITOR.WORK_PLAN;
Specify the location of the remote trail on the target system
Use the RMTTRAIL to specify the location of the remote trail and associate the same with the Data Pump group as it will be wriiten to over the network by the data pump process
GGSCI (devu007) 6> ADD RMTTRAIL /u01/oracle/ggs/dirdat/rt, EXTRACT dpump
RMTTRAIL added.
Create the parameter file for the Data Pump group
Note- the parameter PASSTHRU signifies the mode being used for the Data Pump which means that the names of the source and target objects are identical and no column mapping or filtering is being performed here.
GGSCI (devu007) 2> EDIT PARAMS dpump
“/u01/oracle/software/goldengate/dirprm/dpump.prm” [New file]
EXTRACT dpump
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST redhat346, MGRPORT 7809
RMTTRAIL /u01/oracle/ggs/dirdat/rt
PASSTHRU
TABLE MONITOR.WORK_PLAN;
ON TARGET SYSTEM
Create the Replicat group
The EXTTRAIL clause indicates the location of the remote trail and should be the same as the RMTTRAIL value that was used when creating the Data Pump process on the source system.
GGSCI (redhat346.localdomain) 2> ADD REPLICAT rep1, EXTTRAIL /u01/oracle/ggs/dirdat/rt
REPLICAT added.
Create the parameter file for the Replicat group
GGSCI (redhat346.localdomain) 3> EDIT PARAMS rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP MONITOR.WORK_PLAN, TARGET MONITOR.WORK_PLAN;
Bounce:
========
ON SOURCE
===========
On the source system, now start the Extract and Data Pump processes.
GGSCI (devu007) 3> START EXTRACT ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
=
GGSCI (devu007) 4> START EXTRACT dpump
Sending START request to MANAGER …
EXTRACT DPUMP starting
GGSCI (devu007) 5> info extract ext1
EXTRACT EXT1 Last Started 2010-02-18 11:23 Status RUNNING
Checkpoint Lag 00:40:52 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-18 10:42:19 Seqno 761, RBA 15086096
GGSCI (devu007) 6> INFO EXTRACT dpump
EXTRACT DPUMP Last Started 2010-02-18 11:23 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/lt000000
2010-02-18 11:15:10.000000 RBA 5403
Note- the data pump process is reading from the Local Trail file – /u01/oracle/software/goldengate/dirdat/lt000000
ON TARGET SYSTEM
Start the Replicat process
GGSCI (redhat346.localdomain) 4> START REPLICAT rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
================================
Monitoring GoldenGate
============================
The following tutorial will briefly discuss the different commands we can use to monitor the GoldenGate environment
and get statistics and reports on various extract and replicat operations which are in progress.
More details can be obtained from Chapter 19 of the Oracle GoldenGate Windows and Unix Administration guide – Monitoring GoldenGate processing.
Information on all GoldenGate processes running on a system
GGSCI (devu007) 21> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:04
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT RUNNING EXT2 00:00:00 00:00:07
EXTRACT ABENDED GAVIN 00:00:00 73:29:25
EXTRACT STOPPED WORKPLAN 00:00:00 191:44:18
REPLICAT RUNNING MYLOAD2 00:00:00 00:00:09
REPLICAT RUNNING MYREP 00:00:00 00:00:08
Find the run status of a particular process
GGSCI (devu007) 23> status manager
Manager is running (IP port devu007.7809).
GGSCI (devu007) 24> status extract ext1
EXTRACT EXT1: RUNNING
Detailed information of a particular process
GGSCI (devu007) 6> info extract ext1, detail
EXTRACT EXT1 Last Started 2010-02-19 11:19 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-26 10:45:18 Seqno 786, RBA 44710400
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/oracle/software/goldengate/dirdat/lt 2 55644 10
Extract Source Begin End
/u02/oradata/apex/redo03.log 2010-02-19 11:13 2010-02-26 10:45
/u02/oradata/apex/redo02.log 2010-02-19 11:04 2010-02-19 11:13
/u02/oradata/apex/redo02.log 2010-02-18 10:42 2010-02-19 11:04
Not Available * Initialized * 2010-02-18 10:42
Current directory /u01/oracle/software/goldengate
Report file /u01/oracle/software/goldengate/dirrpt/EXT1.rpt
Parameter file /u01/oracle/software/goldengate/dirprm/ext1.prm
Checkpoint file /u01/oracle/software/goldengate/dirchk/EXT1.cpe
Process file /u01/oracle/software/goldengate/dirpcs/EXT1.pce
Stdout file /u01/oracle/software/goldengate/dirout/EXT1.out
Error log /u01/oracle/software/goldengate/ggserr.log
Monitoring an Extract recovery
GGSCI (devu007) 35> send extract ext1 status
Sending STATUS request to EXTRACT EXT1 ...
EXTRACT EXT1 (PID 1925238)
Current status: Recovery complete: At EOF
Sequence #: 786
RBA: 40549888
Timestamp: 2010-02-26 09:59:57.000000
Output trail #1
Current write position:
Sequence #: 2
RBA: 55644
Timestamp: 2010-02-26 09:59:54.337574
Extract Trail: /u01/oracle/software/goldengate/dirdat/lt
Monitoring processing volume - Statistics of the operations processed
GGSCI (devu007) 33> stats extract ext1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2010-02-26 09:58:27.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 19.00
Mapped operations 2.00
Unmapped operations 9.00
Other operations 8.00
Excluded operations 17.00
Output to /u01/oracle/software/goldengate/dirdat/lt:
Extracting from GGS_OWNER.GGS_MARKER to GGS_OWNER.GGS_MARKER:
*** Total statistics since 2010-02-19 11:21:03 ***
No database operations have been performed.
*** Daily statistics since 2010-02-26 00:00:00 ***
No database operations have been performed.
*** Hourly statistics since 2010-02-26 09:00:00 ***
No database operations have been performed.
*** Latest statistics since 2010-02-19 11:21:03 ***
No database operations have been performed.
Extracting from MONITOR.WORK_PLAN to MONITOR.WORK_PLAN:
*** Total statistics since 2010-02-19 11:21:03 ***
Total inserts 4.00
Total updates 46.00
Total deletes 0.00
Total discards 0.00
Total operations 50.00
*** Daily statistics since 2010-02-26 00:00:00 ***
Total inserts 0.00
Total updates 16.00
Total deletes 0.00
Total discards 0.00
Total operations 16.00
*** Hourly statistics since 2010-02-26 09:00:00 ***
No database operations have been performed.
*** Latest statistics since 2010-02-19 11:21:03 ***
Total inserts 4.00
Total updates 46.00
Total deletes 0.00
Total discards 0.00
Total operations 50.00
End of Statistics.
View processing rate - can use 'hr','min' or 'sec' as a parameter
GGSCI (devu007) 37> stats extract ext2 reportrate hr
Sending STATS request to EXTRACT EXT2 ...
Start of Statistics at 2010-02-26 10:04:46.
Output to /u01/oracle/ggs/dirdat/cc:
Extracting from SH.CUSTOMERS to SH.CUSTOMERS:
*** Total statistics since 2010-02-26 09:29:48 ***
Total inserts/hour: 0.00
Total updates/hour: 95258.62
Total deletes/hour: 0.00
Total discards/hour: 0.00
Total operations/hour: 95258.62
*** Daily statistics since 2010-02-26 09:29:48 ***
Total inserts/hour: 0.00
Total updates/hour: 95258.62
Total deletes/hour: 0.00
Total discards/hour: 0.00
Total operations/hour: 95258.62
*** Hourly statistics since 2010-02-26 10:00:00 ***
No database operations have been performed.
*** Latest statistics since 2010-02-26 09:29:48 ***
Total inserts/hour: 0.00
Total updates/hour: 95258.62
Total deletes/hour: 0.00
Total discards/hour: 0.00
Total operations/hour: 95258.62
End of Statistics.
View latency between the records processed by Goldengate and the timestamp in the data source
GGSCI (devu007) 13> send extract ext2, getlag
Sending GETLAG request to EXTRACT EXT2 ...
Last record lag: 3 seconds.
At EOF, no more records to process.
GGSCI (devu007) 15> lag extract ext*
Sending GETLAG request to EXTRACT EXT1 ...
Last record lag: 1 seconds.
At EOF, no more records to process.
Sending GETLAG request to EXTRACT EXT2 ...
Last record lag: 1 seconds.
At EOF, no more records to process.
Viewing the GoldenGate error log as well as history of commands executed and other events
We can use the editor depending on operating system – vi on Unix for example to view the
ggserr.log file which is located at the top level GoldenGate software installation directory.
We can also use the GGSCI command VIEW GGSEVT as well.
View the process report
Every Manager, Extract and Replicat process will generate a report file at the end of each run
and this
report can be viewed to diagnose any problems or errors as well as view the parameters used,
the environment variables is use, memory consumption etc
For example:
GGSCI (devu007) 2> view report ext1
GGSCI (devu007) 2> view report rep1
GGSCI (devu007) 2> view report mgr
Information on Child processes started by the Manager
GGSCI (devu007) 8> send manager childstatus
Sending CHILDSTATUS request to MANAGER ...
Child Process Status - 6 Entries
ID Group Process Retry Retry Time Start Time
---- -------- ---------- ----- ------------------ -----------
0 EXT1 1925238 0 None 2010/02/19 11:07:54
1 DPUMP 2195496 0 None 2010/02/19 11:08:02
2 MSSQL1 422034 0 None 2010/02/22 13:54:59
4 MYREP 1302702 0 None 2010/02/23 09:08:34
6 MYLOAD2 1200242 0 None 2010/02/23 11:05:01
7 EXT2 2076844 0 None 2010/02/26 08:29:22
=--==================================================================================
------------------------------------------------------------------------------------------
GGSCI (redhat346.localdomain) 5> STATUS REPLICAT rep1
REPLICAT REP1: RUNNING
How do you enable Oracle GoldenGate for Oracle database 11.2.0.4?
ENABLE_GOLDENGATE_REPLICATION=true
Source:
Stop the primary extract
Stop the pump extract
Stop the manager process
Make sure all the processes are down.
Target:
Stop replicat process
Stop mgr
Make sure that all the processes are down.
Proceed with the maintenance
After the maintenance, proceed with starting up the processes:
Source:
Start the manager process
Start the primary extract
Start the pump extract
(Or simply all the extract processes as GGSCI> start extract *)
Make sure all that the processes are up.
Target:
Start the manager process
Start the replicat process.
Make sure that all the processes are up.
$versions
---------------------------------------------------------
GoldenGate enables us to extract and replicate data across a variety of topologies as shown
the diagram below as well as the
exchange and manipulation of data at the transactional level between a variety of database
platforms like Oracle, DB2, SQL Server, Ingres, MySQL etc.
It can support a number of different business requirements like:
Business Continuity and High Availablity
Data migrations and upgrades
Decision Support Systems and Data Warehousing
Data integration and consolidation
--
Let us know look at the differents components and processes that make up a typical GoldenGate
configuration on Oracle.
Manager
The Manager process must be running on both the source as well as target systems before the
Extract or Replicat process can be started and performs a number of functions including
monitoring and starting other GoldenGate processes, managing the trail files and also reporting.
Extract
The Extract process runs on the source system and is the data caoture mechanism of GoldenGate.
It can be configured both for initial loading of the source data as well as to synchronize
the changed data on the source with the target.
This can be configued to also propagate any DDL changes on those databases where
DDL change support is available.
Replicat
The Replicat process runs on the target system and reads transactional data changes as well
as DDL changes
and replicates then to the target database. Like the Extract process, the Replicat process
can also be configured for Initial Load as well as Change Synchronization.
Collector
The Collector is a background process which runs on the target system and is started
automatically by the Manager (Dynamic Collector)
or it can be configured to stsrt manually (Static Collector).
It receives extracted data changes that are sent via TCP/IP and writes then to the
trail files from where they are processed by the Replicat process.
Trails
Trails are series of files that GoldenGate temporarily stores on disks and these files are
written to and read from by the Extract and Replicat processes as the case may be.
Depending on the configuration chosen, these trail files can exist on the source
as well as on the target systems. If it exists on the local system,
it will be known an Extract Trail or as an Remote Trail if it exists on the target system.
Data Pumps
Data Pumps are secondary extract mechanisms which exist in the source configuration.
This is optional component and if Data Pump is not used then Extract
sends data via TCP/IP to the remote trail on the target. When Data Pump is configured,
the the Primary Extract process will write to the Local Trail and then this trail is read
by the Data Pump and data is sent over
the network to Remote Trails on the target system.
In the absence of Data Pump, the data that the Extract process extracts resides in memory
alone and there is no storage of this data anywhere on the source system.
In case of network of target failures, there could be cases where the primary extract process
can abort or abend.
Data Pump can also be useful in those cases where we are doing complex filtering and
transformation of data as well as when we are consolidating data from many sources
to a central target.
Data source
When processing transactional data changes, the Extract process can obtain data directly
from the database transaction logs
(Oracle, DB2, SQL Server, MySQL etc) or from a GoldenGate Vendor Access Module (VAM) where
the database vendor (for example Teradata) will provide the required components that will
be used by Extract to extract the data changes.
Groups
To differentiate between the number of different Extract and Replicat groups which can
potentially co-exist on a system, we can define processing groups.
For instance, if we want to replicate different sets of data in parallel, we can create
two Replicat groups.
A processing group consisits of a process which could be either a Extract or Replicat process,
a corresponding parameter file, checkpoint file or checkpoint table (for Replicat)
and other files which could be associated with the process.
--------
==================================================================================
GoldenGate Tutorial 2 – Installation (Oracle 11g on Linux)
This example will illustrate the installation of Oracle GoldenGate on an RHEL 5 platform.
We had in an earlier post discussed the architecture and various components of a GoldenGate environment.
GoldenGate software is also available on OTN but for our platform we need to download the required software from the Oracle E-Delivery web site.
Select the Product Pack “Oracle Fusion Middleware” and the platform Linux X86-64.
Then select “Oracle GoldenGate on Oracle Media Pack for Linux x86-64? and
since we are installing this for an Oracle 11g database, we download “Oracle GoldenGate V10.4.0.x for Oracle 11g 64bit on RedHat 5.0?
$ unzip V18159-01.zip
Archive: V18159-01.zip
inflating: ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar
$tar -xvof ggs_redhatAS50_x64_ora11g_64bit_v10.4.0.19_002.tar
$ export PATH=$PATH:/u01/oracle/ggs
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/oracle/ggs
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (redhat346.localdomain) 1>
GGSCI (redhat346.localdomain) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/oracle/product/11.2.0/dbhome_1
Parameter files /u01/oracle/ggs/dirprm: created
Report files /u01/oracle/ggs/dirrpt: created
Checkpoint files /u01/oracle/ggs/dirchk: created
Process status files /u01/oracle/ggs/dirpcs: created
SQL script files /u01/oracle/ggs/dirsql: created
Database definitions files /u01/oracle/ggs/dirdef: created
Extract data files /u01/oracle/ggs/dirdat: created
Temporary files /u01/oracle/ggs/dirtmp: created
Veridata files /u01/oracle/ggs/dirver: created
Veridata Lock files /u01/oracle/ggs/dirver/lock: created
Veridata Out-Of-Sync files /u01/oracle/ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/oracle/ggs/dirver/oosxml: created
Veridata Parameter files /u01/oracle/ggs/dirver/params: created
Veridata Report files /u01/oracle/ggs/dirver/report: created
Veridata Status files /u01/oracle/ggs/dirver/status: created
Veridata Trace files /u01/oracle/ggs/dirver/trace: created
Stdout files /u01/oracle/ggs/dirout: created
We then need to create a database user which will be used by the GoldenGate Manager, Extract
and Replicat processes.
We can create individual users for each process or configure just a common user – in our
case we will create the one user GGS_OWNER and
grant it the required privileges.
SQL> create tablespace ggs_data
2 datafile ‘/u02/oradata/gavin/ggs_data01.dbf’ size 200m;
SQL> create user ggs_owner identified by ggs_owner
2 default tablespace ggs_data
3 temporary tablespace temp;
User created.
SQL> grant connect,resource to ggs_owner;
Grant succeeded.
SQL> grant select any dictionary, select any table to ggs_owner;
Grant succeeded.
SQL> grant create table to ggs_owner;
Grant succeeded.
SQL> grant flashback any table to ggs_owner;
Grant succeeded.
SQL> grant execute on dbms_flashback to ggs_owner;
Grant succeeded.
SQL> grant execute on utl_file to ggs_owner;
Grant succeeded.
We can then confirm that the GoldenGate user we have just created is able to connect to the Oracle database
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
AIX 5L, ppc, 64bit (optimized), Oracle 11 on Sep 17 2009 23:54:16
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI (devu007) 1> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
We also need to enable supplemental logging at the database level otherwise we will get this error when we try to start the Extract process -
2010-02-08 13:51:21 GGS ERROR 190 No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key
column is not in first row piece.
2010-02-08 13:51:21 GGS ERROR 190 PROCESS ABENDING.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Database altered
-------------------------
====================================
GoldenGate Tutorial 3 – Configuring the Manager process
The Oracle GoldenGate Manager performs a number of functions like starting the other GoldenGate processes,
trail log file management and reporting.
The Manager process needs to be configured on both the source as well as target systems
and configuration is carried out via a parameter file just as in the case of the other GoldenGate processes like Extract and Replicat.
After installation of the software, we launch the GoldenGate Software Command Interface (GGSCI) and issue the following command to edit the Manager parameter file
EDIT PARAMS MGR
The only mandatory parameter that we need to specify is the PORT which defines the port on the local system where the manager process is running.
The default port is 7809 and we can either specify the default port or some other port provided the port is available and not restricted in any way.
Some other recommended optional parameters are AUTOSTART which which automatically start the Extract and Replicat processes when the Manager starts.
The USERID and PASSWORD parameter and required if you enable GoldenGate DDL support and this is the Oracle user account that we created for the Manager(and Extract/Replicat)
as described in the earlier tutorial.
The Manager process can also clean up trail files from disk when GoldenGate has finished processing them via the PURGEOLDEXTRACTS parameter.
Used with the USECHECKPOINTS clause, it will ensure that until all processes have fnished using the data contained in the trail files, they will not be deleted.
The following is an example of a manager parameter file
[oracle@redhat346 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
GGSCI 2> EDIT PARAMS MGR
PORT 7809
USERID ggs_owner, PASSWORD ggs_owner
PURGEOLDEXTRACTS /u01/oracle/ggs/dirdat/ex, USECHECKPOINTS
The manager can be stopped and started via the GSSCI commands START MANAGER and STOP MANAGER .
Information on the status of the Manager can be obtained via the INFO MANAGER command
GGSCI (devu007) 4> info manager
Manager is running (IP port devu007.7809).
-------------------------------------------
Oracle GoldenGate Tutorial 4 – performing initial data load
This example illustrates using the GoldenGate direct load method to extract records from an Oracle 11g database on Red Hat Linux platform and load the same into an Oracle 11g target database on an AIX platform.
The table PRODUCTS in the SH schema on the source has 72 rows and on the target database the same table is present only in structure without any data. We will be loading the 72 rows in this example from the source database to the target database using GoldenGate Direct Load method.
On Source
1) Create the Initial data extract process ‘load1'
GGSCI (redhat346.localdomain) 5> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.
Since this is a one time data extract task, the source of the data is not the transaction log files of the RDBMS (in this case the online and archive redo log files) but the table data itself, that is why the keyword SOURCEISTABLE is used.
2) Create the parameter file for the extract group load1
EXTRACT: name of the extract group
USERID/PASSWORD: the database user which has been configured earlier for Extract ( this user is created in the source database)
RMTHOST: This will be the IP address or hostname of the target system
MGRPORT: the port where the Manager process is running
TABLE: specify the table which is being extracted and replicated. This can be specified in a number of ways using wildcard characters to include or exclude tables as well as entire schemas.
GGSCI (redhat346.localdomain) 6> EDIT PARAMS load1
EXTRACT load1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE sh.products;
On Target
3) Create the initial data load task ‘load2'
Since this is a one time data load task, we are using the keyword SPECIALRUN
GGSCI (devu007) 1> ADD REPLICAT load2, SPECIALRUN
REPLICAT added.
4) Create the parameter file for the Replicat group, load2
REPLICAT: name of the Replicat group created for the initial data load
USERID/PASSWORD: database credentials for the Replicat user (this user is created in the target database)
ASSUMETARGETDEFS: this means that the source table structure exactly matches the target database table structure
MAP: with GoldenGate we can have the target database structure entirely differ from that of the source in terms of table names as well as the column definitions of the tables. This parameter provides us the mapping of the source and target tables which is same in this case
GGSCI (devu007) 2> EDIT PARAMS load2
“/u01/oracle/software/goldengate/dirprm/rep4.prm” [New file]
REPLICAT load2
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP sh.customers, TARGET sh.customers;
On Source
SQL> select count(*) from products;
COUNT(*)
———-
72
On Target
SQL> select count(*) from products;
COUNT(*)
———-
0
On Source
5) Start the initial load data extract task on the source system
We now start the initial data load task load 1 on the source. Since this is a one time task, we will initially see that the extract process is runningand after the data load is complete it will be stopped. We do not have to manually start the Replicat process on the target as that is done when the Extract task is started on the source system.
On Source
GGSCI (redhat346.localdomain) 16> START EXTRACT load1
Sending START request to MANAGER …
EXTRACT LOAD1 starting
GGSCI (redhat346.localdomain) 28> info extract load1
EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE
GGSCI (redhat346.localdomain) 29> info extract load1
EXTRACT LOAD1 Last Started 2010-02-11 11:33 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SH.PRODUCTS
2010-02-11 11:33:16 Record 72
Task SOURCEISTABLE
On Target
SQL> select count(*) from products;
COUNT(*)
———-
72
-------------------------------------------------------
------------------------------------------------------
configuring online change synchronization
In our earlier tutorial, we examined how to create a GoldenGate environment for initial data capture and load.
In this tutorial, we will see how by using GoldenGate change synchronization, changes that occur on the source (Oracle 11g on Linux) are applied near
real time on the target (Oracle 11g on AIX). The table on the source is the EMP table in SCOTT schema which is being replicated to the EMP table in the target database SH schema.
These are the steps that we will take:
Create a GoldenGate Checkpoint table
Create an Extract group
Create a parameter file for the online Extract group
Create a Trail
Create a Replicat group
Create a parameter file for the online Replicat group
Create the GoldenGate Checkpoint table
GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat process will start processing after any kind of error or shutdown.
This ensures data integrity and a record of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.
We can also create a single Checkpoint table which can used by all Replicat groups from the single or many GoldenGate instances.
In one of the earlier tutorials we had created the GLOBALS file. We now need to edit that GLOBALS file and add an entry for CHECKPOINTTABLE which will include the checkpoint table name which will be available to all Replicat processes via the EDIT PARAMS command.
GGSCI (devu007) 2> EDIT PARAMS ./GLOBALS
GGSCHEMA GGS_OWNER
CHECKPOINTTABLE GGS_OWNER.CHKPTAB
GGSCI (devu007) 4> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.
GGSCI (devu007) 6> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB
Successfully created checkpoint table GGS_OWNER.CHKPTAB.
apex:/u01/oracle/software/goldengate> sqlplus ggs_owner/ggs_owner
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 8 09:02:19 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc chkptab
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NAME NOT NULL VARCHAR2(8)
GROUP_KEY NOT NULL NUMBER(19)
SEQNO NUMBER(10)
RBA NOT NULL NUMBER(19)
AUDIT_TS VARCHAR2(29)
CREATE_TS NOT NULL DATE
LAST_UPDATE_TS NOT NULL DATE
CURRENT_DIR NOT NULL VARCHAR2(255)
Create the Online Extract Group
GGSCI (redhat346.localdomain) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.
Create the Trail
We now create a trail – note that this path pertains to the GoldenGate software location on the target system and this is where the trail files will be created having a prefix ‘rt’ which will be used by the Replicat process also running on the target system
GGSCI (redhat346.localdomain) 2> ADD RMTTRAIL /u01/oracle/software/goldengate/dirdat/rt, EXTRACT ext1
RMTTRAIL added.
Create a parameter file for the online Extract group ext1
GGSCI (redhat346.localdomain) 3> EDIT PARAMS ext1
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTRAIL /u01/oracle/software/goldengate/dirdat/rt
TABLE scott.emp;
ON TARGET SYSTEM
Create the online Replicat group
GGSCI (devu007) 7> ADD REPLICAT rep1, EXTTRAIL /u01/oracle/software/goldengate/dirdat/rt
REPLICAT added.
Note that the EXTTRAIL location which is on the target local system conforms to the RMTTRAIL parameter which we used when we created the parameter file for the extract process on the source system.
Create a parameter file for the online Replicat group, rep1
GGSCI (devu007) 8> EDIT PARAMS rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.emp, TARGET sh.emp;
ON SOURCE
Start the Extract process
GGSCI (redhat346.localdomain) 16> START EXTRACT ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
GGSCI (redhat346.localdomain) 17> STATUS EXTRACT ext1
EXTRACT EXT1: RUNNING
GGSCI (redhat346.localdomain) 16> INFO EXTRACT ext1
EXTRACT EXT1 Last Started 2010-02-08 14:27 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-08 14:27:48 Seqno 145, RBA 724480
ON TARGET
Start the Replicat process
GGSCI (devu007) 1> START REPLICAT rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
GGSCI (devu007) 2> INFO REPLICAT rep1
REPLICAT REP1 Last Started 2010-02-08 14:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/rt000001
2010-02-08 14:27:57.600425 RBA 1045
Note: the trail file has a prefix of ‘rt’ (which we had defined earlier)
LET US NOW TEST …
ON SOURCE
SQL> conn scott/tiger
Connected.
SQL> UPDATE emp SET sal=9999 WHERE ename=’KING’;
1 row updated.
SQL> COMMIT;
Commit complete.
ON TARGET
SQL> SELECT SAL FROM emp WHERE ename=’KING’;
SAL
———-
9999
--------------------------------------
-------------------------------------------
Oracle GoldenGate Tutorial 6 – configuring Data Pump process
The Data Pump (not to be confused with the Oracle Export Import Data Pump) is an optional secondary Extract group
that is created on the source system. When Data Pump is not used, the Extract process writes to a remote trail that is located on the target
system using TCP/IP. When Data Pump is configured, the Extract process writes to a local trail and from here Data Pump will read the trail and write the data over the network
to the remote trail located on the target system.
The advantages of this can be seen as it protects against a network failure as in the absence of a storage device on the local system,
the Extract process writes data into memory before the same is sent over the network. Any failures in the network could then cause the Extract process to abort (abend).
Also if we are doing any complex data transformation or filtering, the same can be performed by the Data Pump.
It will also be useful when we are consolidating data from several sources into one central target where data pump on each individual source system can write to one common trail file on the target.
Create the Extract process
GGSCI (devu007) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.
Create a local trail
Using the ADD EXTRAIL command we will now create a local trail on the source system where the Extract process will write to and which is then read by the Data Pump process. We will link this local trail to the Primary Extract group we just created, ext1
GGSCI (devu007) 3> ADD EXTTRAIL /u01/oracle/software/goldengate/dirdat/lt, EXTRACT ext1
EXTTRAIL added.
Create the Data Pump group
On the source system create the Data Pump group and using the EXTTRAILSOURCE keywork specify the location of the local trail which will be read by the Data Pump process
GGSCI (devu007) 4> ADD EXTRACT dpump, EXTTRAILSOURCE /u01/oracle/software/goldengate/dirdat/lt
EXTRACT added.
Create the parameter file for the Primary Extract group
GGSCI (devu007) 5> EDIT PARAMS ext1
“/u01/oracle/software/goldengate/dirprm/ext1.prm” [New file]
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
EXTTRAIL /u01/oracle/software/goldengate/dirdat/lt
TABLE MONITOR.WORK_PLAN;
Specify the location of the remote trail on the target system
Use the RMTTRAIL to specify the location of the remote trail and associate the same with the Data Pump group as it will be wriiten to over the network by the data pump process
GGSCI (devu007) 6> ADD RMTTRAIL /u01/oracle/ggs/dirdat/rt, EXTRACT dpump
RMTTRAIL added.
Create the parameter file for the Data Pump group
Note- the parameter PASSTHRU signifies the mode being used for the Data Pump which means that the names of the source and target objects are identical and no column mapping or filtering is being performed here.
GGSCI (devu007) 2> EDIT PARAMS dpump
“/u01/oracle/software/goldengate/dirprm/dpump.prm” [New file]
EXTRACT dpump
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST redhat346, MGRPORT 7809
RMTTRAIL /u01/oracle/ggs/dirdat/rt
PASSTHRU
TABLE MONITOR.WORK_PLAN;
ON TARGET SYSTEM
Create the Replicat group
The EXTTRAIL clause indicates the location of the remote trail and should be the same as the RMTTRAIL value that was used when creating the Data Pump process on the source system.
GGSCI (redhat346.localdomain) 2> ADD REPLICAT rep1, EXTTRAIL /u01/oracle/ggs/dirdat/rt
REPLICAT added.
Create the parameter file for the Replicat group
GGSCI (redhat346.localdomain) 3> EDIT PARAMS rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP MONITOR.WORK_PLAN, TARGET MONITOR.WORK_PLAN;
Bounce:
========
ON SOURCE
===========
On the source system, now start the Extract and Data Pump processes.
GGSCI (devu007) 3> START EXTRACT ext1
Sending START request to MANAGER …
EXTRACT EXT1 starting
=
GGSCI (devu007) 4> START EXTRACT dpump
Sending START request to MANAGER …
EXTRACT DPUMP starting
GGSCI (devu007) 5> info extract ext1
EXTRACT EXT1 Last Started 2010-02-18 11:23 Status RUNNING
Checkpoint Lag 00:40:52 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-18 10:42:19 Seqno 761, RBA 15086096
GGSCI (devu007) 6> INFO EXTRACT dpump
EXTRACT DPUMP Last Started 2010-02-18 11:23 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/lt000000
2010-02-18 11:15:10.000000 RBA 5403
Note- the data pump process is reading from the Local Trail file – /u01/oracle/software/goldengate/dirdat/lt000000
ON TARGET SYSTEM
Start the Replicat process
GGSCI (redhat346.localdomain) 4> START REPLICAT rep1
Sending START request to MANAGER …
REPLICAT REP1 starting
================================
Monitoring GoldenGate
============================
The following tutorial will briefly discuss the different commands we can use to monitor the GoldenGate environment
and get statistics and reports on various extract and replicat operations which are in progress.
More details can be obtained from Chapter 19 of the Oracle GoldenGate Windows and Unix Administration guide – Monitoring GoldenGate processing.
Information on all GoldenGate processes running on a system
GGSCI (devu007) 21> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:04
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT RUNNING EXT2 00:00:00 00:00:07
EXTRACT ABENDED GAVIN 00:00:00 73:29:25
EXTRACT STOPPED WORKPLAN 00:00:00 191:44:18
REPLICAT RUNNING MYLOAD2 00:00:00 00:00:09
REPLICAT RUNNING MYREP 00:00:00 00:00:08
Find the run status of a particular process
GGSCI (devu007) 23> status manager
Manager is running (IP port devu007.7809).
GGSCI (devu007) 24> status extract ext1
EXTRACT EXT1: RUNNING
Detailed information of a particular process
GGSCI (devu007) 6> info extract ext1, detail
EXTRACT EXT1 Last Started 2010-02-19 11:19 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-26 10:45:18 Seqno 786, RBA 44710400
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
/u01/oracle/software/goldengate/dirdat/lt 2 55644 10
Extract Source Begin End
/u02/oradata/apex/redo03.log 2010-02-19 11:13 2010-02-26 10:45
/u02/oradata/apex/redo02.log 2010-02-19 11:04 2010-02-19 11:13
/u02/oradata/apex/redo02.log 2010-02-18 10:42 2010-02-19 11:04
Not Available * Initialized * 2010-02-18 10:42
Current directory /u01/oracle/software/goldengate
Report file /u01/oracle/software/goldengate/dirrpt/EXT1.rpt
Parameter file /u01/oracle/software/goldengate/dirprm/ext1.prm
Checkpoint file /u01/oracle/software/goldengate/dirchk/EXT1.cpe
Process file /u01/oracle/software/goldengate/dirpcs/EXT1.pce
Stdout file /u01/oracle/software/goldengate/dirout/EXT1.out
Error log /u01/oracle/software/goldengate/ggserr.log
Monitoring an Extract recovery
GGSCI (devu007) 35> send extract ext1 status
Sending STATUS request to EXTRACT EXT1 ...
EXTRACT EXT1 (PID 1925238)
Current status: Recovery complete: At EOF
Sequence #: 786
RBA: 40549888
Timestamp: 2010-02-26 09:59:57.000000
Output trail #1
Current write position:
Sequence #: 2
RBA: 55644
Timestamp: 2010-02-26 09:59:54.337574
Extract Trail: /u01/oracle/software/goldengate/dirdat/lt
Monitoring processing volume - Statistics of the operations processed
GGSCI (devu007) 33> stats extract ext1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2010-02-26 09:58:27.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 19.00
Mapped operations 2.00
Unmapped operations 9.00
Other operations 8.00
Excluded operations 17.00
Output to /u01/oracle/software/goldengate/dirdat/lt:
Extracting from GGS_OWNER.GGS_MARKER to GGS_OWNER.GGS_MARKER:
*** Total statistics since 2010-02-19 11:21:03 ***
No database operations have been performed.
*** Daily statistics since 2010-02-26 00:00:00 ***
No database operations have been performed.
*** Hourly statistics since 2010-02-26 09:00:00 ***
No database operations have been performed.
*** Latest statistics since 2010-02-19 11:21:03 ***
No database operations have been performed.
Extracting from MONITOR.WORK_PLAN to MONITOR.WORK_PLAN:
*** Total statistics since 2010-02-19 11:21:03 ***
Total inserts 4.00
Total updates 46.00
Total deletes 0.00
Total discards 0.00
Total operations 50.00
*** Daily statistics since 2010-02-26 00:00:00 ***
Total inserts 0.00
Total updates 16.00
Total deletes 0.00
Total discards 0.00
Total operations 16.00
*** Hourly statistics since 2010-02-26 09:00:00 ***
No database operations have been performed.
*** Latest statistics since 2010-02-19 11:21:03 ***
Total inserts 4.00
Total updates 46.00
Total deletes 0.00
Total discards 0.00
Total operations 50.00
End of Statistics.
View processing rate - can use 'hr','min' or 'sec' as a parameter
GGSCI (devu007) 37> stats extract ext2 reportrate hr
Sending STATS request to EXTRACT EXT2 ...
Start of Statistics at 2010-02-26 10:04:46.
Output to /u01/oracle/ggs/dirdat/cc:
Extracting from SH.CUSTOMERS to SH.CUSTOMERS:
*** Total statistics since 2010-02-26 09:29:48 ***
Total inserts/hour: 0.00
Total updates/hour: 95258.62
Total deletes/hour: 0.00
Total discards/hour: 0.00
Total operations/hour: 95258.62
*** Daily statistics since 2010-02-26 09:29:48 ***
Total inserts/hour: 0.00
Total updates/hour: 95258.62
Total deletes/hour: 0.00
Total discards/hour: 0.00
Total operations/hour: 95258.62
*** Hourly statistics since 2010-02-26 10:00:00 ***
No database operations have been performed.
*** Latest statistics since 2010-02-26 09:29:48 ***
Total inserts/hour: 0.00
Total updates/hour: 95258.62
Total deletes/hour: 0.00
Total discards/hour: 0.00
Total operations/hour: 95258.62
End of Statistics.
View latency between the records processed by Goldengate and the timestamp in the data source
GGSCI (devu007) 13> send extract ext2, getlag
Sending GETLAG request to EXTRACT EXT2 ...
Last record lag: 3 seconds.
At EOF, no more records to process.
GGSCI (devu007) 15> lag extract ext*
Sending GETLAG request to EXTRACT EXT1 ...
Last record lag: 1 seconds.
At EOF, no more records to process.
Sending GETLAG request to EXTRACT EXT2 ...
Last record lag: 1 seconds.
At EOF, no more records to process.
Viewing the GoldenGate error log as well as history of commands executed and other events
We can use the editor depending on operating system – vi on Unix for example to view the
ggserr.log file which is located at the top level GoldenGate software installation directory.
We can also use the GGSCI command VIEW GGSEVT as well.
View the process report
Every Manager, Extract and Replicat process will generate a report file at the end of each run
and this
report can be viewed to diagnose any problems or errors as well as view the parameters used,
the environment variables is use, memory consumption etc
For example:
GGSCI (devu007) 2> view report ext1
GGSCI (devu007) 2> view report rep1
GGSCI (devu007) 2> view report mgr
Information on Child processes started by the Manager
GGSCI (devu007) 8> send manager childstatus
Sending CHILDSTATUS request to MANAGER ...
Child Process Status - 6 Entries
ID Group Process Retry Retry Time Start Time
---- -------- ---------- ----- ------------------ -----------
0 EXT1 1925238 0 None 2010/02/19 11:07:54
1 DPUMP 2195496 0 None 2010/02/19 11:08:02
2 MSSQL1 422034 0 None 2010/02/22 13:54:59
4 MYREP 1302702 0 None 2010/02/23 09:08:34
6 MYLOAD2 1200242 0 None 2010/02/23 11:05:01
7 EXT2 2076844 0 None 2010/02/26 08:29:22
=--==================================================================================
------------------------------------------------------------------------------------------
GGSCI (redhat346.localdomain) 5> STATUS REPLICAT rep1
REPLICAT REP1: RUNNING
How do you enable Oracle GoldenGate for Oracle database 11.2.0.4?
ENABLE_GOLDENGATE_REPLICATION=true
Source:
Stop the primary extract
Stop the pump extract
Stop the manager process
Make sure all the processes are down.
Target:
Stop replicat process
Stop mgr
Make sure that all the processes are down.
Proceed with the maintenance
After the maintenance, proceed with starting up the processes:
Source:
Start the manager process
Start the primary extract
Start the pump extract
(Or simply all the extract processes as GGSCI> start extract *)
Make sure all that the processes are up.
Target:
Start the manager process
Start the replicat process.
Make sure that all the processes are up.
$versions
Comments
Post a Comment