API to Retrieve runtime Replication Parameters
We often get thousands of replication conflits/errors at current system. A resolution is generally looking at EM console and resolving each error manually. Please see examples below.
This approach is working well until we have less than some thousands error x’actions at DEFERROR. Anything above that is causing a performance hit. Also DEFERROR /DEFTRANS get highly fragmented adding to the current problem. A solution would be writing a PLSQL API to retrieve runtime parameters as and when needed than using EM console to retrieve millions of rows.
 |
SELECT argcount, schemaname, packagename, procname
FROM defcall WHERE deferred_tran_id in
(select deferred_tran_id from deftran where rownum <100)
ARGCOUNT SCHEMANAME PACKAGENAME PROCNAME 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT 17 CVD_SYSTEM VOUCHER REP_UPDATE 9 CVD_SYSTEM VOUCHER_EVENT REP_INSERT
SELECT e.destination, e.deferred_tran_id, e.origin_tran_db, e.origin_tran_id, e.start_time, count(e.callno) FROM sys.deferror e, sys.defcall c WHERE e.deferred_tran_id = c.deferred_tran_id GROUP BY e.destination, e.deferred_tran_id, e.origin_tran_db, e.origin_tran_id, e.start_time ORDER BY 1
DESTINATION DEFERRED_TRAN_ID ORIGIN_TRAN_DB ORIGIN_TRAN_ID START_TIME COUNT(E.CALLNO)CPI_O5CA.O2 105.45.83730 CPI_O5CB.O2 120.23.60368 09/11/2008 20:24:50 3 CPI_O5CA.O2 107.12.84097 CPI_O5CB.O2 121.41.62034 09/11/2008 20:24:51 1 CPI_O5CA.O2 107.13.84100 CPI_O5CB.O2 123.47.50472 09/11/2008 20:24:52 3 CPI_O5CA.O2 108.1.83596 CPI_O5CB.O2 123.42.50472 09/11/2008 20:24:52 1
SELECT error_msg FROM deferror WHERE deferred_tran_id = '107.12.84097' AND callno = 2 ERROR_MSG ORA-01403: no data found
SELECT t.deferred_tran_id, d.dblink, t.start_time, count(c.callno)
FROM sys.deftran t, sys.defcall c, sys.deftrandest d
WHERE d.deferred_tran_id = t.deferred_tran_id
AND c.deferred_tran_id = t.deferred_tran_id
GROUP BY t.deferred_tran_id, t.start_time, d.dblink ORDER BY 3 desc
Find out the Argument Count and Call number : select deferred_tran_id, callno, argcount, procname, packagename, schemaname from defcall;
Substitute Transaction ID, Argument Number and Call Number to locate parameter for that Call DECLARE
atype INTEGER;
csform NUMBER;
arg_no NUMBER;
callno NUMBER;
tid VARCHAR2(22);
outval VARCHAR2(2000);
BEGIN
arg_no:=1;
callno:='0';
tid:='105.45.83730';
atype:=dbms_defer_query.get_arg_type(callno, arg_no, tid);
csform:=dbms_defer_query.get_arg_form(callno, arg_no, tid);
IF atype = 2 THEN
outval:=to_char(dbms_defer_query.get_number_arg(callno, arg_no, tid));
ELSIF atype = 96 and
csform = 1 THEN
outval:=dbms_defer_query.get_char_arg(callno, arg_no, tid);
ELSIF atype = 96 and
csform = 2 THEN
outval:=translate(dbms_defer_query.get_nchar_arg(callno, arg_no, tid) USING CHAR_CS);
ELSIF atype= 1 and
csform = 1 THEN
outval:=dbms_defer_query.get_varchar2_arg(callno, arg_no, tid);
ELSIF atype= 1 and
csform = 2 THEN
outval:=translate(dbms_defer_query.get_nvarchar2_arg(callno, arg_no, tid) USING CHAR_CS);
ELSIF atype = 12 THEN
outval:=to_char(dbms_defer_query.get_date_arg(callno, arg_no, tid), 'DD-MON-YYYY HH24:MI:SS');
ELSIF atype = 23 THEN
outval:=rawtohex(dbms_defer_query.get_raw_arg(callno, arg_no, tid));
ELSIF atype = 11 THEN
outval:=dbms_defer_query.get_rowid_arg(callno, arg_no, tid);
ELSE
outval:='-Binary Value-';
-- RAISE NO_DATA_FOUND;
END IF;
dbms_output.put_line(outval);
END;
Leave a Reply
You must be logged in to post a comment.