First 17 updated records disappeared from REDOLOG files …

From: Jurijs Velikanovs <j.velikanovs_at_gmail.com>
Date: Tue, 21 Feb 2006 16:44:49 +0000
Message-ID: <d6f0def50602210844ud86a91od5a30f578b3df7f9@mail.gmail.com>

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

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

Received on Tue Feb 21 2006 - 10:44:49 CST