Carga Inicial – Goldengate

Initial load is nothing but moving data from the source database to the target database. There are several ways of doing this, such as RMAN, EXP/IMP etc … but also GG has its own mechanism to move data. As discussed before, the benefit of using tool other than RMAN gives us flexibility to modify/reorganize and tune the storage parameters for objects in the target DB.

GG has several ways of doing the initial load. It’s primary design is to make the initial load directly on target database, while the DB is up and running, so no downtime is needed. However, we are using a different approach and we use a staging database which is an identical copy of production (data has the same SCN), because making the initial load on directly on production DB can be very time consuming and even Oracle is suggesting that other ways/tools to be used when doing the initial load.

GG supports several configuration for doing the initial load; ones that I’ve used are:

– File To Replicat

A simple method that allows the Extract process to write to a file on the target(remote) system that Replicat applies using SQL INSERT statements.

 

-- On source
EXTRACT EL02
USERID ggs_admin@source_db, PASSWORD ********
RMTHOST 192.168.1.111, MGRPORT 7809
RMTFILE ./dirdat/el02.dat, PURGE
TABLE SRC.TABLE1;
-- On target
REPLICAT rel02
SPECIALRUN
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID ggs_admin@target_db, PASSWORD ********
EXTFILE ./dirdat/el02.dat
DISCARDFILE ./dirrpt/el02.dsc, PURGE
MAP SRC.*, TARGET TGT.*;
END RUNTIME

– Direct BULK Load

The Extract process sends data from the source database tables directly to the Replicat. The Replicat process uses Oracle SQL*Loader API, which offers high data load performance, but with some “data type and security limitations”.

-- On source
EXTRACT EL01
USERID ggs_admin@source_db, PASSWORD ********
RMTHOST 192.168.1.111, MGRPORT 7809
RMTTASK REPLICAT, GROUP REL01
TABLE SRC.TABLE1;
-- On target
REPLICAT rel01
USERID ggs_admin@target_db, PASSWORD ********
ASSUMETARGETDEFS
MAP SRC.*, TARGET TGT.*;
END RUNTIME

Setup of the “INITIAL LOAD”

I have ordered the size of the tables by its size and dedicated singe extract/replicat process to the largest tables. Mid to small size tables share same extract/replicat process.
For example:

TABLE_NAME                    SIZE_GB      ASSIGNED_TO
PCCS_CLIENT_TX                11824        EL08
GMD_RESPONSE                   3200        EL07
GMD_REQUEST_BASE               2240        EL06
FP_SMS_INTERFACE_OLD           3776        EL05
FP_SMS_INTERFACE_OLD2          2176        EL05
TASKS_CONTRACTS                 896        EL04
DIRECTORY_NUMBER_BCK            832        EL04
FP_SMS_INTERFACE                456        EL04
WAT_ARCHIVE_CO_2                392        EL04
FP_SMS_INTERFACE_MAR09          208        EL04
WAT_ARCHIVE_CO_ALL              200        EL04
WAT_DUNNING_ERROR               144        EL04
WAT_DUNNING_HISTORY              80        EL04

Direct Bulk Load

-- On the source server
./ggsci
GGSCI (source_server) > ADD EXTRACT EL08, SOURCEISTABLE
GGSCI (source_server) > EDIT PARAMS EL08
EXTRACT EL08
USERID ggs_admin@source_db, PASSWORD ********
RMTHOST 192.168.1.111, MGRPORT 7809
RMTTASK REPLICAT, GROUP REL08
TABLE SRC.PCCS_CLIENT_TX;
-- On the target server
./ggsci
GGSCI (target_server) > ADD REPLICAT rel08, SPECIALRUN
GGSCI (target_server) > EDIT PARAMS rel08
REPLICAT rel08
USERID ggs_admin@target_db, PASSWORD ********
ASSUMETARGETDEFS
MAP SRC.*, TARGET TGT.*;
END RUNTIME

Switch back to the source server, login to GG and start the EXTRACT process; REPLICAT process will be started automatically by the MGR process.

-- On the source server
./ggsci
GGSCI (source_server) > START EXTRACT EL08

Tail the ggserr.log on both source and target and check if the extract/replicat has started. You should see messages like:

-- From source

2012-04-19 15:48:39  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, el08.prm:  EXTRACT EL08 starting.
-- From target
2012-04-19 15:07:25  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, rel08.prm:  REPLICAT REL08 starting.
If both processes were running without errors and there was no network outage, than we have successfully made the load. The log should look like:
-- From source

2012-04-20 07:15:30  INFO    OGG-00991  Oracle GoldenGate Capture for Oracle, el08.prm:  EXTRACT EL08 stopped normally.
-- From target
2012-04-20 06:34:16  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, rel08.prm:  REPLICAT REL08 stopped normally.
File To Replicat
-- On the source server
./ggsci
GGSCI (source_server) > ADD EXTRACT EL02, SOURCEISTABLE
GGSCI (source_server) > EDIT PARAMS EL02
EXTRACT EL02
USERID ggs_admin@source_db, PASSWORD ******
RMTHOST 192.168.1.111, MGRPORT 7809
RMTFILE ./dirdat/el02.dat, PURGE
TABLE SRC.PROCESS_PACKAGE;
TABLE SRC.PROCESS_CUST;
TABLE SRC.PROCESS_CONTR;
-- On the target server
./ggsci
GGSCI (target_server) > ADD REPLICAT rel02, EXTFILE ./dirdat/el02.dat
GGSCI (target_server) > EDIT PARAMS rel02
REPLICAT rel02
SPECIALRUN
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID ggs_admin@target_db, PASSWORD ********
EXTFILE ./dirdat/el02.dat
DISCARDFILE ./dirrpt/el02.dsc, PURGE
MAP SRC.*, TARGET TGT.*;
END RUNTIME

Switch back to the source server, change directory to GG home and start the extract from the OS shell.

nohup time ./extract paramfile dirprm/el02.prm reportfile dirrpt/el02.rpt &

wait s few minutes and tail the ggserr.log until the EL02 is started, then switch back to the target server, change directory to GG home and start the replicat from the OS shell.

nohup time ./replicat paramfile dirprm/rl02.prm reportfile dirrpt/rl02.rpt &

In order to see if both extract/replicat have been started correctly, check the ggserr.log on both source/target server and login to GG and issue:

-- On the source server
./ggsci
GGSCI (source_server) > info extract el02
EXTRACT    EL02      Last Started 2012-04-14 12:13   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Table TGT.PROCESS_PACKAGE
                     2012-04-14 12:14:38  Record 94328
Task                 SOURCEISTABLE
-- On the target server
./ggsci
GGSCI (target_server) > info replicat rel02
REPLICAT   REL02     Initialized   2012-04-19 11:16   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:18:32 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

Lag or Latency is how long it takes to replicate a transaction from the source database to the target. In GG terminology, lag can be measured at different intervals in the process, such as:

– Source to extract: The time taken for a record to be processed by the EXTRACT compared to the commit timestamp in the database
– Replicat to target: The time taken for the last record to be processed by the Replicat compared to the record creation time in the trail file

-- On the target server
-- Get LAG info
./ggsci
GGSCI (target_server) > lag replicat rel02
Sending GETLAG request to REPLICAT REL02 ...
Last record lag: 0 seconds.

You can get performance info by running:

-- On the target server
-- Get performance info
./ggsci
GGSCI (target_server) > stats replicat rel02, totalsonly *, reportrate hr
GGSCI (target_server) > stats replicat rel02, totalsonly *, reportrate min
GGSCI (target_server) > stats replicat rel02, totalsonly *, reportrate sec
GGSCI (target_server) > stats replicat rel02, totalsonly SYSADM.PROCESS_PACKAGE, reportrate min
GGSCI (target_server) > stats replicat rel02, totalsonly TGT.*, reportrate min

Finish.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em GOLDENGATE. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s