配置了Fast Recovery Area可能會導致snapshot controlfile被頻繁更新
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有更新 >
> 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不會有更新>
> alter system set db_recovery_file_dest='' scope=memory;
System altered.
> 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依然有更新>
> 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;
> 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
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>
> 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
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
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>
> 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;
> 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部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1731486/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11gR2 fast recovery area = flash recovery areaOracleAST
- 【RMAN】什麼是Fast Recovery Area(FRA)，如何配置FRAAST
- logback配置不當導致頻繁類載入
- 如何處理頻繁建立物件然後丟棄導致頻繁GC的情況物件GC
- 處理歸檔滿了fast_recovery_area無剩餘空間的案例AST
- RDS最佳實踐(五)—Mysql大欄位的頻繁更新導致binlog暴增MySql
- fastjson到底做錯了什麼？為什麼會被頻繁爆出漏洞？ASTJSON
- crontab導致的頻繁傳送郵件的問題
- Fast Recovery Area空間用滿後的自動清理機制AST
- Flash Recovery Area空間不足導致資料庫不能開啟或hang住資料庫
- aix上跑oracle，swap頻繁導致hdisk100%繁忙AIOracle
- 線上排查：記憶體異常使用導致full gc頻繁記憶體GC
- 設定Flash Recovery Area
- ORACLE中seq$表更新頻繁的分析Oracle
- 又抓了一個導致頻繁GC的鬼--陣列動態擴容GC陣列
- flash recovery area 空間滿了的處理辦法
- mysql的新建索引會導致insert被lockedMySql索引
- 更改SERVICE_NAME導致大量會話被KILL會話
- 光纖交換機埠模組更換導致資料庫IO頻繁100%資料庫
- 微軟承諾Win10慢速預覽通道更新會更頻繁、更穩定微軟Win10
- 一次對pool的誤用導致的.net頻繁gc的診斷分析GC
- flash_recovery_area故障處理
- win7更換主題會導致個性化圖示也被更換了Win7
- 前端效能：股票交易APP頻繁更新怎麼破前端APP
- 蘋果iOS系統頻繁更新 到底升還是不升？蘋果iOS
- 可能會導致.NET記憶體洩露的8種行為記憶體洩露
- Oracle 閃回區(Oracle Flash recovery area)Oracle
- ORACLE 10G flash_recovery_areaOracle 10g
- 個人資訊頻繁被洩露 販賣、非法蒐集如何破解
- ORACLE DML執行計劃頻繁變更導致業務響應極慢問題的處理Oracle
- RabbitMQ 處理過慢，原來是一個 SQL 快取框架導致的 GC 頻繁觸發MQSQL快取框架GC
- 開啟 Keep-Alive 可能會導致http 請求偶發失敗Keep-AliveHTTP
- flashback_area 區域溢位導致資料庫當機資料庫
- OMF和flash_recovery_area的關係！
- 開發小程式被問到最頻繁的問題（上）
- macbook使用者注意了，這個行為可能導致螢幕損壞Mac
- 11.2使用KEEP池導致ENQ: KO - Fast Object Checkpoint等待ENQASTObject
- fast recovery for innodb1.07 in Mysql 5.5ASTMySql