본문 바로가기

[DATABASE] ORACLE/Monitoring9

[Oracle] Active Session 모니터링 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 -- ########################################################### -- ACTIVE SESSION 모니터링 -- -- ########################################################### SELECT b.username , status , b.machine , b.osuser , b.module , b.sql_id , sql_child_number AS child , (SELECT spid FROM v$process WHERE.. 2022. 10. 14.
[Oracle] Lock 모니터링 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 -- ########################################################### -- Lock Waing Session 점검 -- -- 대용량 시스템 오라클 Lock 모니터링 및 Lock Holding Session Kill -- ########################################################### SELECT l.inst_id , DECODE(s.blocking_session,'',s.sid,'') AS sid , DECODE(s.bloc.. 2022. 10. 14.
Alert log monitoring SQL Oracle Alert log monitoring SQL & 조회가 느릴 때 대처 방안 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -- *********************************** -- 1. Alert Log 조회 -- *********************************** SELECT originating_timestamp , component_id , host_id , module_id , message_text FROM v$diag_alert_ext WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY AND REGEXP_LIKE(messa.. 2022. 4. 7.
Tablespace 사용량 모니터링 오라클 Tablespace 사용량 모니터링 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 SELECT a.tablespace_name AS tbs_name , ROUND(NVL(b.bytes/1024/1024/1024,0),2) AS tbs_alloc , ROUND(NVL(b.bytes - NVL(c.bytes,0),0)/1024/1024/1024,2) AS data_used , ROUND(NVL(c.bytes/1024/1024/1024,0),2) AS tbs_free , ROUND(NVL((b.bytes - NVL(c.bytes,0))/b.bytes*100,0),2) AS data_used_per FROM sys.dba_tab.. 2022. 1. 17.
[Oracle] 작업 진행 상황 모니터링 오라클 작업 (쿼리수행, 백업, 리스토어, 기타 작업)에 대한 진행상황 모니터링 수행 방법 How to monitor the progress of Oracle tasks (query execution, backup, restore, and other tasks) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 SELECT b.last_call_et , a.sid , b.serial# , (SELECT spid FROM v$process WHERE addr = b.paddr) spid , b.sql_id , l.elapsed_sec.. 2022. 1. 7.
오라클 무료 모니터링 툴 (oratop) Oracle 12c 부터 지원되는 무료 모니터링 툴 (수행 방법) cd $ORACLE_HOME/suptools ./oratop / as sysdba 이후 h(elp) key 누르고 기능 확인!! 2022. 1. 4.
실시간 수행중인 Query 바인드 변수 확인 --실행중인 쿼리 바인드변수 확인 (3초이상) 1234567891011121314151617SELECT sid , LEVEL AS bind_val, REPLACE(SUBSTR(REPLACE(SUBSTR(data,INSTR(data,'|',1,LEVEL),DECODE(INSTR(data,'|',1,LEVEL+1),0,128,INSTR(data,'|',1,LEVEL+1) - INSTR(data,'|',1,LEVEL))),'|'),instr(REPLACE(SUBSTR(data,INSTR(data,'|',1,LEVEL),DECODE(INSTR(data,'|',1,LEVEL+1),0,128,INSTR(data,'|',1,LEVEL+1) - INSTR(data,'|',1,LEVEL))),'|'),'>')+1),''.. 2021. 12. 3.
Alert Log 쿼리 조회 # Orange로 Alert log 확인하기 v$diag_alert_ext 테이블 조회 1 2 3 4 5 6 7 SELECT originating_timestamp, message_text "error" FROM v$diag_alert_ext WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' HOUR AND message_text LIKE '%ORA-%' AND component_id = 'rdbms' ORDER BY originating_timestamp ; Colored by Color Scripter cs 2021. 12. 2.
OS Watcher (oswbb) 설치 # OS Watcher 구성 OSWatcher (Includes: [Video]) (문서 ID 301137.1) 1. 압축 풀기 $ tar -xvf oswbb840.tar 2. oracle profile 수정 export OSWBB_ARCHIVE_DEST=/oracle/oswbb/archive 3. private.net 파일 수정 cd /oracle/oswbb cp Exampleprivate.net private.net vi private.net ###################################################################### #AIX Example ##############################################################.. 2021. 12. 2.

티스토리툴바