All posts by mrculp

Support for ASMLib on Red Hat

Oracle will no longer release ASMLib kernel drivers for RHEL 6 kernels. See this note on support.oracle.com

Oracle ASMLib Software Update Policy for Red Hat Enterprise Linux Supported by Red Hat (Doc ID 1089399.1)

Going to Oracle EL 6 is an option, and may save money in the long run, one vendor to support OS and database. Oracle is much closer to the drivers that are used for database purposes exclusively, and support may actually be less expensive.

Show waiting sessions and the event they wait on

 

#!/bin/ksh

sqlplus -s "/ as sysdba" <<EOF

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

EOF

 

Linux Quick Finds

Finding Large Directories

look for large files with this command:

find . -type f -size +100000k -exec ls -lh {} \; | awk ‘{ print $9 “: ” $5 }’

This looks for files over 100MB, but other than datafiles there were very few files found. I knew I needed to look for large directories. Directories that had a large amount of small files. This command worked nicely:

du -h / | grep ^[1-9][0-9][0-9.]*G | sort -rn

From there I was able to find the directories that I needed to clear out.

Find and zip files newer than X minutes

find . -mmin -1620 -type f -name \*_lm[d,s]\*trc -print -exec zip /tmp/`hostname`_lm_files.zip {} \;

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;

Start Session Trace Function


/**************************************************************************
 * 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';

-- Find the path of the trace file directory
select value in_patht
  from v\$parameter
 where name = 'user_dump_dest';

-- This will put identifiable characters into the trace file and make it easier to find
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

This will start a trace in another session
exec dbms_system.set_sql_trace_in_session(3,5023,true);
exec dbms_system.set_sql_trace_in_session(3,5023,false);

Oracle11gR2 Linux install

This should be found in linux_common.ksh

Preinstall requirements:

Minimum RAM requirement: 1.5 GB for GRID Infrastructure and 2.5GB for GI plus RAC

you can verify easily by:

# grep memTotal /proc/meminfo

For Swap space checking

# grep SwapTotal /proc/meminfo

For disk temp space

# df -h /tmp