Oracle Goldengate: Configure the Streams pool for integrated Replicat

Configure the Streams pool for integrated Replicat:

When using integrated Replicat the Streams pool must be configured.
If we are using non-integrated Replicat means the Streams pool is not necessary.

The shared memory that is used by the servers comes from the Streams pool portion of the System Global Area (SGA) in the database. Therefore, you must set the database initialization parameter STREAMS_POOL_SIZE high enough to keep enough memory available for the number of Extract and Replicat processes that you expect to run in integrated mode. Note that Streams pool is also used by other components of the database (like Oracle Streams, Advanced Queuing, and Datapump export/import), so make certain to take them into account while sizing the Streams pool for Oracle GoldenGate.

The size requirement of the Streams pool for integrated Replicat is based on a single parameter, MAX_SGA_SIZE. The MAX_SGA_SIZE parameter defaults to INFINITE which allows the Replicat process to use as much of the Streams pool as possible. Oracle does not recommend setting the MAX_SGA_SIZE parameter.

Set the STREAMS_POOL_SIZE initialization parameter for the database to the following value:
(1GB * # of integrated Replicats) + 25% head room
For example, on a system with tw0 integrated Replicat process the calculation would be as follows:
(1GB * 2) * 1.25 = 2.5GB STREAMS_POOL_SIZE = 2500M

For example, on a system with three integrated Replicat process the calculation would be as follows:
(1GB * 3) * 1.25 = 3.75GB STREAMS_POOL_SIZE = 3750M

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

How To Handle Oracle DDL add a Column When not using DDL replication?

Handle Oracle DDL add a Column When not using DDL replication 

Description:-

There will be NO data loss occur  for below activity , because Extract will automatically pick up from where it left off.

Following DDL require downtime for Extract and Replicat.

Step 1:- If the newly added column is a composite key column, make sure there are no open transactions on the affected source table.

Step 2:- Issue LAG EXTRACT. When Extract’s lag is 0… or… at EOF, stop Extract.

GGSCI (gg-11.2.com as gguser@source) 44> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:02
EXTRACT RUNNING EXT1 00:00:00 00:00:04

GGSCI (gg-11.2.com as gguser@source) 45> lag extract ext1

Sending GETLAG request to EXTRACT EXT1 …
No records yet processed.
At EOF, no more records to process.

GGSCI (gg-11.2.com as gguser@source) 46> stop extract ext1

Sending STOP request to EXTRACT EXT1 …
Request processed.

Step 3:-Issue LAG REPLICAT. When Replicat’s lag is 0… or… at EOF…

GGSCI (gg-12.2.com as gguser@source) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:00

GGSCI (gg-12.2.com as gguser@source) 4> lag replicat rep1

Sending GETLAG request to REPLICAT REP1 …
Last record lag 343,307 seconds.
At EOF, no more records to process.

GGSCI (gg-12.2.com as gguser@source) 5> stop replicat rep1

Sending STOP request to REPLICAT REP1 …
Request processed.

Step 4:- Add the column to both the source and target tables.

[oracle@gg-11 ~]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 13 18:40:38 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn sample/sample
Connected.
SQL> show user
USER is “SAMPLE”
SQL> desc tt
Name Null? Type
—————————————– ——– —————————-
ID NUMBER(10)

SQL> alter table tt add (name varchar2(10));

Table altered.

Target Side:

[oracle@gg-12 ~]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 13 18:41:20 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn sample/sample
Connected.
SQL> show user
USER is “SAMPLE”
SQL> desc tt
Name Null? Type
—————————————– ——– —————————-
ID NUMBER(10)

SQL> alter table tt add (name varchar2(10));

Table altered.

Step 5:- Start Extract:

GGSCI (gg-11.2.com as gguser@source) 49> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:05
EXTRACT STOPPED EXT1 00:00:00 00:00:07

GGSCI (gg-11.2.com as gguser@source) 50> start extract ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (gg-11.2.com as gguser@source) 51> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:02
EXTRACT RUNNING EXT1 00:10:18 00:00:02

Step 6:- start Replicat:

GGSCI (gg-12.2.com as gguser@source) 8> start replicat rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (gg-12.2.com as gguser@source) 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:02

Step 7:- Now insert the record into newly added column in source side.

SQL> insert into tt values (10,’SAM’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tt where name=’SAM’;

ID NAME
———- ———-
10 SAM

Step 8:- Target side check whether the above record are replicated or not

SQL> select * from tt where name=’SAM’;

ID NAME
———- ———-
10 SAM

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba