Alert Log Through SQL Plus. Monitoring Session(s), Performance, Load, Killing Session etc Tablespace Monitoring Scripts select df.tablespace_name, df.file_name, round(df.bytes/1024/1024) TotalSizeMB, nvl(round(usedBytes/1024/1024), 0) UsedMB, nvl(round(freeBytes/1024/1024), 0) AvailableMB, nvl(round(freeBytes/df.bytes * 100), 0) freePerc, df.autoextensible from dba_data_files df left join ( select file_id, sum(bytes) usedBytes from dba_extents group by file_id ) ext on df.file_id = ext.file_id left join ( select file_id, sum(bytes) freeBytes from dba_free_space group by file_id ) free on df.file_id = free.file_id order by df.tablespace_name, df.file_name select a.TABLESPACE_NAME, (select file_name from dba_temp_files where FILE_ID=a.file_id) filen, (a.bytes_used)/1024/1024 "Space Used MB", (a.bytes_free)/1024/1024 "Space Free MB" FROM V$temp_space_header a Below is the monitoring scripts which gives us the details about
Total tablespace size, Used space, Free space, Largest Space in
tablespace,Percentage utilizes and Alarm mark (*) if it exceeds 90 %.
select tbs.tablespace_name,
tot.bytes/1024/1024 total, tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 used, sum(nvl(fre.bytes,0))/1024/1024 free, max(fre.bytes/1024/1024) Largest_Space, (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct, decode( greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90), 90, '', '*' ) pct_warn from dba_free_space fre, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) tot, dba_tablespaces tbs where tot.tablespace_name = tbs.tablespace_name and fre.tablespace_name(+) = tbs.tablespace_name group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes order by 6 desc; ____________________________________________________
To check the Uptime of the Database SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance; ____________________________________________________
To check UNDO Space Status
elect status , sum(blocks)*8192/1024/1024/1024 GB from dba_undo_extents group by status;
____________________________________________________
To find the used and free space in Temporary Tablespace
SELECT tablespace_name, SUM(bytes_used)/1024/1024 "Space Used MB", SUM(bytes_free)/1024/1024 "Space Free MB"
FROM V$temp_space_header GROUP BY tablespace_name; ____________________________________________________ Flash Recovery SELECT NAME, SPACE_LIMIT/1024/1024,SPACE_USED/1024/1024 FROM V$RECOVERY_FILE_DEST; after manually deleting the archive log files CROSSCHECK ARCHIVELOG ALL; DELETE EXPIRED ARCHIVELOG ALL; then check the space utilization in the FRA space. ___________________________________________________ To check the status of synchronization between Production and Standby Database
conn <username>/<password>@<TNS-Name of Production Database>
select a.name DB_NAME, b.lh LOG_HIST,c.al ARCH_LOG from (select name||' (PROD) -> ' name from v$database) a, (select max(sequence#) lh from v$log_history) b, (select max(sequence#) al from v$archived_log) c; conn sys/<password>@<TNS-Name of DR Database> as sysdba select a.name,a.open_mode,log_mode,to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "DATE" from v$database a,dual; select a.name DB_NAME, b.lh LOG_HIST,c.al ARCH_LOG from (select name||' (DR) -> ' name from v$database) a, (select max(sequence#) lh from v$log_history) b, (select max(sequence#) al from v$archived_log) c; Note :
Sequence# from V$log_history gives us the archived log that has been applied to the database Sequence# from V$archived_log gives us the archived log that has been registered and copied to the DR Database ____________________________________________________
To get information about the Database
Control Files
SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status" FROM v$controlfile c ORDER BY 1; Datafiles
SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status", d.enabled "Enabled", LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)" FROM v$datafile d ORDER BY 1; Tempfiles
select a.TABLESPACE_NAME, (select file_name from dba_temp_files where FILE_ID=a.file_id) filen, (a.bytes_used)/1024/1024 "Space Used MB", (a.bytes_free)/1024/1024 "Space Free MB" FROM V$temp_space_header a Log Files
select a.group#,to_char(b.first_time,'DDMMYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1 ____________________________________________________
Client IP address select sys_context('USERENV','IP_ADDRESS') from dual; Server IP address select utl_inaddr.get_host_address from dual; ____________________________________________________ To Check locks on the database.
select session_id "sid",SERIAL# "Serial", substr(object_name,1,20) "Object", substr(os_user_name,1,10) "Terminal", substr(oracle_username,1,10) "Locker", nvl(lockwait,'active') "Wait", decode(locked_mode, 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive', 'unknown') "Lockmode", OBJECT_TYPE "Type" FROM SYS.V_$LOCKED_OBJECT A, SYS.DBA_OBJECTS B, SYS.V_$SESSION c WHERE A.OBJECT_ID = B.OBJECT_ID AND C.SID = A.SESSION_ID ORDER BY 1 ASC, 5 Desc / ____________________________________________________
To check Memory utilization by connected sessions. SET LINESIZE 200
COLUMN username FORMAT A20 COLUMN module FORMAT A20 SELECT NVL(a.username,'(oracle)') AS username, a.module, a.program, Trunc(b.value/1024) AS memory_kb FROM v$session a, v$sesstat b, v$statname c WHERE a.sid = b.sid AND b.statistic# = c.statistic# AND c.name = 'PGA Memory' AND a.program IS NOT NULL ORDER BY b.value DESC; ____________________________________________________
To check the status of the Redo log files col member for a30set lines 120 col status for a10 select a.group#,to_char(b.first_time,'DDMMYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1; ____________________________________________________
To SID of Particular resouce consuming PID
select
substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#, substr(b.machine,1,6) box, substr(b.username,1,10) username, -- b.server, substr(b.osuser,1,8) os_user, substr(b.program,1,30) program from v$session b, v$process a where b.paddr = a.addr and type='USER' and a.spid=<PID No> order by spid; ____________________________________________________
To Check Activity of a SID
--Begin--
set serveroutput on size 50000
set echo off feed off veri off accept SID prompt 'Enter Oracle SID: '
DECLARE
v_sid number; s sys.v_$session%ROWTYPE; p sys.v_$process%ROWTYPE; BEGIN begin select sid into v_sid from sys.v_$session s where sid = &&SID; exception when no_data_found then dbms_output.put_line('Unable to find SID &&SID!!!'); return; when others then dbms_output.put_line(sqlerrm||'naaaaaaaaaaaaaaaaaaaaa'); return; end; select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr; dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#); dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program); dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program); dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal); dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine); dbms_output.put_line('Ora User : '|| s.username); dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type); dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE')); dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS')); dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9999999999.0') || ' min'); dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE')); dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE')); dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext where HASH_VALUE = s.sql_hash_value order by piece) loop dbms_output.put_line(chr(9)||c1.sql_text); end loop; dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext where HASH_VALUE = s.prev_hash_value order by piece) loop dbms_output.put_line(chr(9)||c1.sql_text); end loop; dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop dbms_output.put_line(chr(9)||c1.state||': '||c1.event); end loop; dbms_output.put_line('Connect Info:');
for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop dbms_output.put_line(chr(9)||': '||c1.network_service_banner); end loop; dbms_output.put_line('Locks:');
for c1 in ( select decode(l.type, -- Long locks 'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ', 'UL', 'PLS USR LOCK', -- Short locks 'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE', 'CI', 'CROSS INST F', 'DF', 'DATA FILE ', 'CU', 'CURSOR BIND ', 'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP', 'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN', 'FS', 'FILE SET ', 'IN', 'INSTANCE NUM', 'FI', 'SGA OPN FILE', 'IR', 'INSTCE RECVR', 'IS', 'GET STATE ', 'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ', 'LS', 'LOG SWITCH ', 'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY', 'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT', 'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ', 'RW', 'ROW WAIT ', 'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE', 'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC', 'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ', 'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ', 'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ', 'UN', 'USER NAME ', 'WL', 'WRITE REDO ', 'TYPE='||l.type) type, decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', to_char(l.lmode) ) lmode, decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', to_char(l.request) ) lrequest, decode(l.type, 'MR', o.object_name, 'TD', o.object_name, 'TM', o.object_name, 'RW', 'FILE#='||substr(l.id1,1,3)|| ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2, 'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2, 'WL', 'REDO LOG FILE#='||l.id1, 'RT', 'THREAD='||l.id1, 'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'), 'ID1='||l.id1||' ID2='||l.id2) objname from sys.v_$lock l, dba_objects o where sid = s.sid and l.id1 = o.object_id(+) ) loop dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname); end loop; dbms_output.put_line('=====================================================================');
END;
/ undef SID
=====END===
|