在Oracle中，如何定时删除归档日志文件？
在Oracle中，如何定时删除归档日志文件？
♣
题目部分
在Oracle中，如何定时删除归档日志文件？
答案部分
对于单实例的数据库可以使用如下的脚本：
1、在Oracle用户下，创建归档日志删除文件del_OCPLHR1_arch.sh
文件位置：/home/oracle/crontabOra，内容如下：
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=OCPLHR1
export NLS_LANG="american_america.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
LOG_DIR=/home/oracle/crontabOra/log
DATEL=`date '+%Y-%m-%d'`
LOG_NAME=${LOG_DIR}/OCPLHR1_${DATEL}".log"
$ORACLE_HOME/bin/rman log=$LOG_NAME target sys/lhr@OCPLHR1 <<EOF
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-6';
exit;
EOF
2、赋可执行权限
chmod +x del_OCPLHR1_arch.sh
3、设定定时任务，在Oracle用户下，编辑配置文件
crontab -e
配置文件内容（每天下午5点执行删除任务）：
0 17 * * * /home/oracle/crontabOra/del_OCPLHR1_arch.sh
确保crond服务处于启动状态：
ps -ef | grep crond #判断定时服务是否启动
service crond start|stop|restart #启动、停止或重启服务
对于DG环境，需要删除已经应用到备库的归档日志，可以使用如下的脚本，在主备库都需要部署：
mkdir -p /home/oracle/lhr/log
more /home/oracle/lhr/deladgarc_lhr.sh
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/18.3.0/dbhome_1
export ORACLE_SID=htzxdb1
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
LOG_DIR=/home/oracle/lhr/log
DATEL=`date '+%Y-%m-%d'`
LOG_NAME=${LOG_DIR}/deladgarc_${ORACLE_SID}_${DATEL}".log"
SQL_NAME=${LOG_DIR}/deladgarc_${ORACLE_SID}_${DATEL}".sql"
LINK_NAME=tns_htzxdbphy
$ORACLE_HOME/bin/sqlplus -S sys/oracle@${LINK_NAME} as sysdba <<EOF
set feedback off heading off pagesize 0 linesize 100
col exec_sql format a50
spool ${SQL_NAME}
SELECT 'delete archivelog sequence ' || A.SEQUENCE# || ' thread ' || A.THREAD# || ';' EXEC_SQL
FROM V\$ARCHIVED_LOG A
WHERE (A.THREAD#, A.SEQUENCE#, a.RESETLOGS_CHANGE#) IN
(SELECT b.THREAD#,
b.SEQUENCE#,
b.RESETLOGS_CHANGE#
FROM V\$ARCHIVED_LOG B
WHERE B.APPLIED = 'YES'
AND b.COMPLETION_TIME <= SYSDATE - 8)
AND a.NAME NOT IN (SELECT b.DESTINATION
FROM v\$archive_dest b
WHERE b.DESTINATION IS NOT NULL)
AND A.COMPLETION_TIME <= SYSDATE - 8
ORDER BY A.THREAD#,
A.SEQUENCE#;
spool off
exit
EOF
$ORACLE_HOME/bin/rman log=$LOG_NAME target sys/oracle@${LINK_NAME} <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
@${SQL_NAME}
exit;
EOF
一.在主库或备库查询哪些归档日志已经应用到备库，这些日志可以被删除了：
SELECT THREAD#,
NAME,
SEQUENCE#,
ARCHIVED,
APPLIED,
A.NEXT_CHANGE#,
A.COMPLETION_TIME,
'delete archivelog sequence ' || A.SEQUENCE# || ' thread ' ||
A.THREAD# || ';' EXEC_SQL
FROM V$ARCHIVED_LOG A
WHERE (A.THREAD#, A.SEQUENCE#, a.RESETLOGS_CHANGE#) IN
(SELECT b.THREAD#,
b.SEQUENCE#,
b.RESETLOGS_CHANGE#
FROM V$ARCHIVED_LOG B
WHERE B.APPLIED = 'YES'
AND b.COMPLETION_TIME <= SYSDATE - 3)
AND a.NAME NOT IN (SELECT b.DESTINATION
FROM v$archive_dest b
WHERE b.DESTINATION IS NOT NULL)
AND A.COMPLETION_TIME <= SYSDATE - 3
ORDER BY A.THREAD#,
A.SEQUENCE#;
---在备库查询归档日志的应用情况
COL NAME FOR A100
SET LINESIZE 9999 PAGESIZE 9999
COL NEXT_CHANGE# FOR 999999999999999
SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
FROM V$ARCHIVED_LOG A
WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
FROM V$ARCHIVED_LOG B
WHERE B.THREAD# = A.THREAD#
AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
AND B.RESETLOGS_CHANGE# =
(SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
AND B.APPLIED = 'YES'
GROUP BY B.THREAD#)
ORDER BY A.THREAD#, A.SEQUENCE#;