RE: Snapshot too old from READ-ONLY table (data pump export)
Date: Mon, 18 Jul 2011 15:45:35 -0400
Message-ID: <01ea01cc4583$43f3df70$cbdb9e50$_at_rsiz.com>
There have been a variety of threads about this topic. IF you can stop people from updating the segments in the tablespace you are going to make read only BEFORE you actually make it read only long enough to force full table scans visiting all the blocks so that delayed cleanout is performed. Hint: count(*) on a table with an index on a not null column is not good enough, because it will bypass the table and just use the smallest index guaranteed to give the right answer. Then if you make the tablespace read only, you should be able to read blocks that do not require undo to process into block cache images.
Oracle is brilliantly lazy about avoiding work until it is logically required. That is usually a good thing for performance.
My attempts to get Oracle to add a “CLEAN” option on setting tablespaces read only in the early 1990’s did not get enough votes at Oracle VLDB. Why you can get snapshots too old reading things from read only tablespaces has been explained adequately in this thread. But it is still ironic and counter intuitive to the language. Also with a “CLEAN” option Oracle could certainly do it more efficiently than figuring out all the required queries and simply undertake the task at hand to make all the blocks in a tablespace clean.
They could also presumably get it exactly right for the general case, while from the user viewpoint you have to do the dance each time to make sure you are doing it correctly. (Think about blocks laying around in buffer cache that are okay for a new query, so you still don’t get the cleanout, even with a full table scan correctly driven.) They might even consider a new state CLEANING in place of READONLY that prevented new changes to the objects in a tablespace via sql whilst the cleanout task took place (presumably at a controllable parallelism). If they did it as a special task they wouldn’t even need to produce the blocks into shared buffer cache! PGA in the special task would work just fine.
Please note this is not about how Oracle works, but rather about how I wished it worked.
Regards,
Mark
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Brad Peek
Sent: Monday, July 18, 2011 2:14 PM
To: David Roberts
Cc: Bobak, Mark; Oracle-L List
Subject: Re: Snapshot too old from READ-ONLY table (data pump export)
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 GBTotal 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-lReceived on Mon Jul 18 2011 - 14:45:35 CDT