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 forCHECKPOINTTABLE 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.
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
[[code]]
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 – 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;
**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
GGSCI (redhat346.localdomain) 5> **STATUS REPLICAT rep1**
REPLICAT REP1: RUNNING
Oracle GoldenGate Tutorial – configuring DDL synchronization
In addition to providing replication support for all DML statements, we can also configure the GoldenGate environment to provide DDL support as well.
A number of prerequisite setup tasks need to be performed which we willl highlight here.
**Run the following scripts from the directory where the GoldenGate software was installed**.
The assumption here is that the database user GGS_OWNER has already been created and granted the required roles and privileges as discussed in our earlier tutorial.
Note - run the scripts as SYSDBA
SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:GGS_OWNER
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
MARKER TABLE
OK
MARKER SEQUENCE
OK
Script complete.
SQL> alter session set recyclebin=OFF;
Session altered.
SQL> @ddl_setup
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: On Oracle 10g and up, system recycle bin must be disabled.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:GGS_OWNER
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.
Working, please wait ...
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_OWNER
DDLORA_GETTABLESPACESIZE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
CLEAR_TRACE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
OK
DDL HISTORY TABLE(1)
OK
DDL DUMP TABLES
OK
DDL DUMP COLUMNS
OK
DDL DUMP LOG GROUPS
OK
DDL DUMP PARTITIONS
OK
DDL DUMP PRIMARY KEYS
OK
DDL SEQUENCE
OK
GGS_TEMP_COLS
OK
GGS_TEMP_UK
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
OK
DDL TRIGGER RUNNING STATUS
ENABLED
STAYMETADATA IN TRIGGER
OFF
DDL TRIGGER SQL TRACING
0
DDL TRIGGER TRACE LEVEL
0
LOCATION OF DDL TRACE FILE
/u01/app/oracle/diag/rdbms/gavin/gavin/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL>
SQL> @role_setup
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change
the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:GGS_OWNER
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to ggs_owner;
Grant succeeded.
SQL> @ddl_enable
Trigger altered.
SQL> @ddl_pin GGS_OWNERPL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
**Turn Recyclebin OFF**
We need to set the parameter recyclebin to OFF via the ALTER SYSTEM SET RECYCLEBIN=OFF command in order to prevent this error which we will see if we try and configure DDL support and then start the Extract process.
2010-02-19 11:13:30 GGS ERROR 2003 RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBIN in Parameter file to False. Then restart database and extract.
2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.
**Enable additional logging at the table level**
Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by GoldenGate for DDL support.
GGSCI (redhat346.localdomain) 5> **DBLOGIN USERID ggs_owner, PASSWORD ggs_owner**
Successfully logged into database.
GGSCI (redhat346.localdomain) 6> **ADD TRANDATA scott.emp**
Logging of supplemental redo data enabled for table SCOTT.EMP.
**Edit the parameter file for the Extract process to enable DDL synchronization**
We had earlier created a parameter file for an Extract process ext1. We now edit that parameter file and add the entry
**DDL INCLUDE MAPPED**
This means that DDL support is now enabled for all tables which have been mapped and in this case it will only apply to the SCOTT.EMP table as that is the only table which is being processed here. We can also use the INCLUDE ALL or EXCLUDE ALL or wildcard characters to specify which tables to enable the DDL support for.
GGSCI (redhat346.localdomain) 1> **EDIT PARAM EXT1**
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST 10.53.100.100, MGRPORT 7809
RMTTRAIL /u01/oracle/software/goldengate/dirdat/rt
**DDL INCLUDE MAPPED**
TABLE scott.emp;
**Test the same**
We will now alter the structure of the EMP table by adding a column and we can see that this new table structure is also reflected on the target system.
**On Source**
SQL> **ALTER TABLE EMP ADD NEW_COL VARCHAR2(10);
Table altered.
**On Target**
SQL> desc emp
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
NAME VARCHAR2(10)
JOB VARCHAR2(20)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
MYCOL VARCHAR2(10)
**NEW_COL VARCHAR2(10**)
Oracle Goldengate Tutorial 8 – Filtering and Mapping data
Oracle GoldenGate not only provides us a replication solution that is Oracle version independent as well as platform independent, but we can also use it to do data transformation and data manipulation between the source and the target.
So we can use GoldenGate when the source and database database differ in table structure as well as an ETL tool in a Datawarehouse type environment.
We will discuss below two examples to demonstrate this feature – column mapping and filtering of data.
In example 1, we will filter the records that are extracted on the source and applied on the target – only rows where the JOB column value equals ‘MANAGER” in the MYEMP table will be considered for extraction.
In example 2, we will deal with a case where the table structure is different between the source database and the target database and see how column mapping is performed in such cases.
**Example 1**
**Initial load of all rows which match the filter from source to target**. The target database MYEMP table will only be populated with rows from the EMP table where filter criteria of JOB=’MANAGER’ is met.
**On Source**
GGSCI (redhat346.localdomain) 4> **add extract myload1, sourceistable**
EXTRACT added.
GGSCI (redhat346.localdomain) 5> **edit params myload1**
EXTRACT myload1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST devu007, MGRPORT 7809
RMTTASK replicat, GROUP myload1
TABLE scott.myemp, FILTER (@STRFIND (job, “MANAGER”) > 0);
**On Target**
GGSCI (devu007) 2> **add replicat myload1, specialrun**
REPLICAT added.
GGSCI (devu007) 3> **edit params myload1**
“/u01/oracle/software/goldengate/dirprm/myload1.prm” [New file]
REPLICAT myload1
USERID ggs_owner, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP scott.myemp, TARGET sh.myemp;
**On Source – start the initial load extract**
GGSCI (redhat346.localdomain) 6> **start extract myl**oad1
Sending START request to MANAGER …
EXTRACT MYLOAD1 starting
**On SOURCE**
SQL> **select count(*) from myemp;**
COUNT(*)
———-
14
SQL> **select count(*) from myemp where job=’MANAGER’;**
COUNT(*)
———-
9
**On TARGET**
SQL> **select count(*) from myemp where job=’MANAGER’;**
COUNT(*)
———-
9
**Create an online change extract and replicat group using a Filter**
GGSCI (redhat346.localdomain) 10> **add extract myload2, tranlog, begin now**
EXTRACT added.
GGSCI (redhat346.localdomain) 11> **add rmttrail /u01/oracle/software/goldengate/dirdat/bb, extract myload2**
RMTTRAIL added.
GGSCI (redhat346.localdomain) 11> **edit params myload2**
EXTRACT myload2
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST 10.53.200.225, MGRPORT 7809
RMTTRAIL /u01/oracle/software/goldengate/dirdat/bb
TABLE scott.myemp, **FILTER (@STRFIND (job, “MANAGER”) > 0**);
**On Target**
GGSCI (devu007) 2> add replicat myload2, exttrail /u01/oracle/software/goldengate/dirdat/bb
REPLICAT added.
GGSCI (devu007) 3> edit params myload2
“/u01/oracle/software/goldengate/dirprm/myload2.prm” [New file]
REPLICAT myload2
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.myemp, TARGET sh.myemp;
**On Source – start the online extract group**
GGSCI (redhat346.localdomain) 13> **start extract myload2**
Sending START request to MANAGER …
EXTRACT MYLOAD2 starting
GGSCI (redhat346.localdomain) 14> **info extract myload2**
EXTRACT MYLOAD2 Last Started 2010-02-23 11:04 Status RUNNING
Checkpoint Lag 00:27:39 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs
2010-02-23 10:36:51 Seqno 214, RBA 103988
**On Target**
GGSCI (devu007) 4> **start replicat** myload2
Sending START request to MANAGER …
REPLICAT MYLOAD2 starting
GGSCI (devu007) 5> **info replicat myload2**
REPLICAT MYLOAD2 Last Started 2010-02-23 11:05 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/bb000000
First Record RBA 989
On Source we now insert two rows into the MYEMP table – one which has the JOB value of ‘MANAGER’ and the other row which has the job value of ‘SALESMAN’
**On SOURCE**
SQL> INSERT INTO MYEMP
2 (empno,ename,job,sal)
3 VALUES
4 (1234,’GAVIN’,’**MANAGER**‘,10000);
1 row created.
SQL> commit;
Commit complete.
SQL> INSERT INTO MYEMP
2 (empno,ename,job,sal)
3 VALUES
4 (1235,’BOB’,’**SALESMAN**‘,1000);
1 row created.
SQL> commit;
Commit complete.
SQL> **select count(*) from myemp;**
COUNT(*)
———-
**16**
SQL> select count(*) from myemp where job=’MANAGER’;
COUNT(*)
———-
**10**
**On Target**, we will see that even though two rows have been inserted into the source MYEMP table, on the target MYEMP table only one row is inserted because the filter has been applied which only includes the rows where the JOB value equals ‘MANAGER’.
SQL> **select count(*) from myemp**;
COUNT(*)
———-
10
**Example 2 – source and target table differ in column structure**
In the source MYEMP table we have a column named SAL whereas on the target, the same MYEMP table has the column defined as SALARY.
**Create a definitions file on the source using DEFGEN utility and then copy that definitions file to the target system**
GGSCI (redhat346.localdomain) > **EDIT PARAMS defgen**
**DEFSFILE /u01/oracle/ggs/dirsql/myemp.sql**
USERID ggs_owner, PASSWORD ggs_owner
TABLE scott.myemp;
[oracle@redhat346 ggs]$ **./defgen paramfile /u01/oracle/ggs/dirprm/defgen.prm**
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 11 on Sep 18 2009 00:09:13
Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-02-23 11:22:17
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Dec 17 11:41:38 EST 2008, Release 2.6.18-128.el5
Node: redhat346.localdomain
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 14175
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/oracle/ggs/dirsql/myemp.sql
USERID ggs_owner, PASSWORD *********
TABLE scott.myemp;
Retrieving definition for SCOTT.MYEMP
**Definitions generated for 1 tables in /u01/oracle/ggs/dirsql/myemp.sql**
If we were to try and run the replicat process on the target without copying the definitions file, we will see an error as shown below which pertains to the fact that the columns in the source and target database are different and GoldenGate is not able to resolve that.
2010-02-23 11:31:07 GGS WARNING 218 Aborted grouped transaction on ‘SH.MYEMP’, Database error 904 (ORA-00904: “SAL”: invalid identifier).
2010-02-23 11:31:07 GGS WARNING 218 SQL error 904 mapping SCOTT.MYEMP to SH.MYEMP OCI Error ORA-00904: “SAL”: invalid identifier (status = 904), SQL //.//
We then ftp the definitions file from the source to the target system – in this case to the dirsql directory located in the top level GoldenGate installed software directory
We now go and make a change to the original replicat parameter file and change the parameter ASSUMEDEFS to **SOURCEDEFS** which provides GoldenGate with the location of the definitions file.
The other parameter which is included is the **COLMAP** parameter which tells us how the column mapping has been performed. The ‘USEDEFAULTS’ keyword denotes that all the other columns in both tables are identical except for the columns SAL and SALARY which differ in both tables and now we are mapping the SAL columsn in source to the SALARY column on the target.
REPLICAT myload2
**SOURCEDEFS /u01/oracle/software/goldengate/dirsql/myemp.s**ql
USERID ggs_owner, PASSWORD ggs_owner
MAP scott.myemp, TARGET sh.myemp,
**COLMAP (usedefaults,**
**salary = sal);**
We now go and start the originall replicat process myload2 which had abended because of the column mismatch (which has now been corrected via the parameter change) and we see that the process now is running without any error.
now go and start the process which had failed after table modification
GGSCI (devu007) 2> info replicat myload2
REPLICAT MYLOAD2 Last Started 2010-02-23 11:05 Status ABENDED
Checkpoint Lag 00:00:03 (updated 00:11:44 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/bb000000
2010-02-23 11:31:03.999504 RBA 1225
GGSCI (devu007) 3> **start replicat myload2**
Sending START request to MANAGER …
REPLICAT MYLOAD2 starting
GGSCI (devu007) 4> **info replicat myload2**
REPLICAT MYLOAD2 Last Started 2010-02-23 11:43 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File /u01/oracle/software/goldengate/dirdat/bb000000
2010-02-23 11:31:03.999504 RBA 1461
Oracle GoldenGate Tutorial 9 – 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
Producer --->> Kakfa server (Kafka Broker), cluster is a group of broker which has a toic - ---> consumer (request back for mo...
No comments:
Post a Comment