start trace in session

/**********************************************************************
* Description:
*      SQL*Plus script to start the SQL Trace for another session.  This
*      script displays the trace file name and location.  This script will
*      display the user sessions and prompt for information it needs.
*
*********************************************************************/
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF

column in_db_namet new_value out_db_namet noprint
column in_spidt new_value out_spidt noprint
column in_usert new_value out_usert noprint
column in_patht new_value out_patht noprint

select value in_db_namet, user in_usert
from v$parameter
where name = ‘db_name’;

select value in_patht
from v$parameter
where name = ‘user_dump_dest’;

alter session set tracefile_identifier = &out_usert;

SET TERMOUT ON

column program format a20

select username, sid, serial#, program
from v$session
where program NOT LIKE ‘ORACLE%’;

ACCEPT in_user PROMPT ‘Enter User ID from list above : ‘
ACCEPT in_sid PROMPT ‘Enter SID from list above : ‘
ACCEPT in_serial PROMPT ‘Enter SERIAL from list above : ‘
PROMPT
PROMPT ‘Write the SID and SERIAL down…will need them to stop the trace’
PROMPT

SET TERMOUT OFF

select p.spid in_spidt
from v$process p, v$session s
where p.addr = s.paddr
and s.username = upper(‘&in_user’)
and s.sid = &in_sid;

SET TERMOUT ON

PROMPT
PROMPT Started SQL Trace in session

SELECT ‘&out_path’ “Trace File Path” from dual;

SELECT ‘&out_db_namet’ || ‘_ora_’ || ‘&out_spidt’ || ‘.trc’ “Trace File Name”
from dual;
PROMPT

rem exec dbms_system.set_ev(&in_sid, &in_serial, 10046, 12, ”);

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => &in_sid, serial_num => &in_serial, waits => TRUE, binds => TRUE);

SET FEEDBACK ON
SET VERIFY ON
SET ECHO ON

Start Trace

/**********************************************************************
* Description:
*      SQL*Plus script to start the SQL Trace facility for the current session.
*      This script also displays the trace file name being created and its
*      location.
*
*********************************************************************/
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF

column in_db_name new_value out_db_name noprint
column in_spid new_value out_spid noprint
column in_user new_value out_user noprint
column in_path new_value out_path noprint

select value in_db_name, user in_user
from v$parameter
where name = ‘db_name’;

select value in_path
from v$parameter
where name = ‘user_dump_dest’;

alter session set tracefile_identifier = &out_user;

select p.spid in_spid
from v$process p, v$session s
where p.addr = s.paddr
and s.username = USER
and s.sid = (select sid from v$mystat where rownum = 1);

SET TERMOUT ON

PROMPT
PROMPT Start SQL Trace in your session

SELECT ‘&out_path’ “Trace File Path” from dual;

SELECT ‘&out_db_name’ || ‘_ora_’ || ‘&out_spid’ || ‘_’ || lower(‘&out_user’) || ‘.trc’ “Trace File Name”
from dual;
PROMPT

alter session set events ‘10046 trace name context forever,level 12’;

SET FEEDBACK ON
SET VERIFY ON
SET ECHO ON

Display database locks and latches (with tables names, etc)

select s.sid, s.serial#, decode(s.process, null,decode(substr(p.username,1,1), ‘?’,upper(s.osuser), p.username),decode(p.username, ‘ORACUSR ‘, upper(s.osuser), s.process)) process,
nvl(s.username, ‘SYS (‘||substr(p.username,1,4)||’)’) username, decode(s.terminal, null, rtrim(p.terminal, chr(0)), upper(s.terminal)) terminal,decode(l.type,
‘TM’, ‘DML/DATA ENQ’,   ‘TX’, ‘TRANSAC ENQ’, ‘UL’, ‘PLS USR LOCK’, ‘BL’, ‘BUF HASH TBL’,  ‘CF’, ‘CONTROL FILE’, ‘CI’, ‘CROSS INST F’,  ‘DF’, ‘DATA FILE   ‘,
‘CU’, ‘CURSOR BIND ‘, ‘DL’, ‘DIRECT LOAD ‘,  ‘DM’, ‘MOUNT/STRTUP’, ‘DR’, ‘RECO LOCK   ‘,  ‘DX’, ‘DISTRIB TRAN’,  ‘FS’, ‘FILE SET    ‘,  ‘IN’, ‘INSTANCE NUM’,
‘FI’, ‘SGA OPN FILE’, ‘IR’, ‘INSTCE RECVR’,  ‘IS’, ‘GET STATE   ‘, ‘IV’, ‘LIBCACHE INV’,  ‘KK’, ‘LOG SW KICK ‘, ‘LS’, ‘LOG SWITCH  ‘,
‘MM’, ‘MOUNT DEF   ‘,  ‘MR’, ‘MEDIA RECVRY’, ‘PF’, ‘PWFILE ENQ  ‘,  ‘PR’, ‘PROCESS STRT’, ‘RT’, ‘REDO THREAD ‘,  ‘SC’, ‘SCN ENQ     ‘,
‘RW’, ‘ROW WAIT    ‘,  ‘SM’, ‘SMON LOCK   ‘,  ‘SN’, ‘SEQNO INSTCE’, ‘SQ’, ‘SEQNO ENQ   ‘,  ‘ST’, ‘SPACE TRANSC’,
‘SV’, ‘SEQNO VALUE ‘,  ‘TA’, ‘GENERIC ENQ ‘, ‘TD’, ‘DLL ENQ     ‘,  ‘TE’, ‘EXTEND SEG  ‘, ‘TS’, ‘TEMP SEGMENT’,  ‘TT’, ‘TEMP TABLE  ‘,
‘UN’, ‘USER NAME   ‘,  ‘WL’, ‘WRITE REDO  ‘,  ‘TYPE=’||l.type) type, decode(l.lmode, 0, ‘NONE’, 1, ‘NULL’, 2, ‘RS’, 3, ‘RX’,
4, ‘S’,    5, ‘RSX’,  6, ‘X’, to_char(l.lmode) ) lmode, decode(l.request, 0, ‘NONE’, 1, ‘NULL’, 2, ‘RS’, 3, ‘RX’, 4, ‘S’, 5, ‘RSX’, 6, ‘X’,
to_char(l.request) ) lrequest,  decode(l.type, ‘MR’, decode(u.name, null,’DICTIONARY OBJECT’, u.name||’.’||o.name),
‘TD’, u.name||’.’||o.name,  ‘TM’, u.name||’.’||o.name, ‘RW’, ‘FILE#=’||substr(l.id1,1,3)||   ‘ BLOCK#=’||substr(l.id1,4,5)||’ ROW=’||l.id2,
‘TX’, ‘RS+SLOT#’||l.id1||’ WRP#’||l.id2,  ‘WL’, ‘REDO LOG FILE#=’||l.id1,  ‘RT’, ‘THREAD=’||l.id1,
‘TS’, decode(l.id2, 0, ‘ENQUEUE’, ‘NEW BLOCK ALLOCATION’),  ‘ID1=’||l.id1||’ ID2=’||l.id2) object
from   sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,sys.v_$process p
where  s.paddr  = p.addr(+)
  and  l.sid    = s.sid
and  l.id1    = o.obj#(+)
and  o.owner# = u.user#(+)
and  l.type   <> ‘MR’
UNION ALL  /*** LATCH HOLDERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
‘LATCH’, ‘X’, ‘NONE’, h.name||’ ADDR=’||rawtohex(laddr)
from   sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where  h.pid  = p.pid
and  p.addr = s.paddr
UNION ALL    /*** LATCH WAITERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
‘LATCH’, ‘NONE’, ‘X’, name||’ LATCH=’||p.latchwait
from   sys.v_$session s, sys.v_$process p, sys.v_$latch l
where  latchwait is not null
and  p.addr      = s.paddr
and  p.latchwait = l.addr

Find Locked Sessions

FIND LOCKED SESSIONS

set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10

select oracle_username || ‘ (‘ || s.osuser || ‘)’ username
, s.sid || ‘,’ || s.serial# sess_id
, owner || ‘.’ || object_name object
, object_type
, decode( l.block
, 0, ‘Not Blocking’
, 1, ‘Blocking’
, 2, ‘Global’) status
, decode(v.locked_mode
, 0, ‘None’
, 1, ‘Null’
, 2, ‘Row-S (SS)’
, 3, ‘Row-X (SX)’
, 4, ‘Share’
, 5, ‘S/Row-X (SSX)’
, 6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id

Shows waiting sessions and the events they await

 select  sw.sid  sid, p.spid  spid, s.username username, s.osuser   osuser, sw.event event, s.machine  machine
, s.program  program, decode(sw.event,’db file sequential read’, sw.p3, ‘db file scattered read’, sw.p3, null) blocks
from    v$session_wait sw,v$session  s, v$process p
where   s.paddr = p.addr
and     event     not in (‘pipe get’,’client message’)
and     sw.sid  = s.sid

ADRCI

The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data. ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g.

ADRCI enables:

• Viewing diagnostic data within the Automatic Diagnostic Repository (ADR).
• Viewing Health Monitor reports.
• Packaging of incident and problem information into a zip file for transmission to Oracle Support.

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

ADR data is secured by operating system permissions on the ADR directories, hence there is no need to log in to ADRCI.

To query the ADRCI for info.

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INST_ID                                            NUMBER
 NAME                                               VARCHAR2(64)
 VALUE                                              VARCHAR2(512)

set linesize 125
col value format a80
SELECT * FROM gv$diag_info;

For monitoring and DBA services contact IT-Remote.com

Oracle Licensing for Standby and Data Guard

Licensing for Standby and Data Guard is a very misunderstood and sketchy subject.
I’ve worked for companies that were charged for the standby server, and sometimes obtained
huge discounts. It depends on how close to May 31st you are. If the software is running on the
server you must license it.

BTW here is a link to a couple of Oracle’s licensing docs:

Oracle’s Data Recovery Licensing Guidelines Doc

Oracle’s Licensing Guidelines Doc

APEX Development Notes

Over the years I have been very involved in the development of various APEX applications. This area of my blog is to talk about the type of things that I am doing that might be interesting general features and techniques of APEX. Feel free to send me anything you feel is relevant to what I am talking about in here.

CentOS 5.6, Oracle11gR2, and VMware Player 3.0

I have recently done an install of CentOS 5.4 and Oracle11gR2, here are some of my notes. First of all since I wanted to be mobile I decided to install this on my laptop and use VMware Player 3.0 to make my virtuals.  Then I went to www.centos.org to get the latest release of the .ISOs and the DVD. When installing Oracle11gR2 pay attention to the packages that need to be installed, as missing some of these will fail the install.

yum install ncompress

in order to install the uncompress that doesn’t get installed with CentOS 5.4

In order to make sure APEX gets installed simply go to the APEX directory under the ORACLE_HOME and run apexins.sql. This will install the APEX environment into your 11.2 database. This script will take several parameters which include tablespaces in which to install the product. You can simply edit the script to see at the top of the script which parameters are needed. You will also need to choose a port for the gateway to run on.

Once this occurs you can now begin the process of setting up APEX internally.

Sun Solaris Downloads

http://www.sun.com/download/index.jsp?cat=Operating%20Systems&tab=3&subcat=Solaris%20Operating%20Systems