How to Reposition Extract, Data Pump and Replicat in GoldenGate During Trail File Corruption


In this blog, we will walk you through the steps to reposition the Extract, Data Pump and Replicat processes in the event of Extract trail file corruption.

 

The environment consists of:

Host: dbsrv01.localdomain: Source Database (19c) named "master" running on OEL 8.8, Extracting HR.REGIONS table using GoldenGate 21.3.
Host: dbsrv02.localdomain: Target Database (19c) named "orcl" running on OEL 8.8, Replicating HR.REGIONS table using GoldenGate 21.3.


Source (master)

Target (orcl)

1. We have an integrated Extract running on the source database.

 [oracle@dbsrv01 ~]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO

Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23

Operating system character set identified as UTF-8.

 

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

 

 

 

GGSCI (dbsrv01.localdomain) 1> INFO ALL

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

EXTRACT        RUNNING     EXTR          00:00:01      00:00:09

EXTRACT        RUNNING     PUMP        00:00:00      00:00:05

 

 

GGSCI (dbsrv01.localdomain) 2> INFO EXTRACT EXTR

 

Extract    EXTR      Last Started 2024-12-18 07:57   Status RUNNING

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

Process ID                     4148

Log Read Checkpoint  Oracle Integrated Redo Logs

                                       2024-12-18 07:58:15

                                       SCN 0.25257000 (25257000)

1. We have an integrated Replicat running on the target database.

 [oracle@dbsrv02 ~]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO

Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23

Operating system character set identified as UTF-8.

 

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

 

 

 

GGSCI (dbsrv02.localdomain) 1> INFO ALL

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

REPLICAT        RUNNING     REPR        00:00:00      00:00:06

 

 


GGSCI (dbsrv02.localdomain) 2> INFO REPLICAT REPR

 

Replicat   REPR      Last Started 2024-12-18 07:55   Status RUNNING

INTEGRATED

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

Process ID                     3620

Log Read Checkpoint  File ./dirdat/rx000000020

                                       2024-12-18 07:57:26.924461  RBA 1521

 2. In a scenario where the Goldengate home file system is corrupted, leading to trail file or extract checkpoint file corruption, we will simulate the scenario by manually corrupting the trail file.

 

GGSCI (dbsrv01.localdomain) 3> INFO EXTR,DETAIL

 

Extract    EXTR      Last Started 2024-12-18 07:57   Status RUNNING

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

Process ID                     4148

Log Read Checkpoint  Oracle Integrated Redo Logs

                                       2024-12-18 08:06:28

                                       SCN 0.25261871 (25261871)

 

  Target Extract Trails:

 

  Trail Name                                       Seqno        RBA     Max MB Trail Type

 

  ./dirdat/ex                                          3       1949        500 EXTTRAIL

 

 

GGSCI (dbsrv01.localdomain) 4> EXIT

 

[oracle@dbsrv01 ~]$ cd /u01/app/oracle/product/21.3.0/ogg_1/dirdat/

 

[oracle@dbsrv01 dirdat]$ echo "LET'S CORRUPT OGG TRAIL FILE" > ex000000003

 

3. Now the trail file is corrupted, causing the Extract process to ABEND with error OGG-01184.

 [oracle@dbsrv01 ~]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO

Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23

Operating system character set identified as UTF-8.

 

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

 

GGSCI (dbsrv01.localdomain) 1> INFO ALL

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

EXTRACT        RUNNING     EXTR          00:00:00      00:00:09

EXTRACT        RUNNING     PUMP        00:00:00      00:00:01

 

 

GGSCI (dbsrv01.localdomain) 2> STOP EXTR

 

Sending STOP request to Extract group EXTR ...

Request processed.

 

GGSCI (dbsrv01.localdomain) 3> START EXTR

 

Sending STOP request to Extract group EXTR ...

Request processed.

 

 

GGSCI (dbsrv01.localdomain) 4> INFO ALL

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

EXTRACT        ABENDED     EXTR         00:00:00      00:06:38

EXTRACT        RUNNING     PUMP        00:00:00      00:00:04

 

GGSCI (dbsrv01.localdomain) 5> VIEW REPORT EXTR

 

2024-12-18 08:16:35  ERROR   OGG-01184  Expected 4 bytes, but got 0 bytes, in trail /u01/app/oracle/product/21.3.0/ogg_1/dirdat/ex000000003, seqno 3, reading record trailer token at RBA 1949.

 

2024-12-18 08:16:35  ERROR   OGG-01668  PROCESS ABENDING.

 

 

 

 

Here are the steps to reposition the Extract, Data Pump and Replicat processes during Extract trail file corruption using the current Log BSN value from Replicat. 

Find the Log BSN value from Replicat, then adjust the positions of the Extract, Data Pump and Replicat processes accordingly.


Source (master)

Target (orcl)

 2. In GGSCI, use the ALTER EXTRACT command to reposition the primary Extract to start from the LOGBSN position obtained from the target.

 [oracle@dbsrv01 ~]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO

Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23

Operating system character set identified as UTF-8.

 

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

 GGSCI (dbsrv01.localdomain) 1> DBLOGIN USERID oggadmin@master, PASSWORD oggadmin

Successfully logged into database.

 

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 2> ALTER EXTRACT EXTR SCN 25135967

Extract altered.

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 3> ALTER EXTRACT EXTR ETROLLOVER

 

2024-12-18 08:44:11  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that

trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;  it will not happen automatically.

Extract altered.

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 4> INFO EXTR,DETAIL

 

Extract    EXTR      Initialized  2024-12-18 08:43   Status STOPPED

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

Log Read Checkpoint  Oracle Integrated Redo Logs

                                        First Record

                                        SCN 0.25135967 (25135967)

 

  Target Extract Trails:

 

  Trail Name                                       Seqno        RBA     Max MB Trail Type

 

  ./dirdat/ex                                          4          0        500 EXTTRAIL

 1.In GGSCI on the target system, run DBLOGIN and use INFO REPLICAT, DETAIL to get the LOGBSN value.

 [oracle@dbsrv02 ~]$ ggsci

 Oracle GoldenGate Command Interpreter for Oracle

Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO

Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23

Operating system character set identified as UTF-8.

 

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

 GGSCI (dbsrv02.localdomain) 1> DBLOGIN USERID oggadmin@orcl, PASSWORD oggadmin

Successfully logged into database.

 

GGSCI (dbsrv02.localdomain as oggadmin@orcl) 2> INFO REPLICAT REPR,DETAIL

 

Replicat   REPR      Last Started 2024-12-18 07:55   Status RUNNING

INTEGRATED

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

Process ID                    3620

Log Read Checkpoint  File ./dirdat/rx000000020

                                       2024-12-18 08:02:29.321056  RBA 2083

 

INTEGRATED Replicat

DBLOGIN Provided, inbound server name is OGG$REPR in ATTACHED state

 

 

Current Log BSN value: 25135967

 

Low Watermark CSN value: 25147886

(All source transactions prior to this scn have been applied)

 

High Watermark CSN value: 25259325

(Some source transactions between this scn and the low watermark may have been applied)

 

  Extract Source                          Begin             End

 

  ./dirdat/rx000000020                    2024-12-18 05:57  2024-12-18 08:02

  ./dirdat/rx000000019                    * Initialized *   2024-12-18 05:57

  ./dirdat/rx000000018                    * Initialized *   First Record

  ./dirdat/rx000000017                    2024-12-17 19:52  2024-12-18 05:26

  ./dirdat/rx000000016                    2024-12-17 18:09  2024-12-17 19:52

  ./dirdat/rx000000016                    * Initialized *   2024-12-17 18:09

  ./dirdat/rx000000016                    * Initialized *   First Record

  ./dirdat/rx000000016                    * Initialized *   First Record

  ./dirdat/rx000000005                    2024-12-13 00:03  2024-12-13 00:03

  ./dirdat/rx000000005                    2024-12-12 22:27  2024-12-13 00:03

  ./dirdat/rx000000005                    * Initialized *   2024-12-12 22:27

  ./dirdat/rx000000005                    * Initialized *   First Record

  ./dirdat/rx000000005                    * Initialized *   First Record

  ./dirdat/rx000000014                    2024-12-12 11:41  2024-12-12 23:02

  ./dirdat/rx000000012                    * Initialized *   2024-12-12 11:41

  ./dirdat/rx000000012                    * Initialized *   First Record

  ./dirdat/rx000000012                    * Initialized *   First Record

  ./dirdat/rx000000011                    2024-12-10 12:18  2024-12-10 12:18

  ./dirdat/rx000000011                    * Initialized *   2024-12-10 12:18

  ./dirdat/rx000000011                    2024-12-08 22:35  First Record

  ./dirdat/rx000000011                    2024-12-08 02:56  2024-12-08 22:35

  ./dirdat/rx000000009                    2024-12-08 00:50  2024-12-08 02:56

  ./dirdat/rx000000006                    * Initialized *   2024-12-08 00:50

  ./dirdat/rx000000003                    2024-11-24 00:04  First Record

  ./dirdat/rx000000003                    2024-11-23 00:44  2024-11-24 00:04

  ./dirdat/rx000000002                    2024-11-07 02:39  2024-11-23 00:44

  ./dirdat/rx000000001                    * Initialized *   2024-11-07 02:39

  ./dirdat/rx000000000                    * Initialized *   First Record

  ./dirdat/rx000000000                    * Initialized *   First Record

 

 

Current directory    /u01/app/oracle/product/21.3.0/ogg_1

 

Report file          /u01/app/oracle/product/21.3.0/ogg_1/dirrpt/REPR.rpt

Parameter file       /u01/app/oracle/product/21.3.0/ogg_1/dirprm/repr.prm

Checkpoint file      /u01/app/oracle/product/21.3.0/ogg_1/dirchk/REPR.cpr

Process file         /u01/app/oracle/product/21.3.0/ogg_1/dirpcs/REPR.pcr

Error log            /u01/app/oracle/product/21.3.0/ogg_1/ggserr.log

 

 

 3. In GGSCI, issue the ALTER EXTRACT command to reposition the data pump and start a new trail file.


 GGSCI (dbsrv01.localdomain as oggadmin@master) 5> STOP PUMP

 

Sending STOP request to Extract group PUMP ...

Request processed.

 

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 6> ALTER EXTRACT PUMP EXTSEQNO 4

Extract altered.

 

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 7> ALTER EXTRACT PUMP ETROLLOVER

 

2024-12-18 08:53:06  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that

trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;  it will not happen automatically.

Extract altered.

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 8> INFO EXTRACT PUMP,DETAIL

 

Extract    PUMP      Initialized  2024-12-18 08:52   Status STOPPED

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

Log Read Checkpoint  File ./dirdat/ex000000004

                                       First Record  RBA 1949

 

  Target Extract Trails:

 

  Trail Name                                       Seqno        RBA     Max MB Trail Type

 

  ./dirdat/rx                                         21          0        500 RMTTRAIL

 

 

 

 

 

 

 4. In GGSCI, use the ALTER REPLICAT command to reposition the Replicat to start reading the trail from the data pump's new write checkpoint.

GGSCI (dbsrv02.localdomain as oggadmin@orcl) 3> STOP REPLICAT REPR

 

Sending STOP request to Replicat group REPR ...

Request processed.

 

 

GGSCI (dbsrv02.localdomain as oggadmin@orcl) 4> ALTER REPLICAT REPR EXTSEQNO 21

 

2024-12-18 09:07:46  INFO    OGG-06594  Replicat REPR has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplic

ate suppression, start REPR with NOFILTERDUPTRANSACTIONS option.

 

Integrated Replicat altered.

 

 

GGSCI (dbsrv02.localdomain as oggadmin@orcl) 5> ALTER REPLICAT REPR EXTRBA 0

 

2024-12-18 09:07:56  INFO    OGG-06594  Replicat REPR has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplic

ate suppression, start REPR with NOFILTERDUPTRANSACTIONS option.

 

GGSCI (dbsrv02.localdomain as oggadmin@orcl) 6> INFO REPLICAT REPR

 

Replicat   REPR      Initialized  2024-12-18 09:07   Status STOPPED

INTEGRATED

Checkpoint Lag            00:00:00 (updated 00:02:11 ago)

Log Read Checkpoint  File ./dirdat/rx000000021

                                       First Record  RBA 0

 

5. Start the Extract and the Data pump by issuing the START EXTRACT command for both processes in GGSCI.

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 9> START EXTRACT EXTR

 

Sending START request to Manager ...

Extract group EXTR starting.

 

 

GGSCI (dbsrv01.localdomain as oggadmin@master) 10> START EXTRACT PUMP

 

Sending START request to Manager ...

Extract group PUMP starting.

 

 GGSCI (dbsrv01.localdomain as oggadmin@master) 11> INFO ALL

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

EXTRACT        RUNNING     EXTR          00:00:00      00:00:08

EXTRACT        RUNNING     PUMP        00:00:00      00:18:45

 

 GGSCI (dbsrv01.localdomain as oggadmin@master) 12> INFO ALL

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

EXTRACT        RUNNING     EXTR           00:00:00      00:00:02

EXTRACT        RUNNING     PUMP         00:00:00      00:00:01

 

6. Start the Replicat by issuing the START REPLICAT command in GGSCI

 

GGSCI (dbsrv02.localdomain as oggadmin@orcl) 7> START REPR

 

Sending START request to Manager ...

Replicat group REPR starting.

 

 

GGSCI (dbsrv02.localdomain as oggadmin@orcl) 8> INFO ALL

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING

REPLICAT       RUNNING     REPR          00:00:00      00:00:03

Comments

Post a Comment

Popular posts from this blog

Step-by-Step Guide to Configure Oracle GoldenGate Plug-in for Enterprise Manager 13cR5

Image
Oracle GoldenGate Enterprise Manager Plug-in helps monitor and manage Goldengate processes through Oracle Enterprise Manager Cloud Control. Environment details: Host: srvdb01.localdomain Operating System: OEL 8.8 OEM Version: 13.5.0.0 OEM Home: /u01/app/oracle/em13c/oraclehome Database Version: 19.23.0.0.0 Database Home: /u01/app/oracle/product/19.0.0/dbhome_1 Goldengate Version: 21.3.0.0.0 Goldengate Home: /u01/app/oracle/product/21.3.0/ogg_1 Goldengate Agent Home: /u01/app/oracle/oggmon_agent122   All components, including Oracle Enterprise Manager 13.5, Oracle Database 19c, Oracle Goldengate 21.3 and Goldengate Monitor Agent 12.2 are configured on the same host srvdb01.localdomain running on OEL 8.8. We will set up the Oracle GoldenGate Plug-in for Enterprise Manager 13cR5. 1. Download the Oracle Goldengate Plug-in for OEM 13.5 from edelivery.oracle.com. https://www.oracle.com/middleware/technologies/goldengate-downloads.html 2. Copy the Oracle Goldengate Plug-in to the OMS ...
Read more

Step-by-Step Guide to Configure Oracle GoldenGate Veridata 23c

Image
Oracle Goldengate Veridata is a fast tool that compares data between source and target databases, report differences and fixes them. Oracle GoldenGate Veridata 23c supports only MySQL Server as the repository database for storing metadata. The environment consists of: Host: dbsrv01.localdomain : Veridata Agent 23c, Source Database (19c) running on OEL 8.8, extracting  HR.REGIONS table using GoldenGate 21.3 Host: dbsrv02.localdomain : Veridata Agent 23c, Target Database (19c) running on OEL 8.8, replicating HR.REGIONS table using GoldenGate 21.3 Host: dbsrv03.localdomain : Veridata Server 23c, running on OEL 8.8 We will set up the Veridata Server and Veridata Agent 23c for data comparison and synchronization. 1. Download the Oracle Goldengate Veridata installation file from Oracle Software Delivery Cloud. 2.   Copy the Oracle Goldengate Veridata installer to the target server and unzip under /tmp. [oracle@dbsrv03 ~]$ cd /tmp [or...
Read more