Getting some invalid sql_redo commands when inserting data after alter statement ?

[AvinashUrs]

Hi,

Getting some invalid sql_redo data when inserting data after alter statement below is eg: SQL_REDO":"insert into \"DEMO1\".\"TEST\"(\"COL 1\",\"COL 2\",\"COL 3\") values (HEXTORAW('c109'),HEXTORAW('7465737435'),HEXTORAW('7465737438')) the query which i got is different from was inserted .

[erdemcer]

Hi, What is your actual sql statement ? And table structure ?

[AvinashUrs]

This issue is happening randomly, sometimes same data works as expected sometimes it wont, i cant reproduce every time, this happens for even simple insert statement the table which has only firstname column with varchar datatype, after the above issue connector stops capturing but restarting connector works fine again. adding to this sometimes for drop table query is also capturing some invalid data in sqlredo eg: drop table test AS "B$;=dhjdkddkdk" something like above. Note: This scenario happening only after alter operation.

Thank you for the response.

[erdemcer]

"Drop table test as B$;.... " shows dropped table was not dropped system permanently , related object and its dependent objects will remain in recycle bin until purged. Could you please give some detail about alter operations and after DMLs you have mentioned above ? If it is possibe with commands . Thanks

[AvinashUrs]

The below image can describe the issue,

1. created table.
2. altered the table
3. insert operation.

insert command: insert into restt38(names,age,age1)values('neww33','2933','7377');

[AvinashUrs]

Hi, What's the problem? Can you help me out?

[erdemcer]

Hi, It is related with dictionary update of changed object. From Oracle doumentation :

Remember, however, that the online catalog can only reconstruct SQL statements that are executed on the latest version of a table. As soon as a table is altered, the online catalog no longer reflects the previous version of the table. This means that LogMiner will not be able to reconstruct any SQL statements that were executed on the previous version of the table. Instead, LogMiner generates nonexecutable SQL (including hexadecimal-to-raw formatting of binary values) in the SQL_REDO column of the V$LOGMNR_CONTENTS view similar to the following example:

insert into HR.EMPLOYEES(col#1, col#2) values (hextoraw('4a6f686e20446f65'), hextoraw('c306'));"

It is expected issue when using online catalog. I will check it in detail . Thanks.

[yymoxiaochi]

Hi, It is related with dictionary update of changed object. From Oracle doumentation :

Remember, however, that the online catalog can only reconstruct SQL statements that are executed on the latest version of a table. As soon as a table is altered, the online catalog no longer reflects the previous version of the table. This means that LogMiner will not be able to reconstruct any SQL statements that were executed on the previous version of the table. Instead, LogMiner generates nonexecutable SQL (including hexadecimal-to-raw formatting of binary values) in the SQL_REDO column of the V$LOGMNR_CONTENTS view similar to the following example:

insert into HR.EMPLOYEES(col#1, col#2) values (hextoraw('4a6f686e20446f65'), hextoraw('c306'));"

It is expected issue when using online catalog. I will check it in detail . Thanks.

Yeah, I've been working on 'store in redolog'

EXECUTE DBMS_LOGMNR_D.BUILD( -
   OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Does this statement add more cost, and does it only need to be executed once？

[AvinashUrs]

Hi, It is related with dictionary update of changed object. From Oracle doumentation :

Remember, however, that the online catalog can only reconstruct SQL statements that are executed on the latest version of a table. As soon as a table is altered, the online catalog no longer reflects the previous version of the table. This means that LogMiner will not be able to reconstruct any SQL statements that were executed on the previous version of the table. Instead, LogMiner generates nonexecutable SQL (including hexadecimal-to-raw formatting of binary values) in the SQL_REDO column of the V$LOGMNR_CONTENTS view similar to the following example:

insert into HR.EMPLOYEES(col#1, col#2) values (hextoraw('4a6f686e20446f65'), hextoraw('c306'));"

It is expected issue when using online catalog. I will check it in detail . Thanks.

Thank you for your answer. looking forward for more.