在Oracle中，如何定时删除归档日志文件？

在Oracle中，如何定时删除归档日志文件？

适用于:

目标

解决方案

参考

APPLIES TO:

SYMPTOMS

CAUSE

SOLUTION

REFERENCES

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

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

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 # 启动、停止或重启服务

一.在主库或备库查询哪些归档日志已经应用到备库，这些日志可以被删除了：

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

配置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$archived_log a, 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 (c) 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 (c) 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

About Me

........................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（ http://blog.itpub.net/26736162 ）、博客园（ http://www.cnblogs.com/lhrbest ）和个人weixin公众号（ xiaomaimiaolhr ）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162

● 本文博客园地址： http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号： 230161599 （满） 、618766405

● weixin群：可加我weixin，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（ 646634621 ），注明添加缘由

● 于 2018-11-01 06:00 ~ 2018-11-31 24:00 在魔都完成

● 最新修改时间：2018-11-01 06:00 ~ 2018-11-31 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

........................................................................................................................

● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/

● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/

........................................................................................................................

使用 weixin客户端 扫描下面的二维码来关注小麦苗的weixin公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）、添加小麦苗weixin，学习最实用的数据库技术。

........................................................................................................................

适用于:

目标

解决方案

参考

APPLIES TO:

SYMPTOMS

CAUSE

SOLUTION

REFERENCES

更多相关文章

随机推荐