ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
SELECT a.tablespace_name as tablespace_name,
to_char(b.total/1024/1024,999999.99) as Total,
to_char((b.total-a.free)/1024/1024,999999.99) as Used,
to_char(a.free/1024/1024,999999.99) as Free,
to_char(round((total-free)/total,4)*100,999.99) as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
AND a.tablespace_name='UNDOTBS1'
ORDER BY a.tablespace_name;
select FILE_NAME from DBA_DATA_FILES where tablespace_name='UNDOTBS1'
alter tablespace UNDOTBS1 add datafile '/data/cache/undotbs04.dbf' size 4g;
下面的sql语句查询undo表空间的使用:----优化出现的sql语句
SELECT e.sql_text,r.name 回滚段名,
s.serial#,s.sid,s.username 用户名,
t.used_ublk*8192/1024/1024||'M' 使用大小,
substr(s.program, 1, 78) 操作程序
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r,sys.v_$sqlarea e
WHERE t.addr = s.taddr and t.xidusn = r.usn and e.address=DECODE(s.sql_hash_value, 0, s.prev_sql_addr, s.sql_address)
order by t.USED_UBLK desc ;
---查看undo表空间使用的表空间下还有多少可以回退的空间,EXPIRED是可以会退的,ACTIVE是正在用的,UNEXPIRED是系统保留的和undo_retention=600有关。。。。。
SELECT tablespace_name, status, SUM (bytes)/1024/1024 "Bytes(M)" FROM dba_undo_extents GROUP BY tablespace_name, status;
undo表空间用于存放undo数据。
当用户运行DML操作时,undo数据被放在undo段,可以回退事物。运行一个事物时,新数据放在数据段中,如果事物存在问题,也可以用undo数据来恢复数据。。
Oracle 的Undo有两种方式: 一是使用undo 表空间,二是使用回滚段.
我们通过 undo_management 参数来控制使用哪种方式,如果设为auto,就使用UNDO 表空间,这时必须要指定一个UNDO 表空间。 如果设为man l,系统启动后使用rollback segment方式存储undo信息。如果系统没有指定undo_management,那么系统默认以man l方式启动,即使设置了auto方式的参数,这些参数将被忽略。
当实例启动的时候,系统自动选择第一个有效的undo表空间或者是rollback segment,如果没有有效的可用的undo表空间或者是回滚段,系统使用system rollback segment。这种情况是不被推荐的,当系统运行在没有undo的情况下,系统会在alert.log中记录一条警告信息。
UNDO_MANAGEMENT
用于指定undo数据的管理方式。如果使用自动管理,必须设置为AUTO;如果手动管理,必须设置为MANUAL。
使用自动管理的时候,oracle会使用UNDO表空间管理UNDO数据;使用手动管理时,oracle会使用回滚段管理UNDO数据。
如果使用自动管理模式,必须建立UNDO表空间,并且配置UNDO_TABLESPACE参数,否则oracle会使用SYSTEM回滚段存放UNDO纪录,并在警告日志中纪录提示。
undo tablespace 表空间的大小由3个参数决定
1,block的大小,一般为8192bytes
2,每分钟产生的undo数据
3,undo_retention的大小,一般为900秒。。意思就是只要undo 表空间允许,undo数据就可以保存900秒。。
ora-30012错误是由于undo表空间导致的。
如果我们undo表空间错误,要我们重新创建,一般我们可以在参数文件中修改这两个参数
undo_management='manual',undo_tablespace='system' 再启动服务就可以重新起来数据库了,
这时要创建undo表空间,用
create undo tablespace undotbs datafile ' e:\oradata\whx\undotbs.dbf' size 100m;这样创建就行了
然后把相应的undo参数改回去undo_management='auto',undo_tablespace='undotbs' 就ok了
最后把数据库起来。。。。。。。
一个数据库只有一个undo表空间,但rac要两个undo表空间,因为一个UNDO不能由多个例程同时使用。。
建立UNDO表空间
1、使用CREATE DATABASE命令建立UNDO表空间
如果使用的是UNDO管理模式,但是没有指定UNDO TABLESPACE,那么建立数据库的时候oracle会自动生成名称为SYS_UNDOTBS的UNDO表空间。
2、使用CREATE UNDO TABLESPACE 命令建立UNDO表空间
建立数据库以后,可以使用该命令建立UNDO表空间。
修改UNDO表空间
当事务用尽UNDO表空间后,增加数据文件:
ALTER TABLESPACE ... ADD DATAFILE;
当UNDO表空间所在磁盘填满时,移动数据文件到其他磁盘:
ALTER TABLESPACE ...RENAME DATAFILE;
当在OPEN状态下移动UNDO表空间时,可以使表空间脱机/联机:
ALTER TABLESPACE ... OFFLINE/ONLINE;
当数据库处于归档模式时,备份UNDO表空间:
ALTER TABLESPACE ... BEGIN BACKUP/END BACKUP
切换UNDO表空间
启动并打开oracle数据库后,同一时刻只能使用一个UNDO表空间。可以切换UNDO表空间:
ALTER SYSTEM SET undo_tablespace=undotbs02;
删除UNDO表空间
当前使用的UNDO表空间不能被删除,如果要删除当前实例使用的UNDO,需要先切换,然后删除:
DROP TABLESPACE undotbs01;
监控UNDO表空间的使用:
1、确定当前正在使用的undo表空间:
show parameter undo_tablespace
Oracle 的Undo有两种方式: 一是使用undo 表空间,二是使用回滚段.
我们通过 undo_management 参数来控制使用哪种方式,如果设为auto,就使用UNDO 表空间,这时必须要指定一个UNDO 表空间。 如果设为man l,系统启动后使用rollback segment方式存储undo信息。如果系统没有指定undo_management,那么系统默认以man l方式启动,即使设置了auto方式的参数,这些参数将被忽略。
当实例启动的时候,系统自动选择第一个有效的undo表空间或者是rollback segment,如果没有有效的可用的undo表空间或者是回滚段,系统使用system rollback segment。这种情况是不被推荐的,当系统运行在没有undo的情况下,系统会在alert.log中记录一条警告信息。
SQL> show parameter undo
NAME TYPE VAL
------------------------------------ ----------- ------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
一. UNDO 表空间
下面来看一下undo 的表空间管理。先来查看一下表空间的使用情况:
/* Formatted on 2010/6/23 9:46:58 (QP5 v5.115.810.9015) */
SELECT a.tablespace_name,
ROUND (a.total_size) "total_size(MB)",
ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
ROUND (b.free_size, 3) "free_size(MB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);
TABLESPACE_NAME total_size(MB) used_size(MB) free_size(MB) FREE_RATE
-------------------- -------------- ------------- ------------- --------------
SYSAUX 580 545.187 34.813 6%
UNDOTBS1 90 23.875 66.125 73.47%
DAVE 20 6.25 13.75 68.75%
USERS 10 8.375 1.625 16.25%
SYSTEM 960 951.062 8.938 93%
从结果我们看到UNDO 表空间已经用了23.875M。 我们看一下这使用的23M空间里空闲和非空闲比例:
/* Formatted on 2010/6/23 9:49:53 (QP5 v5.115.810.9015) */
SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)"
FROM dba_undo_extents
GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS Bytes(M)
-------------------- --------- ----------
UNDOTBS1 UNEXPIRED 9.1875
UNDOTBS1 EXPIRED 13.6875
我们看一下查询的结果,UNEXPIRED 和EXPIRED 是已使用的undo 表空间,其中expired 说明是已经过期的数据,也就是15分钟(默认情况)以外的数据,以被覆盖,可以认为是空闲的。
在此补充一点知识:
采用UNDO 表空间时,会有一个参数UNDO_RETENTION,该参数用来指定undo 记录保存的最长时间,以秒为单位,是个动态参数,完全可以在实例运行时随时修改,通常默认是900 秒,也就是15 分钟。
undo_retention 只是指定undo 数据的过期时间,并不是说,undo 中的数据一定会在undo表空间中保存15 分钟,比如说刚一个新事务开始的时候,如果undo 表空间已经被写满,则新事务的数据会自动覆盖已提交事务的数据,而不管这些数据是否已过期,因此呢,这就又关联回了第一点,当你创建一个自动管理的undo 表空间时,还要注意其空间大小,要尽可能保证undo 表空间有足够的存储空间。
undo_retention 中指定的时间一过,已经提交事务中的数据就立刻无法访问,它只是失效,只要不被别的事务覆盖,它会仍然存在,并可随时被flashback 特性引用。如果你的undo表空间足够大,而数据库又不是那么繁忙,那么其实undo_retention 参数的值并不会影响到你,哪怕你设置成1,只要没有事务去覆盖undo 数据,它就会持续有效。因此呢,这里还是那句话,要注意undo 表空间的大小,保证其有足够的存储空间。
只有在一种情况下,undo 表空间能够确保undo 中的数据在undo_retention 指定时间过期前一定有效,就是为undo 表空间指定Retention G rantee,指定之后,oracle 对于undo 表空间中未过期的undo 数据不会覆盖,例如:
SQL> Alter tablespace undotbs1 retention g rantee;
禁止undo 表空间retention g rantee,例如:
SQL> Alter tablespace undotbs1 retention nog rantee;
总结一下:
UNDO 表空间是会被重用的,只有当事务没结束,或开了retention g rantee,或在undo_retention时间内不能被重用。
在undo_retention规定的时间内,数据都是有效的,过期后都会设为无效,状态被改为Expired,这些回滚段将会被看作Free Space。但是只要数据没有被覆盖就可以使用。如果空间已满,新事务的数据会自动覆盖掉已经提交的事务数据,即使在undo_retention的时间内。除非指定Retention G rantee模式,才能保证在undo_retention内不被覆盖。
二. UNDO 表空间满了的处理方法
2.1 先模拟UNDO 表空间满的情况
SQL> alter system set undo_retention=10800; -- 3个小时
系统已更改。
SQL> create undo tablespace undo datafile 'F:\backup\undo.dbf' size 1m ;
表空间已创建。
SQL> alter tablespace undo retention g rantee;
表空间已更改。
SQL> alter system set undo_tablespace=undo;
系统已更改。
SQL> create table DBA(id number);
表已创建。
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into dba val s(i);
4 commit;
5 end loop;
6 end;
7 /
begin
*
第 1 行出现错误:
ORA-30036: 无法按 8 扩展段 (在还原表空间 'UNDO' 中)
ORA-06512: 在 line 3
2.2 处理方法
处理方法有两种,一是添加undo 表空间的数据文件,二是切换UNDO tablespace. 这种情况下多用在undo 表空间已经非常大的情况。
2.2.1 增加数据文件
SQL> ALTER TABLESPACE undo ADD DATAFILE 'F:\backup\undo02.dbf' size 100M reuse;
表空间已更改。
SQL> begin
2 for i in 1..100000 loop
3 insert into dba val s(1);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
2.2.2 切换UNDO 表空间
1、建立新的表空间UNDOTBS2
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'F:\backup\undo03.dbf' size 100M reuse;
表空间已创建。
2、切换到新建的UNOD表空间上来,操作如下
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
系统已更改。
3、将原来的UNDO表空间,置为脱机:
SQL> alter tablespace UNDO offline;
表空间已更改。
4、删除原来的UNDO表空间:
SQL> drop tablespace UNDO incl ing contents AND DATAFILES CASCADE CONSTRAINTS ;
表空间已删除。
如果只是drop tablespace UNDO ,则只会在删除控制文件里的记录,并不会物理删除文件。
Drop undo表空间的时候必须是在未使用的情况下才能进行。如果undo表空间正在使用(例如事务失败,但是还没有恢复成功),那么drop表空间命令将失败。在drop表空间的时候可以使用incl ing contents。
三. UNDO 表空间损坏的恢复方法
一般Undo 表空间损坏的情况下,数据库都已不能正常打开了。启动时都会报类似如下的错误:
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/d01/oramtest/proddata/undo01.dbf'
要想解决问题,必须重建UNDO 表空间,但是如果不open, 就不能重建创建undo 表空间。 所以可以先用系统默认的undo 表空间:system rollback segment 来启动数据库,再创建UNDO 表空间。
3.1 创建pfile 文件
SQL> create pfile='F:\initorcl.ora' from spfile;
文件已创建。
3.2 修改pfile文件
#*.undo_tablespace='UNDOTBS1'
#*.undo_management='AUTO'
undo_management='MAN L'
rollback_segments='SYSTEM'
3.3 启动数据库至Mount 状态
SQL> STARTUP MOUNT pfile='F:\initorcl.ora' ;
3.4 offline drop undo 表空间
SQL> ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF' OFFLINE DROP;
3.5 open 数据库
SQL> ALTER DATABASE OPEN;
3.6 删除旧的undo 表空间
SQL> DROP TABLESPACE UNDOTBS1 INCL ING CONTENTS;
注:该命令不会删除物理文件。 要想一起删除物理文件需要 AND DATAFILES CASCADE CONSTRAINTS ;
如: drop tablespace UNDOTBS1 incl ing contents AND DATAFILES CASCADE CONSTRAINTS ;
3.7 创建新的UNDO 表空间
SQL> create undo tablespace undotbs1 datafile 'D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF' size 100M ;
create undo tablespace undotbs1 datafile 'D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF' size 100M
*
第 1 行出现错误:
ORA-01119: 创建数据库文件 'D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF'时出错
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在
因为我们之前删除时并没有删除物理文件,所以在建同名文件时就会报错。 我们可以加上REUSE 参数。 只要文件不在使用,就可以重写已经存在的文件。
SQL> create undo tablespace undotbs1 datafile 'D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF' size 100M reuse;
表空间已创建。
3.8 shutdown 数据库 并将pfile 改回
SQL> select name,issys_modifiable from v$parameter where name='undo_management' or name='rollback_segments';
NAME ISSYS_MOD
-------------------- ---------
rollback_segments FALSE
undo_management FALSE
从上面查询的结果,可以知道修改这2个参数必须重启数据,所以还是shutdown 吧。
SQL> shutdown immediate
3.9 修改pfile 参数
*.undo_tablespace='UNDOTBS1'
*.undo_management='AUTO'
#undo_management='MAN L'
#rollback_segments='SYSTEM'
3.10 用刚才修改的pfile 启动数据库,并创建spfile
SQL> startup pfile='F:\initorcl.ora' ;
SQL> create spfile from pfile='F:\initorcl.ora';
3.10 再次shutdown,用spfile 启动.
SQL> shutdown immediate
SQL> startup
一般数据文件损坏的情况也可以采用类似的方法, 先启动到mount, 在将损坏的数据文件offline drop。 在open 数据库,drop 掉损坏的数据文件。 当然这种做法有数据丢失。 能恢复的话,尽量恢复。
分享到:
相关推荐
create undo tablespace undotbs_tablespace datafile '/XXX/xxx/datafile_name1.ora' size 4095M; 增加表空间: 数据表空间: alter tablespace tablespace_name add datafile '/XXX/xxx/datafile_name1.ora' size ...
本ppt较为系统的介绍了Oracle11g的内存与存储管理,主要... --SPECIAL TABLESpaces:Temporary Tablespaces,SYSAUX,Undo Tablespace --Default Table space --BIGFILE TABsPacle 3.Storage Management --OMF --ASM
禁止undo tablespace自动增长 alter database datafile 'full_path\undotbs01.dbf' autoextend off; 2.-- 创建一个新的小空间的undo tablespace create undo tablespace undotBS2 datafile 'full_path\UNDOTBS02....
AUM(自动 undo 管理,Automatic Undo Management)几乎不需要配置。您基本上只需要定义将前映像保持可用的时间量。这是通过参数 UNDO_RETENTION 控制的,以秒为单位定义。因此,值 900 表示 15 分钟。 一定要...
chmod 777 createdb.sql CREATE DATABASE exchange MAXINSTANCES 8 ...undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/exchange/undo01.dbf' size 20m reuse autoextend on next 5M maxsize ...
chmod 777 createdb.sql CREATE DATABASE exchange MAXINSTANCES 8 ...undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/exchange/undo01.dbf' size 20m reuse autoextend on next 5M maxsize ...
undo tablespace UNDOTBS1 datafile 'E:\app\oracle\oradata\mydb\undotbs1.dbf' size 10m character set ZHS16GBK national character set AL16UTF16 7、创建额外表空间 alter user system temporary ...
创建数据库时创建CREATE DATABASE 命令 CREATE DATABASE db01 ...UNDO TABLESPACE undo1 DATAFILE 'undo1db01.dbf' SIZE 20M AUTOEXTEND ON 《oracle 专家精解》后面还有很多的。。。自己需要就来下了哈
一、如果是刚刚删除,那么有两方法: ...undo_tablespace string UNDOTBS1 undo_retention(保持力),10800单位是秒。即3个小时。 修改默认的undo_retention参数设置: 代码如下:ALTER SYSTEM SE
undo_tablespace=undotbs audit_file_dest=$ORACLE_BASE/admin/orcl/adump background_dump_dest=$ORACLE_BASE/admin/orcl/bdump core_dump_dest=$ORACLE_BASE/admin/orcl/cdump user_dump_dest=$ORACLE_BASE/admin/...
回退段说明 查询回退管理方式: SQL>select value from v... where name='undo_tablespace'; 如果使用回退管理,可以查询系统已经创建的回退段: SQL>select segment_name,tablespace_name from dba_rollback_segs;
Oracle9i在UNIX下的安装(Sun Solaris) ... where name='undo_tablespace'; 如果使用回退管理,可以查询系统已经创建的回退段: SQL>select segment_name,tablespace_name from dba_rollback_segs;
环境:OS:Red Hat Enterprise Linux AS release 4 (Nahant)DB:Oracle Database 10g Enterprise Edition...SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG
首先,在网上看到一篇文章,如何Shrink Undo表空间,释放过度占用的空间 ,用上面的指令看了一下,发现我们的硬盘上的UNDO空间也占了2G,所以想到先把这个空间清出来,以解燃眉之急,所以立即进行了测试。...
第十一讲:oracle tablespace管理 第十二讲:oracle table管理 第十三讲:oracle index管理 第十四讲:oracle Partitioned Tables and Indexes管理 第十五讲:oracle view管理 第十六讲:oracle sequences管理 第十...
3.撤销表空间,如undo。用来帮助回退未提交的事务数据 创建表空间 通过create tablespace 命令创建表空间,如: create tablespace test datafile ‘d:\Oracledb\test.dbf’ size 30M 通过上面的sql语句就创建了一个...
##告警172.0.0.1_Oracle数据库读写分离–Oracle_TableSpace:UNDOTBS1使用率超过90,故障已持续0m(IP地址172.0.0.1;告警时间2020.03.11 09:47:53;检查项oracle.check[pre,UNDOTBS1] ;当前状态PROBLEM;值92 %) ...
tablespace_used.txt tabstat.txt temp_used.txt transaction_get.txt undo_used.txt wait_event_block.txt wait_event_hash.txt wait_event_sqlid.txt wait_event.txt wait_session_hash.txt wait_session_sqlid....
DB存储层次结构 (画了个草图,将就看一下…XD) 管理表空间 -system 存放数据字典信息,必须的,创建数据库时第一个创建 -sysaux 10g新,必须的,辅助分担... 表空间信息:DBA_TABLESPACES V$TABLESPACE
InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 2017-11-15 19:23:48 1404 [Note]...