BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Monitoring Events

Oracle Tips by Burleson Consulting

As you know, Oracle is an event-driven system. Sessions wait for calls; locks and latches spin; processes wake up and go back to sleep—all based on events. The V$SESSION_ EVENT DPT tracks all current events by session. The report in Source 11.40 will generate a report on current events (Oracle-wise anyway). An example of the event report is shown in Listing 11.34.

SOURCE 11.40 Script to generate an event report.

rem
rem FUNCTION: Generate a report on session events by user
rem
rem NAME:events.sql
rem HISTORY: MRA 6/15/97 Created
rem          MRA 5/22/99 Verified on 8i
rem
COLUMN sid                 HEADING Sid
COLUMN event               HEADING Event            FORMAT a40
COLUMN total_waits         HEADING Total|Waits
COLUMN total_timeouts      HEADING Total|Timeouts
COLUMN time_waited         HEADING Time|Waited
COLUMN average_wait        HEADING Average|Wait
COLUMN username            HEADING User
BREAK ON username
START title132 "Session Events By User"
SPOOL rep_out\&db\events
SET LINES 132 PAGES 59 VERIFY OFF FEEDBACK OFF
SELECT
     username, event,total_waits,total_timeouts,
     time_waited,average_wait
FROM
     sys.v_$session_event a,
     sys.v_$session b
WHERE
     a.sid= b.sid
ORDER BY 1;
SPOOL OFF
PAUSE Press Enter to continue
CLEAR COLUMNS
CLEAR BREAKS
SET LINES 80 PAGES 22 VERIFY ON FEEDBACK ON
TTITLE OFF

LISTING 11.34 Example of output from the session events report.

Date: 10/14/01                                                             Page:   1
Time: 05:37 PM                  Session Events By User                     SYS
                                   galinux1 database 

                                              Total     Total      Time   Average
User       Event                              Waits  Timeouts    Waited      Wait
---------- ------------------------------ --------- --------- --------- ---------
SYSTEM     enqueue                              149       149     44425       298
           control file sequential read         214         0        14         0
           log file sync                         61         0        65         1
           SQL*Net message to client            462         0         1         0
           single-task message                    2         0         5         2
           SQL*Net break/reset to client          4         0         0         0
           SQL*Net message from client          800         0    735778       920
           SQL*Net message to client             19         0         0         0
           db file sequential read            24987         0     10986         0
           db file sequential read               16         0        10         1
           pmon timer                        167133    167130  49031711       293
           rdbms ipc message                 163764    163681  49050112       300
           control file sequential read          20         0         2         0
           control file parallel write       163663         0     75411         0
           direct path read                      14         0         0         0
           db file parallel write              3300      3300         8         0
           db file scattered read               596         0       750         1
           db file sequential read              248         0       232         1
           log file parallel write             3760         0      1168         0
           log file single write                  4         0         0         0
           log file sequential read               4         0         2         1
           library cache load lock                1         1       299       299
           smon timer                          1640      1634  47313312     28850
           direct path write                     12         0         0         0
           direct path read                      14         0         0         0
           control file parallel write            8         0         4         0
           control file sequential read       49110         0      1069         0
           async disk IO                          1         0         0         0
           rdbms ipc message                 166085    163858  49051891       295

Press Enter to continue   

Workspaces in Oracle9i

Orcacle9i introduced the concept of a database workspace. A workspace is an environment for a long-term transaction that allows versioning of objects. A workspace can be shared among multiple users. The concept of workspace management involves a series of short transactions and multiple data versions to implement a complete long-transaction event that maintains atomicity and concurrency.

The Workspace Manager (WKS) is installed by default in all seed and DBCA databases. (If you need it in a manually created database you must install it according with the installation guide in the Oracle9i Application Developers Guide--Workspace Manager, Release 1 9.0.1, PART# A88806-01, Oracle Corporation, June 2001.)

Workspaces are monitored using the DBA_ view cluster associated with workspaces shown in Figure 11.9. OEM also provides a Workspace Manager interface accessible from the database listing of the main GUI.

Figure 11.9 DBA_ view cluster for workspaces.

An example report to show workspace status is shown in Source 11.41. An example of the workspace status script output is shown in Listing 11.35.

SOURCE 11.41 Example workspace status script.

rem workspace_status.sql
rem Function: Show status of workspaces in database
rem
rem History: MRA 10/14/2001 Initial Creation
rem
COLUMN WORKSPACE FORMAT a10 HEADING 'Workspace'
COLUMN owner FORMAT a10 HEADING 'Owner'
COLUMN freeze_status FORMAT a8 HEADING 'Freeze|Status'
COLUMN resolve_status FORMAT a8 HEADING 'Resolve|Status'
COLUMN parent_workspace FORMAT a10 HEADING 'Parent|Workspace'
COLUMN freeze_mode FORMAT a8 HEADING 'Freeze|Mode'
start title80 'Workspace Status'
spool rep_out\&db\workspace_status
select
  workspace,
  NVL(parent_workspace,'NONE') parent_workspace,
  owner,
  freeze_status,
  NVL(freeze_mode,'NONE') freeze_mode,
  resolve_status
from
  DBA_workspaces
/
spool off
ttitle off

LISTING 11.35 Example output from workspace status report.

Date: 10/14/01                                              Page:   1
Time: 06:25 PM                Workspace Status              SYS
                              galinux1 database 

           Parent                Freeze   Freeze   Resolve
Workspace  Workspace  Owner      Status   Mode     Status
---------- ---------- ---------- -------- -------- --------
LIVE       NONE       SYS        UNFROZEN NONE     INACTIVE

Other items, such as workspace privileges and savepoints, can also be monitored using the DBA_ series of views.

Monitoring Other Database Objects

Generally speaking, the “other” database objects consist of stored objects such as packages, procedures, and functions. About the only thing for which these stored objects can be monitored (other than tuning stats, which we cover in the next chapter) is status, that is, whether they are either valid or invalid.

Monitoring for Invalid Objects

The script in Source 11.42 generates a listing of invalid database objects. If the object is valid, you don’t need to monitor it! Sample output from the object-monitoring script is shown in Listing 11.36.

SOURCE 11.42 Example of script to report object status.

rem Name: inv_obj.sql
rem Purpose: Show all invalid objects in database
rem Mike Ault 7/2/96
rem Mike Ault 10/14/01 verified for Oracle9i
rem
COLUMN object_name      FORMAT A20      HEADING 'Object|Name'
COLUMN owner            FORMAT a10      HEADING 'Object|Owner'
COLUMN last_time        FORMAT a18      HEADING 'Last Change|Date'
COLUMN object_type      FORMAT a15      Heading 'Object|Type'
SET LINES 80 FEEDBACK OFF PAGES 0 VERIFY OFF
START title80 'Invalid Database Objects'
SPOOL rep_out/&db/inv_obj
SELECT
     owner,
     object_name,
     object_type,
     TO_CHAR(last_ddl_time,'DD-MON-YY hh:mi:ss') Last_time
FROM
     DBA_objects
WHERE
     status='INVALID'
/
PAUSE Press Enter to continue
SET LINES 80 FEEDBACK ON PAGES 22 VERIFY ON
CLEAR COLUMNS
TTITLE OFF
      

LISTING 11.36 Example of output from invalid objects script.

Date: 10/14/01                                        Page:   1
Time: 06:39 PM            Invalid Database Objects          SYS
                              galinux1 databa

Object     Object               Object          Last Change
Owner      Name                 Type            Date
---------- -------------------- --------------- ------------------
SYS        RECREATE_DB          PROCEDURE       23-AUG-01 01:40:59
WKSYS      WK_CRW               PACKAGE BODY    06-JUN-01 12:54:24

Press Enter to continue

If your search reveals that you have invalid objects, then a script similar to that in Source 11.43 can be executed to recompile the objects. The utlrp.sql script, found in the $ORACLE_HOME/rdbms/admin directory, can be used to recompile all invalid PL/SQL objects at any time; there is also the dbms_utility.compile_schema procedure.

SOURCE 11.43 Example of dynamic SQL script to recompile invalid objects.

rem Name: com_proc.sql
rem Function: Create a compile list for invalid procedures
rem
rem MRA 5/1/96
rem
DEFINE cr='chr(10)'
SET HEADING OFF PAGES 0 ECHO OFF TERMOUT OFF FEEDBACK OFF VERIFY OFF
SPOOL recompile.sql
SELECT 'ALTER '||object_type||' '||object_name||' COMPILE;'||&&cr||
'SHOW ERROR'
FROM DBA_objects WHERE status='INVALID'
/
SPOOL OFF
SET HEADING ON TERMOUT ON FEEDBACK ON VERIFY ON
UNDEF cr

Monitoring Multiple Databases

Frequently today, DBAs are called upon to manage multiple instances. In this era of corporate downsizing, what was a manageable job a few years ago has rapidly descended into the chaos of a Doom episode gone wrong, as more responsibility is being placed on fewer and fewer people. Thus, it’s imperative for DBAs to automate the management of multiple databases, especially if they are physically remote.

To this end, a DBA has several options. If your budget permits (the tools seem to get more expensive as the hardware decreases in cost), purchase a good monitoring tool, such as Patrol by BMC, or Eco-Tools, as well as a “point-and-shoot” monitor, such as Q by Savant Corporation, Platinum Technologies’ offerings, or any of the plethora of new tools that seem to spring up each year. If the budget is not there for purchasing tools or, like me, you are a bit of a masochist, you may want to develop your own tools based on the scripts in this book.

To develop your own tools, you must first decide what you want to monitor. Once you have, in a central monitoring database, design and install a small set of database tables to store the data, with an identifier and date stamp for each entry. On each of the remote databases, establish a monitoring user that has select privileges against all required tables. Next, establish a database link from the remote databases to the central monitoring instance. On the central monitoring instance, establish a set of monitoring users with a set of synonyms that point to the remote database tables. Finally, set up a group of monitoring jobs that execute the monitoring scripts via the database links and synonyms against the remote databases, and store the results in the results tables. Once the tables are loaded, you can report against them. Procedurized, the steps are:

1. Establish a user on the remote database that has select privilege on the DBA and V$ views and tables you want to monitor.

2. On the central or monitoring database, establish a corresponding monitoring user that has a database link to the remote database monitoring user.

3. Create synonyms that hide the required database link syntax for a select from the monitoring remote user; for example:

       CREATE SYNONYM galinux_sessions FOR v$session@galinux;

4. Once the synonyms are in place in the monitoring database, create a central repository user that can be used to store statistics (this will depend on what you want to trend;  look at the DBA_RUNNING_STATS definition in the DBA_UTILITIES zip file on the Wiley Web site).

5. Use procedures to collect and store information in the central repository for each monitored instance.

6. Generate reports from of the central repository.

 The Oracle Enterprise Manager enables remote monitoring for multiple instances.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter