Re: Snapshot too old from READ-ONLY table (data pump export)

From: Brad Peek <brad_peek_at_yahoo.com>
Date: Mon, 18 Jul 2011 13:14:16 -0500
Message-Id: <D262DFA9-1545-470B-8868-4FA1AF0FAB63_at_yahoo.com>

Thanks to you and others I have had some informative posts that have helped me understand why this may be happening. One suggested work around was to try and speed up the export (e.g. try parallel) which might work as long as the export completes before the min SCN has been overwritten in the UNDO segments.

I do intend to try that. Does anyone have a different suggestion as a work around?

Having another work around would be helpful because I can envision cases where the size of the segment being exported and the high level of update activity in the system could make that approach very much a hit-or-miss proposition.

The DB version is v11.2.

Sent from my iPad

On Jul 18, 2011, at 8:29, David Roberts <big.dave.roberts_at_googlemail.com> wrote:

> The problem with delayed block cleanout only occurs after the tablespace was placed in read only mode and only when the undo has been aged out.
>  
> The fact that the tablespace is read only is the thing that stops Oracle from performing the clearout.
>  
> The amount of elapsed time since the tablespace was made read only isn't a factor.
>  
> Dave
> 
> On Fri, Jul 15, 2011 at 7:14 PM, Brad Peek <brad_peek_at_yahoo.com> wrote:
> I should have mentioned that the tablespace was placed in read-only mode over two weeks ago.  The explanation in the Tom Kyte article that you referenced leads me to think that would be enough time to avoid still being affected by the delayed block writes.
> 
> Sent from my iPad 
> 
> 
> On Jul 15, 2011, at 12:59, "Bobak, Mark" <Mark.Bobak_at_proquest.com> wrote:
> 

>> Hi Brad,
>>
>>
>>
>> Here’s Tom Kyte’s discussion on ORA-1555 on objects in a read only tablespace:
>>
>> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:895410916429
>>
>>
>>
>> Hope it helps,
>>
>>
>>
>> -Mark
>>
>>
>>
>>
>>
>> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Brad Peek
>> Sent: Friday, July 15, 2011 1:25 PM
>> To: Oracle-L List
>> Subject: Snapshot too old from READ-ONLY table (data pump export)
>>
>>
>>
>> A table level export (via data pump) is failing with an ORA-01555. The curious thing (to me, anyway) about this is that the tablespace containing the table is set to READ-ONLY so I don't get what would be needed from the UNDO segments anyway.
>>
>>
>>
>> Before I open up an SR (SR = serious run-around), can someone help me out with why export might need to get a "before" image of a block that hasn't changed? The export is taking much longer than I would have expected (> 10 hours before failing) so there may be multiple issues at play here.
>>
>>
>>
>> Could it be the datapump master table that is getting the ORA-01555 (seems unlikely)? If so, how would I confirm that, and how would I get around that issue even if that is the case?
>>
>>
>>
>> This error is repeatable. I first got this error trying to export the whole schema, so I decided to try a single table export (details below).
>>
>>
>>
>> From the export log:
>>
>> --------------------
>>
>> Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=expdp_mic_air_sp_send.parfile
>> Estimate in progress using STATISTICS method...
>> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
>> . estimated "MIC_AIR"."SP_SEND" 119.5 GB
>> Total estimation using STATISTICS method: 119.5 GB
>> Processing object type TABLE_EXPORT/TABLE/TABLE
>> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
>> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
>> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
>> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
>> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>> ORA-31693: Table data object "MIC_AIR"."SP_SEND" 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 46 with name "_SYSSMU46_1630464369$" too small
>> Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
>> ******************************************************************************
>> Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
>> /u01/app/datapump_dir/expdp_mic_air_sp_send_01.dmp
>> /u01/app/datapump_dir/expdp_mic_air_sp_send_02.dmp
>> Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 06:09:00
>>
>> The parameter file is:
>>
>> ---------------------
>>
>> $ cat expdp_mic_air_sp_send.parfile
>> tables=mic_air.sp_send
>> directory=data_pump_dir
>> dumpfile=expdp_mic_air_sp_send_%U.dmp
>>
>> filesize=10737418240
>>
>> logfile=expdp_mic_air_sp_send.log
>> estimate=statistics
>> compression=all
>>
>> From the instance ALERT LOG:
>>
>> ---------------------------------
>>
>> Fri Jul 15 06:08:47 2011
>> ORA-01555 caused by SQL statement below (SQL ID: 1sqssb6vhvr6r, SCN: 0x000c.05716ebf):
>> SELECT * FROM RELATIONAL("MIC_AIR"."SP_SEND")
>>
>> Note that in the above output from the instance alert log, the statement is referencing a function named RELATIONAL. That could be a clue, but I don't think I have seen that before (must be a data pump thing).
>>
>>
>>
>> The table doesn't contain any LOB columns:
>>
>> --------------------------------------------
>>
>> BPEEK_at_shareprd1> desc mic_air.sp_send
>> Name Null? Type
>> ----------------------------------------- -------- ----------------------------
>> OID_SP_SEND NOT NULL NUMBER(10)
>> MAILING_ID VARCHAR2(20)
>> RECIPIENT_TYPE VARCHAR2(20)
>> CONTACT_SOURCE NUMBER(10)
>> REFERENCE_NUM VARCHAR2(11)
>> EMAIL_ADDRESS VARCHAR2(80)
>> EVENT_TYPE VARCHAR2(20)
>> WHEN_SENT DATE
>> CAMPAIGNID NUMBER(15)
>> OFFERID NUMBER(15)
>> MAILING_NAME VARCHAR2(120)
>> SUBJECT_LINE VARCHAR2(120)
>> OID_CUSTOMER NUMBER(10)
>> ADDUID NOT NULL VARCHAR2(8)
>> WHEN_ADDED NOT NULL DATE
>> LASTUID NOT NULL VARCHAR2(8)
>> WHENLASTUPDATE NOT NULL DATE
>>
>>
>>
>>
>>

>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 18 2011 - 13:14:16 CDT