定时删除归档日志文件

对于单实例的数据库可以使用如下的脚本：

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
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=KaTeX parse error: Expected group after '_' at position 18: …OG_DIR}/OCPLHR1_̲{DATEL}".log"

$ORACL{E}_{H}OME/bin/rmanlog=$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环境，需要删除已经应用到备库的归档日志，可以使用如下的脚本，在主备库都需要部署：
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
32
33
34
35
36
37
38
39
40
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=KaTeX parse error: Expected group after '_' at position 20: …_DIR}/deladgarc_̲{ORACLE_SID}$DATEL".log"SQ{L}_{N}AME=${LOG_DIR}/deladgarcKaTeX parse error: Expected group after '_' at position 13: {ORACLE_SID}_̲{DATEL}".sql"
LINK_NAME=tns_htzxdbphy
$ORACL{E}_{H}OME/bin/sqlplus-Ssys/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
$ORACL{E}_{H}OME/bin/rmanlog=$LOG_NAME target sys/oracle@$LIN{K}_{N}AME<<EOFcrosscheckarchivelogall;deletenopromptexpiredarchivelogall;@${SQL_NAME}
exit;
EOF
2 、赋可执行权限

chmod +x /home/oracle/lhr/deladgarc_lhr.sh

3 、设定定时任务，在 Oracle 用户下，编辑配置文件

crontab -e

配置文件内容（每天下午 5 点执行删除任务）：

0 17 * * * /home/oracle/lhr/deladgarc_lhr.sh

确保 crond 服务处于启动状态：

ps -ef | grep crond # 判断定时服务是否启动

service crond start|stop|restart # 启动、停止或重启服务

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
32
33
34
35
36
37
38
39
40
一.在主库或备库查询哪些归档日志已经应用到备库，这些日志可以被删除了：
SELECT THREAD#,
NAME,
SEQUENCE#,
ARCHIVED,
APPLIED,
A.NEXT_CHANGE#,
A.COMPLETION_TIME,
'delete archivelog sequence ’ || A.SEQUENCE# || ’ thread ’ ||
A.THREAD# || ‘;’ EXEC_SQL
FROM VKaTeX parse error: Expected 'EOF', got '#' at position 32: …HERE (A.THREAD#̲, A.SEQUENCE#, …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 VKaTeX parse error: Expected 'EOF', got '#' at position 33: …HERE A.SEQUENCE#̲ >= (SELECT MAX…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#;

配置RMAN清除已应用到备库的归档日志 (文档 ID 1577382.1)
文档内容

目标

解决方案

参考

适用于:
Oracle Database - Enterprise Edition - 版本 10.2.0.1 到 10.2.0.5 [发行版 10.2]
本文档所含信息适用于所有平台
目标
我们需要利用RMAN自动从FRA清除已经应用到备库的归档日志。

1. Bug 6216036:

在10g中，使用CONFIGURE命令配置的归档日志删除策略
仅适用于自动删除闪回恢复区的归档日志。
其他的删除机制，如DELETE ARCHIVELOG或DELETE INPUT不遵守这个配置。

2)APPLIED ON STANDBY - 启用闪回恢复区删除已应用在属性为mandatory的备库的归档日志。

更多信息请查看 Oracle Data Guard Concepts and Administration

http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta015.htm#sthref289

在11g此限制已解除。

解决方案
DataGuard的配置中，当数据库满足下列条件时，FRA下的归档日志将被自动清除。

1. 11g之前，如果不使用mandatory ARCHIVELOG目标，数据库（主库和备库）必须更改下面的参数并重新启动：

SQL> alter system set “_log_deletion_policy”=‘ALL’ scope=spfile;

2. 在RMAN中配置下列参数（备库）：

   RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

警告：对于10g，如果设置了APPLIED ON STANDBY, 即使FRA 中的文件没有备份也会被清除。

11g: 从11g 开始，我们对归档日志删除策略的配置进行了增强，包含了 APPLIED ON [ALL] STANDBY [BACKED UP n TIMES TO DEVICE TYPE ] 选项。该选项确认归档日志已经被应用，并且在主库进行过备份后才可以被清除。

3. 归档日志必须已应用到备库。运行下面的查询列出所有已应用到备库的归档日志：

select a.thread#, a.sequence#, a.applied
from v$archive{d}_{l}oga,v$database d
where a.activation# = d.activation#
and a.applied=‘YES’
/

4. FRA有空间压力

当归档日志从FRA自动删除，你会看到数据库的alert.log报出以下信息：

Sat Jun 18 01:00:32 2011
Deleted Oracle managed file /opt/app/oracle/FRA/ORA102/archivelog/2011_05_30/o1_mf_1_151_6y71q675_.arc
Deleted Oracle managed file /opt/app/oracle/FRA/ORA102/archivelog/2011_05_31/o1_mf_1_151_6y87pzg4_.arc

参考
NOTE:331924.1 - RMAN backups in Max Performance/Max Availability Data Guard Environment
BUG:6216036 - RMAN+DG ARCHIVELOG DELETION POLICY APPLIED ON STANDBY NOT RESPECTED
NOTE:740322.1 - RMAN Archived Redo Logs Are Deleted Before Being Applied at Standby Database

Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (文档 ID 1984064.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
SYMPTOMS
When attempting to configure the RMAN archivelog deletion policy in a Data Guard environment on Primary RMAN reports the following warning:

RMAN> configure archivelog deletion policy to applied on standby;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored
RMAN-08591: WARNING: invalid archived log deletion policy

CAUSE
The RMAN archivelog deletion policy determines when archivelogs stored in the fast recovery area (FRA) may be removed from the database fast recovery area automatically when the database starts experiencing space pressure in the FRA.

In cases where the RMAN archivelog deletion policy is being set to “applied on standby” at least one of the destination standby site(s) MUST be set as a “MANDATORY” destination

SOLUTION
The solution to this warning is to change the archivelog deletion policy to APPLIED ON ALL STANDBY in cases where you only have one remote archive destination.

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

PLEASE NOTE : YOUR PRIMARY DATABASE MAY HANG IN A SITUATION WHERE THE REMOTE DESTINATION SET IS THE ONLY ARCHIVE DESTINATION AND BECOMES UNAVAILABLE SAY AS A RESULT OF A STANDBY SERVER CRASH OR INTERMITTENT NETWORK RELATED PROBLEMS

To eliminate the RMAN warning message when setting the deletion policy for archivelogs, at least one destination must be set as a mandatory destination.

If dataguard broker is configured, this change MUST be performed using the broker.

The following process details this:

1. Log in to the broker command line utility

[oracle@grid2vm1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright © 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect /
Connected as SYSDG.

2. Locate the name of the standby site you want to set as a mandatory destination

DGMGRL> show configuration;

Configuration - dg_db121

Protection Mode: MaxPerformance
Members:
db121 - Primary database
db121stb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 39 seconds ago)

3. For the standby site locate the “Binding” property in the sites broker configuration

DGMGRL> show database verbose db121stb

Database - db121stb

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 14.00 KByte/s
Active Apply Rate: 594.00 KByte/s
Maximum Apply Rate: 5.06 MByte/s
Real Time Query: OFF
Instance(s):
db1211 (apply instance)
db1212

Properties:
DGConnectIdentifier = ‘db121_stb’
ObserverConnectIdentifier = ‘’
LogXptMode = ‘ASYNC’
RedoRoutes = ‘’
DelayMins = ‘0’
Binding = ‘optional’ ****** controls whether the destination is mandatory or not
…
.

Database Status:
SUCCESS

Also, you can verify BINDING column of the V$ARCHIVE_DEST view of the database or far sync instance that is sending redo data using SQLPLUS by running below script

SQL > select binding from v$archive_dest;

4. Set the “Binding” property to MANDATORY
   DGMGRL> edit database db121stb set property Binding=‘mandatory’;
   Property “binding” updated

DGMGRL> exit

OR using SQLPLUS if there is no broker configured

SQL>alter system set log_archive_dest_2=‘service=ORACLE MANDATORY lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=LARRY’;

system altered

5. Once the broker configuration has set a standby site as a mandatory destination, the RMAN configuration can be altered to set the archivelog deletion policy to applied on standby.

[oracle@grid2vm1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Feb 26 17:43:27 2015

Copyright © 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: DB121 (DBID=120781903)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB121 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
…
.
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_db1211.f’; # default

RMAN> configure archivelog deletion policy to applied on standby;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored

REFERENCES
http://docs.oracle.com/database/121/DGBKR/dbpropref.htm#DGBKR845
NOTE:728053.1 - Configure RMAN to purge archivelogs after applied on standby
http://docs.oracle.com/database/121/RCMRF/rcmsynta009.htm#r2c1-t43