Total Pageviews

Sunday, 24 April 2016

Scripts To monitor Oracle Database And Application Server

Check Archive log size and deletion
===========================
For last 20 days.
===========
SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_GB,
         SUM_ARCH_DEL.DELETED_GB,
         SUM_ARCH.GENERATED_GB - SUM_ARCH_DEL.DELETED_GB "REMAINING_GB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      GENERATED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      DELETED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
       AND TO_DATE (SUM_ARCH.DAY, 'DD/MM/YYYY') >sysdate-20
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

another
======
SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_GB,
         SUM_ARCH_DEL.DELETED_GB,
         SUM_ARCH.GENERATED_GB - SUM_ARCH_DEL.DELETED_GB "REMAINING_GB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      GENERATED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024)/1024, 2))
                      DELETED_GB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)

ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

ASMspacereport 
=============
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup ORDER BY name
/
exit

ASM disk usage in detail
===================
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup ORDER BY name
/
exit


Check tablespace free space and used
============================
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,       decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,       decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
               from     (select tablespace_name, sum(bytes)/1024/1024 used_mb
                from    dba_data_files group by tablespace_name union all
                        select  tablespace_name || '  **TEMP**'
                                ,       sum(bytes)/1024/1024 used_mb
                                        from    dba_temp_files group by tablespace_name) tsu
                                        ,       (select tablespace_name, sum(bytes)/1024/1024 free_mb
                                                from    dba_free_space group by tablespace_name) tsf
                                                where   tsu.tablespace_name = tsf.tablespace_name (+)
                                                order   by 4
                                                /

Tablespace usage
=============
set lines 300
 SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024/1024) "Tot size GB",
 round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
 round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
 100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
 round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
 FROM (
 SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
 MAX(BYTES) LARGEST,COUNT(*) CHUNKS
 FROM SYS.DBA_FREE_SPACE A
 GROUP BY TABLESPACE_NAME
 UNION
 SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
 FROM SYS.DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
 where A.TABLESPACE_NAME='&TBS'
 GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
 order by 5
/

Redolog Size
==========
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

Datafile size
==========
set linesize 1000
set pagesize 1000
select a.file_name, a.tablespace_name, a.bytes/1024/1024 "Size",
to_char(b.creation_time, 'DD-MON-YYYY HH24:MI:SS') " Creation Date"
from dba_data_files a, v$datafile b where a.tablespace_name='&tbsp'
and a.file_id = b.file#
order by file_name;

Pending Concurrent request
=====================
select COUNT (distinct cwr.request_id) Pending_Requests FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id and cq.LANGUAGE='US'
AND cwr.requested_by = fu.user_id and cq.user_concurrent_queue_name
in ( select unique user_concurrent_queue_name from apps.fnd_concurrent_queues_tl);

User Connected to Database
====================
select sid, serial#, username, osuser, program from gv$session  where username is not null AND PROGRAM NOT LIKE 'FTE_TXN_MANAGER%'
AND PROGRAM NOT LIKE 'STANDARD%'
AND PROGRAM NOT LIKE 'FNDSCH%'
AND PROGRAM NOT LIKE 'RCVOLTM%'
AND PROGRAM NOT LIKE 'JDBC%'
AND PROGRAM NOT LIKE 'PODAMGR%'
AND PROGRAM NOT LIKE 'INVMGR%'
AND PROGRAM NOT LIKE 'frmweb%'
AND PROGRAM NOT LIKE 'OAMCOLMGR%'
AND PROGRAM NOT LIKE 'INVTMRPM%'
AND PROGRAM NOT LIKE 'frmbld%'
AND PROGRAM NOT LIKE 'CRM%'
AND PROGRAM NOT LIKE 'ICM%'
AND PROGRAM NOT LIKE 'IEU_SH_CS%'
AND PROGRAM NOT LIKE 'FNDSM%'
AND PROGRAM NOT LIKE 'CRPINQMGR%'
AND PROGRAM NOT LIKE 'PASMGR%'
AND PROGRAM NOT LIKE 'IEU_WL_CS%'
AND PROGRAM NOT LIKE 'oraagent.bin%'
AND PROGRAM NOT LIKE 'FNDIM%';

Check Managers status from Front end.
=============================
set pagesize 9999;
select decode(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager','FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name", max_processes as "TARGET Processes", running_processes as "ACTUAL Processes" from apps.fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');


Query to check lock
================
set serveroutput on size 10000
declare
  cursor c1 is
    select * from v$lock where request != 0
      order by id1, id2;
  wid1            number := -999999;
  wid2            number := -999999;
  wholder_detail  varchar2(200);
  v_err_msg          varchar2(80);
  wsid            number(5);
  wstep           number(2);
  wtype           varchar2(10);
  wobject_name    varchar2(180);
  wobject_name1   varchar2(80);
  wlock_type      varchar2(50);
  w_lastcallet  varchar2(11);
  h_lastcallet  varchar2(11);
begin
  for c1_rec in c1 loop
    if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
       null;
    else
       wstep  := 10;
       select sid , type into wsid , wtype
         from v$lock
         where id1  = c1_rec.id1
           and id2  = c1_rec.id2
           and request = 0
           and lmode != 4;
      dbms_output.put_line('  ');
       wstep  := 20;
      select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
                ' SID:' || s.sid || ' Status: ' || s.status  ||
                ' (' ||         floor(last_call_et/3600)||':'||
                                floor(mod(last_call_et,3600)/60)||':'||
                                mod(mod(last_call_et,3600),60) ||
                ') Module:'|| module ||
              ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
          into wholder_detail
          from v$session s, v$process p
          where s.sid= wsid
            and s.paddr = p.addr;
      dbms_output.put_line(wholder_detail);

      begin
        select decode(wtype,'TX', 'Transaction',
                            'DL', 'DDL Lock',
                            'MR', 'Media Recovery',
                            'RT', 'Redo Thread',
                            'UN', 'User Name',
                            'TX', 'Transaction',
                            'TM', 'DML',
                            'UL', 'PL/SQL User Lock',
                            'DX', 'Distributed Xaction',
                            'CF', 'Control File',
                            'IS', 'Instance State',
                            'FS', 'File Set',
                            'IR', 'Instance Recovery',
                            'ST', 'Disk Space Transaction',
                            'TS', 'Temp Segment',
                            'IV', 'Library Cache Invalida-tion',
                            'LS', 'Log Start or Switch',
                            'RW', 'Row Wait',
                            'SQ', 'Sequence Number',
                            'TE', 'Extend Table',
                            'TT', 'Temp Table',
                            'Un-Known Type of Lock')
                into wlock_type
                from dual;
        declare
          cursor c3 is
            select object_id from v$locked_object
              where session_id = wsid;
        begin
          wobject_name := '';
          for c3_rec in c3 loop
            select object_type||': '||owner||'.'||object_name
              into wobject_name
              from dba_objects
              where object_id = c3_rec.object_id;
            wobject_name := wobject_name ||' '||wobject_name1;
          end loop;
        exception
          when others then
            wobject_name := wobject_name ||' No Object Found';
        end;
        dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
      exception
        when no_data_found then
          dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
      end;
    end if;
       wstep  := 30;
    select '....   Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
                ' SID:' || s.sid || ' Status: ' || s.status  ||
                ' (' ||         floor(last_call_et/3600)||':'||
                                floor(mod(last_call_et,3600)/60)||':'||
                                mod(mod(last_call_et,3600),60) ||
                ') Module:'|| module ||
              ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
          into wholder_detail
          from v$session s, v$process p
          where s.sid= c1_rec.sid
            and s.paddr = p.addr;
    dbms_output.put_line(wholder_detail);
    wid1  := c1_rec.id1;
    wid2  := c1_rec.id2;
  end loop;
  if wid1 = -999999 then
       wstep  := 40;
    dbms_output.put_line('No one requesting locks held by others');
  end if;
exception
  when others then
         v_err_msg := (sqlerrm ||'  '|| sqlcode||' step='||to_char(wstep));
    DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/

Check tbs
========
select  * from dba_tablespace_usage_metrics where used_percent >90;

To Check Asm mount point disk space
============================
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT
    distinct name                            group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                  allocation_unit_size
  , state                                        state
  , type                                         type
  , round(total_mb/1024)                              "total_gb"
  , round(free_mb/1024)                               "free_gb"
  , round((total_mb - free_mb) / 1024)          "used_gb"
  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"
from v$asm_diskgroup
where name ='DATA_DEV' and round((1- (free_mb / total_mb))*100, 2)>80
ORDER BY name
/





other tbs scripts
===========
http://dbaahmed.blogspot.in/2015/01/tablespace-queries-col-tablespace-for.html

No comments :

Post a Comment