聊聊RMAN的ARCHIVELOG DELETION参数

RMAN是Oracle推出的官方备份还原工具。经过几个大版本的发展，RMAN已经支持多种备份介质和恢复策略的主要工具，也是业界普遍认可是Oracle备份还原官方策略。Archivelog是Oracle备份还原策略的重要组成元素，不完全备份+连续的归档日志可以让我们将数据库恢复到发生故障点，实现数据的无损失恢复。但是，现实生活中archive log给没有经验的运维人员也带来了不少的问题，归档空间占满引起Hang住、瞬间归档日志过多生成引起问题等。一些前辈也在不断强调“归档模式不美好”。在RMAN工作参数中，针对archive log，是可以设置专门的删除策略（Deletion）。在实践领域中，已经备份过或者确保安全传输的归档日志，其实就可以删除了，特别是在有限的Fast Recovery Area管理模式下。对于自动删除archive log的策略，比较常见的是applied to standby和shipped to standby，也就是Data Guard场景下。本篇介绍简单的backed up参数使用情况，并通过一系列实验去研究该参数影响下Oracle和RMAN的工作行为特性。1、基本参数和实验环境笔者使用Oracle 11gR2进行测试，具体版本编号为11.2.0.4。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 – Production默认情况下，archivelog deletion policy参数为NONE。RMAN> show all; RMAN configuration parameters for database with db_unique_name XXXXDB are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default（篇幅原因，有省略……）CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default该参数常见的集中取值如下：configure archivelog deletion policy to backed up 2 times to sbt;configure archivelog deletion policy to backed up 1 times to device type disk;configure archivelog deletion policy to applied on standby; --DG专用configure archivelog deletion policy to shipped on standby; --DG专用configure archivelog deletion policy clear;研究archivelog行为最好的工具视图是v$archived_log。很多DBA喜欢从操作系统层面删除归档日志，但是这种方式是不会直接被Oracle控制文件认可，所以建议使用RMAN或者官方工具来做。--已归档未删除日志SQL> select count（*） from v$archived_log where archived="YES" and deleted="NO"; COUNT（*）---------- 132、第一轮备份测试实验首先我们修改archivelog deletion policy参数，设置为“两次备份后即可以删除”。RMAN> configure archivelog deletion policy to backed up 2 times to device type disk;new RMAN configuration parameters:CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;new RMAN configuration parameters are successfully stored手工备份数据库和归档日志，不进行删除动作。RMAN> backup database plus archivelog;Starting backup at 21-SEP-15current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=16 device type=DISKchannel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log（s） in backup setinput archived log thread=1 sequence=100 RECID=12 STAMP=890690423input archived log thread=1 sequence=101 RECID=13 STAMP=890712061input archived log thread=1 sequence=102 RECID=14 STAMP=890727732input archived log thread=1 sequence=103 RECID=15 STAMP=890776815input archived log thread=1 sequence=104 RECID=16 STAMP=890776833input archived log thread=1 sequence=105 RECID=17 STAMP=890805616input archived log thread=1 sequence=106 RECID=18 STAMP=890814181input archived log thread=1 sequence=107 RECID=19 STAMP=890820201input archived log thread=1 sequence=108 RECID=20 STAMP=890859629input archived log thread=1 sequence=109 RECID=21 STAMP=890892046input archived log thread=1 sequence=110 RECID=22 STAMP=890900632input archived log thread=1 sequence=111 RECID=23 STAMP=890906655input archived log thread=1 sequence=112 RECID=24 STAMP=890942416input archived log thread=1 sequence=113 RECID=25 STAMP=890990204channel ORA_DISK_1: starting piece 1 at 21-SEP-15channel ORA_DISK_1: finished piece 1 at 21-SEP-15piece handle=/u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T091644_bzypmwty_.bkp tag=TAG20150921T091644 （篇幅原因，省略部分……）Finished Control File and SPFILE Autobackup at 21-SEP-15此时，归档日志被备份，并且没有删除。--多出来的两个是由于进行备份时候自动会有switch logSQL> select count（*） from v$archived_log where archived="YES" and deleted="NO"; COUNT（*）---------- 15下面进行第二次实验。RMAN> backup database plus archivelog;Starting backup at 21-SEP-15current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log（s） in backup setinput archived log thread=1 sequence=100 RECID=12 STAMP=890690423input archived log thread=1 sequence=101 RECID=13 STAMP=890712061input archived log thread=1 sequence=102 RECID=14 STAMP=890727732input archived log thread=1 sequence=103 RECID=15 STAMP=890776815input archived log thread=1 sequence=104 RECID=16 STAMP=890776833input archived log thread=1 sequence=105 RECID=17 STAMP=890805616input archived log thread=1 sequence=106 RECID=18 STAMP=890814181input archived log thread=1 sequence=107 RECID=19 STAMP=890820201input archived log thread=1 sequence=108 RECID=20 STAMP=890859629input archived log thread=1 sequence=109 RECID=21 STAMP=890892046input archived log thread=1 sequence=110 RECID=22 STAMP=890900632input archived log thread=1 sequence=111 RECID=23 STAMP=890906655input archived log thread=1 sequence=112 RECID=24 STAMP=890942416input archived log thread=1 sequence=113 RECID=25 STAMP=890990204input archived log thread=1 sequence=114 RECID=26 STAMP=890990263input archived log thread=1 sequence=115 RECID=27 STAMP=890990391channel ORA_DISK_1: starting piece 1 at 21-SEP-15channel ORA_DISK_1: finished piece 1 at 21-SEP-15piece handle=/u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T091951_bzypsqj3_.bkp tag=TAG20150921T091951 （篇幅原因，有省略……）Finished Control File and SPFILE Autobackup at 21-SEP-15第二次备份，之前备份过的日志还出现在自动备份的列表中。但是，在第二次备份的时候，已经备份过两次（deletion policy）的日志并没有自动删除。SQL> select count（*） from v$archived_log where archived="YES" and deleted="NO"; COUNT（*）---------- 17归档日志还在fast recovery area中。[oracle@Databaseintrawebpro fast_recovery_area]$ du -h19M ./XXXXDB/autobackup/2015_09_219.4M ./XXXXDB/autobackup/2015_09_1729M ./XXXXDB/autobackup151M ./XXXXDB/onlinelog6.0G ./XXXXDB/backupset/2015_09_21108K ./XXXXDB/backupset/2015_09_176.0G ./XXXXDB/backupset125M ./XXXXDB/archivelog/2015_09_1927M ./XXXXDB/archivelog/2015_09_214.0K ./XXXXDB/archivelog/2015_09_15127M ./XXXXDB/archivelog/2015_09_18121M ./XXXXDB/archivelog/2015_09_204.0K ./XXXXDB/archivelog/2015_09_1632M ./XXXXDB/archivelog/2015_09_17431M ./XXXXDB/archivelog9.4M ./XXXXDB/controlfile6.6G ./XXXXDB6.6G .此时，归档日志和备份次数，在v$archived_log中可以方便的找出来。SQL> alter system switch logfile;System alteredSQL> select count（*） from v$archived_log where archived="YES" and deleted="NO"; COUNT（*）---------- 18--注意这些已经备份过两次的recid编号SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log where backup_count>1; RECID SEQUENCE# ARCHIVED DELETED BACKUP_COUNT---------- ---------- -------- ------- ------------ 12 100 YES NO 2 13 101 YES NO 2 14 102 YES NO 2 15 103 YES NO 2 16 104 YES NO 2 17 105 YES NO 2 18 106 YES NO 2 19 107 YES NO 2 20 108 YES NO 2 21 109 YES NO 2 22 110 YES NO 2 23 111 YES NO 2 24 112 YES NO 2 25 113 YES NO 2 26 114 YES NO 215 rows selected进行第三次备份。RMAN> backup database plus archivelog;Starting backup at 21-SEP-15current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=498 device type=DISKskipping archived logs of thread 1 from sequence 100 to 114; already backed upchannel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log（s） in backup setinput archived log thread=1 sequence=115 RECID=27 STAMP=890990391input archived log thread=1 sequence=116 RECID=28 STAMP=890990481input archived log thread=1 sequence=117 RECID=29 STAMP=890990667input archived log thread=1 sequence=118 RECID=30 STAMP=890993128channel ORA_DISK_1: starting piece 1 at 21-SEP-15channel ORA_DISK_1: finished piece 1 at 21-SEP-15piece （篇幅原因，有省略…….）Finished Control File and SPFILE Autobackup at 21-SEP-15注意：备份过两次的日志，没有出现在RMAN自动备份的列表中。这里我们定义到了删除策略的一个行为：当满足删除条件的时候，归档日志是不会进入备份集合列表的。归档日志信息：SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log where backup_count>1; RECID SEQUENCE# ARCHIVED DELETED BACKUP_COUNT---------- ---------- -------- ------- ------------ 12 100 YES YES 2 13 101 YES YES 2 14 102 YES YES 2 15 103 YES YES 2 16 104 YES YES 2 17 105 YES YES 2 18 106 YES YES 2 19 107 YES YES 2 20 108 YES YES 2 21 109 YES YES 2 22 110 YES YES 2 23 111 YES YES 2 24 112 YES YES 2 25 113 YES YES 2 26 114 YES NO 2 27 115 YES NO 2 28 116 YES NO 217 rows selected注意：一部分归档日志被删除，但是并没有所有上次备份过两次的日志都删除掉了，比如recid=26的日志。此时，备份fast recovery area空间情况发生了变化。[oracle@Databaseintrawebpro fast_recovery_area]$ du -h29M ./XXXXDB/autobackup/2015_09_214.0K ./XXXXDB/autobackup/2015_09_1729M ./XXXXDB/autobackup151M ./XXXXDB/onlinelog5.5G ./XXXXDB/backupset/2015_09_214.0K ./XXXXDB/backupset/2015_09_175.5G ./XXXXDB/backupset4.0K ./XXXXDB/archivelog/2015_09_192.5M ./XXXXDB/archivelog/2015_09_214.0K ./XXXXDB/archivelog/2015_09_154.0K ./XXXXDB/archivelog/2015_09_184.0K ./XXXXDB/archivelog/2015_09_204.0K ./XXXXDB/archivelog/2015_09_164.0K ./XXXXDB/archivelog/2015_09_172.6M ./XXXXDB/archivelog9.4M ./XXXXDB/controlfile5.7G ./XXXXDB5.7G .在alert log中，我们看到了Oracle自动删除的动作。Mon Sep 21 09:24:27 2015Expanded controlfile section 11 from 28 to 56 recordsRequested to grow by 28 records; added 1 blocks of recordsArchived Log entry 29 added for thread 1 sequence 117 ID 0x774e158c dest 1:Mon Sep 21 10:05:28 2015ALTER SYSTEM ARCHIVE LOGMon Sep 21 10:05:28 2015Thread 1 advanced to log sequence 119 （LGWR switch） Current log# 2 seq# 119 mem# 0: /u01/app/oracle/oradata/XXXXDB/onlinelog/o1_mf_2_bxzzjj5w_.log Current log# 2 seq# 119 mem# 1: /u01/app/oracle/fast_recovery_area/XXXXDB/onlinelog/o1_mf_2_bxzzjj80_.logArchived Log entry 30 added for thread 1 sequence 118 ID 0x774e158c dest 1:Mon Sep 21 10:05:47 2015Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_17/o1_mf_annnn_TAG20150917T195557_bzoblfck_.bkpDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_17/o1_mf_1_100_bzokvqj0_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/autobackup/2015_09_17/o1_mf_s_890682958_bzoblglw_.bkpDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_101_bzp6zx31_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_102_bzpp9nln_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_103_bzr67h1h_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_104_bzr6812s_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_105_bzs2cj5y_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_106_bzsbq54p_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_107_bzsjm99v_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_108_bztq3f2v_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_109_bzvprgf1_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_110_bzvz4rj7_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_111_bzw50zmb_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_112_bzx7yj9g_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_21/o1_mf_1_113_bzypmw8c_.arcDeleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T091644_bzypmwty_.bkpMon Sep 21 10:05:58 2015Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_nnndf_TAG20150921T091647_bzypn055_.bkpMon Sep 21 10:06:15 2015ALTER SYSTEM ARCHIVE LOGMon Sep 21 10:06:15 2015Thread 1 advanced to log sequence 120 （LGWR switch） Current log# 3 seq# 120 mem# 0: /u01/app/oracle/oradata/XXXXDB/onlinelog/o1_mf_3_bxzzjl0z_.log Current log# 3 seq# 120 mem# 1: /u01/app/oracle/fast_recovery_area/XXXXDB/onlinelog/o1_mf_3_bxzzjl35_.logArchived Log entry 31 added for thread 1 sequence 119 ID 0x774e158c dest 1:日志里面，Oracle不仅仅删除了部分备份过两次的日志，而且删除了一个已经obsolete的备份集合。这个体现了一个特性：当fast recovery area空间紧张的时候，obsolete和符合删除deletion policy的归档日志会被自动删除。更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2015-10/124126p2.htm

CentOS系统 Amoeba+MySL主从读写分离配置教程CentOS 6.6 x64 自动化安装Oracle Database 11gR2 RAC脚本相关资讯 RMAN

RMAN故障一例（归档的备份，从不（今 20:42）
RMAN的FORMATA格式说明（03月10日）
Oracle 11g RMAN复制数据库的测试（01月19日）

RMAN数据库迁移（05月22日）
使用RMAN复制恢复开发库环境（02月17日）
Oracle 11g RMAN跨平台传输表空间（01月19日）

本文评论查看全部评论（0）