golden gate replication table

[Roshan]

OGG 18.0
Solaris 11
RAC 2 nodes.

Hi Erman,

I noticed every month on the first day, replicat hangs for a particular table - GPRS_HOME_CDRS. WHen I comment this table, the replicat runs fine.

I have generated the healthcheck report and noticed the sql id below being executed:

cdk1vb3mrsxp4, fg6vd7qu841pj

health01Apr2020.html
reploggt.prm
ggserr.log

I used any of the parameters below but still replicat gets hanged.

--DBOPTIONS INTEGRATEDPARAMS(PARALLELISM 4, COMMIT_SERIALIZATION FULL)
BATCHSQL BATCHESPERQUEUE 1000,BATCHTRANSOPS 100000, OPSPERBATCH 100000,OPSPERQUEUE 100000

Could you please advise.


Regards,

Roshan

[Roshan]

I have checked doc 2349663.1 and doc 2262603.1

Could you please advise if steps are correct to create the classic replicat and move the table?

current process is shown below:

GGSCI (dware1) 1> all

 

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

 

MANAGER     RUNNING

EXTRACT     RUNNING     EXTDWH      00:00:03      00:00:07 <-------------------- integrated extrract

EXTRACT     RUNNING     PXDWN       00:00:04      00:00:08

REPLICAT    RUNNING     REPLOGGT    00:00:05      00:00:07 <------------------ integrated replicat

The following command was initially used to create the IR. Could you please advise how to proceed to create the classic replicat?

 

add replicat REPLOGGT integrated exttrail /ogg/ora12c/dirdat/cd

 

Please validate steps:

 

1. create classic replicat process

add replicat REP2 exttrail /ogg/ora12c/dirdat/cd

 

2. edit REP2 parameter file:

 

REPLICAT REPLOGGT

include ./dirprm/macro.inc

ASSUMETARGETDEFS

DDLOPTIONS REPORT

USERIDALIAS oraggext

DDL INCLUDE MAPPED &

EXCLUDE INSTR 'ALTER TABLE cbsogg.GPRS_HOME_CDRS  ADD PARTITION' &

EXCLUDE INSTR 'ALTER TABLE cbsogg.GSM_HOME_CDRS  ADD PARTITION'

REPERROR (DEFAULT, EXCEPTION)

REPERROR (DEFAULT2, ABEND)

--REPERROR (1403, DISCARD)

DDLERROR DEFAULT IGNORE

--DBOPTIONS INTEGRATEDPARAMS(PARALLELISM 4, COMMIT_SERIALIZATION FULL)

BATCHSQL BATCHESPERQUEUE 1000,BATCHTRANSOPS 100000, OPSPERBATCH 100000,OPSPERQUEUE 100000

 

MAP CBS_CORE.GPRS_HOME_CDRS,TARGET CBSOGG.GPRS_HOME_CDRS;

 

2. stop replicat(REPLOGGT), pump and extract

 

3. start extract, start pump

 

4. remove table from replicat 'REPLOGGT'

 

5. start replicats.

 
Thanks

[ErmanArslansOracleBlog]

Why does your replicat hang?
What is the wait of it?

what do you see in V$GG_APPLY_RECEIVER ? (during the hang)
Maybe there is a missing index in the target? Did you check using GG related SQL: Statement (ASH) (Document 1448324.1) ?
Did you size your sga properly? steams pool and everything.. select * from v$sgastat where pool like 'streams pool' and name='free memory'
Maybe it is because of a hanging DDL ? -> did you check this note -> How to investigate Replicat hanging on a DDL operation (Doc ID 1575915.1)

[Roshan]

Hi Erman,

thanks for the update.

I will check V$GG_APPLY_RECEIVER next month because the hang normally occurs on first day every month.

Yes, the indexes are missing. I will export the indexes for that partitcular table and import them. Do I need to use flashback_scn when exporting the index?

I noticed the server is using swap space. RAM is 80 G per node and streams pool was sized 10g.

SQL>  select * from v$sgastat where pool like 'streams pool' and name='free memory';

POOL         NAME                            BYTES     CON_ID
------------ -------------------------- ---------- ----------
streams pool free memory                10394070120         0

I have precreated the partitions on target and I have used a parameter to skip the DDL to create partition.

[ErmanArslansOracleBlog]

Using swap is not a problem , but if the OS is doing Swap I/O then it is a problem..
The important thing to check is the number of swap in and out operations.

As for the indexes, what you need to is;

1) Stop Replicat.
2) Created an index to the problematic the table.
3) Start Replicat.

Then, Replicat could proceed the replication.

Ref: Integrated Replicat Hangs with State 'Waiting for memory' without Indexes at Target Database (Doc ID 2554761.1)
Additional Ref: GoldenGate Integrated Capture and Integrated Replicat Healthcheck Script (Doc ID 1448324.1)

[Roshan]

Hi Erman,

I have recreated the indexes. Replication is fine now :)

I have checked the doc below

https://casesup.com/category/knowledgebase/databases/how-to-analyse-swap-memory-usage-oracle-solaris

system does not seem to be short of memory.

ora12c@dware1:~$ /usr/bin/ps -e -o pid,rss,args|egrep 'sched|pageout|fsflush'|grep -v grep
    0        0 sched
    2        0 pageout
    3        0 fsflush
ora12c@dware1:~$ vmstat 1 5|awk '{print $8 " " $9}'

pi po
57 110
0 0
0 0
0 0
0 0
You have new mail in /var/mail/ora12c

ora12c@dware1:~$ vmstat 1 2
 kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr am s0 s1 s2   in   sy   cs us sy id
 0 0 72 136630312 9585608 1747 3014 57 110 96 0 2 9 225 63 5 17351 26533 33525 3 2 96
 0 0 209 138784288 8420592 117 5496 0 0 0 0 0 0 61 2 0 11850 17134 16135 2 1 97
ora12c@dware1:~

RSS is 0

I have also set SGA_MAX_SIZE in extract file to limit the memory used by logminer process for the extract. It seems fine now unlike before.

Thanks

[ErmanArslansOracleBlog]

Good to know that Roshan :)

[Roshan]

Hi Erman,

I would like to know what will happen if swap space becomes 0? currently there is 100G free space out of 128G for swap.

[ErmanArslansOracleBlog]

Please create a seperate issue in the correct category for this.

31 May 2020 Pzr 17:59 tarihinde Roshan [via Erman Arslan's Oracle Forum] <[hidden email]> şunu yazdı:

Hi Erman,

I would like to know what will happen if swap space becomes 0? currently there is 100G free space out of 128G for swap.

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/golden-gate-replication-table-tp8191p8405.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML