^_^小麦苗^_^

小麦苗博客,实用经典,简单易懂,接地气!---技术应用型博客,主打实用性,只讲实用的,不讲没用的,多做实验,多思考!

  • 博客访问: 5954598
  • 博文数量: 948
  • 用 户 组: 普通用户
  • 注册时间: 2012-09-23 17:46
  • 认证徽章:
个人简介

QQ:646634621 网名:小麦苗 blog:http://blog.itpub.net/26736162/abstract/1/ 微信公众号:xiaomaimiaolhr QQ群:230161599 微信群:私聊 DBA宝典号内搜:http://data.newrank.cn/m/s.html?s=OSsuPjE4LDI7 《Oracle笔试面试宝典》作者

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(948)

文章存档

2017年(393)

2016年(313)

2015年(167)

2014年(73)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

Oracle增量备份和快速备份(块改变跟踪Block Change Tracking)



下面小麦苗给出全库备份的脚本:


点击(此处)折叠或打开

  1. [oracle@rhel6lhr ~]$ crontab -l
  2. 2 12 * * 1 /home/oracle/lhr/rman/rman_backup_full.sh
  3. [oracle@rhel6lhr ~]$ more /home/oracle/lhr/rman/rman_backup_full.sh
  4. export ORACLE_SID=orclasm
  5. export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
  6. export PATH=$ORACLE_HOME/bin:$PATH
  7. MYDATE=`date +'%Y%m%d%H%M%S'`
  8. BACKUP_DIR=/u05/oracle/oracle_bk/$ORACLE_SID

  9.  
  10. rman target / log /home/oracle/lhr/rman/log/rman_full_$MYDATE.log append <<EOF
  11. run
  12. {
  13.  allocate channel c1 type disk;
  14.  allocate channel c2 type disk;
  15.  backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
  16.  sql 'alter system archive log current';
  17.  backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
  18.  backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
  19. }
  20. EOF

  21. rman target / log /home/oracle/lhr/rman/log/rman_delete_$MYDATE.log append <<EOF
  22.  allocate channel for maintenance type disk;
  23.  allocate channel for maintenance type sbt_tape;
  24.  crosscheck copy;
  25.  crosscheck archivelog all;
  26.  crosscheck backup;
  27.  delete noprompt obsolete;
  28.  delete noprompt expired backup;
  29. EOF

下面小麦苗给出增量备份的脚本,备份策略为周日0级全备,周四为1级增量备份,其它为2级增量备份:


点击(此处)折叠或打开

  1. 40 11 * * * /home/oracle/lhr/rman/run_rman_incremental.sh

  2. [oracle@rhel6lhr ~]$ more /home/oracle/lhr/rman/run_rman_incremental.sh
  3. sh /home/oracle/lhr/rman/rman_incremental.sh 2>&1 &
  4. [oracle@rhel6lhr ~]$ more /home/oracle/lhr/rman/rman_incremental.sh
  5. ########################################################################
  6. ## incremental_database_backup.sh ##
  7. ## created by lhr ##
  8. ## 2015-1-23 ##
  9. ## sun:0 Thu:1 others:2 ##
  10. #########################################################################
  11. #!/bin/ksh


  12. export LANG=en_US
  13. MYDATE=`date +'%Y%m%d%H%M%S'`
  14. MYDATE2=`date +'%Y-%m-%d %H:%M:%S'`
  15. CUSER=`id|cut -d "(" -f2|cut -d ")" -f1`
  16. export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
  17. export PATH=$ORACLE_HOME/bin:$PATH
  18. export RMAN=$ORACLE_HOME/bin/rman
  19. export ORACLE_SID=orclasm
  20. export ORACLE_USER=oracle
  21. BACKUP_DIR=/u05/oracle/oracle_bk/$ORACLE_SID
  22. RMAN_LOG_FILE=$BACKUP_DIR/log/rman_incremental_"$ORACLE_SID"_$MYDATE.log


  23. echo "-----------------$MYDATE2-------------------">$RMAN_LOG_FILE
  24. echo "ORACLE_SID: $ORACLE_SID">>$RMAN_LOG_FILE
  25. echo "ORACLE_HOME:$ORACLE_HOME">>$RMAN_LOG_FILE
  26. echo "ORACLE_USER:$ORACLE_USER">>$RMAN_LOG_FILE
  27. echo "==========================================">>$RMAN_LOG_FILE
  28. echo "BACKUP DATABASE BEGIN......">>$RMAN_LOG_FILE
  29. echo " ">>$RMAN_LOG_FILE
  30. chmod 666 $RMAN_LOG_FILE
  31.  
  32. WEEK_DAILY=`date +%a`
  33.  
  34. case "$WEEK_DAILY" in
  35.        "Mon")
  36.             BAK_LEVEL=2
  37.             ;;
  38.        "Tue")
  39.             BAK_LEVEL=2
  40.             ;;
  41.        "Wed")
  42.             BAK_LEVEL=2
  43.             ;;
  44.        "Thu")
  45.             BAK_LEVEL=1
  46.             ;;
  47.        "Fri")
  48.             BAK_LEVEL=2
  49.             ;;
  50.        "Sat")
  51.             BAK_LEVEL=2
  52.             ;;
  53.        "Sun")
  54.             BAK_LEVEL=0
  55.             ;;
  56.        "*")
  57.             BAK_LEVEL=error
  58. esac
  59. export BAK_LEVEL=$BAK_LEVEL
  60. echo "Today is : $WEEK_DAILY incremental level= $BAK_LEVEL">>$RMAN_LOG_FILE
  61.  
  62. RMAN_RUN_STR="
  63.         export BAK_LEVEL=$BAK_LEVEL
  64.         export ORACLE_HOME=$ORACLE_HOME
  65.         export ORACLE_SID=$ORACLE_SID
  66.         export NLS_DATE_FORMAT='YYYY-MM-DD HH24:mi:ss'
  67.         $RMAN nocatalog TARGET sys/lhr log $RMAN_LOG_FILE append <<EOF
  68.         set echo on;
  69.         run
  70.         {
  71.                 allocate channel c1 type disk;
  72.                 allocate channel c2 type disk;
  73.                 backup incremental level= $BAK_LEVEL skip inaccessible filesperset 5 Database format='$BACKUP_DIR/datafile_lev"$BAK_LEVEL"_%d_%I_%U_%T.bak' tag='bk_df_lev"$BAK_LEVEL_$MYDATE"' ;
  74.                 sql 'alter system archive log current';
  75.                 backup archivelog all format='$BACKUP_DIR/arch_%d_%I_%e_%U_%T.bak' tag='bk_arc_$MYDATE' skip inaccessible filesperset 5 not backed up 1 times delete input;
  76.                 backup current controlfile format='$BACKUP_DIR/ctlfile_%d_%I_%U_%T.bak' tag='bk_ctlfile_$MYDATE' ;
  77.                 sql \"alter database backup controlfile to trace as ''$BACKUP_DIR/ctlfile_trace_"$ORACLE_SID"_$MYDATE.txt'' \";
  78.                 backup spfile format='$BACKUP_DIR/spfile_%d_%I_%U_%T.bak' tag='bk_spfile_$MYDATE';
  79.                 release channel c2;
  80.                 release channel c1;
  81.         }
  82.         allocate channel for maintenance device type disk;
  83.         report obsolete;
  84.         crosscheck copy;
  85.         crosscheck archivelog all;
  86.         delete noprompt obsolete;
  87.         crosscheck backup;
  88.         delete noprompt expired backup;
  89.         list backup summary;
  90.         release channel;
  91.         exit
  92.         EOF
  93. "
  94.  
  95. # Initiate the command string
  96. if [ "$CUSER" = "root" ]
  97. then
  98.     echo "Root Command String: $RMAN_RUN_STR" >> $RMAN_LOG_FILE
  99.     su - $ORACLE_USER -c "$RMAN_RUN_STR" >> $RMAN_LOG_FILE
  100.     RSTAT=$?
  101. else
  102.     echo "User Command String: $RMAN_RUN_STR" >> $RMAN_LOG_FILE
  103.     /bin/sh -c "$RMAN_RUN_STR" >> $RMAN_LOG_FILE
  104.     RSTAT=$?
  105. fi
  106.  
  107. # ---------------------------------------------------------------------------
  108. # Log the completion of this script.
  109. # ---------------------------------------------------------------------------
  110. if [ "$RSTAT" = "0" ]
  111. then
  112.     LOGMSG="ended successfully"
  113. else
  114.     LOGMSG="ended in error"
  115. fi
  116.  
  117. echo >> $RMAN_LOG_FILE
  118. echo Script $0 >> $RMAN_LOG_FILE
  119. echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
  120. echo >> $RMAN_LOG_FILE
  121. ##/bin/mailx -s "RMAN Backup SID " daimm@sf-express.com < $RMAN_LOG_FILE
  122.  
  123. exit $RSTAT







Oracle增量备份

RMAN一个强大的功能是支持增量备份,增量备份中心思想就是减少备份的数据量,我们不需要在从头开始备份了,只需要备份自上次已备份之后的数据块即可。

关于Incremental增量备份级别:

Oracle 9i 共有五种级别 0 1 2 3 4,0级最高-4级最低,0级是1级的基础以此类推。
Oracle 10g官方文档明确指出增量备份只有0和1两种级别(太多增量级别其实没有太大的意义),不过实际执行增量操作时,仍然能够指定多个级别,最大能够支持4级增量备份。
Oracle 11g 增量备份只有0和1两种级别。

Level 0级就是对数据库一个全库备份,增量备份必须从0级开始,也就是说必须要有一个全库备份当基础。如果你做全库备份oracle也不认为这是level 0的全库备份,尽管是一样的也要单独做一次level 0。有了level 0当基础才能有后面的 level 1 level 2 level 3 level 4。。

增量备份原理
每一个数据块上都包含一个SCN,SCN可以标示最近一次对于Block的变更。在增量备份的过程中,RMAN读取每一个数据块的SCN与之前备份的SCN相比较,从而判断出Block是否发生了变化,然后根据此进行备份。但是需要注意的是,在早期版本,即使是数据块只有小的变化,RMAN扫描的时间也可能会极其漫长。Oracle 10g引入了Block Change Tracking特性解决了这一问题。

Oracle的增量备份分为差异增量备份(Differential Incremental Backup)和积累增量备份(Cumulative Incremental Backup)两种 。

差异增量备份(Differential Incremental Backup)
差异备份是RMAN生成的增量备份的默认类型。对于差异备份来说,RMAN会备份自上一次同级或低级差异增量备份以来所有发生变化的数据块。

例如:我们在星期天执行0级差异增量备份操作,这个备份操作会备份整个数据库。根据这个0级备份,我们在星期一执行1级差异增量备份操作。该备份操作将备份自周日0级备份以来所有发生变化的数据块。在周二时1级增量备份将备份所有自周一1级备份以来发生变化的数据块。如果要执行恢复操作,就需要星期一、星期二生成的备份以及星期天生成的基本备份。下面是一个执行1级差异增量备份操作的示例:
backup incremental level=1 database;



累计增量备份(Cumulative Incremental Backup)
累计增量备份是指备份自最近的级别0备份以来所变化的数据块。累积增量备份能减少恢复时间。

例如:我们在星期天执行0级差异增量备份操作,此时会备份整个数据库。随后,我们星期一执行1级备份操作,这种备份不同于差异备份,在星期二进行另一个1级差异备份操作。需要注意的是:星期二生成的备份不仅包含星期一的差异备份以后发生变化的数据块,而且还含有星期一生成的备份中所包含的数据块。因此,累积备份累积了任何同级或低级增量备份操作包含的所有发生变化的数据块。如果要执行恢复操作,就只需要星期二生成的备份以及星期天生成的基本备份。
下面是一个执行1级累积增量备份操作的示例:
backup incremental level=1 cumulative database;



Differential Incremental Backup vs Cumulative Incremental Backup

Differential backups 速度较快、因为仅存储少量变化的块、但需要更长的时间来恢复.
Cumulative backup 的主要优势是恢复时间较短、缺点是备份时间长和磁盘开销大.


ORACLE RMAN备份--差异增量与累积增量的策略实例图

    BACKUP 命令在不显式指定的情况下,默认情况下会选择Differential方式,即差异增量备份。不过从恢复效率的角度考虑,Cumulative方式的增量备份表现更好一些,毕竟不需要读取那么多备份集了.

注意:

    在10g之前的版本中,增量备份可以有 n 多个级别,从0级、1级、2级……这样顺序排列直到 n 级( n 为小于等于4的正整数)。

    在10g版本中,Oarcle的官方文档明确指出增量备份只有0和1两种级别(太多增量级别其实没有太大的意义),不过实际执行增量操作时,仍然能够指定多个级别,最大能够支持4级的增量备份。

10g版本有关0,1差异与累积增量备份策略的示例图如下:








块改变跟踪(Block Change Tracking

block change tracking介绍
Block chage tracking是Oracle10g的一个新特性,Block change tracking进程记录自从上一次备份以来数据块的变化,并把这些信息记录在跟踪文件中。RMAN使用这个文件判断增量备份中需要备份的变更数据。这极大的促进了备份性能,RMAN可以不再扫描整个文件以查找变更数据。 为此Oracle引入了一个新的后台进程,CTWR,其全称为Change Tracking Writer,用于记录变化的块并将变化写入相应的日志文件中。 
配置block change tracking
开启bct:
可以在数据文件,asm,裸设备上创建日志跟踪文件
alter database enable block change tracking using file '/oradata/block_track.log';
alter database enable block change tracking using file ‘+data’;
alter database enable block change tracking using file ‘/dev/rlvbct’;
通过查询v$block_change_tracking视图查询bct的使用情况。
禁用bct:
alter database disable block change tracking;
修改块修改跟踪文件路径
修改块修改跟踪文件可以通过alter database rename file命令,但是只能在mount状态下操作。
查看当前块修改跟踪文件路径:
SQL> select filename from v$block_change_tracking;
FILENAME
--------------------------------------------------------------------------------------
E:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/RMAN_INC.TRK
Shutdown 数据库并启动到mount状态:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1295608 bytes
Variable Size              88083208 bytes
Database Buffers           71303168 bytes
Redo Buffers                7090176 bytes
Database mounted.
手工复制块修改文件到新路径下,然后执行alter database命令:
SQL> alter database rename file 'E:/ORACLE/PRODUCT/10.2.0/DB_1/DATABASE/RMAN_INC.TRK'
  2  to 'E:/oracle/oradata/jssweb/rman_inc.trk';
Database altered.
SQL> alter database open;
Database altered.
查看当前块修改跟踪文件路径:
SQL> select filename from v$block_change_tracking;
FILENAME
-----------------------------------------------------
E:/ORACLE/ORADATA/JSSWEB/RMAN_INC.TRK
如果你的数 据库无法shutdown,其实也无所谓,先disable,然后执行enable时指定一个新路径也可以的:),只不过,原来文件中记录的块修改的跟踪信息也就没有了。
How To Size the Block Change Tracking File [ID 306112.1]
In this Document
  Goal
  Solution
Platforms: 1-914CU; 
This document is being delivered to you via Oracle 
Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to: 
Oracle Server - Enterprise Edition - Version: 
10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Oracle Server - Enterprise 
Edition - Version: 10.1.0.2 to 11.1.0.7   [Release: 10.1 to 11.1]
Information 
in this document applies to any platform.
Goal
This is a clarification of the documentation for sizing the Block Change Tracking File.  Block Change Tracking is used to optimize incremental backups. A new file is introduced:
- Changed blocks are tracked by the CTWR background process as redo is generated
- Database backups automatically use the change tracking file
Sizing of this file is important, and even though you can use 
V$BLOCK_CHANGE_TRACKING view to monitor the usage, it may in some cases not be easy to change the size, ie when using raw devices.
Solution
The formula is: 
<size of change tracking file> = <# of redo threads> * (# of old backups + 2) * (size of db/250000)
Let’s take an example of a 500 GB database, with only one thread, and having eight backups kept in RMAN. The repository for this will require a block change tracking file of 20 MB.
Threads * (number of old backups+2) * (database size in bytes/250000) = 20 MB
The amount of data that each bit in the change tracking file covers is 32KB. That's 262144 bits.
That is where the 1/250000 figure comes from. This is the ratio of the size of *one bitmap* in the change tracking file, to the size of the database data that the bitmap covers. And, because we keep up to 8 levels of bitmap history in the file, plus the current bitmap, that reduces the ratio to about 1/30000, per enabled thread.  Dividing 1TB by 30000, you get 30 MB, per TB, per enabled thread.  So the parameters we use to determine the block change tracking file are:
- Database size
- Number of enabled threads
- Number of old 
backups. (The block change tracking file keeps a record of all changes between 
previous backups, up to eight in total)
 一些备忘知识点
 通过位图跟踪两次备份间变化的数据块;
 每次备份前进行位图切换;
 开发增量备份策略时,要考虑到8个位图的限制;
 在RAC环境中,change tracking file需要放在共享存储上;
 Change tracking file的大小和数据库的大小和enabled的redo thread的个数成正比;
 Change tracking file的大小和数据更新的频率无关;

8 VERSIONS IMPACT
Let’s walk through an example of bi-weekly incremental backup cycle.
2 TB data warehouse database containing 5 years worth of data is backed up every other Sunday with incremental level 0 backup. Full backup is running 20 hours. For the next 13 days incremental level 1 cumulative backup is taken. Cumulative level 1 backup means that RMAN will need to copy blocks changed since last level 0 backup.
Backup is running every morning after nightly ETL batch completes. The batch changes about 1% (including new data loaded, updated indexes and changes to the staging tables). Half of changed blocks are in staging area. Another half is new data loaded and indexes updated. This means that first incremental level 1 cumulative backup is 0.5% of the database or 10 GB. The next level 1 cumulative backup adds 0.25% of the database size to previous size so sizes are 10 GB, 15 GB, 20 GB and so on ending with 70 GB on the last level 1 backup before level 0 backup.
Incremental backups take less an hour so they finish before users start their day and hit database with their requests.
Let’s assume that we enabled change tracking just before level 0 incremental backup and version number 1 is the current version. Incremental level 0 backup starts and as soon as each datafile is backed up, the current version becomes 2.
Monday – incremental backup kicks off and version 3 is the current version. No backup is purged. RMAN is happily using change tracking file to determine which blocks are needed for backup – RMAN scans the bitmaps since last level 0 backup – version 2 bitmaps.
Tuesday - incremental backup kicks off and version 4 is the current version. No backup is purged. RMAN again scans the bitmaps since last level 0 backup. This time it needs bitmaps for versions 2 and 3. Some blocks might be marked dirty in both versions. In fact, those are blocks in the staging area representing 0.25% of the database size as we stated above.
Backups for the next days until Sunday are working under the same scenario using bitmaps since version 2. Sunday’s incremental level 1 cumulative backup does the same but it now purges oldest bitmap version. The current version is switched to number 9 on Sunday’s backup and version 1 needs to be purged – Oracle keeps only 8 versions including current version. This is not a problem and RMAN still can use versions from 2 till 8 to determine which blocks have been changed and must be backed up.
Second Monday - incremental backup kicks off and version 10 becomes the current version. Bitmaps of version 2 are purged.
Now RMAN cannot locate all the required versions to find all the dirty blocks changed since incremental level 0 backup – it misses bitmap version 2 and cannot identify blocks changed between the last level 0 and the first level 1 incremental backup.
As a result, RMAN has to fall back to the old incremental backup method and scan the whole database.
The consequences are 10 hours incremental backup, IO subsystem performance degradation, users are unhappy because their requests take few times longer than usual

参考至:http://showmealone.blog.sohu.com/179007059.html

                  http://blog.csdn.net/Makaveli/article/details/6295960

                  http://www.pythian.com/documents/Pythian-oracle-block-change.pdf




Block ChangeTracking 是Oracle 10g里推出的特性。官网对Block change tracking 的定义如下:

       Adatabase option that causes Oracle to track data file blocks affected by eachdatabase update. The tracking information is stored in a block change trackingfile. When block change tracking is enabled, RMAN uses the record of changedblocks from the change tracking file to improve incremental backup performanceby only reading those blocks known to have changed, instead of reading datafiles in their entirety.

       --Block change tracking 会记录data file里每个block的update 信息,这些tracking信息保存在tracking 文件里。 当启动block change tracking 后,RMAN 使用trackingfile里的信息,只读取改变的block信息,而不用在对整个data file进行扫描,从而提高了RMAN 备份的性能。

      

11gR2 文档里的说明。

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV8125

 

一.About Block Change Tracking

If block changetracking is enabled on a primary or standby database, then RMAN uses a blockchange tracking file to identify changed blocks for incrementalbackups. By reading this small bitmap file to determine which blocks changed,RMAN avoids having to scan every block in the data file that it is backing up.

--block change tracking file 是bitmap file。

 

Block changetracking is disabled by default. Nevertheless, the benefits of avoiding fulldata file scans during backup are considerable, especially if only a smallpercentage of data blocks are changed between backups. If your backup strategyinvolves incremental backups, then block change tracking is recommended. Blockchange tracking does not change the commands used to perform incrementalbackups. The change tracking file requires no maintenance after initialconfiguration.

--Block change tracking 默认是禁用的,如果启用了incrementalbackup,那么建议开启block change tracking。 启用BCT后,不需要其他的维护操作。

 

You can only enable block change tracking at a physical standby database if a license forthe Oracle Active Data Guard option is enabled.

 

1.1 SpaceManagement in the Block Change Tracking File

The change tracking file maintains bitmaps that mark changes in the datafiles betweenbackups. The database performs a bitmap switch before each backup. OracleDatabase automatically manages space in the change tracking file to retainblock change data that covers the eight most recent backups. After the maximumof eight bitmaps is reached, the oldest bitmap is overwritten by the bitmapthat tracks the current changes.

--在备份期间,change tracking会维护已经标记为change 的block 的bitmap 信息。Oracle 会自动管理change tracking file的大小,只保留最近最近8次blockchange 的信息。 超过8次, 那么最前面的block bitmap 信息会被current change 覆盖。

 

The first level0 incremental backup scans the entire data file. Subsequent incremental backupsuse the block change tracking file to scan only the blocks that have beenmarked as changed since the last backup. An incremental backup can be optimizedonly when it is based on a parent backup that was made after the start of theoldest bitmap in the block change tracking file.

--第一个0级的增量备份扫描整个datafile。 随后的增量备份使用block changetracking file的信息,只扫描自上次备份以来被标记为change 的block。

 

Consider theeight-bitmap limit when developing your incremental backup strategy. Forexample, if you make a level 0 database backup followed by seven differentialincremental backups, then the block change tracking file now includes eightbitmaps. If you then make a cumulative level 1 incremental backup, then RMANcannot optimize the backup, because the bitmap corresponding to the parentlevel 0 backup is overwritten with the bitmap that tracks the current changes.

--要考虑change tracking file 的8次限制与备份策略的关系。 如果我们在0级备份之后又进行了7次不同的增量备份,那么这时block change tracking 就包含了8个bitmaps信息。 如果我们在进行1级的增量备份,那么此时的增量就不是最优的, 还会扫描整个data file。 因为我们之前0级的bitmap信息被我们当前的bitmap 信息覆盖掉了,这样就没有了参考的依据。

  所以RMAN的备份策略要考虑到这个8次bitmap 的影响。

 

1.2 Location of the Block Change Tracking File

One block changetracking file is created for the whole database. By default, the changetracking file is created as an Oraclemanaged file in the destination specified by theDB_CREATE_FILE_DEST initializationparameter. You can also place the change tracking file in any location that youchoose, by specifying its name when enabling block change tracking. Oraclerecommends against using a raw device (that is, a disk without a file system)as a change tracking file.

       --block change tracking 文件是针对整个数据库,默认情况下,changetracking file 会作为Oracle managed file,在DB_CREATE_FILE_DEST指定的目录下创建。 可以放到放到其他的目录。 Oracle  不建议使用rawdevice 来存放change tracking file.

 

Note:

In an Oracle RACenvironment, the change tracking file must be located on shared storageaccessible from all nodes in the cluster.

       --如果是RAC 环境,change tracking file 必须放在共享设备上。

 

RMAN does notsupport backup and recovery of the change tracking file. The database resetsthe change tracking file when it determines that the change tracking file isinvalid. If you restore and recover the whole database or a subset, then thedatabase resets the block change tracking file and starts tracking changesagain. After you make a level 0 incremental backup, the next incremental backupcan use change tracking data.

--RMAN 不支持对change tracking file 的备份与恢复。当数据检测到change tracking file 无效时,就会reset changetracking file。 如果我们还原了数据库,那么数据库也会reset block change tracking,并从新进行tracking。

 

1.3 Size of the Block Change Tracking File

The size of the block change tracking file is proportional to thesize of the database and the number of enabled threads of redo. The size of theblock change tracking file can increase and decrease as the database changes.The size is not related to the frequency of updates to the database.

--block change tracking file的大小和数据库的大小及 enabledthreads of redo 的数量有关系,tracking file 的大小会根据数据库大小的变化和变化。 和更新频率没有关系。

 

Typically, thespace required for block change tracking for a single instance is approximately1/30,000 the size of the data blocks to be tracked. For an Oracle RACenvironment, it is 1/30,000 of the size of the database, times the number ofenabled threads.

--典型的,单实例下大约DB的1/30000的block 会把track。 如果是RAC 环境,在乘以threads。

 

The followingfactors that may cause the file to be larger than this estimate suggests:

--一下因素会导致file 超过估计值:

(1)To avoid theoverhead of allocating space as your database grows, the block change trackingfile size starts at 10 megabytes. New space is allocated in 10 MB increments.Thus, for any database up to approximately 300 gigabytes, the file size is nosmaller than 10 MB, for up to approximately 600 gigabytes the file size is nosmaller than 20 megabytes, and so on.

--为了避免tracking file 占用太多的空间, 初始值是10M,然后每次增加10M,如果数据库接近300G,那么trackingfile 不能小于10M,如果接近600G,不能小于20M。

 

(2)For each datafile, a minimum of 320 kilobytes of space is allocated in the block changetracking file, regardless of the size of the data file. Thus, if you have alarge number of relatively small data files, the change tracking file is largerthan for databases with a smaller number of larger data files containing thesame data.

       --每个datafile,在tracking file里最少需要分配320k的空间,所以如果有大量的小data file, change tracking file 也会相对较大。

 

 

二.Enablingand Disabling Block Change Tracking

You can enable block change tracking whenthe database is either open or mounted. This section assumes that you intend tocreate the block change tracking file as an Oracle Managed File in the databasearea, which is where the database maintains active database files such asdata files, control files, and online redo log files. See "Overviewof the Fast Recovery Area" to learn about the database area and fastrecovery area.

--数据库在open 或者 mounted 状态都可以启用blockchange tracking.

 

2.1 To enable block change tracking:

(1)Start SQL*Plus and connect to atarget database with administrator privileges.

(2) Ensure thatthe DB_CREATE_FILE_DEST initialization parameter is set.

 

SQL> showparameter db_create_file_dest

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

db_create_file_dest                  string

 

If the parameteris not set, and if the database is open, then you can set the parameter withthe following form of the ALTERSYSTEM statement:

--如果该参数没有设置,并且数据库是open 状态,可可以修改该参数:

 

SQL> alter system setdb_create_file_dest = '/u01/bct/' scope=both sid='*';

System altered.

 

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE

----------------------------------------------- ------------------------------

db_create_file_dest                  string      /u01/bct/

SQL>

 

(3) Enable block change tracking.

Execute the following ALTER DATABASE statement:

SQL> alter database enable block changetracking;

Database altered.

 

You can alsocreate the change tracking file in a location that you choose yourself by usingthe following form of SQL statement:

--也可以在创建时指定其他目录:

SQL>alter database enable block change tracking using file '/mydir/rman_change_track.f' reuse;

The REUSE optiontells Oracle Database to overwrite any existing block change tracking file withthe specified name.

 

2.2 DisablingBlock Change Tracking

This section assumes that the block changetracking feature is currently enabled. When you disable block change tracking,the database removes the block change tracking file from the operating system.

 

To disable block changetracking:

(1)Start SQL*Plus and connect to atarget database with administrator privileges.

(2)Ensure that the target database ismounted or open.

(3)Disable block change tracking.

Execute thefollowing ALTER DATABASE statement:

SQL>alter database disable block change tracking;

 

2.3 Checking Whether Change Tracking is Enabled

You can query the V$BLOCK_CHANGE_TRACKING viewto determine whether change tracking is enabled, and if it is, the file name ofthe block change tracking file.

--可以使用v$block_change_tracking 视图查看tracking 启动情况。

 

To determine whetherchange tracking is enabled:

Enter the following query in SQL*Plus(sample output included):

 

SQL> col status   format a8

SQL> col filename format a60

SQL> select status, filename fromv$block_change_tracking;

STATUS  FILENAME

--------------------------------------------------------------------

ENABLED /u01/bct/XEZF/changetracking/o1_mf_7dnc623t_.chg

 

 

2.4 Changingthe Location of the Block Change Tracking File

To move the change tracking file, use the ALTERDATABASE RENAME FILE statement. The database must be mounted. Thestatement updates the control file to refer to the new location and preservesthe contents of the change tracking file. If you cannot shut down the database,then you can disable and enable block change tracking. In this case, you losethe contents of the existing block change tracking file.

--可以使用alter database rename 语句来修改changetracking file 文件的位置,该命令会更新控制文件里的信息,命令必须在mount 下执行,会如果DB 不能shutdown,那么可以先disable,然后指定新的位置在启动,但是这样会丢失以前在tracking file里的数据。

 

To change the location of the change tracking file:

 (1)Start SQL*Plus and connect to atarget database.

(2)If necessary, determine the currentname of the change tracking file:

SQL> select filename from v$block_change_tracking;

(3)If possible, shut down thedatabase. For example:

SQL> shutdown immediate

 

If you shut downthe database, then skip to the next step. If you choose not to shut down thedatabase, then execute the following SQL statements and skip all remainingsteps:

SQL> alter database disable block changetracking;

SQL> alter database enable block changetracking using file 'new_location';

 

In this case youlose the contents of the block change tracking file. Until the next time youcomplete a level 0 incremental backup, RMAN must scan the entire file.

 

(4)Using host operating systemcommands, move the change tracking file to its new location.

       --在OS 级别移动tracking file到新的目录。

(5)Mount the database and move thechange tracking file to a location that has more space. For example:

SQL>alter database rename file

  '/disk1/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg' to

  '/disk2/bct/RDBMS/changetracking/o1_mf_2f71np5j_.chg';

 

This statement changes the location of the change tracking file while preserving its contents.

 

(6)Open the database:

SQL> alter database open;

 

 

小结:

       在使用RMAN 增量备份的情况下,启动block change tracking,在做增量备份时会缩短RMAN 备份的时间, 因为不用扫描整个data file。 但是block tracking 也会带来其他的一些开销。 所以要根据实际情况决定是否启用block change tracking。



Block Change Tracking(块改变跟踪)主要用于RMAN备份的增量备份,Oracle 从10g开始提供此功能。在系统中默认关闭

当启用该共功能后,每当数据块发生改变时,相关信息就会记录在跟踪文件中,记录从上一次0级备份以来数据块的变化。相

关后台进程CTWR(Change Tracking Writer)。     当RMAN进行增量备份时,就不用完全遍历整个数据文件,查找需要备

份的数据块从而极大地提高了备份的效率。另外块儿跟踪开启或关闭跟RMAN能否增量备份并没有直接关系,块儿跟踪关闭

态下RMAN也可以正常增量备份,块儿改变跟踪只是影响备份效率。适合修改比例较少,对备份效率要求较高的场景。块修

改跟踪文件中最多只能保存基于相同0级的,最近8天的增量备份所需信息,因此如果超出,则块修改跟踪文件就起不到作用了。


1.对应的视图:

SQL> desc v$block_change_tracking 
 名称                                    是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 STATUS          VARCHAR2(10)
 FILENAME         VARCHAR2(513)
 BYTES          NUMBER


2.查看当前状态:

SQL> select * from v$block_change_tracking;

STATUS          FILENAME       BYTES
------------------------------ ------------------------------ ----------
DISABLED


3.开启数据库块儿跟踪:

SQL> alter database enable block change tracking using file '/oracle/oradata/orcl/chtrack.log';

数据库已更改。

   注:10g中有默认的跟踪文件,可以使用alter database enable block change tracking 就可开启块儿

           跟踪,11g中必须指跟踪使用的定文件(我的库是11g)。


4.查看开启后的状态:

SQL> select * from v$block_change_tracking;

STATUS                 FILENAME                                        BYTES
-----------------        -------------------------------------------         --------------
ENABLED             /oracle/oradata/orcl/chtrack.l og        11599872
         

5.查看对应进程:

SQL> host
[oracle@localhost ~]$ ps -ef|grep ctwr
oracle   21015     1  0 20:13 ?        00:00:00 ora_ctwr_orcl
oracle   21109 21088  0 20:19 pts/4    00:00:00 grep ctwr


6.关闭块儿跟踪特性:

SQL> alter database disable block change tracking;

数据库已更改。

SQL> select * from v$block_change_tracking;

STATUS          FILENAME       BYTES
------------------------------ ------------------------------ ----------
DISABLED



有一服务器有1T左右的数据,备份策略是(1+2)*2(1全备,2增量备份,备份保留2周期)的备份策略,随便增量备份减少了备份的体积,但是增量备份的时间,基本上和全备无差别,都是要近6小时(包括压缩)。这里没有完全体现出增量备份的强大之处,因为没有开启块修改跟踪,无论是增量备份还是全备都需要扫描所有的数据块。虽然网上说开启块修改跟踪可能会会触发一些bug,但是我找了下,10.2.0.4以后块修改跟踪还是比较稳定的,所以决定开启块修改跟踪功能,节约增量备份时间,提高系统性能。
 
一、开启块修改跟踪
[oracle@node1 bdump]$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 – Production on Thu Nov 3 11:13:54 2011
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter database enable block change tracking using file
  2   '/opt/oracle/oradata/ecp/Oracle_change.trace';
 
Database altered.
 
SQL> set long 200
SQL> col filename for a50
SQL> SELECT * FROM v$block_change_tracking;
 
STATUS     FILENAME                                                BYTES
———- ————————————————– ———-
ENABLED    /opt/oracle/oradata/ecp/Oracle_change.trace          11599872
 
SQL> !/opt/oracle/oradata/ecp/Oracle_change.trace
/bin/bash: /opt/oracle/oradata/ecp/Oracle_change.trace: 权限不够
 
SQL> !
[oracle@node1 ~]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace
-rw-r—– 1 oracle oinstall 11600384 11-03 11:05 /opt/oracle/oradata/ecp/Oracle_change.trace
[oracle@node1 ~]$ ps -ef|grep ctwr|grep -v grep
oracle    2771     1  0 11:05 ?        00:00:00 ora_ctwr_ecp
 
[oracle@node1 bdump]$ tail -11 /opt/oracle/admin/ecp/bdump/alert_ecp.log 
Thu Nov 03 11:05:47 CST 2011
alter database enable block change tracking using file
 '/opt/oracle/oradata/ecp/Oracle_change.trace'
Thu Nov 03 11:05:47 CST 2011
Block change tracking file is current.
Starting background process CTWR
CTWR started with pid=18, OS id=2771
Block change tracking service is active.
Thu Nov 03 11:05:48 CST 2011
Completed: alter database enable block change tracking using file
 '/opt/oracle/oradata/ecp/Oracle_change.trace'
通过这些都可以看出来,开启块修改跟踪功能,会启动CTWR进程,并且修改的块号会被记录到指定文件中
 
 
二、关闭块改变跟踪
SQL> alter database disable block change tracking;
 
Database altered.
 
SQL> col filename for a50
 
SQL> SELECT * FROM v$block_change_tracking;
 
STATUS     FILENAME                                                BYTES
———- ————————————————– ———-
DISABLED
 
SQL> !
[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace
ls: /opt/oracle/oradata/ecp/Oracle_change.trace: 没有那个文件或目录
[oracle@node1 bdump]$  ps -ef|grep ctwr|grep -v grep
[oracle@node1 bdump]$ tail -8 /opt/oracle/admin/ecp/bdump/alert_ecp.log 
Thu Nov 03 11:14:06 CST 2011
alter database disable block change tracking
Thu Nov 03 11:14:07 CST 2011
Block change tracking service stopping.
Thu Nov 03 11:14:07 CST 2011
Stopping background process CTWR
Deleted file /opt/oracle/oradata/ecp/Oracle_change.trace
Completed: alter database disable block change tracking
通过这些都可以看出来,关闭块修改跟踪功能,会关闭CTWR进程,并且删除跟踪文件(Linux系统会删除,Window不会)
 
三、块修改跟踪文件重命名
SQL> SELECT * FROM v$block_change_tracking;
 
STATUS     FILENAME                                                BYTES
———- ————————————————– ———-
ENABLED    /opt/oracle/oradata/ecp/Oracle_change.trace          11599872
 
SQL> alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'
  2  to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';
alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-19771: cannot rename change tracking file while database is open
 
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'
  2  to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> SELECT * FROM v$block_change_tracking;
 
STATUS     FILENAME                                                BYTES
———- ————————————————– ———-
ENABLED    /opt/oracle/oradata/ecp/Oracle_change.trace_new      11599872


[oracle@node1 bdump]$ tail -100 /opt/oracle/admin/ecp/bdump/alert_ecp.log |more

Thu Nov 03 11:22:34 CST 2011
alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'
to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'
Completed: alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'
to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'
Thu Nov 03 11:22:40 CST 2011
alter database open
Thu Nov 03 11:22:40 CST 2011
CHANGE TRACKING is enabled for this database, but the
change tracking file can not be found. Recreating the file.
Change tracking file recreated.
Block change tracking file is current.


[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace*
-rw-r—– 1 oracle oinstall 11600384 11-03 11:22 /opt/oracle/oradata/ecp/Oracle_change.trace
-rw-r—– 1 oracle oinstall 11600384 11-03 12:05 /opt/oracle/oradata/ecp/Oracle_change.trace_new


数据库在open状态下不能修改,所以必须把数据库重启至mount状态才能够修改,如果库不能重启,那么可以先关闭原块修改跟踪功能,再开启块修改跟踪功能。
两种方法比较:
1、都会重新建立一个跟踪文件
2、如果是重命名,老的跟踪文件不会自动被删除,需要人工删除
3、推荐使用关闭跟踪功能,然后在重新制定跟踪文件开启跟踪功能















About Me

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

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

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

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

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

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

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

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成

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

● 版权所有,欢迎分享本文,转载请保留出处

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

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。


DBA笔试面试讲解
欢迎与我联系

阅读(1594) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册