Oracle administration and monitoring tools for windows l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 49

Oracle Administration and Monitoring Tools for Windows PowerPoint PPT Presentation


  • 535 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Oracle administration and monitoring tools for windows l.jpg

Oracle Administration and Monitoring Tools for Windows

Administering and Monitoring Oracle with Windows Tools


Objectives l.jpg

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


Managing oracle services l.jpg

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.


Viewing oracle events l.jpg

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.


The task manager tool l.jpg

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


The task manager tool oracle performance l.jpg

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


The system monitor l.jpg

The System Monitor

  • Launching Perfmon

    • Start  Programs  Administrative Tools  Performance


System performance monitor l.jpg

System Performance Monitor

  • Performance Objects

  • Counters

  • Explain Text


Performance logs and alerts l.jpg

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)


Administration assistant for windows l.jpg

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.


Oracle counters for windows performance monitor l.jpg

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


Oracle counters for windows performance monitor12 l.jpg

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.


Oracle counters for windows performance monitor13 l.jpg

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


Managing oracle with sql plus l.jpg

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.


The oradim utility l.jpg

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


The oradim utility16 l.jpg

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


Creating an oracle password file l.jpg

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


The oracle oradebug utility l.jpg

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.


The oracle oradebug utility19 l.jpg

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.


Statspack l.jpg

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


Statspack output summary l.jpg

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


Automatic workload repository features l.jpg

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


Automatic workload repository reports l.jpg

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


Administering oracle and monitoring performance with oracle 10g grid control l.jpg

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


Oracle grid control performance page l.jpg

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


Awr reports l.jpg

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.


Awr reports27 l.jpg

AWR Reports


Using the segment space advisor l.jpg

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.


Using the segment space advisor29 l.jpg

Using the Segment Space Advisor


Using the segment space advisor30 l.jpg

Using the Segment Space Advisor


Sql tuning advisor l.jpg

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


Sql tuning advisor recommendations l.jpg

SQL Tuning Advisor Recommendations


Sql tuning advisor explain plan l.jpg

SQL Tuning Advisor Explain Plan


Using the sqlaccess advisor l.jpg

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.


Using the sqlaccess advisor35 l.jpg

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.


Using the sqlaccess advisor36 l.jpg

Using the SQLAccess Advisor


Automatic database diagnostic monitor l.jpg

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


Automatic database diagnostic monitor cont l.jpg

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


Addm recommended solutions l.jpg

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


Viewing current addm findings l.jpg

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


Addm findings in oem l.jpg

ADDM Findings in OEM


Running addm manually l.jpg

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


Running addm in oem l.jpg

Running ADDM in OEM


Scheduling jobs in oracle 10g l.jpg

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


Rac cluster cache coherency l.jpg

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


Cluster interconnects l.jpg

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.


Conclusions l.jpg

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


Review l.jpg

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?


Summary l.jpg

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


  • Login