分类: Oracle

Oracle11gR2备份数据expdp报错ORA-01555错误处理

在备份一个蓝凌OA系统数据库时LBPM_PROCESS表时报ORA-01555错误,该表导出失败,报错log如下:

ORA-31693: 表数据对象 "EKP9"."LBPM_PROCESS" 无法加载/卸载并且被跳过, 错误如下:
ORA-02354: 导出/导入数据时出错
ORA-01555: 快照过旧: 回退段号 207 (名称为 "_SYSSMU207_1574257116$") 过小
. . 导出了 "EKP9"."SYS_NOTIFY_QUEUE_ERROR"             20.68 GB 9295722 行
ORA-31693: 表数据对象 "EKP9"."KM_REVIEW_MAIN" 无法加载/卸载并且被跳过, 错误如下:
ORA-02354: 导出/导入数据时出错
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_1197734989$") 过小

原因:SQL语句执行时间太长,或者UNDO表空间过小,或者事务量过大,或者过于频繁的提交,导致执行SQL过程中进行一致性读时,SQL执行后修改的 前镜像(即UNDO数据)在UNDO表空间中已经被覆盖,不能构造一致性读块(CR blocks)。

解决方法如下:
1、调整UNDO表空间相关的参数,查看undo_retention执行时间

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

900秒执行太短,修改为10800 即3小时:

SQL> ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;

System altered.

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS1

2、计算所需undo表空间的大小并调整当前大小:
a、计算业务高峰期每秒产生undo数据块的个数

SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
---------------------------------------------
                                   289.651667

b、查看undo数据块在undo表空间中可以保留的最长时间(当前已经设置为10800即3小时,可根据在实际场景在设置大点)


SQL> show parameter undo_retention;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     10800

c、查看数据块大小:

SQL> show parameter db_blo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192

将以上三者的数据相乘就是所需undo表空间的大小数:

SQL> select (289.651667*10800*8192)/1024/1024/1024 undoTablespace_GB from dual;

UNDOTABLESPACE_GB
-----------------
       23.8665619

或者也可以用如下SQL查询计算UNDO表空间所需空间:

--以下SQL语句用于计算过去7*24小时中UNDO表空间的平均使用量
select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
          from v$undostat),
       (select value as dbs from v$parameter where name = 'db_block_size');
       
--以下SQL语句则按峰值情况计算UNDO表空间所需空间:
select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (undoblks / ((end_time - begin_time) * 86400)) ups
          from v$undostat
         where undoblks in (select max(undoblks) from v$undostat)),
       (select value as dbs from v$parameter where name = 'db_block_size');

此处undo表空间最少需要24GB,太小也会报ORA-01555错误,查看当前表空间使用情况:

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(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 4 DESC;

d、当前系统undo空间只有8G,给增加下表空间(业务高峰期间不要执行,会使CPU飙升)

ALTER TABLESPACE "UNDOTBS1" ADD DATAFILE '/data/app/oracle/oradata/hxdb/undotbs02.dbf' size 30G autoextend on;

e、检查是否启用undo表空间的retention guarantee特性,(保证只有过期(已提交且达到undo_retention设定的值)的数据才会被覆盖,即使undo表空间容量已经不足,也不会覆盖未过期的回退信息)

select tablespace_name,block_size,extent_management , segment_space_management,contents,retention  from dba_tablespaces;

t1.png
当前系统未启用;
启用UNDOTBS1表空间的guarantee特性:

alter tablespace UNDOTBS1 retention guarantee;

再次查看已启用:
t2.png

最后重新expdp,正常导出,问题解决。
参考资料:
https://www.cnblogs.com/lijiaman/p/7617351.html
https://www.cnblogs.com/jyzhao/p/5733761.html

相关文章

发表新评论