Warning: Creating default object from empty value in /home/customer/www/gavinsoorma.com/public_html/wp-content/themes/specular/admin/inc/class.redux_filesystem.php on line 29
Monitoring | Oracle DBA – Tips and Techniques
News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Script – Monitor Flashback Logs

  • Posted by Gavin Soorma
  • On July 9, 2009
  • 0 Comments
PROMPT How Far Back Can We Flashback To (Time)? PROMPT select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log; PROMPT PROMPT How Far Back Can We Flashback To (SCN)? PROMPT col oldest_flashback_scn format 99999999999999999999999999 select oldest_flashback_scn from v$flashback_database_log; PROMPT PROMPT Flashback Area Usage SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; PROMPT col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999 […]
Read More
 

Script – Temporary tablespace usage

  • Posted by Arjun Raja
  • On June 30, 2009
  • 3 Comments
— Listing of temp segments.— SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size […]
Read More
 1

Monitor long running operations using v$session_longops

  • Posted by Gavin Soorma
  • On June 30, 2009
  • 0 Comments
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1; Note: the same query can be used to monitor RMAN backup status SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != […]
Read More
 3

Monitor Data Guard Log Shipping

  • Posted by Gavin Soorma
  • On June 30, 2009
  • 0 Comments
Note: This query needs to be run on the Primary database. SET PAGESIZE 124 COL DB_NAME FORMAT A8 COL HOSTNAME FORMAT A12 COL LOG_ARCHIVED FORMAT 999999 COL LOG_APPLIED FORMAT 999999 COL LOG_GAP FORMAT 9999 COL APPLIED_TIME FORMAT A12 SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM ( SELECT NAME DB_NAME FROM V$DATABASE ), ( SELECT […]
Read More
 

Identify active transactions in undo and rollback segments

  • Posted by Gavin Soorma
  • On June 29, 2009
  • 0 Comments
col o format a10 col u format a10 select osuser o, username u, sid, segment_name s, substr(sa.sql_text,1,200) txt from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa where s.taddr=t.addr and t.xidusn=r.segment_id(+) and s.sql_address=sa.address(+) And substr(sa.sql_text,1,200) is not null order by 3; col name format a8 col username format a8 col osuser format a8 col start_time […]
Read More
 

Locked Sessions and Locked Objects

  • Posted by Gavin Soorma
  • On June 29, 2009
  • 0 Comments
PROMPT Blocked and Blocker Sessions col blocker_sid format 99999999999 col blocked_sid format 99999999999 col min_blocked format 99999999999 col request format 9999999 select /*+ ORDERED */ blocker.sid blocker_sid , blocked.sid blocked_sid , TRUNC(blocked.ctime/60) min_blocked , blocked.request from (select * from v$lock where block != 0 and type = 'TX') blocker , v$lock blocked where blocked.type='TX' and […]
Read More
 

Script- Track redo generation by day

  • Posted by Gavin Soorma
  • On June 20, 2009
  • 2 Comments
select trunc(completion_time) rundate ,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1;
Read More
 

Redo Log File Switches – By hour of the day

  • Posted by Gavin Soorma
  • On June 18, 2009
  • 0 Comments
prompt prompt "Morning .........." select to_char(first_time,'DD/MON') day, to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18" from v$log_history WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7 group by to_char(first_time,'DD/MON'); prompt prompt Prompt "Evening ........" prompt select to_char(first_time,'DD/MON') day, to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23", to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06" from v$log_history WHERE […]
Read More
 1