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:

Hardware and OS configuration
Database Pre-requisites
Golden Gate software installation
Create manager process at source and target
Create Extract process at source
Create Replicate process at Target
Create Checkpoint table at source
Initial Dataload target
Start Extract process - source
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

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

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

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

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.

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>

.....

Gouranga's Tech Blog

Pages

Aug 25, 2016

Golden Gate : Uni-direction schema level replication

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

Translate >>

Pages