Oracle数据库导出大字段(CLOB)数据问解及解决

问题描述：数据库导出数据日志报错 

操作系统版本：RHEL AS4 U4 64bit

数据库版本：Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit

错误代号：ORA-01555 ORA-22924

 

查看导出数据命令

$ cat /oradata/info/backup/scripts/exp_couser

 

exp user/passwd buffer=1048576 compress=n owner=couser statistics=none compress=n consistent=y file=/oradata/info/backup/exp_couser_`date +%Y%m%d`.dmp log=/oradata/info/backup/exp_log_`date +%Y%m%d_%H%M%S`.log

 

查看exp导出数据日志

$ cat /oradata/info/backup/exp_log_20080218_010101.log

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user COUSER

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user COUSER

About to export COUSER's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export COUSER's tables via Conventional Path ...

. . exporting table               FSM_COLTREE2XXFL         84 rows exported

. . exporting table              FSM_RIGHT_ARTICLE

EXP-00056: ORACLE error 1555 encountered

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

ORA-22924: snapshot too old

. . exporting table              FSM_RIGHT_COLTREE        369 rows exported

. . exporting table                FSM_RIGHT_DELEY        227 rows exported

. . exporting table                FSM_RIGHT_GROUP          3 rows exported

. . exporting table                 FSM_RIGHT_USER          8 rows exported

. . exporting table               FSM_SYSTEM_ADMIN          1 rows exported

. . exporting table            LOG_ARTICLE_PUBLISH    5688437 rows exported

. . exporting table            TBL_FSM_PRODUCTINFO        948 rows exported

. . exporting table               TBL_FSM_PRODUCTS        206 rows exported

. . exporting table           TBL_FSM_PRODUCT_TYPE          7 rows exported

. . exporting table            TBL_FSM_SCREEN_TYPE         12 rows exported

. . exporting table             TBL_FSM_STYLE_INFO       7278 rows exported

. . exporting table         TBL_FSM_STYLE_POSITION        159 rows exported

. . exporting table                   TBL_FSM_USER          2 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

导出日志显示：表FSM_RIGHT_ARTICLE的记录未能正常导出，出现了ORA-01555和ORA-22924错误

 

查看数据库表空间使用情况，正常

查看数据库警告日志文件，未发现明显异常

查看ORA-01555和ORA-22924 错误描述

$ oerr ora 01555

01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"

// *Cause: rollback records needed by a reader for consistent read are

//         overwritten by other writers

// *Action: If in Automatic Undo Management mode, increase undo_retention

//          setting. Otherwise, use larger rollback segments

 

$ oerr ora 22924

22924, 00000, "snapshot too old"

//  *Cause:  The version of the LOB value needed for the consistent read was

//           already overwritten by another writer.

//  *Action: Use a larger version pool.

 

undo_retention 参数指定了保留还原数据信息的时间(秒)

 

显示当前 undo_retention 参数值

$ sqlplus /nolog

$ conn / as sysdba

SQL> show parameter undo_retention

 

NAME              TYPE    VALUE

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

undo_retention        integer    900

 

修改 undo_retention 参数值

SQL> alter system set undo_retention = 10800 scope=both;

运行导出脚本 exp...   错误依旧...

 

SQL> alter system set undo_retention = 108000 scope=both;

运行导出脚本 exp...   错误依旧...

 

SQL> alter system set undo_retention = 1080000 scope=both;

运行导出脚本 exp...   错误依旧...

 

与此参数无关，改成3600

SQL> alter system set undo_retention = 3600 scope=both;

 

测试导出表fsm_right_article 10行记录，导出成功

$ exp user/passwd statistics=none compress=n consistent=y file=/oradata/info/backup/exp_article.dmp log=/oradata/info/backup/exp_article.log tables=fsm_right_article query=\"where rownum\<=10\"

 

测试导出10,000行记录，导出失败

$ exp user/passwd statistics=none compress=n consistent=y file=/oradata/info/backup/exp_article.dmp log=/oradata/info/backup/exp_article.log tables=fsm_right_article query=\"where rownum\<=10\"

 

经反复测试，确定可以成功导出2400行记录

$ exp user/passwd statistics=none compress=n consistent=y file=/oradata/info/backup/exp_article.dmp log=/oradata/info/backup/exp_article.log tables=fsm_right_article query=\"where rownum\<=2400\"

 

编写一个shell脚本，循环执行导出2400条记录的命令，直到导出全部记录

总共记录有220979，每次导出记录2400行

第一次导出 第 1 行到第 2400 行的记录

第一次导出 第 2401 行到 4800 行的记录

…

第n次导出  第 (n-1)×2400+1 行到 n×2400 行的记录

 

查看表结构，唯一索引的字段无number类型的，不能直接使用作为记数条件

 

以下SQL的作用是：先将表按主键字段排序，然后取第1行到第2400行的记录

select articleid from

(

  select rownum as r_n, article. articleid from

  (

    select articleid from fsm_right_article

    order by articleid desc

  ) article

  where rownum <= 2400

)

where r_n > 0

 

将以上SQL写入exp导出命令的query参数中，注意转义符\的使用

$ exp user/passwd statistics=none compress=n file=/oradata/info/backup/article/article_$i.dmp log=/oradata/info/backup/article/article_$i.log consistent=y tables=fsm_right_article query=\"where articleid in \(select articleid from \(select rownum as r_n\, article\.articleid from \(select articleid from fsm_right_article order by articleid desc\) article where rownum \<= 2400\) where r_n \> 0\)\"

 

编写一个shell脚本，循环执行导出2400条记录的命令，导出表的全部记录

vi /oradata/info/backup/scrips/exp_article.sh

 

#!/bin/bash

export ORACLE_BASE=/oracle/app/oracle

export ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export ORACLE_SID=info

export PATH=/oracle/app/oracle/product/10.2.0/db_1/bin:$PATH:$HOME/bin

 

count=220979

i=0

step=2400

 

while [ $i -le $count ]

do

 

exp user/passwd statistics=none compress=n file=/oradata/info/backup/article/article_$i.dmp log=/oradata/info/backup/article/article_$i.log consistent=y tables=fsm_right_article query=\"where articleid in \(select articleid from \(select rownum as r_n\, article\.articleid from \(select articleid from couser\.fsm_right_article order by articleid desc\) article where rownum \<= $i+$step\) where r_n \> $i\)\"

 

i=`expr $i + $step`

file=`expr $file + 1`

 

done

 

运行 exp_article.sh 生成dmp文件和日志文件

$ /oradata/info/backup/scrips/exp_article.sh

 

查看导出日志文件是否出现错误

$ find /oradata/info/backup/article -name "*.log" | xargs grep -i "with warnings"

 

/oradata/info/backup/article/article_108400.log:Export terminated successfully with warnings.

/oradata/info/backup/article/article_110200.log:Export terminated successfully with warnings.

 

查看出错的导出日志文件

$ cat /oradata/info/backup/article/article_108400.log

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user COUSER

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user COUSER

About to export COUSER's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export COUSER's tables via Conventional Path ...

. . exporting table              FSM_RIGHT_ARTICLE

EXP-00056: ORACLE error 1555 encountered

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

ORA-22924: snapshot too old

Export terminated successfully with warnings.

 

导出日志表明：

108400 至 110800(108400+2400) 的记录未被导出

110200 至 112600(110200+2400) 的记录未被导出

 

修改导出脚本 每次导出100条记录，导出108400至110800的记录(蓝色字体为修改的部分)

vi /oradata/info/backup/scrips/exp_article.sh

 

#!/bin/bash

export ORACLE_BASE=/oracle/app/oracle

export ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export ORACLE_SID=info

export PATH=/oracle/app/oracle/product/10.2.0/db_1/bin:$PATH:$HOME/bin

 

count=110800

i=108400

step=100

 

while [ $i -le $count ]

do

 

exp user/passwd statistics=none compress=n file=/oradata/info/backup/article/article_$i.dmp log=/oradata/info/backup/article/article_$i.log consistent=y tables=fsm_right_article query=\"where articleid in \(select articleid from \(select rownum as r_n\, article\.articleid from \(select articleid from couser\.fsm_right_article order by articleid desc\) article where rownum \<= $i+$step\) where r_n \> $i\)\"

 

i=`expr $i + $step`

file=`expr $file + 1`

 

done

 

运行 exp_article.sh 生成dmp文件和日志文件

$ /oradata/info/backup/scrips/exp_article.sh

 

查看导出日志文件是否出现错误

$ find /oradata/info/backup/article -name "*.log" | xargs grep -i "with warnings"

 

/oradata/info/backup/article/article_108450.log:Export terminated successfully with warnings.

 

修改导出脚本 每次导出1条记录，导出108450至108550的记录(蓝色字体为修改的部分)

vi /oradata/info/backup/scrips/exp_article.sh

 

#!/bin/bash

export ORACLE_BASE=/oracle/app/oracle

export ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export ORACLE_SID=info

export PATH=/oracle/app/oracle/product/10.2.0/db_1/bin:$PATH:$HOME/bin

 

count=108550

i=108450

step=1

 

while [ $i -le $count ]

do

 

exp user/passwd statistics=none compress=n file=/oradata/info/backup/article/article_$i.dmp log=/oradata/info/backup/article/article_$i.log consistent=y tables=fsm_right_article query=\"where articleid in \(select articleid from \(select rownum as r_n\, article\.articleid from \(select articleid from couser\.fsm_right_article order by articleid desc\) article where rownum \<= $i+$step\) where r_n \> $i\)\"

 

i=`expr $i + $step`

file=`expr $file + 1`

 

done

 

运行 exp_article.sh 生成dmp文件和日志文件

$ /oradata/info/backup/scrips/exp_article.sh

 

查看导出日志文件是否出现错误

$ find /oradata/info/backup/article -name "*.log" | xargs grep -i "with warnings"

 

/oradata/info/backup/article/article_108459.log:Export terminated successfully with warnings.

 

查看相应的表记录

select * from fsm_right_article

where articleid in

(

  select articleid from

  (

    select rownum as r_n, article.articleid from

    (

      select articleid from fsm_right_article

      order by articleid desc

    ) article

    where rownum <= 108459 +1

  )

  where r_n > 108459

)

 

发现content字段(clob类型)显示有问题（字样）

用同样方法找到110200 至 112600的错误记录

 

和研发部门同事确认这两条记录content字段的值并更新

SQL> update fsm_right_article set content=’…’ where articleid = …

 

再运行导出脚本无错误显示，问题解决！