The Blog from the DBA Classroom

By: Joel Goodman

Archive for February, 2009

Inside the DBA Classroom 2

Posted by Joel Goodman on 24/02/2009

In my first “Inside the DBA Classroom” post I discussed four goals for delivering seminars and courses to DBAs and which may be applied to any skills and knowledge transfer of any kind.

These are:

1. Add Value

2. Obtain Collateral Knowledge and Skill

3. Know the System

4. Raise the Bar

I write demo scripts to helps me achieve these goals.

Writing good demo scripts helps me to “Know the System” by providing a structure to my research. I first create an outline of what is to be demonstrated defining the steps from start to finish. This is followed by an iterative approach of research and script writing whereby steps are gradually added. This may involve more research and changes to t he original structure if new ideas come to mind.

By creating these demos I am able to “Add Value” to presentations beyond the material presented in the theory part of a course. This requires that my demos do not simply reiterate what is in course notes, but rather that they either examine features related to but not covered in the material, or that they go deeper into the technology than do the lessons. This depth also serves to “Raise the Bar” by challenging delegates to dig deeper and to learn more than what is in offer.

Occasionally this also involves my fourth goal of  “Collateral Knowledge and Skill” by providing an opportunity to teach features that are related to those on the course but which are not covered. For example a demo script created to teach DBAs about the various types of locks in oracle provides an opportunity to discuss the UL Lock type. But to demonstrate user locks one must create and acquire them using the dbms_lock package and this helps to teach the use of that package even though the main purpose of the demo may have been “Diagnosing Lock Problems”.

Here are some tips for writing demo scripts:

1. Action and Reaction

Scripts with DDL statements should follow the statements with queries on the appropriate Data Dictionary views to see the consequences of the DDL. This also helps DBAs learn how to “infer” what may have been done when querying the Data Dictionary. I have seen may demos that were generally good at showcasing product features but they jump from one DDL to the next without showing what has changed in the Dictionary. Of course if one must do many identical actions then the DD query may wait until they are complete such as when creating numerous tablespaces for example. In such a case only one query against DBA_TABLESPACES view may be needed.

2. Avoid Copy and Paste

From time to time, output from a query in a demo is used as input to another demo step such as the filter on a query, or an argument to a procedure call etc. Avoid “copy and paste” to prevent errors due to slips of the mouse and to save time. If using SQL*PLUS, then this is done by using the “column <column name> new_value <variable name> ” statement or by using subqueries.

3. Annotate the demo

Add explanatory comments to the demo explaining what is being done, why and how to interpret output when it is not obvious. For example when demonstrating sessions used by a shared database link there are two sessions connected to the same process on the remote database instance and this may require explanation as to why this is so when querying v$session. These annotations are especially useful if the demo is used for self-study by delegates. I often give my demos to others and they can use the annotations so that the demo is a self contained teaching and learning tool.

4. Use Viewlets

For Complex demos involving multiple screens or Graphical tools I recommend creating viewlets (which are recordings of demos) if you havea viewlet builder. Oracle University create may such demos for use in the DBA Classroom.

5. Maintain the Demos

Many opportunities arise to enhance demos including new features that relate to features in the demo, brainstorms where new ideas for existing features arise, or simply tinkering to improve the flow and formatting of the output. I consider demos to be like an applications in that they require maintenance from time to time.

6. Share your Demos

I share my demos with colleagues, customers and peers in the industry often getting good ideas from them and learning from their demos. Some of my colleagues in Oracle University create fabulous demos from which I have learned a great deal especially on DBA 2.0 topics for which Harald van Breederode created many demos.

Posted in Oracle | Leave a Comment »

Teach Yourself Some OCR

Posted by Joel Goodman on 11/02/2009

The Oracle Clusterware Repository or OCR is a file containing metadata describing the Cluster configuration, the locations of the voting disks, the resources managed by the Clusterware processes and their attributes and interrelationships.

Two of the utilities which access the OCR file and display certain details are:

1. srvctl – this will display certain attributes relating to ASM, Databases, Database and ASM instances, Services, Listeners and Nodeapps. We can see this with a simple invocation of “srvctl”:

Oracle$ srvctl
Usage: srvctl <command> <object> [<options>]

objects: database|instance|service|nodeapps|asm|listener
For detailed help on each command and object and its options use:
srvctl <command> <object> -h

I have highlighted the list of objects that may be managed with any of the commands listed above but notice that the resources in the object do not include clusterware components or metadata and there is no reference to clusterware attributes. Note that the nodeapps include the VIPs which are not part of the clusterware but rather are resources managed by the clusterware.

2. crs_stat – this utility has many flags controlling the level of detail displayed on the screen but essentially this will display clusterware resources in a formatted output. Here are the options:

Oracle$ crs_stat -h
Usage:  crs_stat [resource_name […]] [-v] [-l] [-q] [-c cluster_member]
crs_stat [resource_name […]] -t [-v] [-q] [-c cluster_member]
crs_stat -p [resource_name […]] [-q]
crs_stat [-a] application -g
crs_stat [-a] application -r [-c cluster_member]
crs_stat -f [resource_name […]] [-q] [-c cluster_member]
crs_stat -ls [resource_name […]] [-q]

If “crs_stat” is invoked with no arguments then a terse output is produced containing the name and 3 attributes of resources such as Databases, ASM, Instances of Databases and ASM, Services and their preferred instances, Listeners, and Nodeapps. Here is some example output:

Oracle$ crs_stat

STATE=ONLINE on abcduc1

STATE=ONLINE on abcduc2

STATE=ONLINE on abcduc1

A popular alternative is to use the -t flag to restrict output to one line per resource as follows:

Oracle$ crs_stat -t
Name              Type           Target       State       Host
ora….A1.inst   application    ONLINE     OFFLINE
ora….A2.inst   application    ONLINE     OFFLINE
ora….l1.inst   application     ONLINE     ONLINE     abcduc1
ora….l2.inst   application     ONLINE     ONLINE     abcduc2
ora.angel.db   application    ONLINE     ONLINE     abcduc1


Note that the resource name is not fully printed with this formatting. Also the list of resources is still restricted to the ones I mentioned above.

One way to get the full resource name is to use a script to format the name and other attributes as follows:

# Sample 10g CRS resource status query script
# Description:
#    – Returns formatted version of crs_stat -t, in tabular
#      format, with the complete rsc names and filtering keywords
#   – The argument, $RSC_KEY, is optional and if passed to the script, will
#     limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
#   – $ORA_CRS_HOME should be set in your environment

AWK=/usr/bin/awk    # if not available use /usr/bin/awk

# Table header:echo “”
$AWK \
‘BEGIN {printf “%-45s %-10s %-18s\n”, “HA Resource”, “Target”, “State”;
printf “%-45s %-10s %-18s\n”, “———–“, “——“, “—–“;}’

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
‘BEGIN { FS=”=”; state = 0; }
$1~/NAME/ && $2~/’$RSC_KEY’/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf “%-45s %-10s %-18s\n”, appname, apptarget, appstate; state=0;}’

This will return output containing the complete resource name:

HAResource                                       Target         State

—————                                        ——–         ——

ora.TUTOR1A.TUTOR1A1.inst      ONLINE     ONLINE on abcduc1


If however, you wish to examine all the metadata in the OCR including details of the clusterware files, network addresses, ports, processes and their attributes then use the “ocrdump” utility in the clusterware home bin directory:

To get help simply do:

Oracle$ ocrdump -h

ocrdump – Dump contents of Oracle Cluster Registry to a file.

ocrdump [<filename>|-stdout] [-backupfile <backupfilename>] [-keyname <keyname>] [-xml] [-noheader]

Default filename is OCRDUMPFILE. Examples are:

prompt> ocrdump
writes cluster registry contents to OCRDUMPFILE in the current directory

prompt> ocrdump MYFILE
writes cluster registry contents to MYFILE in the current directory

prompt> ocrdump -stdout -keyname SYSTEM
writes the subtree of SYSTEM in the cluster registry to stdout

prompt> ocrdump -stdout -xml
writes cluster registry contents to stdout in xml format
The header information will be retrieved based on best effort basis.
A log file will be created in $ORACLE_HOME/log/<hostname>/client/ocrdump_<pid>.log. Make sure  you have file creation privileges in the above directory before running this tool.

So now do the following:

Oracle$ ocrdump MYOCRDUMP

This will produce a formatted dump called MYOCRDUMP of all the OCR file contents in the current directory which may them be explored using any convenient editing tool.

The Dumpfile has 3 main parts:

1. SYSTEM – Desribes attributes of the clusterware such as interfaces, cluster name, cluster parameters such as misscount, locations of voting disks, location of OCR backups, cluster node names and numbers, hostnames and much more. This metadata is used by the clusterware processes CRSD, CSSD and EVMD.

2. DATABASE – Contains metadata for all the resources managed by the clusterware. This is the information that is formatted for output by the “crs_stat” utility and  by “srvctl” and lists databases, ASM, instances, services, nodeapps, atc..

3. CRS – Contains metadata for any Non-RAC resources that are managed for high availability by the clusterware. These resources will have been created by the “crs_profile” utility, and then registered in the OCR using the “crs_register” utility. Once all related resources have been registered then they will have been activated using the “crs_start” utility. If no such resources are managed by the clusterware then the CRS section will be virtually empty with a single entry for Scurity.

When I began using Oracle clusterware for 10g, I used the “ocrdump” utility primarily as a learning tool to improve my understanding of the Clustereware architecture and encourage all DBAs using or learning RAC to do the same. I now use it as a teaching tool in the DBA Classroom.

Note that an OCR DUMP file is quite large but an example may be found at thie link:

>>  Sample OCR Dump

Posted in Oracle | 3 Comments »