数据库管理-第265期 Oracle数据库的空间问题检查与处理（20241125）

原创胖头鱼的鱼缸 2024-11-25

407

数据库管理265期 2024-11-25

数据库管理-第265期 Oracle数据库的空间问题检查与处理（20241125）

1 归档空间

1.1 使用FRA
1.2 使用指定目录
1.3 清理归档日志

2 表空间
3 操作系统中的日志

1 审计日志
2 数据库日志
3 监听日志
4 其他日志
5 应急处理

总结

数据库管理-第265期 Oracle数据库的空间问题检查与处理（20241125）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database（Oracle与MySQL）
PostgreSQL ACE Partner
10年数据库行业经验，现主要从事数据库服务工作
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP、年度墨力之星，ITPUB认证专家、专家百人团成员，数盟会长老会成员，OCM讲师，PolarDB开源社区技术顾问，HaloDB外聘技术顾问，OceanBase观察团成员，青学会MOP技术社区（青年数据库学习互助会）技术顾问
圈内拥有“总监”、“保安”、“国产数据库最大敌人”等称号，非著名社恐（社交恐怖分子）
公众号：胖头鱼的鱼缸；CSDN：胖头鱼的鱼缸（尹海文）；墨天轮：胖头鱼的鱼缸；ITPUB：yhw1809。
除授权转载并标明出处外，均为“非法”抄袭

演示文稿1_01.png
在Oracle的运维过程中，其实影响数据库对外提供服务的主要问题除了资源不足（包括烂SQL导致的CPU扛不住），最多的问题其实是各种空间不足导致的一系列问题。本期总监带你针对数据库的各种空间进行检查与问题处理（主要针对19c）。

1 归档空间

这里首先要看数据库的配置：

show parameter log_archive_dest_1

1.1 使用FRA

这里可以看到这个数据库配置的使用fast recovery area（FRA）来存放归档日志，因此还需要通过以下命令来查看FRA指定的路径和限制的逻辑大小（这里需要注意的是，即便指定路径仍然有可用空间，但当FRA占用超过逻辑大小后会导致无法写入）：

show parameter db_recovery_file_dest

还可以使用下面命令来检查FRA的占用率：

select * from v$flash_recovery_area_usage;

1.2 使用指定目录

另一种方式就是直接使用指定的路径：

这种情况下就需要到操作系统或ASM中检查路径占用率，这里仅展示查询ASM的方式：

su - grid
asmcmd lsdg

这种方式并不能很方便的查看到占用率，也可以到ASM实例中使用下列语句进行查看：

SELECT
NAME,
ROUND((TOTAL_MB/1024),2) TOTAL_GB,
ROUND((FREE_MB/1024),2) FREE_GB,
ROUND((TOTAL_MB - FREE_MB)/1024,2) AS USED_GB,
ROUND((TOTAL_MB - FREE_MB) / TOTAL_MB * 100, 2)||'%' AS USED_PERCENT
FROM
V$ASM_DISKGROUP;

1.3 清理归档日志

这里需要使用RMAN来实现：

rman target /
delete archivelog all completed before 'sysdate-1'; --指定删除多久以前的归档日志
--这里需要输入一个YES来确认删除，可以通过添加noprompt关键词来忽略这一操作，直接进行删除

这里也提供一个清理脚本及定时任务配置（每天12点执行）：

#!/bin/bash
# delete_archivelog.sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=xxdbaas1
export PATH=$ORACLE_HOME/bin:$PATH
rman target / <<EOF
delete noprompt archivelog all completed before 'sysdate-1.5';
exit;
EOF

最近就因为我手下员工巡检不认真导致了归档空间满了的问题。

2 表空间

影响业务运行的另一个问题则是表空间空间不足，这里提供一个查询表空间的语句：

SELECT UPPER(D.TABLESPACE_NAME) "TBS_NAME",
D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0) "USED(MB)",
D.TOT_GROOTTE_MB "TOTAL(MB)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0)) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "USED%",
nvl(F.TOTAL_BYTES,0) "AVAILABLE(MB)",
decode(D.TOT_MAXBYTES_MB, 0, D.TOT_GROOTTE_MB, D.TOT_MAXBYTES_MB) "MAX(MB)",
decode(D.TOT_MAXBYTES_MB,0,TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /D.TOT_GROOTTE_MB * 100,2),'990.99') || '%',TO_CHAR(ROUND(((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)) /D.TOT_MAXBYTES_MB * 100,2),'990.99') || '%') "MAX_USED%",
decode(D.TOT_MAXBYTES_MB, 0, D.TOT_GROOTTE_MB, D.TOT_MAXBYTES_MB) -(D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0)) "MAX_AVAILABLE(MB)"
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB,ROUND(SUM( decode(DD.MAXBYTES,0,DD.BYTES,DD.MAXBYTES)) / (1024 * 1024), 2) TOT_MAXBYTES_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY decode(D.TOT_MAXBYTES_MB,0,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /D.TOT_GROOTTE_MB * 100,2),'990.99') || '%',
TO_CHAR(ROUND(((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)) /D.TOT_MAXBYTES_MB * 100,2),'990.99') || '%') desc;

这个语句可以查询出表空间最大可扩展空间（即MAX(MB)），并根据这个值来判断实际在可扩展空间基础上的占用率，避免了在表空间当前大小下计算出的占用率带来的不必要的“恐慌”。如果表空间空间不足，可以通过以下语句进行扩展（关于承载目录是否有剩余空间这里就不做展示了）：

alter tablespace xxx add datafile size 500m autoextend on next 500m maxsize unlimited; --使用OMF
alter tablespace xxx add datafile 'PATH/TO/file.ora' size 500m autoextend on next 500m maxsize unlimited; --未使用OMF或需人工指定
--undo表空间添加数据文件方式与永久表空间一直，建议如无业务反馈undo不足影响业务，无需添加

alter tablespace xxx add tempfile size 500m autoextend on next 500m maxsize unlimited; --使用OMF
alter tablespace xxx add tempfile 'PATH/TO/file.ora' size 500m autoextend on next 500m maxsize unlimited; --未使用OMF或需人工指定
--temp表空间需要修改文件关键字为tempfile，同undo表空间类似建议如无业务反馈temp不足影响业务，无需添加

--在数据大规模数据操作导入时，建议进行测试以提前调整undo和temp表空间大小

这里再附加一个多租户环境在CDB中查询表空间用量的语句：

WITH df AS (
  SELECT
    con_id,
    tablespace_name,
    SUM(bytes) bytes,
    COUNT(*) cnt,
    DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext 
  FROM
    cdb_data_files 
  GROUP BY
    con_id,
tablespace_name),
um AS (SELECT con_id, tablespace_name, used_space ub, used_percent FROM cdb_tablespace_usage_metrics),
pdb AS (SELECT con_id, NAME FROM v$containers) SELECT
p.NAME,
d.tablespace_name,
TO_CHAR (u.used_percent, '99999990.00'),
NVL ((a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0),
a.autoext,
NVL (a.bytes, 0) / 1024 / 1024 / 1024,
NVL (a.bytes - NVL (f.bytes, 0), 0) / 1024 / 1024 / 1024,
NVL (f.bytes, 0) / 1024 / 1024 / 1024,
d.STATUS,
a.cnt,
d.contents,
d.extent_management,
d.segment_space_management 
FROM
  cdb_tablespaces d,
  df a,
  um u,
  pdb p,
  (SELECT CON_ID, tablespace_name, SUM(bytes) bytes FROM cdb_free_space GROUP BY CON_ID, tablespace_name) f 
WHERE
  d.tablespace_name = a.tablespace_name (+) 
  AND d.tablespace_name = f.tablespace_name (+) 
  AND d.tablespace_name = u.tablespace_name (+) 
  AND NOT d.contents = 'UNDO' 
  AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') 
  AND p.con_id = d.con_id 
  AND p.con_id = u.con_id (+) 
  AND p.con_id = a.con_id (+) 
  AND p.con_id = f.con_id (+) UNION ALL
  SELECT p.NAME,
  d.tablespace_name,
  TO_CHAR (u.used_percent, '99999990.00'),
  NVL ((u.ub * d.block_size) / tf.bytes * 100, 0),
  tf.autoext,
  NVL (tf.bytes, 0) / 1024 / 1024 / 1024,
  NVL (u.ub * d.block_size, 0) / 1024 / 1024 / 1024,
  (NVL (tf.bytes, 0) - NVL (u.ub * d.block_size, 0)) / 1024 / 1024 / 1024,
  d.STATUS,
  tf.cnt,
  d.contents,
  d.extent_management,
  d.segment_space_management 
FROM
  cdb_tablespaces d,
  um u,
  pdb p,
  (
    SELECT
      con_id,
      tablespace_name,
      SUM(bytes) bytes,
      COUNT(*) cnt,
      DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext 
    FROM
      cdb_temp_files 
    GROUP BY
      con_id,
  tablespace_name) tf 
WHERE
  d.tablespace_name = tf.tablespace_name (+) 
  AND d.tablespace_name = u.tablespace_name (+) 
  AND d.extent_management = 'LOCAL' 
  AND d.contents = 'TEMPORARY' 
  AND p.con_id = d.con_id 
  AND p.con_id = u.con_id (+) 
  AND p.con_id = tf.con_id (+) UNION ALL
SELECT
  p.NAME,
  d.tablespace_name,
  TO_CHAR (u.used_percent, '99999990.00'),
  NVL ((u.ub * d.block_size) / a.bytes * 100, 0),
  a.autoext,
  NVL (a.bytes, 0) / 1024 / 1024 / 1024,
  NVL (u.ub * d.block_size, 0) / 1024 / 1024 / 1024,
  (NVL (a.bytes, 0) - NVL (u.ub * d.block_size, 0)) / 1024 / 1024 / 1024,
  d.STATUS,
  a.cnt,
  d.contents,
  d.extent_management,
  d.segment_space_management 
FROM
  cdb_tablespaces d,
  df a,
  um u,
  pdb p 
WHERE
  d.tablespace_name = a.tablespace_name (+) 
  AND d.tablespace_name = u.tablespace_name (+) 
  AND d.contents = 'UNDO' 
  AND p.con_id = d.con_id 
  AND p.con_id = u.con_id (+) 
  AND p.con_id = a.con_id (+);

--这条语句相对比较粗糙，请各位自己调整一下

3 操作系统中的日志

1 审计日志

这里需要先到操作系统中进行查询：

show parameter audit_file_dest

清理可以使用以下命令实现：

#清理30天前的日志
/usr/bin/find /u01/app/oracle/admin/xxdbaas/adump/ -name "*.aud" -mtime +30 | /usr/bin/xargs rm -rf {}

2 数据库日志

数据库日志是存放在$ORACLE_BASE/diag/rdbms/{db_name}/{sid}下的，可以通过下面命令清理：

#清理30天前的日志
/usr/bin/find /u01/app/oracle/diag/rdbms/xxdbaas/xxdbaas1/alert/ -name "*.xml" -mtime +30 | /usr/bin/xargs rm -rf {}
/usr/bin/find /u01/app/oracle/diag/rdbms/xxdbaas/xxdbaas1/trace/ -name "*.tr*" -mtime +30 | /usr/bin/xargs rm -rf {}

3 监听日志

监听日志是存放在$ORACLE_BASE/diag/tnslsnr/{hostname}下的：

里面会有多个文件夹asmnet1lsnr_asm、listener、listener_scan1、mgmtlsnr，分别存放ASM、本地监听、SCAN监听以及MGMG监听（容量很小可忽略）日志，可以通过下面命令清理：

#清理30天前的日志
/usr/bin/find /u01/app/grid/diag/tnslsnr/{hostname}/listener/alert/ -name "*.xml" -mtime +30 | /usr/bin/xargs rm -rf {}
/usr/bin/find /u01/app/grid/diag/tnslsnr/{hostname}/listener/trace/ -name "*.log" -mtime +30 | /usr/bin/xargs rm -rf {}
/usr/bin/find /u01/app/grid/diag/tnslsnr/{hostname}/listener_scan1/alert/ -name "*.xml" -mtime +30 | /usr/bin/xargs rm -rf {}
/usr/bin/find /u01/app/grid/diag/tnslsnr/{hostname}/listener_scan1/trace/ -name "*.log" -mtime +30 | /usr/bin/xargs rm -rf {}
/usr/bin/find /u01/app/grid/diag/tnslsnr/{hostname}/asmnet1lsnr_asm/alert/ -name "*.xml" -mtime +30 | /usr/bin/xargs rm -rf {}
/usr/bin/find /u01/app/grid/diag/tnslsnr/{hostname}/asmnet1lsnr_asm/trace/ -name "*.log" -mtime +30 | /usr/bin/xargs rm -rf {}

注：不同数据库、集群监听名称不同所呈现的日志目录名称是不一样的，需要根据实际环境调整语句

4 其他日志

其他日志一般非故障情况下一般不会占用较大空间，比如crs目录：

$ORACLE_BASE/diag/crs/{hostname}/crs/alert/log.xml
$ORACLE_BASE/diag/crs/{hostname}/crs/trace/*.tr*

其余目录可根据实际情况处理，并通过上面类似的命令执行清理日志，并可以写入脚本及crontab中自动定时执行。

5 应急处理

上面的常规清理操作仅在数据库正常运行时可行，如果出现突发情况造成日志暴增，这时候修改时间查询日志将变得不可用，可以使用下面方式查找：

du -sh /path/to/file/* |grep G

这样可以查询出容量来到G的文件，如果是数据库目录还能通过trace名称查询到对应的进程号进行进一步排障处理（这里就不做演示了）。

总结

本期针对数据库归档日志、表空间以及操作系统中的日志空间检查、清理以及排障处理进行了讲解。
老规矩，知道写了些啥。

墨力计划 oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

胖头鱼的数据库之旅（共221篇）

胖头鱼的鱼缸-数据库相关文章

数据库管理-第265期 Oracle数据库的空间问题检查与处理（20241125）

数据库管理265期 2024-11-25

数据库管理-第265期 Oracle数据库的空间问题检查与处理（20241125）

1 归档空间

1.1 使用FRA

1.2 使用指定目录

1.3 清理归档日志

2 表空间

3 操作系统中的日志

1 审计日志

2 数据库日志

3 监听日志

4 其他日志

5 应急处理

总结

文章被以下合辑收录

评论