OGG-00868 Oracle GoldenGate Capture for Oracle

 Issue :

GoldenGate Extract was getting abended with below error

OGG-00868 Oracle GoldenGate Capture for Oracle, extract01.prm: No data found retrieving if to ignore object

Fix :

Permissions on the Table were changed, Granting the permissions helped fixing this issue

Hope this helps.. 

GoldenGate 12c/18c and 19c New Features

Oracle GoldenGate Drop Capture : ORA-01338: Other process is attached to LogMiner session

While Dropping the GG capture Process, it was giving below errors

SQL> exec dbms_capture_adm.drop_capture('OGG$CAPTURE');
BEGIN dbms_capture_adm.drop_capture('OGG$CAPTURE'); END;

*
ERROR at line 1:
ORA-01338: Other process is attached to LogMiner session
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 829
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 800
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 566
ORA-06512: at line 1

Cause :

An execution of the DROP_CAPTURE procedure drops a logminer session.  The problem was caused by an attempt to drop a logminer session while processes are still attached to it.  The processes are still attached, because the capture has not been stopped.

Solution : 

Stop the GG Capture process and then drop 

SQL> exec dbms_capture_adm.stop_capture('OGG$CAPTURE');

PL/SQL procedure successfully completed.

SQL> exec dbms_capture_adm.drop_capture('OGG$CAPTURE');


PL/SQL procedure successfully completed.


Hope this helps you .. 

Oracle GoldenGate : OGG-00663 OCI Error ORA-00904: "INTCOL#": invalid identifier

Issue :

The GoldenGate Extract was getting abended with below error

020-06-03 12:21:09  ERROR   OGG-00663  OCI Error ORA-00904: "INTCOL#": invalid identifier (status = 904), SQL <SELECT .....l#   FROM TABLE (system.logmnr$col_gg_tabf_public( :sid, :mdh, :object_id, :objv, to_number(:csn) )) 

Fix :

We applied the patch: - 

Patch 17030189: LOGMINER GG DICTIONARY SUPPORT : MISSING ATTRIBUTES

 

cd <Patch_Location>/17030189.11204161018

-- Check for Conflicts 

$OH/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph .

-- Apply the Bug Patch

$OH/OPatch/opatch apply

-- Run Post Install

SQL> @?/sqlpatch/17030189/postinstall.sql

-- Then I ran these scripts as well and this time after the db bounce there were no errors:

SQL> drop PACKAGE LOGMNR_DICT_CACHE;

Package dropped.

SQL> @prvtlmcs.plb

Package created.

No errors.

SQL> @$ORACLE_HOME/rdbms/admin/prvtlmcb.plb

SQL> grant EXECUTE,debug on SYSTEM.LOGMNR$KEY_GG_TABF_PUBLIC to GG_USER;

Hope this resolves your issue.. 

Oracle GoldenGate : Find Source of Replicat Process

There are many ways to find the source of the replicat process by looking at the trail files, the easiest way would be to read the details from the trail file directly. 

For example, lets find the source for the Replicat Process REPLICAT1

GGSCI (sev1) 4> info REPLICAT1

REPLICAT   REPLICAT1  Last Started 2018-11-10 23:36   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  File /usr/app/db2/ggs/trails/a1036633
                     2018-12-06 09:42:42.013255  RBA 136840037


Use the trail file given above, and open it using strings  

GGSCI (sev1) 5> sh strings /usr/app/db2/ggs/trails/a1036633 | head -20

!uri:sev2::opt:app:db1:ggs5  <<----- Source Server Name (sev2) and DB name (DB1) 
!uri:sev2::opt:app:db1:ggs6
%/usr/app/db2/ggs/trails/a10366337
15610950306357
Linux1
sev22  <<-------- Source Server Name
2.6.32-696.10.3.el6.x86_643
##1 SMP Thu Sep 21 12:12:50 EDT 20174
x86_642
db12
db13
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
11.2.0.4.09
EXTRACT11 <------- Source Extract Name
HVersion 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO4
15610950306357

Hope this helps you finding the source of Replicat process and troubleshoot the issues easily. 

GoldenGate : ADD SCHEMATRANDATA failing with OGG-01790 + ORA-06550

Tried to enable supplemental logging at schema level on 11.2.0.4 database triggered with following error  

GGSCI (sev1) 1> dblogin USERID g_user, PASSWORD oracle
Successfully logged into database.

GGSCI (sev1) 2> add schematrandata SCOTT

2018-06-11 09:54:07  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema SCOTT because of the following SQL error: ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_CAPTURE_ADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('SCOTT','ALLKEYS_ON'); END;.

The grants are missing for the GoldenGate user. Please run below to open up grants 

SQL> exec dbms_streams_auth.grant_admin_privilege('G_USER');

PL/SQL procedure successfully completed.

It failed again because of missing parameter enable_goldengate_replication, we need this set to TRUE to support GoldenGate Replication

GGSCI (sev1) 3> add schematrandata SCOTT

2018-06-11 10:51:21  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema SCOTT because of the following SQL error: ORA-26947: Oracle GoldenGate replication is not enabled.
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 1577
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 1086
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 722
ORA-06512: at line 1 SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('SCOTT','ALLKEYS_ON'); END;.

Change the parameter on DB level

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

Supplemental Logging got enabled now. 

GGSCI (sev1) 4> add schematrandata SCOTT

2018-06-11 10:52:35  INFO    OGG-01788  SCHEMATRANDATA has been added on schema SCOTT.

To check if its enabled, please run below

GGSCI (sev1) 5> info schematrandata SCOTT

2018-06-11 10:57:23  INFO    OGG-01785  Schema level supplemental logging is enabled on schema SCOTT.

Hope this resolves your issue. 

Oracle GoldenGate : ERROR: opening port for REPLICAT (Connection refused)

The server got restarted, all the replicats we had set up were in status "Starting...", but none was actually doing anything.
Attempting to stop them got the following error:

GGSCI (serv7) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STARTING    REPLICAT1    00:00:00      00:35:16    
REPLICAT    STARTING    REPLICAT2    00:00:00      00:35:08    


GGSCI (serv7) 8> stop r*

Sending STOP request to REPLICAT REPLICAT1 ...

ERROR: opening port for REPLICAT REPLICAT1 (Connection refused).

Sending STOP request to REPLICAT REPLICAT2 ...

ERROR: opening port for REPLICAT REPLICAT2 (Connection refused).

Stopping/Starting the manager service didn't help either - they still said "Starting" and were unresponsive. Before I even attempted to start the replicat for the first time, it said "Starting", and an attempt to start it gave me "ERROR: REPLICAT REPLICAT2 is already running.".

The cause was the replicat process status file, located in the DIRPCS folder under the Goldengate home - there should be a file for each replicat that's currently running giving details about the status. When a replicat stops, this file is deleted. Since all of the current replicats weren't doing anything (they were all sitting at the end of the previous trail file), they should have been stopped. I renamed the PCR files for the affected replicat processes, and then manager reporting "ABENDED" - at that point, I was able to start up each replicat without issue.

prddb1:serv7:prddb1:(391) /dev/prddb1/ggs/12.1.2.1.0/dirpcs
$ ls -lrt
total 12
-rwxr----- 1 dba oracle 66 May 29 16:49 REPLICAT1.pcr
-rwxr----- 1 dba oracle 66 May 29 16:50 REPLICAT2.pcr
-rwxr----- 1 dba oracle 56 May 29 16:57 MGR.pcm
prddb1:serv7:prddb1:(392) /dev/prddb1/ggs/12.1.2.1.0/dirpcs
$ mv REPLICAT1.pcr REPLICAT1.pcr.old
prddb1@PRD:serv7:prddb1:(397) /dev/prddb1/ggs/12.1.2.1.0/dirpcs
$ mv REPLICAT2.pcr REPLICAT2.pcr.old


GGSCI (serv7) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    ABENDED     REPLICAT1    00:00:00      00:38:55    
REPLICAT    ABENDED    REPLICAT2    00:00:00      00:38:47   


GGSCI (serv7) 2> start R*

Sending START request to MANAGER ...
REPLICAT REPLICAT1 starting

Sending START request to MANAGER ...
REPLICAT REPLICAT2 starting

GGSCI (serv7) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPLICAT1    00:00:00      00:44:48    
REPLICAT    RUNNING     REPLICAT2    00:00:00      00:44:40    

GGSCI (serv7) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPLICAT1    00:06:56      00:00:00    
REPLICAT    RUNNING     REPLICAT2    00:00:02      00:00:08   

I Hope this resolves your issue ..  

Oracle GoldenGate : OGG-02022 Logmining server does not exist on this Oracle database

While starting the new extract processes, the extracts were getting abended with below error

2018-05-10 17:32:00  ERROR   OGG-02022  Logmining server does not exist on this Oracle database.

2018-05-10 17:32:00  ERROR   OGG-01668  PROCESS ABENDING.

Solution :

   There is an easy solution for this. You need to login to the database through GGSCI prompt and register the extract. This should get your extracts started

Example : 

GGSCI > dblogin userid guser password LKJFSDKLJFLASDJLKSDJF
GGSCI > register extract extract1, database 
GGSCI > start extract1


Hope this resolves your issue.

ERROR OGG-01028 Formatting error on: table name + Error converting timestamp with timezone from Oracle to ASCII format for column

2015-11-16 17:51:18  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  EXTRACT EXTRACT1 started.
2015-11-16 17:51:18  INFO    OGG-01055  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery initialization completed for target file /usr/app/datab1/ggs/trails/a1000007, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Output file /usr/app/datab1/ggs/trails/a1 is using format RELEASE 11.2.
2015-11-16 17:51:18  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Rolling over remote file /usr/app/datab1/ggs/trails/a1000007.
2015-11-16 17:51:18  INFO    OGG-01053  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for target file /usr/app/datab1/ggs/trails/a1000008, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01057  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for all targets.
2015-11-16 17:51:18  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Position of first record processed Sequence 17507, RBA 21008, SCN 3332.2582756465, Nov 16, 2015 5:32:16 PM.
2015-11-16 17:51:19  INFO    OGG-01020  Oracle GoldenGate Delivery for Oracle, rccp004a.prm:  Processed extract process RESTART_ABEND record at seq 8, rba 1081 (aborted 0 records).
2015-11-16 17:51:21  ERROR   OGG-01028  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Formatting error on: table name CSI_CAET.ERROR_HIT, rowid AAAYnTAAGAAACM/AAA, XID 1.1.2642, position (Seqno 17507, RBA 21616). Error converting timestamp with timezone from Oracle to ASCII format for column LAST_UPDATE_DATE.
2015-11-16 17:51:21  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  PROCESS ABENDING.


GGSCI (Sever2) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     ABENDED     EXTRACT1    00:14:10      00:58:53    


GGSCI (Sever2) 2> edit params EXTRACT1

Solution :
Oracle reported bug 14053498 for the above error {GoldenGate Extract Abends: OGG-01028 Formatting Error on: table name xxxx.xxxx rowid , XID nnnn.nn.nnnn, Position (Seqno n, RBA n) (Doc ID 1558791.1)}

Another Solution

Check the database characterset

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY


Add below two parameters to the extract prm file

SETENV (NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1") -- Please make sure you have the correct characterset from the above output
TRANLOGOPTIONS INCLUDEREGIONID

GGSCI (Sever2) 4> start EXTRACT1

Sending START request to MANAGER ...
EXTRACT EXTRACT1 starting


GGSCI (Sever2) 5> info EXTRACT1

EXTRACT    EXTRACT1  Last Started 2015-11-16 18:50   Status RUNNING
Checkpoint Lag       01:13:36 (updated 00:00:01 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-16 17:37:08  Seqno 17507, RBA 176876032
                     SCN 3332.2582759675 (14313413789947)

OGG-00717 : Found unsupported in-memory undo record + Minimum supplemental logging must be enabled to prevent data loss

2015-11-09 16:23:54  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  EXTRACT EXTRACT3 started.
2015-11-09 16:23:54  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  No recovery is required for target file /usr/app/db1/ggs/trails/x1000000, at RBA 0 (file not opened).
2015-11-09 16:23:54  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  Output file /usr/app/db1/ggs/trails/x1 is using format RELEASE 11.2.
2015-11-09 16:23:54  ERROR   OGG-00717  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  Found unsupported in-memory undo record in sequence 11566, at RBA 1473040, with SCN 3330.2812655651 (14305053751331) ... Minimum supplemental logging must be enabled to prevent data loss.
2015-11-09 16:23:54  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  PROCESS ABENDING.


Possible Cause:
Supplemental logging is not enabled on the database level or the extract is positioned to a point where the supplemental logging has not been enabled i.e an attempt was made to start the extract when the supplemental logging was not enabled on the database.

Solution:

Check if Supplemental logging is enabled

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

Enable Supplemental Logging

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Switch Logfile

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

And attempt to start the extract with begin now option

GGSCI (sev274) 2> alter extract EXTRACT3, begin now
EXTRACT altered.


GGSCI (sev274) 3> start EXTRACT3

Sending START request to MANAGER ...
EXTRACT EXTRACT3 starting


GGSCI (sev274) 4> info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:29:20  Seqno 0, RBA 0
                     SCN 0.0 (0)


GGSCI (sev274) 5> !
info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:29:20  Seqno 0, RBA 0
                     SCN 0.0 (0)


GGSCI (sev274) 6> !
info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:55:07  Seqno 11567, RBA 281600
                     SCN 3330.2812657068 (14305053752748)

OGG-01004 + Database error 26945 (OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS

While using delete cascade on the database, the replicat needs below parameter set in the prm file

DBOPTIONS DEFERREFCONST

After setting this, the replicat was getting abended with the below error

2015-10-30 09:21:34  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0.
2015-10-30 09:21:34  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  Aborted grouped transaction on 'user5.transactions', Database error 26945 (OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0).
2015-10-30 09:21:34  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  Repositioning to rba 1679 in seqno 8.
2015-10-30 09:21:34  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  SQL error 26945 mapping user5.transactions to user5.transactions OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0.

Solution :

exec dbms_goldengate_auth.grant_admin_privilege('<GGSUSER>');
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE = BOTH;

ERROR OGG-00446 Oracle GoldenGate Delivery for Oracle, replicat3.prm: Unable to lock file "/usr/app/db1/ggs/11.2.1.0.28/dirchk/REPLICAT3.cpr" (error 11, Resource temporarily unavailable). Lock currently held by process id (PID) 68732

ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, replicat3.prm:  Unable to lock file "/usr/app/db1/ggs/11.2.1.0.28/dirchk/REPLICAT3.cpr" (error 11, Resource temporarily unavailable).  Lock currently held by process id (PID) 68732

Replicat was getting abended with above error, checked to see what the process PID 68732 is, but nothing was showing up with

ps -ef | grep 68732

and no processes showed as running with the replicat REPLICAT3 name.

Solution :

Copied the original  REPLICAT3.cpr file as REPLICAT3.cpr.tmp , removed the original file REPLICAT3.cpr and renamed REPLICAT3.cpr.tmp as REPLICAT3.cpr.

This released the lock and replicat process started running

Steps :
cp REPLICAT3.cpr REPLICAT3.cpr.tmp
rm REPLICAT3.cpr
mv REPLICAT3.cpr.tmp REPLICAT3.cpr

ERROR OGG-01032 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. Length is 3 - 000000: 46 45 00 |FE. |.

After the Server 2 crashed and brought up the pumps on Server 1 sending trails to Server2 was abending with below error

ERROR   OGG-01032  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. Length is 3 - 000000: 46 45 00                                        |FE.             |.

Cause

Trail file system on target goes out of space causing the pump  to hang. The issue is ultimately due to an  interrupted rmt trail writing caused by connection issues because the target file system cannot be written to.

Note that this is a single instance of a broader issue. Anytime the collector server fails for any reason, it will look like a TCP or network issue to the pump. This can be space or security or path issues or anything else.

Solution

Fix any target space issue.

1) Go the source server and login into ggsci

- Execute the below command
alter <extract pump> etrollover

start <extract pump>

(ETROLLOVER will force the pump to write on a fresh trail)

3) Go the target server and check if the new trail files are being generated.

4) Verify if the first record in the new trail file is indeed the next record in the local extract trail. (It can be done by looking at the RBA value through the logdump utility)

5) Alter the replicat to the new trail file
alter <replicat> extseqno <new trail file> extrba 0

6) Start the Replicat process

7) Verify if all the processes are running fine.