自己管理データベース: 自動健全性監視および変更
Contents
1. 752. 7 FFF DT IF 4 CPU FIE DT IF 4 Feb vrd VO Real Application Clusters RAC
3. Q OWAE MAT D NEI I PEK S DE SHA TRILO TE TECT HHHHHHHHHH SQL ping ping 1
4. HHHHHHHHHH HHHHHHHHHHHH 7 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTINe HHHHHHHHHH Enterprise Manager 7 Enterprise Manager Console
5. ADDM SQL SQL Undo HHHHH S HHHHHHHHHH
6. HHHHHHHHHHHHHHHHH
7. FRISKE T h 85 97 Y BASY ROZE HIK KR BE UEFA GERE vrd SEL DL TW 3 LOIT kE NTOE T 2 BEnterprise Manager Oracle HHHHHHHHHHHH ALERT QUE DBA_OUTSTANDING_ALERTS DBA ALERT HISTORY v Wi
8. HHHHHHHHHH HHHHHHHHHHHH 4 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH BOEDE EET ED BOEDE ER ED Oracle Database10gHHHHHHHHHHHH Oracle Database10g OM Oracle Database10g
9. HHHHHHHHHHHHH Oracle Database10g T RDBMS MMON Ni Automatic Workload Repository AWR Automatic Database Diagnostic Monitor ADDM Active Sessions History ASH MMON Oracle Database10g
10. SGA HHHH BEUVER keH AWR
11. HHHHHHHHHH HHHHHHHHHHHH Paper 40169 1 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH HHHHH HHHHH
12. WO CPU OO ORACLE N Lane Enterprise Manager All Tee Kost dundu peace com gt 0Mansef mario 000805 dprd smed mn te Database mgmt10i 030806 dsunrdf03 Home Performance Rilimlhn bhertessce Cick an on aroa afa graph or legend 1o get mose detai View Data Rani Fme 15 Secard Reessh Host g 40 jiii ea i EE amp 2 0 e E Run Queue Length v 20 g 1 0 9 0 HM Pegng Rate 1210m 1220m 1230m 12 40pm 12 50pm August 11 2003 Active Sessions Waiting and Working 1 6 14 E User VO 1 2 E System 1 0 M Scheduler 1 0 M Cher 0 8 Network Cantiguration 0 6 oncurrency 0 4 Session Coumt E Application 0 2 B gt cminisrahve 9 0 E CPU Used 1210pm 1230m August 11 2005 s Instance Throughput HHHHHHHHHH HHHHHHHHHHHH 11 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTINeH HHHHHHHHHH O
13. Oracle Database10g Enterprise Manager T Oracle Database10g Oracle QoS EUL 1 Adams D The Ultimate Hitchhiker s Guide to the Galaxy Del Rey 2002 http www randomhouse com HHHHHHHHHH HHHHHHHHHHHH 17 Paper 40169 Oracle Corporation THE SELF MANAGING
14. 4WA UEGNET AAW SOL PKD NKKTT HHHHHHHHHH HHHHHHHHHHHH Paper 40169 5 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH ASH SQL AWR 30 ASH Oracle SGA HHHHH DBA
15. V SYSMETRIC V SYSMETRIC_HISTORY AWR DBA_HIST HHHHHHHHHH HHHHHHHHHHHH 8 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH mp e DBA THRESHOLDS e V ALERT TYPES Oracle Enterprise Manager 10g Database Control O0 0000 Enterprise Manager
16. 1 AWR 7 HHHHHHH 2
17. HHHHHHHHHHH ping Oracle SQL Enterprise Manager USER_WAIT_TIME_PCT O
18. Oracle Database1OglIIIHHHHIHHHHIHIHHHHHIHHLH Oracle Database10g Oracle HHHHHHHHHH HHHHHHHHHHHH 3 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH HHHHHHHHHHHHHHHHHHHHH
19. HHHHHHHHHH HHHHHHHHHHHH 6 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH ILHHHHHT DBA Server Components Space Management Recovery Advanced Server Queue Alert Undo Infrastructure Managment Metrics Monitoring MMON 00 MMON
20. SQL gt BEGIN DBMS_AOELM SET MATLHOST yourmailhost com DBMS_AOELM SET MATLPORT 25 DBMS_AOELM SET SENDE ROM anedoeG yourcompany Com COMMIT END PL SQL procedure successfully completed HHHHHHHHHH HHHHHHHHHHHH 15 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING 5 SOES D ALERT_QUE DBMS_AQ DEQUEUE OCIAQDeq ALERT QUE ECLARE HHHHHHHHHH 1 dequeue options message properties dbms aq dequeue options tj dbms aq message properties tj AL message message handle BEGIN dequeue options consumer name Never wa
21. SQL DELETE SOL WAIT_OBJECTS OBJECT_NAME f EM Database Console HHHHHHH Enterprise Manager 6 User Limit Usage gt co User Logon Time msec gt homo User Rollback Undo Records Applied per second gt LL User Rallback Undo Records Applied per transaction gt User Rollbacks per second gt ll I User Rollbacks per transaction gt Wait Time gt wo po da TE DE HHIHHHHHIHIIH PLSQL AP
22. Oracle System Global Area SGA Oracle SGA SQL HHHHHHHHHH HHHHHHHHHHHH 2 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH SGA SQL SQL SGA CPU Oracle C
23. LO SQL 5 ORACLE Sae retenas tew ue S Enterprise Manager Hooit ui us oracle core gt xss oni 030805 dimil gt ine Sessions Waiting User VO Lopgad is s system View Data Rea Tine 15 Second Retesh Active Sessions Waiting User 1 O 0 o i o M db fle wojuestisl read 0 E db fhe sostiered seed 0 E read by other sason ETP Dra ihe shofed bos 19 change thh Sina period for Lip disd section balor ss ii n n NES Overview Tab SGL Top Sessi Top SQL by Wait Count Top Sessions by Wait Count Teta Senplie Cosrt Tatal Sampha Court 4 Bitis D iais Ovarviow Ti T A Top Pilas Tap Otsecis P z Top Files By Wait Count Top Files By Average Walt Time Tetai Sampie Court amp Tot Samale Court 4 4 40 wo w 00 015 30 046 050 075 09 iw B Wat Court FH Artie Wit Te s 5 12 HHHHHHHHHH HHHHHHHHHHHH Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTINeH HHHHHHHHHH e SQL 3
24. ping SNMP EM EM a 99 E ADDM 2 oe ORACLE imo farierences lo Laat Enterprise Manager Hast
25. MMON Oracle SYS ALERT QUE MMON 1 2
26. Oracle SYS SYSTEM 1 ALERT QUE DBMS_AQADM DBMS_AQADM ADD_SUBSCRIBER ALERT QUE The second argument of the following procedure is the subscribe i e the agent on whose behalf the subscription has been defined AOS_AGENT ALERT USRI SQL gt exec DBMS_AOADM ADD_SUBSCRTBER queue name gt ALERT QUE AO AGENT ALERT USRI 0 PL SQL procedure successfully complete
27. ALERT QUE HTTP PL SQOL DBMS_AQ REGISTER ALERT_QUE AQ SQL gt DECLARE reginfo aq _reg_info reginfolist aq _reg_info_list BEGIN reginfo AQ _REG_INFO ALERT_QUE ALERT_USR1 DBMS AQ NAMESPACE AQ mailto janedoetyourcompany com NULL Create the registration info list reginfolist AOS REG INFO LIST reginfo Register the registration info list DBMS AQ REGISTER reginfolist 1 END PL SQL procedure successfully completed 4 EL Il HTTP 7a FOR HTTP DBMS_AQELM
28. MMON DBA Oracle Database10g Enterprise Manager AUTOMATIC WORKLOAD REPOSITORY AWRH AWR AWR
29. H DBA ILHHHHHHIHIHHIHHIHIHI Oracle Database10g ODDEN Werner Heisenberg 1901 1976 20 HHHH 1927 2 KF E EIIDKOMEE EIIE EMEL L I ETER WA
30. KTTCHEN 60 80 SQL gt exec DBMS SERVER ALERT SET THRESHOLD 9000 DBMS SERVER ALERT OPERATOR GE 60 DBMS SERVER ALERT OPERATOR GE 80 1 1 NULL DBMS SERVER ALERT OBJECT TYPE TABLESPACE KITCHEN HHHHHHHHHH HHHHHHHHHHHH 13 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH KITTCHEN 60 KITCHEN 80 e 9000 gt V METRICNAME METRIC_ID e UED NIRE DBMS_SERVER_ALERT OPERATOR_GE DBMS_SERVER_ALERT OPBRATOR_LT e
31. 60 e e 80 e 1 1 i SE e NULL e DBMS_ALERT OBJEBCT_TYPE_TABLESPACE e KITCHEN CT BORDE
32. e R RIL Eac VISARE e e HHHHHHHHHHHHHHHHHHHH o
33. Oracle OOCOROOCOMOOOO0ORDN 12 00 Tivo 1 e
34. Oracle Database10g t Oracle gg gse70g HEM EWA 72 S 8 7 HHHHHHHHHHHHH 0 1 SQL Oracle Database10g 10g
35. 1 yZ ZF R Tar MMON MMON MMON
36. CLO EM HHHHHH Oracle Database10g PL SQL DBMS_SERVER_ALERT PL SQL e GET THRESHOLD e SET THRESHOLD
37. DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH ORACLE HHHHHHHHHH HHHHHHHHHHHH Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores CA 94065 U S A HHHHHHHHHHH 1 650 506 7000 00000 1 650 506 7200 www oracle com oraceHHHHHHHHHHHHHH HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH Copyright 2004 Oracle Corporation All rights reserved HHHHHHHHHH HHHHHHHHHHHH 18 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING
38. drunndeG sm oracle com Database mg 10k 130806 desrrdCG Lagged mA Database mgmt 10i 030806 dsunrdfo3 Home Examaoce Simmon Masteoance 7 ESSEN Latest Dat i Target Aug 11 2003 12 96 15 AM Ratoin View Dara Foai Tne Monsa Refresh General Host CPU Active Sessions yA shufdown 1o mss Up i Ei 082 A Un Since Awg 3003 920 0 PM rs Tne Zose POT Boe ceu B Aahbimr Wi 109 amme 43 Re EA RE B eoma B cer liirwe Name manaii versien 10 18 40 9 Hest dsuredill3 us aracie cam hion meee Run uewe Acia 5695000 aLI Oracle Mome i Joras DY SEM Pogra pages per second SOL Response Tine 4 v 5217 Akt Leg Na ORA errors Garani m aira Space Usage Advice High Availabiity Problem Taldespaces v O ADOM Finding 3 lriice heecwwyY Te seconds W Fragmentation hsuws HL xrSaurxd Po ty Vielationa 2 LiBawctup wa Darmp Arsa Used CS v Arch ng keahlnd chww Aras Used S ma Flasatack Leggng Dsskiad 3 Wat Botronecks Nar Tinne 5 Te DD sluser wor time act is 99 Aug M 2003 11 4853 AM 5 Aag 11 X03 1204 53 PM A User Auda Abdted Usee User SYS logget sn kam kun Aug 7 2003 1 55 36 PM Aag 11 2003 1055 35 AM User Block Diochieg Session Com Sreo 0 2 boken 38857 10 other sogne Au 8 2003 30539 AM 9 D Respsnse Uas Lagon Time msec User logen orme ja 5579 62 maca Aug 11 2003 11 46 15 AM 3757 57 Aag 11 2003 120615 PM Related Alerts x auerdDG us Crache com Ho ga3 CPU Ultraton CPU Uzatan m 0 Asg 1
39. message id Teason argument 2 messag message reason argument 4 messag DBMS OUTPUT PUT LINE Sequence DBMS OUTPUT PUT LINE Reason I DBMS OUTPUT PUT LINE Object N DBMS OUTPUT PUT LINE Object T DBMS OUTPUT PUT LINE Instance DBMS OUTPUT PUT LINE Suggeste reason argument 5 Id j message sequence id ds message reason id ame o message object name ype i message object type Name 5 message instance name d action 1 DBMS SERVER _ ALERT EXPAND MESSAGE userenv LANGUAGE message suggested action msg id message action argument 1 message action argument 2 message action argument 3 message action argument 4 DBMS OUTPUT PUT LINE Advisor DBMS OUTPUT PUT LINE Scope END PL SQL procedure successfully comp Name message advisor name ii message scope leted Teason argument 3 message action argument 5 HHHHHHHHHH HHHHHHHHHHHH Paper 40169 16 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING The following output is formatted Alert message dequeued Timestamp Organization Id essage Type essage Group essage Level Host Id Reason Sequence Id Reason Id Object Name Object Type Instance Name Suggested action Advisor Name Scope Host Network Addr 08 AUG 03 13 43 15 392725 PM yourcompany com N
40. 12008 13 11417 300 Aug 11 2008 1207 11 AM PM x NOYION_dssrmd0d stwwer Listera wsgerrie Response Tinne aset Listener esacnie to a TNS ony n A20 mieca Aug 11 2003 120701 420 Aw 11 2003 1207 01 PM FM A E in us oral com Most F osysterme Fiesysiem Space Amdabo Fissysiem pnott has ooy 19 90 mwlk Aug 0 X090 1501 AM 1165 Aug 11 2009 11 1601 9 space AM ADDM Analysis Posed Stan Time 11 2009 11 30 28 AM Dueen fmtes 29 82 HHHHHHHHHH HHHHHHHHHHHH 9 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH b ADDM ADDM SQL XI DELETE DO 74 ADDM ADDM 30 3 99 2189 dd JOY or ALE sa Bomas ee
41. HHHHHHHHHH E Oracle Database10g Oracle Database10g 1 Oracle Database Enterprise Manager EM QoS qua
42. O REN HHHH Oracle Database10g Oracle Oracle Enterprise Manager 10g Database Control Home Page DBA Oracle Database10g
43. d The agent in this context is the subscribing user SQL gt exec DBMS AQADM CREATE AQ AGENT agent name gt ALERT USR1 PL SQL procedure successfully completed HHHHHHHHHH HHHHHHHHHHHH 14 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTING HHHHHHHHHH 2 ALERT QUE DBMS_AQADM ENABLE_DB_ACCBSS DBMS_ADQDM GRANT_QUEUE_PRIVILEGE SQL gt exec DBMS_AQADM ENABLE DB ACCESS agent name gt ALERT USRI db username gt SYSTEM PL SQL procedure successfully completed SQL gt exec DBMS AQADM GRANT QUEUE PRIVILEGE privilege gt DEQUEUE queue name gt ALERT QUE V grantee gt SYSTEM grant option gt FALSE PL SQL procedure successfully completed 3 WORE
44. it dequeue options wait DBMS AQ Always reset the position to dequeue options navigation Remove the message when don dequeue options dequeue mod set some filter condition dequeue options deg condition DBMS AQ DEQUEUE queue name gt dequeue options gt message properties gt payload gt msgid gt ERT TYPE RAW 16 ALERT USR1 NO WAIT the begining of the AQ DBMS AQ FIRST MESSAGE DBMS_AO REMOVE EJ tab user data sequence id gt 6 ALERT_QUE dequeue_options message_properti message message handle S The alert message dequeued fields in the message queue DBMS_OUTPUT PUT_LINE Alert mess DBMS OUTPUT PUT LINE Timestam DBMS OUTPUT PUT LINE Organiza DBMS OUTPUT PUT LINE Componen DBMS OUTPUT PUT LINE Message DBMS OUTPUT PUT LINE Message DBMS OUTPUT PUT LINE Message DBMS OUTPUT PUT LINE Host Id DBMS OUTPUT PUT LINE Host Net DBMS OUTPUT PUT LINE Reason DBMS SERVER ALERT EXPAND MESSAGE userenv LANGUAGE messag message reason argument 1 messag output presented below is a subset of the available age dequeued p i message timestamp originating tion Id X message organization_id t Id 1 message component_id Type g message message_type Group X message message group Level T message message_level 1 message host_id work Addr message host_nw_addr U
45. lity of service MINHMMMILMMIMMU EM 42 Adams 1 42 EDIZ Oracle DBA DBA
46. otification Configuration 32 acme 5559 59559 5595595 Threshold is updated on metrics Tablespace Space Usage 41 137 KITCHEN TABLESPACE instl Check DBA THRESHOLDS view to verify the result Default Advisor Database 07 00 PL SQL procedure successfully completed PL SQL EM EM LAHE FBE 6 HHHHHHHHHH ik J NA Ni NY DET He Fi MZ ff
47. tax Enterprise Manager tost dsun 4 03 us aracdle cam gt atabase mom 104 050800 dan di gt amp gszorCertrsj gt ALOIA Tass ADOM Finding Dotais Logged n As system ADDM Finding Details Pernod Siar ime Aeg 11 2000 1210 17 PH Database Tme ranesy 109 73 Duaon mtes 293 esc mney FAT Time spom en the CPU by tho instance was Fn asg fespomible for a sebstanmial pan of database Imfeet 3 563 time Recommendations Susil Details toie AL Dainis Viae SO Tuning 10 04 SCL Text DE 0 TE 7 i J Achion Hun Afisi Now 5 Pile 30 Tuning an 5104 S0 Tuning 372 Findings Path Q Tine spert on the CPU by he isatance was raspossibie for a substanial part of atase time 5 Database Time Breakdown For Analysis Period gt os BAdmrestratrve 0 B Appicatin 5 8 Cont 23 Concureoncy G 5 M Corrigunatien 0 Wwa 1 24 Othe 1 B Synem VO 6 9 Museo VO 74 28 ow Hame Tages Contgigion I Alerts hs Manogemart System Setup I Preferences Ip Lagast Copyrigtt 01796 2000 Oracie AI nijta reserved Skr Carit riuet Marens 3 HHHHHHHHHH HHHHHHHHHHHH 10 Paper 40169 Oracle Corporation THE SELF MANAGING DATABASE AUTOMATIC HEALTH MONITORING AND ALERTINe HHHHHHHHHH c 4
Download Pdf Manuals
Related Search