Friday, May 01, 2009

Scripts Used in Critical Production Biz Time Monitoring

=== Temp Usage ===

 

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;

 

SELECT ss.sid, sum(st.blocks)/1024*8 FROM v$sort_usage st, v$session ss where ss.saddr=st.session_addr group by ss.sid having sum(st.blocks)/1024*8 > 100 order by 2;

 

SELECT  /*+ RULE */ s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                         NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                         s.status ||' for '||
                         LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                         LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                         u.tablespace, u.contents, u.extents, round((u.blocks*8)/1024),
                         s.sql_address, s.sql_hash_value
                 FROM    v$session s, v$sort_usage u
                 WHERE   s.saddr    = u.session_addr
                 AND     u.contents = 'TEMPORARY'
                 AND     s.audsid != USERENV('sessionid')
                 AND    (u.blocks*8)/1024 >= 1000
                 ORDER   BY 1,2,3,4,5 Desc;

 

=== High Redo ===

 

SELECT s.inst_id,s.sid, s.serial#, s.username, s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000
ORDER BY 6 desc, 1, 2, 3, 4;


=== Rollback Used ===
                                 
SELECT rn.name, ROUND(rs.rssize/1024/1024),
                        s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                        NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                        s.sql_address, s.sql_hash_value, p.spid,
                        s.status ||' for '||
                        LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                        LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                        round(ceil((t.used_ublk*8)/1024),1)
                 FROM   v$rollname rn, v$rollstat rs,
                        v$session s, v$transaction t, v$process p
                 WHERE  rn.usn = rs.usn
                 AND    round((t.used_ublk*8)/1024) >= 1000
                 AND    rs.usn = t.xidusn
                 AND    s.sid = p.pid (+)
                 AND    s.taddr = t.addr
                 ORDER  BY 2 desc, s.sid ,s.status

 

=== Roll back segement Information ====

 

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) "Total Space in GB" from dba_undo_extents where tablespace_name like '%UNDO%'
group by tablespace_name, status;

 

=== Shared Pool Usage ===

 

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' FROM gv$sgastat  WHERE name='free memory' AND pool='shared pool';

 

=== Archive Generation for last 5 hours ===

 

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)  FROM v$log_history WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5 GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'), TO_CHAR(first_time, 'HH24') ORDER BY 2;

 

=== High Memory ===

 

select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb, p.*
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512;

 

=== Performance ===

 

select sql_hash_value, count(*) from v$session where event like 'enq%' group by sql_hash_value;

 

select sql_hash_value,username,osuser, count(*) from v$session where event like 'enq%' and SQL_HASH_VALUE='&event' group by sql_hash_value,username,osuser;

 

select sql_text from v$sqlarea where hash_value = '&hash_value';

 

select s1.sid,FETCHES,ROWS_PROCESSED from v$sql s,v$session s1 where s.HASH_VALUE=s1.SQL_HASH_VALUE and s1.sid=4885;

 

col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x where s.paddr in
( select addr from v$process where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;

 

col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';

 

=== Top 10 Memory Process at OS level ===

 

UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep -i `echo $ORACLE_SID` | tail -10

 

=== Other Script to update the daily report ===

 

select status,count(1) from gv$session group by status; 

 


STATUS     COUNT(1)
-------- ----------
ACTIVE           22
INACTIVE        155

 

select count(1) from dba_tables where logging='NO';

 

  COUNT(1)
----------
       919

 

select distinct status,count(1) from dba_indexes group by status;

 

STATUS     COUNT(1)
-------- ----------
N/A            1639
UNUSABLE          1
VALID          6162

 

select count(1) from dba_objects where status='INVALID';
'
  COUNT(1)
----------
       681

 

CRSCTL CheatSheet

CRSCTL CheatSheet


You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

Start Oracle Clusterware

#crsctl start crs

Stop Oracle Clusterware

#crsctl stop crs

Enable Oracle Clusterware

#crsctl enable crs

It enables automatic startup of Clusterware daemons

Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some
operations like OS patching and does not want clusterware to start the daemons automatically.

Checking Voting disk Location

$crsctl query css votedisk

0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).

Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

Add Voting disk

#crsctl add css votedisk path

Remove Voting disk

#crsctl delete css votedisk path

Check CRS Status

$crsctl check crs

Cluster Synchronization Services appears healthy

Cluster Ready Services appears healthy

Event Manager appears healthy

You can also see particular daemon status

$crsctl check cssd

Cluster Synchronization Services appears healthy

$crsctl check crsd

Cluster Ready Services appears healthy

$crsctl check evmd

Event Manager appears healthy

You can also check Clusterware status on both the nodes using

$crsctl check cluster

prod01 ONLINE

prod02 ONLINE

Checking Oracle Clusterware Version

To determine software version (binary version of the software on a particular cluster node) use

$crsctl query crs softwareversion

Oracle Clusterware version on node [prod01] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.

There are other options for CRSCTL too which can be seen using

$crsctl

Or

$crsctl help




Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...