数据库管理-第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。 除授权转载并标明出处外，均为“非法”抄袭
在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) SELECTp.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 ALLSELECT 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名称查询到对应的进程号进行进一步排障处理（这里就不做演示了）。
总结
本期针对数据库归档日志、表空间以及操作系统中的日志空间检查、清理以及排障处理进行了讲解。 老规矩，知道写了些啥。
