我们经常会遇到数据库磁盘空间爆满的问题, 或由于归档日志突增, 或由于数据文件过多, 大导致磁盘使用紧俏. 这里主要说的场景是磁盘空间本身很大, 但表空间对应的数据文件初始化的时候就直接顶满了磁盘空间, 导致经常收到磁盘空间满的报警.
一, 错误信息
告警内容如下:
[发现异常] 地产客储系统数据库 Oracle_192.168.xx.xx,192.168.xx.xx, 数据库 customer, 连接错误, 0 ORA-00257: archiver error. Connect internal only, until freed.
[发生时间] 2018.07.04 09:12:21
二, 错误原因
上述错误一看大致就知道是由于磁盘空间不足, 导致归档无法完成所致, 我们只需要清理足够的磁盘空间即可. 但在磁盘清理的时候发现磁盘空间本身可清理的不多, 被很多很大的数据文件占用, 而实际使用的 segment 大小总共不足 400G, 磁盘空间本身 1T, 所以我们可以通过收缩数据文件的方式回收磁盘空间.
数据文件初始化方式:
1. 我们创建表空间一般有两种方式初始化其数据文件, 即指定初始大小为 32G(很大的值)或指定初始大小为 100M(很小的值)然后通过自动扩展方式慢慢按需增长.
2. 第一种初始数据文件方法坏处就是开始不管你用不用到那么大, 都会占用这么大的磁盘空间(这种数据迁移的时候可以使用). 第二种初始化方法按需增长, 比较好的监控实际使用磁盘空间, 所以推荐初始值很小, 使用自动扩展慢慢增长的方式.
三, 处理步骤
1. 查看磁盘空间大小
2. 查看数据库表空间大小
- #!/bin/bash
- sqlplus -S /nolog <<EOF
- conn /as sysdba;
- set echo off heading on underline on;
- column inst_num heading "Inst Num" new_value inst_num format 99999;
- column inst_name heading "Instance" new_value inst_name format a12;
- column db_name heading "DB Name" new_value db_name format a12;
- column dbid heading "DB Id" new_value dbid format 9999999999 just c;
- prompt
- prompt Current Instance
- prompt ~~~~~~~~~~~~~~~~
- select d.dbid dbid
- , d.name db_name
- , i.instance_number inst_num
- , i.instance_name inst_name
- from v\$database d,
- v\$instance i;
- set term on feedback off lines 130 pagesize 999 tab off trims on
- column MB format 999,999,999 heading "Total MB"
- column free format 9,999,999 heading "Free MB"
- column used format 99,999,999 heading "Used MB"
- column Largest format 999,999 heading "LrgstMB"
- column tablespace_name format a20 heading "Tablespace"
- column status format a3 truncated
- column max_extents format 99999999999 heading "MaxExt"
- col extent_management for a1 trunc head "M"
- col allocation_type for a1 trunc head "A"
- col Ext_Size for a4 trunc head "Init"
- column pfree format a3 trunc heading "%Fr"
- break on report
- compute sum of MB on report
- compute sum of free on report
- compute sum of used on report
- select
- d.tablespace_name,
- decode(d.status,
- 'ONLINE', 'OLN',
- 'READ ONLY', 'R/O',
- d.status) status,
- d.extent_management,
- decode(d.allocation_type,
- 'USER','',
- d.allocation_type) allocation_type,
- (case
- when initial_extent <1048576
- then lpad(round(initial_extent/1024,0),3)||'K'
- else lpad(round(initial_extent/1024/1024,0),3)||'M'
- end) Ext_Size,
- NVL (a.bytes / 1024 / 1024, 0) MB,
- NVL (f.bytes / 1024 / 1024, 0) free,
- (NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)) used,
- NVL (l.large / 1024 / 1024, 0) largest,
- d.MAX_EXTENTS ,
- lpad(round((f.bytes/a.bytes)*100,0),3) pfree,
- (case when round(f.bytes/a.bytes*100,0)>= 20 then '' else'*' end) alrt
- FROM sys.dba_tablespaces d,
- (SELECT tablespace_name, SUM(bytes) bytes
- FROM dba_data_files
- GROUP BY tablespace_name) a,
- (SELECT tablespace_name, SUM(bytes) bytes
- FROM dba_free_space
- GROUP BY tablespace_name) f,
- (SELECT tablespace_name, MAX(bytes) large
- FROM dba_free_space
- GROUP BY tablespace_name) l
- WHERE d.tablespace_name = a.tablespace_name(+)
- AND d.tablespace_name = f.tablespace_name(+)
- AND d.tablespace_name = l.tablespace_name(+)
- AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
- UNION ALL
- select
- d.tablespace_name,
- decode(d.status,
- 'ONLINE', 'OLN',
- 'READ ONLY', 'R/O',
- d.status) status,
- d.extent_management,
- decode(d.allocation_type,
- 'UNIFORM','U',
- 'SYSTEM','A',
- 'USER','',
- d.allocation_type) allocation_type,
- (case
- when initial_extent <1048576
- then lpad(round(initial_extent/1024,0),3)||'K'
- else lpad(round(initial_extent/1024/1024,0),3)||'M'
- end) Ext_Size,
- NVL (a.bytes / 1024 / 1024, 0) MB,
- (NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)) free,
- NVL (t.bytes / 1024 / 1024, 0) used,
- NVL (l.large / 1024 / 1024, 0) largest,
- d.MAX_EXTENTS ,
- lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) pfree,
- (case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100)>= 20 then '' else'*' end) alrt
- FROM sys.dba_tablespaces d,
- (SELECT tablespace_name, SUM(bytes) bytes
- FROM dba_temp_files
- GROUP BY tablespace_name order by tablespace_name) a,
- (SELECT tablespace_name, SUM(bytes_used ) bytes
- FROM v\$temp_extent_pool
- GROUP BY tablespace_name) t,
- (SELECT tablespace_name, MAX(bytes_cached) large
- FROM v\$temp_extent_pool
- GROUP BY tablespace_name order by tablespace_name) l
- WHERE d.tablespace_name = a.tablespace_name(+)
- AND d.tablespace_name = t.tablespace_name(+)
- AND d.tablespace_name = l.tablespace_name(+)
- AND d.extent_management LIKE 'LOCAL'
- AND d.contents LIKE 'TEMPORARY'
- ORDER by 1
- /
- prompt
- exit
- EOF
3. 查询可直接收缩表空间数据文件
这里查看的是可以直接收缩的数据文件大小, 比如最开始初始化的数据文件为 32G, 在数据文件高水位以下的为 20G, 那么可直接回收的为 12G.
- select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
- ceil(HWM * a.block_size)/1024/1024 ResizeTo,
- (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
- 'alter database datafile'''||a.name||'''resize'||
- ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
- from v$datafile a,
- (select file_id,max(block_id+blocks-1) HWM
- from dba_extents
- group by file_id) b
- where a.file# = b.file_id(+)
- and (a.bytes - HWM *block_size)>0;
4. 直接收缩数据文件
alter database datafile '/oracle/oradata/bi/data01.dbf' resize 1548M;
5. 再次查看磁盘空间, 已释放很多, 可手动完成归档测试.
四, 总结
针对 oracle 的数据文件收缩 (磁盘空间收缩), 我们一般可通过当前磁盘空间查看(df -h)--> 执行可直接收缩的查询命令和收缩命令 -->执行大表高水位收缩 -->执行表空间高水位收缩 (降低文件高水位线)--> 再次执行直接回收表空间数据文件命令
直接收缩数据文件的方式参考本文上述步骤即可完成.
大表的高水位收缩参考: https://www.linuxidc.com/Linux/2018-11/155309.htm
那么如何降低表空间的数据文件高水位, 进而完成表空间数据文件回收呢?
1. 查看大于 10G 的数据文件
select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024) >10 order by file_id;
2. 查看大于 10G 的数据文件对应的数据块信息
- select file_id,max(block_id+blocks-1) HWM,block_id
- from dba_extents
- where file_id =14
- group by file_id,block_id
- order by hwm desc ;
3. 查看大表对应的数据块信息
- ## 查看大表
- select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024) >10 order by file_id;
- ## 查看大表对应的块
- select owner,segment_name,file_id,block_id,blocks from dba_extents where segment_name='TABLE_NAME';
4. 降低表的高水位
- alter table table_name move;
- alter index idx_name rebuild;
5. 查看数据文件对应的最大的 block_id
- SELECT MAX(block_id)
- FROM dba_extents
- WHERE tablespace_name = 'TABLESPACE_NAME';
6. 执行数据文件收缩
(block_id+blocks-1)数据文件的 HWM
alter database datafile '/oracle/oradata/bi/data01.dbf' resize xxxM;
来源: http://www.linuxidc.com/Linux/2018-11/155308.htm