First 17 updated records disappeared from REDOLOG files …

Hello everybody,

I don't believe that change vectors are disappearing from REDO files.
Presumably they are not.
But the fact is that due some optimization or other reason Log Minder
is not capable to retrieve data from an archive log file for first 17
records updated by an update operation.
. The FULL Test Case text is provided below .
VJVDROPME:ASR02> update vjv_dropme_upd2
VJVDROPME:ASR02> set object_name = 'HERE YOU GO.'
VJVDROPME:ASR02> where rownum<=20
VJVDROPME:ASR02> ;
-------------=> 20 rows updated. <=-------------
VJVDROPME:ASR02> commit;
... Starting Log Minder session commands …
VJVDROPME:ASR02> SELECT  a.sql_redo
VJVDROPME:ASR02> FROM V$LOGMNR_CONTENTS a
VJVDROPME:ASR02> where a.seg_name='VJV_DROPME_UPD2';
SQL_REDO
----------------------------------------------------------------------------------------------------
update "VJVDROPME"."VJV_DROPME_UPD2" set "OBJECT_NAME" = 'HERE YOU
GO.' where "OBJECT_NAME" = 'I_CDE
update "VJVDROPME"."VJV_DROPME_UPD2" set "OBJECT_NAME" = 'HERE YOU
GO.' where "OBJECT_NAME" = 'IND$'
update "VJVDROPME"."VJV_DROPME_UPD2" set "OBJECT_NAME" = 'HERE YOU
GO.' where "OBJECT_NAME" = 'SEG$'
-------------=> 3 rows selected. <=-------------
.
I have tried to increase updated rows count to 10k, 20k, in any case
Log Minder not capable to retrieve information about the fist few
records.
.
Just wonder if somebody has any idea?
So far it is look like we can't relay on Log Minder using it for
example auditing purposes.

PS May be I have made an error creating the test case. Please be so
kind, point me to the error.

Jurijs

================================================
FULL TEST CASE TEXT follows
================================================
VJVDROPME:ASR02> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

VJVDROPME:ASR02> drop table vjv_dropme_upd2;

Table dropped.

VJVDROPME:ASR02> create table vjv_dropme_upd2 tablespace users as
select * from dba_objects;

Table created.

VJVDROPME:ASR02>
VJVDROPME:ASR02> alter system archive log current;

System altered.

VJVDROPME:ASR02> select dbms_flashback.get_system_change_number SCN from dual;

       SCN
----------
    909364

1 row selected.

VJVDROPME:ASR02> update vjv_dropme_upd2
VJVDROPME:ASR02> set object_name = 'HERE YOU GO.'
VJVDROPME:ASR02> where rownum<=20
VJVDROPME:ASR02> ;

20 rows updated.

VJVDROPME:ASR02> commit;

Commit complete.

VJVDROPME:ASR02> select dbms_flashback.get_system_change_number SCN from dual;

       SCN
----------
    909367

1 row selected.

VJVDROPME:ASR02> alter system archive log current;

System altered.

VJVDROPME:ASR02> SELECT NAME FROM V$ARCHIVED_LOG a
VJVDROPME:ASR02>    WHERE 1=1
VJVDROPME:ASR02> and a.first_change# = (select a.first_change# from
v$log_history a where 1=1
VJVDROPME:ASR02> and 909364 between a.first_change# and a.next_change#
VJVDROPME:ASR02> and 909367 between a.first_change# and a.next_change#
VJVDROPME:ASR02> );

NAME
----------------------------------------------------------------------------------------------------
/u20/ora_arch_dest/ASR02/log_ASR02_1_27_580224695.arc

1 row selected.

VJVDROPME:ASR02> begin
VJVDROPME:ASR02> sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>
VJVDROPME:ASR02> '/u20/ora_arch_dest/ASR02/log_ASR02_1_27_580224695.arc'
VJVDROPME:ASR02> , OPTIONS => sys.DBMS_LOGMNR.NEW);
VJVDROPME:ASR02> end;
VJVDROPME:ASR02> /

PL/SQL procedure successfully completed.

VJVDROPME:ASR02> begin
VJVDROPME:ASR02>
sys.dbms_logmnr.start_logmnr(options=>sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
VJVDROPME:ASR02> end;
VJVDROPME:ASR02> /

PL/SQL procedure successfully completed.

VJVDROPME:ASR02> SELECT
VJVDROPME:ASR02> a.sql_redo
VJVDROPME:ASR02> FROM V$LOGMNR_CONTENTS a
VJVDROPME:ASR02> where 1=1
VJVDROPME:ASR02> and a.seg_name='VJV_DROPME_UPD2'
VJVDROPME:ASR02> ;

SQL_REDO
----------------------------------------------------------------------------------------------------
update "VJVDROPME"."VJV_DROPME_UPD2" set "OBJECT_NAME" = 'HERE YOU
GO.' where "OBJECT_NAME" = 'I_CDE
update "VJVDROPME"."VJV_DROPME_UPD2" set "OBJECT_NAME" = 'HERE YOU
GO.' where "OBJECT_NAME" = 'IND$'
update "VJVDROPME"."VJV_DROPME_UPD2" set "OBJECT_NAME" = 'HERE YOU
GO.' where "OBJECT_NAME" = 'SEG$'

3 rows selected.

VJVDROPME:ASR02> begin
VJVDROPME:ASR02> sys.dbms_logmnr.end_logmnr;
VJVDROPME:ASR02> end;
VJVDROPME:ASR02> /

PL/SQL procedure successfully completed.

VJVDROPME:ASR02>
--
http://www.freelists.org/webpage/oracle-l

Other related posts: