Golden Gate : Uni-direction schema level replication

ORACLE GOLDEN GATE (UNIDIRECTIONAL)

   - Schema Level Replication without DDL 

   - Schema Level Replication with DDL 
   - Monitoring Golden Gate

Introduction:

Oracle GoldenGate helps organizations harness the value of their IT investments and improve business operations by providing continuous access to business-critical information in real time. With support for a wide array of continuous availability, disaster tolerance, and data integration scenarios, the software provides a modular foundation that easily scales to address the high-volume data management challenges faced by organizations today.

Oracle Golden Gate is a comprehensive software package for real-time data integration and replication in heterogeneous IT environments. The product  enables different business requirements like high availability solutions, real-time data migration and upgrades, decision support systems and data ware housing as well as data integration and consolidation.

Available Configuration Methods:

Golden Gate can be configured in following method

Method	Use in Business Scenario
Unidirectional	Reporting Instance
Bi-directional	Instance Fail Active-Active
Peer-To-Peer	Load Balancing, High Availability
Broad cost	Data distribution
Consolidation	Data warehouse or Mart or Store
Cascading	Scalability, Database tiering

Golden Gate architecture:

Hardware Requirements:

Golden gate is a memory and CPU intensive type. So more Memory and CPU head rooms are required as per you database transaction and load.

For details go though below link:

https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/system_requirements.htm#GIORA116

Golden gate Process flow:

Golden Gate Unidirectional Configuration:

Let us we will proceed to configure using the method Unidirectional and Datapump.

Advantage:

The advantage of this Unidirectional configuration method is mainly performance, because the business reports(mainly huge reports by means of volume and time consuming) can be run on Reporting server(Target) instead of Production Server(Source) so that Production server resources will be free and provide best performance on real time business (example: month-end closing period in financial sector). The source and target database can be synchronized always for specific schema or entire database based on business requirement.

Prerequisite:

Assumption that two database (Source and Destination) are available with archive log enabled.  Both source & target hosts should be able to do ping/ssh/scp each other.

Environment ( My Test Case)

Particulars	Source	Target
Operating Systems	RHEL 6.3	RHEL 6.3
HOSTNAME	uatdb2	uatdb1
DOMAIN	facedba.com	facedba.com
DB Name	PROD1	PROD2
DB Version	11.2.0.4.6	11.2.0.4.6
Golden Gate Installation Location	/u10/gg_soft	/u10/gg_soft

               

Consolidate Steps:

1. Hardware and OS configuration
2.  Database Pre-requisites
3.  Golden Gate software installation
4.  Create manager process at source and target
5. Create Extract process at source
6. Create Replicate process at Target
7.  Create Checkpoint table at source
8. Initial Dataload target
9.  Start Extract process - source
10. Start replicate process – target

Installation of Golden Gate Software:

1) download, copy and unzip golden gate software both in source and target:

2) Unzip and follow the below steps:

$ cd /u10

$ mkdir gg_soft

$ cd gg_soft

$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

Archive:  ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar 

  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf 

  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt 

  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc 

$ ls

fbo_ggs_Linux_x64_ora11g_64bit.tar            OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf     Oracle GoldenGate 11.2.1.0.1 README.txt

ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip  Oracle GoldenGate 11.2.1.0.1 README.doc

$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

$ ls -lt|wc -l

90

3) set env variable both in source and target

export ORACLE_HOME=/u10/app/oracle/product/11.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u10/gg_soft:/usr/share/java

Then run .bash_profile

Preparing the database for replication:

1) Create a Golden Gate user and execute below scripts - both source and target

create tablespace ggs_data datafile '/u10/oradata/PROD/ggs_data01.dbf' size 100M autoextend on;

create user ggs_owner identified by ggs_owner default tablespace ggs_data ;

grant connect,resource,unlimited tablespace to ggs_owner;

grant SELECT_CATALOG_ROLE to ggs_owner;

grant execute on utl_file to ggs_owner;

grant flashback any table to ggs_owner ;

grant select any dictionary to ggs_owner ;

GRANT CREATE TABLE,CREATE ANY TABLE TO ggs_owner ;

grant insert any table,update any table ,delete any table to ggs_owner ;

grant execute on dbms_flashback to ggs_owner ;

grant select any table ,create any table,drop any table to ggs_owner ;

grant create any view to ggs_owner;

grant create any procedure to ggs_owner;

grant create any sequence to ggs_owner;

grant create any index to ggs_owner;

grant create any trigger to ggs_owner;

grant create any view to ggs_owner;

2) Enable minimal supplemental logging on both source and target

SQL> alter database add supplemental log data;

Database altered.

SQL>

3) Enable below parameter

SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=TRUE ;

System altered.

SQL>

4) Run below scripts both in source and target:

$ cd /u10/gg_soft

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 23 16:45:17 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggs_owner;

SQL> @ddl_enable.sql

5) Login to golden gate interface and create subdirs both source and target:

$ cd /u10/gg_soft

 ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (uatdb2) 1> create subdirs

Creating subdirectories under current directory /u10/gg_soft

Parameter files                /u10/gg_soft/dirprm: already exists

Report files                   /u10/gg_soft/dirrpt: created

Checkpoint files               /u10/gg_soft/dirchk: created

Process status files           /u10/gg_soft/dirpcs: created

SQL script files               /u10/gg_soft/dirsql: created

Database definitions files     /u10/gg_soft/dirdef: created

Extract data files             /u10/gg_soft/dirdat: created

Temporary files                /u10/gg_soft/dirtmp: created

Stdout files                   /u10/gg_soft/dirout: created

GGSCI (uatdb2) 2>

Now Source schema to be restored in Target database:

Use EXPDP and IMPDP to complete the process. Let us assume we are replicating SCOTT schema. Before starting extract and replicate process, data can be imported also.

Do the below Configurations and Start process for replication:

1) Login via golden gate user and Enable additional logging at table level on both source and target:

GGSCI (uatdb2) 2> dblogin userid ggs_owner, password ggs_owner

Successfully logged into database.

GGSCI (uatdb2) 4> add trandata scott.*      -- here we are doing schema level replication

ERROR: No viable tables matched specification. ( if data not imported/ table doesn’t exist, then this issue will come)

GGSCI (uatdb2) 5> add trandata scott.*    -- -- here we are doing schema level replication

Logging of supplemental redo data enabled for table SCOTT.BONUS.

Logging of supplemental redo data enabled for table SCOTT.DEPARTMENT.

Logging of supplemental redo data enabled for table SCOTT.EMPLOYEE.

Logging of supplemental redo data enabled for table SCOTT.PROJECT.

Logging of supplemental redo data enabled for table SCOTT.PROJECT_PARTICIPATION.

Logging of supplemental redo data enabled for table SCOTT.ROLE.

Logging of supplemental redo data enabled for table SCOTT.SALARYGRADE.

GGSCI (uatdb2) 6>

2) Manager process status check in source:

GGSCI (uatdb2) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           

GGSCI (uatdb2) 4>

3) Create a manager process to replicate ( run in source only)

GGSCI (uatdb2) 6> edit params mgr

port 7809

USERID ggs_owner,PASSWORD ggs_owner

PURGEOLDEXTRACTS /u10/gg_soft/dirdat/ex, USECHECKPOINTS

:wq!

-- start manager process

GGSCI (uatdb2) 8> add extract extl, tranlog, begin now

EXTRACT added.

GGSCI (uatdb2) 9> add exttrail /u10/gg_soft/dirdat/ld, extract extl

EXTTRAIL added.

GGSCI (uatdb2) 9> edit params extl

extract extl

userid ggs_owner, password ggs_owner

rmthost 172.18.1.17, mgrport 7810

RMTTRAIL /u10/gg_soft/dirdat/ld

table scott.*;

:wq!

4) Create a manager process to apply transaction data ( run in Target only)

GGSCI (uatdb1) 4> edit params mgr

port 7810

USERID ggs_owner,PASSWORD ggs_owner

PURGEOLDEXTRACTS /u10/gg_soft/dirdat/ex, USECHECKPOINTS

:wq!

GGSCI (uatdb1) 5> edit params ./GLOBAL

GGSCHEMA ggs_owner

CHECKPOINTTABLE ggs_owner.ggschkpt

:wq!

GGSCI (uatdb1) 6> add checkpointtable ggs_owner.ggschkpt

Successfully created checkpoint table ggs_owner.ggschkpt.

-- add process

GGSCI (uatdb1) 8> add replicat repl, exttrail /u10/gg_soft/dirdat/ld, checkpointtable ggs_owner.ggschkpt

REPLICAT added.

GGSCI (uatdb1) 9> edit params repl

replicat repl

HANDLECOLLISIONS

ASSUMETARGETDEFS

userid ggs_owner, password ggs_owner

discardfile /u10/gg_soft/dirrpt/repl_discard.txt append, megabytes 10

map scott.* target scott.*;

:wq!

5) Start manager and extract process at source end:

GGSCI (uatdb2) 12> start mgr

Manager started.

GGSCI (uatdb2) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     STOPPED     EXTL        00:00:00      00:18:56   

GGSCI (uatdb2) 14>

GGSCI (uatdb2) 14> start extract extl

Sending START request to MANAGER ...

EXTRACT EXTL starting

6) Run in Target to replicate

GGSCI (uatdb1) 10> start mgr

Manager started.

GGSCI (uatdb1) 12> start replicat repl

Sending START request to MANAGER ...

REPLICAT REPL starting

GGSCI (uatdb1) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    RUNNING     REPL        00:00:00      00:00:03   

GGSCI (uatdb1) 14>

Now cross verify both source and target manager process are running or not:

Source:

GGSCI (uatdb2) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     RUNNING     EXTL        00:00:00      00:00:09   

GGSCI (uatdb2) 23> info extract extl

EXTRACT    EXTL      Last Started 2016-08-23 19:48   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:06 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2016-08-24 11:22:08  Seqno 518, RBA 218649600

                     SCN 0.74964754 (74964754)

GGSCI (uatdb2) 24>

Target :

GGSCI (uatdb1) 14> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    RUNNING     REPL        00:00:00      00:00:05   

GGSCI (uatdb1) 16> info replicat repl

REPLICAT   REPL      Last Started 2016-08-23 18:29   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:06 ago)

Log Read Checkpoint  File /u10/gg_soft/dirdat/ld000000

                     2016-08-24 11:06:14.880411  RBA 1769

GGSCI (uatdb1) 17>

So, in source extract process is running successfully and in target replication process is running successfully. If extract process is showing “STOPPED”, then replication will not continue though replication process in running status.

Be careful while creating your configuration files. Missing or wrong spelled of any character including space may lead configuration issues and manager process will not start:

Test case:

Now insert/ update few data in source and commit them. Check in target side whether replicated or not.

Errors you may face:

Missing of grants on golden gate user may lead below Oracle error: 

    

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation 

error

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

FROM  ."GGS_SETUP" ERROR at line 2: ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP" ERROR at line 2:ORA-00942: table or view does not exist

error in GoldenGate while setting up ....

$ cd /u10/gg_soft

$ sqlplus / as sysdba

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

SQL> @ddl_enable.sql

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

FROM  ."GGS_SETUP" ERROR at line 2: ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP" ERROR at line 2:ORA-00942: table or view does not exist

DDL TRIGGER RUNNING STATUS

-----------------------------------

ENABLED

FROM "GGATE" ."GGS_SETUP"

              *

ERROR at line 2:

ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP"

              *

ERROR at line 2:

ORA-00942: table or view does not exist

FROM "GGATE" ."GGS_SETUP"

              *

ERROR at line 2:

ORA-00942: table or view does not exist

==============================

Fix:

drop this trigger with login as SYSDBA.

SQL> drop TRIGGER SYS.GGS_DDL_TRIGGER_BEFORE ;

Trigger dropped.

Then Run all the grants as given above and continue the below process again.

$ sqlplus / as sysdba

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggs_owner;

SQL> @ddl_enable.sql

Now you can start configuration process.

Replicate process in “ABENDED” state. Not able to start replicate process

-- In target side( showing sample scenario)

GGSCI (uatdb1) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    ABENDED     REPL        00:00:00      00:00:41   

GGSCI (uatdb1) 19>

Fix:

GGSCI (uatdb1) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    ABENDED     REPL        00:00:00      00:03:30   

GGSCI (uatdb1) 21> start replicat repl

Sending START request to MANAGER ...

REPLICAT REPL starting

GGSCI (uatdb1) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

REPLICAT    RUNNING     REPL        00:00:00      00:03:49   

Replicate process constantly going down:

Error reports from “ggserr.log”

$ pwd

/u10/gg_soft

$ tail -100f ggserr.log

2016-08-25 12:40:59  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, repl.prm:  Failed to retrieve column list handle for table SCOTT.GG_TEST.

2016-08-25 12:40:59  ERROR   OGG-00199  Oracle GoldenGate Delivery for Oracle, repl.prm:  Table scott.GG_TEST does not exist in target database.

2016-08-25 12:40:59  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, repl.prm:  PROCESS ABENDING.

Fix:

scott.GG_TEST table DDL is not available in Target. If DDL replicate is not enable, then initial data must be imported.

Enabling DDL replication:

Follow the below steps to configure DDL replication.

1    1) 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 _RECYCLEBI

N in parameter file to FALSE. Then restart database and extract.

2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.

-- source

SQL> alter session set recyclebin=OFF;

Session altered.

     2) Run ddl_pin for golden gate user

e.g.,

SQL> @ddl_pin GGS_OWNER

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

   3)  Add "DDL INCLUDE MAPPED"in "repl" parameter:

e.g.,

GGSCI (uatdb2) 1> edit params extl

extract extl

userid ggs_owner, password ggs_owner

rmthost 172.18.1.17, mgrport 7810

RMTTRAIL /u10/gg_soft/dirdat/ld

DDL INCLUDE MAPPED

table scott.*;

:wq!

Now you can start Extract process in source and replication process in target. Create a  table in source and you able to see same table in target also.

 Hope this document may helpful for you.

Test yourself????

1) When implementing GoldenGate on a 11gR2 Oracle database and after running the GoldenGate Database profiling script, you discover that certain objects are compressed, what should you do?

A. Contact Oracle support
B. Continue the implementation of OGG and exclude those objects from replication
C. Configure OGG with Classic Capture
D. Configure OGG with Integrated Capture

2) Which Replicat parameter is used when looking up table definitions in a file?
A. SPECIALRUN
B. DEFERAPPLYINTERVAL
C. SOURCEDEFS
D. SOURCEISFILE

3) Select the function that stores environmental values.
A. GGENVIRONMENT
B. @COLGEN
C. @GETENV
D. ETOKEN

4) Select the statement that is supported by OGG DDL Replication.
A. CREATE ROLE 
B. ALTGLOBAL
C. ENCRYPT
D. ALTDB

5) Select the commands that give statistics regarding OGG process and memory resource usage.
A. SEND…REPORT
B. SEND...CACHEMGR
C. SEND…STATS
D. INFO…SHOWSTATS

Now verify your answers with below correct ansers.

Ans:
1) D
2) C
3) C
4) A

5) B

How to manage and monitor Oracle Golden Gate?

Overview of the Oracle GoldenGate monitoring tools
You can monitor Oracle GoldenGate processing to view process status, statistics, and
events by using the following tools.

GGSCI information commands:

The ggserr.log file (known as the error log)
Process reports
The discard file
The Event Viewer on Windows systems or the syslog on UNIX systems to view errors at the operating-system level

-- source

[oracle@uatdb2 gg_soft]$ ./ggsci

-- Shoing configuration

GGSCI (uatdb2) 1> show all;

Parameter settings:

SET SUBDIRS    ON
SET DEBUG      OFF

Current directory: /u10/gg_soft

Using subdirectories for all process files

Editor:  vi

Reports (.rpt)                 /u10/gg_soft/dirrpt
Parameters (.prm)              /u10/gg_soft/dirprm
Stdout (.out)                  /u10/gg_soft/dirout
Replicat Checkpoints (.cpr)    /u10/gg_soft/dirchk
Extract Checkpoints (.cpe)     /u10/gg_soft/dirchk
Process Status (.pcs)          /u10/gg_soft/dirpcs
SQL Scripts (.sql)             /u10/gg_soft/dirsql
Database Definitions (.def)    /u10/gg_soft/dirdef


GGSCI (uatdb2) 2>

-- what process is running?

GGSCI (uatdb2) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXTL        00:00:00      00:00:08  


GGSCI (uatdb2) 4>


Using the error log:

Use the Oracle GoldenGate error log to view:
a history of GGSCI commands
Oracle GoldenGate processes that started and stopped
processing that was performed
errors that occurred
informational and warning messages
Because the error log shows events as they occurred in sequence, it is a good tool for
detecting the cause (or causes) of an error. For example, you might discover that:

someone stopped a process
a process failed to make a TCP/IP or database connection
a process could not open a file


To view the error log:

Use any of the following:
Standard shell command to view the ggserr.log file within the root Oracle GoldenGate directory
Oracle GoldenGate Director
VIEW GGSEVT command in GGSCI

To filter the error log:

The error log can become very large, but you can filter it based on a keyword. For example,
this filter show only errors:

$ more ggserr.log | grep ERROR

Because the error log will continue to grow as you use Oracle GoldenGate, consider archiving and deleting the oldest entries in the file.

NOTE- The Collector process might stop reporting to the log on UNIX systems after the log has been cleaned up. To get reporting started again, restart the Collector process after the cleanup

E.g.,

export GG_HOME=/u10/gg_soft
$cd $GG_HOME
$ tail -100f ggserr.log


OR

use VIEW GGSEVT. This also shows Contents of the Oracle GoldenGate error log


GGSCI (uatdb2) 3> VIEW GGSEVT


-- INFO MANAGER | STATUS MANAGER
-- Run status and port number

source :

GGSCI (uatdb2) 1> INFO MANAGER

Manager is running (IP port uatdb2.7809).
OR
GGSCI (uatdb2) 2> STATUS MANAGER

Manager is running (IP port uatdb2.7809).
(same output)

Target:

GGSCI (uatdb1) 2> INFO MANAGER

Manager is running (IP port uatdb1.7810).


GGSCI (uatdb1) 3>

-- Monitor status of EXTRACT | REPLICAT lagging
-- Latency between last record processed and timestamp in the data source

LAG {EXTRACT | REPLICAT} group

Run status (starting, running, stopped, abended)
GGSCI (uatdb2) 1> LAG EXTRACT extl

Sending GETLAG request to EXTRACT EXTL ...
Last record lag: 2 seconds.
At EOF, no more records to process.


GGSCI (uatdb2) 2>

Note: EXTL is my extract process name

-- You can use wild card character like below

GGSCI (uatdb2) 11> INFO EXTRACT ext*

EXTRACT    EXTL      Last Started 2016-08-25 13:17   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2016-09-01 15:29:04  Seqno 608, RBA 87513088
                     SCN 0.78601425 (78601425)


GGSCI (uatdb2) 12>

-- VIEW THE REPORTS
-- Contents of the process report
-- Using VIEW REPORT EXTL


GGSCI (uatdb2) 1> VIEW REPORT EXTL


***********************************************************************
                 Oracle GoldenGate Capture for Oracle
    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2016-08-25 13:17:26
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Jun 13 18:24:36 EDT 2012, Release 2.6.32-279.el6.x86_64
Node: uatdb2
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: 28838

Description:
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2016-08-25 13:17:26  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
extract extl
userid ggs_owner, password *********
rmthost 172.18.1.17, mgrport 7810
RMTTRAIL /u10/gg_soft/dirdat/ld
DDL INCLUDE MAPPED
table scott.*;

2016-08-25 13:17:27  INFO    OGG-01815  Virtual Memory Facilities for: BR
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u10/gg_soft/BR/EXTL.

Bounded Recovery Parameter:
BRINTERVAL = 4HOURS
BRDIR      = /u10/gg_soft

2016-08-25 13:17:27  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u10/gg_soft/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                               64G
CACHEPAGEOUTSIZE (normal):                8M
PROCESS VM AVAIL FROM OS (min):         128G
CACHESIZEMAX (strict force to disk):     96G

2016-08-25 13:17:27  INFO    OGG-01639  BOUNDED RECOVERY:  ACTIVE: for object pool 1: p6998_extr.

2016-08-25 13:17:27  INFO    OGG-01640  BOUNDED RECOVERY: recovery start XID: 0.0.0.

2016-08-25 13:17:27  INFO    OGG-01641  BOUNDED RECOVERY: recovery start position: SeqNo: 525, RBA: 293134864, SCN: 0.75295971 (75295971), Timestamp
: 2016-08-25 00:19:40.000000, Thread: 1.

2016-08-25 13:17:27  INFO    OGG-01642  BOUNDED RECOVERY: recovery end position: SeqNo: 525, RBA: 294132224, SCN: 0.75295971 (75295971), Timestamp:
2016-08-25 00:19:40.000000, Thread: 1.

2016-08-25 13:17:27  INFO    OGG-01643  BOUNDED RECOVERY: CANCELED: for object pool 1: p6998_extr.

2016-08-25 13:17:27  INFO    OGG-01579  BOUNDED RECOVERY: VALID BCP: CP.EXTL.000000010.

2016-08-25 13:17:27  INFO    OGG-01629  BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: <<NONE TO RECOVER>>.

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

.....

-- VIEW GGSEVT Contents of the Oracle GoldenGate error log

e.g,

GGSCI (uatdb2) 3>  VIEW GGSEVT

2016-08-23 18:02:30  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add trandata scott.*.
2016-08-23 18:05:39  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add trandata scott.*.
2016-08-23 18:07:24  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit params mgr.
2016-08-23 18:11:39  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add extract extl  tranlog, begin now.
2016-08-23 18:13:36  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): add exttrail /u10/gg_soft/dirdat/ld  extract extl.
2016-08-23 18:14:07  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit params extl.
2016-08-23 18:30:25  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2016-08-23 18:30:25  INFO    OGG-00983  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7809).
2016-08-23 18:31:52  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start EXTL.
2016-08-23 18:31:52  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host uatdb2 (START EXTRACT EXTL ).
.....


Monitoring processing volume:


The volume statistics show you the amount of data that is being processed by an Oracle
GoldenGate process, and how fast it is being moved through the Oracle GoldenGate
system. With this information, you can diagnose suspected problems and tune the
performance of the Oracle GoldenGate processes.
To view volume statistics

Syntax STATS {EXTRACT | REPLICAT | ER} {<group | wildcard>}
[TABLE {<name | wildcard>}]

bELOW Sample basic STATS EXTRACT for one table


GGSCI (uatdb2) 5> stats extract EXTL

Sending STATS request to EXTRACT EXTL ...

Start of Statistics at 2016-09-02 12:51:43.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         8.00
        Mapped operations                                  4.00
        Unmapped operations                                4.00
        Other operations                                   0.00
        Excluded operations                                4.00

Output to /u10/gg_soft/dirdat/ld:

Extracting from GGS_OWNER.GGS_MARKER to GGS_OWNER.GGS_MARKER:

*** Total statistics since 2016-08-25 13:17:35 ***

        No database operations have been performed.

*** Daily statistics since 2016-09-02 00:00:00 ***

        No database operations have been performed.

*** Hourly statistics since 2016-09-02 12:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2016-08-25 13:17:35 ***

        No database operations have been performed.

Extracting from SCOTT.GG_TEST3 to SCOTT.GG_TEST3:

*** Total statistics since 2016-08-25 13:17:35 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-09-02 00:00:00 ***

        No database operations have been performed.

*** Hourly statistics since 2016-09-02 12:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2016-08-25 13:17:35 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

Extracting from SCOTT.EMPLOYEE to SCOTT.EMPLOYEE:

*** Total statistics since 2016-08-25 13:17:35 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2016-09-02 00:00:00 ***

        No database operations have been performed.

*** Hourly statistics since 2016-09-02 12:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2016-08-25 13:17:35 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.


GGSCI (uatdb2) 6>

In target :

GGSCI (uatdb1) 10> STATS REPLICAT REPL

Sending STATS request to REPLICAT REPL ...

Start of Statistics at 2016-09-02 13:39:10.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                         4.00
        Mapped operations                                  4.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00
        Errors                                             0.00
        Retried errors                                     0.00
        Discarded errors                                   0.00
        Ignored errors                                     0.00

Replicating from SCOTT.GG_TEST3 to SCOTT.GG_TEST3:

*** Total statistics since 2016-08-25 13:12:40 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-09-02 00:00:00 ***

        No database operations have been performed.

*** Hourly statistics since 2016-09-02 13:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2016-08-25 13:12:40 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

Replicating from SCOTT.EMPLOYEE to SCOTT.EMPLOYEE:

*** Total statistics since 2016-08-25 13:12:40 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2016-09-02 00:00:00 ***

        No database operations have been performed.

*** Hourly statistics since 2016-09-02 13:00:00 ***

        No database operations have been performed.

*** Latest statistics since 2016-08-25 13:12:40 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.


GGSCI (uatdb1) 11>


-- To limit the types of statistics that are displayed:

Syntax STATS {EXTRACT | REPLICAT | ER} {<group | wildcard>},
{TOTAL | DAILY | HOURLY | LATEST}


E.g.,
GGSCI (uatdb1) 13> STATS REPLICAT REPL HOURLY

GGSCI (uatdb1) 14> STATS REPLICAT REPL LATEST



-- To clear all filters that were set with previous options:

Syntax STATS {EXTRACT | REPLICAT | ER} {<group | wildcard>}, RESET

To send interim statistics to the report file
Syntax SEND {EXTRACT | REPLICAT | ER} {<group | wildcard>}, REPORT

E.g.,

GGSCI (uatdb2) 9> STATS  EXTRACT EXTL, RESET

Sending STATS request to EXTRACT EXTL ...

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         9.00
        Mapped operations                                  5.00
        Unmapped operations                                4.00
        Other operations                                   0.00
        Excluded operations                                4.00

Successfully reset statistics.


GGSCI (uatdb2) 10>

GGSCI (uatdb2) 8> SEND EXTRACT EXTL, REPORT

Sending REPORT request to EXTRACT EXTL ...
Request processed.


GGSCI (uatdb2) 9>


.....