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>

四、清理后效果

清理后可能导致客户端掉线情况，重新登录客户端即可。查看表空间使用情况 如下 SYSAUX 表空间使用率为 50% 多。

隔两天后观察归档日志生成情况如下,已经不在暴增，客户表示非常满意！

以上就是Oracle归档日志爆满问题的处理方法的详细内容，更多关于Oracle归档日志爆满的资料请关注其它相关文章！