oracle归档日志处理方法
归档日志如果不定期删除,会越来越多,有很大部分在备份或者过期后都是无用的,只会浪费大量磁盘空间,所以需要定期删除。因为archivelog的相关信息是记录在controlfile中的,并且可以从相关的动态视图(例如v$archived_log)查询,archivelog文件从OS层直接物理删除后,该部分日志仍然标注为存在,也就是说Oracle并不认为这些日志被删除了,所以在删除archivelog的时候,需要做一些设置。
一、OS层定期删除
1、物理删除archivelog
2、进入RMAN
[oraarp@bjb ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Feb 14 16:31:19 2019
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ARPDB (DBID=3035516155)
3、检查所有归档日志
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=606 devtype=DISK
validation failed for archived log
archive log filename=/arp/oraarp/archivelog/1_7638_704132477.dbf recid=4735 stamp=998460520
validation failed for archived log
archive log filename=/arp/oraarp/archivelog/1_7639_704132477.dbf recid=4736 stamp=998478034
validation succeeded for archived log
archive log filename=/arp/oraarp/archivelog/1_7640_704132477.dbf recid=4737 stamp=998517613
validation succeeded for archived log
·······
3、列出失效的归档日志
RMAN> list expired archivelog all;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
4735 1 7638 X 24-JAN-19 /arp/oraarp/archivelog/1_7638_704132477.dbf
4736 1 7639 X 25-JAN-19 /arp/oraarp/archivelog/1_7639_704132477.dbf
4、删除失效的归档日志
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=606 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
4735 1 7638 X 24-JAN-19 /arp/oraarp/archivelog/1_7638_704132477.dbf
4736 1 7639 X 25-JAN-19 /arp/oraarp/archivelog/1_7639_704132477.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/arp/oraarp/archivelog/1_7638_704132477.dbf recid=4735 stamp=998460520
deleted archive log
archive log filename=/arp/oraarp/archivelog/1_7639_704132477.dbf recid=4736 stamp=998478034
Deleted 2 EXPIRED objects
RMAN>
OS删除命令,以及rman命令,可以都放在shell脚本中,定期执行。以下是rman命令脚本
#!/bin/bash
DATE=`date +%Y%m%d%H`
source /home/oracle/.bash_profile
$ORACLE_HOME/bin/rman log=/u01/app/rman_${DATE}.log <<EOF
connect target/
run{
crosscheck archivelog all;
delete noprompt expired archivelog all;
}
exit;
EOF
exit
二、RMAN直接删除
RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; //删除七天前的归档
RMAN>DELETE ARCHIVELOG FROM TIME 'SYSDATE-7'; //删除七天到现在的归档(慎用)
三、RMAN备份后,直接删除归档
RMAN>backup format '/u01/arch_%T_%s_%U' archivelog all delete input;