ON CALL DBA SUPPORT

— Database blog

Archive for May, 2011

ORA-01403 NO DATA FOUND in ORACLE STREAMS

Posted by ssgottik on 09/05/2011

ORA-01403 NO DATA FOUND in ORACLE STREAMS

ORA-01403 error occurs when an apply process tries to update an existing row in the target database and the OLD_VALUES in the row LCR do not match the current values at the destination database.
Typically, one of the following conditions causes this error:
1. Supplemental logging is not specified for the columns that require supplemental logging at the source database side. This is because, LCRS from source database may not contain values for key columns.
2. There may be a problem with the primary key in the destination table. If there is no primary key mentioned for the target table or if the primary key in the target database table is different from the source database table.
3. If there is any data mismatch between source table and the target table.

TROUBLESHOOTING ORA – 01403:
STEP 1:  where you will find ORA – 01403 errors in streams?
If you query DBA_APPLY_ERROR in the Target Database or  in the database where Apply is running , you will get the list of objects or records which are out of synch or getting ORA_01403 error in the error_message column:
Run the below mentioned query:
SQL >  SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;

We have to check each and every error_message and see what is the error related to it and which command it cause this problem.
To check what the root cause of this error is, we have to use PRINT_LCR package provides by oracle: 

STEP 2 : Run below mentioned code as STRMADMIN user (streams admin user) only once
CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS
tn VARCHAR2(61);
str VARCHAR2(4000);
chr VARCHAR2(1000);
num NUMBER;
dat DATE;
rw RAW(4000);
res NUMBER;
BEGIN
IF data IS NULL THEN
DBMS_OUTPUT.PUT_LINE(‘NULL value’);
RETURN;
END IF;
tn := data.GETTYPENAME();
IF tn = ‘SYS.VARCHAR2’ THEN
res := data.GETVARCHAR2(str);
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253));
ELSIF tn = ‘SYS.CHAR’ then
res := data.GETCHAR(chr);
DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253));
ELSIF tn = ‘SYS.VARCHAR’ THEN
res := data.GETVARCHAR(chr);
DBMS_OUTPUT.PUT_LINE(chr);
ELSIF tn = ‘SYS.NUMBER’ THEN
res := data.GETNUMBER(num);
DBMS_OUTPUT.PUT_LINE(num);
ELSIF tn = ‘SYS.DATE’ THEN
res := data.GETDATE(dat);
DBMS_OUTPUT.PUT_LINE(dat);
ELSIF tn = ‘SYS.BLOB’ THEN
DBMS_OUTPUT.PUT_LINE(‘BLOB Found’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘typename is ‘ || tn);
END IF;
END print_any;
/
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN SYS.ANYDATA) IS
typenm VARCHAR2(61);
ddllcr SYS.LCR$_DDL_RECORD;
proclcr SYS.LCR$_PROCEDURE_RECORD;
rowlcr SYS.LCR$_ROW_RECORD;
res NUMBER;
newlist SYS.LCR$_ROW_LIST;
oldlist SYS.LCR$_ROW_LIST;
ddl_text CLOB;
BEGIN
typenm := lcr.GETTYPENAME();
DBMS_OUTPUT.PUT_LINE(‘type name: ‘ || typenm);
IF (typenm = ‘SYS.LCR$_DDL_RECORD’) THEN
res := lcr.GETOBJECT(ddllcr);
DBMS_OUTPUT.PUT_LINE(‘source database: ‘ ||
ddllcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE(‘owner: ‘ || ddllcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || ddllcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE(‘is tag null: ‘ || ddllcr.IS_NULL_TAG);
DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
ddllcr.GET_DDL_TEXT(ddl_text);
DBMS_OUTPUT.PUT_LINE(‘ddl: ‘ || ddl_text);
DBMS_LOB.FREETEMPORARY(ddl_text);
ELSIF (typenm = ‘SYS.LCR$_ROW_RECORD’) THEN
res := lcr.GETOBJECT(rowlcr);
DBMS_OUTPUT.PUT_LINE(‘source database: ‘ ||
rowlcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE(‘owner: ‘ || rowlcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || rowlcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE(‘is tag null: ‘ || rowlcr.IS_NULL_TAG);
DBMS_OUTPUT.PUT_LINE(‘command_type: ‘ || rowlcr.GET_COMMAND_TYPE);
oldlist := rowlcr.GET_VALUES(‘OLD’);
FOR i IN 1..oldlist.COUNT LOOP
if oldlist(i) is not null then
DBMS_OUTPUT.PUT_LINE(‘old(‘ || i || ‘): ‘ || oldlist(i).column_name);
print_any(oldlist(i).data);
END IF;
END LOOP;
newlist := rowlcr.GET_VALUES(‘NEW’);
FOR i in 1..newlist.count LOOP
IF newlist(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(‘new(‘ || i || ‘): ‘ || newlist(i).column_name);
print_any(newlist(i).data);
END IF;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(‘Non-LCR Message with type ‘ || typenm);
END IF;
END print_lcr;
/

CREATE OR REPLACE PROCEDURE print_errors IS
CURSOR c IS
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
FROM DBA_APPLY_ERROR
ORDER BY SOURCE_DATABASE, SOURCE_COMMIT_SCN;
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgcnt NUMBER;
errnum NUMBER := 0;
errno NUMBER;
errmsg VARCHAR2(128);
lcr SYS.AnyData;
r NUMBER;
BEGIN
FOR r IN c LOOP
errnum := errnum + 1;
msgcnt := r.MESSAGE_COUNT;
txnid := r.LOCAL_TRANSACTION_ID;
source := r.SOURCE_DATABASE;
errmsg := r.ERROR_MESSAGE;
errno := r.ERROR_NUMBER;
DBMS_OUTPUT.PUT_LINE(‘*************************************************’);
DBMS_OUTPUT.PUT_LINE(‘—– ERROR #’ || errnum);
DBMS_OUTPUT.PUT_LINE(‘—– Local Transaction ID: ‘ || txnid);
DBMS_OUTPUT.PUT_LINE(‘—– Source Database: ‘ || source);
DBMS_OUTPUT.PUT_LINE(‘—-Error Number: ‘||errno);
DBMS_OUTPUT.PUT_LINE(‘—-Message Text: ‘||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE(‘–message: ‘ || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid);
print_lcr(lcr);
END LOOP;
END LOOP;
END print_errors;
/
CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(128);
lcr SYS.ANYDATA;
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgcnt, errno, errmsg
FROM DBA_APPLY_ERROR
WHERE LOCAL_TRANSACTION_ID = ltxnid;
DBMS_OUTPUT.PUT_LINE(‘—– Local Transaction ID: ‘ || txnid);
DBMS_OUTPUT.PUT_LINE(‘—– Source Database: ‘ || source);
DBMS_OUTPUT.PUT_LINE(‘—-Error Number: ‘||errno);
DBMS_OUTPUT.PUT_LINE(‘—-Message Text: ‘||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE(‘–message: ‘ || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); — gets the LCR
print_lcr(lcr);
END LOOP;
END print_transaction;
/

STEP 3: Use the below mentioned code to check what is present inside the local transaction ID:

DECLARE
lcr SYS.AnyData;
BEGIN
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE
(1,'<LOCAL_TRANSACTION_ID>’);
print_lcr(lcr);
END;
/

If there are more than one table involved in out of synch problem. Then execute below mentioned code to get the list of tables which are involved in OUT OF SYNCH issue. Run the below code as STRMADMIN user:
STEP 4 : Run the below query only one time as STRMADMIN user:


CREATE OR REPLACE PROCEDURE print_objectname(lcr IN SYS.ANYDATA) IS
typenm VARCHAR2(61);
ddllcr SYS.LCR$_DDL_RECORD;
proclcr SYS.LCR$_PROCEDURE_RECORD;
rowlcr SYS.LCR$_ROW_RECORD;
res NUMBER;
newlist SYS.LCR$_ROW_LIST;
oldlist SYS.LCR$_ROW_LIST;
ddl_text CLOB;
BEGIN

typenm := lcr.GETTYPENAME();
IF (typenm = ‘SYS.LCR$_DDL_RECORD’) THEN
res := lcr.GETOBJECT(ddllcr);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || ddllcr.GET_OBJECT_NAME);

ELSIF (typenm = ‘SYS.LCR$_ROW_RECORD’) THEN
res := lcr.GETOBJECT(rowlcr);
DBMS_OUTPUT.PUT_LINE(‘object: ‘ || rowlcr.GET_OBJECT_NAME);
–DBMS_OUTPUT.PUT_LINE(‘command_type: ‘ || rowlcr.GET_COMMAND_TYPE);
END IF;
END print_objectname;
/
Run the query query when ever there is out of synch issue. Spool the output of this query and select the distinct tables inside the spool file , it will give you the list of tables which are out of synch.
set serveroutput on;

DECLARE
lcr SYS.AnyData;
type strarr is table of varchar2(100);
localtrnid strarr ;
msgcount strarr ;
BEGIN
 select local_transaction_id,message_count bulk collect into localtrnid ,msgcount  from
DBA_APPLY_ERROR where apply_name=’APPLY_NAME’;
for i in 1 .. localtrnid .count loop
 for j in 1.. to_number(msgcount(i)) loop
  lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(j,localtrnid(i) );
  print_objectname(lcr);
 end loop;
 
end loop;

–print_lcr(lcr);
END;
/

Thanks and Regards,

Satish.G.S

Posted in Oracle STREAMS | 3 Comments »

STEPS FOR ADDING NEW OBJECTS TO THE EXISTING ORACLE TABLE LEVEL STREAMS CONFIGURATION

Posted by ssgottik on 09/05/2011

Here i am adding a table by name NEW_STRM_TBL to already up and running TABLE level streams.

SOURCE :

SERVER IP     : 192.168.0.1
DATABASE NAME : SOURCEDB
SCHEMA NAME   : SCOTT
TABLE NAME    : NEW_STRM_TBL
TARGET :

SERVER IP     : 192.168.1.1
DATABASE NAME : TARGETDB
SCHEMA NAME   : SCOTT
TABLE_NAME    : NEW_STRM_TBL

STEP 1: STOP STREAMS

Stop APPLY, PROPAGATION and CAPTURE process

STEP 2: DEFINE THE RULE FOR APPLY
CONN STRMADMIN/STRMADMIN@TARGETDB

show user
–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_type => ‘APPLY’,
streams_name => ‘STREAM_APPLY_A1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP 3: DEFINE THE RULE FOR PROPAGATION

CONN STRMADMIN/STRMADMIN@SOURCEDB

–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_name => ‘STREAM_PROPAGATE_P1’,
source_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q@TARGETDB’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP 4: DEFINE THE RULE FOR CAPTURE

CONN strmadmin/STRMADMIN@SOURCEDB

show user

–NEW_STRM_TBL

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ‘SCOTT.NEW_STRM_TBL’,
streams_type => ‘CAPTURE’,
streams_name => ‘STREAM_CAPTURE_C1’,
queue_name => ‘STRMADMIN.STREAMS_QUEUE_Q’,
include_dml => true,
include_ddl => true,
source_database => ‘SOURCEDB’);
END;
/
STEP5: OBJECT INSTANTATION

Take the export of table from the source database:

exp SCOTT/TIGER file=exp_tbl.dmp log=exp_tbl.log object_consistancy=y tables=SCOTT.new_strm_tbl

Transfer the dump file to target database:
Import the dump into the target database:

imp SCOTT/TIGER fromuser=SCOTT touser=SCOTT file='<PATH>/exp_tbl.dmp’ log='<PATH>/exp_tbl.log’ STREAMS_INSTANTIATION=Y IGNORE=Y COMMIT=Y

STEP 6: START STREAMS

Start APPLY, PROPAGATION and APPLY

Thanks and Regards,

Satish.G.S

Posted in Oracle STREAMS | Leave a Comment »