The structure of the table is as follows:
Name Null? Type ----------------- -------- --------------- ID NOT NULL NUMBER FIRST_NAME VARCHAR2(20) LAST_NAME VARCHAR2(30)
To test the conflict resolution, I will update the same row of the table with different values at same time on the two databases. This will be archieved through scheduler jobs. First,the follwoing procedure is created:
create or replace procedure update_strm_tab3(l_id in number, l_first_name in varchar2, l_last_name in varchar2 ) as begin update strm_tab3 set first_name = l_first_name, last_name=l_last_name where id= l_id; commit; end; /
Second, the folliwng scheduler jobs that have same start date will be submitted:
At TEST10G:
begin
dbms_scheduler.create_job (
job_name => 'update_strm_tab3_job',
job_type => 'PLSQL_BLOCK',
job_action => 'begin update_strm_tab3(6,''fn6d_10g'', ''ln6d_10g''); end;',
start_date => '14-DEC-2008 06:35:00 PM',
enabled => true,
comments => 'Update strm_tab3 ');
end;
/
At TEST02DB
begin
dbms_scheduler.create_job (
job_name => 'update_strm_tab3_job',
job_type => 'PLSQL_BLOCK',
job_action => 'begin update_strm_tab3(6,''fn6d_02db'', ''ln6d_02db''); end;',
start_date => '14-DEC-2008 06:35:00 PM',
enabled => true,
comments => 'Update strm_tab3 ');
end;
/
Note: to check scheduler job, isssue the following statment:
col owner format a15
col next_run_date format a20
select owner, job_name, state, last_run_duration,
next_run_date
from dba_scheduler_jobs
where owner='DENIS';
I have tested the following cases:
Case 1 - No conflict resolution method is set up
Results:
At TEST10G
ID FIRST_NAME LAST_NAME ---------- -------------------- ------------------------------ 6 fn6d_10g ln6d_10g
At TEST02G
ID FIRST_NAME LAST_NAME ---------- -------------------- ------------------------------ 6 fn6d_02db ln6d_02db
The apply processes status became 'ABORT' on both database, for example
we can get something like:
++ APPLY PROCESS INFORMATION ++ APPLY_NAME : STRMADMIN_APPLY_2 MAX_APPLIED_MESSAGE_NUMBER : STATUS : ABORTED STATUS_CHANGE_TIME : 14-dec-2008 15:10:09 ERROR_NUMBER : 26714 ERROR_MESSAGE : ORA-26714: User error encountered while applying
Case 2 - Conflict resolution - OVERWRITE at TEST02DB
OVERWRITE - When a conflict occurs, the OVERWRITE handler replaces the current value at the destination database with the new value in the LCR from the source database.
According Oracle doc: You must specify a conditional supplemental log group at the source database for all of the columns in the column_list at the destination database
I issued the following statment at TEST10G and TEST02DB:
ALTER TABLE denis.strm_tab3 ADD SUPPLEMENTAL LOG GROUP log_group_jobs_cr (first_name, last_name);
I then set an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package, using the prebuilt method OVERWRITE.
I issue the following statement at TEST02DB:
DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'first_name';
cols(2) := 'last_name';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'denis.strm_tab3',
method_name => 'OVERWRITE',
resolution_column => 'first_name',
column_list => cols);
END;
/
Note:
The resolution_column is not used for OVERWRITE and DISCARD methods, but one of the columns in the column_list still must be specified.
RESULTS:
1. We can see the following on both database.
ID FIRST_NAME LAST_NAME ---------- -------------------- ------------------------------ 6 fn6d_10g ln6d_10g
2. The apply process is in abort status at TEST10G, and apply process at TEST02DB works fine
To prepare for the next test, I restart the apply process at TEST10G, and issue the
following statment at TEST02DB:
denis@TEST02DB> update strm_tab3 set first_name='first', last_name='last' where id=6;
1 row updated.
denis@TEST02DB> commit;
I verified that this row is updated at both databases.
Case 3 - Conflict resolution - OVERWRITE at TEST02DB, DISCARD at TEST10G
DISCARD - When a conflict occurs, the DISCARD handler ignores the values in the LCR from the source database and retains the value at the destination database.
I issue the following statement at TEST10G to set up the DISCARD handler:
DECLARE
cols DBMS_UTILITY.NAME_ARRAY;
BEGIN
cols(1) := 'first_name';
cols(2) := 'last_name';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name => 'denis.strm_tab3',
method_name => 'DISCARD',
resolution_column => 'first_name',
column_list => cols);
END;
/
Then I submitted the scheduler job again.
Results:
This time the row is updated as expected and apply processes run fine at both database. So in this configuration, if there are update conflicts, the statment issued at TEST10G will take effect. The statment issued at TEST02G will be ignored.
Note: we can issue the following query to check information about update conflict Handlers.
COLUMN OBJECT_OWNER HEADING 'TableOwner' FORMAT A5 COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A12 COLUMN METHOD_NAME HEADING 'Method' FORMAT A12 COLUMN RESOLUTION_COLUMN HEADING 'ResolutionColumn' FORMAT A13 COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A30 SELECT OBJECT_OWNER, OBJECT_NAME, METHOD_NAME, RESOLUTION_COLUMN, COLUMN_NAME FROM DBA_APPLY_CONFLICT_COLUMNS ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;
The output looks like:
At TEST10G
Table Resolution Owner Table Name Method Column Column Name ----- ------------ ------------ ------------- ------------------------ DENIS STRM_TAB3 DISCARD FIRST_NAME LAST_NAME DENIS STRM_TAB3 DISCARD FIRST_NAME FIRST_NAME
At TEST02DB
Table Resolution Owner Table Name Method Column Column Name ----- ------------ ------------ ------------- ----------------- DENIS STRM_TAB3 OVERWRITE FIRST_NAME LAST_NAME DENIS STRM_TAB3 OVERWRITE FIRST_NAME FIRST_NAME
To explore more about conflict resolution, check Chapter 3 Streams Conflict Resolution of the Oracle online documentation.
No comments:
Post a Comment