otak-otak | favorite evening food

February 4, 2012

Script to fix streams error at apply side

Filed under: streams — ora62 @ 8:24 am

The script is one of my streams implementation and can save my time to not always keep catching on the dba_apply_error.

CREATE OR REPLACE PROCEDURE fix_err_dtl(in_any IN SYS.ANYDATA, arg_object_owner varchar2, arg_object_name varchar2) IS

lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
i NUMBER;
l_dmlcommand varchar2(20);
l_dummy number;
lcrrow sys.lcr$_row_list;
pkdata sys.anydata;
pknumber number;
pkdate date;
pkchar10 char(10);
pkchar4 char(4);
pkchar1 char(1);
pkvarchar2 VARCHAR2(32000);

-- field1 through field3 is unique/pk index for table test_control
l_field1 number;
l_field2 number;
l_field3 varchar2(100);

BEGIN
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();
IF arg_object_owner='TEST' and arg_object_name='TEST_CONTROL' then

IF dmlcommand in ('INSERT','UPDATE','DELETE') THEN
if dmlcommand in ('UPDATE','DELETE') THEN
lcrrow := lcr.GET_VALUES('OLD');
elsif dmlcommand = 'INSERT' THEN
lcrrow := lcr.GET_VALUES('NEW');
end if;

for i in 1..lcrrow.count loop
if lcrrow(i).column_name = 'FIELD1' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getnumber(pknumber);
l_field1 := pknumber;
elsif lcrrow(i).column_name = 'FIELD2' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getnumber(pknumber);
l_field2 := pknumber;
elsif lcrrow(i).column_name = 'FIELD3' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getvarchar2(pkvarchar2);
l_field3 := pkvarchar2;
end if;
end loop;

begin
select count(*) into l_dummy
from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;

if l_dummy >= 1 then
if dmlcommand = 'INSERT' then
delete from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;
elsif dmlcommand in ('UPDATE','DELETE') and l_dummy >= 1 then
delete from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;

insert into TEST.TEST_CONTROL(field1,field2,field3)

values(l_field1,l_field2,l_field3);

end if;
elsif l_dummy = 0 then
if dmlcommand in ('UPDATE','DELETE') then
insert into TEST.TEST_CONTROL(field1,field2,field3)

values(l_field1,l_field2,l_field3);
end if;
end if;
end;
END IF;
elsIF arg_object_owner='TEST' and arg_object_name='TEST2' then
...........
...........
...........
...........
end if; -- if last
END;
/

the above procedure will be called by this procedure:

CREATE or replace PROCEDURE fix_err(arg_apply_name varchar2) IS

cursor c1 is select LOCAL_TRANSACTION_ID, MESSAGE_NUMBER, MESSAGE_COUNT
FROM DBA_APPLY_ERROR where apply_name=arg_apply_name order by SOURCE_COMMIT_SCN;

i NUMBER;
lcr ANYDATA;
lcr2 sys.lcr$_row_record;
lcrrow sys.lcr$_row_list;
rc2 pls_integer;
l_objname varchar2(50);
l_object_owner varchar2(50);
l_MESSAGE_NUMBER number;
l_LOCAL_TRANSACTION_ID varchar2(50);
l_msg varchar2(100);
BEGIN
for h in c1 loop
for j in 1..h.MESSAGE_COUNT loop
begin
select MESSAGE_NUMBER,LOCAL_TRANSACTION_ID into

l_MESSAGE_NUMBER, l_LOCAL_TRANSACTION_ID
from dba_apply_error
where LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID;

if l_MESSAGE_NUMBER is null or l_MESSAGE_NUMBER=0 then
begin
dbms_apply_adm.execute_error(h.LOCAL_TRANSACTION_ID);
exception
when others then
null;
end;
elsif j = l_MESSAGE_NUMBER then
lcr := NULL;
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(j,

h.LOCAL_TRANSACTION_ID);
rc2 := lcr.getobject(lcr2);
l_objname := lcr2.GET_OBJECT_NAME;
l_object_owner := lcr2.GET_OBJECT_OWNER();

fix_err_dtl(lcr, l_object_owner, l_objname);

begin
i:=h.MESSAGE_COUNT;
dbms_apply_adm.execute_error(h.LOCAL_TRANSACTION_ID);
exception
when others then
select MESSAGE_NUMBER into i
from dba_apply_error
where LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID;

if l_LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID

and i<>l_MESSAGE_NUMBER then
null;
else
l_msg := ' id: '||h.LOCAL_TRANSACTION_ID||' num: '||to_char(i);
raise_application_error(-20001,'err: '||SQLCODE||' -ERROR- '||SQLERRM||l_msg);
end if;
end;
end if;
exception
when no_data_found then
null;
end;
end loop;
commit;
end loop;
commit;
END;
/

and finally create scheduler by interval 10 secs for this fix error apply:

conn strmadmin/passwd

create procedure fix_strm_err is
l_status varchar2(10);
l_cnt number;
l_name varchar2(50) := 'BPK_APPLY';
begin
select count(1) into l_cnt from dba_apply_error where apply_name=l_name;

if l_cnt >= 1 then
fix_err(l_name);
end if;

select status into l_status
from dba_apply where apply_name=l_name;

if l_status<>'ENABLED' then
dbms_apply_adm.start_apply(l_name);
end if;

end;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'PRG_FIX_STRM'
,program_type => 'PLSQL_BLOCK'
,program_action => 'begin fix_strm_err; end;'
,number_of_arguments => 0
,enabled => FALSE
,comments => NULL
);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'PRG_FIX_STRM');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'JOB_FIX_STRM'
,start_date => TO_TIMESTAMP_TZ('2012/02/01 16:59:59.467189 +07:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=SECONDLY;INTERVAL=10'
,end_date => NULL
,program_name => 'PRG_FIX_STRM'
-- ,job_class => 'BATCH_JOB_CLASS'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'RESTARTABLE'
,value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
--SYS.DBMS_SCHEDULER.LOGGING_FULL
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'JOB_PRIORITY'
,value => 1);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'JOB_FIX_STRM');
END;
/

happy streaming….:)

May 9, 2011

streams tuning on high batch jobs

Filed under: streams — ora62 @ 7:19 am
this apply parameter in streams environment is intended for the machine that has many cpus. 
the parallelis and commit_serialization will help when there are many insert, 
the streams will extremely fast enough to handle huge transaction.
but if your transaction is mandatory by update or delete, commit_serialization will depends on the scn order. 
so there are no impact to your business (but it should need careful test)

BEGIN
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => 'TXN_LCR_SPILL_THRESHOLD',
   value => '15000000');

DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_TXN_BUFFER_SIZE',
   value => '64');
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_hash_table_size',
   value => '10000000');
dbms_apply_adm.set_parameter('A_NONAME','_SGA_SIZE','8192');
dbms_apply_adm.set_parameter('A_NONAME', '_RESTRICT_ALL_REF_CONS','N');
dbms_apply_adm.set_parameter('A_NONAME', 'COMMIT_SERIALIZATION','NONE');
dbms_apply_adm.set_parameter('A_NONAME', 'PARALLELISM','64');

END;
/

exec dbms_apply_adm.stop_apply('A_NONAME');

exec dbms_apply_adm.start_apply('A_NONAME');

April 19, 2011

Skip streams capture SCN

Filed under: streams — ora62 @ 8:16 pm

I have a nice experience on streams capture 10.2.0.1
when captures is set to grab all dml at the schema levels,
and suddenly user performed CTAS, and magically oracle
ora lucky number ORA-600 coming.

got error that capture builder has error, OK well done.
I started manually the capture process and after 10 minutes
PMON always kill capture builder process.

PMON has hidden parameter by default 10 minutes
to scan dead process.

no chance to do workaround as of metalink,
the note will state that database should be upgraded
to atleast 10.2.0.3…phiew

dong sync manualy from the latest applied SCN
and then skip capture SCN to start from
the last sync SCN :(

exec dbms_capture_adm.stop_capture(capture_name => 'AQ_CAPTURE');
delete from system.logmnr_restart_ckpt$ where spare1 < nnnn ;
exec dbms_capture_adm.alter_capture(capture_name => 'AQ_CAPTURE', first_scn => nnnn, start_scn => nnnn);
exec dbms_capture_adm.start_capture(capture_name => 'AQ_CAPTURE');

by looking at the alert log, luckily only objects belongs to sysman schema
got warning…which those are not captured.

Create a free website or blog at WordPress.com.