RE: expdp and ORA-01555
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: richa03@xxxxxxxxx, 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 27 Aug 2008 01:03:56 +0800
Hi Richa,
Regarding MON_MOD$ accounting - I haven't tested this, but I wouldn't be
surprised if some LOB data updates aren't accounted in there as LOB handling
stuff uses different codepath.
The LOB segment read consistency mechanism doesn't use undo segments (only
LOB indexes and inline LOB items which are being stored inline with "parent"
row).
Instead the LOB segment just keeps old versions of changed LOB chunks lying
around (that's where the PCTVERSION and RETENTION come into play).
The ORA-1555 you see (without an undo segment name/number) indicates that
this error was raised from LOB codepath. Also the stack indicates the same
(the raising function name starts with kok instead of ktr).
As you're using PCTVERSION (they should be mutually exclusive with RETENTION
if it hasn't changed in 10.2) you should bump it up with this command:
alter table t1 modify lob(a) (pctversion xx);
You can set the pctversion temporarily to 100 that you would get your export
done (that way no old LOB chunk is overwritten and your LOB segments will
constantly grow due DML).
Note that the RETENTION doesn't always work that well for LOBs under heavy
DML activity. It's also said in Oracle docs:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/undo002.htm
Note:
Automatic tuning of undo retention is not supported for LOBs. This is
because undo information for LOBs is stored in the segment itself and not in
the undo tablespace. For LOBs, the database attempts to honor the minimum
undo retention period specified by UNDO_RETENTION. However, if space becomes
low, unexpired LOB undo information may be overwritten.
--
Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/>
http://n.otepad.com <http://n.otepad.com/> - n.ote this!
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of richa03@xxxxxxxxx
Sent: Tuesday, August 26, 2008 23:50
To: oracle-l
Subject: expdp and ORA-01555
64-bit 10.2.0.3 on Solaris 10 x86
When trying to export a single table, we get:
ORA-01555: snapshot too old: rollback segment number with name "" too small
The table contains out of line LOBs using PCTVERSION of 10, however, these
LOBs are never updated, so the PCTVERSION should not be the issue.
[This has been proven through the MON_MODS$ table using the OBJECT_ID column
of DBA_OBJECTS - see below]
We have scanned this table per Oracle Note 452341.1 and there is no
corruption.
We have created a larger undo segment - from 1GB to 5GB.
We set event 1555 to dump errorstack at level 3 and have the trace file.
We have changed undo_retention from 900 to 21140 per v$undostat.
The columns SSOLDERRCNT and NOSPACEERRCNT are 0.
Furthermore, the columns UNXPSTEALCNT & EXPSTEALCNT are all 0 and the column
MAXQUERYLEN was not over the setting of the undo_retention parameter (21140
- the high was 8069) during the export.
The export fails at about 141009977344 bytes (5 runs, sometimes earlier with
less bytes).
The table is about 100M records and about 275GB. It's structure is:
Name Null? Type
----------------------------------------- -------- --------------
TRHS_PKEY NOT NULL NUMBER
TRHS_SEQ NOT NULL NUMBER
TRAN_KEY NOT NULL NUMBER
BSTP_KEY NUMBER
TRSP_KEY NUMBER
TRHS_DESC NOT NULL VARCHAR2(256)
TRHS_DOC_URL VARCHAR2(512)
TRHS_DOC BLOB
TRHS_DOC_SIZE NOT NULL NUMBER
TRHS_ZIPPED NOT NULL NUMBER
TRHS_CONTENT_TYPE VARCHAR2(128)
TRHS_CREATED_DATE NOT NULL DATE
The table is insert only - about 700 records per min.
select * from SYS.MON_MODS$ where OBJ# = 32716;
OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS
DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ----------
-------------
32716 36837918 0 1320167 26-AUG-08 0
0
The export was tested in a test environment (without load) and succeeded -
170341232640 bytes.
Stack trace:
Summary Stack (to Full stack) (to Function List)
koklierr NAME: koklisdisk - KOK Lob Internal Set DISK part of locator.
kluuldcs
kluprd
kluuscn
kluuld
kpodpuls
opiodr OPIODR: ORACLE code request driver - route the current request
We have an open SR with Oracle, but, well, you know...
We can try to simulate load on this object, however this will be difficult
if not impossible in test.
Under insert, I would expect the internal LOB index to be updated - does
this count against undo?
If so, where is this undo stored - normal undo segments or internal to the
LOB object (like the pctversion space)?
Does expdp lock this undo to ensure consistency?
TIA
Other related posts:
