Gerenciamento de conflitos com Goldengate

Oracle GoldenGate
Best Practice – Oracle GoldenGate Conflict Management
Version 1
Date: November 11, 2011
Ananth R. Tiru
Senior Solutions Architect
Center of Excellence
Table of Contents
Introduction: ………………………………………………………………………………………………………………………………. 3
Requirements: …………………………………………………………………………………………………………………………. 3
Methodology: ………………………………………………………………………………………………………………………….. 4
Approach: ……………………………………………………………………………………………………………………………….. 4
Implementation: ……………………………………………………………………………………………………………………… 5
Create Users: ……………………………………………………………………………………………………………………….. 5
Create Tables ………………………………………………………………………………………………………………………. 5
Add Supplemental Logging ……………………………………………………………………………………………………. 6
Configure Extract, Replicat and Stored Procedure ……………………………………………………………………. 6
Creating and starting extract on the source …………………………………………………………………………….. 6
Creating and starting replicat on target …………………………………………………………………………………… 7
ETAB.PRM – Implementation highlights …………………………………………………………………………………… 7
RTAB.prm – Implementation highlights……………………………………………………………………………………. 7
cm_procedure.sql – Implementation highlights………………………………………………………………………… 9
Testing: …………………………………………………………………………………………………………………………………. 11
Update Tests: …………………………………………………………………………………………………………………….. 11
Insert Tests: ……………………………………………………………………………………………………………………….. 11
Delete Tests: ……………………………………………………………………………………………………………………… 12
Recommendations: ………………………………………………………………………………………………………………… 12
Conclusion: ……………………………………………………………………………………………………………………………. 12
Key Reviewers ……………………………………………………………………………………………………………………….. 12
Appendix: ……………………………………………………………………………………………………………………………… 13
Extract Param file – Etab.prm ……………………………………………………………………………………………… 13
Replicat Param file – Rtab.prm …………………………………………………………………………………………….. 14
Conflict Handling Stored Procedure – cm_procedure ……………………………………………………………… 15
Introduction:
In a multi-master database environment where the occurrence of conflicts is possible during replication, it is highly desirable to have a conflict management scheme which is enforced as an exception rather than as a norm. Typically, the chances of conflicts occurring during replication are low and hence, checking for conflicts every time before a record is applied on the target is not efficient. However, at the same time it is highly important to ensure that when a conflicting scenario is encountered it is handled in the right manner.
The goal of this document is to illustrate a generic approach which would handle conflicts as an exception. The approach eliminates the need to check for conflict every time before a record is applied to the target table. It will check and handle conflicts only if an exception occurs when attempting to apply a record on the target table.
The approach described is the key focus of this document. It illustrates the configuration and the procedure for handling conflicts as an exception using OGG. The sample implementation is provided to illustrate the approach and is specific to the requirements listed in this document and Oracle database. However, depending on the actual requirements and use cases different implementation should be pursued. It is therefore, highly recommended to evaluate the implementation provided in this document from a functional, performance, and maintenance perspectives before adopting it in an actual use case.
Requirements:
It is highly desired to have a robust conflict management mechanism which addresses the following requirements for a two node multi-master database environment.
 Identify and handle conflicts due to inserts
o Potentially the same record could be inserted on source and target. During replication it is required to keep the record with the latest timestamp. The rejected record should be moved to an exception table for auditing purposes.
 Identify and handle conflicts due to updates
o Potentially the same record could be modified on the source and target. During replication it is required to keep the record with the latest timestamp. The rejected record should be moved to an exception table for auditing purpose.
o Potentially the record modified on the source could have been deleted on the target. During replication the record should be moved to an exception table.
o PK value of the record could be modified. If PK is modified, the rules listed above apply.
 Identify and handle conflicts due to deletes
o Potentially when propagating deletes from source to target the record on the target could have already been deleted. During replication it is required to move the record to an exception table for auditing purposes.
Methodology:
Given the possibility of occurrences of conflict during various database operations and the desired resolution when these conflicts arise, the approach illustrated below provides a way to deal with conflicts as an exception which will address all the aspects of the requirements listed above.
OGG will always assume that there is no conflict when applying a record on the target and will rely on the DB to throw an exception when there is a conflict. The exception will then be handled based on the above requirement.
Approach:
 Use KEYCOLS in both the extract and replicat. Include the table primary key(s) and the timestamp columns to the keycols.
o By doing this OGG will automatically include before image of the record which will be used by the replicat to identify the record on the target table. If the before image matches the current image on the target table replicat will apply the record. However, if the target record has changed, which is typically indicated by the timestamp column, replicat will not be able to find the record and an exception will be thrown.
o Note, supplemental logging must be turned on to include the timestamp in addition to the primary keys.
 Use multiple maps for the a given source and target table. Typically, a table will have three map statements. The first map statement will attempt to apply the record, the second map statement will handle the DB exception that may be generated by the first map statement and invokes the conflict management logic. Finally, the third map statement will handle the exception, if any, from the second map statement and will insert the incoming record into the exception table.
 The map dealing with handling the DB exception can either invoke an external stored procedure or implement the logic in a SQLEXEC (within the replicat param file) to detect and resolve conflict. The complexity of the stored procedure or the logic implemented in a SQLEXEC will depend on the requirements and use case.
Implementation:
The implementation illustrated below attempts to address the requirements using the approach described in a 2 node multi-master database environment.
Create Users:
The implementation was tested on a 2 node multi-master database with source schema named – atiru_east1 and target schema named – atiru_west1 with password for both the schema set to ‘oracle’. Two OGG users named atiru_gguser1 and atiru_gguser2 was created on source and target with password for both the users set to ‘oracle’
Create Tables
The following table, MMS_TEST, will be used as the sample table and is deployed in a 2 node multi-master database environment for which conflict management is desired. The table MMS_TEST_EXCEPTION is created to store records resulting from the conflict management strategy that is adopted based upon the requirements and use case.
Create table MMS_TEST
(CITY VARCHAR2(64),
CODE VARCHAR2(36),
INFORMATION VARCHAR2(500),
PAYLOAD BLOB,
create_timestamp number,
modify_date timestamp);
alter table MMS_TEST add constraint MD_CITY_PK primary key (CITY, CODE);
–Table to store the exception records
Create table MMS_TEST_EXCEPTION
(CITY VARCHAR2(64),
CODE VARCHAR2(36),
INFORMATION VARCHAR2(500),
PAYLOAD BLOB,
create_timestamp number,
modify_date timestamp);
Add Supplemental Logging
After successfully creating the table, turn on supplemental logging on both the source and target for the table MMS_TEST. The following box illustrates adding supplemental logging for the source table.
Configure Extract, Replicat and Stored Procedure
The following artifacts which provide the implementation as described in the approach discussed above accompany this document. Also, the appendix of this document lists the code present in each of the following files.
o Etab.prm – Extract param file for the source.
o Rtab.prm – Replicat param file for the target.
o Cm_procedure.sql – Stored Procedure to resolve conflicts, invoked by replication when an exception occurs when applying the record on the target.
Modify the param files and cm_procedure to properly reflect the schema names in the actual environment. Import the cm_procedure to the target schema. Create and start the extract and replicat on the source and target OGG environment respectively.
Using the extract and replicat param files provided, create another set of extract and replicat param file for replicating from target to source. Import the cm_procedure onto the source schema. Register the extract and replicat and start them as described above. Note, performing this step is optional when the focus is getting an conceptual understanding of the approach and to execute the test cases listed below, but it is mandatory in an actual production multi-master database scenario.
Creating and starting extract on the source
— Log on to your source database.
GGSCI>dblogin userid atiru_east1 password oracle
— This will add supplemental log data for columns – city, code and modify_date on the source
GGSCI>add trandata atiru_east1.mms_test, cols(modify_date)
– Log into source DB. Ensure the OGG user atiru_gguser2 is created on the source DB.
GGSCI> DBLOGIN USERID atiru_gguser1 PASSWORD oracle
–Configure an extract to read from the transaction logs.
GGSCI> ADD EXTRACT ETAB, TRANLOG, BEGIN NOW
–Configure a remote trail to which the extract will write.
GGSCI > ADD RMTTRAIL ./dirdat/ta, EXTRACT ETAB, MEGABYTES 5
–Start the extract.
GGSCI> start etab
Creating and starting replicat on target
ETAB.PRM – Implementation highlights
Some of the important points to be noted in the extract parameter file on the source. See Appendix for full listing.
Note: Do not just specify the extra columns, because when a table has a primary key or unique index, the KEYCOLS specification will override them. Using KEYCOLS in this way ensures that before images are available for updates to the key or index columns.
RTAB.prm – Implementation highlights
Some of the important points to be noted in the replicat parameter file on the target. See Appendix for full listing.
Typically for a given table that requires conflict management there are three map statements. The first map statement assumes no conflicts and attempts to apply the DML on the target table. Using the
– Log into target DB. Ensure the OGG user atiru_gguser2 is created on the target DB.
GGSCI> DBLOGIN USERID atiru_gguser2 PASSWORD oracle
— Add checkpoint table, use the GLOBAL file to specify the name of the checkpoint table
GGSCI> add checkpointtable
–Add replicat on the target
GGSCI> ADD REPLICAT RTAB, EXTTRAIL ./dirdat/ta
–Start Replicat
GGSCI>start rtab
–In a bi-direction replication configuration the following parameter will ensure that the updates performed by replicat logged as ‘atiru_gguser1’ on the source DB will not be picked up the extract.
TRANLOGOPTIONS EXCLUDEUSER atiru_gguser1
–Specify KEYCOLS and include the primary key and the timestamp column. Using KEYCOLS will facilitate the conflict detection when applying the record on the target.
TABLE atiru_east1.mms_test, keycols (city, code, modify_date);
keycols specified, for an update operation, the replicat will use the before image of the keycols and attempt to find the record on the target, if it is successful in finding the record it will update record. However, if it cannot find the record either because the record was updated on the target, in which case the before image of the incoming record will not match the record, or the record was deleted, it will get an exception.
The second map statement will handle the exception thrown by the first map statement and will invoke the stored procedure. Based on the requirements the store procedure will implement an appropriate conflict management logic and return a value indicating whether the replicat should apply the record on the target or discard the record and throw an exception.
Notice the above map statement uses a filter clause which evaluates the return value from the stored procedure and determines whether or not to apply the record. The filter will apply the incoming record to the target if the stored procedure returns a value equal to ‘1’ otherwise it will raise an exception.
The third map statement will handle the exception thrown by the second map statement and insert the incoming record into an exception table.
–First map statement for handling DML for the given table.
–Assume no conflicts and apply record to the target.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
keycols (city, code, modify_date),
COLMAP ( USEDEFAULTS );
–Second map statement for handling DB exceptions, e.g. ORA-1403, thrown by the first map statement for the given table.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
EXCEPTIONSONLY,
SQLEXEC (SPNAME atiru_west1.cm_procedure, ID detect,
PARAMS (i_city=city, i_code=code, i_modify_date=modify_date, ib_city=before.city, ib_code=before.code, ib_modify_date=before.modify_date, i_opcode=@getenv (“GGHEADER”, “OPTYPE”)),
EXEC SOURCEROW,
BEFOREFILTER),
FILTER ((@getval (detect.o_result) = 1),
RAISEERROR 9999),
keycols (city, code),
COLMAP (USEDEFAULTS);
cm_procedure.sql – Implementation highlights
The implementation of the stored procedure invoked by the map statement to handle conflict is highly dependent on the requirements. Also, depending on the requirement implementing the logic in a SQLEXEC statement within the replicat param file can be considered. Based on the requirement listed above, it was deemed that using a stored procedure to implement the conflict management logic was appropriate.
The procedure takes the current and before values of the primary key(s) and timestamp, the database operation type and returns either a ‘0’ or ‘1’ to indicate to replicat whether to reject or apply the record respectively.
First the procedure checks for the existence of the record in the target. If the record is found it continues with the processing. However, if the record is not found it handles the exception thrown by the database and depending upon the DML it returns an appropriate value . Example, if the database operation is ‘update’ and the record is not found then it returns ‘0’.
–Third map statement for handling exception thrown by the second map statement as a result of the logic implemented for conflict management.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test_exception,
INSERTALLRECORDS,
EXCEPTIONSONLY,
COLMAP (USEDEFAULTS);
CREATE OR REPLACE PROCEDURE “ATIRU_WEST1″.”CM_PROCEDURE” (
o_result OUT NUMBER,
i_opcode IN VARCHAR2,
i_city IN VARCHAR2,
i_code IN VARCHAR2,
i_modify_date IN TIMESTAMP,
ib_city IN VARCHAR2 =null,
ib_code IN VARCHAR2 =null,
ib_modify_date IN TIMESTAMP =null,
)
Then the procedure checks for conflict based on timestamp column and based on the requirement which is to retain the latest timestamp sets the return value to either ‘0’ or ‘1’
IF i_opcode=’UPDATE’ OR i_opcode = ‘INSERT’ THEN
SELECT * into mms_record
FROM mms_test
WHERE city=i_city
AND code=i_code for update;
END IF;
IF i_opcode=’PK UPDATE’ THEN
SELECT * INTO mms_record
FROM mms_test
WHERE city=ib_city
AND code=ib_code for UPDATE;
END IF;
t_last_change_ts := mms_record.modify_date;
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ OR i_opcode=’INSERT’ THEN
IF t_last_change_ts IS NULL THEN
o_result := 1;
ELSE
IF sys_extract_utc(i_modify_date) >= sys_extract_utc(t_last_change_ts) THEN
o_result := 1;
dbms_output.put_line(‘record will be processed by replicat’);
ELSE
o_result := 0;
dbms_output.put_line(‘record will not processed by replicat’);
END IF;
END IF;
END IF;
EXCEPTION
when TOO_MANY_ROWS then
o_result :=0; return;
when NO_DATA_FOUND then
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ THEN
o_result := 0;
ELSE –insert
o_result := 1;
END IF;
Finally, it performs logging into the exception table of the record that will be replaced and returns.
Testing:
All the tests for inserts, updates and deletes can be simulated and verified on the replication flow from source to target. It is not required to have the replication from target to source to conduct the test. When conducting the tests it is assumed that the modify_date column is updated every time the record is modified.
Update Tests:
o Update a record on source and validate it gets replicated.
o Update a record on the target, update the same record on the source. Validate the source record is replicated to the target and the replaced record on the target is moved to the exception table.
o Stop the replicat ‘RTAB’. Update the record on the source, update the same record on the target. Start the replicat ‘RTAB’. Validate the target record is retained and the record from the source is moved to the exception table.
o Delete a record on the target. Update the same record on the source. Validate the record from the source is moved to the exception table.
o Perform all the above tests with PK update.
Insert Tests:
o Insert a record on source and validate it gets replicated.
o Insert a record on the target. Insert a same record (having same keys) on the source. Validate the source record is replicated to the target and the replaced record on the target is moved to the exception table.
IF o_result = 1 THEN
IF i_opcode=’UPDATE’ or i_opcode=’PK UPDATE’ THEN
INSERT INTO mms_test_exception
values mms_record;
END IF;
IF i_opcode=’INSERT’ THEN
DELETE FROM mms_test WHERE city=i_city AND code=i_code;
END IF;
END IF;
Delete Tests:
o Delete a record that exists on both the target and source on the source. Validate the record is deleted on the target.
o Delete a record on the target. Delete the same record on the source. Validate the record from the source is moved to the exception table.
Recommendations:
The exception table can be enhanced and additional code can be added in the replicat param file to capture additional information about the records that rejected because of conflict management rules. This information can be used to identify the causality of the conflicts and determine the actions that can be taken to minimize the conflicts.
Conclusion:
The key goal of this best practice document was to illustrate an approach to deal with conflict management during replication in an efficient manner. Using the approach an implementation was presented to address a set of requirements to handle conflicts. It should be noted while the scope of the implementation will depend upon the requirements; the approach presented provides an alternate and an efficient method to build the implementations.
When constructing your own conflict management solution it is important to consider the consequences of “choosing” one DML operation over another based solely on primary key and timestamp. It is possible that different columns are updated for the same PK on both the source and target system. When this occurs, choosing one update over the other could result in some information loss occurring. In our example that risk is not addressed as the stated requirement was to use all the values for the DML operation with the most current timestamp.
Key Reviewers
G. Allen Pearson
Director, CoE
Steve George
CMTS
Appendix:
Extract Param file – Etab.prm
EXTRACT ETAB
USERID atiru_gguser1, PASSWORD oracle
–Exclude records updated by replicat
TRANLOGOPTIONS EXCLUDEUSER atiru_gguser1
–Comment out the following if not using ASM
TRANLOGOPTIONS ASMUSER sys@asm1, ASMPASSWORD coe123
— Turn Bounded Recovery off, if required turn it on.
BR BROFF
RMTHOST localhost, MGRPORT 16000
RMTTRAIL ./dirdat/ta
TABLE atiru_east1.mms_test, keycols (city, code, modify_date);
Replicat Param file – Rtab.prm
REPLICAT RTAB
USERID atiru_gguser2, PASSWORD oracle
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RTAB.DSC, PURGE
ALLOWDUPTARGETMAP
REPERROR (7777, DISCARD)
REPERROR (9999, EXCEPTION)
REPERROR (DEFAULT, EXCEPTION)
— Assume no conflicts and map DML to the target
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
keycols (city, code, modify_date),
COLMAP ( USEDEFAULTS );
— Handle DB exceptions. E.g. ORA-1403, thrown by by the above map. Perform conflict management.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test,
EXCEPTIONSONLY,
SQLEXEC (SPNAME atiru_west1.cm_procedure, ID detect,
PARAMS (i_city=city, i_code=code, i_modify_date=modify_date, ib_city=before.city, ib_code=before.code, ib_modify_date=before.modify_date, i_opcode=@getenv (“GGHEADER”, “OPTYPE”)),
EXEC SOURCEROW,
BEFOREFILTER),
FILTER ((@getval (detect.o_result) = 1),
RAISEERROR 9999),
keycols (city, code),
COLMAP (USEDEFAULTS);
— Handle exception as a result of conflict management logic.
MAP atiru_east1.mms_test, TARGET atiru_west1.mms_test_exception,
INSERTALLRECORDS,
EXCEPTIONSONLY,
COLMAP (USEDEFAULTS);
Conflict Handling Stored Procedure – cm_procedure
——————————————————–
— DDL for Procedure CM_PROCEDURE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “ATIRU_WEST1″.”CM_PROCEDURE” (
o_result OUT number,
i_opcode IN VARCHAR2,
i_city IN VARCHAR2,
i_code IN VARCHAR2,
i_modify_date IN TIMESTAMP,
ib_city IN VARCHAR2 := NULL,
ib_code IN VARCHAR2 := NULL ,
ib_modify_date IN TIMESTAMP :=NULL
)
IS
t_last_change_ts TIMESTAMP;
mms_record mms_test%rowtype;
BEGIN
t_last_change_ts := NULL;
— If DML is ‘delete’ this means record is already deleted
— on the target, hence return 0
— so replicat will put this record into the exception table.
IF i_opcode =’DELETE’ THEN
o_result :=0;
RETURN;
END IF;
— Check if the record on the target exists.
— If record is not found or more than one record is found handle
— it as an exception.
— When more than one record is found then
— return 0 so replicat will put the record into the exception
— table.
— If record is not found then Return 0, if opcode is update or pk
— update so replicat will put the record in the exception table.
— Return 1, If opcode is insert, so replicat will process the
— record appropriately.
— If record found then continue processing and resolve the
— conflict.
— Return 0 if the incoming record timestamp is lesser than target
— timestamp, otherwise return 1.
— When performing conflict detection and resolution on a record,
— lock the record until the resolution is completed.
IF i_opcode=’UPDATE’ OR i_opcode = ‘INSERT’ THEN
SELECT * into mms_record
FROM mms_test
WHERE city=i_city
AND code=i_code for update;
END IF;
IF i_opcode=’PK UPDATE’ THEN
SELECT * INTO mms_record
FROM mms_test
WHERE city=ib_city
AND code=ib_code for UPDATE;
END IF;
t_last_change_ts := mms_record.modify_date;
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ OR i_opcode=’INSERT’
THEN
IF t_last_change_ts IS NULL THEN
o_result := 1;
ELSE
IF sys_extract_utc(i_modify_date) >=
sys_extract_utc(t_last_change_ts)
THEN
o_result := 1;
dbms_output.put_line(‘record will be processed by replicat’);
ELSE
o_result := 0;
dbms_output.put_line(‘record will not processed by replicat’);
END IF;
END IF;
END IF;
— Perform logging of the target record.
— In case of update, log the rejected target record into an
— exception table.
— In the case of insert, delete the target record.
IF o_result = 1 THEN
IF i_opcode=’UPDATE’ or i_opcode=’PK UPDATE’ THEN
INSERT INTO mms_test_exception
values mms_record;
END IF;
IF i_opcode=’INSERT’ THEN
DELETE FROM mms_test WHERE city=i_city AND code=i_code;
END IF;
END IF;
–Handle the exception when more than one record is found or no
–record is found.
EXCEPTION
when TOO_MANY_ROWS then
o_result :=0; return;
when NO_DATA_FOUND then
IF i_opcode =’UPDATE’ OR i_opcode =’PK UPDATE’ THEN
dbms_output.put_line(‘exception’);
o_result := 0;
ELSE –insert
o_result := 1;
END IF;
RETURN;
END cm_procedure;
/

Anúncios

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 e marcado , , , , , , , , . Guardar link permanente.

Deixe um comentário

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