Re: First 17 updated records disappeared from REDOLOG files …
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <j.velikanovs@xxxxxxxxx>, "ORACLE-L" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 22 Feb 2006 03:29:17 -0700
Hi,
That's in-memory undo (10g new feature), which is fooling logminer. Logminer
probably can't handle all new structures in redologs yet (hint-hint: logical
standby and streams ;)
Try to set _in_memory_undo=false in your session and try again, you should
get all records now.
Stop reading here if you're not interested how Oracle10g achieves extreme
scalability for OLTP transactions ;)
IMU anticipates small transactions, relieving us from excessive CBC latch
gets and undo segment buffer pinning. We can generate the undo into a
preallocated shared pool area (number of pools is controlled by _imu_pools
parameter).
You can see the IMU stuff in shared pool:
SQL> select * from v$sgastat where name like 'KTI%';
POOL NAME BYTES
------------ ---------------------------------------- ----------
shared pool KTI latch structure 1000
shared pool KTI-UNDO 686280
shared pool KTI pool states 12
shared pool KTI latches 160
When starting an IMU transaction, we preallocate an IMU pool for it and
further undo generation can be done into it without getting that many
latches like with undo segment block buffer get and pin operation. Also,
there's less contention as each IMU buffer is protected by separate latch
(In memory undo latch).
In memory undo mechanism goes hand-in-hand with private redo strand
mechanisms - redo generated for IMU transactions is generated into private
redo strands which work pretty much the same way in principle.
Now when our transaction's undo or redo amount reaches a level where it
doesn't fit into our ~64kB preallocated memory area anymore, Oracle switches
back to the old conventional undo/redo mechanism. It's called IMU flush and
can be seen from v$sysstat.
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects;
Table created.
SQL> select n.name, s.value from v$statname n, v$sesstat s
where n.statistic# = s.statistic#
and s.sid = (select sid from v$mystat where rownum = 1)
and n.name in ('IMU Flushes','IMU commits');
2 3 4
NAME VALUE
---------------------------------------------------------------- ----------
IMU commits 0
IMU Flushes 0
SQL> update t set owner = 'X' where rownum = 1;
1 row updated.
SQL> select n.name, s.value from v$statname n, v$sesstat s
where n.statistic# = s.statistic#
and s.sid = (select sid from v$mystat where rownum = 1)
and n.name in ('IMU Flushes','IMU commits');
2 3 4
NAME VALUE
---------------------------------------------------------------- ----------
IMU commits 0
IMU Flushes 0
SQL> update t set owner = 'X' where rownum <= 200;
200 rows updated.
SQL> select n.name, s.value from v$statname n, v$sesstat s
where n.statistic# = s.statistic#
and s.sid = (select sid from v$mystat where rownum = 1)
and n.name in ('IMU Flushes','IMU commits');
2 3 4
NAME VALUE
---------------------------------------------------------------- ----------
IMU commits 0
IMU Flushes 1
You see, here our last update caused an IMU Flush, thus switching back to
conventional undo generation mode.
Logminer, probably because a bug, isn't able to read any change records
created in IMU mode. If you change the amount of redo/undo generated during
your update, you'll see different number of rows "ignored" by logminer.
Tanel.
----- Original Message -----
From: "Jurijs Velikanovs" <j.velikanovs@xxxxxxxxx>
To: "ORACLE-L" <Oracle-L@xxxxxxxxxxxxx>
Sent: Tuesday, February 21, 2006 9:44 AM
Subject: 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
