风哥教程

培训 . 交流 . 分享
Make progress together!

expdp备份时出现ORA-01555: snapshot too old: rollback segment number too small

[复制链接]
内容发布:风哥| 发布时间:2014-1-26 10:50:31
在使用数据泵(Export Datapump)进行oracle数据库逻辑备份时,出现ORA-31693, ORA-02354 and ORA-01555 with Export Datapump报错的处理过程。以下来自Oralce官方的处理建议:

Symptoms
Using Database Data Pump (expdp), one table fails to export with the following errors:


ORA-31693: Table data object "APPLSYS"."FND_LOBS" 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 154 with name "_SYSSMU154_1434655980$" too small
Followed the steps in Note:452341.1 and/or Note:787004.1, and found that there is no LOB corruption.


Cause
The old versions (consistent read) of the LOB can be specified by either the PCTVERSION or the RETENTION parameters.
For SecureFiles, only the RETENTION parameter can be specified.
for BasicFiles LOBs you can specify either PCTVERSION or RETENTION , but not both.

- PCTVERSION : This parameter specifies the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. Under 11g compatibility, this parameter is silently ignored when SecureFiles LOBs are created.
PCTVERSION is the default in manual undo mode and the default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode.
- RETENTION is the default in automatic undo mode.
You can specify the RETENTION parameter only if the database is running in automatic undo mode. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. In automatic undo mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION.
You can specify the optional settings after RETENTION only if you are using SecureFiles.
You can see more details about the RETENTION parameter for SecureFiles and BasicFiles LOBs in the following link :
http://docs.oracle.com/cd/E11882 ... bles.htm#ADLOB45282
RETENTION Parameter for SecureFiles LOBs
http://docs.oracle.com/cd/E11882 ... bles.htm#ADLOB45281
RETENTION Parameter for BasicFiles LOBs

To Determine whether LOB segment is using RETENTION or PCTVERSION , use the following statement :
SQL> select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER=upper('&OWNER') and TABLE_NAME=upper('&TABLE_NAME') ;
COLUMN_NAME                    SEC PCTVERSION  RETENTION
------------------------------ --- ---------- ----------
FILE_DATA                NO                    900

Note : If You've a value for both PCTVERSION and RETENTION columns , this is incorrect output and to really know which option the LOB is using Please Check Note 422826.1 : How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary

The LOB Retention is not defined properly , This is confirmed by queries:

SQL> show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
16331
We see the retention comes back showing 900 seconds (15 minutes) which is the same as the current UNDO_RETENTION, but the maxquery length is 16331 seconds.

When the LOB was created, the actual setting for RETENTION was defined by the current setting for UNDO_RETENTION.
This time is not long enough.

Solution

1. Modify the current UNDO_RETENTION for the database:
SQL>ALTER SYSTEM SET UNDO_RETENTION = 16500 scope=both sid='*';

2. Modify the LOB retention to become greater than the undersized retention parameter following the steps from Note:563470.1
SQL> alter table APPLSYS.FND_LOBS modify lob(FILE_DATA) (pctversion 5);
Table altered.
SQL> alter table APPLSYS.FND_LOBS modify lob(FILE_DATA) (retention);
Table altered.

3. Query the lob retention again to verify that the change has taken hold:
SQL> select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER=upper('&OWNER') and TABLE_NAME=upper('&TABLE_NAME') ;
COLUMN_NAME                    SEC PCTVERSION  RETENTION
------------------------------ --- ---------- ----------



上一篇:Oracle数据库出现出ORA-00600[6002]与[25027]报错处理
下一篇:关于resource busy and acquire with nowait specified的解决方法
专业提供Oracle/MySQL/NoSQL/Linux数据库培训与技术支持服务,QQ号:113257174
关注风哥教程微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

热门文章教程

  • 实战PHP与MySQL权威指南PDF电子书下载
  • 大数据技术与应用入门培训教程(电子版下载
  • Oracle 12cR2 九大新功能全面曝光_详解云数
  • Oracle OCP认证考试IZ0-053题库共712题数据
  • MySQL5权威指南(第3版)PDF电子版下载
  • 风哥Oracle数据库巡检工具V1.0(附2.6网页
快速回复 返回顶部 返回列表