Tag Archives: trace sid

Trace

How to Enable Trace

1. Trace levels
2. Trace own session
3. Trace other’s session
4. Service Level Trace
5. Module Level Trace
6. Database Level Trace
7. How to identify Trace file
8. trcsess Utility
9. TKPROF


Trace levels.

Level 0 - No trace. Just like switching sql_trace off.
Level 2 - The equivalent of regular sql_trace.
Level 4 - The same as level 2, but with the addition of bind variable values.
Level 8 - The same as level 2, but with the addition of wait events.
Level 12 - The same as level 2, but with the addition of both bind variable values and wait events.


Trace own session

set timing on timi on
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’MY_TRACE’;

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Execute the queries or operations to be traced here --
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

OR

SQL> EXEC DBMS_MONITOR.session_trace_enable; (OR)
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_MONITOR.session_trace_disable;

OR

SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE();

OR

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

OR

SQL> ALTER SESSION SET sql_trace=TRUE;
-- Execute the queries or operations to be traced here --
SQL> ALTER SESSION SET sql_trace=FALSE;

OR

SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SUPPORT.stop_trace;

Note: Need to install before use 
SQL> connect / AS SYSDBA
SQL> @?\rdbms\admin\dbmssupp.sql
SQL> GRANT execute ON dbms_support TO schema_owner;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; 


Trace other’s session

set timing on timi on
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’MY_TRACE’;

-- Enable 10046 Trace on other's session

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

OR

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234); (OR)
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

OR

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

OR

SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);

OR

Tracing a process after it has started. Please find out PID, SPID using below query 

select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/

SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid) 

Using SPID (os pid)

connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
-- Execute the queries or operations to be traced here --
oradebug tracefile_name; -- display the current trace file.
oradebug event 10046 trace name context off

OR

using PID (ora pid)

connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
-- Execute the queries or operations to be traced here --
oradebug tracefile_name;  -- display the current trace file.
oradebug event 10046 trace name context off


Service Level Trace

SELECT sid, serial#, client_identifier, service_name, action, module FROM V$SESSION where username=’&USERNAME’;
select * from DBA_ENABLED_TRACES;
select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES;
select * from sys.wri$_tracing_enabled;

-- Level 12 Trace on service_name (waits=>true,binds=>true)

SQL> EXEC dbms_monitor.serv_mod_act_trace_enable (service_name=>'orcl',waits=>true,binds=>true);
	-- Execute the queries or operations to be traced here --
SQL> EXEC dbms_monitor.serv_mod_act_trace_disable (service_name=>'orcl');


Module Level Trace

-- Level 12 Trace on Module (waits=>true,binds=>true)

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'TOAD 9.5.0.31', waits => TRUE, binds => TRUE);
	-- Execute the queries or operations to be traced here --
SQL> exec dbms_monitor.serv_mod_act_trace_disable(service_name=>'orcl', module_name=>'TOAD 9.5.0.31');


Database Level Trace

For all sessions in the database

NOTE: Please be cautious when setting system wide, as this will impact performance due to every session being traced.
Warning: Setting the 10046 event for the entire database is not recommended. This can cause severe performance issues.

Existing sessions will not be traced. 
 
EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);
EXEC dbms_monitor.DATABASE_TRACE_DISABLE();

alter system set events '10046 trace name context forever,level 12';
alter system set events '10046 trace name context off';


How to identify Trace file

Trace file named with spid under user_dump_dest location
-- To find SPID
select p.PID,p.SPID,s.SID 
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = '&SESSION_ID'
/

eg: instancename_ora_spid.trc i.e orcl_ora_4393.trc

show parameter user_dump_dest
cd user_dump_dest location
ls -ltr *ora_spid*.trc
eg: ls -ltr *ora_4393*.trc


trcsess Utility

When using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure, tracing
information is present in multiple trace files and you must use the trcsess tool to collect it into a single file.
The trcsess utility is useful for consolidating the tracing of a particular session or service for performance or debugging purposes.

trcsess [output=output_file_name]
[session=session_id]
[clientid=client_identifier]
[service=service_name]
[action=action_name]
[module=module_name]
[]

eg: 
cd user_dump_dest
trcsess output=one_big.trc service=orcl *.trc
ls -ltr one_big.trc


TKPROF

The tkprof utility parses SQL trace files to produce more readable output.
tkprof does not report COMMITs and ROLLBACKs that are recorded in the trace file.
Note: Set the TIMED_STATISTICS parameter to TRUE when tracing sessions because no time-based comparisons can be made without this. TRUE is the default value with Oracle Database 11g.

eg: 
tkprof one_big.trc output=one_big.txt sys=no

[oracle@rac1 trace]$ tkprof one_big.trc output=one_big.txt sys=no

TKPROF: Release 11.2.0.3.0 - Development on Sun Sep 11 16:50:56 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle@rac1 trace]$ ls -ltr one_big.txt

Reference:

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm#CFAHBEAB
http://www.juliandyke.com/Diagnostics/Packages/DBMS_MONITOR/SERV_MOD_ACT_TRACE_ENABLE.php
https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

OS

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. We do not guarantee it will work for you. Ensure that you run it in your test environment before using.

OS commands for DBA

1. RAM SIZE 
2. Find
3. SAR
4. vmstat
5. iostat
6. swap
7. uptime
8. Top CPU users
9. Top memory users
10. Check disk space
11. TOP Command Examples
12. Find number of CPU
13. Trace SID
14. System logfile
15. Send mail
16. Tar Command Examples
17. ZIP Command Examples
18. GZIP Command Examples


1. RAM SIZE

/usr/sbin/prtconf | grep Mem  	-- For Solaris
dmesg 					-- For HP-UX
lsdev -C | grep mem  			-- For AIX
lsattr -El mem0 ( output from previous command) -- For AIX
free 					-- For Linux
uerf -r 300 | grep -I mem		-- DEC-UNIX


2. Find

find / -print | grep -i z.sql  -- In root mount point searching for file "z.sql"
find /usr/lib -name "*raj*" /*finding can’t remember the exact name of*/
find . -size +20000 /*find files bigger than 20000 blocks, about 10MB )*/
find . -size +20000 -mtime -1 -ls
find . -mtime -1 /*Find files or directories modified in last day*/
find /tmp -atime +45 /*find files not accessed (read) in 45 days*/
find . -name "*.c" |xargs grep -l libsocket /*look for a particular word in all your .c files*/


3. SAR (System Activity Report)

sar -u 1 10   /*if I run sar 1 10 it will report to the screen, 10 instances every one second*/


4. vmstat

vmstat 1 5 /*vmstat [interval] [count]*/


5. iostat

iostat -x 1 5  /*iostat [interval] [count]*/


6. swap space

/usr/sbin/swap -s /*solaris*/


7. uptime

uptime /*check system load average: an uptime more than double the number of CPUs is getting a bit busy*/


8. Top CPU users

/usr/ucb/ps uax |head /*solaris*/


9. Top memory users

/usr/ucb/ps vax |head /*solaris*/


10. Check disk space

df -h or df -k or df -g /*shows all mounted filesystems*/
df -h /u01 /*show space for specified mountpoing /u01 */
du -sh /home/oracle /*To find size of a directory */
du -sk
[root@rac1 ~]# du -k /u01 | sort -r -n -k 1 | head -20 | awk ' { print $2 "....."$1/1024 "MB" } '
/u01.....48585.8MB
/u01/app.....40167.6MB
/u01/app/oracle.....21521.9MB
/u01/app/oracle/product.....20262.3MB
/u01/app/grid/product/12.2.....17900.2MB
/u01/app/grid/product.....17900.2MB
/u01/app/grid.....17900.2MB
/u01/app/oracle/product/12.2.0/dbhome_1.....13300.9MB
/u01/app/oracle/product/12.2.0.....13300.9MB
/u01/app/oracle/product/12.2/db_1.....6961.39MB
/u01/app/oracle/product/12.2.....6961.39MB
/u01/stage/30920127.....5662.84MB
/u01/stage.....5662.84MB
/u01/app/oracle/product/12.2.0/dbhome_1/dbs.....3836.5MB
/u01/app/oracle/product/12.2/db_1/database.....3667.68MB
/u01/app/oracle/product/12.2/db_1/database/stage.....3665.52MB
/u01/app/grid/product/12.2/.patch_storage.....3249.18MB
/u01/stage/30920127/30869447.....3093.78MB
/u01/stage/30920127/30869447/files.....3093.64MB
/u01/app/oracle/product/12.2/db_1/database/stage/Components.....3053.15MB
[root@rac1 ~]#


11. TOP Command Examples

# top /*it will show information like tasks, memory, cpu and swap. Press ‘q‘ to quit window*/
# top -u oracle /*will display specific User process details*/
# top ---> then press z /*will display running process in color which may help you to identified running process easily*/
# top ---> then press c /*will display absolute path of running process*/
# top ---> then press d /*Default screen refresh interval is 3.0 seconds, can change pressing ‘d‘ option*/
# top ---> then press k /*You can kill PID in running top command without exiting from top window*/
# top ---> then press Shift+P /*to sort processes as per CPU utilization*/
# top ---> then press r /*To change the priority of the process (Renice a Process)*/
# top ---> then press Shift+W /*To save top command results*/
# top ---> then press h /*Getting Top Command Help*/
# top ---> then press Shift+O then press "a" /*press ‘a‘ letter to sort process with PID (Process ID)/*


12. Find number of CPU

psrinfo | wc -l /*Solaris --suppose virtual CPU*/
cat /proc/cpuinfo|grep processor|wc –l /*Linux*/
psrinfo -v|grep "Status of processor"|wc –l /*Solaris*/
lsdev -C|grep Process|wc –l /*AIX*/
ioscan -C processor | grep processor | wc -l /*HP-UX*/


13. Trace SID

truss -p  /*Solaris and AIX*/
strace -p  /*Linux*/
trace -p  /*Tru 64*/


14. System logfile

Linux: /var/log/messages
Solaris, HP Tru64: /var/adm/messages
HP-UX: /var/adm/syslog/syslog.log
AIX: /bin/errpt


15. Send mail with attachments

uuencode 1.txt 2.txt | mailx -s "subject of mail" br8dba@gmail.com
OR
echo Body of the mail |mailx -s Attachment -a file.txt br8dba@gmail.com


16. Tar Command Examples

TAR

tar -cvf /u01/zzz.tar /home/zzz/ /*will create tar file for /home/zzz directory, it will keep original files as it is*/
c – Creates a new .tar archive file.
v – Verbosely show the .tar file progress.
f – File name type of the archive file.

UNTAR

tar -xvf /u01/zzz.tar -C /home/oracle /*Untar files in specified Directory*/

TAR Compress
Create tar.gz Archive File use option z
tar cvzf /u01/zzz.tar.gz /home/zzz /*will create tar file for /home/zzz directory*/

TAR UnCompress 
tar -xvf /u01/zzz.tar.gz -C /home/oracle /*Untar files in specified Directory*/


TAR Compress tar.bz2

Create tar.bz2 Archive File
The bz2 feature compress and create archive file less than the size of the gzip.
The bz2 compression takes more time to compress and decompress files as compared to gzip which takes less time. 
To create highly compressed tar file we use option as j.

tar cvfj /u01/zzz.tar.bz2 /home/zzz/ -- /*will create tar file for /home/zzz directory*/

TAR UnCompress tar.bz2
tar -xvf /u01/zzz.tar.bz2 -C /home/oracle /*Untar files in specified Directory*/

List Content of tar Archive File

tar -tvf /u01/zzz.tar
tar -tvf /u01/zzz.tar.gz
tar -tvf /u01/zzz.tar.bz2

Untar Single file from tar File

tar -xvf /u01/zzz.tar init.ora
tar -zxvf /u01/zzz.tar.gz init.ora
tar -jxvf /u01/zzz.tar.gz.bz2 init.ora
or
tar --extract --file=/u01/zzz.tar init.ora
tar --extract --file=/u01/zzz.tar.gz init.ora
tar --extract --file=/u01/zzz.tar.gz.bz2 init.ora

Untar Multiple files from tar, tar.gz and tar.bz2 File

tar -xvf /u01/zzz.tar "file 1" "file 2" 
tar -zxvf /u01/zzz.tar.gz "file 1" "file 2" 
tar -jxvf /u01/zzz.tar.bz2 "file 1" "file 2"


Extract Group of Files using Wildcard

tar -xvf /u01/zzz.tar --wildcards '*.php'
tar -zxvf /u01/zzz.tar.gz --wildcards '*.php'
tar -jxvf /u01/zzz.tar.bz2 --wildcards '*.php'


17. ZIP Command Examples

Zipping individual files
zip abc.zip file1 file2 file3

Zip a file or folder
zip archivename.zip file1 file2 folder1

Zip a folder (including all subdirectories)
zip -r folder1*

Zip a folder to a different location
zip -r /tmp/abc.zip /home/oracle/folder1

List all the files stored in a zip file
unzip -l abc.zip

Unzip
unzip abc.zip

To unzip to a specific directory use -d option
unzip abc.zip -d /tmp

Unzip specific file from an archive
unzip abc.zip test.sh


18. GZIP Command Examples

zipping a file with gzip command
gzip file1 file2 file3
oracle@rac1:~/test/test$ ls
dump.doc.gz  file1.gz  test.sh.gz <-- it will zip individually
oracle@rac1:~/test/test$
Note: Above gzip command will create files dump.doc.gz, file1.gz and test.sh.gz respectively by replacing original files.
To avoid deleting of original files, use -k(keep) option with zgip command

gzip  -k dump.doc file1 test.sh 
oracle@rac1:~/test/test$ ls
dump.doc  dump.doc.gz  file1  file1.gz  test.sh  test.sh.gz
oracle@rac1:~/test/test$

How to zip group of files to a gzip single compressed file
gzip don't know how to add files to a single comprss file and it just compress each file individually by default.

oracle@rac1:~/test/test$ cat dump.doc file1 test.sh | gzip > all.gz
oracle@rac1:~/test/test$ ls
all.gz  dump.doc  file1  test.sh
oracle@rac1:~/test/test$

Unzip the gzip file
gunzip all.gz

oracle@rac1:~/test/test$ ls
all.gz  dump.doc  file1  test.sh
oracle@rac1:~/test/test$ gunzip all.gz 
oracle@rac1:~/test/test$ ls
all  dump.doc  file1  test.sh <-- gzip file automatically deleted once unzipped