May 2007

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

May 2007
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Monitoring Oracle’s Cluster Services

You’re invited to submit your Oracle-related questions to us at askdbspecialists05@dbspecialists.com. Please include your name and telephone number in your email.

Oracle Backup and Recovery Overview

One of the first things you should do when you get Oracle installed is to develop and implement a backup and recovery plan. A lot of factors should play into your decision in this area. Oracle Corporation’s website provides a review of various options and considerations such as:

  • What information should be backed up?
  • Which backup methods should be used?
  • Understanding basic recovery strategy.
  • What are the different types of recovery?

Check out the full article at http://www.oracle.com/technology/deploy/availability/htdocs/BR_Overview.htm”.

Considering Oracle Real Application Clusters (RAC) ?

Many of you have asked about RAC technology and whether it makes sense for you to consider adopting it within your enterprise. While RAC can increase scalability and does increase availability by safeguarding against certain types of failure, RAC alone does not constitute a complete high availability solution. Furthermore, the increased complexity and possible points of failure must be weighed against the benefits in order to determine if RAC is an appropriate technology for your environment.

In doing your research, you may want to review the website of the RAC special interest group (associated with the Independent Oracle Users Group) whose stated purpose is to “advance RAC awareness and adoption while enhancing the experience of current RAC users by providing a forum for technical interchange.” A few of the white paper topics available on their site are:

  • Adding New Nodes to Your Oracle RAC 10g Cluster on Linux
  • Monitoring Your Cluster Environment
  • Installing Oracle Database 10g RAC on Sun Solaris 10 x86-64
  • 10g RAC & CRS Troubleshooting

These articles and more can be found at http://www.oracleracsig.org.

On Oracle Security

Pete Finnigan does a lot of work on the topic of Oracle security and has a blog that contains some of his musings, presentations, and upcoming events. Recent blog topics include:

  • Getting started with Oracle security
  • Oracle BI Suite and Row Level Security

Check out his blog at http://www.petefinnigan.com/weblog/entries/. You can also follow the links to many of Pete’s white papers and scripts—full of valuable information on the topic of security.

 

This month’s question comes to us from Gage in Boise, ID: We have a two-node Oracle 10gR2 RAC cluster running on IBM AIX. On occasion we see an alert for an instance or a listener. The alert might be as simple as someone typing the word “oracle” at the command line which will generate an ORA-0600, or possibly network congestion which is causing an application or monitoring daemon to be slow in connecting.

At any rate, when time is of the essence and I need to check the general health of the cluster services, I would like to be able to use the $CRS_HOME/bin/crs_stat command. However, this command spews out a long list of resource targets, types, and current states. If I use the -t parameter to crs_stat, the output should be more readable, but the resource names are often truncated because of long names. How can I quickly check the cluster services and get readable output?

Rich Headrick of the Database Specialists team responds: The $CRS_HOME/bin/crs_stat command displays the status of the cluster services, but the output is not very readable. The crs_stat command does give a more readable output when invoked with the –t parameter, but this parameter causes the resource names to be abbreviated and the output therefore may not be very useful. Sample output from crs_stat appears below. Note how the resource names are abbreviated:

/home/oracle-->$CRS_HOME/bin/crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    aix1
ora....x1.lsnr application    ONLINE    ONLINE    aix1
ora....ix1.gsd application    ONLINE    ONLINE    aix1
ora....ix1.ons application    ONLINE    ONLINE    aix1
ora....ix1.vip application    ONLINE    ONLINE    aix1
ora....SM2.asm application    ONLINE    ONLINE    aix2
ora....x2.lsnr application    ONLINE    ONLINE    aix2
ora....ix2.gsd application    ONLINE    ONLINE    aix2
ora....ix2.ons application    ONLINE    ONLINE    aix2
ora....ix2.vip application    ONLINE    ONLINE    aix2
ora....test.db application    ONLINE    ONLINE    aix1
ora....x1.inst application    ONLINE    ONLINE    aix1
ora....x2.inst application    ONLINE    ONLINE    aix2
/home/oracle--> 

Oracle has made available on the Metalink website a wrapper script for the crs_stat command that makes the output much more readable. The output contains the formatting and column information that you would expect from running $CRS_HOME/bin/crs_stat –t, but without abbreviating the resource names. The script is available in Metalink document 259301.1. Sample output from the wrapper script appears below. Note that the resource names are no longer abbreviated, but the output is still quite readable:

HA Resource                                   Target     State             
-----------                                   ------     -----             
ora.test.test1.inst                           ONLINE     ONLINE on aix1
ora.test.test2.inst                           ONLINE     ONLINE on aix2         
ora.test.db                                   ONLINE     ONLINE on aix1
ora.aix1.ASM1.asm                             ONLINE     ONLINE on aix1
ora.aix1.LISTENER_aix1.lsnr                   ONLINE     ONLINE on aix1
ora.aix1.gsd                                  ONLINE     ONLINE on aix1
ora.aix1.ons                                  ONLINE     ONLINE on aix1
ora.aix1.vip                                  ONLINE     ONLINE on aix1
ora.aix2.ASM2.asm                             ONLINE     ONLINE on aix2
ora.aix2.LISTENER_aix2.lsnr                   ONLINE     ONLINE on aix2
ora.aix2.gsd                                  ONLINE     ONLINE on aix2
ora.aix2.ons                                  ONLINE     ONLINE on aix2
ora.aix2.vip                                  ONLINE     ONLINE on aix2

The wrapper script provided in the Metalink document is a simple Korn shell script, and it should work on most Unix and Linux platforms:

#!/usr/bin/ksh
#
# 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 

RSC_KEY=$1
QSTAT=-u
AWK=/usr/bin/awk    # if not available use /usr/xpg4/bin/awk

# Table header:echo ""
$AWK \
  'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
          printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$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;}'

Leave a Reply

Your email address will not be published. Required fields are marked *