oracle undo ora-30036 问题待处理

Dav_2099

已于 2024-07-03 08:20:50 修改

阅读量1.3k

点赞数 30

文章标签： oracle 数据库

于 2024-07-02 23:05:02 首次发布

本文链接：https://blog.csdn.net/Dav_2099/article/details/140138077

版权

impdp ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

1.在执行impdp 导入一个表的时候报错了，提示 ora-30036 unable to extend 8 in undotbs1 ;

这个表大小查询是6.23G，count(*) is 53760000 条；

非分区表；

2.分析原因

在前20分钟导数正常，已经完成了98%的导入，后续就一直卡在98%；

检查占用undo的 sid sql_id

select s.sid,
s.serial#,
s.sql_id,
s.USERNAME,
v.usn,
segment_name,
r.status,
v.rssize / 1024 / 1024 MB
from dba_rollback_segs r, v$rollstat v, v$transaction t, v$session s
where r.segment_id = v.usn
and v.usn = t.xidusn
and t.addr = s.taddr
order by segment_name;

select tablespace_name,sum(bytes)/1024/1024 mb from dba_data_files where tablespace_name='UNDOTBS1' group by tablespace_name;
select tablespace_name,contents from dba_tablespaces where tablespace_name='UNDOTBS1';

select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS1';

select tablespace_name,sum(bytes)/1024/1024 mb from dba_data_files where tablespace_name='UNDOTBS8' group by tablespace_name;
查看undo的使用率：
SELECT UPPER(F.TABLESPACE_NAME) tablespace_name ,D.TOT_GROOTTE_GB ,
　　D.TOT_GROOTTE_GB - F.TOTAL_BYTES used_totalG,
　　F.TOTAL_BYTES free_GB,
　　TO_CHAR(ROUND( (D.TOT_GROOTTE_GB - F.TOTAL_BYTES) / D.TOT_GROOTTE_GB*100 ,2),'990.99')
|| '%' used_percent
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024*1024*1024), 2) TOTAL_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　ROUND(SUM(DD.BYTES) / (1024*1024*1024), 2) TOT_GROOTTE_GB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME and d.tablespace_name='UNDOTBS2'
　　ORDER BY used_percent desc;

当前undo表空间使用状态:
SQL> SELECT DISTINCT STATUS,SUM(BYTES)/1024/1024/1024,COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

SELECT DISTINCT STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

SQL>
SQL> SELECT DISTINCT STATUS,SUM(BYTES)/1024/1014/1024 ,COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

STATUS SUM(BYTES)/1024/1014/1024 COUNT(*)
--------- ------------------------- ----------
ACTIVE .064102564 4
EXPIRED 85.6955128 38686
UNEXPIRED 41.5334689 7915

select u.owner,u.segment_name,status,SUM(BYTES)/1024/1014/1024 from DBA_UNDO_EXTENTS u group by u.owner,u.segment_name,status order by 4 desc;

可用看到当前undo的使用情况；

查看活动事务v$transaction
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR=B.SES_ADDR;

SELECT XID AS "txn_id", XIDUSN AS "undo_seg", USED_UBLK "used_undo_blocks",XIDSLOT AS "slot", XIDSQN AS "seq", STATUS AS "txn_status" FROM V$TRANSACTION;

select s.sid,substr(s.program,1,15) program,s.machine,t.xidusn||','||t.xidslot||'.'||t.xidsqn tx_addr,
t.status,t.start_time,tbs.tablespace_name tbs_name,round(t.used_ublk*tbs.block_size/1048576,2) undo_size_mb,t.used_urec from v$transaction t,v$session s,v$parameter p,dba_tablespaces tbs
where t.ses_addr=s.saddr and p.name='undo_tablespace';

确认占用undo的sql

SELECT S.SID,
S.USERNAME,
U.NAME,
Q.SQL_TEXT,
Q.HASH_VALUE,
T.UBABLK
FROM V$TRANSACTION T,
V$ROLLSTAT R,
V$ROLLNAME U,
V$SESSION S,
V$SQL Q
WHERE S.TADDR = T.ADDR
AND T.XIDUSN = R.USN
AND R.USN = U.USN
AND Q.HASH_VALUE =
DECODE(S.SQL_HASH_VALUE,
NULL, S.PREV_HASH_VALUE,
S.SQL_HASH_VALUE)
ORDER BY S.USERNAME;

--查询占用undo资源的SQL
set pagesize 999 linesize 120
col machine for a16
col program for a20
col status for a10
col sql_id for a16
col sql_text for a20
select distinct s.machine,s.program,s.sid,round(t.used_ublk*8/1024,2) undo_MB,used_urec undo_records,s.status,l.sql_text
from v$transaction t,gv$session s,v$sqlstats l
where t.ses_addr=s.saddr
and s.sql_id=l.sql_id(+)order by undo_MB;

---查询回滚需要多久
select usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-
undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;

后续回收或释放undo；
1、创建新的原来的UNDO表空间
可以在其它的磁盘空间临时创建还原表空间
SQL>create undo tablespace undotbs1 datafile '+DATA/orcl/datafile/undotbs1' size 10M autoextend on maxsize 3G;

2、切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs1 scope=both;

3、验证当前数据库的还原表空间
SQL> show parameter undo

4、等待原UNDO表空间所有UNDO SEGMENT OFFLINE
select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;

5、删除UNDO2表空间
SQL> drop tablespace undotbs2 including contents and datafiles;

产生问题的原因主要以下两点：
1. 有较大的事务量让Oracle Undo自动扩展，产生过度占用磁盘空间的情况；
2. 有较大事务没有收缩或者没有提交所导制；
说明：本问题在ORACLE系统管理中属于比较正常的一现象，日常维护多注意对磁盘空间的监控。

后续设置undo的表空间管理：
alter system set undo_management=auto scope=spfile;

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 250m autoextend on ;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs1 scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> show parameter offline

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_offline_rollback_segments string TRUE
SQL> alter system reset "_offline_rollback_segments" scope=spfile sid='*';

System altered.

SQL> shutdown immediate;

4.查看UNDO表空间上回滚段得状态
select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
5.更改回滚段得状态：ALTER ROLLBACK SEGMENT rollback_segment OFFLINE;（select * from v$rollname）或者查询SELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;
回收会滚段：ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 2M（不知道大小就写TO integer）;
删除回滚段：DROP ROLLBACK SEGMENT rollback_segment ----对应的数据字典DBA_ROLLBACK_SEGS

检查undo的变动
select vs.name,ms.value from v$mystat ms,v$sysstat vs where ms.statistic#=vs.statistic# and name='undo change vector size';

查询历史undo是否还有事务(包含回滚事务)
SELECT a.tablespace_name,a.segment_name,b.ktuxesta,b.ktuxecfl,
b.ktuxeusn||'.'||b.ktuxeslt||'.'||b.ktuxesqn trans
FROM dba_rollback_segs a, x$ktuxe b
WHERE a.segment_id = b.ktuxeusn
AND a.tablespace_name = UPPER('&tsname')
AND b.ktuxesta <> 'INACTIVE';
--因为有undo_retention参数,所以不能简单的通过确定该sql无事务就可以删除原undo

切换undo表空间(无论是否有事务,均可以切换[最好是无事务时切换],但是不能直接删除原undo表空间)
alter system set undo_tablespace='undo_x';

官方的解决方案

Run Out Of Space On UNDO Tablespace Using DataPump Import/Export (文档 ID 735366.1)

SOLUTION

Unlike the traditional Export and Import utilities, which used the BUFFER, COMMIT, COMPRESS, CONSISTENT, DIRECT and RECORDLENGTH parameters, DataPump needs no tuning to achieve maximum performance.

DataPump chooses the best method to ensure that data and metadata are exported and imported in the most efficient manner. Initialization parameters should be sufficient upon installation.

However, you can receive the error:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

during the Import (impdp) if indexes are present in some cases.

Impdp maintains indexes during import by default and does not use direct_path if tables and indexes are already created. However, if there is no index to enforce constraints and you specify:

ACCESS_METHOD=DIRECT_PATH

with the DataPump import command line, DataPump can use direct path method to do the import.

To get around potential issues with the UNDO tablespace in this case:

- load data by direct path by disabling primary key constraint (using ALTER TABLE ... MODIFY CONSTRAINT ... DISABLE NOVALIDATE) and using access_method=direct_path.
- after loading data, enable primary key constraint (using ALTER TABLE ... MODIFY CONSTRAINT ... ENABLE VALIDATE)

2.Error ORA-30036 DataPump Import (IMPDP) Exhausts Undo Tablespace (文档 ID 727894.1)

MPTOMS

The import DataPump session completes with the following errors:

ORA-31693: Table data object "[schema]"."[table-name]" failed to load/unload and is being skipped due to error:
ORA-30032: the suspended (resumable) statement has timed out
ORA-30036: unable to extend segment by 8 in undo tablespace '<UNDO_TABLESPACE>'
Job "[user]"."SYS_IMPORT_TABLE_01" completed with 141 error(s) at 01:15:34

This indicates that ROLLBACK was being performed during the time in which no progress was made. It appears there is excessive UNDO being generated.

CHANGES

CAUSE

Excess undo generation can occur when there is a Primary Key (PK) constraint present on the system. Import DataPump will perform index maintenance and this can increase undo usage especially if there is other DML occurring on the database.

SOLUTION

Disable constraints for Primary Keys (PK) on the database during import datapump load. This will reduce undo as index maintenance will not be performed.

REFERENCES

NOTE:1670349.1 - Import DataPump - How To Limit The Amount Of UNDO Generation of an IMPDP job ?
NOTE:735366.1 - Run Out Of Space On UNDO Tablespace Using DataPump Import/Export

以下通过：

1、impdp先导入数据(如果有index和约束的先drop)

1）CONTENT=METADATA_ONLY
TABLE_EXISTS_ACTION=REPLACE
EXCLUDE=CONSTRAINT
EXCLUDE=INDEX

2）CONTENT=DATA_ONLY

2、再创建索引，约束等

exclude和include参数能够在使用expdp或impdp是对特定的对象或对象类型进行筛选或过滤。比如因工作的需要导出特定的表或不导出特定
的表、视图以及存储过程、索引、约束、授权统计信息等等。下面将给出expdp或impdp使用exclude和include参数的方法和示例。

一、exclude/include参数用法：

EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] -->排出特定对象

INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause] -->包含特定对象

   object_type子句用于指定对象的类型，如table,sequence,view,procedure,package等等
   name_clause子句可以为SQL表达式用于过滤特定的对象名字。它由SQL操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象。
   当未指定name_clause而仅仅指定object_type则所有该类型的对象都将被过滤或筛选。多个[object_type]:[name_clause]中间以逗号分割。

示例：
expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')"

impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'"

二、常用的过滤SQL表达式

    EXCLUDE=SEQUENCE,VIEW                          --过滤所有的SEQUENCE,VIEW

   EXCLUDE=TABLE:"IN ('EMP','DEPT')"               --过滤表对象EMP,DEPT

    EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT

   EXCLUDE=INDEX:"= 'INDX_NAME'"                   --过滤指定的索引对象INDX_NAME

    INCLUDE=PROCEDURE:"LIKE 'PROC_U%'"              --包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符)

    INCLUDE=TABLE:"> 'E' "                          --包含大于字符E的所有表对象

    其它常用操作符 NOT IN, NOT LIKE, <, != 等等
  直接将过滤操作符封装到参数文件中，如下面的例子
  Parameter file:exp_scott.par

   DIRECTORY = dump_scott
   DUMPFILE = exp_scott_%U.dmp
   LOGFILE = exp_scott.log
    SCHEMAS = scott
    PARALLEL= 2
    EXCLUDE = TABLE:"IN ('EMP', 'DEPT')"

    expdp system/manager parfile=exp.par
==>QQ: 240549084

三、命令行下转义符的处理

Windows平台:

    D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
    INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"

   在Windows平台下，需要对象双引号进行转义，使用转义符\

Unix平台:
  在未使用parfile文件的情形下，所有的符号都需要进行转义，包括括号，双引号，单引号等
    % expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
    INCLUDE=TABLE:\"IN \'EMP\',\'DEP\'\'𝐸𝑀𝑃\',\'𝐷𝐸𝑃\'\"

四、exclude/include常见的错误

  任意需要转义的字符如果未转义或转义错误，都会产生ORA错误。下面给出几种常见的ORA错误。

     ORA-39001: invalid argument value
     ORA-39071: Value for INCLUDE is badly formed.
     ORA-00936: missing expression

     ORA-39001: invalid argument value
    ORA-39071: Value for EXCLUDE is badly formed.
     ORA-00904: “DEPT”: invalid identifier

ORA-39001: invalid argument value
ORA-39041: Filter “INCLUDE” either identifies all object types or no object types.

ORA-39001: invalid argument value
ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types

ORA-39001: invalid argument value
ORA-39038: Object path “USER” is not supported for TABLE jobs.

导出示例
  -->导出不包含sequence，表EMP，DEPT的其它所有对象
    [oracle@orasrv scott]$ expdp scott/tiger directory=dump_scott dumpfile=fliter_1.dmp log=fliter_1.log schemas=scott \
   > exclude=sequence,table:\" in \'EMP\',\'DEPT\'\'𝐸𝑀𝑃\',\'𝐷𝐸𝑃𝑇\'\"

-->包含大于字符T的所有表对象
[oracle@orasrv ~]$ expdp scott/tiger directory=dump_scott dumpfile=tmp.dmp logfile=tmp.log include=table:\"\>\'T\'\"

仅仅导入表对象TT，如果存在则将其替换
[oracle@orasrv scott]$ impdp scott/tiger directory=dump_scott dumpfile=tmp.dmp logfile=tmp_imp.log \
> include=table:\" =\'TT\' \" table_exists_action=replace