Heartbeat table in Oracle Goldengate

Managing heartbeat in goldengate is important specially when it comes to monitor the lag and the heartbeat, this post covers how to add a heartbeat table at target, clean it up and reconfigure the heartbeat table

GGSCI (rhel7.example.com as ggadmin@euro) 3> add heartbeattable

2020-07-13 00:22:30  INFO    OGG-14001  Successfully created heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14032  Successfully added supplemental logging for heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14000  Successfully created heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14033  Successfully added supplemental logging for heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14016  Successfully created heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14086  Successfully disabled partitioning for heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14023  Successfully created heartbeat lag view ""GGADMIN"."GG_LAG"".

2020-07-13 00:22:30  INFO    OGG-14024  Successfully created heartbeat lag history view ""GGADMIN"."GG_LAG_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14003  Successfully populated heartbeat seed table with "EURO".

2020-07-13 00:22:31  INFO    OGG-14004  Successfully created procedure ""GGADMIN"."GG_UPDATE_HB_TAB"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14017  Successfully created procedure ""GGADMIN"."GG_PURGE_HB_TAB"" to purge the heartbeat history table.

2020-07-13 00:22:31  INFO    OGG-14005  Successfully created scheduler job ""GGADMIN"."GG_UPDATE_HEARTBEATS"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14018  Successfully created scheduler job ""GGADMIN"."GG_PURGE_HEARTBEATS"" to purge the heartbeat history table.


GGSCI (rhel7.example.com as ggadmin@euro) 2> delete heartbeattable

2020-07-13 00:22:25  INFO    OGG-14007  Heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"" dropped.

2020-07-13 00:22:25  INFO    OGG-14009  Heartbeat table ""GGADMIN"."GG_HEARTBEAT"" dropped.

2020-07-13 00:22:25  INFO    OGG-14011  Heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"" dropped.

2020-07-13 00:22:25  INFO    OGG-14026  Heartbeat lag view ""GGADMIN"."GG_LAG"" dropped.

2020-07-13 00:22:25  INFO    OGG-14028  Heartbeat lag history view ""GGADMIN"."GG_LAG_HISTORY"" dropped.

2020-07-13 00:22:25  INFO    OGG-14013  Procedure ""GGADMIN"."GG_UPDATE_HB_TAB"" dropped.

2020-07-13 00:22:25  INFO    OGG-14020  Procedure ""GGADMIN"."GG_PURGE_HB_TAB"" dropped.

2020-07-13 00:22:25  INFO    OGG-14015  Scheduler job ""GGADMIN"."GG_UPDATE_HEARTBEATS"" dropped.

2020-07-13 00:22:25  INFO    OGG-14022  Scheduler job ""GGADMIN"."GG_PURGE_HEARTBEATS"" dropped.

GGSCI (rhel7.example.com as ggadmin@euro) 3> add heartbeattable

2020-07-13 00:22:30  INFO    OGG-14001  Successfully created heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14032  Successfully added supplemental logging for heartbeat seed table ""GGADMIN"."GG_HEARTBEAT_SEED"".

2020-07-13 00:22:30  INFO    OGG-14000  Successfully created heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14033  Successfully added supplemental logging for heartbeat table ""GGADMIN"."GG_HEARTBEAT"".

2020-07-13 00:22:30  INFO    OGG-14016  Successfully created heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14089  Successfully tracking extract restart position with heartbeat table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14086  Successfully disabled partitioning for heartbeat history table ""GGADMIN"."GG_HEARTBEAT_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14023  Successfully created heartbeat lag view ""GGADMIN"."GG_LAG"".

2020-07-13 00:22:30  INFO    OGG-14024  Successfully created heartbeat lag history view ""GGADMIN"."GG_LAG_HISTORY"".

2020-07-13 00:22:30  INFO    OGG-14003  Successfully populated heartbeat seed table with "EURO".

2020-07-13 00:22:31  INFO    OGG-14004  Successfully created procedure ""GGADMIN"."GG_UPDATE_HB_TAB"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14017  Successfully created procedure ""GGADMIN"."GG_PURGE_HB_TAB"" to purge the heartbeat history table.

2020-07-13 00:22:31  INFO    OGG-14005  Successfully created scheduler job ""GGADMIN"."GG_UPDATE_HEARTBEATS"" to update the heartbeat tables.

2020-07-13 00:22:31  INFO    OGG-14018  Successfully created scheduler job ""GGADMIN"."GG_PURGE_HEARTBEATS"" to purge the heartbeat history table.

How to know if my replicat is Integrated or not ?

In the below output of Report file of the replicat process, you see the message "Integrated replicat successfully attached to inbound server..." That means your replicat is integrated not the old one ....

GGSCI (node7.example.com as oggadmin@orcl) 62> view report rep4


***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
   Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
   Linux, x64, 64bit (optimized), Oracle 12c on Aug  7 2014 10:47:47
............................
..................................
.........................................

Output truncated...
.........................

2016-06-02 00:59:07  INFO    OGG-02530  Integrated replicat successfully attached to inbound server OGG$rep4.

Is my Extract Classic or Intergrated ?

Recently i was asked how to know if my extract is classic extract or integrated extract, here is how we can know about it

If below output is Oracle Integrated Redo Logs for Log Read Checkpoint then it is integrated extract

GGSCI (node7.example.com as gguser@orcl) 78> info extract extcap

EXTRACT    extcap   Last Started 2016-06-01 23:39   Status STOPPED
Checkpoint Lag       00:00:04 (updated 00:05:43 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2016-06-02 00:47:48
                     SCN 0.2037575 (2037575)

If below output if it is Oracle Redo Logs for Log Read Checkpoint then it is classic extract

GGSCI (node7.example.com as gguser@orcl) 79> info ext2

EXTRACT    EXT2     Last Started 2016-06-02 00:50   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Process ID           14091
Log Read Checkpoint  Oracle Redo Logs
                     2016-06-02 00:53:10  Seqno 35, RBA 10232832
                     SCN 0.2038468 (2038468)

LOGDUMP Utility in Goldengate

To view the record header with the data:

Logdump 1> GHDR ON

The record header contains information about the transaction.

To add column information:

Logdump 2> DETAIL ON

Column information includes the number and length in hex and ASCII.

To add hex and ASCII data values to the column information:

Logdump 3> DETAIL DATA

To view user tokens:

Logdump 4> USERTOKEN ON

User tokens are custom user-defined information that is specified in a TABLE or FILE mapping statement and stored in the trail file for specific purposes.

To view automatically generated tokens:

Logdump 4> GGSTOKEN ON
Oracle GoldenGate automatically generated tokes include the transaction ID (XID), the row id for DML operations, the fetching status (if applicable), and tag value.

To control how much record data is displayed:

Logdump 5> RECLEN length
______________________________


I will now insert 3 rows in T1 Table

SQL> insert into t1 values(3,3,3);

1 row created.

SQL> commit;

Commit complete.

SQL>  insert into t1 values(4,4,4);

1 row created.

SQL> commit;

Commit complete.

SQL>  insert into t1 values(5,5,5);

1 row created.

SQL> commit;

Commit complete.

## Now lets see how we can read the data with above explanations :-

[oracle@edvmr1p0 gg_$$$]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1

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



Logdump 25 >DETAIL ON
Logdump 26 >GHDR ON
Logdump 27 >DETAIL DATA
Logdump 28 >USERTOEN ON
sh: USERTOEN: command not found

Logdump 29 >USERTOKEN ON
Logdump 30 >n
Error: Logtrail not opened
Logdump 31 >open dirdat/rt000000
Current LogTrail is /u01/app/oracle/product/gg_$$$/dirdat/rt000000
Logdump 32 >n

2016/05/31 19:42:54.395.268 FileHeader           Len  1414 RBA 0
Name: *FileHeader*
 3000 0326 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..&0...GG..TL..1...
 0004 3200 0004 2000 0000 3300 0008 02f2 5a57 6cd5 | ..2... ...3.....ZWl.
 1d84 3400 0033 0031 7572 693a 6564 766d 7231 7030 | ..4..3.1uri:edvmr1p0
 3a3a 7530 313a 6170 703a 6f72 6163 6c65 3a70 726f | ::u01:app:oracle:pro
 6475 6374 3a67 675f 616d 6572 3a45 5854 3136 0000 | duct:gg_amer:EXT16..
 3100 2f2f 7530 312f 6170 702f 6f72 6163 6c65 2f70 | 1.//u01/app/oracle/p
 726f 6475 6374 2f67 675f 6575 726f 2f64 6972 6461 | roduct/gg_$$$/dirda

Logdump 33 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    27  (x001b)   IO Time    : 2016/05/31 19:43:01.000.232
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         23       AuditPos   : 40069136
Continued  :     N  (x00)     RecCount   :     1  (x01)

2016/05/31 19:43:01.000.232 Insert               Len    27 RBA 1422
Name: SCOTT.T1
After  Image:                                             Partition 4   G  s
 0000 0005 0000 0001 3300 0100 0500 0000 0133 0002 | ........3........3..
 0005 0000 0001 33                                 | ......3
Column     0 (x0000), Len     5 (x0005)
 0000 0001 33                                      | ....3
Column     1 (x0001), Len     5 (x0005)
 0000 0001 33                                      | ....3
Column     2 (x0002), Len     5 (x0005)
 0000 0001 33                                      | ....3

### Here we see values 3,3,3

Logdump 34 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    27  (x001b)   IO Time    : 2016/05/31 19:49:12.000.372
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         23       AuditPos   : 40925712
Continued  :     N  (x00)     RecCount   :     1  (x01)

2016/05/31 19:49:12.000.372 Insert               Len    27 RBA 1563
Name: SCOTT.T1
After  Image:                                             Partition 4   G  s
 0000 0005 0000 0001 3400 0100 0500 0000 0134 0002 | ........4........4..
 0005 0000 0001 34                                 | ......4
Column     0 (x0000), Len     5 (x0005)
 0000 0001 34                                      | ....4
Column     1 (x0001), Len     5 (x0005)
 0000 0001 34                                      | ....4
Column     2 (x0002), Len     5 (x0005)
 0000 0001 34                                      | ....4

### Here we see values 4,4,4

Logdump 35 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    27  (x001b)   IO Time    : 2016/05/31 19:59:19.000.300
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         23       AuditPos   : 41101328
Continued  :     N  (x00)     RecCount   :     1  (x01)

2016/05/31 19:59:19.000.300 Insert               Len    27 RBA 1700
Name: SCOTT.T1
After  Image:                                             Partition 4   G  s
 0000 0005 0000 0001 3500 0100 0500 0000 0135 0002 | ........5........5..
 0005 0000 0001 35                                 | ......5
Column     0 (x0000), Len     5 (x0005)
 0000 0001 35                                      | ....5
Column     1 (x0001), Len     5 (x0005)
 0000 0001 35                                      | ....5
Column     2 (x0002), Len     5 (x0005)
 0000 0001 35                                      | ....5

### Here we see values 5,5,5

LAG REPLICAT Command

LAG REPLICAT command is to determine a true lag time between Replicat and the trail. LAG REPLICAT estimates the lag time more precisely than INFO REPLICAT because it communicates with Replicat directly rather than reading a checkpoint position.

For Replicat, lag is the difference, in seconds, between the time that the last record was processed by Replicat (based on the system clock) and the timestamp of the record in the trail.

An example :-

GGSCI (node07.example.com) 2> lag replicat rep1

Sending GETLAG request to REPLICAT REP1 ...
Last record lag 5 seconds.
At EOF, no more records to process.

LAG EXTRACT Command

LAG EXTRACT is the command to determine a true lag time between Extract and the data source. LAG EXTRACT calculates the lag time more precisely than INFO EXTRACT because it communicates with Extract directly, rather than reading a checkpoint position in the trail.

For Extract, lag is the difference, in seconds, between the time that a record was processed by Extract (based on the system clock) and the timestamp of that record in the data source.

An example :-

GGSCI (node07.example.com) 2> lag extract ext1

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

Bounded Recovery in Oracle Goldengate

Bounded Recovery

Bounded Recovery is a component of the general Extract checkpointing facility. It guarantees an efficient recovery after Extract stops for any reason, planned or unplanned, no matter how many open (uncommitted) transactions there were at the time that Extract stopped, nor how old they were. Bounded Recovery sets an upper boundary for the maximum amount of time that it would take for Extract to recover to the point where it stopped and then resume normal processing.

Caution: Before changing this parameter from its default settings, contact Oracle Support for guidance. Most production environments will not require changes to this parameter. You can, however, specify the directory for the Bounded Recovery checkpoint files without assistance.

How Extract Recovers Open Transactions

When Extract encounters the start of a transaction in the redo log (in Oracle, this is the first executable SQL statement) it starts caching to memory all of the data that is specified to be captured for that transaction. Extract must cache a transaction even if it contains no captured data, because future operations of that transaction might contain data that is to be captured.

When Extract encounters a commit record for a transaction, it writes the entire cached transaction to the trail and clears it from memory. When Extract encounters a rollback record for a transaction, it discards the entire transaction from memory. Until Extract processes a commit or rollback, the transaction is considered open and its information continues to be collected.

If Extract stops before it encounters a commit or rollback record for a transaction, all of the cached information must be recovered when Extract starts again. This applies to all transactions that were open at the time that Extract stopped.

Extract performs this recovery as follows:

If there were no open transactions when Extract stopped, the recovery begins at the current Extract read checkpoint. This is a normal recovery.

If there were open transactions whose start points in the log were very close in time to the time when Extract stopped, Extract begins recovery by re-reading the logs from the beginning of the oldest open transaction. This requires Extract to do redundant work for transactions that were already written to the trail or discarded before Extract stopped, but that work is an acceptable cost given the relatively small amount of data to process. This also is considered a normal recovery.

If there were one or more transactions that Extract qualified as long-running open transactions, Extract begins its recovery with a Bounded Recovery.

How Bounded Recovery Works

A transaction qualifies as long-running if it has been open longer than one Bounded Recovery interval, which is specified with the BRINTERVAL option of the BR parameter. For example, if the Bounded Recovery interval is four hours, a long-running open transaction is any transaction that started more than four hours ago.

At each Bounded Recovery interval, Extract makes a Bounded Recovery checkpoint, which persists the current state and data of Extract to disk, including the state and data (if any) of long-running transactions. If Extract stops after a Bounded Recovery checkpoint, it will recover from a position within the previous Bounded Recovery interval or at the last Bounded Recovery checkpoint, instead of processing from the log position where the oldest open long-running transaction first appeared.

The maximum Bounded Recovery time (maximum time for Extract to recover to where it stopped) is never more than twice the current Bounded Recovery checkpoint interval. The actual recovery time will be a factor of the following:

# the time from the last valid Bounded Recovery interval to when Extract stopped.

# the utilization of Extract in that period.

# the percent of utilization for transactions that were previously written to the trail. Bounded Recovery processes these transactions much faster (by discarding them) than Extract did when it first had to perform the disk writes. This constitutes most of the reprocessing that occurs for transactional data.

When Extract recovers, it restores the persisted data and state that were saved at the last Bounded Recovery checkpoint (including that of any long running transactions).

For example, suppose a transaction has been open for 24 hours, and suppose the Bounded Recovery interval is four hours. In this case, the maximum recovery time will be no longer than eight hours worth of Extract processing time, and is likely to be less. It depends on when Extract stopped relative to the last valid Bounded Recovery checkpoint, as well as Extract activity during that time.

Advantages of Bounded Recovery

The use of disk persistence to store and then recover long-running transactions enables Extract to manage a situation that rarely arises but would otherwise significantly (adversely) affect performance if it occurred. The beginning of a long-running transaction is often very far back in time from the place in the log where Extract was processing when it stopped. A long-running transaction can span numerous old logs, some of which might no longer reside on accessible storage or might even have been deleted. Not only would it take an unacceptable amount of time to read the logs again from the start of a long-running transaction but, since long-running transactions are rare, most of that work would be the redundant capture of other transactions that were already written to the trail or discarded. Being able to restore the state and data of persisted long-running transactions eliminates that work.

ERROR OGG-01201 Error reported by MGR : Access denied

Today i faced an issue in goldengate

2016-05-27 15:55:42  ERROR   OGG-01201  Error reported by MGR : Access denied.

2016-05-27 15:55:42  ERROR   OGG-01668  PROCESS ABENDING.


What happens in Oracle GoldenGate 12.2 is that MANAGER and related EXTRACT/REPLICAT cannot be started or stopped remotely. So what happens is when the direct load is started on the source server it tries to start replicat remotely on the target server, so this leads to ERROR OGG-01201  Error reported by MGR : Access denied, and this error will be reported in report file of the process.

The solution and the fix i found is that it is necessary to write below lines in the remote manager parameter file  ACCESSRULE, PROG *, IPADDR 192.168.1.161, ALLOW

This will allow source server to make connection and start the replicat process remotely

Integrated Replicat in Goldengate

Integrated Replicat or also known as Inetgrated Deliver has been introduced from 11.2.0.4 onwards that means it is also available in 12C, In integrated mode, the Replicat process leverages the apply processing functionality that is available within the Oracle database. In this mode, Replicat operates as follows:

# Reads the Oracle GoldenGate trail.

# Performs data filtering, mapping, and conversion.

# Constructs logical change records (LCR) that represent source database DML transactions (in committed order). DDL is applied directly by Replicat.

# Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface.

# Transmits the LCRs to the inbound server, which applies the data to the target database.


Below is an image which lists the example of an integrated apply with 2 Apply Servers

Errors in Goldengate Scenario 4

Scenario 4


ERROR   OGG-00396  Command 'MAP' not terminated by semi-colon.

ERROR   OGG-01668  PROCESS ABENDING.

## This error message clearly specifies MAP command not finished with semi- colon, so below is how we fixed it :-

## Now you will see that semicolon is clearly mentioned :-

GGSCI (node4.example.com as ogguser@euro) 61> edit params rep1

GGSCI (node4.example.com as ogguser@euro) 62> view params rep1

REPLICAT REP1
ASSUMETARGETDEFS
USERID ogguser, PASSWORD o
MAP scott.emp4, TARGET scott.emp4;


GGSCI (node4.example.com as ogguser@euro) 63> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (node4.example.com as ogguser@euro) 64> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
REPLICAT    RUNNING     REP1        00:00:00      00:05:43


__________________

Errors in Goldengate Scenario 3

Scenario 3

Error Message :- ERROR   OGG-01044  The trail '/u01/app/oracle/product/ogg_trg/dirdat/rg' is not assigned to extract 'EXT1'. Assign the trail to the extract with the command "ADD EXTTRAIL/RMTTRAIL /u01/app/oracle/product/ogg_trg/dirdat/rg, EXTRACT EXT1".

2015-09-26 17:34:57  ERROR   OGG-01668  PROCESS ABENDING.


GGSCI (node4.example.com as ogguser@amer) 49> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
EXTRACT     ABENDED     EXT1        00:00:00      00:07:30

## After checking we found that remote trail file name was not correct so we fixed it solve it as per below details :-

## Let check what exact remote should the extract send to, so its the rt file not rg which was observed in the error log

GGSCI (node4.example.com as ogguser@amer) 51> info extract ext1, detail

EXTRACT    EXT1      Last Started 2015-09-26 17:34   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:09:14 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-09-26 17:30:07  Seqno 20, RBA 25548800
                     SCN 0.2082218 (2082218)

  Target Extract Trails:

  Trail Name                                       Seqno        RBA     Max MB Trail Type

  /u01/app/oracle/product/ogg_trg/dirdat/rt            0       1964        100 RMTTRAIL


## We can also check from below command :-


GGSCI (node4.example.com as ogguser@amer) 52> info rmttrail *

       Extract Trail: /u01/app/oracle/product/ogg_trg/dirdat/rt
             Extract: EXT1
               Seqno: 0
                 RBA: 1964
           File Size: 100M

## Below are details of how we have fixed it :-

GGSCI (node4.example.com as ogguser@amer) 54> edit params ext1



GGSCI (node4.example.com as ogguser@amer) 55> view params ext1

EXTRACT ext1
userid ogguser, password o
RMTHOST node4.example.com , mgrport 7909
rmttrail /u01/app/oracle/product/ogg_trg/dirdat/rt
TABLE SCOTT.EMP4;



GGSCI (node4.example.com as ogguser@amer) 56> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (node4.example.com as ogguser@amer) 57> info all    

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
EXTRACT     RUNNING     EXT1        00:10:54      00:00:05

Errors in Goldengate Scenario 2

# Scenario 2

WARNING OGG-01223  TCP/IP error 111 (Connection refused), endpoint: node4.example.com:7907.

INFO    OGG-01971  The previous message, 'WARNING OGG-01223', repeated 2 times.


Whenever there is problem with extract always check whether it is able to capture the records or not, this will be done by stats extract command from GGSCI prompt

GGSCI (node4.example.com as ogguser@amer) 42> stats extract ext1

Sending STATS request to EXTRACT EXT1 ...

No active extraction maps.

## this means that extract is not able to capture the records as of now

We check the extract report file in location

/u01/app/oracle/product/ogg_src/dirrpt

and the report file name is EXT1.rpt

Below is the error details :-

2015-09-26 17:31:20  WARNING OGG-01223  TCP/IP error 111 (Connection refused), endpoint: node4.example.com:7907.

2015-09-26 17:31:50  INFO    OGG-01971  The previous message, 'WARNING OGG-01223', repeated 2 times.

## After reading the above error message, we came to know that the MGR port number was not correct in extract parameter file, so after correcting it we are able to solve it, lets check how

GGSCI (node4.example.com as ogguser@amer) 37> view params ext1


EXTRACT ext1
userid ogguser, password o
RMTHOST node4.example.com , mgrport 7907
rmttrail /u01/app/oracle/product/ogg_trg/dirdat/rt
TABLE SCOTT.EMP4;

## After correcting the port number to 7909


GGSCI (node4.example.com as ogguser@amer) 47> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting



GGSCI (node4.example.com as ogguser@amer) 44> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
EXTRACT     RUNNING     EXT1        00:00:00      00:02:10

Errors in Goldengate Scenario 1

 Scenario 1

ERROR   OGG-00446  Missing filename opening checkpoint file.

ERROR   OGG-01668  PROCESS ABENDING.

___

Checking the replicat status

GGSCI (node4.example.com as ogguser@euro) 45> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                        
REPLICAT    STOPPED     REP1        00:00:00      00:00:06


Below is the error :-

2015-09-26 17:26:03  ERROR   OGG-00446  Missing filename opening checkpoint file.

2015-09-26 17:26:03  ERROR   OGG-01668  PROCESS ABENDING.


## this means that in replicat file keyword REPLICAT rep_group name is not written, lets fix this :-

edit params rep1

GGSCI (node4.example.com as ogguser@euro) 49> view params rep1

REPLICAT REP1
ASSUMETARGETDEFS
USERID ogguser, PASSWORD o
MAP scott.emp4, TARGET scott.emp4;


# Now we have updated REPLICAT REP1


GGSCI (node4.example.com as ogguser@euro) 52> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (node4.example.com as ogguser@euro) 53> info replicat rep1

REPLICAT   REP1      Last Started 2015-09-26 17:28   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
Process ID           9086
Log Read Checkpoint  File /u01/app/oracle/product/ogg_trg/dirdat/rt000000
                     2015-09-26 16:49:45.000288  RBA 1964

Installing Oracle Goldengate Software

Below we will learn how to Install Oracle Goldengate Software :-

Let us see with ls -ltr and check the zip file of the software :-

[oracle@node4 gg_orcl]$ ls -ltr
total 87192
-rwxrw-rw- 1 oracle oinstall 89186858 Mar  4 16:07 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@node4 gg_orcl]$
[oracle@node4 gg_orcl]$

Lets unzip the file :-

[oracle@node4 gg_orcl]$ 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

[oracle@node4 gg_orcl]$ tar -xvof *.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
UserExitExamples/ExitDemo_passthru/readme.txt
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
UserExitExamples/ExitDemo_lobs/readme.txt
UserExitExamples/ExitDemo_pk_befores/
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
UserExitExamples/ExitDemo_pk_befores/readme.txt
UserExitExamples/ExitDemo/
UserExitExamples/ExitDemo/exitdemo.vcproj
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS
UserExitExamples/ExitDemo/exitdemo.c
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
UserExitExamples/ExitDemo/exitdemo_utf16.c
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
UserExitExamples/ExitDemo/readme.txt
bcpfmt.tpl
bcrypt.txt
cfg/
cfg/password.properties
cfg/MPMetadataSchema.xsd
cfg/jps-config-jse.xml
cfg/ProfileConfig.xml
cfg/mpmetadata.xml
cfg/Config.properties
chkpt_ora_create.sql
cobgen
convchk
db2cntl.tpl
ddl_cleartrace.sql
ddl_ddl2file.sql
ddl_disable.sql
ddl_enable.sql
ddl_filter.sql
ddl_nopurgeRecyclebin.sql
ddl_ora10.sql
ddl_ora10upCommon.sql
ddl_ora11.sql
ddl_ora9.sql
ddl_pin.sql
ddl_purgeRecyclebin.sql
ddl_remove.sql
ddl_session.sql
ddl_session1.sql
ddl_setup.sql
ddl_status.sql
ddl_staymetadata_off.sql
ddl_staymetadata_on.sql
ddl_trace_off.sql
ddl_trace_on.sql
ddl_tracelevel.sql
ddlcob
defgen
demo_more_ora_create.sql
demo_more_ora_insert.sql
demo_ora_create.sql
demo_ora_insert.sql
demo_ora_lob_create.sql
demo_ora_misc.sql
demo_ora_pk_befores_create.sql
demo_ora_pk_befores_insert.sql
demo_ora_pk_befores_updates.sql
dirjar/
dirjar/xmlparserv2.jar
dirjar/fmw_audit.jar
dirjar/jps-internal.jar
dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar
dirjar/org.springframework.context-3.0.0.RELEASE.jar
dirjar/jps-upgrade.jar
dirjar/oraclepki.jar
dirjar/org.springframework.transaction-3.0.0.RELEASE.jar
dirjar/xstream-1.3.jar
dirjar/jsr250-api-1.0.jar
dirjar/org.springframework.beans-3.0.0.RELEASE.jar
dirjar/ldapjclnt11.jar
dirjar/spring-security-cas-client-3.0.1.RELEASE.jar
dirjar/jps-manifest.jar
dirjar/org.springframework.aspects-3.0.0.RELEASE.jar
dirjar/identityutils.jar
dirjar/org.springframework.aop-3.0.0.RELEASE.jar
dirjar/jacc-spi.jar
dirjar/jmxremote_optional-1.0-b02.jar
dirjar/slf4j-log4j12-1.4.3.jar
dirjar/jps-api.jar
dirjar/slf4j-api-1.4.3.jar
dirjar/identitystore.jar
dirjar/jps-unsupported-api.jar
dirjar/osdt_xmlsec.jar
dirjar/org.springframework.orm-3.0.0.RELEASE.jar
dirjar/jagent.jar
dirjar/commons-codec-1.3.jar
dirjar/jps-ee.jar
dirjar/spring-security-taglibs-3.0.1.RELEASE.jar
dirjar/log4j-1.2.15.jar
dirjar/osdt_core.jar
dirjar/spring-security-acl-3.0.1.RELEASE.jar
dirjar/xpp3_min-1.1.4c.jar
dirjar/spring-security-web-3.0.1.RELEASE.jar
dirjar/spring-security-core-3.0.1.RELEASE.jar
dirjar/spring-security-config-3.0.1.RELEASE.jar
dirjar/jps-mbeans.jar
dirjar/org.springframework.test-3.0.0.RELEASE.jar
dirjar/jdmkrt-1.0-b02.jar
dirjar/jps-common.jar
dirjar/org.springframework.web-3.0.0.RELEASE.jar
dirjar/jps-patching.jar
dirjar/jps-wls.jar
dirjar/commons-logging-1.0.4.jar
dirjar/org.springframework.expression-3.0.0.RELEASE.jar
dirjar/org.springframework.instrument-3.0.0.RELEASE.jar
dirjar/monitor-common.jar
dirjar/osdt_cert.jar
dirjar/org.springframework.asm-3.0.0.RELEASE.jar
dirjar/org.springframework.context.support-3.0.0.RELEASE.jar
dirjar/org.springframework.core-3.0.0.RELEASE.jar
dirprm/
dirprm/jagent.prm
emsclnt
extract
freeBSD.txt
ggMessage.dat
ggcmd
ggsci
help.txt
jagent.sh
keygen
libantlr3c.so
libdb-5.2.so
libgglog.so
libggrepo.so
libicudata.so.38
libicui18n.so.38
libicuuc.so.38
libxerces-c.so.28
libxml2.txt
logdump
marker_remove.sql
marker_setup.sql
marker_status.sql
mgr
notices.txt
oggerr
params.sql
prvtclkm.plb
pw_agent_util.sh
remove_seq.sql
replicat
retrace
reverse
role_setup.sql
sequence.sql
server
sqlldr.tpl
tcperrs
ucharset.h
ulg.sql
usrdecs.h
zlib.txt

[oracle@node4 gg_orcl]$ ./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 (node4.oracle.com) 1> Create Subdirs

Creating subdirectories under current directory /u01/app/oracle/gg_orcl

Parameter files                /u01/app/oracle/gg_orcl/dirprm: already exists
Report files                   /u01/app/oracle/gg_orcl/dirrpt: created
Checkpoint files               /u01/app/oracle/gg_orcl/dirchk: created
Process status files           /u01/app/oracle/gg_orcl/dirpcs: created
SQL script files               /u01/app/oracle/gg_orcl/dirsql: created
Database definitions files     /u01/app/oracle/gg_orcl/dirdef: created
Extract data files             /u01/app/oracle/gg_orcl/dirdat: created
Temporary files                /u01/app/oracle/gg_orcl/dirtmp: created
Stdout files                   /u01/app/oracle/gg_orcl/dirout: created


GGSCI (node4.oracle.com) 2> dblogin userid krishna, password some_password
Successfully logged into database.