Hi All -
I've read many forum entries on this, but so far I've been unable to solve my problem. Running on Windows 2003 Server, Oracle 11g 18.104.22.168.0
I'm trying to do an expdp of my entire database. The only process that is running is the expdp. I have shut down all user applications that make connections to the database. There's nothing writing to this table, and nothing reading from it aside from expdp.
. . exported "HDB_MAIN"."WAVEFORM0" 64.64 GB 1811611 rows
ORA-31693: Table data object "USERNAME"."WAVEFORM2" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1_1193973708$" too small
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 9000
undo_tablespace string UNDOTBS1
SQL> select sum(bytes) from user_segments where segment_name = 'WAVEFORM2';
7.7513E+10 # this is about 72GB, the largest table in the database
I increased undo_retention from 900 to 9000, but that didn't make any difference. Since undo_management is AUTO, I don't have control over actually allocating more space to the undo segments, right?
(I've been a DBA before, but less experience with Oracle... )
A few "For what it's worth" items:
the final output file was 328gb without WAVEFORM2. Still 193gb free on target drive after the 328gb output file has been saved there.
oradata takes 946gb
disk free space 42.6gb on oradata drive
Any ideas on what I could look at next?
Pl post the complete expdp command. Does this table have any LOB columns ? If so, this may be a bug
ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. (Doc ID 452341.1)
Export Fails With ORA-02354 ORA-01555 ORA-22924 and How To Confirm LOB Segment Corruption Using Export Utility? (Doc ID 833635.1)
H:\ORACLE_EXPORT>expdp SYSTEM/password FULL=y DUMPFILE=dbname.dmp DIRECTORY=HEXPORT LOGFILE=dbname.log
Yes, there are lots of binary waveform file chunks in this table, thus "yes" in answer to LOB.
I'll see what I can find in the way of answers on corruption.
You're right. I had increased unto_retention from 900 to 9000. This time I increased it to 90000. That did it. The backup completed successfully. I don't have metalink access, so I don't know how to check for LOB corruption, but I'm assuming that everything is fine since this time around I have a 333gb file, and the .log file contains no error.
One thing that was strange was that the table that failed each time (WAVEFORM2) was slightly smaller than the one right before it which succeeded (not larger as I had been led to believe from the SQL I used to get me the size). Here's the expdp log excerpt:
. . exported "USERNAME"."WAVEFORM0" 64.64 GB 1811611 rows
. . exported "USERNAME"."WAVEFORM2" 63.99 GB 1768224 rows
. . exported "USERNAME"."WAVEFORM3" 50.94 GB 4606522 rows
. . exported "USERNAME"."WAVEFORM1" 53.72 GB 1425423 rows
. . exported "USERNAME"."WAVEFORM4" 44.16 GB 3400018 rows