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 USINGFILE ‘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;

Updating a table from another table

Updating a table from another table

Lets first create following tables to show the concept
SQL> CREATE TABLE table_1 ( c_code NUMBER PRIMARY KEY, c_name VARCHAR2(40) );Table created.
SQL> CREATE TABLE table_2 ( c_code NUMMBER PRIMARY KEY, c_name VARCHAR2(40) );Table created.
Note:We need a PRIMARY KEY or an UNIQUE KEY on source table atleast from where we will be getting the values because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambigous situation.
SQL> INSERT INTO table_1 VALUES ( 1, ‘First Row’ );SQL> INSERT INTO table_1 VALUES ( 2, ‘Nothing’ );SQL> INSERT INTO table_2 VALUES ( 2, ‘Second Row’ );
SQL> UPDATE ( SELECT a.c_name a_col, b.c_name b_col2 FROM table_1 a, table_2 b3 WHERE a.c_code = b.c_code )4 SET a_col = b_col5 /
1 row updated.
SQL> select * from a2 /
c_code c_name————– ————————-1 First Row2 Second Row
Below are few more variance sql for same purpose, but SQL above gives best chance to optimizer in getting a good execution plan.
SQL> UPDATE table_1 a2 SET c_name = ( SELECT c_name3 FROM table_2 b4 WHERE b.c_code = a.c_code)5 WHERE EXISTS ( SELECT c_name6 FROM table_2 b7 WHERE b.c_code = a.c_code )8 /
1 row updated.
SQL> UPDATE table_1 a2 SET c_name = ( SELECT c_name3 FROM table_2 b4 WHERE b.c_code = a.c_code )5 WHERE a.c_code IN ( SELECT c_code6 FROM table_2 )7 /
1 row updated.