ITPub博客

首页 > 数据库 > Oracle > Oracle数据库日常问题-归档异常增长

Oracle数据库日常问题-归档异常增长

原创 Oracle 作者:chenoracle 时间:2020-02-14 22:18:06 0 删除 编辑

Oracle 数据库日常问题 - 归档异常增长

 

数据库启用归档模式,主要是保证数据安全,但是如果归档增长过快,或者人员维护不合理,可能会导致归档文件把磁盘占满,最终数据库无法正常工作;

数据库归档增长异常,最终导致数据库无法使用,如何查找原因,解决问题呢?

当出现归档空间不足 ,首先需要 通过 扩空间或者移动 ( 删除 ) 部分归档文件释放空间,尽快让数据库正常工作;

数据库可以后,再去 具体分析归档文件增长过快的原因;

归档空间满了,在删除归档之前需要确定归档所在目录(archive log list );

如果归档文件放在默认的闪回区,必须通过RMAN delete 命令进行删除归档,或者直接通过命令扩大闪回区大小,不能通过操作系统命令直接删除闪回区下的归档文件;

如果归档文件存放路径是手动指定的其他目录,非闪回去,除了RMAN 删除归档外,也可以通过操作系统命令移动或删除归档文件;

1 删除过期归档

删除过期(expired) 的归档,释放空间;

RMAN> crosscheck archivelog all;

RMAN> list expired archivelog all;

RMAN> delete expired archivelog all;

删除指定时间归档

RMAN>delete archivelog until time 'sysdate-7';  

删除废弃(obsolete) 的归档,释放空间;

RMAN> report obsolete;

RMAN> delete obsolete;

扩大归档所在空间( 闪回区 )

select  dbid , name , log_mode from  v$database ;

SQL> archive log list;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      D:\app_10.2.0.4\Administrator\flash_recovery_area

db_recovery_file_dest_size           big integer 2530M

select   *   From  v$flash_recovery_area_usage ;

修改闪回 区大小 alter system set db_recovery_file_dest_size = 4G (更改大小)

删除部分归档后,数据库就可以正常工作了,这时需要具体分析归档过快的原因;

首先需要知道每天( 每小时 ) 归档产生频率和大小;

如果每天大多数归档文件都某个特定时间内产生的,那么可能是这段时间有定时JOB ,或者计划任务,查看一下这些 JOB 和计划任务是否合理;

如果每天的每秒每分钟都在不停的产生归档,很可能产品或者数据库存在BUG ,需要具体分析产生归档的 SQL 语句,才能和业务操作联系起来;

如果都是工作时间内产生的归档,可能是正常业务操作产生的归档,具体分析业务操作对应的表,SQL 信息等,通常情况需要增加存储空间;

2 分析归档过快原因

查看归档参数频率

查看数据库JOB

查看计划任务

---- 查看数据库归档分布及频率

3 查找归档增长异常常见方法

一:日志挖掘

分析多个归档文件中SQL 信息

1. Enable Supplemental Logging

2. Extract a LogMiner Dictionary  (unless you plan to use the online catalog)

3. Specify Redo Log Files for Analysis

4. Start LogMiner

5. Query V$LOGMNR_CONTENTS

6. End the LogMiner Session

二:AWR 报告

Segments by DB Blocks Changes 结合 TOP SQL 进行分析

归档异常增长 案例

问题原因: 11g 数据库自动维护任务 - 段指导 BUG 导致归档增长过快。

现象: 平时每天归档5G 左右,突然有一天 产生200 G 归档

分析过程: 先通过SQL 查看全天中每小时归档量,找出归档最集中的时间段,并收集这一时间段的 AWR 报告,或通过日志挖掘分析这一时间段的归档文件。

发现大多数归档文件生成时间特别集中,收集这段时间AWR 报告即可。

通过AWR 报告查找归档异常增长原因

查看问题期间AWR 报告,发现有一条 CTAS 语句特别耗时

SQL 语句如下:

1 call dbms_space.auto_space_advisor_job_proc ( )

2 create table “XXX".DBMS_TABCOMP_TEMP_UNCMP tablespace NNC_DATA02 as select /*+ full(“ CHENJCH ".SM_PUB_FILESYSTEM)*/ * from “ CHENJCH ".SM_PUB_FILESYSTEM sample block( 41)

其中 SM_PUB_FILESYSTEM 表是一张附件表,包含blob 字段,大小 200 G ,通过 DBMS_SCHEDULER 可知这条耗时耗空间的语句是 Oracle 自动执行到了。

解决方案:

11g 数据库,自动维护任务 - 段指导 BUG 导致归档过快 ,禁用段指导。

begin

  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',

                               operation   => NULL,

                               window_name => NULL);

end;

详细信息请参考:

How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP? (Doc ID 1326118.1)

归档异常增长 案例

问题原因: 应用程序 产品 BUG 导致归档 增长异常。

问题现象: 同事反馈,一个小系统,全库大小不到10G ,但是启动归档模式后,发现每天产生的归档文件有 300G 大小。

分析过程: 先通过SQL 查看全天中每小时归档量,找出归档最集中的时间段,并收集这一时间段的 AWR 报告,或通过日志挖掘分析这一时间段的归档文件。

发现每小时每分钟都在不断生成大量归档文件。

查看AWR 报告:发现产生归档的对象主要集中在 ARAP_BALANCE2 对象上。

查看TOP SQL ,找出 ARAP_BALANCE2 相关 DML DDL 语句。

每分钟都会执行大量如下SQL ,通过 arap_balance2 表定位到具体的业务,和对应业务开发对接查找SQL 产生的原因。

1. delete from arap_balance2 where dr = 9

2. update arap_balance2 set dr = 9

3. i nsert into arap_balance2 ( pk_balance, compondmd5, pk_group, pk_org, accperiod, pk_currtype, objtype, billclass, billstatus, effectstatus, customer, supplier, pk_deptid, pk_psndoc, def1, def2, def3, def4, def5, def6, accyear, quantity_de, quantity_cr, money_de, money_cr, local_money_de, local_money_cr, grouplocal_money_de, grouplocal_money_cr, globallocal_money_de, globallocal_money_cr ) values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 )

解决方案:产品BUG ,研发提供补丁


归档异常增长 案例

问题原因:数据库JOB 执行太频繁;

问题现象:每分钟都有归档产生


平均每天产生 60G 归档文件

查看当前正在执行的SQL

Select * from v$sql where address in (select sql_address from v$session);

日志挖掘

查看数据库job

问题原因:

JOB 每分钟执行一次,调用存储过程, 频繁 delete,insert 大量 数据,当存储过程在一分钟之内执行不完时,下一个JOB 又开始调用存储过程,导致 delete,insert 秒都在执行,造成归档疯涨;

解决方案:

客户反馈这个JOB 对应的应用已经不用了,但是数据库 JOB 没有及时停掉,手动停掉这个 JOB 即可。


归档异常增长 案例

问题原因:预算业务产生大量归档文件

查找原因:

查看归档频率及归档分布,查看JOB 以及后台计划任务

日挖掘多个归档日志

找出执行次数多的SQL

分析SQL 由来

跟踪表的变化

 

查看归档频率及归档分布,查看JOB 以及后台计划任务

---- 查看数据库归档分布及频率

发现归档产生并没有集中在某一小时或某一小段时间内,可能和备份计划任务(0点)和JOB关系不大。

--- 查看后台 job

select  job , schema_user , last_date , next_date , broken , interval   from  dba_jobs ;

日志挖掘多个归档日志

--- 查看每天归档大小

select   sum ( block_size *  blocks )   /   1024   /   1024   /   1024  "size(GB)" ,

       to_char ( first_time ,   'yyyymmdd' )

   from  v$archived_log

  group   by  to_char ( first_time ,   'yyyymmdd' )

  order   by   2   desc ;

--- 查看归档大小及目录名

select  block_size *  blocks /   1024   /   1024  "size(M)" ,

        name ,

       first_time ,

       next_time ,

       creator

   from  v$archived_log a

  order   by  first_time desc ;

--- 日志挖掘

(分析连续的几个归档)

SQL >   EXECUTE  DBMS_LOGMNR.ADD_LOGFILE ( LogFileName => '/u01/app/oracle/archive/1_5673_863636484.dbf' , Options => dbms_logmnr.new );

SQL >   exec  dbms_logmnr.start_logmnr ( options => dbms_logmnr.dict_from_online_catalog );

SQL >   create   table  test1 as   select   *   from  v$logmnr_contents ;

SQL >   exec  dbms_logmnr.end_logmnr ;

--- 查看归档中执行次数多的 SQL

select   count (*), substr ( sql_redo , 1 , 100 )   from  test.test1 group   by   substr ( sql_redo , 1 , 100 )   order   by   1   desc ;

--- 查看 sql_redo 为空归档的分布

select   count (*), data_obj# from  test.test1 where  sql_redo is   null   group   by  data_obj# order   by   1   desc ;

/* 发现信息主要分布在两个 data_obj# */

--- 通过 data_obj# 查询具体的对象

select   *   from  dba_objects where  data_object_id in   ( '92468' , '92467' );

--- 查看所属段

select   *   from  dba_segments where  segment_name in ( 'SYS_LOB0000092466C00006$$' , 'SYS_IL0000092466C00006$$' );

--- 查询具体对象类型( OBJECT_TYPE LOB

select   *   from  dba_lobs where  SEGMENT_NAME = 'SYS_LOB0000092466C00006$$' ;

--- 查询具体对象类型( OBJECT_TYPE index  

select   *   from  dba_indexes where  index_name = 'SYS_IL0000092466C00006$$' ;

--- 查看 tb_taskshtmodel 段大小(6.25M)

select  bytes / 1024 / 1024 , a. *   from  dba_segments a where  owner = 'NC63PROD'   and  segment_name = 'TB_TASKSHTMODEL' ;

--- 查看 tb_taskshtmodel SHEETMODEL 列的 blob SYS_LOB0000092466C00006$$ 大小(47G)

select  bytes / 1024 / 1024 / 1024 , a. *   from  dba_segments a where  owner = 'NC63PROD'   and  segment_name = 'SYS_LOB0000092466C00006$$' ;

-- 查看 tb_taskshtmodel SHEETMODEL 列的 blob SYS_LOB0000092466C00006$$ 对应段索引 SYS_IL0000092466C00006$$ 的大小(70.5M)

select  bytes / 1024 / 1024 , a. *   from  dba_segments a where  owner = 'NC63PROD'   and  segment_name = 'SYS_IL0000092466C00006$$' ;

---- 查看 LOB 字段大小分布

select   max ( dbms_lob.getLength ( sheetmodel )   /   1024   /   1024 ),

        min ( dbms_lob.getLength ( sheetmodel )   /   1024   /   1024 ),

        avg ( dbms_lob.getLength ( sheetmodel )   /   1024   /   1024 )

   from  nc63prod.tb_taskshtmodel ;

结论:tb_taskshtmodel 表段 6.25M tb_taskshtmodel SHEETMODEL 列的 blob SYS_LOB0000092466C00006$$ 47G, tb_taskshtmodel SHEETMODEL 列的 blob SYS_LOB0000092466C00006$$ 对应段索引 SYS_IL0000092466C00006$$ 70.5M。

--- 查看 TB_TASKSHTMODEL 数据量

select   count (*)   from  nc63prod.TB_TASKSHTMODEL ;

---tb_taskshtmodel sheetmodel 字段总大小(44.3G)

select   sum ( a )   /   1024   /   1024   /   1024

   from   ( select  dbms_lob.getLength ( sheetmodel )  a

           from  nc63prod.tb_taskshtmodel );

--- 查看 tb_taskshtmodel 字段11月7日大小

select   sum ( a )   /   1024   /   1024   /   1024

   from   ( select  dbms_lob.getLength ( sheetmodel )  a

           from  nc63prod.tb_taskshtmodel

          where  ts like   '2015-11-07%' );

如果有delete 操作,会产生更多归档,

通过JOB 来跟踪 tb_taskshtmodel 表每分钟数据量,从而推测出 表插入和删除的操作 次数。

=================

2015-11-05 08:54

=================

---1

create   table  c_test ( t_date date   default   sysdate , t_count number );

---2

create   or   replace   procedure  p_test as

begin

   insert   into  c_test

     select   sysdate ,   count ( 1 )   from  TB_TASKSHTMODEL ;

   commit ;

end ;

 

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as nc63prod

 

SQL> show parameter aud

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      /u01/app/oracle/admin/NCPROD/adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string      

audit_trail                          string      NONE

 

SQL> variable job1 number;

SQL> begin

  2  dbms_job.submit(:job1,'p_test;',sysdate,'sysdate+1/1440');

  3  end;

  4  /

 

PL/SQL procedure successfully completed

job1

---------

23

 

select  job ,  what from  dba_jobs ;

SQL> begin

  2  dbms_job.run(23);

  3  end;

  4  /

PL/SQL procedure successfully completed

---

select   *   from   nc63prod.c_test order   by   1   desc ;

--- 查看 nc63prod.TB_TASKSHTMODEL 删除情况

select   *   from  nc63prod.c_test a , nc63prod.c_test b where  a.t_date > b.t_date and  a.t_count < b.t_count order   by   3 ;

结论: nc63prod.TB_TASKSHTMODEL 24小时内插入18000行数据,删除583条数据

---删除JOB

=================

2015-11-06 08:56

=================

SQL> begin

  2  dbms_job.remove(23);

  3  end;

  4  /

PL/SQL procedure successfully completed

 

解决方案:

审计也会产生一部分归档文件,如果不使用审计,建议关闭。

1 、查看审计功能是否开启

SQL> show parameter audit

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      /u01/app/oracle/admin/NCPROD/adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string      

audit_trail                          string       DB

说明:VALUE 值为 DB ,表面审计功能为开启的状态

2 、关闭 oracle 的审计功能

SQL> alter system set audit_trail= none  scope=spfile;

System altered.

3 、重启数据库 生效

SQL> shutdown immediate;

SQL> startup;

 

减少 "NC63PROD"."TB_TASKSHTMODEL" 表产生日志

具体操作:

(1)   数据库里执行

alter   table   NC63PROD.TB_TASKSHTMODEL   nologging ;

(2) 开发人员将

insert into "NC63PROD"."TB_TASKSHTMODEL"

更改为

insert   /*+ append */   into   "NC63PROD"."TB_TASKSHTMODEL"

 

注意: append+nologing 存在的风险,会导致rman 恢复这个表时出现问题,需要充分评估是否有必要使用此方案。

欢迎关注我的微信公众号"IT小Chen",共同学习,共同成长!!!


来自 “ ITPUB博客 ” ,链接:/29785807/viewspace-2675645/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP、Oracle 11g OCM、OCMU 用户组成员,微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    290
  • 访问量
    820549