在Oracle中，如何定時刪除歸檔日誌檔案？

lhrbest發表於2018-11-27
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

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， 學習最實用的資料庫技術。

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

歡迎與我聯絡

 

 



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2221765/，如需轉載，請註明出處，否則將追究法律責任。

相關文章