最近客户单位的oracle数据库出了问题，经常出现无法连接,报错提示 ora-00257: archiver error, connect internal only, until freed.，手动清除归档日志后可以恢复访问，但是过不了几天依旧会爆满，每日生成的归档日志很大。经过详细排查发现 sysaux 表空间使用率达到 99.9%,清理该表空间后日志恢复正常！
一、先清理归档日志使得数据库能够正常连接
[root@rac1 ~]# su - grid grid@ asm1:/home/grid$ asmcmd lsdg state type rebal sector block au total_mb free_mb req_mir_free_mb usable_file_mb offline_disks voting_files name mounted extern n 512 4096 1048576 409600 127 0 127 0 n arch/ mounted extern n 512 4096 1048576 1662976 997139 0 997139 0 n data/ mounted normal n 512 4096 1048576 30720 29794 10240 9777 0 y ocrdg/
上述结果可以看到表空间仅仅剩余 127 mb 可用。需要立刻释放空间。
使用rman工具 执行 删除2天前的归档日志
delete archivelog until time "sysdate-2";
[root@rac1 ~]# su - oracle password: oracle@orcl1:/home/oracle$rman target / recovery manager: release 11.2.0.4.0 production on mon oct 14 13:12:18 2024 d88尊龙官网手机app copyright (c) 1982, 2011, oracle and/or its affiliates. all rights reserved. connected to target database: orcl (dbid=1556520972) rman> delete archivelog until time "sysdate-2";
二、排查问题
- 查看归档日志每天生成情况
发现每天都生成70~80g的日志数据，正常应该2-8g;
rman> exit recovery manager complete. oracle@orcl1:/home/oracle$sqlplus / as sysdba sql*plus: release 11.2.0.4.0 production on mon oct 14 13:12:32 2024 d88尊龙官网手机app copyright (c) 1982, 2013, oracle. all rights reserved. connected to: oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production with the partitioning, real application clusters, automatic storage management, olap, data mining and real application testing options sql> sql> select trunc(first_time) "time",sum(block_size * blocks) / 1024 / 1024 / 1024 "size(gb)"from v$archived_log group by trunc(first_time) order by trunc(first_t); time size(gb) ------------ ---------- 03-oct-24 75.8037338 04-oct-24 65.9294729 05-oct-24 65.2526731 06-oct-24 71.6385746 07-oct-24 212.996218 08-oct-24 138.052895 09-oct-24 84.8484373 10-oct-24 262.300638 11-oct-24 74.3476553 12-oct-24 70.9598064 10 rows selected.
查看数据库表空间使用情况
发现 sysaux 表空间使用率达到 99.84%
sql> select f.tablespace_name tablespace_name,round(((d.sumbytes d.extend_bytes) / 1024 / 1024 / 1024), 2) total_g, round((f.sumbytes d.extend_bytes) / 1024 / 1024 / 1024, 2) free_g, round((d.sumbytes - f.sumbytes) / 1024 / 1024 / 1024, 2) used_g, round((d.sumbytes - f.sumbytes) * 100 / (d.sumbytes d.extend_bytes), 2) used_percent from (select tablespace_name, sum(bytes) sumbytes from dba_free_space group by tablespace_name) f, (select tablespace_name, sum(aa.bytes) sumbytes, sum(aa.extend_bytes) extend_bytes from (select nvl(case when autoextensible = 'yes' then (case when (maxbytes - bytes) >= 0 then (maxbytes - bytes) end) end, 0) extend_bytes, tablespace_name, bytes from dba_data_files) aa group by tablespace_name) d where f.tablespace_name = d.tablespace_name order by used_percent desc; tablespace_name total_g free_g used_g used_percent ------------------------------ ---------- ---------- ---------- ------------ users 759.75 57.51 702.24 92.43 sysaux 62 .10 61.90 99.84 system 32 31.01 .99 3.09 undotbs2 32 31.64 .36 1.12 undotbs1 32 31.94 .06 .18
- 查找归sysaux空间占用大于200mb的对象并生成删除sql
查出的对象属于 sys用户 请使用此账户执行如下sql语句的 'opsql’字段语句完成清理工作。
sql> select distinct 'truncate table ' || segment_name || ';' as opsql, s.bytes / 1024 / 1024 mb from dba_segments s where s.segment_name like 'wrh$%' and segment_type in ('table partition', 'table') and s.bytes / 1024 / 1024 > 200 order by s.bytes / 1024 / 1024 desc;
opsql
--------------------------------------------------------------------------------
mb
----------
truncate table wrh$_active_session_history;
12436
truncate table wrh$_event_histogram;
1984
truncate table wrh$_latch_misses_summary;
1763
opsql
--------------------------------------------------------------------------------
mb
----------
truncate table wrh$_sysstat;
1600
truncate table wrh$_seg_stat;
1280
truncate table wrh$_parameter;
1152
opsql
--------------------------------------------------------------------------------
mb
----------
truncate table wrh$_system_event;
978
truncate table wrh$_sql_plan;
864
truncate table wrh$_dlm_misc;
456
opsql
--------------------------------------------------------------------------------
mb
----------
truncate table wrh$_mvparameter;
400
truncate table wrh$_service_stat;
312
truncate table wrh$_rowcache_summary;
280
opsql
--------------------------------------------------------------------------------
mb
----------
truncate table wrh$_service_wait_class;
144
truncate table wrh$_sysmetric_history;
144
truncate table wrh$_db_cache_advice;
120
opsql
--------------------------------------------------------------------------------
mb
----------
truncate table wrh$_sqltext;
104
16 rows selected.
sql>
三、处理问题
挑选较大的进行清理。如下
sql> truncate table wrh$_active_session_history; table has bing truncated. sql> truncate table wrh$_event_histogram; table has bing truncated. sql>
四、清理后效果
清理后可能导致客户端掉线情况，重新登录客户端即可。
查看表空间使用情况 如下 sysaux 表空间使用率为 50% 多。
隔两天后观察归档日志生成情况如下,已经不在暴增，客户表示非常满意！
以上就是oracle归档日志爆满问题的处理方法的详细内容，更多关于oracle归档日志爆满的资料请关注其它相关文章！
