Understanding Oracle Trace Utility – Oracle Database 10g


Some times the situation demands more information about an event. Example if we take a case of some error coming in our database because of some application. In this case its the job of developer to drill down into the application and get the exact queries those are failing. I have seen many situations where we get ORA-0600 error or ORA-07445 errors. In such situation, if one wants to debug the issue, then he need to know the exact query which is failing so that he should be able to reproduce the issue from command prompt and another thing is when he can reproduce the issue form command prompt, then he can get detailed level of information about the running query by setting the tracing levels.
This post is all about setting the tracing at session level (either own session or some other session) and getting detailed information about the query. I will show you, what are the different levels of tracing events that are present and what are the various levels of tracing.

I will discuss 2 types of tracing here

  1. SQL tracing
  2. Event based tracing

SQL Tracing:

Session Level:

One of the simplest method to trace SQL is to use “alter session set SQL_TRACE=true” command and oracle will generate the trace file for the SQL statment that you run in that session.

System Level:

Also you can set the SQL trace at system level using “alter system set SQL_TRACE=true” command. But be careful about this, because this is going to generate huge trace file and especially when the number of users are large, this will degrade database performance.

The pre-reqs for enabling the SQL trace either at session level or at system level is ‘TIMED_STATISTICS’ parameter should be set to “TRUE” and “STATISTICS_LEVEL” should be set minimum to “TYPICAL”. It can be set to “ALL” as well, which is the higest level of tracing information. While setting the statistics level to ALL, you have to be careful.

Important *** If possible do not set the statistics level to all at system level, set only at session level, else the database performance will be impacted as database has to produce a very detail level of tracing. Also do not set SQL trace at system level, set only at session level. Setting SQL trace at system level will generate huge trace file and database performance will degrade.

For other sessions:

You can also set SQL_TRACE for some other session as well using DBMS_SYSTEM package.

procedure DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION will enable you to do this.

login as sysdba and execute

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<sid>,<serial#>,true) – for enabling the tracing

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<sid>,<serial#>,false) – for disabling the tracing

Event Based Tracing

Session Level:

Event level tracing is a way to create detail information about some kind of event. Not what exactly we mean by event.

“An event is simply a flag to the Oracle kernel to tell it to emit some trace messages or to add some additional processing or to activate some new functionality.”

Some events are used by support analysts and developers to force certain conditions to occur for testing purposes.

For example the event 10046 will enable SQL statement timings. So setting this event tells oracle kernel to through timing information about SQLs in its trace files. Like wise we have several events.

The details of all the events and actions are provided in $ORACLE_HOME/rdbms/mesg/oraus.msg file.

For each of these events mentioned in oraus.msg file, we can set the level. These are the level of information that kernel should put in the trace files. Below are the various trace levels present in Oracle tracing utility.

  • Level 0 = No statistics generated
  • Level 1 = standard trace output including parsing, executes and fetches plus more.
  • Level 2 = Same as level 1.
  • Level 4 = Same as level 1 but includes bind information
  • Level 8 = Same as level 1 but includes wait’s information
  • Level 12 = Same as level 1 but includes binds and waits

Also setting any event other that trace (10046) should not be done without the guidance of Oracle support.

There are many ways in which we can enable event level tracing. Below are some of the methods to do that.

Method 1: Setting the trace for event 10046 level 8

It is better to make a new connection before you start a trace each time, if not information will be appended if any trace has already been started.

alter session set max_dump_file_size=unlimited;
alter session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;

We should be able to distinctly identify our trace file from other trace files. For that we can set tracefile_identifier parameter.


alter session set tracefile_identifier = index_trace;

So setting this will set the name of our trace files as %index_trace.trc

A simple example of analyzing the index by setting the trace event 10236 is shown below. Event no 10236 is – dump redo on object no. or block type mismatch errors 1410/8103.

SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL> alter session SET timed_statistics = true;

Session altered.

SQL> alter session set STATISTICS_LEVEL = ALL ;

Session altered.

SQL> alter session set tracefile_identifier = index_trace;

Session altered.

SQL> alter session set events ‘10236 trace name context forever, level 8’;

Session altered.

SQL> analyze index msc.test_idx validate structure online;
analyze index msc.test_idx validate structure online
ERROR at line 1:
ORA-01410: invalid ROWID

This is to check some kind of corruption.

SQL> ALTER SESSION SET EVENTS ‘10236 trace name context off’;

Session altered.

Method 2: Using DBMS_SYSTEM package.

Another way to set the event trace is to use the package DBMS_SYSTEM as given below.


Example: exec DBMS_SYSTEM.SET_EV(10,20,10046,8,”);

This will set the tracing for any session.

Method 3: Using DBMS_SUPPORT package.

Another method to set tracing is to use DBMS_SUPPORT package. This package is not installed by default and is in fact undocumented and indeed on some platforms and versions its not even shipped and you will need to talk to Oracle support and get it from metalink.

Installaing the package first:

SQL> @?/rdbms/admin/dbmssupp.sql

Package created.

Package body created.

Using DBMS_SUPPORT package for tracing.


Example: exec dbms_support.start_trace_in_session(10,20,waits=>true,binds=>false);

To stop tracing: exec dbms_support.stop_trace_in_session(10,20);

Start tracing in your own session:

exec dbms_support.start_trace(waits=>true,binds=>false);

Stop tracing in your session: exec dbms_support.stop_trace;

System Level:

If you want to set the event level tracing at system level, then we can either use alter system instead of alter session or we can use init.ora parameter to set the events.

Method 1: Using Alter System

SQL> alter system set events ‘10046 trace name context forever, level 1’;

System altered.

To turn off the tracing at system level again we can use the below command.

SQL> alter system set events ‘10046 trace name context off’;

System altered.

Method 2: Using init.ora parameter

init.ora parameter name is “event” and it can be set as given below. But be careful while setting up this as this will generate huge trace files.

event = “10046 trace name context forever, level 12”




Oracle Database 10g – Recovery Scenario

Recovery Scenarios


Recovering a Database in NOARCHIVELOG Mode:

When the database is running in noarchivelog mode, then its very difficult to perform the complete recovery. Because while the database is running, the online redo log file will get overwritten as the transactions proceeds. Following scenerio explains the complete and incomplete recovery.

Scenario 1: Incomplete Recovery


Losing Datafiles in NOARCHIVELOG Mode


Media failure happens and damages a datafile. Datafile is unusable now.


In this case since we lost a datafile and database is running in noarchivelog mode, you cannot recover the datafile from the online logs, because the datafile itself is lost. You need to restore the complete database from the latest old consistant backup. Restoring only that datafile wont help, because that datafile wont be consistant with respect to other datafile. Also we cannot apply the current online redo log files to that 1 datafile, since the changes must have been over written since we have taken a backup of that datafile.
If the backup is taken just few minutes back and a media failure has happned after opening the database, then there is a chance that restoring only that database and applying the online redo log file will recover the datafile and database can be opened. This is because the redo log might not have been over written within those few minutes. But this is a very very rare situation. Here we can have a complete recovery in no archivelog mode.

So the general solution applicable to this scenerio will be to restore the complete database (datafile and controlfile) from the latest old consistant backup. Since we dont take a backup of online redo log files, we have to use the current online redo log files. But the SCN in current online redo log file is different than the restored datafiles and controlfiles, we need to wipe out the content of current redo log files. This is called changing the incarnation. This is possible by using “alter database open resetlogs”.
Follow below steps to recover the database.


1) Shut down the database

2) Restore the backup to the appropriate location.

cp (backup location) (datafile location)

In case the current datafile location is damaged due to media failure, you can restore the backup to some other location. In this case you need not restore the control files. You have to create a new control file as given below.

GROUP 1 ‘/u03/oradata/orcl/redo01.log’ SIZE 50M,
GROUP 2 ‘/u03/oradata/orcl/redo02.log’ SIZE 50M,
GROUP 3 ‘/u03/oradata/orcl/redo03.log’ SIZE 50M

you can obtain the above script for your database using below commands


Once you get the “Create Controlfile” command, change the location of datafiles and online redo log files to new location.

Change the location of control file in init.ora file (control_files is the name of parameter).
Run the script and create the control file to new location.


In order to allow the database to reset the online redo logs, you must first mimic incomplete recovery:



Recovering a Database in NOARCHIVELOG Mode:

Scenario 2: Complete Recovery


Losing Datafiles in ARCHIVELOG Mode. Performing closed database recovery.


Media failure happens and damages a datafile. Datafile is unusable now. We can shutdown the database and recover.


If the datafile that is lost belongs to SYSTEM tablespace, then the database will get shutdown. If the media failure is temporary, then fix the media problem and then start the database. Usually crash recoery will restore the lost transactions and database will be up and consistant.

If there is a major problem with media, then the database needs to be restored to alternate location and recovery needs to be performed.

1) You can determine which file needs to be recovered by quering V$RECOVER_FILE table after mounting the database.

2) The files which needs to be recovered can be copied to alternate location using OS copy commands. If the current database files are damaged and cannot be used, then use the most recent backup of those files to restore to alternate location.

3) Once restored, you need to change the location of files in control file. Use the below command to do so after mounting the database.

4) Recovering the database involves applying the archivelog files and online redo log files.
Before applying the archivefiles and recovering the database, make sure that all the datafiles are online.
ALTER DATABASE DATAFILE ‘/oracle/dbs/user_01.dbf’ ONLINE;
5) You can issue any of the below commands to recover the database.
RECOVER DATABASE # This will recover complete database in one command
RECOVER TABLESPACE USER # This will recover one tablespace at a time.
RECOVER DATAFILE ‘/oracle/dbs/user_01.dbf’ # This will recover 1 datafile at a time.

During recovery it will ask for the archivefiles. Please provide the required archivefiles and redo log files and revover the database.

Once you get a message “Media recovery complete.”, means that recovery is done.
You can once again check V$RECOVER_FILE table to see if some datafile is still remaining to be recovered. Once everything is done use “ALTER DATABASE OPEN” to open the database. Do not use resetlogs to open the database.

Scenario 3: Complete Recovery


Losing Datafiles in ARCHIVELOG Mode. Performing open database recovery.


Media failure happens and damages a datafile. Datafile is unusable now. Some times the database wont shutdown down but makes the datafile offline, unless the datafile does not belongs to SYSTEM tablespace. You will come to know about the same when a user complain about getting the below error.

ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 11: ‘/oracle/oradata/trgt/cwmlite02.dbf’
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3


This clearly says that datafile ‘/oracle/oradata/trgt/cwmlite02.dbf’ is offline. The file now needs recovery to be consistant. If there is a media problem, you can check if that can be resolved quickly so that the datafile can be restored to same location. Else if the media failure takes time, you can restore the required datafile at some other locaiton.
Note that since the database is in archivelog mode, we dont need to restore the complete database back, only the files that needs recovery should be restored.
You can keep the database in open status and just make the respective tablespace offline, whose datafile needs to be recovered.


Once taken offline, you can restore the file from the last consistant backup (if the current datafile is damaged). If the file is restored at some alternate location, then you need to make changes about the location of file in control file as well, using below command.

ALTER DATABASE RENAME FILE ‘?/oradata/trgt/users01.dbf’ TO ‘/disk2/users01.dbf’;

Recover the tablespace


Bring the tablespace online.


Scenario 4: InComplete Recovery


Doing an incomplete recovery due to user error.


In case of user error, we some times need to perform incomplete recovery to rollback the changes. User must have updated some tables with wrong value, to rollback we take the database or that specific tablespace back in time just before the user had made the changes.


Do the incomplete recovery even thought the database is in archivelog mode. We can recover the database until some SCN or until some time. Also we can do a tablespace point in time recoery.