What is the recommended way to use RANGE function with EXTRACT.

What is the recommended way to use RANGE function with EXTRACT.

 

Q1) Can this be done with OGG datapump (e.g. multiple trail files)?

 

 

Q2) Must there be an individual extract for each remote trail file generated?

 

Fix

A1) Yes. EXTRACT can be used to capture all tables and write to a local trail. Then use EXTRACT PUMP with @RANGE to write 2 trails to the target.

 

The most important consideration when using @RANGE is that the tables in different @RANGE groups cannot be related by foreign keys. If there are foreign key relationships, those tables must be included in the same @RANGE group.

 

A2) No. A single EXTRACT can write multiple trails. Here’s an example of an EXTRACT writing 2 trails using @RANGE on selected table to balance the load between the trails for those tables:

 

ADD EXTRACT ex_st, tranlog, begin now

ADD EXTTRAIL ./dirdat/ss, EXTRACT ex_st

ADD EXTTRAIL ./dirdat/tt, EXTRACT ex_st

 

— GGS EXTRACT Param File

 

EXTRACT ex_st

 

USERID ggs, PASSWORD ggs

 

EXTTRAIL ./dirdat/ss

TABLE ggs.scustmer, FILTER (@RANGE (1, 2, CUST_CODE));

TABLE ggs.scustord;

TABLE ggs.order, FILTER (@RANGE (1, 2, ORDER_ID));

 

 

EXTTRAIL ./dirdat/tt

TABLE ggs.scustmer, FILTER (@RANGE (2, 2, CUST_CODE));

TABLE ggs.test1;

TABLE ggs.order, FILTER (@RANGE (2, 2, ORDER_ID));

 

Here’s an example of EXTRACT PUMP reading from a single trail but using @RANGE to write 2 trails:

 

dd extract ep_st, exttrailsource ./dirdat/aa

add rmttrail ./dirdat/ss, extract ep_st

add rmttrail ./dirdat/tt, extract ep_st

 

— GGS EXTRACT PUMP Param

 

EXTRACT ep_st

 

USERID ggs, PASSWORD ggs

 

RMTHOST arbazan, MGRPORT 8091

 

RMTTRAIL ./dirdat/ss

TABLE ggs.scustmer, FILTER (@RANGE (1, 2, CUST_CODE));

TABLE ggs.scustord;

TABLE ggs.order, FILTER (@RANGE (1, 2, ORDER_ID));

 

 

RMTTRAIL ./dirdat/tt

TABLE ggs.scustmer, FILTER (@RANGE (2, 2, CUST_CODE));

TABLE ggs.test1;

TABLE ggs.order, FILTER (@RANGE (2, 2, ORDER_ID));

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 ORACLE 11gR2. 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