Jagjeet's Oracle Blog!

October 3, 2009

How to identify tracefile in 11g

Filed under: Oracle 11g — Tags: , — Jagjeet Singh @ 11:36 am

It’s quite often that DBA needs to enable tracing for a particular session to diagnose the performance or any other issue, which create trace file(s) in user_dump_directory. Usually this directory contains hundreds of files on a medium/large user base system, DBA needs to identify the OS process id to identify the trace file.

Naming Convention of user trace file is <<instance_name>>_ora_<<process id>>.trc under user_dump_directory. Oracle also provide option to use tracefile_identifire which can be utilized to make file name unique to identify easily.

Tracefile identifire will be appended in the name of file.In 11g, It has become very easy to identify the trace file directly from v$process view. Column named “Tracefile” has been added to this view.

SQL> select tracefile from v$process join v$session on (addr=paddr)
2 where sid = 76;


SID 76 is my session id.

After setting Tracefile Identifier.

SQL> alter session set tracefile_identifier='JS';

Session altered.

SQL> select tracefile from v$process join v$session on (addr=paddr)
2 where sys_context(‘userenv’,’sessionid’) = audsid
3 /


Note – I have removed SID and using sys_context… function to identify the current session. SID is useful to identify end-user’s sessions.

This column will always be populated with name of trace file even there has been no file generated.

SQL> ! ls -l /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc
ls: /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc: No such file or directory

After enabling trace.

SQL> alter session set sql_trace=true;

Session altered.

SQL> !ls -l /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc
-rw-r----- 1 oracle dba 1101 Oct 3 07:10 /u01/app/oracle/diag/rdbms/test/test1/trace/test1_ora_27817_JS.trc

before 11g, below query can be utilized to identify the trace file.

SQL> select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid
|| nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump
cross join V$PARAMETER instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = 'user_dump_dest'
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');


October 9, 2008

Access ASM through FTP and HTTP using XDB Configuration

Filed under: ASM — Tags: , , — Jagjeet Singh @ 11:25 am

10g release 2 introduced a new feature to access the ASM disk groups outside the database via FTP and through a web browser using HTTP.

The use of FTP and HTTP adds a flexibility to manage the ASM environment. Any FTP client can be used to move/copy the files stored on ASM.

Because ASM is not a regular file system which can be accessed using standard FTP and HTTP service. To make this enable we need to setup the Oracle XML DB access which is a one time setup. Oracle XML DB provides the file mapping functionality.

Disk groups can be available outside the database via a Virtual file system /sys/asm.

Please follow the below steps:

1) Follow the Note: 243554.1 “How to Deinstall and Reinstall XML Database(XDB)” to install the XDB.

We can check if XDB is installed from dba_registry.

SQL> select comp_id,comp_name,version,status from dba_registry
2 where comp_id = 'XDB';

---------- -------------------- ----------- -----------
XDB        Oracle XML Database  VALID

2) Configure the ports for FTP and HTTP

This can be done by two ways:

i) By executing the below script from sysdba and pass the FTP and HTTP port.

SQL> start ?/rdbms/admin/catxdbdbca 2100 8000

ii) By calling the package’s routines after connecting sysdba.

SQL>; exec dbms_xdb.setftpport(2100)
PL/SQL procedure successfully completed.

SQL>; exec dbms_xdb.sethttpport(8000)
PL/SQL procedure successfully completed.

Configuration for FTP and HTTP port can be confirmed using

SQL> ho lsnrctl status | grep TP

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=js)(PORT=2100))(Presentation=FTP) (Session=RAW))

Now we are ready to access ASM disk groups using FTP client or HTTP browser.
I have used the System database user to connect.

For access through web browser : http://hostaddress:8000

Below is the screen shot for FTP

FTP Client connected to ASM

FTP Client connected to ASM

« Newer Posts

Create a free website or blog at WordPress.com.