Thursday, May 18, 2006

How to use Fast Incremental Backups with Block Change Tracking with Oracle 10g

In Oracle 9i we could create incremental backups with level 0 to 4.
A level 0 backup is a full backup.
In Oracle 10g there are only incremental level 0 and 1 backups.

Starting with Oracle 10g RMAN can take incremental backups without having to read the entire datafiles in order to find out which blocks have changed since the last backup.
This new feature is called FAST INCREMENTAL BACKUP.
The new technology used for this feature is called BLOCK CHANGE TRACKING.

You enbale block change tracking by making an entry in the controlfile:
SQL> alter database enable block change tracking;
alter database enable block change tracking
*
ERROR at line 1:
ORA-19773: must specify change tracking file name

If you do not have DB_CREATE_FILE_DEST specified (used for OMF => oracle managed files)
you will encounter an error if you do not specify a name fot the change tracking file
SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

So I try to alter this parameter:
SQL> alter system set db_create_file_dest='/home/oracle/file_create';
alter system set db_create_file_dest='/home/oracle/file_create'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_create_file_dest destination string cannot be
translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory

Oracle checks if the location existes before it allows us to change this parameter!
SQL> ! mkdir -p /home/oracle/file_create
SQL> alter system set db_create_file_dest='/home/oracle/file_create';
System altered.

Now we can enable block change tracking
SQL> alter database enable block change tracking;
Database altered.
As an alternative you can specify a user managed filename if you do not want to use the parameter:
alter database enable block change tracking using file '/home/oracle/change.trc';

Let's take a look at the alert_log now:
alter database enable block change tracking
Thu May 18 06:39:14 2006
ORA-19773 signalled during: alter database enable block change tracking...
Thu May 18 06:39:46 2006
alter database enable block change tracking using file
'/home/oracle/change.trc'
Thu May 18 06:39:46 2006
Block change tracking file is current.
Starting background process CTWR
CTWR started with pid=21, OS id=26871
Block change tracking service is active.
Thu May 18 06:39:47 2006
Completed: alter database enable block change tracking using file
'/home/oracle/change.trc'


This is what oerr returns for this error:
[oracle@kr12-KL2 bdump]$ oerr ora 19773
19773, 00000, "must specify change tracking file name"
// *Cause: No file name was specified with the ALTER DATABASE ENABLE
// CHANGE TRACKING command, and the DB_CREATE_FILE_DEST parameter
// was not set.
// *Action: Either specify a file name, or set the DB_CREATE_FILE_DEST
// parameter.


Let's take a look at the change tracking file:
[oracle@edchr3p8 oracle]$ cd file_create/
[oracle@edchr3p8 file_create]$ ll
total 4
drwxr-x--- 3 oracle oinstall 4096 May 3 15:55 ORCL
[oracle@edchr3p8 file_create]$ cd ORCL/
[oracle@edchr3p8 ORCL]$ ll
total 4
drwxr-x--- 2 oracle oinstall 4096 May 3 15:55 changetracking

[oracle@edchr3p8 ORCL]$ cd changetracking/
[oracle@edchr3p8 changetracking]$ ll
total 11348
-rw-r----- 1 oracle oinstall 11600384 May 3 15:55 o1_mf_25l66r5c_.chg

# this is an OMF change tracking file!

The change tracking file has an initial size of 10Mb.
How to monitor the change treacking file from inside Oracle:
SQL> desc v$block_change_tracking
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(10)
FILENAME VARCHAR2(513)
BYTES NUMBER

SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
---------- -------------------------------------------------- ----------
ENABLED /home/oracle/change.trc 11599872

Now that we have enabled block change tracking a new background process called CTWR (change track writer) is started and it will automatically be started for every new instance.
[oracle@kr12-KL2 ~]$ ps -ef grep orcl
oracle 9531 1 0 May16 ? 00:00:00 ora_pmon_orcl
oracle 9533 1 0 May16 ? 00:00:02 ora_psp0_orcl
oracle 9535 1 0 May16 ? 00:00:00 ora_mman_orcl
oracle 9537 1 0 May16 ? 00:00:06 ora_dbw0_orcl
oracle 9539 1 0 May16 ? 00:00:16 ora_lgwr_orcl
oracle 9541 1 0 May16 ? 00:00:17 ora_ckpt_orcl
oracle 9543 1 0 May16 ? 00:00:25 ora_smon_orcl
oracle 9545 1 0 May16 ? 00:00:00 ora_reco_orcl
oracle 9547 1 0 May16 ? 00:00:03 ora_cjq0_orcl
oracle 9549 1 0 May16 ? 00:00:18 ora_mmon_orcl
oracle 9551 1 0 May16 ? 00:00:02 ora_mmnl_orcl
oracle 9554 1 0 May16 ? 00:00:00 ora_d000_orcl
oracle 9556 1 0 May16 ? 00:00:00 ora_s000_orcl
oracle 9560 1 0 May16 ? 00:00:00 ora_qmnc_orcl
oracle 9574 1 0 May16 ? 00:00:06 ora_q000_orcl
oracle 10746 1 0 May16 ? 00:00:00 ora_q001_orcl
oracle 26854 26850 0 06:38 ? 00:00:00 oracleorcl (DESCRIPTION=
(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26871 1 0 06:39 ? 00:00:00 ora_ctwr_orcl
oracle 27778 1 0 06:50 ? 00:00:00 ora_j000_orcl
oracle 27780 27153 0 06:50 pts/0 00:00:00 grep orcl


CTWR will track addresses of blocks which have changed since the last backup in the change tracking file from now on.
RMAN can use this information for the next incremental backup. It will be able to find out which block must be written to the backupset by just reading the change tracking file.
RMAN will not have to read the entire datafiles into the SGA in order to find out which blocks must be backed up as it had to do before 10g.
This methode is much faster.

Now what happens if we lose the change tracking file or if it gets corrupted?
Let's corrupt is:
[oracle@edchr3p8 changetracking]$ echo 'hallo' > o1_mf_25l66r5c_.chg
And now let us startup the database with a corrupt change tracking file!
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file:
'/home/oracle/file_create/ORCL/changetracking/o1_mf_25l66r5c_.chg'
ORA-27038: created file already exists
Additional information: 1
ORA-27047: unable to read the header block of file
Linux Error: 2: No such file or directory
Additional information: 1
Wed May 3 16:03:30 2006
CHANGE TRACKING is enabled for this database, but the
change tracking file can not be found. Recreating the file.

Oracle tries to automaqtically recreate the missing file but it cannot overwrite an existing on!

Let us create some changed blocks now so Oracle cannot track them in the change tracking file!

SQL> update hr.employees set salary=salary*1.1;
107 rows updated.
SQL> commit;
Commit complete.


Now let us take a look at the ALERT_LOG:

CHANGE TRACKING ERROR 19756, disabling change tracking
Wed May 3 16:07:24 2006
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_ctwr_6628.trc:
ORA-19756: corrupt block number 18 found in change tracking file
ORA-19750: change tracking file: '/home/oracle/file_create/ORCL/changetracking/o1_mf_25l6qckh_.chg'
Block change tracking service stopping.
Deleted Oracle managed file /home/oracle/file_create/ORCL/
changetracking/o1_mf_25l6qckh_.chg


Oracle automatically deletes a currupted change tracking file and stops block change tracking.

Let us now take an incremental backup with RMAN:
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 03-MAY-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/

2006_05_03/ o1_mf_nnnd1_TAG20060503
T160745_25l6x2s1_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/

backupset/2006_05_03/o1_mf_ncsn1_TAG20060503
T160745_25l6z4mv_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-MAY-06

RMAN was able to take an incremental backup but now it had to use the old methode => read all the blocks in order to find out which ones have changed since the last backup;

Next I replace the change tracking file with an old version in order to see what happens when I try to take an incremental backup:

SYS @10gR2 SQL > ! mv /home/oracle/change.old /home/oracle/change.trc
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 19-MAY-06
using channel ORA_DISK_1
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_nnnd1_TAG20060519T123227_26v7owgo_.bk p tag=
TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_example_26v7oyjx_.dbf tag=TAG20060519T123227
recid=3 stamp=590848373
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/sales1.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_sales_26v7pr8j_.dbf tag=TAG20060519T123227 rec id=4 stamp=590848386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_users_26v7qb8t_.dbf tag=TAG20060519T123227 rec id=5 stamp=590848394
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_ncsn1_TAG20060519T123227_26v7qfb5_.bk p tag=TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
RMAN> list backup of tablespace sysaux;
There is no backup!!!
RMAN has found out that I tried to cheat and that it was not able to create an incremental backup for all files by using the change tracking file.
This is what oerr says about ORA-19694:
[oracle@lutzasm ~]$ oerr ora 19694
19694, 00000, "some changed blocks were not found in the change tracking file"
// *Cause: A backup or copy found that some changed blocks had not been
// recorded in the change tracking file. The details of which files
// and blocks are affected will be in an Oracle trace file.
// *Action: This indicates that there is a problem with the change tracking
// feature. Disable change tracking and re-start the backup.


3 Comments:

At Fri Jun 23, 10:41:00 AM, Blogger Byte64 said...

Lutz,
i think you should remark the fact that block change tracking is available only with the enterprise edition.

When i first read the block change tracking feature on oracle magazine i got very excited about it, just to discover shortly afterwards that on my standard edition databases i was getting plainly: ORA-00439: feature not enabled:Block Change Tracking as soon as i tried alter database enable block change tracking;.

Cheers,
Flavio

 
At Sun Jun 25, 03:36:00 PM, Blogger lutz_hartmann said...

Hi Flavio, thank you for pointing this out!
Of course I should have mentioned this and I will change the post right away, so everybody will have the info without having to read the comments.

There must be some reasons why you should pay more for the Enterprise Edition than for the Standard Edition after they have made RAC (limited to two nodes) available in the Standard Edition with 10g.

 
At Fri Dec 01, 09:05:00 PM, Anonymous RobH said...

Do you need to put the "for recover of copy" syntax there?

because Note:
Even when the BACKUP INCREMENTAL LEVEL 1 ... FOR RECOVER OF COPY command is used with DEVICE TYPE SBT to create a backup on tape, the first time it is used it creates the image copy on disk, and does not write any backup on tape. Subsequent incremental level 1 backups can be created on tape once the image copy is on disk.

This means we need double disk to hold the backup, even if doing to tape?!

 

Post a Comment

<< Home