ORACLE expdp備份與ORA-31693、ORA-02354、ORA-01555
近期，某綜合網管系統expdp備份出現異常，報錯資訊如下：
Export: Release 10.2.0.4.0 - 64bit Production on 星期二, 12 4月, 2016 11:30:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_SCHEMA_07": system/******** schemas=ultra directory=backup_expdp dumpfile=expnms_201604121.dmp,expnms_201604122.dmp,expnms_201604123.dmp,exp_nms201604124.dmp filesize=40g VERSION=10.2.0.2 exclude=statistics logfile=exp_20160412.log
正在使用 BLOCKS 方法進行估計...
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 197.1 GB
處理物件型別 SCHEMA_EXPORT/USER
處理物件型別 SCHEMA_EXPORT/SYSTEM_GRANT
處理物件型別 SCHEMA_EXPORT/ROLE_GRANT
處理物件型別 SCHEMA_EXPORT/DEFAULT_ROLE
處理物件型別 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
處理物件型別 SCHEMA_EXPORT/TYPE/TYPE_SPEC
處理物件型別 SCHEMA_EXPORT/DB_LINK
處理物件型別 SCHEMA_EXPORT/SEQUENCE/SEQUENCE
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE
處理物件型別 SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/INDEX
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/COMMENT
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
處理物件型別 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
處理物件型別 SCHEMA_EXPORT/VIEW/VIEW
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/TRIGGER
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
處理物件型別 SCHEMA_EXPORT/JOB
處理物件型別 SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
ORA-31693: 表資料物件 "ULTRA"."DAY_META_TASK_RESULT_B" 無法載入/解除安裝並且被跳過, 錯誤如下:
ORA-02354: 匯出/匯入資料時出錯
ORA-01555: 快照過舊: 回退段號 24 (名稱為 "_SYSSMU24$") 過小
. . 匯出了 "ULTRA"."ZXMG_UDPATE_BACKUP" 18.36 GB 536021168 行
接下來是問題的處理過程，看到Ora-01555，首先想到的是檢視及調整undo_retention引數
SQL> show parameter undo_r
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 3600
SQL> select tablespace_name,sum(bytes)/1024/1024/1024 gb from dba_data_files where tablespace_name like '%UNDO%' group by tablespace_name;
TABLESPACE_NAME GB
------------------------------ ----------
UNDOTBS1 106.992172
然後檢視了undo表空間的使用率，其使用率只有1%，因此考慮調整undo_retention引數到5400，調整後問題依舊，後來又調整到7200，問題依然存在，但是undo表空間的使用率最高到30%。現在，可以判斷不是單純的undo表空間引數undo_retention的問題了，需要具體問題具體分析一下。檢視問題表的結構發現，該表有2個大欄位：
SQL> desc "ULTRA"."DAY_META_TASK_RESULT_B"
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
META_TASK_RESULT_ID NOT NULL VARCHAR2(50)
SCHEDULER_ID VARCHAR2(50)
META_TASK_ID VARCHAR2(50)
TIME NUMBER(20)
ALARM NUMBER(5)
META_TASK_STATE NUMBER(5)
INSTANCE VARCHAR2(200)
UNIT VARCHAR2(10)
VALUE_CHECK VARCHAR2(5)
SHELL_RESULT CLOB
ADVICE VARCHAR2(4000)
OPINION VARCHAR2(100)
ALARM_INFO VARCHAR2(200)
VALUE CLOB
VTABLE VARCHAR2(30)
REFLAG VARCHAR2(50)
透過查詢oracle官網，oracle的lob大欄位有自己的retention引數，如果只調整undo_retention，而沒有同步到lob大欄位，該引數還是預設的900s，確認查詢結果如下：
SQL>select table_name,column_name,pctversion,retention from dba_lobs where table_name='DAY_META_TASK_RESULT_B'
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION
------------------------------ ------------------------------ ---------- ----------
DAY_META_TASK_RESULT_B SHELL_RESULT 900
DAY_META_TASK_RESULT_B VALUE 900
明確了問題原因，接下來就是修改表含有lob大欄位的retention設定
SQL>ALTER TABLE DAY_META_TASK_RESULT_B MODIFY LOB(SHELL_RESULT)(retention);
table altered.
SQL>ALTER TABLE DAY_META_TASK_RESULT_B MODIFY LOB(VALUE)(retention);
table altered.
SQL>select table_name,column_name,pctversion,retention from dba_lobs where table_name='DAY_META_TASK_RESULT_B'
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION
------------------------------ ------------------------------ ---------- ----------
DAY_META_TASK_RESULT_B SHELL_RESULT 7200
DAY_META_TASK_RESULT_B VALUE 7200
調整完畢後，該平臺的expdp恢復正常：
Export: Release 10.2.0.4.0 - 64bit Production on 星期四, 21 4月, 2016 11:30:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** schemas=ultra directory=backup_expdp dumpfile=expnms_201604211.dmp,expnms_201604212.dmp,expnms_201604213.dmp,exp_nms201604214.dmp filesize=40g VERSION=10.2.0.2 exclude=statistics logfile=exp_20160421.log
正在使用 BLOCKS 方法進行估計...
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 199.8 GB
處理物件型別 SCHEMA_EXPORT/USER
處理物件型別 SCHEMA_EXPORT/SYSTEM_GRANT
處理物件型別 SCHEMA_EXPORT/ROLE_GRANT
處理物件型別 SCHEMA_EXPORT/DEFAULT_ROLE
處理物件型別 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
處理物件型別 SCHEMA_EXPORT/TYPE/TYPE_SPEC
處理物件型別 SCHEMA_EXPORT/DB_LINK
處理物件型別 SCHEMA_EXPORT/SEQUENCE/SEQUENCE
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE
處理物件型別 SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/INDEX
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/COMMENT
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
處理物件型別 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
處理物件型別 SCHEMA_EXPORT/VIEW/VIEW
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/TRIGGER
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
處理物件型別 SCHEMA_EXPORT/JOB
處理物件型別 SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . 匯出了 "ULTRA"."DAY_META_TASK_RESULT_B" 39.40 GB 20009768 行
. . 匯出了 "ULTRA"."ZXMG_UDPATE_BACKUP" 18.87 GB 550823549 行
.
.
.
. . 匯出了 "ULTRA"."WLANUPDATESCHEDULE" 0 KB 0 行
. . 匯出了 "ULTRA"."WORKFLOW_STAT" 0 KB 0 行
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_SCHEMA_02"
******************************************************************************
SYSTEM.SYS_EXPORT_SCHEMA_02 的轉儲檔案集為:
/opt/backup/expnms_201604211.dmp
/opt/backup/expnms_201604212.dmp
/opt/backup/expnms_201604213.dmp
/opt/backup/exp_nms201604214.dmp
作業 "SYSTEM"."SYS_EXPORT_SCHEMA_02" 已於 18:12:35 成功完成
至此，故障處理完畢！
Export: Release 10.2.0.4.0 - 64bit Production on 星期二, 12 4月, 2016 11:30:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_SCHEMA_07": system/******** schemas=ultra directory=backup_expdp dumpfile=expnms_201604121.dmp,expnms_201604122.dmp,expnms_201604123.dmp,exp_nms201604124.dmp filesize=40g VERSION=10.2.0.2 exclude=statistics logfile=exp_20160412.log
正在使用 BLOCKS 方法進行估計...
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 197.1 GB
處理物件型別 SCHEMA_EXPORT/USER
處理物件型別 SCHEMA_EXPORT/SYSTEM_GRANT
處理物件型別 SCHEMA_EXPORT/ROLE_GRANT
處理物件型別 SCHEMA_EXPORT/DEFAULT_ROLE
處理物件型別 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
處理物件型別 SCHEMA_EXPORT/TYPE/TYPE_SPEC
處理物件型別 SCHEMA_EXPORT/DB_LINK
處理物件型別 SCHEMA_EXPORT/SEQUENCE/SEQUENCE
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE
處理物件型別 SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/INDEX
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/COMMENT
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
處理物件型別 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
處理物件型別 SCHEMA_EXPORT/VIEW/VIEW
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/TRIGGER
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
處理物件型別 SCHEMA_EXPORT/JOB
處理物件型別 SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
ORA-31693: 表資料物件 "ULTRA"."DAY_META_TASK_RESULT_B" 無法載入/解除安裝並且被跳過, 錯誤如下:
ORA-02354: 匯出/匯入資料時出錯
ORA-01555: 快照過舊: 回退段號 24 (名稱為 "_SYSSMU24$") 過小
. . 匯出了 "ULTRA"."ZXMG_UDPATE_BACKUP" 18.36 GB 536021168 行
接下來是問題的處理過程，看到Ora-01555，首先想到的是檢視及調整undo_retention引數
SQL> show parameter undo_r
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 3600
SQL> select tablespace_name,sum(bytes)/1024/1024/1024 gb from dba_data_files where tablespace_name like '%UNDO%' group by tablespace_name;
TABLESPACE_NAME GB
------------------------------ ----------
UNDOTBS1 106.992172
然後檢視了undo表空間的使用率，其使用率只有1%，因此考慮調整undo_retention引數到5400，調整後問題依舊，後來又調整到7200，問題依然存在，但是undo表空間的使用率最高到30%。現在，可以判斷不是單純的undo表空間引數undo_retention的問題了，需要具體問題具體分析一下。檢視問題表的結構發現，該表有2個大欄位：
SQL> desc "ULTRA"."DAY_META_TASK_RESULT_B"
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
META_TASK_RESULT_ID NOT NULL VARCHAR2(50)
SCHEDULER_ID VARCHAR2(50)
META_TASK_ID VARCHAR2(50)
TIME NUMBER(20)
ALARM NUMBER(5)
META_TASK_STATE NUMBER(5)
INSTANCE VARCHAR2(200)
UNIT VARCHAR2(10)
VALUE_CHECK VARCHAR2(5)
SHELL_RESULT CLOB
ADVICE VARCHAR2(4000)
OPINION VARCHAR2(100)
ALARM_INFO VARCHAR2(200)
VALUE CLOB
VTABLE VARCHAR2(30)
REFLAG VARCHAR2(50)
透過查詢oracle官網，oracle的lob大欄位有自己的retention引數，如果只調整undo_retention，而沒有同步到lob大欄位，該引數還是預設的900s，確認查詢結果如下：
SQL>select table_name,column_name,pctversion,retention from dba_lobs where table_name='DAY_META_TASK_RESULT_B'
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION
------------------------------ ------------------------------ ---------- ----------
DAY_META_TASK_RESULT_B SHELL_RESULT 900
DAY_META_TASK_RESULT_B VALUE 900
明確了問題原因，接下來就是修改表含有lob大欄位的retention設定
SQL>ALTER TABLE DAY_META_TASK_RESULT_B MODIFY LOB(SHELL_RESULT)(retention);
table altered.
SQL>ALTER TABLE DAY_META_TASK_RESULT_B MODIFY LOB(VALUE)(retention);
table altered.
SQL>select table_name,column_name,pctversion,retention from dba_lobs where table_name='DAY_META_TASK_RESULT_B'
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION
------------------------------ ------------------------------ ---------- ----------
DAY_META_TASK_RESULT_B SHELL_RESULT 7200
DAY_META_TASK_RESULT_B VALUE 7200
調整完畢後，該平臺的expdp恢復正常：
Export: Release 10.2.0.4.0 - 64bit Production on 星期四, 21 4月, 2016 11:30:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** schemas=ultra directory=backup_expdp dumpfile=expnms_201604211.dmp,expnms_201604212.dmp,expnms_201604213.dmp,exp_nms201604214.dmp filesize=40g VERSION=10.2.0.2 exclude=statistics logfile=exp_20160421.log
正在使用 BLOCKS 方法進行估計...
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 199.8 GB
處理物件型別 SCHEMA_EXPORT/USER
處理物件型別 SCHEMA_EXPORT/SYSTEM_GRANT
處理物件型別 SCHEMA_EXPORT/ROLE_GRANT
處理物件型別 SCHEMA_EXPORT/DEFAULT_ROLE
處理物件型別 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
處理物件型別 SCHEMA_EXPORT/TYPE/TYPE_SPEC
處理物件型別 SCHEMA_EXPORT/DB_LINK
處理物件型別 SCHEMA_EXPORT/SEQUENCE/SEQUENCE
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE
處理物件型別 SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/INDEX
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/COMMENT
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
處理物件型別 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
處理物件型別 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
處理物件型別 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
處理物件型別 SCHEMA_EXPORT/VIEW/VIEW
處理物件型別 SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
處理物件型別 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
處理物件型別 SCHEMA_EXPORT/TABLE/TRIGGER
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
處理物件型別 SCHEMA_EXPORT/JOB
處理物件型別 SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . 匯出了 "ULTRA"."DAY_META_TASK_RESULT_B" 39.40 GB 20009768 行
. . 匯出了 "ULTRA"."ZXMG_UDPATE_BACKUP" 18.87 GB 550823549 行
.
.
.
. . 匯出了 "ULTRA"."WLANUPDATESCHEDULE" 0 KB 0 行
. . 匯出了 "ULTRA"."WORKFLOW_STAT" 0 KB 0 行
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_SCHEMA_02"
******************************************************************************
SYSTEM.SYS_EXPORT_SCHEMA_02 的轉儲檔案集為:
/opt/backup/expnms_201604211.dmp
/opt/backup/expnms_201604212.dmp
/opt/backup/expnms_201604213.dmp
/opt/backup/exp_nms201604214.dmp
作業 "SYSTEM"."SYS_EXPORT_SCHEMA_02" 已於 18:12:35 成功完成
至此，故障處理完畢！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29618264/viewspace-2149733/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE expdp備份與ORA-31693、ORA-02354、ORA-02149Oracle
- 揭祕ORACLE備份之－－邏輯備份（EXPDP）Oracle
- Oracle 邏輯備份 expdp/impdpOracle
- oracle資料泵備份（Expdp命令）Oracle
- Oracle 邏輯備份之EXPDP精講Oracle
- Oracle 效能優化-expdp備份速度優化02Oracle優化
- Oracle 效能優化-expdp備份速度優化03Oracle優化
- Oracle EXPDP自動備份緩慢問題解決Oracle
- windowns系統，oracle資料庫expdp自動備份Oracle資料庫
- expdp 邏輯備份指令碼指令碼
- ORACLE的EXPDP與ORA-39125、ORA-01555、ORA-06512Oracle
- 【EXPDP】不使用DIRECTORY引數完成expdp資料備份
- ORACLE expdp備份到windows網路共享檔案目錄（NFS）OracleWindowsNFS
- Oracle 11g EXPDP、RMAN自動備份SHELL指令碼Oracle指令碼
- expdp備份+FTP自動上傳FTP
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- Oracle之差異增量備份與累積增量備份Oracle
- Oracle 備份 與 恢復 概述Oracle
- Oracle RAC備份與恢復Oracle
- Oracle備份與恢復 (zt)Oracle
- Oracle備份與恢復案例Oracle
- Oracle備份與恢復(轉)Oracle
- 【EXPDP】使用EXPDP備份資料時預估大小——ESTIMATE引數
- ORACLE 資料匯出LOB欄位報錯ORA-31693,ORA-02354,ORA-22924Oracle
- Oracle資料庫冷備份與熱備份操作梳理Oracle資料庫
- Oracle RMAN中全備份與0級備份區別Oracle
- Linux 平臺下 Oracle 資料泵備份（expdp） SHELL 指令碼LinuxOracle指令碼
- 【EXPDP】使用expdp的QUERY引數限定備份資料的範圍
- Expdp 備份到ASM之 ORA-39070ASM
- oracle備份--離線備份Oracle
- 備份與恢復oracle_homeOracle
- Oracle OCR的備份與恢復Oracle
- Oracle 備份與恢復(一):概念Oracle
- oracle備份與恢復雜記Oracle
- Oracle備份與恢復入門Oracle
- Oracle備份與恢復案例 (zt)Oracle