首页 > 数据库 >Oracle归档日志异常增长问题的排查过程 转载 : https://blog.csdn.net/3moods/article/details/132031152

Oracle归档日志异常增长问题的排查过程 转载 : https://blog.csdn.net/3moods/article/details/132031152

时间:2024-08-02 11:27:46浏览次数:17  
标签:CHAR 3moods 归档 blog HH24 TIME 日志 132031152 FIRST

Oracle归档日志是Oracle数据库的重要功能,用于将数据库的重做日志文件(Redo Log)保存到归档日志文件(Archive Log)中。归档日志的作用是提供数据库的备份和恢复功能,以及支持数据库的持续性和数据完整性。
当数据库处于归档模式时,数据库引擎会将已经写满的重做日志文件保存到归档日志文件中,而不是覆盖已有的重做日志。这样可以确保数据库的完整性,并且可以使用归档日志文件进行数据库的恢复操作。
归档日志对于数据库的备份和恢复非常重要。通过定期备份归档日志文件,可以保证数据库在发生故障时能够进行恢复。同时,归档日志还允许将数据库恢复到特定的时间点,以满足特定业务需求。

基础操作

在Oracle数据库中,可以通过以下步骤来设置和查看归档日志空间:

  1. 首先,确认数据库是否处于归档模式。可以通过以下SQL语句查询:
SQL> SELECT log_mode FROM v$database;
LOG_MODE
  ARCHIVELOG

如果log_mode的值为ARCHIVELOG,则数据库处于归档模式;如果值为NOARCHIVELOG,则数据库未启用归档模式。

  1. 如果数据库未启用归档模式,可以通过以下SQL语句将其切换到归档模式:
    修改归档模式的操作只能在 mount 状态下进行,不能处于 open 状态
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 3290345472 bytes
Fixed Size                  2180224 bytes
Variable Size            2382367616 bytes
Database Buffers          889192448 bytes
Redo Buffers               16605184 bytes
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
SQL> alter database open;
数据库已更改。
  1. 确认数据库已切换到归档模式后,可以设置归档日志空间的大小。可以通过以下SQL语句设置归档日志空间的大小为50MB(根据需求进行调整):
    52428800 = 50 * 1024 * 1024
SQL> alter system set db_recovery_file_dest_size=  52428800;
系统已更改。
  1. 使用以下SQL语句查询当前归档日志空间的使用情况:
select name,
       space_limit / 1024 / 1024 / 1024 || 'GB' as 空间限制,
       space_used / 1024 / 1024 / 1024 || 'GB' 已使用
  from v$recovery_file_dest

这将显示归档日志目标的名称、空间限制和已使用的空间。

问题发生

下面进入对一次因归档日志空间占满,导致系统停止服务的故障在某个阳光明媚的周末发生后的处理过程。

  1. 系统停止响应,数据库登录有以下提示:
ORA-00257:archiver error. Connect internal only,until freed
  1. 很明显,归档日志满了,立即删除归档日志,保留最近3天。
rman
RMAN> connect target 用户名/密码;
连接到目标数据库: ORCL (DBID=1616110362)
RMAN> delete archivelog until time 'sysdate-3';
  1. 问题未解决,查看归档空间占用情况。
select name,
       space_limit / 1024 / 1024 / 1024 || 'GB' as 空间限制,
       space_used / 1024 / 1024 / 1024 || 'GB' 已使用
  from v$recovery_file_dest
  1. 发现占用空间未释放,接着删除所有归档:
RMAN> delete archivelog all;
  1. 系统恢复。过了几个小时,问题再次发生。
  2. 再次删除所有归档日志,系统恢复,开始排查问题原因。

排查过程

  1. 按天统计
select to_char(COMPLETION_TIME, 'yyyymmdd'), count(*)
  from v$archived_log t
 where COMPLETION_TIME > sysdate - 7
 group by to_char(COMPLETION_TIME, 'yyyymmdd')
 order by to_char(COMPLETION_TIME, 'yyyymmdd');

这是一个查询语句,用于查询过去7天内完成的归档日志数量,并按照日期进行分组和排序。
发现前6天正常,当天归档日志异常增长。
2. 按小时统计

select to_char(FIRST_TIME, 'yyyymmddhh24'), count(*)
  from sys.v_$archived_log t
 where t.FIRST_TIME > trunc(sysdate)
 group by to_char(FIRST_TIME, 'yyyymmddhh24')
 order by to_char(FIRST_TIME, 'yyyymmddhh24')

该SQL用于查询当天开始的归档日志数量,并按照小时进行分组和排序。
3. 按天和小时综合统计

SELECT    TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999') "01",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999') "02",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999') "03",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999') "04",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999') "05",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999') "06",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999') "07",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999') "08",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999') "09",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999') "10",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999') "11",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999') "12",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999') "13",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999') "14",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999') "15",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999') "16",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999') "17",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999') "18",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999') "19",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999') "20",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999') "21",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999') "22",
                TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999') "23"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD') 
ORDER BY 1 DESC;

此SQL语句,用于统计每天每个小时的日志数量,并按照日期倒序排序
3. 根据按小时统计分析,发现归档日志集中在当天2个时间段,其他时间段基本正常。怀疑是在相关时间自动执行的后台任务造成,经深入排查予以否认。
4. AWR报告生成

sqlplus /nolog
conn / as sysdba
@?/rdbms/admin/awrrpt.sql

报告生成失败,原因是没有快照(Snap)
5. 分析没有快照(Snap)原因,网上说一般是SYSAUX表空间不足造成的,查询表空间占用情况,果然满了
6. 清理表空间

select distinct 'truncate table ' || segment_name || ';',
                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 > 100
 order by s.bytes / 1024 / 1024 desc;

此SQL可生成清理以 ‘WRH$’ 开头的、大于100MB的表的SQL。生成后执行,完成表空间清理。

  1. 问题解决,真是阴差阳错。

猜测的原因:
因SYSAUX表空间满,造成连锁反应,表现为归档日志异常增长。

一般情况分析

归档日志增长一般是DML操作大量数据造成的,而由SYSAUX表空间满的原因所造成的则比较少见,故记之。

排查归档日志暴增的方法,一般包括以下三个手段:

  1. SQL语句
  2. AWR
  3. 挖掘归档日志

标签:CHAR,3moods,归档,blog,HH24,TIME,日志,132031152,FIRST
From: https://www.cnblogs.com/zclzc/p/18338392

相关文章

  • 在AWS Lightsail建立WordPress Multisite & Route 53 subdomains & Hexo Blog & WordP
    1.0前言玩Startup比賽,因需高效快速地做POC原型產品,所以利用AWS云端服務來更快地開發。你會學到:LightSail建立WordpressmultisiteRoute53註冊WordpressSubdomains&GithubCuostomDomainLightSailCustomDomain&SSLHexo快速搭建GihubPages博客+ Route53 Custom......
  • zookeeper、kafka单机版安装 https://www.cnblogs.com/dogleftover
    zookeeper、kafka单机版安装前提已经安装了jdk1.8、zookeeper3.6.3主机名:master映射:192.168.128.129master安装zookeeper单机版#将apache-zookeeper-3.6.3-bin.tar.gz上传到服务器#解压tar-zxvfapache-zookeeper-3.6.3-bin.tar.gz#移动mv/home/apache-zookeeper-3......
  • 【Blog1】PyCharm写hello world
    创建项目选择项目类型和项目目录设置python编译器选择编译器新建Python文件编写代码并运行运行运行结果......
  • 【Blog2】PyCharm的基本使用
    工程目录打开和隐藏PyCharm导航栏区域显示和隐藏导航栏打开PyCharm设置设置主题设置字体大小......
  • 小白的第一篇blog
    Markdown学习1.标题要写标题可用#加空格,再下字,然后再用回车键。2.字体1.粗体打法:在文字两侧加两个*如:helloworld!2.斜体打法:在文字两侧加一个*如:helloworld!3.既要斜体又要粗体打法:在文字两侧加三个*如helloworld!4.划线打法:在文字两侧加两个~~如:helloworld!3.引......
  • pg 关于表膨胀 转发:https://www.cnblogs.com/lottu/p/14549463.html
    对于PostgreSQL处理MVCC(数据文件中新增tuple)的方式;相比其他数据库(Oracle、Mysql)而言;更容易触发表/索引膨胀。因为update操作也会影响表膨胀的问题。PostgreSQL处理的方式是对表autovacuum,vacuum是不会降低水位线。能避免表、索引膨胀。vacuumfull,reindex才会降低水位线。当然......
  • 使用 GitHub 跟踪博文数据,并自动同步到 cnblogs
    导读开通了博客园的VIP发现了会员服务中的GitHub跟踪和同步博文数据的功能,测试发现习惯使用GitHub的话,这个还是一个挺不错的功能。本文主要分两部分,一是介绍说明如何使用该功能,并做相关个人的经验分享,二是作为个人GitHub同步cnblogs的文章的索引教程:如何使用适合人......
  • Linux系统搭建轻量级个人博客VanBlog并一键发布公网远程访问
    文章目录前言1.Linux本地部署2.VanBlog简单使用3.安装内网穿透4.创建公网地址5.创建固定公网地址前言今天和大家分享如何在LinuxUbuntu系统搭建一款轻量级个人博客VanBlog,并结合cpolar内网穿透软件生成公网地址,轻松实现随时随地远程访问本地部署的站点。无需......
  • blog 收藏
    数学质数表高斯消元数论、数学—常见定理、结论、性质汇总一文搞懂裴蜀、扩欧、乘法逆元、中国剩余定理隔板法题型整理斐波那契数列的性质整理欧拉函数多项式计数杂谈杂项在线O(1)逆元数据结构链剖分fhqLCT总结概念LCT总结应用线段树字符串SAM随机化退火图......
  • 博客园被百度搜索降权一事的来龙去脉分析 —— 百度减少收录cnblogs博文
    相关事情:蜘蛛的依旧疯狂与园子的新畅想:尝试放出被屏蔽的百度蜘蛛网段再次尝试放出被屏蔽的百度蜘蛛网段面对百度的无期徒刑,幸好还有微软的必应百度搜索引擎是国内搜索引擎的一哥,平时素来霸道,到时也是对谁都霸道的那种。cnblogs虽然是技术博客中的顶流,但是由于没有很好的......