当前位置:首页 > 开发 > 数据库 > 正文

Oracle rman change tracking CTWR(原创)

发表于: 2014-04-25   作者:czmmiao   来源:转载   浏览次数:
摘要: block change tracking介绍Block chage tracking是Oracle10g的一个新特性,Block change tracking进程记录自从上一次备份以来数据块的变化,并把这些信息记录在跟踪文件中。RMAN使用这个文件判断增量备份中需要备份的变更数据。这极大的促进了备份性能,RMAN可以不再扫描整个文件以查找变更数据。 为此Oracle引入了一个新的后台进程,CT

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

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

Oracle rman change tracking CTWR(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
申明本博客转至http://www.codeproject.com/Articles/338724/SQL-Change-Tracking-on-Table 在阅读
可以用Change命令来改变字符串(即替换字符串)。语法如下: C[HANGE] sepchar old [sepchar [new [
Time-Stamped Dimensions If there is any uncertainty about requirements for historic data, the
Type 3 Changes In most dimensional schemas, the bulk of changes to source data generate type
今天第一次学习RMAN的使用。先登录系统: 数据库未启动,rman命令不能执行。在rman下 也可以 startu
这两天在论坛上看到有人在询问“如何将EM的访问方式由HTTPS便成为HTTP”,其实很简单,看下面实验。
Overview of Table Clusters A table cluster is a group of tables that share common columns and
“Tracking Branches” And “Remote-Tracking Branches” Commands used in this section: git add
多用户环境下的数据并发访问及数据一致性简介 在只有单一用户的数据库中,用户可以任意修改数据,而
Oracle中的RBO Oracle的优化器有两种优化方式: 基于规则的优化方式:Rule-Based Optimization(RBO
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号