Oracle Linux服务器磁盘空间不足

1. 使用df-h命令查看系统磁盘空间

2. 使用du -h --max-depth=1 data/*查看哪个文件或目录占用较大的磁盘空间

3. 删除/data/tmp两年前的tmp文件，使用以下命令:

   # find data/tmp/ -name '*.*' -mtime +720 |xargs rm

ORA-16014:log 3 sequence# 99 not archived, no available destinations

ASM磁盘空间不足导致的重做日志不能归档，登录RMAN删除无用的归档日志释放空间

RMAN> delete archivelog until time 'sysdate-7'; --删除7天前的归档日志

RMAN> crosscheck archivelog all;

RMAN> delete noprompt expired archivelog all;

UNDO表空间过大导致磁盘空间不足的解决

查看表空间名：

SQL> show parameter undo_tablespace

NAME TYPE VALUE

------------------------ ----------- ----------------

undo_tablespace string UNDOTBS1

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

EXAMPLE

TEST

RMAN

8 rows selected.

SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%';

FILE_NAME    BYTES/1024/1024

-------------------  -------------------------------------

+DATA/orcl/datafile/undotbs1.264.951533909  85

查看回滚段的使用情况

哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）

SQL> select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;

检查UNDO Segment状态：

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

       USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS

---------- ---------- --------------------- ---------------------- ----------

2    0 .000358582 .001945496    2

7    0 .000358582 .001213074    2

0    0 .000358582 .000358582    0

8    0 .000480652 .000968933    4

6    1 .001945496 .003227234    1

4    0 .001945496 .001945496    2

1    0 .001945496 .001945496    0

5    0 .001945496 .001945496    1

9    0 .001945496 .001945496    1

10    0 .002189636 .002189636    0

3    0 .003044128 .003044128    0

11 rows selected.

创建新的UNDO表空间

设置自动扩展参数：

SQL>  create undo tablespace undotbs2 datafile '+data' size 100m reuse autoextend on next 50m maxsize 500M;

Tablespace created.

动态更改spfile配置文件：

SQL> alter system set undo_tablespace=undotbs2 scope=both;  

System altered. 

等待原UNDO表空间所有UNDO SEGMENT OFFLINE：

SQL> select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from   v$rollstat order by rssize; 

       USN      XACTS STATUS          RSSIZE/1024/1024 HWMSIZE/1024/1024

---------- ---------- --------------- ---------------- -----------------

   SHRINKS

----------

         4          0 OFFLINE                  .3046875         5.1171875

         3

         0          0 OFFLINE                  .3671875          .3671875

         0

         3          0 OFFLINE                 1.1171875         5.1171875

         4

         2          0 OFFLINE                 1.1171875         4.1171875

         2

         1          0 OFFLINE                 1.1171875         4.1171875

         3

         5          0 OFFLINE                 1.1171875         5.1171875

         2

         6          0 OFFLINE                 1.1171875         4.1171875

         3

         7          0 OFFLINE                 1.9921875         4.1171875

         3

         8          0 OFFLINE                 2.1171875         4.1171875

         1

         9          0 OFFLINE                 2.1171875         5.1171875

         4

        10          0 OFFLINE                 2.1171875         4.1171875

         1

11 rows selected.

删除原有的UNDO表空间：

SQL> drop tablespace undotbs1 including contents;  

Tablespace dropped.

更新pfile

SQL> create pfile from spfile;  

File created.