Block Change Tracking in Oracle

Oracle Block change Tracking is introduced in 10g.Its used to track the changed blocks of a database to a tracking file and used for better performance of incremental backups. If block change tracking is enabled for a database RMAN uses block change tracking file to identify changed blocks for incremental backups instead full data file scans during the backup.

By default, Block change tracking is disabled for a database. You can Enable or disable the change tracking when the database is open or mounted.

You can check the status of block change tracking using the below query.

SQL> SELECT FILE,STATUS,BYTES FROM V$BLOCK_CHANGE_TRACKING;

Enable Block Change Tracking:

Use the below command to enable block change tracking.

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Create change tracking file in a specified location.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE /oracle/fra/bct.log';

Create change tracking file in ASM disk group.

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DGROUP_FR1';

Disable Block Change Tracking:

Use the below command to disable block change tracking

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Note: When disabled oracle will automatically delete the tracking file from physical  

         location.

Use the below Query to monitor the status of block change tracking

SQL> SELECT FILE,STATUS,BYTES FROM V$BLOCK_CHANGE_TRACKING;

Change location of Tracking file:

you must disable block change tracking before you move it to new location.

Step1: Disable the change tracking

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Step2: Re-enable it with the new location

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

Create/Drop RMAN Catalog

Create a Catalog:

• Connect to the RMAN instance as sysdba
• SQL>Create user rcat identified by rcat;
• SQL>Grant connect,resource,recovery_catalog_owner to rcat;
• rman catalog rcat/rcat@rmanalias
• RMAN>create catalog;

Drop a Catalog:

• $ rman catalog rcat/rcat@rmanalias
• RMAN> drop catalog;
• Recovery catalog owner is RCAT
• Enter DROP CATALOG command again to confirm catalog removal
• RMAN> drop catalog;
• Recovery catalog dropped
• SQL>Drop user rcat cascade;

Register/Unregister Target in RMAN

Register a Target database in RMAN catalog:
Make necessary modifications for listener.ora and tnsnames.ora files to connect from RMAN server to target.

Create password file if not existing for the target database to connect as SYS from RMAN.

Bounce the listener on target side if the service is in blocked state.


$rman catalog rcat/xxx@rmanalias target sys/xxx@targetalias

RMAN>register database;

To check database is registered you need to login in to RMAN database as catalog owner (rcat).

$Sqlplus rcat/xxx

SQL>Select *from rc_database;

Unregister a Target database from RMAN catalog:

Login to RMAN database as catalog owner.

$Sqlplus rcat/xxx

SQL>execute dbms_rcvcat.unregisterdatabase(DB_KEY,DB_ID);
            Note: you can get the values for DB_KEY and DB_ID from the data 
                     dictionary of catalog

SQL>Select *from rc_database;

Recreate AWR Tables in 10g

If any issue with the AWR tables we can recreate them using the below steps.

 1. Disable AWR statistics by setting the statistics level to basic, e.g.: 

• sqlplus / as sysdba
• show parameter statistics_level 
• alter system set statistics_level = basic scope=both; 

2. Stop and restart the instance in restricted mode, e.g.: 

• sqlplus / as sysdba
• shutdown immediate 
• startup restrict 

3. Drop and recreate the AWR objects, e.g.: 

• sqlplus / as sysdba
• @?/rdbms/admin/catnoawr.sql 
• alter system flush shared_pool; 
• @?/rdbms/admin/catsvrm.sql 
• @?/rdbms/admin/utlrp.sql 

4. Then re-enable the AWR statistics gathering as required, by setting          
    STATISTICS_LEVEL back to its original value, and restart the instance 
    normally, e.g.: 

• sqlplus / as sysdba
• alter system set statistics_level = scope=both; 
• shutdown immediate 
• startup 

isqlplus in oracle

What is iSQL*Plus?

iSQL*Plus is a browser-based interface to an Oracle database. It is a component of the SQL*Plus

product. iSQL*Plus has a server-side listener process that must be started before you can connect

with a browser.

To start this server process

$isqlplusctl start

To stop this service

$isqlplusctl stop

After the server process is started, perform the following steps to connect to iSQL*Plus:

1. Connect to the Internet or your intranet, and start your Web browser.

2. Enter your the iSQL*Plus URL. The iSQL*Plus URL looks like the following:

http://hostname:port/isqlplus.

The port number used by iSQL*Plus is usually 5560 unless the Oracle Universal Installer (OUI)

detects something already using that port. Check $ORACLE_HOME/install/portlist.ini

to find the port used by iSQL*Plus.

3. Press Enter to go to the URL. The iSQL*Plus Login page is displayed in your 
    Web browser.

4. Enter your Oracle username and password in the Username and Password
    fields. If you do not know your Oracle username and password, ask your
    database administrator.

5. Leave the Connection Identifier field blank to connect to the default
    database.Enter the Oracle Net database alias to connect to a database 
    other than the default.

6. Click Login to connect to the database. The iSQL*Plus Workspace is displayed in your Web 
    browser

RAC on/off

On 10R2 if the Oracle binaries are installed with the RAC, one can’t use those binaries to start Oracle Instance if CRS is not running, so in case when one needs to start the instance without CRS one needs to rebuild the oracle and it’s libraries to start the instance.

To turn off RAC

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_off

$ cd $ORACLE_HOME/bin

$ relink oracle

To turn on RAC

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_on

$ cd $ORACLE_HOME/bin

$ relink oracle