Enabling Trace in Oracle

Tracing a session is a imporatant phase of a problem analysis for a query,job in Oracle.There are various methods of enabling trace for sessions.Following are a few methods for enabling trace.
To check if any trace is enabled in the current database, the following query can be used:
select * from DBA_ENABLED_TRACES ;

1. Enable trace at instance level
Start trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = TRUE;
Stop trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = FALSE;

2. Enable trace for the current session
Start trace:
ALTER SESSION SET sql_trace = TRUE; (or)EXECUTE dbms_session.set_sql_trace (TRUE); (or)EXECUTE dbms_support.start_trace;Stop trace:
ALTER SESSION SET sql_trace = FALSE; (or)EXECUTE dbms_session.set_sql_trace (FALSE); (or)EXECUTE dbms_support.stop_trace;

3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
Start trace:
EXECUTE DBMS_MONITOR..start_trace_in_session (SID, SERIAL#);
With Waits and Binds
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE);
With Waits
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE, binds=>TRUE);

Stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

Oracle 10g - RMAN Block change tracking

There are performance issues associated with incremental backups where most of the times we have issues with the CPU/IO being consumed more. In Oracle 10g it is possible to track changed blocks using a change tracking file.

Prior to introduction of Oracle 10g block change tracking (BCT), RMAN had to scan the whole datafile to and filter out the blocks that were not changed since base incremental backup and overhead or incremental backup was as high as full backup. Oracle 10g new feature, block change tracking, minimizes number of blocks RMAN needs to read to a strict minimum. With block change tracking enabled RMAN accesses on disk only blocks that were changed since the latest base incremental backup.

Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups.

The current change tracking status can be displayed using the following query:
SELECT status FROM v$block_change_tracking;

Change tracking is enabled using the ALTER DATABASE command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/ts1/oradata/block_change_track.dbf';

The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.

Change tracking can be disabled using the following command:
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Renaming or moving a tracking file can be accomplished in the normal way using the ALTER DATABASE RENAME FILE command. If the instance cannot be restarted you can simply disable and re-enable change tracking to create a new file. This method does result in the loss of any current change information.

Background Process – Change Tracking Writer (CTWR). This process takes care of logging information about changed blocks in block change tracking file.