参考文献
-
- ORA-30013: Undo Tablespace ‘UNDOTBS1’ Is Currently In Use
一、背景知识
- Oracle数据库逻辑结构从小至大依次由块(block)、区(extent)、段(segment)、表空间(tablespace)、数据库(database)构成。
- undo表空间下，每个段区的数量最大为32765，当有大事务出现时，会产生大量的undo，导致回滚段区的数量超出最大限制，产生ORA-01628报错。
二、解决方案
- 查询段下区数超数undo表空间及相应的段名，此处检测阈值为25600
查询一：
SELECT *
FROM (SELECT tablespace_name,
segment_name,
Count(*) cnt
FROM dba_undo_extents
GROUP BY tablespace_name,
segment_name
ORDER BY cnt DESC)
WHERE cnt > 25600;
查询二：
SELECT s.tablespace_name,
s.segment_name,
v.EXTENTS
FROM gv$rollstat v,
dba_rollback_segs s
WHERE v.USN = s.segment_id(+)
AND v.extents > 25600;
- 查询待替换undo表空间数据文件信息
查询语句：
WITH A
AS (SELECT tablespace_name,
file_name,
bytes
FROM dba_data_files
WHERE tablespace_name = '&tbs')
SELECT B.*,
A.file_name
FROM A,
(SELECT tablespace_name,
Sum(bytes)
FROM A
GROUP BY tablespace_name) B
WHERE A.tablespace_name = B.tablespace_name;
- 查询各个实例所用undo表空间
查询memory中的信息：
col value FOR a40
col name FOR a20
SELECT gi.inst_id,
gi.instance_name,
gp.name,
gp.value
FROM gv$parameter gp,
gv$instance gi
WHERE gp.name = 'undo_tablespace'
AND gp.inst_id = gi.inst_id;
查询spfile中的信息：
col value FOR a40
col name FOR a20
SELECT gi.inst_id,
gi.instance_name,
gp.name,
gp.value
FROM gv$spparameter gp,
gv$instance gi
WHERE gp.name = 'undo_tablespace'
AND gp.inst_id = gi.inst_id;
- 创建总大小相当的单个文件大小16g的新undo表空间
创建新的undo表空间命令
create undo tablespace newundotbs datafile '+data01' size 16g;
为新的表空间增加文件命令
alter tablespace newundotbs add datafile '+data01' size 16g;
- 切换至新的undo表空间
alter system set undo_tablespace='newundotbs' sid='&sid';
- 删除旧的undo表空间
drop tablespace oldundotbs including contents and datafiles;
三、自动修复代码
# _*_ coding='utf-8' _*_
# __author__="Liaojing"
import os
# string for query the undotbs extents exceeded 25600
query_undotbs_old='''
SELECT DISTINCT tablespace_name
FROM (SELECT tablespace_name,
Count(segment_name) cnt
FROM dba_undo_extents
GROUP BY tablespace_name,
segment_name)
WHERE cnt > 25600
ORDER BY tablespace_name;
'''
# string for query the number of undofile
query_number_of_undofile = '''
col tablespace_name for a20
WITH A
AS (SELECT tablespace_name,
file_name,
bytes
FROM dba_data_files
WHERE tablespace_name IN (SELECT DISTINCT tablespace_name
FROM dba_undo_extents))
SELECT DISTINCT B.*
FROM A,
(SELECT tablespace_name,
Ceil(Sum(bytes) / 1024 / 1024 / 1024 / 16)
FROM A
GROUP BY tablespace_name) B
WHERE A.tablespace_name = B.tablespace_name
ORDER BY B.tablespace_name;
'''
# string for query the undotbsname and sid
query_undotbsname_sid = '''
col instance_name for a20
col value for a20
SELECT gp.value,
gi.instance_name
FROM gv\$parameter gp,
gv\$instance gi
WHERE gp.name = 'undo_tablespace'
AND gp.inst_id = gi.inst_id
ORDER BY instance_name;
'''
# get expired undotbs and new undotbs
def get_expired_new_undotbs(undotbs_d_cnt, undotbs_d_sid, undotbs_old):
print("Get expired undotbs and new undotbs...")
undotbs_expired = set(undotbs_old) - set(undotbs_d_sid)
undotbs_new = set(undotbs_d_cnt) - set(undotbs_d_sid) - undotbs_expired
print("Expired undotbs: " + ','.join(undotbs_expired))
print("New undotbs: " + ','.join(undotbs_new))
return undotbs_expired, undotbs_new
# using a sql string to create a sqlplus string
def sqlplus_string(sql, feedback = False):
sqlplus_string="sqlplus -s / as sysdba<<EOF\n"
if not feedback:
sqlplus_string += "set heading off feedback off linesize 2048 pages 0\n"
return sqlplus_string + sql + "\nEOF\n"
# execute sqlplus sql and return the execution result
def exec_func(sql_string, feedback=False):
p = os.popen(sqlplus_string(sql_string, feedback = feedback))
return p.read().strip()
#get current instance undotbs
def get_current_undotbs():
print("Get current undotbs...")
exec_result = exec_func("show parameter undo_tablespace")
print(exec_result)
return exec_result.split()[-1]
# get the undotbs extents exceeded 25600
def get_undotbs_old(current_undotbs):
print("Get the undotbs extents exceeded 25600...")
exec_result = exec_func(query_undotbs_old).split()
if not exec_result or current_undotbs not in exec_result:
print("The extents of current undotbs are not exceeded 25600!")
exit()
print('\n'.join(exec_result))
return exec_result
# get the number of undofile
def get_undotbs_d_cnt():
print("Get the number of undofile...")
exec_result = exec_func(query_number_of_undofile)
print(exec_result)
return dict(x.strip().split() for x in exec_result.split('\n'))
# get the undotbsname and sid
def get_undotbs_d_sid():
print("Get the undotbsname and sid...")
exec_result = exec_func(query_undotbsname_sid)
print(exec_result)
return dict(x.strip().split() for x in exec_result.split('\n'))
# set undo in spfile
def set_undotbs_in_spfile(undotbs_d_sid):
print("Set undo in spfile...")
set_undotbs_string = ''
for (undotbs, sid) in undotbs_d_sid.items():
set_undotbs_string += "alter system set undo_tablespace='" + undotbs + "' sid='" + sid + "\';\n"
print(set_undotbs_string)
exec_result = exec_func(set_undotbs_string, feedback=True)
if exec_result.count('System altered.') == len(undotbs_d_sid):
print("Set undo in spfile succeed!")
else:
print("Set undo in spfile failed!")
exit()
# drop expired undotbs
def drop_expired_undotbs(undotbs_expired):
print("Drop expired undotbs...")
drop_undotbs_expired = ''
if not undotbs_expired:
print("There's no expired undotbs to drop!")
return None
for name in undotbs_expired:
drop_undotbs_expired += "drop tablespace " + name +" including contents and datafiles;\n"
print(drop_undotbs_expired)
exec_result = exec_func(drop_undotbs_expired, feedback=True)
if exec_result.count("Tablespace dropped.") == len(undotbs_expired):
print("All expired undotbs dropped succeed!")
else:
print("Not all expired undotbs dropped!")
# create new undotbs
def create_new_undotbs(undotbs_new, current_undotbs, undotbs_d_cnt):
print("create new undotbs...")
create_new_undotbs_string = ''
if undotbs_new not in undotbs_d_cnt:
create_new_undotbs_string += "create undo tablespace " + undotbs_new + " datafile '+data01' size 16g;\n"
undotbs_d_cnt[undotbs_new] = '1'
for i in range(int(undotbs_d_cnt[current_undotbs]) - int(undotbs_d_cnt[undotbs_new])):
create_new_undotbs_string += "alter tablespace " + undotbs_new + " add datafile '+data01' size 16g;\n"
print(create_new_undotbs_string)
exec_result = exec_func(create_new_undotbs_string, feedback=True)
if exec_result.count("Tablespace altered.") == (int(undotbs_d_cnt[current_undotbs]) - int(undotbs_d_cnt[undotbs_new])):
print("Undo tablespace creating succeed!")
else:
print("Undo tablespace creating failed")
exit()
# get new ubdotbs name
def get_new_undotbs(undotbs_unused, current_undotbs, undotbs_d_cnt):
print("Get new undotbs name...")
undotbs_new = ''
if not undotbs_unused:
for i in range(1,len(undotbs_d_cnt)+2):
if "UNDOTBS" + str(i) not in undotbs_d_cnt:
undotbs_new = "UNDOTBS" + str(i)
create_new_undotbs(undotbs_new, current_undotbs, undotbs_d_cnt)
break
else:
undotbs_new=min(undotbs_unused, key= lambda x: abs(int(undotbs_d_cnt[x]) - int(undotbs_d_cnt[current_undotbs])))
if int(undotbs_d_cnt[undotbs_new]) < int(undotbs_d_cnt[current_undotbs]):
create_new_undotbs(undotbs_new, current_undotbs, undotbs_d_cnt)
print("New undotbs is: " + undotbs_new)
return undotbs_new
# set new undotbs
def set_new_undotbs(undotbs_new, current_undotbs, undotbs_d_sid):
print("Set new undotbs...")
set_new_undotbs_string = "alter system set undo_tablespace=" + undotbs_new + " sid='" + undotbs_d_sid[current_undotbs] + "';\n"
print(set_new_undotbs_string)
exec_result = exec_func(set_new_undotbs_string, feedback=True)
if exec_result.count("System altered."):
print("Set new undotbs succeed!")
else:
print("set new undotbs failed!")
exit()
if __name__ == '__main__':
print('-' * 80)
current_undotbs = get_current_undotbs()
undotbs_old = get_undotbs_old(current_undotbs)
undotbs_d_cnt = get_undotbs_d_cnt()
undotbs_d_sid = get_undotbs_d_sid()
set_undotbs_in_spfile(undotbs_d_sid)
undotbs_expired, undotbs_unused = get_expired_new_undotbs(undotbs_d_cnt, undotbs_d_sid, undotbs_old)
drop_expired_undotbs(undotbs_expired)
undotbs_new = get_new_undotbs(undotbs_unused, current_undotbs, undotbs_d_cnt)
set_new_undotbs(undotbs_new, current_undotbs, undotbs_d_sid)
网友评论