Death by a thousand trace files

Found a large number of trace files generated in the /udump admin directory.

Each file looked like the following:

$ cat orcl_ora_12191.trc 
/u01/app/oracle/admin/ORCL/udump/orcl_ora_12191.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    SunOS
Node name:      hostname
Release:        5.10
Version:        Generic_144488-14
Machine:        sun4u
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 389
Unix process pid: 12191, image: oracle@hostname

opiino: Attach failed! error=-1 ifvp=0

Not much help on support.oracle.com or google in diagnosing this issue.

Oracle support was helpful in doing some tracing which proved that these were failures to connect rather than existing connections that were lost.

With that information in hand, we correlated the timestamps of the trace files to timestamps of listener connections. This narrowed our issue down to a specific IP address, and by that a specific application.

Our lead application administrator was able to determine the root cause of the failed connections, an outdated library used in development of the application:

From what I can tell CoreLab Oracle is now dotConnect for Oracle from Devart. They resolved this issue with version 5.70.140.

Whew.

UDM for Banner Jobsub and other subsystems

Over the weekend we had a SungardHE Banner subsystem offline. This particular subsystem uses Oracle Pipes to manage user job submission. The downtime could have been prevented if this subsystem had been represented as a target within Grid Control.

So, to prevent future outages, we're going to make this process a User Defined Metric, and have grid control fire off a notification if the service is offline. At the end of every maintenance window, if Grid Control says all systems are online, then my team can have more confidence that all systems are actually online.

Found a great article by Sagar Patil titled Oracle Grid Tracking OS Process Using a Custom User Defined Matrix udmshell script, which is the basis for what follows.

I created the following script that determines if ARG1 has ARG2 processes running and returns either Online or Offline:

#!/usr/local/bin/bash

if [ $# -ne 2 ]
then
  echo "Usage: `basename $0` {ORACLE_SID} {Expected Count}"
  exit 1
fi
toCheck=$1
testEm=`/bin/ps -ef | /usr/local/bin/grep -v grep | /usr/local/bin/grep -v $0 |
/usr/local/bin/grep $toCheck | /usr/local/bin/wc -l`

if [ $testEm -eq $2 ]
then
  echo "em_result=Online"
else
  echo "em_result=Offline"
fi

Next I create the UDM.

From the target Host screen, click on "User-Defined Metrics", then click create.

Here's the parameters I used:

Metric Name: GURJOBS DEVL

Metric Type: String

Command Line: /banner/u01/app/oracle/product/agent11g/sysman/emd/custom/checkSubsystems.sh gurjobDEVL 1

User Name: Oracle

Comparison Operator: Match

Critical: Offline

The rest I left as default. After hitting OK, it took about 15 minutes to show that gurjobDEVL was in fact online. Shutting down gurjobs in DEVL returned an error in the UDM screen but did not send a notification.

To enable notifications, I had to do the following:

Grid Control User Preferences -> Rules -> Select "Host Availability and Critical States", then click Edit. In the Metrics tab, click Add, enter "User" in the search and click Go.

To enable the metric, check the box next to User Defined Metric, and then choose "Critical" and "Clear" severity states, then click continue, and then OK.

Whoever is subscribed to that notification rule will be sent a notification per their preferences.

In this simple case, knowing that Banner Jobsub is offline is great. Another example would be with CAPP Pipes, the next step is to build in automatic restart scripts. That will have to wait for another blog post.

VI trick of the year

Sometimes the faster you type the slower you are.

Yesterday I was working on a configuration file, commenting and uncommenting about 10 different lines. Being a somewhat fast typist, the keyboard gymnastics required to add "--" to the beginning of 10 lines of code wasn't daunting enough to make me consider better ways to get the job done.

Those keyboard gymnastics consisted of the following 6 keypresses per line commented out:
i to insert
-- to comment the line out
ESC to go back to command mode
down arrow
then zero to go to the beginning of the line

I suppose if this had been 25 or 50 or whatever that unknowable mental threshold of "too many lines", I would have used awk or sed to get the job done.

My new coworker saw what I was doing, and asked why I didn't use the repeat command. Que? I asked?

Apparently hitting a period while in command mode will repeat the last insert/append that took place in edit mode. So simply made the change once in edit mode, then hit "." to repeat on the next line.

Ok, maybe not the "trick of the year". Maybe trick of the day is more appropriate, but I'm sure this is one of those tips that I'll be using on a daily basis -- a somewhat year changing event.

Thanks to this blog post and metalink doc 741004.1, learned some fun stuff about how an Oracle agent determines the amount of free memory available on a Solaris host.

ORACLE_HOME=/u01/app/oracle/product/agent11g
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/nmupm osLoad | awk -F"|" '{print $14}'

Now how to figure out why this would be so wildly different than vmstat...

Insert update and deleting records in a view

Recently a vendor gave me a vague request to grant "write access" to a view. Remembering an error a user had shown me in the past, I replied off the cuff that one can't simply write to a view.

I created the following test to prove that I was right, and you'll see that I proved myself wrong:

drop user u_owner;
create user u_owner identified by pass2pass;
alter user u_owner default tablespace users;
alter user u_owner quota unlimited on users;
grant create session to u_owner;
grant create table to u_owner;
grant create view to u_owner;

drop user u_viewer;
create user u_viewer identified by pass2pass;
grant create session to u_viewer;

conn u_owner/pass2pass;

create table tb_test (v_int1 int);
create view vw_test as select v_int1 from tb_test;
grant select, insert, update, delete on vw_test to u_viewer;

conn u_viewer/pass2pass;

select * from u_owner.vw_test;
insert into u_owner.vw_test values (1);
select * from u_owner.vw_test;
update u_owner.vw_test set v_int1 = 2;
select * from u_owner.vw_test;
delete u_owner.vw_test where v_int1 = 2;
select * from u_owner.vw_test;
conn / as sysdba;
drop user u_owner cascade;
drop user u_viewer;

exit;

So as it turns out you absolutely can insert / update / delete into views. Still trying to make sense of it, but it *is* possible.

Grid control claims App Server 10.1.2 is down wen console says it's up

I'm currently going through moving all of my targets from grid control repositories and in the process of doing that I'm cleaning up anything that doesn't need to be there, or anything that reports as being down. For some reason I like to see the fully green pie chart.

I had added one of my oracle mid tiers that had a version of 10.1.2.3 running to support our forms application and grid control reported that the app server was down, but I knew for a fact it was not. All the subcomponents were reporing as up, opmnctl reported everything up, and the iasconsole on the server itself reported everything was up. In summary, it was up.

After much digging through perl code and metalink docs I have resolved the issue and figured out it had to do with Solaris 10 security of /usr/ucb/ps command not being able to report extended process info without being the owner of the process. There was a pretty good metalink document that put me on this trail, but didn't solve the problem itself. Here's a metalink ID roundup for what helped (or didn't but was related).


395013.1 - Application Server shown as Down in Grid Control even though all of its Components are shown as up
276350.1 - How to Enable the Metric Browser/Agent Browser for the Oracle Management Agent [Video]
752944.1 - CMSDK ORACLE_IAS TARGET DOWN WHEN IT IS UP

Enabling the metric browser was essential for this, in addition to running the perl scripts manually from the host.

So the problem ended up being this, the agent checks for forms processes on the host and reports back the sid.
Run this as your agent owner, and then also as root to see the difference.

/usr/ucb/ps -axww | grep oc4j.jar | grep OC4J_BI_Forms | grep <$ORACLE_HOME>

Since the process is owned by a different user than the agent, it can't get the extended process info like root can. So to solve that two things had to be done.

First set up sudo privileges for your agent user to run /usr/ucb/ps as root

gridagent          ALL=(root)NOPASSWD: /usr/ucb/ps

Second, modify the perl code in $AGENT_HOME/sysman/admin/scripts/formsinit.pl to use sudo.
Change

elsif ( $os eq "SunOS" )
{
    $PS = "/usr/ucb/ps -axww";

To

elsif ( $os eq "SunOS" )
{
    $PS = "sudo /usr/ucb/ps -axww";

Restart the agent force an upload and check all your metrics again. You can do that via the metric browser, the commandline or wait for grid control to register the target again.

If anyone finds a metalink document that covers this exactly please add something in the comments.

OCM patch required even though you're not using OCM

While installing OAS 10gR3 patch 8626084 to upgrade OAS to 10.1.3.5, I received an error that step "Run One-off OPatches" had failed.

Looking at the log file, I found the following:


This is a OCM patch.
Home has OCM installed but not configured.


To run in silent mode, OPatch requires a response file for Oracle Configuration Manager (OCM).
Run /u01/app/oracle/product/oas10gr3/OPatch/ocm/bin/emocmrsp to generate an OCM response file. The generated response file can be reused on different platforms and in multiple OPatch silent installs.

To regenerate an OCM response file, rerun /u01/app/oracle/product/oas10gr3/OPatch/ocm/bin/emocmrsp.


ERROR: OPatch failed because of cmd. args. problem.


Doing a bit of digging, I found this recommendation to go ahead and run setupCCR in disconnected mode.

What's frustrating is that I requested that OCM not be installed or configured.

The following did the trick and I was able to complete the patch installation.

/u01/app/oracle/product/oas10gr3/ccr/bin/setupCCR -s -d