ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 配置了Fast Recovery Area可能会导致snapshot controlfile被频繁更新

配置了Fast Recovery Area可能会导致snapshot controlfile被频繁更新

原创 Oracle 作者：oliseh 时间：2015-07-13 12:30:47 0 删除 编辑

snapshot controlfile是current controlfile在某一时刻的快照，以下两种情况会生成snapshot controlfile
1、resync catalog
2、backup controlfile

查看snapshot controlfile的存放路径
RMAN> show SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f'; # default

修改snapshot controlfile的存放路径可以通过CONFIGURE SNAPSHOT CONTROLFILE NAME TO ... 命令

但是你可能不知道的是，如果系统配置了Fast recovery area(以下简称FRA)，在RMAN里执行任何backup命令都会触发snapshot controlfile更新，我们来测试一下几种情况

< 1. 配置了FRA，backup spfile，snapshot controlfile有更新 >
SYS@tstdb1-SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata06/fra
db_recovery_file_dest_size           big integer 200G

---备份前的controlfile snapshot时间戳
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 10:36 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f

RMAN> backup spfile;

Starting backup at 20150710 13:15:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=200 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20150710 13:15:39
channel ORA_DISK_1: finished piece 1 at 20150710 13:15:40
piece handle=/oradata06/fra/TSTDB1/backupset/2015_07_10/o1_mf_nnsnf_TAG20150710T131539_1kmsUDQ4V_.bkp tag=TAG20150710T131539 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150710 13:15:40

---备份后controlfile snapshot时间戳更新为当前时间
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 13:15 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f


<2. 不配置FRA，backup spfile，snapshot controlfile不会有更新>
SYS@tstdb1-SQL> alter system set db_recovery_file_dest='' scope=memory;

System altered.

SYS@tstdb1-SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 200G

RMAN> backup spfile;

Starting backup at 20150710 13:18:05
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20150710 13:18:05
channel ORA_DISK_1: finished piece 1 at 20150710 13:18:06
piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/8iqbmqcd_1_1 tag=TAG20150710T131805 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150710 13:18:06

---controlfile snapshot时间戳还停留在以前的时间
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 13:15 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f

<3. 配置FRA目录，backup到非FRA目录下，snapshot controlfile依然有更新>
SYS@tstdb1-SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata06/fra
db_recovery_file_dest_size           big integer 200G

RMAN> backup spfile format '/oradata06/sp_%u';

Starting backup at 20150710 13:36:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=926 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=3 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20150710 13:36:52
channel ORA_DISK_1: finished piece 1 at 20150710 13:36:53
piece handle=/oradata06/sp_8lqbmrfk tag=TAG20150710T133652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150710 13:36:53

---备份后的controlfile更新为最新的时间戳
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 13:36 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f

配置FRA的情况下，不但backup命令运行完后会更新snapshot controlfile，delete命令也会更新

<4. 配置了FRA，delete FRA目录下的备份会更新snapshotcontrolfile>
alter system set db_recovery_file_dest='/oradata06/fra' scope=memory;

SYS@tstdb1-SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata06/fra
db_recovery_file_dest_size           big integer 200G

RMAN> delete backupset tag=TAG20150710T131539;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
15      15      1   1   AVAILABLE   DISK        /oradata06/fra/TSTDB1/backupset/2015_07_10/o1_mf_nnsnf_TAG20150710T131539_1kmsUDQ4V_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oradata06/fra/TSTDB1/backupset/2015_07_10/o1_mf_nnsnf_TAG20150710T131539_1kmsUDQ4V_.bkp RECID=15 STAMP=884697339
Deleted 1 objects

tstdb1@jq570322b:/home/tstdb1>date
Fri Jul 10 13:20:42 BEIST 2015

---delete运行完后controlfile snapshot时间戳更新为了当前时间
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 13:20 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f


<5. 配置了FRA，delete 非FRA目录下的备份也会更新snapshotcontrolfile>
SYS@tstdb1-SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata06/fra
db_recovery_file_dest_size           big integer 200G

RMAN> delete backupset tag=TAG20150710T133034;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
18      18      1   1   AVAILABLE   DISK        /oracle/app/oracle/product/11.2.0/db_1/dbs/8kqbmr3q_1_1    <---非FRA目录的备份

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/app/oracle/product/11.2.0/db_1/dbs/8kqbmr3q_1_1 RECID=18 STAMP=884698234
Deleted 1 objects

tstdb1@jq570322b:/home/tstdb1>date
Fri Jul 10 13:33:17 BEIST 2015

---尽管删除的不是FRA目录下的备份，snapshot controlfile也会有更新
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 13:32 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f  


<6.不配置db_recovery_file_dest，delete不会更新snapshot controlfile>
SYS@tstdb1-SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata06/fra
db_recovery_file_dest_size           big integer 200G

RMAN> backup spfile;

Starting backup at 20150710 13:21:57
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20150710 13:21:57
channel ORA_DISK_1: finished piece 1 at 20150710 13:21:58
piece handle=/oradata06/fra/TSTDB1/backupset/2015_07_10/o1_mf_nnsnf_TAG20150710T132157_1kmsqjNpI_.bkp tag=TAG20150710T132157 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150710 13:21:58

alter system set db_recovery_file_dest='' scope=memory;

SYS@tstdb1-SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 200G

RMAN> delete backupset tag=TAG20150710T132157;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
17      17      1   1   AVAILABLE   DISK        /oradata06/fra/TSTDB1/backupset/2015_07_10/o1_mf_nnsnf_TAG20150710T132157_1kmsqjNpI_.bkp

Do you really want to delete the above objects (enter YES or NO)? YEs
deleted backup piece
backup piece handle=/oradata06/fra/TSTDB1/backupset/2015_07_10/o1_mf_nnsnf_TAG20150710T132157_1kmsqjNpI_.bkp RECID=17 STAMP=884697717
Deleted 1 objects

---snapshot controlfile时间戳没有更新
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 13:21 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f

其实在配置了FRA的情况下部分会对controlfile产生修改的操作都会触发snapshot controlfile的更新
比如catalog一个backuppiece
ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 13:38 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f

RMAN> catalog backuppiece '/oradata06/dupuse/150708/8bqbh1oj';

cataloged backup piece
backup piece handle=/oradata06/dupuse/150708/8bqbh1oj RECID=20 STAMP=884701662

ls -l /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f
-rw-r-----    1 oracle   oinstall   14303232 Jul 10 14:27 /oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f

对于配置FRA时snapshot controlfile频繁被更新的原因，oracle解释为：FRA区域里的文件由oracle自动管理，因此rman backup或者delete命令运行后oracle需要知道FRA里最实时的文件分布情况，决定哪些文件需要被自动清理，为此需要生成一份最新的待清理文件列表，为了实现这个目的snapshot controlfile才会频繁的更新。
本文以backup spfile作为例子，其实在配置了FRA的前提下，backup archivelog 、backup datafile同样也会更新snapshot controlfile，有兴趣的同学可以尝试一下

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/53956/viewspace-1731486/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： recover database skip [forever] tablespace XXX 的用法介绍
下一篇： 没有备份的情况下如何处理logical & physical corrupt block
请登录后发表评论 登录
全部评论
oliseh
不仅仅专注Oracle database技术, member of SHOUG

注册时间：2014-04-06

  • 博文量
    300
  • 访问量
    1959620

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593