Oracle: March 2022

Monday, March 21, 2022

EXTRACT Stuck in RECOVERY since long time in GG HUB

OGG : 19c Classic Architecture

Source DB : 19C Oracle

Goldengate and Database are running on two different Servers , Hub Server (where OGG is installed) is running in UTC time zone but the database server is running in US Central Time zone.

GGSCI (ec2-devhostas ggadmin@DEVDB) 41> start EDEVDB

Sending START request to MANAGER ...

EXTRACT EDEVDB starting

GSCI (ec2-devhost as ggadmin@DEVDB) 45> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EDEVDB 01:18:52 00:00:05

EXTRACT STOPPED PDEVDB 00:00:00 01:18:56

GGSCI (ec2-devhost as ggadmin@DEVDB) 47> send EDEVDB status

Sending STATUS request to EXTRACT EDEVDB ...

EXTRACT EDEVDB (PID 4790)

Current status: In recovery[1]: Processing data

Current read position:

Redo thread #: 1

Sequence #: 0

RBA: 0

Timestamp: 2021-06-19 16:11:01.000000

SCN: 0.0 (0)

Current write position:

Sequence #: 4

RBA: 1449

Timestamp: 2021-06-19 17:30:04.008729

Extract Trail: /u02/goldengate/home/dirdat/SRC/DEVDB/tgtdb/sc

GGSCI (ec2-devhost as ggadmin@DEVDB) 57> view params EDEVDB

EXTRACT EDEVDB

SETENV (ORACLE_HOME='/u01/app/oracle/product/19.0.0.0/dbhome_1')

SETENV (ORACLE_SID='DEVDB')

USERIDALIAS ggadmin_DEVDB

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 1024)

GETUPDATEBEFORES

NOCOMPRESSDELETES

NOCOMPRESSUPDATES

REPORTCOUNT EVERY 1 HOUR, RATE

WARNLONGTRANS 1D, CHECKINTERVAL 10M, SKIPEMPTYTRANS

EXTTRAIL /u02/goldengate/home/dirdat/SRC/DEVDB/tgtdb/sc

TABLE SCOTT.CB_GG GETBEFORECOLS(ON UPDATE ALL, ON DELETE ALL) ;

Solution:

Source DB Time :

12:01:36 ADMIN@DEVDB > select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

19-JUN-21 12.30.36.030634 PM -05:00

GG HUB Server/DB version :

[oracle@ec2-devhost home]$ date

Sat Jun 19 17:41:26 UTC 2021

Add below parameter on source param: (introduced in OGG 12.3.0.1.2)

TRANLOGOPTIONS SOURCE_OS_TIMEZONE GMT-5

GGSCI (ec2-devhost as ggadmin@DEVDB) 49> edit params EDEVDB

GGSCI (ec2-devhost as ggadmin@DEVDB) 50> start EDEVDB

Sending START request to MANAGER ...

EXTRACT EDEVDB starting

GGSCI (ec2-devhost as ggadmin@DEVDB) 51> send EDEVDB status

Sending STATUS request to EXTRACT EDEVDB ...

EXTRACT EDEVDB (PID 5013)

Current status: Recovery complete: At EOF