1 / 49

Oracle Administration and Monitoring Tools for Windows

Oracle Administration and Monitoring Tools for Windows. Administering and Monitoring Oracle with Windows Tools. Objectives. At the end of this module the student will understand the following tasks and concepts. Oracle GUI Administration Tools Oracle Services Configuration Event Viewer

july
Download Presentation

Oracle Administration and Monitoring Tools for Windows

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Administration and Monitoring Tools for Windows Administering and Monitoring Oracle with Windows Tools

  2. Objectives At the end of this module the student will understand the following tasks and concepts. • Oracle GUI Administration Tools • Oracle Services Configuration • Event Viewer • Task Manager • Oracle Administration Assistant for Windows • Windows Performance Monitor • Oracle Counters for Windows Performance Monitor • Oracle Command Line Administration Tools • Using SQLPlus for Administration • The ORADIM utility • Creating password files with orapwd • The ORADEBUG Utility • STATSPACK • Automatic Workload Repository Reports (10g) • Oracle Web Tools • Managing Oracle with Oracle 10g Grid Control

  3. Managing Oracle Services • Oracle Services can be accessed by Control Panel -> Administrative Tools -> Services • You can start, stop, pause and restart an Oracle Service. • Services can also be enabled or disabled. • It is also possible to run a custom program upon a Service failure event.

  4. Viewing Oracle Events • Oracle Events can be viewed through the Application Event Viewer. • The Event Viewer can be accessed by Control Panel -> Administrative Tools -> Event Viewer • Choose Application from the Log menu. • Oracle 10g problems and other significant events are recorded here. • A “drill-down” description of each event is available by double-clicking an individual event.

  5. The Task Manager Tool • The Task Manager Tool displays: • High-level summary of the concurrent applications and processes running • Information about process state • Graphical summary of CPU usage • Graphical summary of memory usage • Information about OS users • Task Manager can be used to change the priority of a process. • The Task Manager can be launched two ways: • Hit control-alt-escape • Right-click on the task bar at the bottom of the screen • The Performance tab contains useful information that directly impacts Oracle performance

  6. The Task Manager Tool – Oracle Performance • CPU Usage, as well as Memory and Page File Usage can be monitored with the Performance tab • Both CPU Usage and Memory and Page File Usage are critical for Oracle performance • CPU Usage should be consistently high, but not maxed out • You should not consistently see higher memory usage than the physical RAM • Neither should you see high Page File Usage or a steadily climbing rate of Page File Usage

  7. The System Monitor • Launching Perfmon • Start  Programs  Administrative Tools  Performance

  8. System Performance Monitor • Performance Objects • Counters • Explain Text

  9. Performance Logs and Alerts • Right click on Counter Logs, Trace Logs, or alerts to set up • Alerts require you to set limits for counter values • Creating a performance log file with System Monitor • Comma-separated format or tab-separated format • Use meaningful collection intervals (> 1 minute)

  10. Administration Assistant for Windows • Startup from Start -> Oracle -Orahome92 -> Configuration and Migration Tools -> Administration Assistant for Windows • Maneuver to the Database object • Right click to login • You can now startup and shutdown Oracle, as well as several other administrative functions. • Right click and choose Process Information • This gives you a way to kill Oracle threads, if necessary.

  11. Oracle Counters for Windows Performance Monitor • To initially configure the Oracle Counters for Windows Performance Monitor, open a command line window • Enter the following: OPerfCfg –U system –P {password} –D {SID} • The registry entries for the Performance Monitor are now set. • Note: Oracle Counters for Performance Monitor is sensitive to Oracle Net settings, which must be correct

  12. Oracle Counters for Windows Performance Monitor • Oracle Counters for Windows Performance Manager is available as a stand-alone application, or as add-in performance counters to Perfmon • Startup from Start -> Oracle -Orahome92 -> Configuration and Migration Tools -> Oracle Counters for Windows Performance Monitor • By default, two Oracle counters for Sorts are displayed.

  13. Oracle Counters for Windows Performance Monitor • To display alternate counters, right-click in the window and choose Properties • Click on the Performance Object menu. A list of Oracle counters is displayed. • Use the Add and Remove buttons to modify the display. • For example, to monitor database I/O, add the following counters: • physreads/sec • physwrites/sec

  14. Managing Oracle with SQL Plus • Startup SQL Plus from Start -> Oracle -Orahome92 -> Application Development -> SQL Plus • Log in as the SYSTEM user. • You can then use connect / as sysdba to login with full privileges (on the console) • You can now startup and shutdown Oracle, as well as any other administrative function.

  15. The ORADIM Utility • The ORADIM utility is a command line tool that can be used to manually create, delete or modify databases • To get help, enter oradim -? | -H | -HELP • To create an instance called PROD, for example, you could enter: C:\> oradim -NEW -SID prod -INTPWD mypassword1 -STARTMODE auto -PFILE C:\oracle\admin\prod\pfile\init.ora • To start an instance called PROD, for example, you could enter: C:\> oradim -STARTUP -SID prod -STARTTYPE inst -PFILE C:\oracle\admin\prod\pfile\init.ora

  16. The ORADIM Utility • To stop an instance called PROD, for example, you could enter: C:\> oradim -SHUTDOWN -SID prod -SHUTTYPE srvc, inst • To change an instance name from prod to lynx, set a new instance password mycat123, and specify a new initialization parameter file, for example, you could enter: C:\> oradim -EDIT -SID prod -NEWSID lynx -INTPWD mycat123 -STARTMODE a –PFILE C:\oracle\admin\lynx\pfile\init.ora • To delete an instance called prod, for example, you might enter: C:\> oradim -DELETE -SID prod

  17. Creating an Oracle Password File • To establish a secure local logon user and password, you need to create a password file • It can also be used for remote access to the database for selected users • To start, open a command line session and cd C:\oracle\ora92\dbs • Enter the following: orapwd file=ORACLE_SIDpwd password={your password} entries=5 (the maximum number of open admin connections) • A password file is now created

  18. The Oracle ORADEBUG Utility • The ORADEBUG utility is a debugging tool that sends debug commands through SQL*Plus to an Oracle process. • To start the ORADEBUG utility: • Start SQL*Plus from the command prompt:C:\> sqlplus / NOLOG • Connect to Oracle9i database as SYSDBA:SQL> CONNECT / AS SYSDBA • Enter the following at the SQL*Plus prompt:SQL> ORADEBUG ORADEBUG • The utility runs and prompts you for parameters.

  19. The Oracle ORADEBUG Utility • To obtain a list of ORADEBUG parameters, enter the following: SQL> ORADEBUG HELP • Output from most debug commands is written to trace files in the BACKGROUND_DUMP_DEST and USER_DUMP_DEST directories. • To find the location of your trace file, enter the following at the SQL*Plus prompt: SQL> ORADEBUG TRACEFILE_NAME • If output is more than one line, then the result is sent to a trace file. • If a debug command produces only one line of output, then the output is relayed directly to SQL*Plus.

  20. Statspack • Standard Performance Package • Similar to original BSTAT/ESTAT • Install @?\rdbms\admin\spcreate • Run SQL> CONNECT perfstat/perfstat SQL> EXECUTE statspack.snap; • Create the report with @spreport • Tell it which snapshots to use. • A report is created. • Report contains a wealth of diagnostic information and predictive tools

  21. Statspack Output Summary • Statspack is divided into several sections including: • Summary information • RAC statistics (if applicable) • Wait events • Top resource consumers • Instance activity • Cache statistics • Rollback and Undo statistics • SGA and Shared Pool statistics • Initialization parameters

  22. Automatic Workload Repository Features • Statspack on Steroids (10g only) • Collects stats every 60 minutes • Kept for a week then purged • A collection of performance stats • A new background process MMON • Resides in new sysaux tablespace

  23. Automatic Workload Repository Reports • Two reports provided by Oracle • awrrpt.sql • awrrpti.sql • Similar to Statspack report • Reports can be generated with scripts or through a GUI interface • Optional HTML or plain text formats

  24. Administering Oracle and Monitoring Performance with Oracle 10g Grid Control • Oracle Grid Control is the enhanced version of OEM that is installed with Oracle 10g • Requires a central Management Repository and Agents on each database server • Enhancements for managing and deploying RAC nodes and application grid nodes • Significant improvements in automatic performance monitoring and analysis • Central interface for administering and monitoring Oracle 10g • Perform all Administration tasks • Contains a variety of Advisors for improving performance

  25. Oracle Grid Control Performance Page • The database Performance page gives a quick look at performance trends • Host CPU • Average Active Sessions • Instance Disk I/O • Instance Throughput

  26. AWR Reports • To create an AWR report: • On the Database Administration page, select the Automatic Workload Repository link under Statistics Management. The Automatic Workload Repository page appears. • Under Manage Snapshots and Preserved Snapshot Sets, click Snapshots link. The Snapshots page appears. • Under Select Beginning Snapshot, select the start point for the range of snapshots that will be included in the report. • From the Actions pull-down menu, select View Report and click Go. The View Report page appears. • Under Select Ending Snapshot, select the end point for the range of snapshots and click OK. The Processing: View Report page appears while the report is being generated. • Once completed, the Snapshot Details page appears and the report will be displayed. • To save the report as an HTML file, click Save to File.

  27. AWR Reports

  28. Using the Segment Space Advisor • The Oracle 10g Segment Advisor helps you determine whether an object has space available for reclamation. • Available in 10g OEM or Grid Control • The Segment Advisor can generate advice at three levels: • Object level - advice is generated for the entire object, such as a table. Advice does not cascade to dependent objects. • Segment level, - advice is generated for a single segment, such as unpartitioned table, a partition or subpartition of a partitioned table, or an index or LOB column. • Tablespace level - advice is generated for every segment in the tablespace.

  29. Using the Segment Space Advisor

  30. Using the Segment Space Advisor

  31. SQL Tuning Advisor • 10g Oracle Enterprise Manager (DB Console, Grid Control) offers the SQL Tuning Advisor as a SQL statement tuning tool. • Accessed from Advisor Central • Select one or more SQL statements, or generate an SQL Tuning Set • View automatically generated Recommendations • View Explain Plan output

  32. SQL Tuning Advisor Recommendations

  33. SQL Tuning Advisor Explain Plan

  34. Using the SQLAccess Advisor • The SQLAccess Advisor provides an alternative to manually determining which indexes are required. • The SQLAccess advisor may be invoked from Advisor Central in OEM or run with the procedures in the DBMS_ADVISOR package. • For a chosen schema, the SQLAccess Advisor either recommends using a workload or it can generate a hypothetical workload. • Given a workload, the SQLAccess Advisor generates a set of recommendations from which you can select indexes to be implemented. • SQLAccess Advisor generates an implementation script that can be executed manually or through OEM.

  35. Using the SQLAccess Advisor • The SQLAccess Advisor provides an alternative to manually determining which indexes are required. • The SQLAccess advisor may be invoked from Advisor Central in OEM or run with the procedures in the DBMS_ADVISOR package. • For a chosen schema, the SQLAccess Advisor either recommends using a workload or it can generate a hypothetical workload. • Given a workload, the SQLAccess Advisor generates a set of recommendations from which you can select indexes to be implemented. • SQLAccess Advisor generates an implementation script that can be executed manually or through OEM.

  36. Using the SQLAccess Advisor

  37. Automatic Database Diagnostic Monitor • ADDM considers the following to make performance tuning recommendations: • CPU bottlenecks • Undersized Memory Structures • I/O capacity issues • High load SQL statements • High load PL/SQL execution and compilation • High load Java usage • RAC specific issues: • Global cache hot blocks and objects • Iinterconnect latency issues

  38. Automatic Database Diagnostic Monitor (cont.) • ADDM considers the following to make recommendations: • Sub-optimal use of Oracle by the application: • Poor connection management • Excessive parsing • Application level lock contention • Database configuration issues: • Incorrect sizing of log files • Archiving issues, excessive checkpoints • Sub-optimal parameter settings • Concurrency issues - Are there buffer busy problems? • Hot objects and top SQL for various problem areas

  39. ADDM Recommended Solutions • Hardware changes • Adding CPUs • Changing the I/O subsystem configuration • Database configuration • Changing initialization parameter settings • Schema changes • Hash partitioning a table or index • Using automatic segment-space management (ASSM) • Application changes • Using the cache option for sequences • Using bind variables • Using other advisors • Running the SQL Tuning Advisor on high load SQL • Running the Segment Advisor on hot objects

  40. Viewing Current ADDM Findings • You can view ADDM findings based on the latest analysis period within OEM • Go to the Database Home page • View the Diagnostic Summary to see a list of ADDM findings • Click on a finding link to see details

  41. ADDM Findings in OEM

  42. Running ADDM Manually • From the Database Home page • Under Related Links, click the Advisor Central link • On the ADDM page, under Advisors, click the ADDM link • Select Run ADDM to analyze past instance performance • Choose the Period Start Time (choose starting snapshot) • Choose the Period End Time (default is last snapshot) • Click OK to start analysis

  43. Running ADDM in OEM

  44. Scheduling Jobs in Oracle 10g • Oracle 10g contains a new Scheduler for scheduling and tracking jobs. • One way to access the Scheduler is through the Grid Control Job Activity page. From this page you can: • Search for existing job runs and job executions • Restrict the search by name, owner, status, scheduled start, job type, target type, and target name. • Create a job • View, edit, create like, suspend, resume, stop, and delete a run • View, edit, create like, suspend, resume, retry, stop, and delete an execution

  45. RAC Cluster Cache Coherency • Heavy concurrent read and write activity on shared data in a cluster can cause performance problems. • The Cluster Cache Coherency page enables you to • View cache coherency metrics for the entire cluster database • Identify processing trends • Optimize performance for your Real Application Clusters environment

  46. Cluster Interconnects • The Cluster Interconnects page enables you to view the current state of interfaces on hosts • You can use this page to • Monitor the interconnect interfaces • Determine configuration issues • Identify transfer rate-related issues, such as excess traffic. • This page helps determine the load added by individual instances and databases on the interconnect. • Sometimes, you can immediately identify interconnect delays due to applications outside the Oracle Database.

  47. Conclusions • Oracle GUI Administration Tools • Oracle Services Configuration • Event Viewer • Oracle Administration Assistant for Windows • Windows Performance Monitor • Oracle for Windows Performance Monitor • Oracle Command Line Administration Tools • Using SQLPlus for Administration • The oradim utility • Creating password files with orapwd • The ORADEBUG Utility • Statspack • AWR • Oracle Web Tools • Managing Oracle with Oracle 10g Grid Control

  48. Review • How could you customize an Oracle Service? • What command is used to create an Oracle password file? • How must you log in to SQLPlus in order to stop and start the database? • Where can you go if you need to kill Oracle threads? • What Advisors can you launch from Oracle Grid Control to help monitor performance?

  49. Summary In this training module the following tasks and concepts were covered: • Managing Oracle Services on Windows • Creating a password file with orapwd • Managing a database with SQLPlus • Managing a database with Administration Assistant for NT • Managing and Monitoring a database with Oracle Enterprise Manager

More Related