LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)

LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2016***

PURPOSE

This document provides troubleshooting steps to resolve ORA-01555 for LOB objects. It also discusses known issue and bugs related ORA-01555 for LOBS.

本文提供了故障排除步骤来解决LOB对象的ORA-01555。它还讨论了LOBS的已知问题和与ORA-01555相关的错误

TROUBLESHOOTING STEPS

Background

There are four types of LOB:   LOB有四种类型

CLOB, BLOB, NCLOB  ->  stored internally to Oracle  CLOB，BLOB，NCLOB->内部存储到Oracle
BFILE   -> stored externally  BFILE->外部存储

A table containing LOBs (CLOB, NCLOB and BLOB) creates 2 additional disk segments per LOB column - a LOBINDEX and a LOBSEGMENT. These can be viewed, along with the LOB attributes, using the dictionary views:

包含 LOBs (CLOB, NCLOB and BLOB) 的表为每个LOB列创建2个额外的磁盘段 LOBINDEX 和 LOBSEGMENT 。可以使用字典视图查看这些内容以及LOB属性

DBA_LOBS, ALL_LOBS or USER_LOBS

LOB columns can be allowed to store data within the row or not as detailed below. Whether in-line storage is allowed or not can ONLY be specified at creation time.

LOB 列可以允许在行内存储数据，也可以不按以下详细说明存储数据。在创建时只能指定是否允许在线存储

1.) INLINE LOBs

"STORE AS ( enable storage in row )"

Allows LOB data to be stored in the TABLE segment provided it is less than about 4000 bytes. The actual maximum in-line LOB is 3964 bytes. In-line LOBS are subject to normal chaining and row migration rules within Oracle. Ie: If you store a 3900 byte LOB in a row with a 2K block size then the row piece will be chained across two or more blocks.

允许将LOB数据存储在TABLE段中，前提是该段小于4000字节。 实际的最大串联LOB为3964字节。 内联LOBS受Oracle内常规链接和行迁移规则的约束。 即：如果您将3900字节的LOB存储在2K块大小的行中，则该行段将链接在两个或更多块上。

Undo Generation

For In-Line LOBs Both REDO and UNDO are written as they are part of the normal row data.

REDO 和 UNDO 都被写入，因为它们是普通行数据的一部分

2.) OUT OF LINE LOBs

"STORE AS ( disable storage in row )"

If the LOB data is greater than 3964 bytes, then the LOB data is stored in the LOB SEGMENT (ie: out of line). An out of line LOB behaves as described under 'disable storage in row' except that if its size shrinks to 3964 or less the LOB can again be stored inline.

如果 LOB 数据大于3964字节，则LOB数据将存储在 LOB SEGMENT 中 (ie: out of line)。out of line LOB的行为与'disable storage in row'中所述的相同，不同之处在于，如果其大小缩小到3964或更小，则LOB可以再次以内联方式存储

A 20 byte LOB locator is stored in the ROW which gives a unique identifier for a LOB in the LOB segment for this column. The Lob Locator actually gives a key into the LOB INDEX which contains a list of all blocks (or pages) that make up the LOB.

一个20字节的LOB定位符存储在ROW中，它为该列的LOB段中的LOB提供了唯一的标识符。实际上，Lob Locator为LOB INDEX提供了一个密钥，该密钥包含构成LOB的所有blocks (or pages)的列表

When a LOB is stored out-of-line in an 'enable storage in row' LOB column between 36 and 84 bytes of control data remain in-line in the row piece.

当将LOB out-of-line 存储在'enable storage in row'中时，控制数据的36到84字节之间的LOB列将保持在行内

Undo Generation

UNDO is only written for the column locator and LOB INDEX changes. UNDO仅为列定位器和LOB INDEX更改而编写

No UNDO is generated for pages in the LOB SEGMENT.Consistent Read is achieved by using page versions i.e. When you update a page of a LOB the OLD page remains and a new page is created. This can appear to waste space but old pages can be reclaimed and reused.

LOB SEGMENT 中的页面不会生成UNDO。使用页面版本可以实现一致读取，即，当您更新LOB的页面时，将保留OLD页面并创建一个新页面。这看起来很浪费空间，但是旧页面可以被回收和重新使用

Troubleshooting Steps

ORA-1555 means the page we got to did not have the (lob_id + version) that we expected so can indicate a Consistent Read issue or a corruption on the lob segment itself.

ORA-1555 意味着我们到达的页面没有我们期望的 (lob_id + version)，因此可以指示一致性读取问题或lob段本身损坏

V$undostat would not contain useful information because this does not reflect LOB undo information.

V$undostat 将不包含有用的信息，因为这不能反映LOB undo 信息

a.) Checking for corruption.  检查是否损坏

One of the prime reasons of ORA-01555 getting reported with LOBs is corruption. In case of corruption, the OR-1555 error message will be in the following format:

ORA-01555 被LOB报告的主要原因之一是损坏。如果发生损坏，则OR-1555错误消息将采用以下格式

ORA-01555: snapshot too old: rollback segment number  with name "" too small

ORA-22924: snapshot too old

Due to corrupt LOBs, ORA-01555 may get reported. For diagnosing corruption problems, refer the following note:

由于LOB损坏，ORA-01555可能会得到报告。有关诊断损坏问题，请参考以下注释
ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. [ID 452341.1]
Export Fails With ORA-2354 ORA-1555 ORA-22924 and How To Confirm LOB Segment Corruption Using Export Utility? [ID 833635.1]

You could also run the following script from note:  您也可以从note中运行以下脚本
ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors (Doc ID 293515.1)

-- 1. Create a new temporary table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"

-- 1. 创建一个新的临时表，用于存储损坏的LOB的所有rowid。我们称之为"corrupt_lobs"

SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);

-- 2. Make a desc on the table containing the LOB column:

-- 2. 在包含LOB列的表上进行描述

DESC LOBDATA

Name Null? Type

---------- --------- ------------

ID NOT NULL NUMBER

DOCUMENT BLOB

-- Run the following PLSQL block:

-- 运行以下PLSQL块

set concat off

set serveroutput on

declare

error_1578 exception;

error_1555 exception;

error_22922 exception;

error_600 exception;

pragma exception_init(error_1578,-1578);

pragma exception_init(error_1555,-1555);

pragma exception_init(error_22922,-22922);

pragma exception_init(error_600,-600);

n number;

begin

for cursor_lob in (select rowid r, document from LOBDATA) loop

begin

n:=dbms_lob.instr(cursor_lob.document,hextoraw('889911'));

exception

when error_1578 then

insert into corrupt_lobs values (cursor_lob.r, 1578);

commit;

when error_1555 then

insert into corrupt_lobs values (cursor_lob.r, 1555);

commit;

when error_22922 then

insert into corrupt_lobs values (cursor_lob.r, 22922);

commit;

when error_600 then

insert into corrupt_lobs values (cursor_lob.r, 600);

commit;

end;

end loop;

end;

/

-- In the end all rowids of the corrupted LOBs will be inserted into the corrupt_lobs newly created table.

-- 最后，损坏的LOB的所有rowid将插入到新创建的destroy_lobs表中

-- A possible solution would then be to empty the affected LOBs using a statement like:

-- 然后可能的解决方案是使用如下语句清空受影响的LOB

SQL> update LOBDATA set document = empty_blob()

where rowid in (select corrupt_rowid from corrupt_lobs);

( for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB )

-- （对于 BLOB 和 BFILE 列，请使用 EMPTY_BLOB；对于 CLOB 和 NCLOB 列，请使用 EMPTY_CLOB）

-- Or export the table without the corrrupted row, like:

-- 或导出没有损坏的行的表，例如

% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_satc.dmp LOGFILE=expdp_satc.log TABLES=tc.lobdata QUERY=\"WHERE rowid NOT IN \(\'<orrupt_rowid>\'\)\"

As a possible workaround for the LOB corruption, convert LOB from basicfile to securefile as per the following note:

作为 LOB 损坏的一种可能的解决方法，请按照以下说明将 LOB 从 basicfile 转换为 securefile

HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES [ID 728758.1]

Using Securefile as the default type of LOBs can prevent this problem. You can force all LOBs to be created as Securefiles by setting the following:

使用 Securefile 作为默认 LOB 类型可以防止此问题。您可以通过设置以下内容来强制将所有LOB创建为 Securefiles

ALTER SYSTEM SET DB_SECUREFILE=FORCE SCOPE=BOTH;

Enabling DIRECT I/O can help overcome the problem and also prevent it from happening. This can be enabled as follows:

启用 DIRECT I/O 可以帮助解决问题，并防止它发生。可以按以下方式启用它

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=DIRECTIO SCOPE=SPFILE;

-- or you can set FILESYSTEMIO_OPTIONS=SETALL to enable both Asynchronous and Direct I/O

-- 或您可以设置 FILESYSTEMIO_OPTIONS = SETALL 来启用异步和Direct I/O

SHUTDOWN IMMEDIATE

STARTUP

b.) Issues with Retention/Pctversion values

PCTVERSION and RETENTION

PCTVERSION affects the reclamation of old copies of LOB data. This affects the ability to perform consistent read. If a session is attempting to use an OLD version of a LOB and that version gets overwritten (because PCTVERSION is too small) then the user will typically see the errors:

PCTVERSION 影响LOB数据的旧副本的回收。这会影响执行一致读取的能力。如果会话正在尝试使用OLD版本的LOB，并且该版本被覆盖（因为PCTVERSION太小），则用户通常会看到以下错误

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

PCTVERSION can prevent OLD pages being used and force the segment to extend instead. The PCTVERSION parameter is used to control the percentage of the lob segment that must be allocated to retain the version data. Do not expect PCTVERSION to be an exact percentage of space as there is an internal fudge factor applied. PCTVERSION was used earlier and now RETENTION is prefered.

PCTVERSION 可以防止使用OLD页，并强制该段扩展。PCTVERSION 参数用于控制必须分配以保留版本数据的lob段的百分比。不要期望 PCTVERSION 是精确的空间百分比，因为应用了内部 fudge 系数。较早使用了PCTVERSION，现在首选RETENTION

Alternatively RETENTION parameter is used to determine for how long (in seconds) the lob undo should be retained. You cannot set the value of the RETENTION parameter explicitly. The amount of time for retention of LOB versions in determined by the UNDO_RETENTION parameter. Usage of the RETENTION parameter is only supported in Automatic Undo Management mode.

另外，RETENTION 参数用于确定应将 lob undo 保留多长时间（以秒为单位）。 您不能显式设置 RETENTION 参数的值。 保留LOB版本的时间由 UNDO_RETENTION 参数确定。 仅在“自动撤消管理”模式下支持使用RETENTION参数。

If the value of PCTVERSION or RETENTION was set to a very large number and if the queries on the lob segment are relatively shorter, then a large portion of the segment could be wasted in retaining the older lob versions. In case these values are low, ORA-01555 is expected. The LOB storage clause can specify RETENTION or PCTVERSION, but not both.

如果将 PCTVERSION 或 RETENTION 的值设置为非常大的值，并且如果对lob段的查询相对较短，则可能会浪费该段的大部分内容来保留较旧的lob版本。如果这些值较低，则应使用ORA-01555。LOB存储子句可以指定 RETENTION 或 PCTVERSION ，但不能同时指定两者。

In 10g we have no control over the actual setting for RETENTION for lobs other than the current setting for UNDO_RETENTION at the time of the creation or modification of the LOB. Stopping and starting the database will not modify the setting as it seems to be static. It is assumed that when UNDO_RETENTION is changed the lobs connected to that retention are also changed which is not the case .

在10g中，除了创建或修改LOB时，UNDO_RETENTION的当前设置外，我们无法控制其他 lobs 的 RETENTION 的实际设置。停止和启动数据库不会修改设置，因为它似乎是静态的。假定当UNDO_RETENTION更改时，与该保留连接的叶也将更改，情况并非如此

If a lob is modified from RETENTION to PCTVERSION and back to RETENTION again then the lob retention is updated.

如果将 lob 从 RETENTION 修改为 PCTVERSION，然后再次返回 RETENTION，则将更新lob retention

In 11g we allow users to specify retention for each LOB segment .  在11g中，我们允许用户为每个LOB段指定保留时间

NOTE 563470.1 Lob retention not changing when undo_retention is changed

NOTE 2175438.1 How to change retention of securefile Lob segment

c.) More checks

If BLOB/CLOB is NOCACHE , then check if making CACHE helps or has no effect.

如果 BLOB/CLOB 是NOCACHE，则检查使CACHE是否有用或无效

d.) Bugs

d.1) BUG 5636728 - LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation
Versions confirmed as being affected - 10.2.0.3

Description

After shrinking an ENABLE STORAGE IN ROW LOB column selecting the LOB may fail with ORA-1555 / ORA-22924 errors .

This issue is fixed in:

10.2.0.2 Patch 14 on Windows Platforms
10.2.0.2 Patch 15 on Windows Platforms
10.2.0.3 Patch 9 on Windows Platforms
10.2.0.4 (Server Patch Set)
11.1.0.6 (Base Release)

d.2)  Bug 5212539 - LOB corruption for NOCACHE LOBs (ORA-1555/ORA-22924)

Versions confirmed as being affected

9.2.0.6
9.2.0.7
10.1.0.4
10.1.0.5
10.2.0.1
10.2.0.2

Description:

If a LOB is created in an ASSM tablespace without the CACHE option then LOB corruption can result during roll-forward (on a standby or following recovery) if a rollback to savepoint is done between two LOB writes in the same transaction. A rollback to savepoint can occur , for example, if a constraint violation occurs causing an implicit statement level rollback. The data at the primary is not corrupt but the REDO stream contains the wrong information to recover through the rollback to savepoint.

LOB corruptions can typically show as ORA-1555 errors when accessing the LOB column.

This issue is fixed in:

9.2.0.8 (Server Patch Set)
10.2.0.2 Patch 10 on Windows Platforms
10.2.0.3 (Server Patch Set)

d.3)

@ e.) Additional Information

REFERENCES

NOTE:452341.1 - ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption.
NOTE:66431.1 - LOBS - Storage, Redo and Performance Issues
NOTE:833635.1 - Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility
NOTE:728758.1 - HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES