ITPub博客

首页 > Linux操作系统 > Linux操作系统 > undo表空间占用磁盘空间满案例处理

undo表空间占用磁盘空间满案例处理

原创 Linux操作系统 作者:tian1982tian 时间:2013-08-03 19:16:31 0 删除 编辑
undo占用磁盘空间使用100%,其内容只增不减,数据库事物无法完成,
数据库奇慢无比,以下是处理方法
[oracle@localhost ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             291G   56G  221G  21% /
/dev/sda1             190M   12M  169M   7% /boot
tmpfs                  16G     0   16G   0% /dev/shm
/dev/sda5              19G  773M   17G   5% /oraredo
/dev/sda6              19G   18G     0 100% /oraundo
/dev/sda7              56G   42G   11G  80% /oracle
/dev/sda8             459G   24G  412G   6% /oradatab
/dev/sda9             926G  159G  720G  19% /oradataa
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 29 20:28:20 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
  >conn / as sysdba
Connected.
SYS AS SYSDBA >CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/oradataa/undotbs02.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;
Tablespace created.
SYS AS SYSDBA >show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace        string      UNDOTBS1
SYS AS SYSDBA >alter system set undo_tablespace='UNDOTBS' scope=both;
System altered.
SYS AS SYSDBA >show parameter undo;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace        string      UNDOTBS2
SYS AS SYSDBA >select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------------
SYSTEM                         SYSTEM                         ONLINE
UNDOTBS1                       _SYSSMU12$                     ONLINE
UNDOTBS1                       _SYSSMU13$                     OFFLINE
UNDOTBS1                       _SYSSMU14$                     OFFLINE
UNDOTBS1                       _SYSSMU15$                     OFFLINE
UNDOTBS1                       _SYSSMU16$                     OFFLINE
UNDOTBS1                       _SYSSMU17$                     OFFLINE
UNDOTBS1                       _SYSSMU18$                     OFFLINE
UNDOTBS1                       _SYSSMU19$                     OFFLINE
UNDOTBS1                       _SYSSMU20$                     OFFLINE
UNDOTBS1                       _SYSSMU21$                     OFFLINE
TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------------
UNDOTBS1                       _SYSSMU22$                     OFFLINE
UNDOTBS1                       _SYSSMU23$                     OFFLINE
UNDOTBS1                       _SYSSMU24$                     OFFLINE
UNDOTBS1                       _SYSSMU25$                     OFFLINE
UNDOTBS1                       _SYSSMU26$                     OFFLINE
UNDOTBS1                       _SYSSMU27$                     OFFLINE
UNDOTBS1                       _SYSSMU28$                     OFFLINE
UNDOTBS1                       _SYSSMU29$                     OFFLINE
UNDOTBS1                       _SYSSMU30$                     OFFLINE
UNDOTBS1                       _SYSSMU31$                     OFFLINE
UNDOTBS1                       _SYSSMU32$                     OFFLINE
TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------------
UNDOTBS1                       _SYSSMU33$                     OFFLINE
UNDOTBS2                       _SYSSMU34$                     ONLINE
UNDOTBS2                       _SYSSMU35$                     ONLINE
UNDOTBS2                       _SYSSMU36$                     ONLINE
UNDOTBS2                       _SYSSMU37$                     ONLINE
UNDOTBS2                       _SYSSMU38$                     ONLINE
UNDOTBS2                       _SYSSMU39$                     ONLINE
UNDOTBS2                       _SYSSMU40$                     ONLINE
UNDOTBS2                       _SYSSMU41$                     ONLINE
UNDOTBS2                       _SYSSMU42$                     ONLINE
UNDOTBS2                       _SYSSMU43$                     ONLINE
TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------------
UNDOTBS2                       _SYSSMU44$                     ONLINE
UNDOTBS2                       _SYSSMU45$                     ONLINE
UNDOTBS2                       _SYSSMU46$                     ONLINE
UNDOTBS2                       _SYSSMU47$                     ONLINE
UNDOTBS2                       _SYSSMU48$                     ONLINE
UNDOTBS2                       _SYSSMU49$                     ONLINE
UNDOTBS2                       _SYSSMU50$                     ONLINE
UNDOTBS2                       _SYSSMU51$                     ONLINE
UNDOTBS2                       _SYSSMU52$                     ONLINE
UNDOTBS2                       _SYSSMU53$                     ONLINE
UNDOTBS2                       _SYSSMU54$                     ONLINE
TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------------
UNDOTBS2                       _SYSSMU55$                     ONLINE
UNDOTBS2                       _SYSSMU56$                     ONLINE
UNDOTBS2                       _SYSSMU57$                     ONLINE
UNDOTBS2                       _SYSSMU58$                     ONLINE
48 rows selected.
SYS AS SYSDBA >
如上:undotbs1表空间的segment_name: _SYSSMU12$还处于online状态,再查多次
SYS AS SYSDBA >select tablespace_name,segment_name,status from dba_rollback_segs;
同时:
[oracle@localhost ~]$ ps -ef | grep ora_
oracle     713 24762  0 20:27 pts/5    00:00:00 grep ora_
oracle   24180     1  0 19:36 ?        00:00:00 ora_pmon_ora10g
oracle   24182     1  0 19:36 ?        00:00:00 ora_psp0_ora10g
oracle   24184     1  0 19:36 ?        00:00:00 ora_mman_ora10g
oracle   24186     1  1 19:36 ?        00:00:33 ora_dbw0_ora10g
oracle   24188     1  1 19:36 ?        00:00:32 ora_dbw1_ora10g
oracle   24190     1  0 19:36 ?        00:00:07 ora_lgwr_ora10g
oracle   24192     1  0 19:36 ?        00:00:00 ora_ckpt_ora10g
oracle   24194     1  1 19:36 ?        00:00:50 ora_smon_ora10g
oracle   24196     1  0 19:36 ?        00:00:00 ora_reco_ora10g
oracle   24198     1  0 19:36 ?        00:00:00 ora_cjq0_ora10g
oracle   24200     1  0 19:36 ?        00:00:00 ora_mmon_ora10g
oracle   24202     1  0 19:36 ?        00:00:00 ora_mmnl_ora10g
oracle   24215     1  0 19:36 ?        00:00:18 ora_p000_ora10g
oracle   24217     1  0 19:36 ?        00:00:08 ora_p001_ora10g
oracle   24219     1  0 19:36 ?        00:00:04 ora_p002_ora10g
oracle   24221     1  0 19:36 ?        00:00:03 ora_p003_ora10g
oracle   24223     1  0 19:36 ?        00:00:09 ora_p004_ora10g
oracle   24225     1  0 19:36 ?        00:00:04 ora_p005_ora10g
oracle   24227     1  0 19:36 ?        00:00:05 ora_p006_ora10g
oracle   24229     1  0 19:36 ?        00:00:04 ora_p007_ora10g
oracle   24231     1  0 19:36 ?        00:00:09 ora_p008_ora10g
oracle   24233     1  0 19:36 ?        00:00:08 ora_p009_ora10g
oracle   24235     1  0 19:36 ?        00:00:04 ora_p010_ora10g
oracle   24237     1  0 19:36 ?        00:00:04 ora_p011_ora10g
oracle   24239     1  0 19:36 ?        00:00:07 ora_p012_ora10g
oracle   24241     1  0 19:36 ?        00:00:03 ora_p013_ora10g
oracle   24243     1  0 19:36 ?        00:00:06 ora_p014_ora10g
oracle   24404     1  0 19:38 ?        00:00:06 ora_p015_ora10g
oracle   24406     1  0 19:38 ?        00:00:08 ora_p016_ora10g
oracle   24408     1  0 19:38 ?        00:00:02 ora_p017_ora10g
oracle   24410     1  0 19:38 ?        00:00:04 ora_p018_ora10g
oracle   24412     1  0 19:38 ?        00:00:07 ora_p019_ora10g
oracle   24414     1  0 19:38 ?        00:00:09 ora_p020_ora10g
oracle   24416     1  0 19:38 ?        00:00:04 ora_p021_ora10g
oracle   24418     1  0 19:38 ?        00:00:03 ora_p022_ora10g
oracle   24420     1  0 19:38 ?        00:00:05 ora_p023_ora10g
oracle   24422     1  0 19:38 ?        00:00:07 ora_p024_ora10g
oracle   24426     1  0 19:38 ?        00:00:05 ora_p025_ora10g
oracle   24428     1  0 19:38 ?        00:00:04 ora_p026_ora10g
oracle   24430     1  0 19:38 ?        00:00:07 ora_p027_ora10g
oracle   24432     1  0 19:38 ?        00:00:00 ora_qmnc_ora10g
oracle   24434     1  0 19:38 ?        00:00:04 ora_p028_ora10g
oracle   24436     1  0 19:38 ?        00:00:04 ora_p029_ora10g
oracle   24438     1  0 19:38 ?        00:00:07 ora_p030_ora10g
oracle   24440     1  0 19:38 ?        00:00:11 ora_p031_ora10g
oracle   24466     1  0 19:38 ?        00:00:00 ora_q000_ora10g
oracle   24468     1  0 19:38 ?        00:00:00 ora_q001_ora10g
[oracle@localhost ~]$
可以看到oracle很多并行进程,到数据库查看
 select * from v$process where spid in('24412','24414','24416','24418')
 select * from v$session where paddr in('00000000BDBBC820','00000000BDBBD7F0','00000000BDBBD008','00000000BDBBDFD8')
 select * from v$sqlarea where sql_id='4gd6b1r53yt88'
其中查到backgroud进程在recover恢复数据库
Deq: Txn Recovery
证明undo里面还有前滚重做redo所需要的undo回滚数据,所以UNDOTBS1的_SYSSMU12$ 段一直处于online状态,经过多次查询,半数小时过后
UNDOTBS1的_SYSSMU12$ 段终于处于offline状态,此时可以删除表空间undotbs1及其磁盘文件,oracle的并行backgroud恢复进程也消失了
[oracle@localhost ~]$ ps -ef | grep ora_
oracle    3086 24762  0 20:40 pts/5    00:00:00 grep ora_
oracle   24180     1  0 19:36 ?        00:00:00 ora_pmon_ora10g
oracle   24182     1  0 19:36 ?        00:00:00 ora_psp0_ora10g
oracle   24184     1  0 19:36 ?        00:00:00 ora_mman_ora10g
oracle   24186     1  0 19:36 ?        00:00:35 ora_dbw0_ora10g
oracle   24188     1  0 19:36 ?        00:00:33 ora_dbw1_ora10g
oracle   24190     1  0 19:36 ?        00:00:08 ora_lgwr_ora10g
oracle   24192     1  0 19:36 ?        00:00:00 ora_ckpt_ora10g
oracle   24194     1  1 19:36 ?        00:00:50 ora_smon_ora10g
oracle   24196     1  0 19:36 ?        00:00:00 ora_reco_ora10g
oracle   24198     1  0 19:36 ?        00:00:00 ora_cjq0_ora10g
oracle   24200     1  0 19:36 ?        00:00:00 ora_mmon_ora10g
oracle   24202     1  0 19:36 ?        00:00:00 ora_mmnl_ora10g
oracle   24432     1  0 19:38 ?        00:00:00 ora_qmnc_ora10g
oracle   24466     1  0 19:38 ?        00:00:00 ora_q000_ora10g
oracle   24468     1  0 19:38 ?        00:00:00 ora_q001_ora10g
[oracle@localhost ~]$
SYS AS SYSDBA >drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS AS SYSDBA >
[oracle@localhost ~]$ ll /oraundo
total 16
drwx------ 2 oracle oinstall 16384 Mar 14 11:00 lost+found
[oracle@localhost ~]$
磁盘上的undotbs01文件也已删除掉
在等待UNDOTBS1的_SYSSMU12$ 段offline的过程中,做了一个测试
begin
 for i in 1..10000000 loop
  insert into test values(i);
 end loop;
end;
/
注意用toad观察undo表空间undotbs1和undotbs2结果,undotbs2的空间使用率一直在增长,且undotbs1
的空间没见增长,可以断定,undo表空间改变已经生效,且新的所有事物undo数据都写进了undotbs2上
[oracle@localhost oraundo]$ ll /oraundo/
total 16
drwx------ 2 oracle oinstall 16384 Mar 14 11:00 lost+found
[oracle@localhost oraundo]$
[root@localhost oraundo]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             291G   56G  221G  21% /
/dev/sda1             190M   12M  169M   7% /boot
tmpfs                  16G     0   16G   0% /dev/shm
/dev/sda5              19G  773M   17G   5% /oraredo
/dev/sda6              19G   18G     0 100% /oraundo
/dev/sda7              56G   42G   11G  80% /oracle
/dev/sda8             459G   24G  412G   6% /oradatab
/dev/sda9             926G  159G  720G  19% /oradataa
[root@localhost oraundo]# umount /dev/sda6
umount: /oraundo: device is busy
umount: /oraundo: device is busy
[root@localhost oraundo]# umount /dev/sda6 /oraundo
umount: /oraundo: device is busy
umount: /oraundo: device is busy
umount: /oraundo: device is busy
umount: /oraundo: device is busy
不知什么原因,/oraundo下已无文件,但其使用情况依然是100%
重启数据库:
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 29 20:55:15 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
  >conn / as sysdba
Connected to an idle instance.
SYS AS SYSDBA >startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             352322136 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SYS AS SYSDBA >
[root@localhost oraundo]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             291G   56G  221G  21% /
/dev/sda1             190M   12M  169M   7% /boot
tmpfs                  16G     0   16G   0% /dev/shm
/dev/sda5              19G  773M   17G   5% /oraredo
/dev/sda6              19G  173M   18G   1% /oraundo
/dev/sda7              56G   42G   11G  80% /oracle
/dev/sda8             459G   24G  412G   6% /oradatab
/dev/sda9             926G  159G  720G  19% /oradataa
[root@localhost oraundo]#
磁盘空间显示正常
 
 
 
 
 
 
 
 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16400082/viewspace-767727/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle 12c 发布
下一篇: 账号被封!!!
请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    163750