Oracle归档日志爆满问题的处理方法
最近客户单位的oracle数据库出了问题，经常出现无法连接,报错提示 ORA-00257: archiver error, Connect internal only, until freed.，手动清除归档日志后可以恢复访问，但是过不了几天依旧会爆满，每日生成的归档日志很大。经过详细排查发现 sysaux 表空间使用率达到 99.9%,清理该表空间后日志恢复正常！
[root@rac1 ~]# su - gridgrid@+ASM1:/home/grid$ asmcmd lsdgState Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files NameMOUNTED 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 - oraclePassword: oracle@orcl1:/home/oracle$rman target /Recovery Manager: Release 11.2.0.4.0 Production on Mon Oct 14 13:12:18 2024Copyright (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 exitRecovery Manager complete.oracle@orcl1:/home/oracle$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 14 13:12:32 2024Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL 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.803733804-OCT-24 65.929472905-OCT-24 65.252673106-OCT-24 71.638574607-OCT-24 212.99621808-OCT-24 138.05289509-OCT-24 84.848437310-OCT-24 262.30063811-OCT-24 74.347655312-OCT-24 70.959806410 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.43SYSAUX 62 .10 61.90 99.84SYSTEM 32 31.01 .99 3.09UNDOTBS2 32 31.64 .36 1.12UNDOTBS1 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; 12436truncate table WRH$_EVENT_HISTOGRAM; 1984truncate table WRH$_LATCH_MISSES_SUMMARY; 1763OPSQL--------------------------------------------------------------------------------MB----------truncate table WRH$_SYSSTAT; 1600truncate table WRH$_SEG_STAT; 1280truncate table WRH$_PARAMETER; 1152OPSQL--------------------------------------------------------------------------------MB----------truncate table WRH$_SYSTEM_EVENT; 978truncate table WRH$_SQL_PLAN; 864truncate table WRH$_DLM_MISC; 456OPSQL--------------------------------------------------------------------------------MB----------truncate table WRH$_MVPARAMETER; 400truncate table WRH$_SERVICE_STAT; 312truncate table WRH$_ROWCACHE_SUMMARY; 280OPSQL--------------------------------------------------------------------------------MB----------truncate table WRH$_SERVICE_WAIT_CLASS; 144truncate table WRH$_SYSMETRIC_HISTORY; 144truncate table WRH$_DB_CACHE_ADVICE; 120OPSQL--------------------------------------------------------------------------------MB----------truncate table WRH$_SQLTEXT; 10416 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归档日志爆满的资料请关注8b8.cn其它相关文章！
Oracle归档日志爆满问题的处理方法
