Como configurar replicação de DDL com Oracle Goldengate

Tutorial 5: How to Configure Goldengate DDL Replication?

February 23, 2016 by Natik Ameen 12 Comments

Goldengate supports the replication of DDL commands, operating at a schema level, from one database to another.

By default the DDL replication is disabled on the source database (extract side) but is enabled on the target Database (replicat side). Learn more on how to configure Goldengate DDL Replication.

Configure Goldengate DDL Replication

Prerequisite Setup
Navigate to the directory where the Oracle Goldengate software is installed.

Connect to the Oracle database as sysdba.

sqlplus sys/password as sysdba

For DDL synchronization setup, run the marker_setup.sql script. Provide OGG_USER schema name, when prompted.

Here the OGG_USER is the name of the database user, assigned to support DDL replication feature in Oracle Goldengate

SQL> @marker_setup.sql

Then run the ddl_setup.sql script. Provide the setup detail information below.

SQL> @ddl_setup.sql

For 10g:

Schema Name : OGG_USER
Installation mode : initialsetup
To proceed with the installation : yes

For 11g:

Start the installation : yes
Schema Name : OGG_USER
Installation mode : initialsetup

For 12c:

In Oracle database 12c, DDL replication does not require any setup of triggers as it is natively supported at the database level.

So none of the marker, ddl_setup or any of the other scripts need to be run. All that is required is including the “DDL INCLUDE MAPPED” parameter in the Extract parameter file as shown in the last step.

Run the role_setup.sql script. Provide OGG_USER schema name, when prompted.

SQL> @role_setup.sql

Then grant the ggs_ggsuser_role to the OGG_USER.

SQL> grant ggs_ggsuser_role to OGG_USER;

Run the ddl_enable.sql script as shown in below command:

SQL> @ddl_enable;

Run the ddl_pin.sql script as shown below.

SQL> @ddl_pin OGG_USER;

Configure Extract Process with DDL Replication

The following extract ESRC01 was configured previously. Adding “DDL INCLUDE MAPPED” enables extracting the DDL which ran in the database. Here the “MAPPED .. TABLE” are all tables specified in [schema_name].*.

On restart of the ESRC01 process all DDL on the speicfied tables will be picked up and placed in the trail file for applying to the destination database.

EXTRACT ESRC01
USERID OGG_USER PASS_WORD OGG_USER
EXTTRAIL ./dirdat/st
TRANLOGOPTIONS EXCLUDEUSER OGG_USER
DDL INCLUDE MAPPED
TABLE APPOLTP01.*;

Don’t forget to add DDL INCLUDE MAPPED in the Pump and Replicat processes.

DDL replication setup on source completed!

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
Nota | Esse post foi publicado em GOLDENGATE e marcado , , , , , , , , , . Guardar 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