logminer.mine_value giving error ORA-01323: Invalid state

From: Rao <faheemrao_at_gmail.com>
Date: Mon, 9 Jun 2008 08:07:30 -0700 (PDT)
Message-ID: <5beedc48-a0cd-4a87-8b85-b797b7ea5e43@34g2000hsf.googlegroups.com>


logminer.mine_value giving error ORA-01323: Invalid state

I am trying to capture changes using logminer

and I am getting ORA-01323: Invalid state.

Basically I am trying to get the actual redo and undo values( not the binary representation of actual values)

Here are the steps which I am doing.

  1. begin sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\APP \ADMINISTRATOR\PRODUCT\11.1.0\DB_2\RDBMS\ARC00027_0656739866.001', OPTIONS => sys.DBMS_LOGMNR.ADDFILE); end;
  2. BEGIN sys.dbms_Logmnr.Start_Logmnr(Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY + sys.DBMS_LOGMNR.CONTINUOUS_MINE); END;
  3. In this step I am atarting my stored procedure called sartcapture( actuall code is atached)

begin
ds.StartCapture(1594395);
end;

In this pocedure I am selecting from the logminer_contents and in trun calls dbms_logminer.mine_value and get the ORA-01323.

After reading the post for ORA-01323 , it says that there is no dictionary associated with logminer session. But I am kind of confuse that I am specifying DICT_FROM_ONLINE_CATALOG in step 2 while starting the logminer session, and why still I am getting this error.

any help would be aprreciated.

CODE FOR the Stored procdure StartCapture



create or replace
PROCEDURE StartCapture(p_Starting_SCN number) is

cursor cur_Browse_Logminer
is
select
seg_owner,
seg_name,
--SQL_COLUMN_NAME,

redo_value,
undo_value,
username,
operation,
scn,
timestamp,
commit_timestamp
from sys.v_$Logmnr_Contents where scn>=p_Starting_SCN ;--and seg_name='TEST4';

--cursor cur_Browse_Logminer is select sysdate from dual;
Before_Image_test4_rec test4%rowtype;
After_Image_test4_rec test4%rowtype;

v_Column varchar2(1000);

begin

for logmnr_rec in cur_Browse_Logminer --(p_Starting_SCN) loop

--insert into capture_log

values(sysdate,logmnr_rec.commit_timestamp);

--dbms_output.put_line('Entring the loop');
if upper(logmnr_rec.seg_NAME)='TEST4' then --and logmnr_rec.SQL_COLUMN_NAME='ID' then

  • dbms_output.put_line(' Entring the if condition');
  • v_Column:=logmnr_rec.SEG_OWNER||'.'||logmnr_rec.SEG_NAME||'.'|| logmnr_rec.SQL_COLUMN_NAME; if sys.DBMS_LOGMNR.COLUMN_PRESENT(logmnr_rec.redo_value,'DS.TEST4.ID')=1 then Before_Image_test4_rec.id:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.redo_value,'DS.TEST4.ID');
    --null;
    end if;
    --
    Before_Image_test4_rec.value:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.undo_value,'DS.TEST4.VALUE');

--

After_Image_test4_rec.id:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.redo_value,v_Column);
--

After_Image_test4_rec.value:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.redo_value,'DS.TEST4.VALUE');

insert into captured_changes
(
seg_owner,
seg_name,
username,
operation,
Commit_SCN,
Operation_Time,
Commit_Time,
Captured_Time,
Id_Before ,
Name_Before ,
ID_After ,
Name_After
)
values
(

logmnr_rec.seg_owner,
logmnr_rec.seg_name,
logmnr_rec.username,
logmnr_rec.operation,
logmnr_rec.scn,
logmnr_rec.timestamp,
logmnr_rec.commit_timestamp,

sysdate,
logmnr_rec.undo_value,
Before_Image_test4_rec.value,
logmnr_rec.redo_value,

After_Image_test4_rec.value

);

commit;

end if ;

end loop;

end;


Received on Mon Jun 09 2008 - 10:07:30 CDT

Original text of this message