Linux Basics by dffhrtcv3

VIEWS: 1 PAGES: 49

									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
       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
   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
   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
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
   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

   Launching Perfmon
       Start  Programs  Administrative Tools 
        Performance
System Performance Monitor

   Performance
    Objects
   Counters
   Explain Text
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
   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
   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 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 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
   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
   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 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
   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
   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
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
   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
   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
   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
   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
   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
   The database
    Performance
    page gives a
    quick look at
    performance
    trends
       Host CPU
       Average Active
        Sessions
       Instance Disk I/O
       Instance
        Throughput
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.
AWR Reports
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
Advisor
Using the Segment Space
Advisor
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
SQL Tuning Advisor Explain
Plan
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
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
Advisor
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.)
   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
   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
   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
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
    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
     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

   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
   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
   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
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

								
To top