Oracle: March 2019

Saturday, March 9, 2019

OGG Trail File Sequence Number Does Not Reset To 0

GGSCI (myorasrc.domain.com) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

JAGENT RUNNING

GGSCI (myorasrc.domain.com) 8> add extract EXTMYDEV, INTEGRATED TRANLOG, BEGIN NOW

EXTRACT (Integrated) added.

GGSCI (myorasrc.domain.com) 9> add EXTTRAIL /goldengate/dirdat/devdb/cr, extract EXTMYDEV, MEGABYTES 100

EXTTRAIL added.

GGSCI (myorasrc.domain.com) 10> add extract PMPMYDEV, EXTTRAILSOURCE /goldengate/dirdat/devdb/cr

EXTRACT added.

GGSCI (myorasrc.domain.com) 11> ADD RMTTRAIL /goldengate/dirdat/devdb/CR, extract PMPMYDEV, MEGABYTES 100

RMTTRAIL added.

GGSCI (myorasrc.domain.com) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

JAGENT RUNNING

EXTRACT STOPPED EXTMYDEV 00:00:00 00:00:10

EXTRACT STARTING PMPMYDEV 00:00:00 00:00:08

It shouldn’t be in STARTING state, it should be STOPPED (Like Extract above)

GGSCI (myorasrc.domain.com) 15> info EXTMYDEV detail

EXTRACT EXTMYDEV Initialized 2019-01-24 10:46 Status STOPPED

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

Log Read Checkpoint Oracle Integrated Redo Logs

2019-01-24 10:46:57

SCN 0.0 (0)

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

/goldengate/dirdat/devdb/cr 0 0 100 EXTTRAIL

Integrated Extract outbound server first scn: Unavailable.Need DBLOGIN.

Integrated Extract outbound server filtering start scn: Unavailable.Need DBLOGIN.

Extract Source Begin End

Not Available * Initialized * 2019-01-24 10:46

Current directory /goldengate

Report file /goldengate/dirrpt/EXTMYDEV.rpt (does not yet exist)

Parameter file /goldengate/dirprm/EXTMYDEV.prm

Checkpoint file /goldengate/dirchk/EXTMYDEV.cpe

Process file /goldengate/dirpcs/EXTMYDEV.pce

Error log /goldengate/ggserr.log

GG extract looks good, lets check PUMP

GGSCI (myorasrc.domain.com) 13> info PMPMYDEV detail

EXTRACT PMPMYDEV Last Started 2019-01-24 12:18 Status RUNNING

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

Process ID 130534

Log Read Checkpoint File /goldengate/dirdat/devdb/cr000000000

First Record RBA 1451

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

/goldengate/dirdat/devdb/CR 122 911 100 RMTTRAIL --> Wrong should be “0”

Extract Source Begin End

/goldengate/dirdat/devdb/cr000000000 * Initialized * First Record

Current directory /goldengate

Report file /goldengate/dirrpt/PMPMYDEV.rpt

Parameter file /goldengate/dirprm/PMPMYDEV.prm

Checkpoint file /goldengate/dirchk/PMPMYDEV.cpe

Process file /goldengate/dirpcs/PMPMYDEV.pce

Error log /goldengate/ggserr.log

GGSCI (myorasrc.domain.com) 14> info EXTMYDEV showch

EXTRACT EXTMYDEV Last Started 2019-01-24 11:06 Status RUNNING

Checkpoint Lag 00:00:01 (updated 00:00:05 ago)

Process ID 117568

Log Read Checkpoint Oracle Integrated Redo Logs

2019-01-24 13:55:34

SCN 95.1378716772 (409400609892)

Current Checkpoint Detail:

Read Checkpoint #1

Oracle Integrated Redo Log

Startup Checkpoint (starting position in the data source):

Timestamp: 2019-01-24 10:46:57.000000

SCN: Not available

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Timestamp: 2019-01-24 13:55:34.000000

SCN: 95.1378716771 (409400609891)

Current Checkpoint (position of last record read in the data source):

Timestamp: 2019-01-24 13:55:34.000000

SCN: 95.1378716772 (409400609892)

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 122

RBA: 1451

Timestamp: 2019-01-24 13:55:35.651323

Extract Trail: /goldengate/dirdat/devdb/cr

Seqno Length: 9

Flip Seqno Length: No

Trail Type: EXTTRAIL

Header:

Version = 2

Record Source = A

Type = 13

# Input Checkpoints = 1

# Output Checkpoints = 1

File Information:

Block Size = 2048

Max Blocks = 100

Record Length = 2048

Current Offset = 0

Configuration:

Data Source = 3

Transaction Integrity = 1

Task Type = 0

Status:

Start Time = 2019-01-24 11:06:15

Last Update Time = 2019-01-24 13:55:35

Stop Status = A

Last Result = 520

$ ls -lrrth /goldengate/dirdat/devdb/cr*

-rw-r----- 1 oracle dba 1.5K Jan 24 11:06 /goldengate/dirdat/devdb/cr000000000

Trail files on source side starting with number ‘ZERO’ which is Correct as expected

CAUSE :

Issue is due to the TRAIL files NOT deleted on the replciat (TARGET) side so when you start the PUMP on source side it will start writing to same Trail file on target side which follows the same target trail seq number

Target side:

oratgtdev.doamin.com:NOTSET:/goldengate/dirdat/devdb $ ls -lrth

total 16K

-rw-r----- 1 oracle dba 14K Jan 24 15:43 CR000000121

-rw-r----- 1 oracle dba 14K Jan 24 15:43 CR000000122

Solution:

DELETE Trail files on replicat side and recreate PUMP

GGSCI (myorasrc.domain.com) 16> stop PMPMYDEV

Sending STOP request to EXTRACT PMPMYDEV ...

Request processed.

GGSCI (myorasrc.domain.com) 17> delete PMPMYDEV

Deleted EXTRACT PMPMYDEV.

GGSCI (myorasrc.domain.com) 10> add extract PMPMYDEV, EXTTRAILSOURCE /goldengate/dirdat/devdb/cr

EXTRACT added.

GGSCI (myorasrc.domain.com) 11> ADD RMTTRAIL /goldengate/dirdat/devdb/CR, extract PMPMYDEV, MEGABYTES 100

RMTTRAIL added.

GGSCI (myorasrc.domain.com) 16> info PMPMYDEV detail

EXTRACT PMPMYDEV Initialized 2019-01-24 10:46 Status STARTING

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

Process ID 157497

Log Read Checkpoint File /goldengate/dirdat/devdb/cr000000000

First Record RBA 0

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

/goldengate/dirdat/devdb/CR 0 0 100 RMTTRAIL

Extract Source Begin End

/goldengate/dirdat/devdb/cr000000000 * Initialized * First Record

Current directory /goldengate

Report file /goldengate/dirrpt/PMPMYDEV.rpt (does not yet exist)

Parameter file /goldengate/dirprm/PMPMYDEV.prm

Checkpoint file /goldengate/dirchk/PMPMYDEV.cpe

Process file /goldengate/dirpcs/PMPMYDEV.pce

Error log /goldengate/ggserr.log

Now I see Seq# and RBA reset to ‘ZERO’ values

Trail File Sequence Number Does Not Reset To 0 While Delete And Recreation Of Goldengate Extract. (Doc ID 2316739.1)

ERROR OGG-01332 Oracle GoldenGate Collector for Oracle

Source side :

DB : 12.2

OGG : 18.1

GG Mode: Integrated Extract

Target side :

DB: 12.2

OGG: 12.2

GG Mode: Integrated Replicat

After GG upgrade on source side when trying to start the Replicat on target side we are getting below error

$ tail -f /goldengate/ggserr.log

2019-01-23 14:37:47 ERROR OGG-01332 Oracle GoldenGate Collector for Oracle: File /goldengate/dirdat/mydev/mydev/HP000000244, with compatibility level 6, is not compatible with the current software version's compatibility level of 5. Modify the file writer's parameter file to generate the appropriate format using the FORMAT LEVEL 5 option.

2019-01-23 14:37:47 ERROR OGG-01668 Oracle GoldenGate Collector for Oracle: PROCESS ABENDING.

Solution :

When we upgrade GG on source side we do an alter to pump to generate a new trail file on the Target machine so we need to start the replicat also by altering to the same SEQNO and RBANO if we not do alter and just start with same old trail file then we encounter the error above

To fix the issue alter the replicat (On target side) also along with extract and pump (on Source side)

GGSCI (myoradev.domain.com) 10> alter RDBDEV1 extseqno 245 extrba 0

2019-01-23 14:40:18 INFO OGG-06594 Replicat RDBDEV1 has been altered through GGSCI. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start RDBDEV1 with NOFILTERDUPTRANSACTIONS option.

REPLICAT (Integrated) altered.

GGSCI (myoradev.domain.com) 11> info RDBDEV1

REPLICAT RDBDEV1 Initialized 2019-01-23 14:40 Status STOPPED

INTEGRATED

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

Log Read Checkpoint File /goldengate/dirdat/mydev/mydev/HP000245

First Record RBA 0

GGSCI (myoradev.domain.com) 12> start RDBDEV1

Sending START request to MANAGER ...

REPLICAT RDBDEV1 starting

ggserr.log

2019-01-23 14:40:18 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): alter RDBDEV1 extseqno 245 extrba 0.

2019-01-23 14:40:18 INFO OGG-06594 Oracle GoldenGate Command Interpreter for Oracle: Replicat RDBDEV1 has been altered through GGSCI. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start RDBDEV1 with NOFILTERDUPTRANSACTIONS option.

2019-01-23 14:40:27 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info RDBDEV1.

2019-01-23 14:40:33 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start RDBDEV1.

2019-01-23 14:40:33 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.10.33.17:56242 (START REPLICAT RDBDEV1 ).

2019-01-23 14:40:33 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #5).

2019-01-23 14:40:33 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RDBDEV1 starting.

2019-01-23 14:40:33 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: REPLICAT RDBDEV1 starting.

2019-01-23 14:40:33 INFO OGG-03059 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Operating system character set identified as UTF-8.

2019-01-23 14:40:33 INFO OGG-02695 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: ANSI SQL parameter syntax is used for parameter parsing.

2019-01-23 14:40:33 INFO OGG-02095 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Successfully set environment variable ORACLE_HOME=/opt/oracle/product/12.1.0.2.64.

2019-01-23 14:40:33 INFO OGG-02095 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Successfully set environment variable ORACLE_SID=mydev.

2019-01-23 14:40:34 INFO OGG-02095 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Successfully set environment variable ORACLE_HOME=/opt/oracle/product/12.1.0.2.64.

2019-01-23 14:40:34 INFO OGG-02095 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Successfully set environment variable ORACLE_SID=mydev.

2019-01-23 14:40:34 INFO OGG-01815 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/goldengate/dirtmp.

2019-01-23 14:40:34 INFO OGG-02526 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Parameter FILTERDUPTRANSACTIONS is ignored by Integrated Replicat.

2019-01-23 14:40:34 INFO OGG-02545 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Parameter GROUPTRANSOPS is ignored by Integrated Replicat when parallelism is greater than 1.

2019-01-23 14:40:34 INFO OGG-02527 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Integrated Replicat does not populate a trace table.

2019-01-23 14:40:35 INFO OGG-02530 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: Integrated replicat successfully attached to inbound server OGG$RDBDEV1.

2019-01-23 14:40:35 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, RDBDEV1.prm: REPLICAT RDBDEV1 started.

Check Similar issue :

http://chandu208.blogspot.com/2019/03/ogg-01416-oracle-goldengate-capture-for.html

Replicat Abends With ERROR OGG-01332 With Compatibility Level 6, Is Not Compatible With The Current software version's compatibility level of 5. (Doc ID 2385520.1)