数据库启用归档模式,主要是保证数据安全,但是如果归档增长过快,或者人员维护不合理,可能会导致归档文件把磁盘占满,最终数据库无法正常工作;
数据库归档增长异常,最终导致数据库无法使用,如何查找原因,解决问题呢?
1 当出现归档空间不足 ,首先需要 通过 扩空间或者移动 ( 删除 ) 部分归档文件释放空间,尽快让数据库正常工作;
2 数据库可以后,再去 具体分析归档文件增长过快的原因;
归档空间满了,在删除归档之前需要确定归档所在目录(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.4Administratorflash_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.
2. (unless you plan to use the online catalog)
3.
4.
5.
6.

二: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 恢复这个表时出现问题,需要充分评估是否有必要使用此方案。

作者:chenoracle

最后修改:2022 年 02 月 27 日
如果觉得我的文章对你有用,请随意赞赏