Monday 15 October 2007

Script for parsing *.ora files

The script below is able to parse *.ora files - mainly tnsnames.ora or listener.ora, though it should properly deal with other files with this Oracle syntax (complex values build by nested brackets, simple ones as pairs of key and value).

It is not complete (probably list of keywords in @keywords does not exhaust all possibilities, but it is enough to rebuild it with new entries).
Data::Dumper module is not a must - it is used only for debugging purposes and can be removed.

Lately I have got a question what licensing I would apply to this script and here some rules:
  • the script is free of charge
  • I would like to be still presented as an author ;-)
  • one can not distribute and charge for it (which does not mean it can not be added as a kind of "free" bonus to something commercial, but it must be stated so somewhere)
  • one may modify the script along to the needs - it would be nice to return changes to me assuming they have a general character (so are not bound to some local environment)

The script in more edible form

#!/usr/bin/perl -w
## ----------------------------------------------------------------
## $Id: ora_tns_parser.pl,v 1.2 2012/11/22 23:16:00 rems Exp $
## @action: parses *.ora files
## $Author: Remigiusz Sokolowski <rems@wp.pl> $
## $Date: 2007/10/15 10:50:15 $
## $Revision: 1.2 $
##
## Opis
## $Log: ora_tns_parser.pl,v $
## Revision 1.1  2007/10/15 10:50:15  rems
## tool for parsing *.ora files (mainly tnsnames or listener)
##
## parses provided *.ora file and returns pairs in the form
## key:value
## ALL - default; all pairs
## CUSTOM - alias/listener pairs (i.e. no parameters)
## LISTENERS_AND_PASSWORDS - pairs listener:encoded password (relevant only for listener.ora)
## NAMES - only keys - alias/listener names
##
## Usage
## ===================
## ./ora_tns_parser.pl <format> <path>
##
## Revision 1.2 2012/11/22 23:16:00 rems
## fix for bugged behavior when specific *.ora format in use (one line entry)
## not tested yet completely 
## ----------------------------------------------------------------

## arguments
my $fmt = shift;
my $fname = shift;

## modules and constant variables
my %formats = (
'ALL'    => 1, #default
'CUSTOM' => 1,
'LISTENERS_AND_PASSWORDS' => 1,
'NAMES'  => 1
);

my @keywords = (
'INBOUND_CONNECT_TIMEOUT',
'LOG_DIRECTORY',
'LOG_FILE',
'LOGGING',
'PASSWORDS',
'SAVE_CONFIG_ON_STOP',
'SID_LIST',
'TRACE_DIRECTORY',
'TRACE_FILE',
'TRACE_LEVEL'
);

use strict;
use warnings;
use Data::Dumper;

## functions
sub trim {
my $string = shift;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}

## counting instances of the provided x char in string tval
sub _count_chars {
my $x = shift;
my $tval = shift;
my @chars = split(//, $tval);
my $ch;
my $counter = 0;
foreach $ch (@chars) {
if ($ch eq $x) {
$counter++;
}
}
return $counter;
}

## whole algorithm depends on counting brackets
sub parse_tns_addr {
my $lines = shift;
my $i = shift;
my $tval = shift;
my $level = shift;

my $numL = _count_chars('(', $lines->[$i]);
my $numP = _count_chars(')', $lines->[$i]);
$level = $level + $numL - $numP;

if ($level <= 0) {
return ($i, $tval, 0);
} else {
$tval = $tval . trim($lines->[++$i]);
($i, $tval) = parse_tns_addr($lines, $i, $tval, $level);
}
}

## execution
my @lines;
my $line;
open(FH, "< $fname");
while (<FH>) {
chomp;
$line = trim($_);
if ($line !~ m/^#/ && $line !~ m/^\s*$/) {
push(@lines, $_);
}
}
close FH;

my $i=0;
my $pname = '';
my $pval = '';
my %params;

my @tmp;
my $trimmed = '';
for ($i=0; $i<@lines; $i++) {
## if sign '=' is not in line, it must be in the next one
if ($lines[$i] !~ m/=/) {
$lines[$i+1] = $lines[$i].$lines[$i+1];
$i++;
}
if ($lines[$i] =~ m/=/) {
## if in line there is '=', we split at first equality sign
if ((my $pos = index($lines[$i], "=")) >= 0) {
$tmp[0] = substr($lines[$i], 0, $pos);
$tmp[1] = substr($lines[$i], $pos+1);
} else {
$tmp[0] = $lines[$i];
}
$pname = trim($tmp[0]);

$trimmed = defined $tmp[1] ? trim($tmp[1]) : undef;
## if first char is '(', complex entry
if (@tmp>1 && substr($trimmed,0,1) eq '(') {
($i, $pval) = parse_tns_addr(\@lines, $i, $trimmed, 0);

## if not and entry has some not empty value, simple entry
} elsif (@tmp>1 && $trimmed ne '') {
$pval = $trimmed;

## it is possible, that value is in next line/s
} else {
$i++;
$trimmed = trim($lines[$i]);
if (substr($trimmed,0,1) ne '(') {
$pval = $trimmed;
} else {
my $level;
($i, $pval) = parse_tns_addr(\@lines, $i, $trimmed, 0);
}
}
$params{$pname} = $pval;
} else {
## if not, and we have no empty lines, probably wrong syntax in file
die "probably wrong syntax at line $i!"
}
@tmp = ();
$pname = '';
$pval  = '';
}

## for debugging purposes
#print "++++++++++++++++++++++++++\n";
#print Dumper(%params);
#print "++++++++++++++++++++++++++\n";

## we parse our list to throw away not needed entries
my $keyword;

## trimming output according to format
my $flg_del = 0;
if (defined $formats{$fmt} && $fmt ne 'ALL') {
foreach $pname (keys %params) {
foreach $keyword (@keywords) {
if (substr($pname, 0, length $keyword) eq $keyword) {
$flg_del = 1;
if ($fmt eq 'LISTENERS_AND_PASSWORDS' && $keyword eq 'PASSWORDS') {
$params{substr($pname, 1 + length 'PASSWORDS')} = $params{$pname};
}
}
}
if ($flg_del == 1) {
delete($params{$pname});
$flg_del = 0;
} elsif ($fmt eq 'LISTENERS_AND_PASSWORDS' && substr($params{$pname}, 0, 1) eq '(') {
## a little ugly hack, assuming that entries without keywords
## with values starting with "(" are certainly custom names
## i.e. listener names
$params{$pname} = '';
}
}
}

## output
my $format = (defined $formats{$fmt} && $fmt eq 'NAMES') ? "%s\n" : "%s:%s\n";
foreach $pname (keys %params) {
printf $format, $pname, $params{$pname};
}

Monday 20 August 2007

Joining Grid Control with SNMP framework

I realize it would be fine to connect our Grid Control (GC) installation with another monitoring tool our sysadmins use to control whole infrastructure.
With GC it is possible in two ways:
  1. just monitor all databases, their listeners and their hosts with GC and allow for generating traps by GC

    This way is fairly simple and requires almost no work. The first thing is to add to GC notification methods on the "Setup" page yet another method as SNMP trap. As we need to cooperate with snmptrapd deamon fired somewhere (being for us a gateway to SNMP management framework), we just provide 4 parameters to it. The next step is to check in the "Preferences" page in which areas a GC installation should generate SNMP traps. That is all - of course we need some software behind snmptrapd which takes the trap in order to service it.

  2. start SNMP agents responsible for communication with SNMP management framework and a "bridge" between GC agents and SNMP agent

Tuesday 19 June 2007

Licences

The Grid Control fascilities are provided for free for properly licensed products. It means that the database used for the repository does not require additional licences as well as the used application server as far as the Grid Control is the only thing on the database.

Well, I am not quite right - there can be installed for example also RMAN repository and other additional software provided for free by Oracle for monitoring and maintaining Your other properly licenced Oracle software installations.

In general however the Oracle licensing is very misty and incomprehensible. Oracle Enterprise Manager Licensing does not clear about OEM itself, focusing on available packs, plugins, etc

Thursday 14 June 2007

Starting and stopping Grid Control up

To start the Grid Control services one need to run the following sequence of activities:
  1. run database (first way)
    • sh# export ORACLE_SID={sid}
    • sh# sqlplus / as sysdba
    • SQL# startup
    • SQL# exit
    • sh# lsnrctl start {listener_name}
  2. run database (second way; one needs properly configured /etc/oratab)
    • sh# ORACLE_HOME/bin/dbstart
  3. run management services
    • sh# OMS_HOME/opmnctl startall
  4. optionally run agent to monitor also management repository and services
    • sh# AGENT_HOME/bin/emctl start agent
To stop the Grid Control services one need to do all the stuff above in reverse way:
  1. stop agent to monitor also management repository and services
    • sh# AGENT_HOME/bin/emctl stop agent
  2. stop management services
    • sh# OMS_HOME/opmnctl stopall
  3. stop database (first way)
    • sh# export ORACLE_SID={sid}
    • sh# sqlplus / as sysdba
    • SQL# shutdown immediate
    • SQL# exit
    • sh# lsnrctl stop {listener_name}
  4. stop database (second way; one needs properly configured /etc/oratab)
    • sh# ORACLE_HOME/bin/dbshut

Monday 11 June 2007

Policies used

Grid Control starts with quite high number of different policies (generally they have something to do with security issues). Of course it is recommended to apply all of them, though in general it is a little complicated. For example on one hand one should revoke EXECUTE privilege to let's say UTL_FILE from PUBLIC, while at the same time invalidating some synonyms within PUBLIC schema.

Here is shortcut of those policies toward database targets:

  • Parameter SQL92_SECURITY should be set to true. The parameter enforces having SELECT privilege to run UPDATE or DELETE with SET or WHERE clauses (and usage of column values)
  • Parameter GLOBAL_NAMES should be set to true. The parameter enforces a database link to have the same name as the database it connects to.
  • Checks whether UTL_FILE_DIR is not used with Oracle RDBMS version 9i and later (instead should be used directories of course)
  • Checks PUBLIC access to packages such as UTL_FILE, UTL_HTTP, UTL_SMTP, UTL_TCP, DBMS_LOB, DBMS_JOB, etc.
  • Checks if all objects are VALID, so on start one should compile all invalid objects and possibly remove those, which do not compile properly.
  • The password related settings for profiles in use should have non default values (default values mean usually UNLIMITED period). It particularly relates to PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, PASSWORD_REUSE_MAX and PASSWORD_GRACE_TIME.

Tuesday 5 June 2007

Glossary

  • AGENT_HOME - location of the agent installed on target
  • OMS_HOME - location of the Management Service
  • ORACLE_HOME - location of the database

Management service

Management Service (OMS) is an application which interacts with agents, gathering data on targets and sending commands. It runs on Oracle Application Server. It saves data into repository.

Adding target to Grid Control

  1. I have done the activities presented in the post "Agents"
  2. I have had to run emctl secure agent, providing password for OMS due to errors during the configuration phase of agent installation process
  3. next step was to run agent (emctl start agent)
  4. that was enough assuming DBSNMP accounts on target databases were open
  5. after that I was able to move to Grid Central Console, choose particular target database (by clicking link) and provide there password to DBSNMP account (on page loaded by link "Monitoring Configuration")

Versions

At the time there is available Release 3 of Grid Control. However full installers are provided only for Linux x86-64 and HP-UX Itanium architectures.

Beacause I am in position to run with completely new installation, I prefer full installer pack. For my architecture (Solaris 10 on sparc fortunately) there is Release 2 full installer with option to upgrade by patching to Release 3.

Database version provided in Release 2 is 10.1.0.4 - possible to be upgraded to 10.2.0.3 by installing full bunch of patches. That is why I decided to start the Grid Control installation with full installer of database in version 10.2.0.1 - I save this way installing some patches and can start directly with installation of 10.2.0.3 patches.

To install version 10.2.0.3 in this case it is required to run installer from p3731593_10203_SOLARIS64.zip (despite setting DISPLAY environment variable one needs to set ORACLE_HOME to OMS_HOME value). No special steps during this stage (I omit the installation of Oracle Configuration Manager).

Agents

Agents are provided for quite broad range of architectures and for many products (not only of Oracle origin). They work on hosts of targets and fulfill the role of a middle-man between Oracle Management Service (OMS) and target.

Agent is not the same as Database Console agent. You need to install agent provided in full installer or in Mass Agent Deployment section (depending on the architecture of target's host).

Agent software is installed on targets in two ways:
  1. running Grid Control installer on target's host and choosing option Additional management agent
  2. downloading agent and installing it within OMS_HOME/sysman/agent_download

Example of installation of agent for Solaris x64 for deployment:
  1. download Solaris_x64_GridControl_agent_download_10_2_0_2_0.zip
  2. create directory OMS_HOME/sysman/agent_download/10.2.0.2 if it does not exist
  3. unzip zip file within the newly created directory
  4. move agent_download.rsp.bak to agent_download.rsp, if agent_download.rsp does not exist
  5. modify agent_download.rsp file
    • %s_OMSHost% to correct OMS host
    • %s_OMSPort% to correct port (default 4889)
    • optionally one can set also %s_encrSecurePwd% (agent secure registration password) - there will not be the question about said password during installation
  6. modify OMS_HOME/sysman/agent_download/10.2.0.2.0/solaris_x64/agentDownload.solaris_x64 in the same way as agent_download.rsp

Example of final installation of agent for Solaris x64 on a remote host:
  1. one needs to ensure wget utility is provided and path to it within PATH environment variable
  2. download http://OMS_HOST:4889/agent_download/10.2.0.2.0/solaris_x64/agentDownload.solaris_x64 by let's say wget (of course 10.2.0.2.0 can be substituted by any proper agent version one provided on OMS_HOST, while solaris_x64 architecture can be substituted by any supported archutecture)
  3. run agentDownload.solaris_x64 on target host (usually one needs chmod u+x agentDownload.solaris_x64; agentDownload.solaris_x64 -b <agent_home_parent> -i <oracle_home>/oraInst.loc -t; during script run one will be prompted for agent registration password, if it was not specified already within agentDownload.solaris_x64 script)
    • Configuration assistant "Agent Configuration Assistant" failed - information provided about that failure was rather scarce, <agent_home>/cfgtoollogs/configToolFailedCommands contained only name of a Java class (oracle.sysman.emcp.agent.AgentPlugIn)
    • even with some commands failed agent software was installed correctly
    • of course due to some problems with confguration I was made to do some additional steps to run agent - for these look "Adding target to Grid Control" post
    • the configuration stage failed due to not setting AGENT_INSTALL_PASSWORD environment variable to agent secure registration password (or fulfilling the setting of said password within agent script and response (i.e. rsp) files)
  4. after that one needs to run script AGENT_HOME/root.sh; however in case of failed installation the script exists somewhere within agentDownload{version}Oui directory. I must admit that after providing proper password (i.e. password configured at Setup->Registration Passwords of the OMS Control Console) installation fails no more. I have found also notices that any listeners should be down while installing agent.

Targets

Targets are opposite to repository elements of the Grid Control architecture. They are just databases or application servers or whatever is supported.

Databases are connected to as stated earlier by using DBSNMP accounts.

Repository

First thing is a repository - it is a common Oracle database, which must be Enterprise Edition (AFAIK that is due to usage of partitioning, may be more). Good news is one does not need to acquire additional licences as stated in post "Licences".

I start from the installation of RDBMS only from Oracle RDBMS 10.2.0.1 bundle. During the further installation of Grid Control I have to choose option of installation on an existing database. There were no special additions during installation of the database, I unchecked only the option with Database Console (wich is somewhat a duplication of the Grid Control functionality).

Accounts

Grid Control repository is run on SYSMAN account - it is created and populated while installing Grid Control or Database Control option of database installation.

Agents connect to database targets by DBSNMP accounts - those accounts are created always during database installation. However if You have not chosen Database Control option, it is locked and password stays set to change_on_install. Those accounts are granted with OEM_MONITOR role which allows for SELECT ANY DICTIONARY and usage of few other privileges (managing statistics and queues, creating jobs, managing advisors) needed for performing basic administration tasks.

However to access part of the console one needs to log on
to a chosen database once again - this time directly from OMS using some account - it can be also DBSNMP, however one might be not allowed to perform all possible on console operations.


Most tabs

Trying to run Oracle Grid Control

Last time I realized, we want to monitor our Oracle databases. The Oracle Company provides something called shortly Grid Control what allows monitor performance of databases, application servers, etc and manage those targets.

I have started at 0 level - I have small knowledge about the whole thing and no idea about Oracle Application Server in particular. At first look it seems at least messy. The documentation is large and important things are sunk among those of low importance.

That's the reason behind writing this blog...