ORA-01628

参考文献

1. 某客户回滚段达到32765处理

2. ORA-30013: Undo Tablespace ‘UNDOTBS1’ Is Currently In Use

一、背景知识

1. Oracle数据库逻辑结构从小至大依次由块(block)、区(extent)、段(segment)、表空间(tablespace)、数据库(database)构成。
2. undo表空间下，每个段区的数量最大为32765，当有大事务出现时，会产生大量的undo，导致回滚段区的数量超出最大限制，产生ORA-01628报错。

二、解决方案

1. 查询段下区数超数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;

2. 查询待替换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;

3. 查询各个实例所用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;

4. 创建总大小相当的单个文件大小16g的新undo表空间

创建新的undo表空间命令

create undo tablespace newundotbs datafile '+data01' size 16g; 

为新的表空间增加文件命令

alter tablespace newundotbs add datafile '+data01' size 16g; 

5. 切换至新的undo表空间

alter system set undo_tablespace='newundotbs' sid='&sid';

6. 删除旧的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)