Monthly Archives: May 2012

Configure GoldenGate Extract to read from remote logs

2 Replies

Sometimes you may need to run GoldenGate on some different machine than the one that hosts the database. It is very much possible but some kind of restrictions apply. First is that the Endian order of both the systems should be same and the second is the bit width has to be same. For example it is not possible to run GoldenGate on a 32 bit system to read from a database that runs on some 64 bit platform. Assuming that the environemnt satisfies the above two conditions; we can use the LOGSOURCE option of TRANSLOGOPTIONS to achieve this.

Here we run GG on host goldengate1 (192.168.0.109) and the database from which we want to capture the changes runs on the host goldengate3 (192.168.0.111). Both the systems run 11.2.0.2 on RHEL 5.5. On goldengate3 redo logs are in the mount point /home which has been NFS mounted on goldengate1 as /home_gg3

Filesystem           1K-blocks      Used Available Use% Mounted on

192.168.0.111:/home   12184800   7962496   3593376  69% /home_gg3

The Extract parameters are as follows:

EXTRACT ERMT01

USERID ggadmin@orcl3, PASSWORD ggadmin

EXTTRAIL ./dirdat/er

TRANLOGOPTIONS LOGSOURCE LINUX, PATHMAP /home/oracle/app/oracle/oradata/orcl /home_gg3/oracle/app/oracle/oradata/or
cl, PATHMAP /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog /home_gg3/oracle/app/oracle/flash_recovery_
area/ORCL/archivelog

TABLE HR.*;

(The text in the line starting with TRANLOGOPTIONS is a single line)

So using PATHMAP we can make GG aware about the actual location of the red logs & archive logs on the remote server and the mapped location on the system where GG is running (It is somewhat like db_file_name_convert option for Data Guards).

We fire some DMLs on the source database and then run stats command for the Extract

GGSCI (goldengate1) 93> stats ermt01 totalsonly *

Sending STATS request to EXTRACT ERMT01 ...

Start of Statistics at 2012-05-26 05:17:05.

Output to ./dirdat/er:

Cumulative totals for specified table(s):

*** Total statistics since 2012-05-26 04:51:10 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                1.00
        Total discards                               0.00
        Total operations                             2.00
.
.
.

End of Statistics.

GGSCI (goldengate1) 94>

For more details have a look at the GG reference guide (Page 402).

 

Tuning GoldenGate Extract Pump performance

2 Replies

Just a quick note/post about the significance of COMPRESS and TCPBUFSIZE parameter in performance of a GoldenGate Extract Pump process. COMPRESS helps in compressing the outgoing blocks hence helping in better utilization of the bandwidth from source to target. GG is going to uncompress the blocks before writing them to the remote trail file on the target. Compression ratios of 4:1 or better can be achieved. Of course, use of COMPRESS may result in increased CPU usage on both the sides.

TCPBUFSIZE controls the size of the TCP buffer socket that is going to be used by the Extract. If the bandwidth allows, it will be a good idea to send larger packets. So depending upon the available bandwidth one can experiment with the values of TCPBUFSIZE. At one of the client sites, I saw a great increase in the performance after setting TCPBUFSIZE. The trail file (10 MB size) that was taking almost a minute to transfer started getting through in few seconds after setting this parameter. Documentation (http://docs.oracle.com/cd/E35209_01/doc.1121/e29399.pdf page 313) provides the method to calculate the optimum value for TCPBUFSIZE for your environment.

While using TCPBUFSIZE value for TCPFLUSHBYTES (at least equal to the value of TCPBUFSIZE) also needs to be set. It is the buffer that collects the data that is going to be transferred to the target.

These parameters can be used like following:

rmthost, mgrport, compress, tcpbufsize 10000, tcpflushbytes 10000

Also see the metalink note 1071892.1.