OGG-02028 Failed to attach to logmining server Error code 1304

OGG-02028 Failed to attach to logmining server Error code 1304, error message: ORA-01304: subordinate process error

In this post, I would liketo share my experience of an error which I had faced recently.

For a project, I Configured Oracle GoldenGate 12c Integrated Mode for replicating between two Oracle Databases. I faced an issue and the Integrated Extract got ABENDED due to the below Error.

2014-11-12 10:30:43 ERROR OGG-02028 Failed to attach to logmining server Error code 1304, error message: ORA-01304: subordinate process error. Check alert and trace logs.2014-11-12 10:30:43 ERROR OGG-01668 PROCESS ABENDING.

I then analyzed the report of the Extract Process.,

GGSCI (OGG1) 9> view report TRAEXT
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0T1_PLATFORMS_140313.1216
AIX 5L, ppc, 64bit (optimized), Oracle 11g on Mar 13 2014 16:07:43

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
Starting at 2014-11-12 10:30:06
***********************************************************************

Operating System Version:
AIX
Version 7, Release 1
Node: OGG1
Machine: 00F87DF34C00
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 11796572

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************

2014-11-12 10:30:06 INFO OGG-03059 Operating system character set identified as ISO-8859-1.

2014-11-12 10:30:06 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT TRAEXT
USERID ggadmin, PASSWORD ******

2014-11-12 10:30:07 INFO OGG-03522 Setting session time zone to source database time zone ‘-04:00’.
DDL INCLUDE MAPPED EXCLUDE ALL INSTR ‘MATERIALIZED VIEW’
DDLOPTIONS ADDTRANDATA, REPORT
EXTTRAIL /ogg/dirdat/tt
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 100, parallelism 1)
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLEEXCLUDE TREASURY.OUT_BAL_VIEW
TABLEEXCLUDE TREASURY.TREASURY_FEED_DAILY
TABLEEXCLUDE TREASURY.OUT_BAL_VIEW_MONTH
TABLEEXCLUDE TREASURY.TREASURY_FEED_MONTHLY
TABLEEXCLUDE TREASURY.TREASURY_CCBFR_FEED_MONTHLY_V
TABLE TREASURY.*;

2014-11-12 10:30:07 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.

2014-11-12 10:30:07 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:
/ogg/BR/TRAEXT.

Bounded Recovery Parameter:
Options = BRRESET
BRINTERVAL = 4HOURS
BRDIR = /ogg

2014-11-12 10:30:07 INFO OGG-01851 filecaching started: thread ID: 1286.

2014-11-12 10:30:07 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:
/ogg/dirtmp.

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

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

Database Language and Character Set:
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “UTF8”

2014-11-12 10:30:08 INFO OGG-02089 Source redo compatibility version is: 11.2.0.3.

Source Context :
SourceModule : [er.redo.oraxo] SourceID : [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/app/er/redo/oracle/redooraix.c] SourceFunction : [REDOORAIX_attach(UString &, BOOL, errmsg_t &)] SourceLine : [1437]

2014-11-12 10:30:43 ERROR OGG-02028 Failed to attach to logmining server Error code 1304, error message: ORA-01304: subordinate process error. Check alert and trace logs.

2014-11-12 10:30:43 ERROR OGG-01668 PROCESS ABENDING.

Everything went fine, it seemed that there was some problem with the Logmining Server. On analyzing the alertlog I found the below error.,

ORA-1653: unable to extend table SYS.LOGMNRLT_123_COL$ by 64 in tablespace SYSAUX
krvxerpt: Errors detected in process 156, role builder.
krvxmrs: Leaving by exception: 604
Errors in file /ifc/oradba/admin/GGDB1/dbdump/diag/rdbms/GGDB1/GGDB1/trace/GGDB1_ms0d_24707164.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.LOGMNRLT_123_COL$ by 64 in tablespace SYSAUX
LOGMINER: session#=20 (OGG$CAP_TRAEXT), builder MS0D pid=156 OS id=24707164 sid=770 stopped
Errors in file /ifc/oradba/admin/GGDB1/dbdump/diag/rdbms/GGDB1/GGDB1/trace/GGDB1_ms0d_24707164.trc:
ORA-01653: unable to extend table SYS.LOGMNRLT_123_COL$ by 64 in tablespace SYSAUX
Wed Nov 12 10:30:43 2014
LOGMINER: session#=20 (OGG$CAP_TRAEXT), preparer MS0E pid=184 OS id=17957008 sid=1036 stopped
Wed Nov 12 10:30:43 2014
LOGMINER: session#=20 (OGG$CAP_TRAEXT), reader MS0C pid=142 OS id=22282372 sid=1417 stopped

SYSAUX tablespace was filled. Increased the size of the SYSAUX tablespace. You can purge the SYSAUX tablespace.

Started the Extract and it ran well.

GGSCI (OGG1) 12> info extract TRAEXT

EXTRACT TRAEXT Last Started 2014-11-12 10:43 Status RUNNING
Checkpoint Lag 00:00:01 (updated 00:00:02 ago)
Process ID 17170546
Log Read Checkpoint Oracle Integrated Redo Logs
2014-11-12 10:47:50
SCN 1708.3296197740 (7339100339308)

Check whether Integrated capture started by seeing the ggserr.log

2014-11-12 10:43:08 INFO OGG-02068 Oracle GoldenGate Capture for Oracle, traext.prm: Integrated capture successfully attached to logmining server OGG$CAP_TRAEXT using OGGCapture API.
2014-11-12 10:43:09 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, traext.prm: EXTRACT TRAEXT started.
2014-11-12 10:43:09 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, traext.prm: No recovery is required for target file /ogg/dirdat/tt000000, at RBA 0 (file not opened).

SUMMARY:

SYSAUX tablespace should be considered while we go for GoldenGate Integrated Mode. Because, Unlike Classic Mode, here Logminer Plays an important role in Integrated Mode. Logminer is the one which captures the changes from the Redologs and gives it to the Extract Process to write it to the Trail files.

So by default Logminer uses the SYSAUX tablespace. From the output of the below query you can see it clearly.

col OCCUPANT_NAME for a30
col OCCUPANT_DESC for a60
col schema_name for a10

select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;

OGG-02028 Failed to attach to logmining server Error code 1304