Archive for the ‘GoldenGate’ tag
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 220.127.116.11 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).
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.
The last post was just like that. It was this GoldenGate issue that woke me up from the deep sleep to do a post after a long time .
Well it was a simple schema to schema replication setup using GoldenGate. We were using the SCN method (Metalink Doc ID 1276058.1 & 1347191.1) to do the intial load so that there is no overlvapping of transactions and the replicat runs with minimum issues. Even after following this method, the replicat was hitting
2011-10-31 19:25:17 WARNING OGG-01004 Aborted grouped transaction on 'SCHEMA.TABLE', Database error 1403 (). 2011-10-31 19:25:17 WARNING OGG-01003 Repositioning to rba 3202590 in seqno 1. 2011-10-31 19:25:18 WARNING OGG-01154 SQL error 1403 mapping SCHEMA.TABLE TO SCHEMA.TABLE. 2011-10-31 19:25:18 WARNING OGG-01003 Repositioning to rba 3468713 in seqno 1.
If we managed to bypass this error somehow, it hit:
2011-10-24 19:58:15 WARNING OGG-00869 OCI Error ORA-00001: unique constraint (SCHEMA.UK) violated (status = 1), SQL <INSERT INTO "SCHEMA"."TABLE" (<INSERT HERE> 2011-10-24 19:58:15 WARNING OGG-01004 Aborted grouped transaction on 'SCHEMA.TABLE', Database error 1 (OCI Error ORA-00001: unique constraint (SCHEMA.UK) violated (status = 1), SQL <INSERT HERE>). 2011-10-24 19:58:15 WARNING OGG-01003 Repositioning to rba 1502788 in seqno 3. 2011-10-24 19:58:15 WARNING OGG-01154 SQL error 1 mapping SCHEMA.TABLE to SCHEMA.TABLE OCI Error ORA-00001: unique constraint (SCHEMA.UK) violated (status = 1), SQL <INSERT HERE>. 2011-10-24 19:58:15 WARNING OGG-01003 Repositioning to rba 1502788 in seqno 3.
1403 means that GoldenGate couldn’t find the record it wanted to update.
00001 would mean that the record GoldenGate tried to insert was already there.
In our case, as we used SCN method so none of them was expected. So these weird errors left us totally confused. Some guys suggested that expdp was not taking a consistent image and some transactions were getting overlapped (picked up by both expdp & GG extract trail). We took the database down and repeated the exercise but oops ! it hit almost the same errors again. So it was not about consistency for sure.
Till now we haven’t been examining the contents of discard file very seriously. As the errors were pretty simple so we always suspected that some transactions were getting overlapped. Now it was high time to take some help from discard file as well . We took the before/after image of the record from the discard file and checked it in the target database & values in one or two columns were different (that is why GG couldn’t find that record). The new values were the actual hint towards the solution [It was a table storing the mail requests and their statuses. This update that GG was trying to run was updating the status from NOT-SENT TO SENT but here on the target the status was already set to 'ORA-something......']. We got the clue that something must have run on the target itself that spoiled this record and now GG is not able to find it and abending with 1403. select * from dba_jobs cleared it all. While doing the initial load with expdp/impdp, job also got imported and some of them were in not broken state. They were firing according to their schedule and making changes to data in the target. So before GG came to update/insert record the job had already done its game and the replicat was hitting different errors. We did the initial load again (this time by using flashback_scn in the running database), disabled all the jobs and ran the replicat. It went through without any errors.
So things to take care of, in such cases:
1) Disable all the triggers on the target side (or exclude triggers while running expdp)
2) Look for and disable any scheduled jobs (could be dba_jobs, dba_scheduler_jobs or cron)
Happy GoldenGate’ing !