大家好，这里是公众号 DBA学习之路，分享一些学习数据库路上的知识和经验。

@TOC

前言

上周给客户巡检的时候，发现有一套 Oracle RAC 11GR2 数据库的 ASM 磁盘使用率 99.9% 了，来不及扩容了，需要紧急处理，遇到这种情况你会怎么做？

可以看到目前 DATA 磁盘组可使用的空间仅剩 196M，好在还有一个 DATA1 磁盘组可用空间比较充足。好在还有一个 DATA1 磁盘组，大概想到了如下紧急在线处理的思路（数据库无法停机），：

  1. 关闭 DATA 磁盘组下所有数据文件的自动扩展；
  2. 移走 DATA 磁盘组下可以在线移动的文件；
    • 11GR2 不支持数据文件在线移动，所以排除数据文件；
    • 在线重建临时表空间；
    • 在线重建在线重做日志；

本文记录一下处理步骤，分享给大家一起学习。

问题分析

首先需要查看一下 DATA 磁盘组中是否存在临时表空间和在线重做日志文件，这里分享一个可以查看 ASM 磁盘组使用情况的脚本 asmdu.sh：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
cat<<-\EOF>asmdu.sh
#!/bin/bash
#
# du of each subdirectory in a directory for ASM
#
D=$1
 
if [[ -z $D ]]
then
 echo "Please provide a directory !"
 exit 1
fi
 
(for DIR in `asmcmd ls ${D}`
 do
     echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`
 done) | awk -v D="$D" ' BEGIN {  printf("\n\t\t%40s\n\n", D " subdirectories size")           ;
                                  printf("%25s%16s%16s\n", "Subdir", "Used MB", "Mirror MB")   ;
                                  printf("%25s%16s%16s\n", "------", "-------", "---------")   ;}
                               {
                                  printf("%25s%16s%16s\n", $1, $2, $3)                         ;
                                  use += $2                                                    ;
                                  mir += $3                                                    ;
                               }
                         END   { printf("\n\n%25s%16s%16s\n", "------", "-------", "---------");
                                 printf("%25s%16s%16s\n\n", "Total", use, mir)                 ;} '
EOF
chmod +x asmdu.sh

快速查看了一下 DATA 磁盘组的使用情况：

1
2
3
4
5
6
7
8
9
10
[grid@luciferdb1 ~]$ ./asmdu.sh +DATA/MESDB

                         +DATA/MESDB subdirectories size

                   Subdir         Used MB       Mirror MB
                   ------         -------       ---------
....
               ONLINELOG/           15390           15390
                TEMPFILE/            7904            7904
....

可以看到临时表空间和在线重做日志存放在 DATA 磁盘组中，那就可以按照之前的思路开始操作。

处理过程

关闭自动扩展

为了防止空间再次减少，需要第一时间将数据文件和临时文件的自动扩展关闭：

1
2
3
4
5
-- 脚本一键获取关闭自动扩展的命令
SQL> set line222 pages1000
select 'alter database datafile ''' || file_name || ''' autoextend off;' from dba_data_files where AUTOEXTENSIBLE='YES' and file_name like '+DATA/%'
union
select 'alter database tempfile ''' || file_name || ''' autoextend off;' from dba_temp_files where AUTOEXTENSIBLE='YES' and file_name like '+DATA/%';

使用以上输出的结果即可一键关闭已开启自动扩展的数据文件和临时文件。

重建临时表空间

重建临时表空间可以在线执行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- 查看当前默认临时表空间（如果有临时表空间组，需要针对组进行删除）
SQL> select * from dba_tablespace_groups;

SQL> col PROPERTY_NAME for a30
col PROPERTY_VALUE for a20
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE        MESTEMP

-- 记录原始表空间文件
SQL> col file_name for a100
select file_name from dba_temp_files where tablespace_name in ('MESTEMP');

FILE_NAME
----------------------------------------------------------------------------------------------------
+DATA/mesdb/tempfile/mestemp.4603.959594665
+DATA/mesdb/tempfile/mestemp.2634.941818439
+DATA/mesdb/tempfile/mestemp.4606.960055783

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
+DATA/mesdb/tempfile/temp.271.879188975
+DATA/mesdb/tempfile/mestemp.4603.959594665
+DATA/mesdb/tempfile/temp.2328.941550433
+DATA/mesdb/tempfile/mestemp.2634.941818439
+DATA/mesdb/tempfile/mestemp.4606.960055783
+DATA/mesdb/tempfile/temp.4320.1040983415
+DATA/mesdb/tempfile/temp.4433.1040983647

-- 创建临时的临时表空间 tempdata
create temporary tablespace tempdata tempfile '+DATA1' size 1G autoextend on;
alter tablespace tempdata add tempfile '+DATA1' size 1g autoextend on;

-- 切换默认临时表空间为临时的临时表空间
alter database default temporary tablespace tempdata;

-- 删除原始临时表空间 MESTEMP
drop tablespace MESTEMP including contents and datafiles cascade constraints;

-- kill 掉占用原始临时表空间的会话
select 'alter system kill session ''' || a.sid || ',' || a.serial# || ''' immediate;'
  from v$session a, v$sort_usage srt
 where a.saddr = srt.session_addr
       and srt.tablespace = 'MESTEMP'
 order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;

-- 重建原始临时表空间 MESTEMP
create temporary tablespace MESTEMP tempfile '+DATA1' size 1G autoextend on;

-- 新增临时表空间 MESTEMP 数据文件（根据原始临时表空间文件数量来新增）
alter tablespace MESTEMP add tempfile '+DATA1' size 1g autoextend on;
alter tablespace MESTEMP add tempfile '+DATA1' size 1g autoextend on;
alter tablespace MESTEMP add tempfile '+DATA1' size 1g autoextend on;

-- 切换默认临时表空间为原始临时表空间 MESTEMP
alter database default temporary tablespace MESTEMP;

--删除临时表空间
drop tablespace tempdata including contents and datafiles cascade constraints;

-- 检查默认临时表空间以及文件路径
SQL> col PROPERTY_NAME for a30
col PROPERTY_VALUE for a20
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE        MESTEMP

-- 再次查看临时表空间文件是否成功切换到 DATA1 磁盘组下
SQL> col file_name for a100
select file_name from dba_temp_files where tablespace_name in ('MESTEMP');

FILE_NAME
----------------------------------------------------------------------------------------------------
+DATA1/mesdb/tempfile/mestemp.2678.1195646647
+DATA1/mesdb/tempfile/mestemp.2622.1195646703
+DATA1/mesdb/tempfile/mestemp.2576.1195646705
+DATA1/mesdb/tempfile/mestemp.2717.1195646707

可以看到临时表空间文件路径已经切换。

重建 REDO 文件

在线重做日志文件在线重建也比较简单：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- 查看在线日志
SQL> set line2222 pages1000
select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1     424169   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
         2          1     424168   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
         3          2     418545   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
         4          2     418543   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
         5          1     424170   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
         6          1     424171   52428800        512          2 NO  CURRENT             4.5454E+10 13-MAR-25   2.8147E+14
         7          2     418544   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
         8          2     418546   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
         9          1     424167   52428800        512          2 YES ACTIVE              4.5454E+10 13-MAR-25   4.5454E+10 13-MAR-25
        10          2     418547   52428800        512          2 NO  CURRENT             4.5454E+10 13-MAR-25   2.8147E+14

10 rows selected.

-- 新增临时在线日志
SQL> alter database add logfile thread 1 
group 100 size 1G,
group 101 size 1G;

alter database add logfile thread 2 
group 200 size 1G,
group 201 size 1G;

-- 切换日志
alter system archive log current;
alter system checkpoint;

-- 删掉错误路径的日志文件（确保日志状态为 INACTIVE）
SQL> alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;

-- 重新添加在线日志（刚删除的）
SQL> alter database add logfile thread 1 
group 1 size 1G,
group 2 size 1G,
group 3 size 1G,
group 4 size 1G,
group 5 size 1G;

alter database add logfile thread 2 
group 6 size 1G,
group 7 size 1G,
group 8 size 1G,
group 9 size 1G,
group 10 size 1G;

-- 删除临时在线日志（确保日志状态为 INACTIVE）
SQL> alter database drop logfile group 200;
alter database drop logfile group 201;
alter database drop logfile group 100;
alter database drop logfile group 101;

-- 再次查看在线日志
SQL> col member for a100
select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1     424173 1073741824        512          2 NO  CURRENT             4.5454E+10 13-MAR-25   2.8147E+14
         2          1          0 1073741824        512          2 YES UNUSED                       0                      0
         3          1          0 1073741824        512          2 YES UNUSED                       0                      0
         4          1          0 1073741824        512          2 YES UNUSED                       0                      0
         5          1          0 1073741824        512          2 YES UNUSED                       0                      0
         6          2     418549 1073741824        512          2 NO  CURRENT             4.5454E+10 13-MAR-25   2.8147E+14
         7          2          0 1073741824        512          2 YES UNUSED                       0                      0
         8          2          0 1073741824        512          2 YES UNUSED                       0                      0
         9          2          0 1073741824        512          2 YES UNUSED                       0                      0
        10          2          0 1073741824        512          2 YES UNUSED                       0                      0

10 rows selected.

可以看到在线日志的路径已经切换到新的目录。经过以上处理之后，再次查看 DATA 磁盘组的使用情况：

可以看到 DATA 磁盘组的可用空间为 94G 左右，问题已经解决。

写在最后

其实回溯一下这个问题，为什么磁盘组使用到剩余可用 196M 时在巡检时被发现呢？

  1. 已经部署了 ASM 磁盘使用率监控，且是每天都发送邮件，为什么没有被重视？
  2. DATA1 磁盘组明显是因为之前 DATA 磁盘组使用空间不足才添加的，为什么新的数据文件还是建在了 DATA 磁盘组下？

数据库问题还是要重视起来！好了，本次分享就到这了~