Oracle数据泵expdp导出报ORA-01555和ORA-22924

0    122    1

Tags:

👉 本文共约3877个字,系统预计阅读时间或需15分钟。

现象

原因

1、undo_retention 参数配置过小,表本身很大或含有CLOB字段

2、表存在大的CLOB字段,而lob字段有坏块导致。

解决

可能原因一

表中存在lob字段,而lob字段有坏块导致。报错如下:

首先应该查询坏块的rowid,并且记录到sys.corrupted_lob_data表:

然后查询表sys.corrupted_lob_data

MOS上给出的导出方案是将问题数据exclude掉,这里为了临时解决问题,将这几条数据的COLB字段置空。然后再次导出数据库数据,不再提示报错。

可能原因二

原因二是undo_retention 参数配置过小,表本身很大或含有CLOB字段,报错:

首先修改undo_retention=10800,若含有大的LOB字段,则重新导出可能问题依旧存在:

这个表本身不大,但是lob字段占用的空间很大,可以发现即使改动了参数undo_retention=10800,表lob的属性RETENTION依旧900,必须增加该参数,或者使用PCTVERSION参数。

修改以PCTVERSION为准:

查询:

若是以undo_retention为准,则前后顺序需要修改:

查询:

然后重新导出表即可。

LOB字段大小查询

LOB字段的retention和PCTVERSION说明

pctversion n /retention:这两个属性用来解决lob段的一致性读问题。lob的特殊性决定它不能使用undo/rollback segment来管理自己的更新的old version,通常lob会在自己所在的表空间中划分一部分空间来管理自己的undo,保证read consistent.lob中更新原理是在lob segment中分配新的chunk插入新的数据,保留旧的镜像,如果一个数据有多个更新存在的话, 那么就会存在多个版本.pctversion用来定义lob segment中undo区域的大小,pctverision 是一个百分比,定义所有lob空间用来存放前镜像的百分比,如果前镜像使用空间超过这个百分比了,oracle不自动扩展这部分的大小, 会重用这些前镜像的空间.如果一个lob segment段的更新很频繁的情况下,那么该lob段的增长可能会很快.retention是9i的新参数, 只能用在tablespace采用ASSM的情况,在lob更新的时候,前镜像会保留一段时间, 具体的时间由undo_retention参数决定.决定采用乃种undo 方式,必须对应用测试后在决定.

Lob字段默认是pctversion等到于10,意思是表lob字段所在的表空间需要预留10%的空间给lob的前映象使用,当表空间比较小时,用pctversion应该不会有大的问题,当表空间超过100g时,就需要预留10g,可能存在浪费空间的问题,比如每小时更新lob记录的频率增长不多,还要减去(当lob的内容小于4000字节时,lob的前映象保存在undo表空间)这部分,但如果该表空间增长到300g,就有30g的空间预留,导致较多的空间被浪费.所以,当您的LOB字段所在的表空间已经很大时,要注意pctversion参数要适当的调小,否则空间的浪费是相当的严重...

关于lob的retention参数,这个与undo表空间的undo_retention是一致的,如果你的lob更新较小,就可以通过修改undo_retention来调整lob前映象的失效时间,由于lob的retention参数是与undo_retention保持一致,所以修改undo_retention来说,一定要慎重,毕竟它将会影响库中所有lob字段的retention参数...

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.

The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.

Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema 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
Information in this document applies to any platform.

SYMPTOMS

Following errors encountered during export:

ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old

CAUSE

LOB segment corruption.

How To Confirm LOB Segment Corruption Using Export Utility

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

- Identify the table rowid's referencing the corrupted LOB segment blocks by running the PL/SQL procedure mentioned in the Note 787004.1.

As there is already a similar PL/SQL procedure indicated in various notes such as: Note 452341.1 or Note 253131.1, the reason for this article is to propose a method to confirm the LOB segment corruption using export utility by exporting the corrupted rowids.

For example:

If you encounter the error “ORA-1555: snapshot too old: rollback segment number with name " " too small” during export of table () with a LOB column (), then run the PL/SQL procedure mentioned in the Note 787004.1.

Note: Run the PL/SQL procedure after creating a temporary table “CORRUPTED_LOB_DATA”. This table is used to spool the corrupted rowids.

SOLUTION

o Alternatively, you could use flashback query to solve the LOB corruption. However, details are beyond the scope of this article.

For more information, please refer to http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#i1008579

Note: If there is no corrupt lob data and the issue persists then rebuild the entire table to resolve the issue.

REFERENCES

NOTE:253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555)
NOTE:452341.1 - ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption.
NOTE:787004.1 - Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922

Lob retention not changing when undo_retention is changed (Doc ID 563470.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 10.2.0.5 [Release 8.1.7 to 10.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

Disclaimer: this note illustrates a "problem"/behavior of setting retention in 10g (8.1.7.4~10.2.0.5), and is not intended to explain how retention/pctversion works or more retention option in 11g/12c.

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 . (this is incorrect)

If a lob is modified from RETENTION to PCTVERSION and back to RETENTION again then the lob retention is updated based on the current UNDO_RETENTION at the time that the LOB was altered / created to use RETENTION.

In 11g we allow users to specify retention for each LOB segment

Current documentation reads

Oracle� Database Application Developer's Guide - Large Objects
10g Release 2 (10.2)
Part Number B14249-01

4 LOBs in Tables

RETENTION

As an alternative to the PCTVERSION parameter, you can specify the RETENTION parameter in the LOB storage clause of the CREATE TABLE
or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a period of time, rather than using a
percentage of the table space.

For example:
.
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB)
lob (c) STORE AS SEGNAME (TABLESPACE lobtbs1 CHUNK 4096
RETENTION
NOCACHE LOGGING
STORAGE (MAXEXTENTS 5)
);

The RETENTION parameter is designed for use with Undo features of the database, such as Flashback Versions Query.When a LOB column has
the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.

Note the following with respect to the RETENTION parameter:

* Undo SQL is not enabled for LOB columns as it is with other datatypes. You must set the RETENTION property on a LOB column to use
Undo SQL on LOB data.

* 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. You must configure your tablefor use with
Automatic Undo Management before you can set RETENTION on a LOB column.

* The LOB storage clause can specify RETENTION or PCTVERSION, but not both.

CAUSE

Problem is caused as current documentation not fully explaining how lob retention works.

SOLUTION

Here is example of the problem.

参考

http://www.dbdream.com.cn/2016/10/oracle-%E4%BF%AE%E6%94%B9lob%E5%AD%97%E6%AE%B5pctversion-%E7%9A%84%E6%96%B9%E6%B3%95/

https://www.cnblogs.com/datalife/p/7574396.html
http://dbs-service.cn/index.php/a/271.html

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

1 × 2 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部