Oracle Technical

Posts about Oracle databases

Oracle ASM – Commands

Posted by

0

SQL 

select * from v$asm_diskgroup order by free_mb

asmcmd 

asmcmd lsdg|awk '{sub(/\//,"",$14);print}'|tr -dc '[:print:]\n'|awk -v date="$(date +"%Y-%m-%d %r")" '{print $14 "\t\t" $9"\t\t"$8"      :          " date}'|sort -k 2 -n

Oracle Virtual Machine Manager

Posted by

0

Also see: Troubleshoot high cpu usage – Java Thread

OVM Manager consists of a MySQL database and a Weblogic Server.
I have found that the cpu usage is at nearly 100% on the OVMM Linux server.
After some investigation I see that the java thread that causes most of the cpu usage belongs to CLIMain
and the thread is “EchoShell”

It is the OVM CLI – when I stop the service (/sbin/service ovmcli stop), the high cpu usage disappears.
The Command Line Interface (CLI) is used to enter commands to manage the VMs, repositories, NICs, etc.

The high cpu usage occurs when the CLI times out after 45 minutes – will test it and then use the “-o ServerAliveInterval=40” option for ssh connection in an attempt to avoid the switch to high cpu usage

High cpu usage definitely starts when timeout occurs –
the “-o ServerAliveInterval=40” option for ssh connection keeps the session alive,
no timeout, and no high cpu.

Oracle – tkprof – Extract all waits from output file using Powershell

Posted by

0

$astr=@("Event", "\*\*\*\*", "--------")
#Gets them all
#set up the regexp
$regex = '(?ms) Event waited on(.+?)\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*'
#get-content gets an array of strings, join them all into one string separated by newline
$string=(Get-Content .\CCI1_ora_15824_2.txt) -join "`n"
# search for the regexp in the string and output the matches
Select-String -input $string -Pattern $regex -AllMatches | `
% { $_.Matches } | `
% { 
    $arr = $_.Value -split  "`n"
    foreach ($line in $arr) {
     # output only lines that do not contain the strings in the array, trim it and replace "more than one space" with a semi-colon
     Select-String -input $line -pattern $astr -NotMatch | ForEach-Object {$_.Line.Trim() -replace '  +',";"}
    }
}

Oracle – Idle Connections

Posted by

0

Determine connections that have been idle for more than a certain amount of time
(be aware that last_call_et also shows number of seconds active also)


Examples

-- Connections idle for > 60 minutes 
select
s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60) >60
order by username, last_call_et desc;


-- Connections idle for > 1 day 
select
s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60 / 60 /24) > 1
order by username, last_call_et desc;

-- Connections idle for > 5 days 

select s.sid,s.serial#,s.username,s.status,
to_char(s.logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "IDLE",
s.program, s.inst_id,p.spid,
'alter system kill session '''||s.sid||','||s.serial#||''''
from
gv$session s
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
where
type='USER'
and (LAST_CALL_ET / 60 / 60 /24) > 5
order by username, last_call_et desc;