在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#;
