Introduction
Prior to Oracle 10g, one of the most important advices given to VLDB administrators was to store their non-changing static data segments within read-only tablespaces for backup performance. Since RMAN just skips any read-only tablespace during incremental backups (other tablespaces were scanned entirely for any block change), read-only tablespace usage had significant impact on incremental backup performance for VLDB sites. This was so-called pseudo incremental backup technique, which was strongly coupled with read-only tablespaces.
By 10g, Oracle introduces genuine incremental backup with change tracking file. New CTWR background process records changed blocks into change tracking file. Therefore, RMAN can directly find the blocks necessary for incremental backup without reading all blocks of data files.
In this post, you will find how this tiny file removes the requirement of read-only tablespace usage for incremental backup performance.
Problems in Using Readonly Tablespaces
In many sites, due to inappropriate physical design (incorrect implementation of data windowing or mapping between data segments and tablespaces), some hot data subject to change and some cold data used only for reporting purposes fall into the same tablespaces. This fallacy in design removes the chance of using readonly tablespaces in those database environments.
The second problem people face with read-only tablespaces is the default characteristics of RMAN. RMAN acts in a lazy way during the full database restore process and it just skips restoring the read only tablespaces.
Change Tracking File
In default Oracle 10g installation, Oracle does not create change tracking file. You need to create this file in order to use it. Fortunately, once you create this file, RMAN automatically uses it without any intervention.
SQL> alter database enable block change tracking;
Database altered.
SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
------- --------------------------------- --------
ENABLED +DGROUP1/asmtest/changetracking/... 16457472
Contrary to common belief, keep in mind that this file does not mark an aggressive growth trend correlated with total database size. For an eight terabytes database, it is only 111MB of size for us.
Change Tracking File OFF
Firstly, let’s see the positive effect of read-only tablespace usage when change tracking is disabled.
In a tablespace incremental backup, Oracle needs to read whole datafiles of tablespace to find changed blocks in them. This time consuming operation decreases the overall performance of incremental backup.
RMAN> backup incremental level 1 tablespace users;
Starting backup at 17-APR-08 19:42:27
...
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 17-APR-08 19:43:12
If, by altering tablespace into readonly mode, Oracle is informed that tablespace is static, RMAN will just simply skip datafiles of that tablespace.
RMAN> sql 'alter tablespace users read only';
sql statement: alter tablespace users read only
RMAN> backup incremental level 1 tablespace users;
Starting backup at 17-APR-08 19:47:09
...
skipping datafile 00004 because it has not changed
...
Finished backup at 17-APR-08 19:47:10
Therefore, for databases, that does not use change tracking and use incremental backup strategy readonly tablespaces are critical for backup performance.
Change Tracking On
Let’s look exactly the same scenario but this time with change tracking option enabled.
RMAN> sql 'alter tablespace users read write';
sql statement: alter tablespace users read write
RMAN> backup incremental level 0 tablespace users;
Starting backup at 17-APR-08 19:49:52
...
SQL> alter database enable block change tracking;
Database altered.
All changes on tablespace after level 0 backup are recorded into tracking file. If the tablespace has a readonly characteristic, no record will reside within tracking file on that tablespace.
RMAN> backup incremental level 1 tablespace users;
Starting backup at 17-APR-08 19:50:12
...
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-APR-08 19:50:13
Magically, although the tablespace is in read write mode incremental backup takes only one second instead of 45 seconds when the change tracking option is on.
Conclusion
By 10g, Oracle introduces the genuine incremental backup. Contrary to previous versions, it reduces the block change tracking activity from tablespace level granularity to segment level granularity.
If you are at the stage of reengineering your VLDB, use readonly tablespaces for their numerous merits by keeping their drawbacks in mind. However if changing your entire physical database model requires a lot of work, do not bother much about the incremental backup performance and just use change tracking.
