Enabling Faster Incremental Backups in Oracle 10g

In this blog entry let me introduce you to a new capability in Oracle 10g called Block Change Tracking, which can help DBA’s do faster incremental backups via RMAN (Recovery Manager).

Once this new capability in Oracle 10g is enabled it start recording the modified since last backup and stores the log of it in a block change tracking file.

Later while doing backup RMAN uses this file to determine which blocks are needed to be backed up?

Logically as we can see, this process improves the performance as RMAN does not have to scan whole datafile to detect which block in it has changed and needs backup.

This may not show any significant impact on smaller database but definitely will have big impact on bigger databases as now with this new functionality it does not have to scan full datafile but only need to query block change tracking file.

To create the Change Tracking file and enable Change Tracking, make sure the database is either open or mounted. Then issue following SQL:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING
FILE ‘c:\oracle\oradata\rkt\block_track\block_track_file01.log’;

Database altered.

Once you issue above SQL, Oracle creates a new background process which is responsible for writing data to the block change tracking file, which is called the block change writer CTRW.

To verify the status of block change tracking, use following SQL:

SQL> SELECT filename, status, bytes FROM v$block_change_tracking;

FILENAME

——————————————————

STATUS BYTE

————— ————–

C:\ORACLE\ORADATA\RKT\BLOCK_TRACK\BLOCK_TRACK_FILE01.LOG

ENABLED 1024

To disable block change tracking, use following SQL:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Posted in Articles:: Oracle Backup, Blog::Oracle Backup | No Comments »

creating UNIQUE index on a table with existing non unique values.
Monday, May 7th, 2007
Today I came up with this challenge of creating UNIQUE index on a table with exiting non unique value. After lot of r&d I came up with following very interested solution:

I am using scott schema to show my solution.

SQL> create table test as select deptno from emp;

Table created.

SQL> select deptno from test;

DEPTNO
———-
20
30
30
20
30
30
10
20
10
30
20

DEPTNO
———-
30
20
10

14 rows selected.

SQL> alter table test add constraint unique_index unique(deptno) disable;

Table altered.

SQL> create index unique_index on test(deptno);

Index created.

SQL> alter table test enable novalidate constraint unique_index;

Table altered.

SQL> insert into test values (20);
insert into test values (20)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UNIQUE_INDEX) violated