expdp導(dǎo)出數(shù)據(jù)時(shí)朴译，ORA-31693井佑，ORA-02354，ORA-01555錯(cuò)誤問題處理
一眠寿、機(jī)器及數(shù)據(jù)庫配置
OS:RHEL6.5
內(nèi)核:2.6.32-431.el6.x86_64
CPU:4顆96c
內(nèi)存:256G
數(shù)據(jù)版本：oracle 11.2.0.4 rac 雙節(jié)點(diǎn)
數(shù)據(jù)文件存儲(chǔ)：ASM
數(shù)據(jù)導(dǎo)出方式：expdp ... compression=all cluster=n parallel=12
二躬翁、報(bào)錯(cuò)信息
1)expdp日志報(bào)錯(cuò)信息
ORA-31693: Table data object "schema1"."table1" 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 20 with name "_SYSSMU20_3347302954$" too small
......
2)實(shí)例上alter.log報(bào)錯(cuò)信息
Wed Aug 14 21:57:03 2019
ORA-01555 caused by SQL statement below (SQL ID: bcmcxyhhx3ns4, SCN: 0x09eb.de998b9e):
SELECT * FROM RELATIONAL("schema1"."table1")
......
可以看到，alter日志和expdp日志報(bào)錯(cuò)一致盯拱，是同一個(gè)問題盒发。
三、解決思路
ORA-01555問題狡逢，一般是undo表空間不足宁舰，或undo_retention參數(shù)設(shè)置太小，導(dǎo)致導(dǎo)出大表時(shí)奢浑，之前的undo記錄被覆蓋
1)表上沒有LOB字段
1.先檢查undo表空間使用率
#表空間使用率查詢SQL
SQL> SELECT UPPER(F.TABLESPACE_NAME) AS "表空間名",
D.TOT_GROOTTE_MB AS "表空間大小(M)",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES AS "已使用空間(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES AS "空閑空間(M)",
F.MAX_BYTES AS "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
2.檢查undo_retention參數(shù)設(shè)置
SQL> show parameter undo_retention;
3.檢查undo中最長查詢時(shí)間
SQL> select max(maxquerylen) from v$undostat;
小結(jié)：
如果undo表空間使用率已滿蛮艰，擴(kuò)展undo表空間。
根據(jù)undo中最長查詢時(shí)間雀彼，設(shè)置undo_retention參數(shù)值印荔。
2)表上有LOB字段
oracle的lob大字段有自己的retention參數(shù)，如果只調(diào)整undo_retention详羡，而沒有同步到lob大字段仍律，該參數(shù)還是默認(rèn)的900s，所以除了設(shè)置全局retention參數(shù)外实柠，
還應(yīng)設(shè)置lob字段的retention參數(shù)水泉。
1.檢查LOB字段retention
SQL> select table_name,column_name,pctversion,retention from dba_lobs where owner='SCHEMA1' and table_name='TABLE1';
或
SQL> select column_name, pctversion, retention from user_lobs where table_name='TABLE1'
注意:如果表是使用之前的pctversion，要同步修改為retention
2.修改LOB字段retention時(shí)間
SQL>ALTER TABLE schema1.table1 MODIFY LOB(lob_colume)(retention);
四窒盐、解決問題
1)檢查表結(jié)構(gòu)草则，確認(rèn)表上是否有LOB字段
SQL> desc schema1.table1
Name Null? Type
----------------------------------------- -------- ----------------------------
POLICYNO NOT NULL VARCHAR2(30)
ENDORSEQNO NOT NULL VARCHAR2(3)
PLANCODE NOT NULL VARCHAR2(4)
RISKCODE NOT NULL VARCHAR2(4)
CLAUSECODE NOT NULL VARCHAR2(12)
LINENO NOT NULL NUMBER(20)
CLAUSECNAME VARCHAR2(255)
CLAUSECONTEXT VARCHAR2(2000)
TITLEIND NOT NULL VARCHAR2(1)
SERIALNO NOT NULL NUMBER(4,1)
READONLYIND NOT NULL VARCHAR2(1)
DISPLAYNO NUMBER(7,2)
FLAG VARCHAR2(2)
CREATED_BY VARCHAR2(100)
DATE_CREATED NOT NULL DATE
UPDATED_BY VARCHAR2(100)
DATE_UPDATED NOT NULL DATE
可見，表上沒有LOB字段蟹漓。
2)檢查undo表空間使用率
可以看到undo表空間使用率并不高炕横，節(jié)點(diǎn)1的使用率只有2%
UNDOTBS1 32867.98 663.04 2.02% 32204.94 3968
UNDOTBS2 25550 862.06 3.37% 24687.94 3968
3)檢查undo_retention
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
4)檢查檢查undo中最長查詢時(shí)間
SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
11972
11972s約3.5小時(shí)，數(shù)據(jù)庫的undo_retention=900(默認(rèn))葡粒，我們把保留時(shí)間設(shè)置為4小時(shí)(14400s)
5)設(shè)置undo_retention時(shí)間
alter system set undo_retention=14400 sid='*' scope=both;
五份殿、最終效果
expdp導(dǎo)出數(shù)據(jù)正常，報(bào)錯(cuò)消失嗽交。
1)參數(shù)undo_retention時(shí)間
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 14400
2)undo中最長查詢時(shí)間卿嘲，與之前沒有變化
SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
11972
3)undo表空間使用率，相比之前有所增加
UNDOTBS1 32867.98 2103.04 6.40% 30764.94 3968
UNDOTBS2 25550 1822.06 7.13% 23727.94 3968
六夫壁、總結(jié)
分析產(chǎn)生該錯(cuò)誤的主要原因?yàn)榛貪L段設(shè)置太小拾枣，通常在UNDO回滾段中會(huì)保留數(shù)據(jù)庫在某個(gè)時(shí)間點(diǎn)的數(shù)據(jù)，用來保證數(shù)據(jù)的一致性讀。而在用戶利用數(shù)據(jù)泵工具執(zhí)行導(dǎo)出數(shù)據(jù)表操作時(shí)梅肤，又有其它用戶對(duì)該表進(jìn)行了修改司蔬，如果修改提交后UNDO中無足夠空間，之前保存在UNDO中的數(shù)據(jù)資料就會(huì)被覆蓋姨蝴，從而依賴于這些數(shù)據(jù)資料的操作就無法獲得一致性讀俊啼，導(dǎo)致數(shù)據(jù)遷移過程產(chǎn)生以上報(bào)錯(cuò)。而且Oracle由參數(shù)undo_retention指定時(shí)間去釋放UNDO回滾段似扔，所以如果數(shù)據(jù)遷移時(shí)長超過undo_retention指定時(shí)間也會(huì)導(dǎo)致快照過舊的問題產(chǎn)生吨些。
七、知識(shí)點(diǎn)
LOB字段的PCTVERSIOIN與RETENTION
1）老的方式：PCTVERSIOIN
這個(gè)參數(shù)關(guān)系到LOB數(shù)據(jù)的一致讀炒辉，指的是表lob字段所在的表空間需要預(yù)留給lob的前映象使用的最大百分比豪墅，默認(rèn)值是10。也就是說黔寇，只要使用不超過10%偶器，LOB字段的前映像的數(shù)據(jù)是不會(huì)被覆蓋的。
2） 新的方式（自動(dòng)還原段管理使用）：RETENTION
Oracle用UNDO_RETENTION參數(shù)來決定在數(shù)據(jù)庫中保留多少已經(jīng)提交的UNDO數(shù)據(jù)缝裤。這種方式LOB段跟普通段使用相同的過期策略屏轰。
計(jì)算業(yè)務(wù)高峰期每秒產(chǎn)生undo數(shù)據(jù)塊的個(gè)數(shù)
SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;
