How to know if STATSPACK is installed

 

Statspack is important tool for dba to investigate performance issue in oracle database, it makes DBA's life easy in performance issues cases. Most of the time, statspack is not installed automatically unless it is specified in custom script prepared from database creation.

The question is how to check if statspack is installed or not? There are number of ways to detect that, we will discuss few here

 

1.  PERFSTAT user

statspack uses PERFSTAT user to perform its operation, so check in DBA_USERS whether user is present of not using select * from dba_users where USERNAME='PERFSTAT';

2. Presence of table STATS$DATABASE_INSTANCE

statspack generates snap id's and save information in table STATS$DATABASE_INSTANCE, and table is created during installation of statspack, check using desc STATS$DATABASE_INSTANCE if table is present, if present then statspack is installed otherwise not

3. Running report using spreport.sql

For DBA's convenience, there are number of scripts provided by oracle in admin folder under oracle home directory, spreport.sql is such script used to generate statspack report , try running this sql file, if it asks for begin snap id and end snap id then assume statspack is installed otherwise it will throw error.

 

 

Read More

Oracle : The oerr Utility (Oracle Error)

• The oerr utility (oracle error) is provided only with Oracle Database on UNIX platform
• oerr is not an executable, but instead a shell script that retrieves messages from installed message files
• oerr is not provided on windows, since it uses “awk” commands to retrieve the requested text from the file

Syntax

oerr

where

facility is prefix to error number, this includes ORA, PLS, EXP etc.

The error is actual error number

For example

$ oerr ora 01652

01652, 00000, "unable to extend temp segment by %s in 

tablespace %s"

// *Cause:  Failed to allocate an extent of the required number of blocks for

//          a temporary segment in the tablespace indicated.

// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more

//          files to the tablespace indicated.

$ oerr EXP 00008

00008, 00000, "ORACLE error %lu encountered"

// *Cause:  Export encountered the referenced Oracle error.

// *Action: Look up the Oracle message in the ORA message chapters of this

//          manual and take appropriate action.

Output shows “”Cause” of the error and “Action” recommended.

How oerr works

• To best understand how oerr works, you can review the shell script oerr.ksh available at $ORACLE_HOME/bin
• The commands in the oerr.ksh file confirms $ORACLE_HOME is set and if not utility will terminate.
• Facility information is read from facility file located in $ORACLE_HOME/lib/facility.lis. below is some portion of facility.lis file

aud:ord:*:

amd:cwmlite:*:

av:pfs:*:

bbed:rdbms:*:

brw:browser:*:

clsr:racg:*:

ds:office:*:

dsp:office:*:

dsw:office:*:

dsz:office:*:

ebu:obackup:*:

evm:evm:*:

exp:rdbms:*:

fmc:forms40:*:

iac:forms:*:

iad:forms:*:

lcd:rdbms:*:

oao:office:*:

obk:obackup:ebu:

omv:office:*:

opw:rdbms:*:

ora:rdbms:*:

osn:network:*:

osnq:network:*:

Facility file contains three mandatory data items and one optional data item

Mandatory

•   Facility
•  Component
•  Name of alias for component if any, otherwise a * will be used

In above file facility.lis ora is facility, rdbms is component and it does not have alias so *

Optional

•   Description

Using the facility name provided on command line, oerr retrieves the component for that facility

For example 

oerr ora 01652, oerr uses rdbms component, using this information, appropriate message file can be retrieved

Msg_File=$ORACLE_HOME/$Component/mesg/$(Facility)us.msg

for ORA errors

Msg_File=$ORACLE_HOME/rdbms/mesg/oraus.msg

Once this path and file is retrieved the content of the will be provided with cause and action for each error in facility

oerr on Windows

• oerr  is only available on UNIX but it does not take much code to access message file and display the message on windows
• To do this, the actual message file needs to copied to windows machine and placed in same directory as java program.
• The java program (oerr.java) reads the message and display text associated with error code.
• Java program reads the message file line by line until it encounters the actual error code

For example

C:\oracle\bin\java oerr ora 01652

Create custom Message Files

There are number of cases, where oerr does not show up any message, for such error codes, we can create own customized message file with error code

For example

$ oerr ora 00942

00942, 00000, "table or view does not exist"

// *Cause:

// *Action:

Following can be used to show customized message file

oerr technofile 00942

Where technofile is customized error message file. A customized message file can be created with below steps

1. Add the facility in facility.lis file
2. Create a directory that contains new message file
3. Create the actual message file

Alternatives for oerr 

ORACLE TechNet  - http://technet.oracle.com

ORACLE Metalink – http://support.oracle.com

Read More