Oracle rman 自动清理归档日志

一、背景：

数据库开启归档日志，若不及时清理归档日志，系统就会报

ORA-00257: archiver error. Connect internal only, until freed

有没有自动清理归档脚本，不需要每次都手动清理呢？

当然有，实现自动清理归档日志，可以通过 Linux crontab,Oracle rman。

二、编写删除rman脚本

新建一个脚本clearLog.sh

${ORACLE_HOME}/bin/rman nocatalog log=/home/oracle/del_arch$(date +%Y-%m-%d).log  <<EOF
connect target /
#crosscheck archivelog all;
#delete noprompt expired archivelog all;
DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-11';
exit;
EOF

三、手动测试脚本

赋权：chomd +x clearLog.sh

执行：./clearLog.sh

ok,,查看del_arch.log,能删除归档。

四、添加至corntab

在oracle 用户下执行crontab -e

42 20 * * * /home/oracle/clearLOG.sh

晚上20:42分执行clearLog.sh

五、测试与验证

修改crontab中的clearLog.sh运行时间为系统时候后的1分钟 。

1、查看运行日志： more del_arch文件，并无内容

2、查看cron运行日志

more /var/log/cron

Jul  4 18:40:01 orac1 CROND[632]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Jul  4 18:41:18 orac1 crontab[1415]: (oracle) BEGIN EDIT (oracle)
Jul  4 18:41:37 orac1 crontab[1415]: (oracle) REPLACE (oracle)
Jul  4 18:41:37 orac1 crontab[1415]: (oracle) END EDIT (oracle)
Jul  4 18:42:01 orac1 crond[3515]: (oracle) RELOAD (/var/spool/cron/oracle)
Jul  4 18:42:02 orac1 CROND[1604]: (oracle) CMD (/home/oracle/clearLOG.sh)

发现脚本在指定时间运行了，但为什么没有清除归档？百度一下，发现有两种解决方案

1、使用su - oracle -c

2、添加oracle 环境变量

六、添加oracle 环境变量

切换至oracle 用户

more .bash_profile

将oracle 相关的环境变量粘贴至clearLog.sh文件中

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/db
export ORACLE_SID=orcl1
export ORACLE_UNQNAME=orcl
export ORACLE_TERM=xterm
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/oui/bin:/usr/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/sdbms/jlib
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

${ORACLE_HOME}/bin/rman nocatalog log=/home/oracle/del_arch$(date +%Y-%m-%d).log
  <<EOF
connect target /
#crosscheck archivelog all;
#delete noprompt expired archivelog all;
DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-11';
exit;
EOF

七、再次测试与验证

[oracle@orac1 ~]$ ls
backup       clearLOG.sh_bak  core.14518  del_arch2014-07-04.log
clearLOG.sh  core.14076       core.14561
[oracle@orac1 ~]$ wc -l del_arch2014-07-04.log 
79 del_arch2014-07-04.log
[oracle@orac1 ~]$ more del_arch2014-07-04.log 

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jul 4 20:31:04 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> 
connected to target database: ORCL (DBID=1367727099)
using target database control file instead of recovery catalog

RMAN> 2> 3> 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=174 instance=orcl1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=190 instance=orcl1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=217 instance=orcl1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=113 instance=orcl1 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
68984   1    39462   A 2014-06-23 18:07:21
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_1_seq_39462.2542.851021325

68988   1    39463   A 2014-06-23 18:48:44
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_1_seq_39463.2548.851023995

68992   1    39464   A 2014-06-23 19:33:15
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_1_seq_39464.2555.851027033

68983   2    29516   A 2014-06-23 18:37:56
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29516.2545.851021323

68985   2    29517   A 2014-06-23 18:48:42
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29517.2543.851022017

68986   2    29518   A 2014-06-23 19:00:15
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29518.2546.851022997

68987   2    29519   A 2014-06-23 19:16:37
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29519.2550.851023995

68989   2    29520   A 2014-06-23 19:33:14
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29520.2549.851025009

68990   2    29521   A 2014-06-23 19:50:08
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29521.2552.851025979

68991   2    29522   A 2014-06-23 20:06:18
        Name: +ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29522.2558.851027033

deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_1_seq_39462.2542.851021325 RECID=68984 STAMP=851021324
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_1_seq_39463.2548.851023995 RECID=68988 STAMP=851023995
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_1_seq_39464.2555.851027033 RECID=68992 STAMP=851027033
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29516.2545.851021323 RECID=68983 STAMP=851021322
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29517.2543.851022017 RECID=68985 STAMP=851022017
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29518.2546.851022997 RECID=68986 STAMP=851022998
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29519.2550.851023995 RECID=68987 STAMP=851023995
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29520.2549.851025009 RECID=68989 STAMP=851025010
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29521.2552.851025979 RECID=68990 STAMP=851025980
deleted archived log
archived log file name=+ARV1/orcl/archivelog/2014_06_23/thread_2_seq_29522.2558.851027033 RECID=68991 STAMP=851027032
Deleted 10 objects


RMAN> 

Recovery Manager complete.

ok 祝你好运。