ORA-00600: internal error code, arguments: [4819], [], [], [], [], [], [], []
ORA-00308: cannot open archived log '/home/oracle/archive/arc_ARJU_001.arc'
ORA-27037: unable to obtain file status

This problem started to happen because recently database has been switched into NOARCHIVELOG mode from ARCHIVELOG mode.

Cause of the Problem
The problem arises because the LOG_ARCHIVE_DEST_n parameter is still set to point to an archive log destination and the default value for the LOG_ARCHIVE_DEST_STATE_n parameter is 'ENABLE'. Based on these two settings database still attempts to open the required archive log file during transaction recovery.

Solution of the Problem
To resolve this problem, mark the destination as deferred and null out the archive destination, That is
1) Login as sysdba.
SQL> connect / as sysdba

2) Set the archive_dest_state_1 to defer.
SQL> alter system set log_archive_dest_state_1 = defer scope=both;

TUNING THE REDOLOG BUFFER

1. What is the Redolog Buffer
 

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database . Redo entries are used for database recovery, if necessary.

Redo entries are copied by Oracle server processes from the user's memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active online redo log file (or group of files) on disk.

The initialization parameter LOG_BUFFER determines the size (in bytes) of the redo log buffer. In general, larger values reduce log file I/O, particularly if transactions are long or numerous. The default setting is four times the maximum data block size for the host operating system.

2. Redolog Latches

 
When a change to a data block needs to be done, it requires to create a redo record in the redolog buffer executing the following steps:

• Ensure that no other processes has generated a higher SCN
• Find for space available to write the redo record. If there are not space available a the LGWR must write to disk or issue a log switch
• Allocate the space needed in the redo log buffer
• Copy the redo record to the log buffer and link it to the appropriate structures for recovery purposes.

The database has three redo latches to handle this process:

• Redo Copy latch
The redo copy latch is acquired for the whole duration of the process described above. The init.ora LOG_SIMULTANEOUS_COPIES determines the number of redo copy latches. It is only released when a log switch is generated to release free space and re-acquired once the log switch ends.
• Redo  allocation latch
The redo allocation latch is acquired to allocate memory space in the log buffer. Before Oracle9.2, the redo allocation latch is unique and thus serializes the writing of entries to the log buffer cache of the SGA. In Oracle 9.2. Entreprise Edition, the number of redo allocation latches is determined by init.ora LOG_PARALLELISM.   The redo allocation latch allocates space in the log buffer cache for each transaction entry.  If transactions are small, or if there is only one CPU on the server, then the redo allocation latch also copies the transaction data into the log buffer cache. If a logswitch is needed to get free space this latch is released as well with the redo copy latch.
• Redo writing latch
This unique latch prevent multiple processes posting the LGWR  process requesting log switch simultaneously. A process that needs free space must acquire the latch before of deciding whether to post the LGWR to perform a write, execute a log switch or just wait.

3. Instance Parameters Related with the Redolog Latches
 

In Oracle7 and Oracle 8.0, there are two parameters that modify the behavior of the latch allocation in the redolog buffer: LOG_SIMULTANEOUS_COPIES (This parameter controls the number of redo copy latches when the system has more than one CPU),  and LOG_SMALL_ENTRY_MAX_SIZE. When LOG_SIMULTANEOUS_COPIES is set to a non-zero value, and the size of the transaction entry is smaller than the value of the LOG_SMALL_ENTRY_MAX_SIZE parameter then the copy of the transaction entry into the log buffer cache is performed by the redo allocation latch.  If the size of the transaction entry exceeds LOG_SMALL_ENTRY_MAX_SIZE, then the transaction entry is copied into the log buffer cache by the redo copy latch.

In Oracle8i and Oracle9.0, a redo copy latch is always required regardless of the redo size so the check is no longer performed. The init.ora LOG_SIMULTANEOUS_COPIES becomes obsolete and the number of redo copy latches defaults to twice the number of cpus. The parameter LOG_SMALL_ENTRY_MAX_SIZE is also obsolete. For further detail on the change of this parameters in Oracle 8i seeNote:94271.1

In Oracle9.2 and higher, multiple redo allocation latches become possible with init.ora LOG_PARALLELISM. The log buffer is split in multiple LOG_PARALLELISM areas that each have a size of init.ora LOG_BUFFER. The allocation job of each area is protected by a specific redo allocation latch. The number of redo copy latches is still determined by the number of cpus

4. Detecting and Resolving Redolog Buffer Performance Problem

 
Contention in the redolog buffer will impact the performance of the database since all DML and DDL must record a entry before of being executed. Contention can be seen as a latch contention or as excessive request for free space in the log buffer.

Note: In general log buffer contention is not frequent problem unless the latches already mentioned are consistently in the top wait events. Experience usually shows redo IO throughput is the main culprit of redo contention.

The database allow you to detect both types of contention as described below:

• Latch contention

The following query determines the miss ratio and the "immediate" miss ratio for redolog latches.

SELECT  substr(ln.name, 1, 20), gets, misses, immediate_gets, immediate_misses
FROM v$latch l, v$latchname ln
WHERE   ln.name in ('redo allocation', 'redo copy')
                and ln.latch# = l.latch#;

If the ratio of MISSES to GETS exceeds 1%, or the ratio of IMMEDIATE_MISSES to (IMMEDIATE_GETS + IMMEDIATE_MISSES) exceeds 1%, there is latch contention.

Note: Oracle recommends to tune first the redo allocation latch rather than the redo copy latch.

In Oracle7 and Oracle8.0:
If the contention is caused by redo allocation latch decrease the value of LOG_SMALL_ENTRY_MAX_SIZE. The recommended value is the average of redo size which can be calculated as (redo size/redo entries) from V$SYSSTAT.
If you find redo copy latch contention, you can increase the parameter LOG_SIMULTANEOUS_COPIES to have more latches available. The recommended value is twice the numbers of CPUs.

In Oracle8i and Oracle9.0:
If the contention is caused by redo allocation latch you can either use the NOLOGGING option to reduce the amount of redo log entries for certain operations (See Note:147474.1) or reduce the load on the latch increasing the LOG_BUFFER PARAMETER.


If you find redo copy latch contention, you can increase the hidden init.ora _LOG_SIMULTANEOUS_COPIES to have more latches available. The default is twice the numbers of CPUs.

In Oracle 9.2:
If the contention is caused by redo allocation latch you can try to increase their number via init.ora LOG_PARALLELISM
If you find redo copy latch contention, you can increase the hidden init.ora _LOG_SIMULTANEOUS_COPIES to have more latches available. The default is twice the numbers of CPUs.

In Oracle 10.2 and higher:
The statistic REDO BUFFER ALLOCATION RETRIES reflects the number of times a
user process waits for space in the redo log buffer. The value of redo buffer
allocation retries should be near zero over an interval. If this value increments
consistently, then processes have had to wait for space in the redo log buffer.
The wait can be caused by the log buffer being too small or by checkpointing.
Increase the size of the redo log buffer, if necessary, by changing the value of
the initialization parameter LOG_BUFFER. The value of this parameter is expressed
in bytes.

 

• Request for space contention
 
The statistic "redo log space requests" reflects the number of times a user process waits for space in the redo log file, not the buffer space ..  This statistic is available through the dynamic performance table V$SYSSTAT. By default, this table is only available to the user SYS and to users granted SELECT ANY TABLE system privilege, such as SYSTEM.  Monitor this statistic over a period of time while
your application is running with this query:

   SELECT name, value
   FROM v$sysstat
   WHERE name = 'redo log space requests';

The value of "redo log space requests" should be near 0. If this value increments consistently, processes have had to wait for space in the buffer. This may be caused the checkpointing or log switching.  Improve thus the checkpointing or archiving process.

sojha@sojha-w7dt ~
$ xclock
Error: Can't open display:

sojha@sojha-w7dt ~
$ startxwin
startxwin:  Resource temporarily unavailable (errno 11):  Another X server insta
nce is running on DISPLAY :0

sojha@sojha-w7dt ~
$ export DISPLAY=:0

sojha@sojha-w7dt ~
$ xclock

sojha@sojha-w7dt ~
$ xhost +
access control disabled, clients can connect from any host

sojha@sojha-w7dt ~
$ ssh oracle@va-txdb01
The authenticity of host 'va-txdb01 (10.0.60.32)' can't be established.
RSA key fingerprint is cf:bb:b3:91:ea:38:8c:5c:d4:a5:e3:0c:a6:35:8e:33.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'va-txdb01,10.0.60.32' (RSA) to the list of known hos
ts.

********************** W * A * R * N * I * N * G ****************va-txdb01*
* Individuals using this Arbinet-TheXchange system are subject to having  *
* all their activities on this system monitored, interecpted, recorded,   *
* recorded, read, copied, or captured in any manner and disclosed in any  *
* manner by system personnel.                                             *
*                                                                         *
* USE OF THIS SYSTEM BY ANY USER, AUTHORIZED OR UNAUTHORIZED,             *
* CONSTITUTES CONSENT TO SUCH MONITORING, INTERCEPTION, RECORDING,        *
* READING, COPYING OR CAPTURING and DISCLOSURE.                           *
*                                                                         *
* This Arbinet-TheXchange system and all related equipment are to be used *
* for the communication, transmission, processing, and storage of         *
* information relating to the official business of Arbinet-TheXchange.    *
* Unauthorized access or use of this Arbinet-TheXchange system is         *
* prohibited by U.S. laws including 18 U.S.C. 1030 (The Computer Fraud    *
* and Abuse Act of 1986).  If the monitoring, intercepting, recording,    *
* reading, copying or capturing reveals any possible voilation of         *
* criminal statutes, Arbinet-TheXchange will provide to law enforcement   *
* officials all relevant information including the data gathered.         *
* Violators will be prosecuted to the maximum extent allowed by the law.  *
***************************************************************************

oracle@va-txdb01's password:
Last unsuccessful login: Tue May 31 02:13:23 2011 on ssh from 10.0.55.104
Last login: Tue Jun  7 15:05:45 2011 on ssh from va-ibill01.thexchange.com

*******************************************************************************
*                                                                             *
*                                                                             *
*  Welcome to AIX Version 5.3!                                                *
*                                                                             *
*                                                                             *
*  Please see the README file in /usr/lpp/bos for information pertinent to    *
*  this release of the AIX Operating System.                                  *
*                                                                             *
*                                                                             *
*******************************************************************************

Database instances:
RAT1
TDB1

va-txdb01:RAT1:/u01/ARBPRDDB/home/oracle> dbca
DISPLAY not set.
Set DISPLAY environment variable, then re-run.

va-txdb01:RAT1:/u01/ARBPRDDB/home/oracle> export DISPLAY=10.0.20.122:0.0

va-txdb01:RAT1:/u01/ARBPRDDB/home/oracle> dbca

va-txdb01:RAT1:/u01/ARBPRDDB/home/oracle>

There are two forms of JDBC URLs:

Old form: jdbc:oracle:thin:<host>:<port>:<SID>

New form: jdbc:oracle:thin://<host>:<port>/<SERVICE_NAME>

The switch between old and new forms apparently happened at 9iR2,

If you see "foo" (or "FOO") it's a SID, if you see "foo.mydom.net" it's a SERVICE_NAME". Many people relate them and think that SERVICE_NAME = SID + DOMAIN. But not necessarily so.

The answer lies in the server configuration. If the listener.ora file is minimal, it will mediate between the database instance that registers and the clients that connect to port 1521 (or whatever). But that ONLY works with SERVICE_NAME, and that ONLY works with the new style URL and that's only supported by the JDBC driver (even though the filename is NOT different. Be aware that ojdbc14.jar may be old or new, you can only tell from the slightly larger size (1.5 instead of 1.3 or 1.4 MB).

The fix is to add a SID_LIST to the listener.ora file on the server:

SID_LIST_LISTENER = (SID_LIST =
(SID_DESC =(GLOBAL_DBNAME = foo.mydom.net)
(ORACLE_HOME = /usr/oracle/oraclehome)
(SID_NAME = mysidthatilike)
)
)

And note that you can put any SID_NAME of your choosing, it doesn't seem to matter which.

Some of this may be talked about on Metalink in

- Note:76531.1 Service Name Usage in Net8i
- Note: 183905.1 JDBC Thin Connection to Database Fails with ORA-12505/TNS-12505

A final lesser-known feature is that you can also use a full blown connection descriptor on the jdbc URI. For instance:

url="jdbc:oracle:thin:@(DESCRIPTION=
(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service_name)))"

or

url="jdbc:oracle:thin:@(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service_name)))"

Display the open cursor setting in the database
SQL>show parameter open

SQL> select user_name,count(*)  from v$open_cursor group by user_name;

SQL>select * from v$sysstat where name like '%opened cursors current%';

TOP SQL  opening the CURSOR

 select user_name,SQL_TEXT,count(*) as cnt  from  v$open_cursor  where user_name='DBAREF' group by user_name,SQL_TEXT order by  cnt desc;

Another SQL to capture current open cursors by users

SQL> select s.username, max(a.value)
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid (+)= a.sid
and b.name = 'opened cursors current'
group by s.username;

select * from v$sysstat where name like '%opened cursors%';

spo /tmp/drop_off.sql
set feed off
prompt spo /tmp/drop_off.log
prompt set echo on timing on time on
select 'drop tablespace '||tablespace_name||' including contents and datafiles;'
from dba_tablespaces
where status = 'OFFLINE'
/
set feed on
spo off
@@/tmp/drop_off.sql
set echo off timing off time off

Property	Description
Parameter type	Integer
Default value	900
Modifiable	ALTER SYSTEM
Range of values	0 to 231 - 1
Real Application Clusters	Oracle recommends that multiple instances have the same value.

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.

The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.

The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

Tools to analyse trace files

Up to and including Oracle 10g the tool that is generally used to analyse trace files is called tkprof. This tool formats the trace files that have been generated into a more readable format. Understanding the trace file format seems daunting on first inspection. A good source for details on the trace file format is a metalink note 39817.1. In 10g there is a new tool for formatting trace files called trcsess. This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module / action. This allows trace to be completed even if connection pooling and multi-threading is used. An individual client in these circumstances could share many different sessions.

Find out where the trace file will be written to

If the user you are using is not a DBA or to be more specific has not been granted access to the data dictionary view V$PARAMETER then you will need to use this technique to find out where your trace files are written to:

    SQL> set serveroutput on size 1000000 for wra
    SQL> declare
      2  paramname varchar2(256);
      3  integerval binary_integer;
      4  stringval varchar2(256);
      5  paramtype binary_integer;
      6  begin
      7   paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval);
      8   if paramtype=1 then
      9    dbms_output.put_line(stringval);
     10   else
     11    dbms_output.put_line(integerval);
     12   end if;
     13  end;
     14  /
    C:\oracle\admin\sans\udump
   
PL/SQL procedure successfully completed.
   
 SQL>                        
                       

If the user you are using has access to the base views then you can do the following instead.

    SQL> select name,value
      2  from v$parameter
      3  where name='user_dump_dest';
   
    NAME
    ----------------------------------------------------------------
    VALUE
    --------------------------------------------------------------------------------
    user_dump_dest
    C:\oracle\admin\sans\udump
   
   
    SQL>                        
                       

Making trace files available

There is an undocumented parameter _trace_files_public that if set to true changes the file permissions in the user_dump_dest directory when trace files are created to allow everyone to read them. This parameter can be checked with the following SQL. Beware that this is an undocumented parameter and should not be routinely set to true as some information in trace files can be used by hackers or malicious users. You can set this parameter by adding the following line to the init.ora file:

    # allow trace files to be created with public permissions
    _trace_files_public=true
    # disable this feature:
    #_trace_files_public=true
    # or =>
    _trace_files_public=false                       
                       

Here is the SQL to check the value of this parameter:

    SQL> select x.ksppinm name,y.ksppstvl value
      2  from sys.x$ksppi x,sys.x$ksppcv y
      3  where x.inst_id=userenv('Instance')
      4  and y.inst_id=userenv('Instance')
      5  and x.indx=y.indx
      6  and x.ksppinm='_trace_files_public';
   
    NAME
    ----------------------------------------------------------------
    VALUE
    --------------------------------------------------------------------------------
    _trace_files_public
    FALSE
   
   
SQL>                        
                       

Let's start with some examples of how to check trace for another session that is connected to the database.

Now find the SID and SERIAL# of the other session

We are using a simple example and the session we are looking for is for the user SCOTT and we are logged into this session with AS SYSDBA. We need to be logged in as SYS or AS SYSDBA so that we can access the packages DBMS_SUPPORT and DBMS_SYSTEM needed to set trace in another session or in our own session. Again as with the first example about access to v$parameter a user with access to the views V$SESSION and V$PROCESS is needed. First lets find the SID and SERIAL#

    SQL> connect system/manager@sans as sysdba
    Connected.
    SQL> col sid for 999999
    SQL> col serial# for 999999
    SQL> col username for a20
    SQL> col osuser for a20
    SQL> select s.sid,s.serial#,s.username,s.osuser
      2  from v$session s,v$process p
      3  where s.paddr=p.addr;
     
        SID SERIAL# USERNAME             OSUSER
    ------- ------- -------------------- --------------------
          1       1                      SYSTEM
          2       1                      SYSTEM
          3       1                      SYSTEM
          4       1                      SYSTEM
          5       1                      SYSTEM
          6       1                      SYSTEM
          7       1                      SYSTEM
          8       1                      SYSTEM
          9     253 SYSTEM               ZULIA\pete
         10      20 SCOTT                ZULIA\pete
   
    10 rows selected.
   
    SQL>                        
                       

great the SID and SERIAL# that we need are 10 and 20.

A word about trace levels

Before we use the DBMS_SYSTEM package to set trace in SCOTT's session we need to discuss what levels are. Trace in fact sets an event in the Oracle kernel (what is an 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). In our case we want to look at event number 10046 - This event tells the Oracle kernel to emit trace lines and timings. The levels available in Oracle through some of the interfaces used to set trace are:

    * 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

For a complete list of events that can be set look at the file $ORACLE_HOME/rdmbs/mesg/oraus.msg on Unix or Linux. This file is not shipped on Windows systems. Also setting any event other that trace (10046) should not be done without the guidance of Oracle support.

Set trace in another session using DBMS_SYSTEM

First lets set trace in SCOTT's session using the DBMS_SYSTEM package. Before we do let's turn on timed statistics so that the trace files get timing info and also set the dump file size so that there is plenty of room for the trace being generated.

    SQL> exec dbms_system.set_bool_param_in_session(10,20,'timed_statistics',true);
   
    PL/SQL procedure successfully completed.
   
    SQL> exec dbms_system.set_int_param_in_session(10,20,'max_dump_file_size',2147483647);
   
    PL/SQL procedure successfully completed.
                       
                       

OK, here we set trace in SCOTT's session

    SQL> -- now use standard dbms_support interface
    SQL> exec dbms_system.set_sql_trace_in_session(10,20,true);
   
    PL/SQL procedure successfully completed.
   
    SQL> -- execute some code
    SQL> exec dbms_system.set_sql_trace_in_session(10,20,false);
   
    PL/SQL procedure successfully completed.
   
    SQL>                        
                       

A second way to set trace in another session - This time setting trace level as well

Next we can again use the DBMS_SYSTEM interface but this time use the set event syntax. This allows us to set any event in the database. This is of course not sanctioned by Oracle support and can cause damage to your database if not done correctly. Use this interface with care and just set 10046 (trace) events. Here is how it is done:

    SQL> exec dbms_system.set_ev(10,20,10046,8,'');
   
    PL/SQL procedure successfully completed.
   
    SQL> -- execute some code
    SQL> exec dbms_system.set_ev(10,20,10046,0,'');
   
    PL/SQL procedure successfully completed.                           
                       

Installing the DBMS_SUPPORT package

Using the example above we set trace to level 8, you can of course set it to any level you wish from the list we discussed above. Next we will use the DBMS_SUPPORT package to set trace. 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. First we will install the package:

    SQL> -- now do the same with dbms_support
    SQL> -- the package has to be installed first - you should ask Oracle first though!
    SQL> @%ORACLE_HOME%\rdbms\admin\dbmssupp.sql
   
    Package created.
   
   
    Package body created.
   
    SQL>                        
                       

Use DBMS_SUPPORT to set trace in another users session

Next use the interface to again set trace for SCOTT's session that we found earlier. here it is:

    SQL> exec dbms_support.start_trace_in_session(10,20,waits=>true,binds=>false);
   
    PL/SQL procedure successfully completed.
   
    SQL> -- execute some code
    SQL> exec dbms_support.stop_trace_in_session(10,20);
   
    PL/SQL procedure successfully completed.
   
    SQL>                    
                       

use DBMS_SUPPORT to set trace in your own session

OK, that's how to set trace in SCOTT's session. How do we set trace in our own session. Well first we can use all of the approaches seen above and pass in the SID and SERIAL# for our own session. There are other methods for setting trace in your own session though. The first is again using the DBMS_SUPPORT package. Here it is:

    SQL> exec dbms_support.start_trace(waits=>true,binds=>false);
   
    PL/SQL procedure successfully completed.
   
    SQL> -- run some code
    SQL> exec dbms_support.stop_trace;
   
    PL/SQL procedure successfully completed.
   
    SQL>                        
                       

Use DBMS_SESSION to set trace in your own session

The next method for setting trace in our own session also is done using a built in package, this time DBMS_SESSION. here it is:

    SQL> -- in your own session using dbms_session
    SQL> exec dbms_session.set_sql_trace(true);
   
    PL/SQL procedure successfully completed.
   
    SQL> -- execut some code
    SQL> exec dbms_session.set_sql_trace(false);
   
    PL/SQL procedure successfully completed.
   
    SQL>                        
                       

using oradebug to set trace through SQL*Plus

oradebug is a debugging utility that is essentially undocumented and is intended for use by Oracle support analysts for various tasks one of which is that it can be used to set trace. oradebug is available from svrmgrl before Oracle 9i and from SQL*Plus after. The first step in using this tool is to find the OS PID or the Oracle PID of the process you want to analyse. You can do this as follows:

    SQL> connect system/manager@sans as sysdba
    Connected.
    SQL> col sid for 999999
    SQL> col serial# for 999999
    SQL> col spid for a8
    SQL> col username for a20
    SQL> col osuser for a20
      1  select s.sid,s.serial#,p.spid,p.pid,s.username,s.osuser
      2  from v$session s,v$process p
      3* where s.paddr=p.addr
    SQL> /
   
        SID SERIAL# SPID            PID USERNAME             OSUSER
    ------- ------- -------- ---------- -------------------- --------------------
          1       1 2528              2                      SYSTEM
          2       1 2536              3                      SYSTEM
          3       1 2540              4                      SYSTEM
          4       1 2544              5                      SYSTEM
          5       1 2552              6                      SYSTEM
          6       1 2604              7                      SYSTEM
          7       1 2612              8                      SYSTEM
          8       1 2652              9                      SYSTEM
         10     343 3740             12 SYS                  ZULIA\pete
         12      70 864              13 SCOTT                ZULIA\pete
   
    10 rows selected.
               
                       

Now that we have found the Operating System PID and Oracle PID (values 864 and 13 in this case) of SCOTT's session we can use this to set trace with the oradebug tool as follows:

SQL> -- set the OS PID
SQL> oradebug setospid 864               
Windows thread id: 864, image: ORACLE.EXE
SQL> -- or set the Oracle pid
SQL> oradebug setorapid 13
Windows thread id: 864, image: ORACLE.EXE
SQL> -- set the trace file size to unlimitd
SQL> oradebug unlimit
Statement processed.
SQL> -- now turn on trace for SCOTT
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> -- run some queries in another session and then turn trace off
SQL> oradebug event 10046 trace name context off
Statement processed.
                       

Some things to be aware of

You should be aware that some of these methods allow setting of extended trace and some do not. Those that allow extended trace are easy to spot. These methods include ways to set the trace level or include variables suitably named such as waits or binds which again enable extended trace facilities. Some trace methods have a default level such as set sql_trace=true which sets trace to level 8. The rest set trace to normal trace levels.

One other point to note is that we have looked first at ways to set trace in another session to the one you are logged into and also now at ways of setting trace in your own session, there is a third option, which is to set trace for the whole system (i.e for all users sessions), This is not recommended unless you know what you are doing and are monitoring trace as you can quickly fill the file system.

Setting trace at the instance level using the init.ora

Trace can be set in the database initialization file the init.ora file. If you use spfile then you can still use the init.ora file and then copy it to the spfile. Simply add the following line to the init.ora file:

    sql_trace=true
                       

You can also set timed_statistics and max_dump_file_size in the init.ora file in the same way. i.e

    timed_statistics=true
    max_dump_file_size=unlimited
                       

Trace can also be disabled at the instance level by simply commenting out the same parameter or by deleting it. A commented line is shown next:

    #sql_trace=true
                       

Or you can set the same parameter to false:

    sql_trace=false
                       

A second instance level method - setting events

Another method that can be used to set trace at the instance level is to add an event (or multiple events)to the initialization file, the init.ora as described above. Again if you use spfile's then you can copy the init.ora to spfile or use ALTER SYSTEM to set the value in the spfile. Here is an example of setting the trace event 10046 to level 12 in the initialization file:

    # set the event in the init.ora
    event = "10046 trace name context forever, level 12"
    # to turn off the event simply comment out the line as follows:
    # event = "10046 trace name context forever, level 12"   
                       

Using ALTER SESSION to set trace in your own session

The alter session command can be used to set trace for the current session as follows:

    SQL> alter session set sql_trace=true;
   
    Session altered.
   
    SQL> -- execute some code
    SQL> alter session set sql_trace=false;
   
    Session altered.
   
    SQL>
                       

This method can also be used to set timing and dump file size for the current session as follows:

    SQL> alter session set timed_statistics=true;
   
    Session altered.
   
    SQL> alter session set max_dump_file_size=unlimited;
   
    Session altered.                       
   
    SQL>
                       

Using ALTER SESSION to set extended trace using events

One last method I want to demonstrate is the alter session syntax to set events. Again stick to 10046 (trace) and do not attempt to set any of the other events that are available without Oracles say so in a supported system. Here is the example of setting trace to level 12, including binds and waits:

    SQL> alter session set events '10046 trace name context forever, level 12';
   
    Session altered.
   
    SQL> -- execute some code
    SQL> alter session set events '10046 trace name context off';
   
    Session altered.
   
    SQL>                        
                       

A sample logon trigger to set trace

Quite often you would like trace to be set for a session as soon as the user logs on. Also you may want to be able to set trace for a specific set of users when they log in. This can easily be done with a database logon trigger. Here is a sample trigger.

    Connected to:
    Personal Oracle9i Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production
   
    SQL> create or replace trigger set_trace after logon on database
      2  begin
      3   if user not in ('SYS','SYSTEM') then
      4    execute immediate 'alter session set timed_statistics=true';
      5    execute immediate 'alter session set max_dump_file_size=unlimited';
      6    execute immediate 'alter session set sql_trace=true';
      7   end if;
      8  exception
      9   when others then
     10    null;
     11  end;
     12  /
   
    Trigger created.
   
    SQL> sho errors
    No errors.
    SQL>                        
                       

OK, that was easy. You can also use the alter session set events '10046 trace name context forever,level 12' syntax if you prefer You can also enable other checks within the trigger if need by using any valid PL/SQL logic that you prefer. One tip is that if you have any troubles with your system trigger and it causes logins to fail is to always include, as I have, an exception handler that calls null; for any error condition. If all else fails you can disable system triggers by setting the parameter _system_trig_enabled=false in the initialisation file. This undocumented / hidden parameter stops the processing of system triggers such as logon triggers.

Using ALTER SYSTEM to set trace at the instance level

Finally you can also use the alter system syntax to set trace at the system level. Here is a simple example:

    SQL> alter system set sql_trace=true scope=spfile;
   
    System altered.
   
    SQL>
    SQL> -- to turn it off again do:
    SQL> alter system set sql_trace=false scope=spfile
   
    System altered.
   
    SQL>
                       

Checking the privileges of the packages used to set trace

Some of the packages used in this example have to be run as SYS or you need to be logged in AS SYSDBA or specific privileges need to be granted against those packages for the user that will run them. The default privileges for DBMS_SYSTEM, DBMS_SUPPORT and for DBMS_SESSION are showed next in output from who_can_access.sql (A script that shows privileges hierarchically for an object who's name is passed in). Here they are:

    -- check who has access to dbms_system
    who_can_access: Release 1.0.0.0.0 - Production on Fri Feb 27 12:53:24 2004
    Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
   
    get user input
   
    NAME OF OBJECT TO CHECK [USER_OBJECTS]: dbms_system
    OWNER OF THE OBJECT TO CHECK    [USER]: sys
    OUTPUT METHOD Screen/File          [S]:
    FILE NAME FOR OUTPUT        [priv.lst]:
    OUTPUT DIRECTORY                [/tmp]:
   
    Checking object => SYS.DBMS_SYSTEM
    ====================================================================
   
   
    Object type is => PACKAGE (TAB)
            Privilege => EXECUTE is granted to =>
                    Role => OEM_MONITOR which is granted to =>
                                    User => SYS
   
    PL/SQL procedure successfully completed.
   
   
    For updates please visit http://www.petefinnigan.com/tools.htm
   
    SQL>
   
    -- check who has access to dbms_support
    who_can_access: Release 1.0.0.0.0 - Production on Fri Feb 27 12:54:29 2004
    Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
   
    get user input
   
    NAME OF OBJECT TO CHECK [USER_OBJECTS]: dbms_support
    OWNER OF THE OBJECT TO CHECK    [USER]: sys
    OUTPUT METHOD Screen/File          [S]:
    FILE NAME FOR OUTPUT        [priv.lst]:
    OUTPUT DIRECTORY                [/tmp]:
   
    Checking object => SYS.DBMS_SUPPORT
    ====================================================================
   
   
   
    PL/SQL procedure successfully completed.
   
   
    For updates please visit http://www.petefinnigan.com/tools.htm
   
    SQL>
   
    -- check who has access to dbms_session
    who_can_access: Release 1.0.0.0.0 - Production on Fri Feb 27 12:55:31 2004
    Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
   
    get user input
   
    NAME OF OBJECT TO CHECK [USER_OBJECTS]: dbms_session
    OWNER OF THE OBJECT TO CHECK    [USER]: sys
    OUTPUT METHOD Screen/File          [S]:
    FILE NAME FOR OUTPUT        [priv.lst]:
    OUTPUT DIRECTORY                [/tmp]:
   
    Checking object => SYS.DBMS_SESSION
    ====================================================================
   
   
    Object type is => PACKAGE (TAB)
            Privilege => EXECUTE is granted to =>
                    Role => PUBLIC
   
    PL/SQL procedure successfully completed.
   
   
    For updates please visit http://www.petefinnigan.com/tools.htm
   
    SQL>                        
                       

That's it, there are many ways to set trace in your session, in others sessions and at system level. Also many ways to enable extended trace. Beware of the privileges needed to run some of them and beware of setting events explicitly.

New tracing methods in Oracle 10g - DBMS_MONITOR

Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC / Java or something like Forte to be traced where it would normally be very difficult to identify a database session belonging to a client as the sessions / clients pairings change with time.

The new functionality works in three levels. You can use the old SID / SERIAL# pairings to identify a session but you can also use a client identifier or a service name / module / action to identify a client session to be traced. The package also offers a set of procedures to allow statistics to be gathered for the same groups. These statistics can then be selected from dynamic views.

let's now take a look at some of the features of this package.

Setting trace with DBMS_MONITOR using SID / SERIAL#

Trace can be set for the current user session, for the current session or for another users session. First lets look at tracing another users session. First we need to get the SID and SERIAL# - we will use SCOTT connected through SQL*Plus as our sample session:

    SQL> select s.sid,s.serial#,s.username
      2  from v$session s, v$process p
      3  where s.paddr=p.addr
    SQL> /

    ...
           SID    SERIAL# USERNAME                                                 
    ---------- ---------- ------------------------------                           
           248        153 SCOTT                                                    
           258         61 DBSNMP                                                   
           251        418 SYSMAN                                                   
           255        961 SYS                                                      
           249        215                                                          

    27 rows selected.

    SQL>
                       

OK as with previous methods we can use a SID / SERIAL# pair of 248 and 153. lets set trace for this user session:

    SQL> exec dbms_monitor.session_trace_enable(248,153,TRUE,FALSE);

    PL/SQL procedure successfully completed.

    SQL> -- execute some sql
    SQL> -- in the other session
    SQL> -- turn trace off
    SQL> exec dbms_monitor.session_trace_disable(248,153);

    PL/SQL procedure successfully completed.

    SQL>
                       

Setting trace at the session level using DBMS_MONITOR

The same procedures can be used to set trace for the session by omitting the serial#. This is demonstrated next:

    SQL> exec dbms_monitor.session_trace_enable(248);

    PL/SQL procedure successfully completed.

    SQL> -- execute some sql in the other session
    SQL> -- turn off trace
    SQL> exec dbms_monitor.session_trace_disable(248);

    PL/SQL procedure successfully completed.

    SQL> -- or you can turn it on with
    SQL> exec dbms_monitor.session_trace_enable(248,null);

    PL/SQL procedure successfully completed.

    SQL> -- turn off again with:
    SQL> exec dbms_monitor.session_trace_disable(248,null);

    PL/SQL procedure successfully completed.

    SQL>
                       

Setting trace for the current session using DBMS_MONITOR

Setting trace for the current user session is done by leaving out the SID and SERIAL# altogether by setting them to NULL. Here is an example:

    SQL> -- trace the current session
    SQL> exec dbms_monitor.session_trace_enable(null,null);

    PL/SQL procedure successfully completed.

    SQL> -- execute some code
    SQL> -- turn it off again
    SQL> exec dbms_monitor.session_trace_disable(null,null);

    PL/SQL procedure successfully completed.

    SQL> -- to get waits and binds do
    SQL> exec dbms_monitor.session_trace_enable(null,null,true,true);

    PL/SQL procedure successfully completed.

    SQL> -- execute some code
    SQL> -- then turn off tracec
    SQL> exec dbms_monitor.session_trace_disable(null,null);

    PL/SQL procedure successfully completed.

    SQL> -- or turn it on like this
    SQL> exec dbms_monitor.session_trace_enable();

    PL/SQL procedure successfully completed.

    SQL> -- execute some SQL and then turn off trace
    SQL> exec dbms_monitor.session_trace_disable();

    PL/SQL procedure successfully completed.

    SQL>
                       

That completes some of the ways to use DBMS_MONITOR to set trace using SID, SERIAL#, or at the session level or for the current session.

Set trace using a client identifier

Tracing using the client identifier allows trace to be set across multiple sessions as many Oracle shadow processes can work on behalf of one client. Also trace is persistent across all instances and restarts. First we need to see how the client identifier is set. This can be done using the DBMS_SESSION package as follows:

    SQL> exec dbms_session.set_identifier('pete id');

    PL/SQL procedure successfully completed.

    SQL>
                       

We can check now for a specific identifier in the V$SESSION view with the client_identifier column.

    SQL> select s.username,s.client_identifier
      2  from v$session s,v$process p
      3  where s.paddr=p.addr
      4  and client_identifier is not null;

    USERNAME                                                                       
    ------------------------------                                                 
    CLIENT_IDENTIFIER                                                              
    ----------------------------------------------------------------               
    SCOTT                                                                          
    pete id                                                                        
                                           

    SQL>
                       

OK, now we can use this information to set trace for this client identifier as follows:

    SQL> exec dbms_monitor.client_id_trace_enable('pete id',true,false);

    PL/SQL procedure successfully completed.

    SQL> -- wait for the client session to do something
    SQL> -- turn off trace as follows:
    SQL> exec dbms_monitor.client_id_trace_disable('pete id');

    PL/SQL procedure successfully completed.

    SQL>
                       

That was quite easy!. next let's look at setting trace at the service, module action levels.

Setting trace for service/module/action with DBMS_MONITOR

This method of setting trace acts hierarchically. The first level is that trace is set globally for the whole database (all instances) You can override this by setting an instance name in the call to turn on trace. For this example as I am on a single instance database I will leave this parameter at its default. There are three levels to the hierarchy. If we set ACTION to NULL then all actions for the module and service are traced. The next level, if we set MODULE to NULL then all actions for all modules for the specified service name are traced. The trace will be collected into multiple trace files and the new tool trcsess must be used to collate all the trace files into one usable file.

The service name can be set using the package DBMS_SERVICE and the procedure CREATE_SERVICE. Here is an example:

    SQL> exec dbms_service.create_service('Test Service','test network');

    PL/SQL procedure successfully completed.

    SQL> -- it can be deleted with
    SQL> exec dbms_service.delete_service('Test Service');

    PL/SQL procedure successfully completed.

    SQL>
                       

The service name can quite often be set already by the tool. It could be used to group together a set of programs / modules that perform some business task. Next let's see how the module and actions can be set.

    SQL> -- set action
    SQL> exec dbms_application_info.set_action('PAYMENT');

    PL/SQL procedure successfully completed.

    SQL> -- set the module
    SQL> exec dbms_application_info.set_module('ACCOUNTS','PAYMENT');

    PL/SQL procedure successfully completed.

    SQL>
                       

To view the relevant service names, modules and actions for sessions in the database you can use the v$SESSION view as follows:

    SQL> col service_name for a15 wrapped
    SQL> col username for a15 wrapped
    SQL> col module for a15 wrapped
    SQL> col action for a15 wrapped
    SQL> select s.username,s.service_name,s.module,s.action
      2  from v$session s,v$process p
      3  where s.paddr=p.addr;
    ...
    USERNAME        SERVICE_NAME    MODULE          ACTION                         
    --------------- --------------- --------------- ---------------                
    SYSMAN          SANS                                                           
    SYSMAN          SANS            OEM.SystemPool                                 
    DBSNMP          SYS$USERS       emagent@emil (T                                
                    NS V1-V3)                                      
                                           
    DBSNMP          SYS$USERS       emagent@emil (T                                
                    NS V1-V3)                                      
                                           
            SYS$USERS                                                      
        SYS             SANS            ACCOUNTS        PAYMENT
    SCOTT           SANS            SQL*Plus                                       
    ...
    29 rows selected.

    SQL>
                       

As we deleted our sample service name set up with DBMS_SERVICE.CREATE_SERVICE we will just use the default value SANS inserted by Oracle in our test case. Let's test some of the methods of setting trace with this functionality.

    SQL> -- set trace for all modules and actions for SANS service name
    SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS',DBMS_MONITOR.ALL_MODULES,DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);

    PL/SQL procedure successfully completed.

    SQL> -- turn it off
    SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS');

    PL/SQL procedure successfully completed.

    SQL> -- now trace all actions for service SANS and module accounts
    SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS',DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);

    PL/SQL procedure successfully completed.

    SQL> -- now turn it off
    SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS');

    PL/SQL procedure successfully completed.

    SQL> -- finally test service SANS, module ACCOUNTS and action PAYMENT
    SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS','PAYMENT',TRUE,FALSE,NULL);

    PL/SQL procedure successfully completed.

    SQL> -- turn it off
    SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS','PAYMENT');

    PL/SQL procedure successfully completed.

    SQL> -- you can turn on or off binds and waits as well or use the waits=>true
    SQL> -- syntax instead.
    SQL>
                       

OK, that wraps up the new procedures in 10g that can be used to turn on trace in different ways to capture true end to end trace for multi-tier applications. You should also be aware that DBMS_MONITOR also provides procedures to enable statistic gathering at the same levels of client identifier and service name/module/action. These statistics are stored and can then be accessed by selecting from V$SERV_MOD_ACT_STATS and V$CLIENT_STATS views. I will not detail those procedures here as this short paper is concentrating on trace only.

One last idea - use AUTOTRACE in SQL*Plus

OK, one final way to set and get trace, is to use the SQL*Plus AUTOTRACE facilities. There are a few settings that you can use. These are as follows:

    * set autotrace off - The default - no output
    * set autotrace on explain - This shows only the optimizer path
    * set autotrace on statistics - This only shows SQL statistics
    * set autotrace on - Includes both of the above
    * set autotrace traceonly - As above but the query output is not displayed

One more final item - CBO trace 10053

One other event that you might like to try and experiment with is the 10053 event. This event traces the Cost Based Optimizer (CBO) and shows all of the plans and costs assigned to them that it tried in its search for the best cost and also is shows how it came to its decision. The 10053 event has two levels 1 and 2. More detail is emitted if level 1 is used rather than level 2. The output is again sent to a trace file in the directory specified by user_dump_dest. The trace is only generated if the SQL is hard parsed and also obviously uses the CBO. To get a trace file you can use any of the methods above that allow the event number to be specified. An example is:

SQL> alter session set events '10053 trace name context forever, level 1

Session altered.

SQL> -- execute some SQL to create a CBO trace.
SQL> -- turn CBO trace off
SQL> alter session set events '10053 trace name context off';

Session altered.

SQL>

Oracle Net Services provides methods for understanding and resolving network problems through the use of log and trace files. These files keep track of the interaction between network components as errors occur. Evaluating this information will help you to diagnose and troubleshoot even the most complex network problems.

see detail instruction how to troubleshoot Oracle NET issues.

http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/troublestng.htm#i435776

In some situations it is necessary to quickly add or remove histograms from a particular column (also known as column statistics.)

This article shows how to accomplish this with one command.

The commands given in this article will not affect column statistics currently gathered for other columns, they will only touch the specified column.

Column statistics can be:
- so called minimal consisting of just 1 bucket (2 endpoints) with min/max value information required by the Optimizer
- histograms with between 2 and 254 buckets (3 and 255 endpoints respectively)
- absent with no column information recorded in the data dictionary (not normally recommended)

Solution

Assuming you are connected as the owner of the table.

To add column statistics use one of the following varieties:

• using up to the default number of buckets 75:
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name>');

• using the appropriate number of buckets for the data distribution (will be minimal when not skewed) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size skewonly');
• using maximum number of buckets (up to 254) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size 254');
• using up to specified number of buckets N (N is between 2 and 254) :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt=>'for columns <column_name> size N');

• gathering statistics for multiple columns with different bucket sizes :
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');
• gathering statistics for multiple columns with different bucket sizes plus minimal information for the remaining columns:
  SQL> exec dbms_stats.gather_table_stats(null, '<table_name>', cascade=>false, method_opt => 'for all columns size 1 for columns size N <column_name1>,<column_name2> for columns size M <column_name3>,<column_name4>');
  
  

To delete histograms from a column leaving behind base column statistics i.e. minimal information required for the Optimizer (this is for pre-Oracle11g versions:)
To completely erase all types of column statistics/histograms for a column including minimal statistics:This is generally not recommended as the Optimizer needs at least the minimal (size 1) information.

In Oracle11g there is new syntax to delete the histogram for a column while still leaving behind the base column statistics:
Additionally there is functionality to delete histograms for a partition:


and to delete column histograms for the table and all its partitions: