Oracle归档日志爆满问题的处理方法_Oracle

最近客户单位的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
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
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>