Oracle Functions Converting Between Latitude/Longitude and MGRS Coordinates

Let me provide the general context here. I worked on a gig in the recent past where, in addition to my DBA duties, I did some custom Oracle development work in a geospatial environment. For the sake of readability and context, suppose we were building a database of test aircraft bombing runs. We can use latitude (NS) and longitude (EW) to describe the target location. NATO militaries conventionally use an MGRS format 15-character length string (to specify within a meter or so precision):

The Military Grid Reference System (MGRS) is a grid-based system used to represent locations on the universal transverse Mercator (UTM) and universal polar stereographic (UPS) grid systems, expressed as an alphanumeric string. An MGRS coordinate defines an area on the Earth's surface as opposed to a specific point. A fully qualified MGRS string is 15 characters long and consists of the following three components: grid zone designation, 100,000-meter square identifier, and easting/northing.

Feeds might include data in latitude/longitude and/or MGRS format. Also, latitude/longitude data may be expressed in DMS (degrees/minutes/seconds) or decimal format (60 minutes per degree, 60 seconds per minute). I also wrote conversion functions for these, not included in this post. The functional developers I worked with preferred decimal format in working with related software like ArcGIS. For the purposes of the conversion functions below, the inputs or outputs are in decimal format.

One of the issues I had in dealing with a functional project manager was that, despite his decade of geospatial experience, he didn't understand the limitations of the data. For example I might have DMS data in whole numbers, and truncated/rounded fractions of seconds could effect computed coordinates.

I personally did not find Oracle-compatible functions for the conversions and so I adapted Java-like code from sources like Steve Brooker's embedded code and here. I cross-validated against online conversion websites like here.

Another thing I'm exemplifying here is my preferred style of technical documentation:

CONV_FROM_MGRS

Purpose:

To derive latitude/longitude in decimal format from MGRS in standard 15-character format. Among other reasons, in theory this provides an internal consistency check among source latitude, longitude, MGRS.

Syntax:

conv_from_mgrs(’15-character MGRS value’)

Output:

latitude decimal, longitude decimal

*** denotes an input error. There are explicit checks for input length and integer contents for the last 10 characters.

Code:

create or replace function conv_from_mgrs (mgrs in varchar2)

return varchar2

IS outlatlong varchar2(100);

pi number:=asin(1)*2;

type iarray is varray(20) of double precision;

i iarray;

type jarray is varray(20) of double precision;

j jarray;

lat double precision;

long double precision;

b1 varchar2(3);

b2 varchar2(2);

b3 double precision;

b4 double precision;

c double precision;

d varchar2(1);

e double precision;

f varchar2(1);

g integer;

h integer;

k double precision;

l double precision;

m double precision;

n double precision;

o double precision;

p double precision;

q double precision;

r double precision;

s double precision;

t double precision;

u double precision;

v double precision;

w double precision;

x double precision;

y double precision;

z double precision;

aa double precision;

ab double precision;

ac double precision;

ad double precision;

ae double precision;

af double precision;

ag double precision;

ah double precision;

ai double precision;

aj double precision;

ak double precision;

al double precision;

begin

if length(mgrs)=15 and invalid_integer_check(substr(mgrs,6,10))=0 then

b1:=substr(mgrs,1,3);

b2:=substr(mgrs,4,2);

b3:=to_number(substr(mgrs,6,5));

b4:=to_number(substr(mgrs,11,5));

c:=to_number(substr(b1,1,2));

d:=substr(b1,3,1);

e := ((c-1)*6-177)*pi / 180;

case

  when ((c-1) mod 3) = 0 then f:=instr('ABCDEFGH',substr(b2,1,1));

  when ((c-1) mod 3) = 1 then f:=instr('JKLMNPQR',substr(b2,1,1));

  else f:=instr('STUVWXYZ',substr(b2,1,1));

end case;

g:=instr('CDEFGHJKLMNPQRSTUVWXX',d);

case

  when ((c-1) mod 2) = 0 then h:=instr('ABCDEFGHJKLMNPQRSTUV',substr(b2,2,1))-1;

  else h:=instr('FGHJKLMNPQRSTUVABCDE',substr(b2,2,1))-1;

end case;

i:=iarray(1.1,2.0,2.8,3.7,4.6,5.5,6.4,7.3,8.2,9.1,0,0.8,1.7,2.6,3.5,4.4,5.3,6.2,7.0,7.9);

j:=jarray(0,2,2,2,4,4,6,6,8,8,0,0,0,2,2,4,4,6,6,6);

k:=i(g);

l:= j(g) + h / 10;

if (l < k)  then l:=l + 2; end if;

m:=f*100000.0 + b3;

n:=l*1000000 + b4;

m:= m-500000.0;

if (d < 'N') then n:=n-10000000.0; end if;

m:= m/0.9996;

n:=n/0.9996;

o:= n / 6367449.14570093;

p:= o + (0.0025188266133249035*sin(2.0*o)) + (0.0000037009491206268*sin(4.0*o)) + (0.0000000074477705265*sin(6.0*o)) + (0.0000000000170359940*sin(8.0*o));

q:=tan(p);

r:=q*q;

s:=r*r;

t:=cos(p);

u:=0.006739496819936062*power(t,2);

v:=40680631590769/(6356752.314*sqrt(1+u));

w:=v;

x:=1.0/(w*t);

w:=w*v;

y:=q/(2.0*w);

w:=w*v;

z:=1.0/(6.0*w*t);

w:=w*v;

aa:= q / (24.0*w);

w:=w*v;

ab:= 1.0 / (120.0*w*t);

w:=w*v;

ac:= q / (720.0*w);

w:=w*v;

ad:= 1.0 / (5040.0*w*t);

w:=w*v;

ae:= q / (40320.0*w);

af:= -1.0-u;

ag:= -1.0-2*r-u;

ah:= 5.0 + 3.0*r + 6.0*u-6.0*r*u-3.0*(u*u)-9.0*r*(u*u);

ai:= 5.0 + 28.0*r + 24.0*s + 6.0*u + 8.0*r*u;

aj:= -61.0-90.0*r-45.0*s-107.0*u + 162.0*r*u;

ak:= -61.0-662.0*r-1320.0*s-720.0*(s*r);

al:= 1385.0 + 3633.0*r + 4095.0*s + 1575*(s*r);

lat:= p + y*af*(m*m) + aa*ah*power(m,4) + ac*aj*power(m,6) + ae*al*power(m,8);

long:= e + x*m + z*ag*power(m,3) + ab*ai*power(m,5) + ad*ak*power(m,7);

lat:=lat*180/pi;

long:=long*180/pi;

outlatlong:=to_char(lat,'fm999.9999999')||','||to_char(long,'fm999.9999999');

return(outlatlong);

else return('***');

end if;

exception

when others then return('***');

end;

/

CONV_TO_MGRS

Purpose:

To derive latitude/longitude in decimal format from MGRS in standard 15-character format. Among other reasons, in theory this provides an internal consistency check among source latitude, longitude, MGRS.

Syntax:

conv_to_mgrs(’latitude decimal’, ‘longitude decimal’)

Note: single quotes are optional, but unquoted/undefined nonnumeric characters in inputs will result in an explicit SQL*PLUS error. Exceptions for quoted inputs are captured.

Output:

MGRS (standard 15-character string)

*** denotes an invalid input

(SQL*PLUS session serveroutput must be on for any relevant raised displayed messages, e.g., lat/long limits)

Code:

create or replace function conv_to_mgrs (lat in varchar2, lon in varchar2)

return varchar2

IS outmgrs varchar2(15);

latitude double precision;

longitude double precision;

invalid_latitude exception;

invalid_longitude exception;

pi double precision:=asin(1)*2;

outaa varchar2(5);

outab varchar2(5);

c double precision;

e double precision;

k double precision;

l double precision;

m double precision;

n double precision;

o double precision;

p double precision;

q double precision;

r double precision;

s double precision;

t double precision;

u double precision;

v double precision;

w double precision;

x double precision;

y double precision;

z double precision;

aa double precision;

ab double precision;

ad varchar2(1);

ae double precision;

af varchar2(1);

ag double precision;

ah varchar2(1);

begin

latitude:=to_number(lat);

longitude:=to_number(lon);

if abs(latitude) > 90 then raise invalid_latitude; end if;

if abs(longitude) > 180 then raise invalid_longitude; end if;

c:=1+floor((longitude+180)/6);

e:=c*6-183;

k:=latitude*pi/180;

l:=longitude*pi/180;

m:=e*pi/180;

n:=cos(k);

o:=0.006739496819936062*power(n,2);

p:=40680631590769/(6356752.314*sqrt(1+o));

q:=tan(k);

r:=q*q;

s:=(r*r*r)-power(q,6);

t:=l-m;

u:=1-r+o;

v:=5-r+9*o+4*(o*o);

w:=5-18*r + (r*r) + 14*o - 58*r*o;

x:=61-58*r + (r*r) + 270*o - 330*r*o;

y:=61-479*r + 179*(r*r) - (r*r*r);

z:=1385-3111*r + 543*(r*r)- (r*r*r);

aa:=p*n*t+(p/6*power(n,3)*u*power(t,3))+(p/120*power(n,5)*w*power(t,5))+(p/5040*power(n,7)*y*power(t,7));

ab:=6367449.14570093*(k - (0.00251882794504*sin(2*k)) + (0.00000264354112*sin(4*k)) - (0.00000000345262*sin(6*k)) + (0.000000000004892*sin(8*k))) + (q/2.0*p*power(n,2)*power(t,2)) + (q/24.0*p*power(n,4)*v*power(t,4)) + (q/720.0*p*power(n,6)*x*power(t,6)) + (q/40320.0*p*power(n,8)*z*power(t,8));

aa:=aa*0.9996 + 500000.0;

ab := ab*0.9996;

if ab < 0 then ab:=ab + 10000000.0; end if;

ad:=substr('CDEFGHJKLMNPQRSTUVWXZ',floor((latitude/8 + 11)),1);

ae:=floor(aa/100000);

case

  when ((c-1) mod 3) = 0 then af:= substr('ABCDEFGH',ae,1);

  when ((c-1) mod 3) = 1 then af:= substr('JKLMNPQR',ae,1);

  else af:=substr('STUVWXYZ',ae,1);

end case;

ag:=1+(floor(ab/100000)) mod 20;

case

  when ((c-1) mod 2) = 0 then ah:=substr('ABCDEFGHJKLMNPQRSTUV',ag,1);

  else ah:=substr('FGHJKLMNPQRSTUVABCDE',ag,1);

end case;

outaa:=ltrim(to_char((aa mod 100000),'00000'));

outab:=ltrim(to_char((ab mod 100000),'00000'));

outmgrs:=c||ad||af||ah||outaa||outab;

return(outmgrs);

exception

when invalid_latitude then dbms_output.put_line('latitude between -90 and 90'); return('***');

when invalid_longitude then dbms_output.put_line('longitude between -180 and 180'); return('***');

when others then return('***');

end;

/

A Simple Script For Generating Randomized Oracle Passwords

Passwords are a fact of life in Oracle user management, going far beyond simply resetting passwords for users who have forgotten their own. For example, you may have production tasks scheduled in OEM. There are security issues like a disgruntled terminated employee who knows a product schema or other privileged account passwords. In general, permanent passwords pose a risk, like leaving your house key under the welcome mat.

The federal government publishes (in the public domain) standards, like STIGs. There have been tightening standards, like shrinking password rotation cycles and reusability, increased complexity (length and use of alternative character types), etc. You often encounter organizational resistance; for instance, in one federal engagement, a civilian tech lead said password rotation was complex and affected her codebase, and she wasn't willing to rotate salient passwords more than once annually (vs., say, 6 times a year). More recently, I had been working on a multi-factor authentication method tying user CAC's to externally-defined Oracle user accounts, i.e., something you have (a government CAC/smartcard) and something you know (a smartcard PIN).

Of course, the above-mentioned codebase problem and OEM scheduling of production jobs with rotating passwords reflect a more entrenched problem with data/program interdependence, e.g., you change a password but the old password is embedded/hard-coded in (multiple?) production scripts or OEM stored parameters. One practical solution I deployed at a government facility 15 years back was a freeware product called Oracle Password Repository (the last time I checked, this product was still available via SourceForge.net). In essence, I could read passwords stored in the encrypted OPR into configured Unix script variables, so all I needed to do was register a centralized password change into OPR instead of manually propagating password changes to a poorly documented potentially large number of scripts. We see a comparable solution in OEM stored passwords at the middle-tier/weblogic level, use of Oracle Wallet, etc.

In this post, I want to focus on a simple script that can be used to generate randomized Oracle passwords with given complexity rules. (Yes, I'm aware that a number of password programs like LastPass, KeePass, etc., and/or websites provide similar functionality.) I'm going to illustrate it through a bash script that focuses on a STIG-like complexity rule set: a 15-character output that guarantees at least 2 upper-case, 2 lower-case, 2 digit and 2 special characters using randomization functionality. In this case I am narrowing special characters to a selection of 3 (note that Unix/Linux scripts are sensitive to special characters, which need to be prefaced by a backslash). This is because certain types of passwords, e.g., in OEM infrastructure, are sensitive to the use of other special characters. But many schema passwords can use alternative special characters. You can easily adapt the script to modify character type limits, changes to source character strings, or password length by adjusting relevant limits and/or modulus (%) arguments. [I am not going to go into an arcane discussion of the technical merits of bash randomization functionality; it is certainly an improvement over the status quo.] Note that for the output below, I used bash in Ubuntu available through Windows 10.

Genpwd.sh first displays concatenated generated randomized character type strings focusing on relevant rules, mostly for QC purposes (and you can delete/comment out the relevant echo $pw line) and then reshuffles pw to npw and displays it.

#!/bin/bash
ls=abcdefghijklmnopqrstuvwxyz
us=ABCDEFGHIJKLMNOPQRSTUVWXYZ
ds=0123456789
cs=\#\$\_
ts=$ls$us$ds$cs
pw=''
for (( c=1; c<=2; c++ ))
do
   p=$(($RANDOM % 26 ))-1
   pw=$pw${ls:$p:1}
done
for (( c=1; c<=2; c++ ))
do
   p=$(($RANDOM % 26 ))-1
   pw=$pw${us:$p:1}
done
for (( c=1; c<=2; c++ ))
do
   p=$(($RANDOM % 10 ))-1
   pw=$pw${ds:$p:1}
done
for (( c=1; c<=2; c++ ))
do
   p=$(($RANDOM % 3 ))-1
   pw=$pw${cs:$p:1}
done
for (( c=1; c<=7; c++))
do
   p=$(($RANDOM % 64 ))-1
   pw=$pw${ts:$p:1}
done
echo $pw
npw=`echo $pw | fold -w1 | shuf | tr -d '\n'`
echo $npw

This sample extract was generated from the working directory containing genpwd.sh:

 ./genpwd.sh
wtKV47__YGAIxAv
xtAVGIY__4KAwv7
./genpwd.sh
ubKZ51$##b43bqV
4VKZu3b#q51$bb#
./genpwd.sh
ncXU93__6DZZ#tX
3tnZc_ZXU9X6D#_
./genpwd.sh
vwMI87_#4KRCXJo
XvKI7wJM4C_#Ro8

Note that you can easily accommodate other rules. For instance, suppose you have a rule that a password must begin with, say, an upper-case character. There are a variety of solutions you could deploy, like rotating the generated password to the first occurrence of the desired character type. In the sample excerpt below, I use a brutal-force method of generating passwords until I get one starting with the target character type:

type='NO'

until [[ "$type" == "lower" ]]; do pwd=`./genpwd.sh |tail -1`; echo $pwd;  case ${pwd:0:1} in [[:lower:]]) type='lower';; [[:upper:]]) type='upper';; [0-9]) type='digit';; *) type='something else';; esac; echo $type; done
WIp2pM92TV_nyd#
upper
$hQ6QR$#fK65XBk
something else
Sa#rGtuNdK2#Vv4
upper
0z_dJvc$vGi9I5C
digit
kO0O4VkV#p_vDZ_
lower

until [[ "$type" == "digit" ]]; do pwd=`./genpwd.sh |t
ail -1`; echo $pwd;  case ${pwd:0:1} in [[:lower:]]) type='lower';; [[:upper:]]) type='upper';; [0-9]) type='digit';; *)
 type='something else';; esac; echo $type; done
FO6p6FC$m#RdNoH
upper
#$JsQ71XtIYCBPc
something else
$E0g#3x7tMeBSmo
something else
W_gTC1xTpvZp$9I
upper
0iQfU_luyr24Up#
digit

until [[ "$type" == "upper" ]]; do pwd=`./genpwd.sh |tail -1`; echo $pwd;  case ${pwd:0:1} in [[:lower:]]) type='lower';; [[:upper:]]) type='upper';; [0-9]) type='digit';; *) type='something else';; esac; echo $type; done
qFT7xqr9Ar_VpO$
lower
c8tO0fUG#UA3ex_
lower
LMxeA#zI5Q_yx65
upper

until [[ "$type" == "something else" ]]; do pwd=`./gen
pwd.sh |tail -1`; echo $pwd;  case ${pwd:0:1} in [[:lower:]]) type='lower';; [[:upper:]]) type='upper';; [0-9]) type='di
git';; *) type='something else';; esac; echo $type; done
GT72I_w7KA#Ye1o
upper
jtX$W3OEBQ$QLz8
lower
zt7sf_g#bb6L11O
lower
pt8GRXu3#Cpg_BG
lower
4Sox_8_r37AXwXn
digit
88Enb0RO_F7$$Vo
digit
7M_J8_IU_1_hseE
digit
Pme7J4j6H4W_kb_
upper
kYv_j7Dy7_AH6p$
lower
86wFXf_kc$byjuJ
digit
Pu8N$#meHOb9FuT
upper
R5YHp$$6MFYxc0M
upper
_r52lXz7$oF_zC5
something else

A Brief History of My Hell in Dealing with Oracle Tech Support

What follows is an excerpt from my flagship political blog (I have a journal format.) Probably every Oracle DBA has his own Support stories. I remember when I worked on the West Coast, a number of us would wait until after Colorado tech support would roll off around 9 PM, so we could access more competent Australian Tech Support. I remember one particularly obnoxious moment when I met some in passing. I was in an "Oracle University" class (back when I was an Oracle senior principal in the GEHS pratice); as I recall it was an advanced replication course. There was a string of them sitting in the back of class, cracking "dumb client DBA" stories. It wasn't just their incompetence and lack of professionalism: they would outright lie about customers (to gullible duty managers).

I remember one slanderous, totally baseless allegation about me, that I had subjected one female analyst to verbal abuse (swearing, cursing, etc.) She was reportedly so emotionally distraught that her duty manager had to send her home for the day. The best you can say about Oracle on this is that if it happened it was a case of mistaken identity. I keep my cool even under adverse circumstances like terminations. [I sometimes say that I must have this Howdy Doody personality, because people will pick me out out of a crowd to ask me to retrieve an item from a supermarket shelf, ask me for directions, etc.] I spent 8 years in academia, teaching literally hundreds of students. I had to deal with all sorts of rogue student behavior (probably the most outrageous was when I warned students about my academic dishonesty policy (without identifying the students in question), and the young woman (in the most ludicrous self-incrimination I've ever heard) guessed (correctly) that she was implicated and had a complete emotional meltdown in lecture). I never singled out a student in front of his classmates; if a student asked a question that I answered 5 minutes earlier, I didn't make a fuss over it; lecture him about not paying attention, etc. It was just easier to repeat the answer. Now I might push back on something wrong an analyst said, but yelling insults or swearing at people is not in my personality. I don't see it as productive. If I had to deal with an unpleasant or incompetent analyst, I would escalate the issue to a more senior analyst. I never went to a duty manager to lodge a complaint against a specific analyst; I was always more focused on getting the underlying issue resolved and once the rogue analyst was out of the way, I figured it was Oracle's problem to deal with him.

Much of my dissatisfaction was not having access to Oracle's internal knowledge base, and I was frustrated because many of their analysts didn't know how to query correctly. In particular, in reference to the 12.2.1.3.0 SR I described below, there was an ultimate fix of modifying a configuration parameter. So I must have gone through a handful of Engineering Support analysts, but the last analyst solved it like in 10 minutes, noting that another government client with an ODA (HHS?) had run into the same issue in an upgrade from 11G to 12C. and at some point they had had done a bare metal restore.

One of the things that particularly annoys me is the busy work often required in filing SR's, even when you're asking Engineered Systems when is the next patch going to be released (which was always futile: it'll be released when it's released). In part, I was having to answer to a government auditor wanting to know why there was a lag between CPU patch releases and ODA bundle patches. So Oracle was often wanting like 5 GB of logs, etc. uploaded, and it seemed like maybe 25% of the time their upload process was broken--and then, of course, they would never do anything with the logs but it's what they needed to punch your ticket.

I've been a professional Oracle DBA for 26 years. It would take probably a dozen posts of this size to run through all the issues I've encountered with Oracle Support, but the following is a good start:

I'm going to attempt to be as readable as possible in describing a couple of Oracle Tech Support scenarios. I'm sure almost every reader has their own past issues with tech support. Let me describe the typical Oracle Support scenario during the late 90's when most issues were still handled through calling an 800 number. (Oracle Support is not "free"; don't quote this percentage, but it's close enough: maybe around 22% of your license costs annually.) Oracle has a support portal, originally called Metalink, more recently My Oracle Support; this includes a knowledge base/search engine of notes, etc., on error conditions, best practices, and other material. . Oracle Support has access to a superset of KB called webiv, including information not available to Oracle customers.

Oracle trains a lot of inexperienced analysts just out of college. So they may take your issue symptoms, feed it to webiv, maybe pull up 40 or so hits, almost all totally irrelevant to your context. And they want to go through each link in order (and demand you provide evidence that the irrelevant citation doesn't hold); if you push back on any of that, they'll accuse you of having a bad attitude and threaten to close the TAR (technical assistance request)/SR (service request). So in the real world as a production DBA, I don't have the time or patience to deal with inexperienced analysts; what helped was escalating the issue to a senior analyst, I can think of at least a couple of dozen occasions where a senior analyst would resolve my issue literally within 5-10 minutes.

Oracle Support duty managers hate me with a passion because in their view, I was escalating issues too frequently (not true, but that's a judgment call). For the most part, I consider duty managers as not so bright self-important incarnations of the Peter Principle. Keep in mind none of these people ever directly interacted with me. But a couple of telling incidents; while my boss was out of the office, the switchboard forwarded a screaming duty manager to me, and this guy, clueless that he was talking to me, started lying about me in his rant. In a second example, while I was a senior principal (highest non-managerial rank) at Oracle Consulting, a hostile duty manager called my practice manager and promised to help recruit my replacement if he would fire me.

I think I've previously discussed my initial bad experience with Oracle Support I think around the summer of 1996 working as a subcontractor for a large credit card issuer in the St. Louis suburbs. The company probably had the worst setup for new people I've ever seen. I was assigned to a cubicle over the next several weeks where I didn't have access to voice mail or a network account, so basically I had to work at an open workstation out of sight from my cubicle. So, among other things, I wouldn't be able to hear or answer my cubicle phone--and even if there was a call that dropped to voice mail, I couldn't retrieve it.

So my boss, basically another contractor (different company) told me to call Oracle Support and not to settle for anything less than a response to a technical issue. (He headed the production DBA efforts (with OPS, a precursor to RAC, which involves multiple servers with access to the same database, effectively supporting nearly 100% uptime) while I maintained the (non-OPS) test database. The client had nearly 2 dozen "DBA's", being transitioned from mainframe roles, but basically contractors were doing the real work.

I called up Support and got connected to a Support analyst doing a form of computer triage; he determined that the issue I was trying to address didn't deserve same day service. The client had an expensive "Gold Service" maintenance agreement. I tried to argue the point, given my boss' instructions, but I got nowhere with this Oracle cog. In fact, he started ranting that I was violating Gold Support standards, and Oracle could decide to drop Gold Support for the client.

It didn't stop there. It turns out one of the mainframe DBA retreads served as the local Metalink administrator. So the analyst or his duty manager ends up calling up the client "DBA" and starts ranting over this rogue contractor and repeated the (empty) threat to revoke Gold Support coverage. Now client management (who really didn't know me) are pissed off at me; if my boss said anything to defend me, I didn't know about it.

The Oracle troublemaking bastards weren't done with me. As the reader may have guessed (given my explanation of voicemail access), if and when Oracle Support did call me back on the SR, it went to voicemail. Now the SOBs are going back the the client Metalink administrator and complaining that this rogue contractor is refusing to return their calls and they want to close the TAR. Do you think the client management takes responsibility for the fact after weeks, I still don't have voicemail access and fixes the problem? Of course not. It's easier to them to accept Oracle's complaints at face value. (As I recall, they let me go not that long after that. Luckily I had written into my contract that my employers would have to pay my moving expenses back to the Chicago area. The clients wouldn't agree for me to travel from Chicago.)

It would take me several book chapters to chronicle my issues over the years with Oracle Support. But I'll settle for a couple of recent examples that I think speak for themselves and illustrate the level of incompetence that I've had to deal with.

A few years back Oracle purchased Sun Microsystems; probably a good plurality of my first 15 years or so of professional DBA work dealt with Sun hardware and their signature Solaris (Unix) operating system, although I think their main motivation was to acquire rights to the very popular Java language.

One thing that Oracle did with their hardware acquisitions is develop the concept of a database appliance. Basically the Oracle database appliance (Oracle engineered systems) provided Oracle customers with a one-stop shop concept; instead of customers having to deal with vendors pointing at each other over technical issues, Oracle assumed that risk and provided comprehensive support and patching for everything from firmware to RAC database patching through ODA patching; unlike Oracle's quarterly security "Critical Patch Updates",  ODA patches (including CPU), in my experience, were released at irregular intervals from 6 weeks to up to 5 months.

So my first example was an issue that affected my development ODA. One of the 2 clustered servers would not update. As I seem to recall, it was eventually resolved by replacing  a configured parameter that affected ODA's that had undergone a prior bare metal reinstall (done by a predecessor in my role), But when I initially filed the SR, the Oracle Engineered Systems Support analyst denied the ODA bundle patch in question (it may have been 12.2.1.3.0)  had been released, demanded to know where I got this unofficial (beta) software, and reminded me Oracle Support didn't support beta software. I got the usual presumptuous lecture (I think it's a script they all memorize) that I need to read ODA master note 888888.1; I knew 888888.1 better than any Support analyst I ever dealt with.

I have no idea how bad internal communications are at Oracle Engineered Systems, but I have no clue how Support engineers were unaware of a released patch. But 888888.1 noted around the transition from 12.1 to 12.2 ODA patching, that client DBA's should identify new versions from release note links on the OES help/home page. [Think that Oracle Engineering would send a new bundle patch alert to their customer distros like for CPU patching? No, that would make too much sense.] (Eventually 888888.1 would be updated to link to the latest patch.)

So I patiently tried to walk him through the publicly available release note link to the release notes and the hot link in the release notes to the platform patch download. But there was no reasoning with him; he was in a state of denial. I think I ended up having the SR reassigned. This was embarrassing for Oracle because you can't explain away that kind of incompetence.

The second example requires knowledge of an overview of ODA patching from the original OAKCLI (command line interface). There is an unpacking operation of the ODA patch from typically 3 zipfiles to local root-accessible directories. Then one basically does version updates of up to 3 components: system, storage, and database.

So there was no 12.2.1.5.0; we had to go from 12.2.1.4.0 to 18.3. This was a major change in ODA patching for my version ODA: we would be transitioning from OAKCLI to the DCS model with an ODACLI language during the system component upgrade. The documentation then outlines (supposedly) the process of using ODACLI to patch the storage and database components.

It turns out that there's a separate zipfile/"updating the repository" [unpacking] step for the database component. And the DOCUMENTATION STOPS THERE. (The last time I checked a few weeks back they still hadn't modified this.) Conceptually unpacking the software/updating the repository doesn't involve applying the patch. You know, is the emperor wearing clothes? On my own, I discovered the missing piece (a verb to update the database home) using a published OAKCLI/ODACLI cross-reference. I'm trying to explain to the Support analyst: "Dude, my RAC software hasn't been updated from the April to July CPU." I had already filled in the missing pieces. I  thought they had a duty to fix the documentation so DBA's following me wouldn't have to resolve the issue on their own. As a documentation guru, I had no idea how they could leave their documentation incomplete during a one-time migration process. This is not rocket science; they simply were too incompetent to fix their own mistakes, even when a customer pointed out the problem and what to do to fix it.

ODAs, CLI Documentation, Deinstalling an Oracle Home, A Datapatch Issue on Windows

I'm sure that most of us who work with Metalink/MyOracleSupport have similar experiences. For instance, recently I have been with Oracle Database Appliances--sort of a one-stop for clustered servers/hardware, firmware, storage, OS and database software. We don't patch components individually on ODAs. Instead, Oracle releases interoperable bundle patches (firmware to database software), which have ranged in my experience 2 to 5 months between releases. This is unlike the usual quarterly critical patch updates with prescheduled releases.And of course CPU's for ODA's have to be integrated into interoperability testing, so there's usually a modest implementation delay. Unlike for CPU's, I don't get email notifications for new ODA patches, basically Doc Id 888888.1 tells us to check release notes via an ODA release page for new versions. On a recent release, I couldn't find the patch through the general patch portal and later found the patch hyperlinked in a subsequent section of the release notes. So I was literally having to check the release notes daily for months between patches. Contacting Support wasn't much better. When I tried to open up a Service Request on the latest patch, the analysts were in a state of denial that the software had been released; one guy accused me of filing a ticket on a beta release.

Command line interface documentation is sadly lacking in a number of respects; for example in dealing with ODA patches, you have to use oakcli; on the other hand, if you run into a hardware issue (say, a defective redundant power supply), you have to use a different CLI in logging into ILOM, e.g., in getting diagnostic logs for filing a hardware SR with Oracle. The CLI command syntax is not as fleshed out as you might expect, e.g., in Oracle database command documentation. For example, I once had to shut down a service on ILOM. I only found one passing example of syntax to start up the service in question.

I once ran into an issue on a Windows backserver for Oracle. (Luckily, this was not a production server but a testbed). I had upgraded the server (and my 3 databases) from 12.1 to 12.2. The bottom line is our vulnerability scanner showed the old Oracle home was not running the April 18 CPU, and I had to uninstall the old home. So of course I started up OUI, and it told me I had to run a certain utility at the command line. In the command sequence, it asks for a list of databases. Why? None of my databases were running on that home. There wasn't any warning, say, that it would not only go out and drop the database but also any related backups in FRA. This totally ignores Usability 101: design for human error, i.e., you make users jump through hoops in order to do something catastrophic like drop a database. (And that's what the prompt option did.)  I don't mind if Oracle provides a separate data utility, but I didn't expect it in a deinstall utility. I lost a database before I realized what was happening.

On the same Windows server. I ran into some issues running datapatch -verbose (the utility you run during the second phase of opatch, applying the new patch against the database in startup upgrade mode), Long story short, the problem had to do with an xml_file.xml file, which needs to be deleted but was being held by certain Windows processes (in my case, a set of cmd processes). So my solution was to delete those cmd processes in Windows task manager (and then said xml file). At that point, expected functionality returned to datapatch.

Practical Tips I: Compressing Files

Quite often in a consulting scenario I'll find myself in a situation, particularly working for the government, where I'm dealing with tight constraints and need to find space. I remember when I was sitting on several gigabytes of archived logs going back 4 years. I think the oldest backup image datafile (yes, the client wasn't running RMAN) was maybe 6 months old (now, in practice, I've never gone past since my last usable backup, even for doing a clone or refresh of test or development), but the point is that even if the client wanted to clone the production database as of 7 months ago, I didn't have the necessary datafiles. But I couldn't get a client manager to give me the OK to purge 3.5 years of unusable archive logs.

In a recent situation, I needed to find enough space on my /u01 and /opt mountpoints to make the prerequisite storage requirements for running a major bundle patch. Here's one tip: look for audit files, something other DBA's don't realize until one day they find themselves facing a full file system. One source is to check your admin folders for adump, e.g., /u01/app/oracle/admin/[SID]/adump. If you're running on a grid (clusterware/RAC), also look for an audit directory off $GRID_HOME/rdbms.

So what do you do when you run into wildcard problems because you have too many files in your directory? (I'm assuming here that you are running on Linux or a Unix variant.)  I love to do DO LOOPs; there are a variety of things you can do (including moving files where you have more space), but if you delete files after zipping them, you should reclaim available space throughout the process. So, for example, assuming you are running in the audit file working directory

ls | while read fn
do
zip aud.zip $fn
rm $fn
done

Now there is a complication in Linux: what if you have say a file name preceded by a minus sign, e.g., like I've seen in grid audit files. A practical solution is to work "-- "" into the command syntax, e.g., ls -- *.aud

I'm a fan of using the job scheduler in OEM 12c. (I find the interface design somewhat quirky and non-intuitive to launch job creation off the bottom of the screen.)  Now one of the things I frequently like to is date-stamp a file (say, for instance I want to run a periodic [weekly] audit file sweep indicating the end of the audit file period), e.g.,

cd [audit file directory]
export zf=aud`date +%y%m%d`.zip
zip $zf    *.aud
rm *.aud


Now this script will not work (in my experience) through OEM job scheduler, and it has to do with the use of %. So what you want to do is to repeat the '%', e.g.,

export zf=aud`date +%%y%%m%%d`.zip

Why I Became an Oracle DBA

I've made my living as an Oracle DBA since 1993. I've often had auxiliary responsibilities, e.g., development, tech lead, ERP administration and system administration, but my primary responsibility has been as a DBA. Did I grow up with the ambition of being a DBA? No. I grew up in the era of mainframe computing. In fact, I had to use card punch machines to write my first computer programs (FORTRAN) in college; My college didn't have its own computer, so I had to submit my card decks to a sister campus in San Antonio. (There was a 3-day turnaround, so I could only afford to make one mistake for the weekly assignment.)

Originally I thought I  had a vocation for the Roman Catholic priesthood, which is why I attended OLL, a private college. (I was an Air Force brat, and my family at the time lived at a military installation about 150 miles south of San Antonio. I didn't have a car or a license.) I didn't necessarily want to be a diocesan priest, like my maternal uncle. I had done well in school and naturally thought about teaching as a career. I did think of joining a teaching order of the Church, e.g., the Jesuits or the Oblates. In fact, I had a preliminary interview with the Jesuits at my college dorm; I don't think they ever followed up.  Originally, I had intended to major in secondary education and probably would be a math/science high school teacher in Texas, except I fought my academic adviser to get into a philosophy class. (My adviser wanted me to take art history; but philosophy is a common major for prospective seminarians.)  That course changed my life; philosophy remains my one true love. I transitioned to a double major (in math and philosophy). But philosophy is not a core reference discipline for secondary education. More to the point I had progressed from wanting to be a teacher to becoming a professor. There was the one experience that cemented the idea in my mind; I went to visit one of my principal philosophy professors, an Oblate priest, in his office. I walked into a room where he was probably playing his favorite, Leonard Bernstein, on a record player, pungent pipe smoke filled the room, walls crowded with books: making a living talking about deep thoughts sounded like heaven on earth.

Unfortunately, beyond teaching, there aren't many professional opportunities for philosophers, and job opportunities for philosophy PhD's were extremely limited and competitive--basically one had to hope for existing tenured faculty to resign or retire. (A number of people had frank conversations with me about the marketability of my philosophy degree.) So I shifted to consider becoming a math professor, In fact, one of the RC sisters/nuns who had earned her degree at a Louisiana university had lobbied to get me considered for its PhD program; I was verbally promised a graduate student financial aid package, but to be honest, I had my sights set on a more prestigious program at the University of Texas at Austin (where my academic adviser had earned her PhD). Another case  of woulda, coulda, shoulda. I almost instantly got into political trouble as a teaching assistant to a visiting East European professor from Ohio State. (The students were at a point of rebellion; he was difficult to follow and had unorthodox tests. As a teaching assistant, I had 2 problem solving class session classes, and the students were basically unhappy with me as a surrogate to the professor, feeling maybe I wasn't doing enough to prepare them for his exams; to me, the definitive moment was when a coed, the best student in class, literally threw her second exam in my face as she left the room. (It's not like I knew what he was going to ask on the exam.) Another classic moment: one day he starts writing on a series of blackboards in the front and then on the right side of class. The students were just mechanically just jotting down everything he was writing; he wasn't really lecturing; he was just filling up the boards without comment. Suddenly, about 8 boards into his writing, he seemed to get lost in whatever he was trying to do; he looked up at me and sheepishly asked if I knew where he was going with this. (I didn't.)

The other TA set me up, saying we had to have an intervention. We agreed I would start. The professor was troubled by what I had to say and asked Jeff for his comment. Jeff turned on me, effectively saying, "Yeah, a few students are complaining, but you get some of those malcontents in any class." What the hell? I had no idea why Jeff had set me up like that, but the professor decided that I was the one with an evil agenda and became obsessed with me. I'll never forgot how he followed me into my graduate real analysis class and started bitching about me to the professor. It then got even worse; I got called to the department chair's office where I got yelled at and threatened over my "disloyal" actions. I think I knew at that moment I would never get my terminal degree from UT and would have to settle for my MA.

There were other considerations, too. It turns out there was a glut of mathematicians on the market and newly minted PhD's, if they could find a job, would have to settle for some obscure state branch university. So they decided to raise doctoral qualification to 6 exams with limited retries and a time table. I had befriended a married couple in the department; she got through her 6, but her husband was dropped from the program lacking the final sixth exam. (I never really sat for the exams, but they had become a moot point: during the spring semester it turned out I had lost my teaching assistant position for the next year, and I really couldn't afford to go to school. I got a token grader job in my last semester working on my Master's thesis, but I had to ask my maternal grandfather for a $500 loan so I could have it professional typed to department standards. (This was in the days of typing, and you needed specialized equipment to print mathematical equations.)

My job prospects were limited. I originally thought that the Air Force was going to offer me a commission and have me trained as a meteorologist, but I was passed over in the selection pools. There were no local community college instructor jobs. I tried to check into trying to pick up high school teaching credentials, but I didn't have the resources.

Now most math majors at the time almost inevitably minored in computer science, but UT had an excellent philosophy program, and it was a no-brainer at the time. Now it was a lost opportunity, as I found out a pure (vs. applied) math degree wasn't that marketable. Long story short, I eventually got a computer programming trainee position at a prominent insurance company in San Antonio. They were having problems training other programmers (say, COBOL) to write in APL, a mathematically notated interactive language that IBM had developed for rapid prototyping; APL was often used in areas like property actuary at the insurance company. There's another story behind that experience; let's just say I taught myself APL well enough to attract a job offer with a substantial increase in salary the next year from the largest APL timesharing vendor in Houston. (The timesharing business model was to sell premium-priced computer time running customized enhanced APL applications; this was a time when buying another mainframe meant big bucks, plus it was a workaround to in-house application backlogs.) The introduction of the PC was a disruptive technology; by the time I earned my MIS PhD in the mid-80's, the companies I had worked for had all gone out of business.

After about a year of living in Houston, I started looking at picking up an MBA at the University of Houston, which had a part-time nighttime program which I could fit into my work schedule. (I also hoped that it might improve my dating prospects; in fact, I did date a few coeds but nothing serious.) By my second year I was aware that the business school had a PhD program in MIS, which reopened my deferred dream of becoming a professor, was admitted and eventually got a part-time teaching stipend (2 classes a semester), enabling me to study full-time.

One of the attractions of the discipline was MIS classes were "hot"; I used to joke that we could offer "Basketweaving for MIS" and hit a class limit of 35 in 10 minutes of registration. Business schools desperately wanted newly-minted PhDs; granted, the land grant name schools were highly competitive (our first 2 graduates went to Arizona State and Iowa State), but when I first hit the academic market in the mid-1980's at conferences associated with two competitive doctoral consortia, I literally did not have to dial a phone; I fully booked every 30-minute slot available and probably could have booked at least 2-3 more days of interviews. That doesn't mean I got dozens of campus visit offers (prerequisite to a job offer; it usually involves a research presentation and several potential colleague/administrator interviews. I usually got 2-4 campus visits while I was on the market and 0-2 offers.)

I had started my MIS doctoral coursework by the time I had finally finished up the MBA in 1983; I had taken two database classes under Richard Scamell, easily one of the best professors I've ever had (and my future dissertation chair). Scamell had a contact at Oracle; this was around the time of Oracle 2, when documentation came in three-ring binders and SQL*PLUS was called "UFI" (user friendly interface). UH was running Oracle on an IBM-compatible mainframe. What I particularly remember is that we had a hard time doing our database projects due to lousy uptime; in fact, the entire class had to take incompletes that fall semester, I think that's the first time I had really encountered DBA issues or was familiar with that role. I don't think at the time I knew it would become a future career; recall, I was convinced I would a professor until retirement. Still, of all the roles I would deal with as a professor (systems analyst, etc.), a DBA role had the most appeal.

My dissertation was not on database, but my research interests included human-computer interaction, including the use of SQL, the de facto standard query language behind relational databases, like Oracle, DB2 and SQL Server. I also had a focus on documentation. One of my office mates back at UH, Minnie Yen, did look at query languages in her dissertation, so around the turn of the 1990's as my academic career began to wind down, I had proposed a joint project, my writing a "minimal manual" on learning SQL for a research project including one or more of Minnie's students.

I didn't get a chance to teach database during my 3 years at UWM, because 2 of my tenured colleagues who did more technical database research "owned" the courses (although I seem to recall they were using popular PC data applications, not SQL. I think while I was there Oracle had decided to roll out a PC bundle for Oracle 5 for roughly $200 (not that bad considering you got a huge stack of paper documentation with the bundle), and I bought a copy; however, I don't think Oracle was installed at any of the 3 universities I taught at as a professor.

I finally got a chance to teach database (and, separately, data structures) at UTEP; we didn't have Oracle, but I was determined for my students to have exposure to SQL--and used an option similar to when I taught structured 1985-standard COBOL using textbook bundled Ryan-McFarland software. In this case, the textbook I chose came bundled with a limited version of XDB-SQL.

UTEP had hired me principally because they were up for AACSB accreditation and wanted my research vita/resume at file (I'm not saying that I was a deciding factor, but I would be a second published MIS researcher in the department), and I soon discovered that the department chair (it was a fusion department, including production logistics faculty) quickly reneged on key promises and probably the most obnoxious department chair I've ever met. I'm not going to go into specifics here other than my stay in El Paso was a hellish experience, so much so I went on a campus visit in Louisiana during finals week of that first fall semester. (It was not my idea; I wanted to defer it to the spring. For some reason, they refused  And then they decided not to make an offer, which I would have accepted.) By the spring, it was clear that UTEP wasn't that thrilled with me, either. My options were limited by that time because most of the colleges had done their screenings at the late fall conferences. It turned out that Illinois State had a temporary 1-year appointment available because one of their applied computer science professors was trying to set up a research facility.

As in the other two universities, there were some nasty office politics at play. I went on the market, but I found that the market had drastically changed. There were probably 3 reasons: (1) to help the regional schools unable to hire new MIS PhDs, Indiana University offered a summer program to retrain other professors (e.g., PLM, QMS) to MIS,  (2) there was a bumper crop of new PhD's; for example, I had leapfrogged 12 ABD's to graduate #4 (#3 defended her dissertation one week before me); almost all of these went through as UH warned them they might need to re-qualify as their 5-year clock ended, plus others I had studied with joined the market; (3) MIS class enrollments started to cool off. I had noticed this at UWM, where classes of 35 the next year had available slots, like 28 enrolled. Also, there was a recession, and even computer science graduates, who normally didn't want anything to do with business schools, were submitting their vitae.

The market had radically shifted; schools that normally got a dozen resumes and hoped their top 3 would be available suddenly got flooded with 85 resumes, so then they quickly had to adapt. It depended on the school but they developed filters which didn't necessarily favor me, despite my research and teaching record, not being an ABD. Maybe, for instance, a male-dominated department might want to recruit a female candidate (I know one school that did this), there was a demand for computer networking faculty, etc.

I think I did get 2-3 campus visits, but no offers. Ironically, Illinois State was in the market, mostly to cover the service course (I was working for a hybrid Applied Computer Science department). Initially they wanted to talk to me, but I expect that some campus politics came into play, and they refused to return my calls.

I didn't realize it at the time but my academic career was over. I did get an offer in 1994, but the offer came several weeks after my visit, and I had just started a new job; a lot of professional recruiters were worried that I wanted to use them until the academic market recovered.  I was between jobs when I went on the campus visit, and I made it in good faith. They had led me to believe that I would get a status in 2 weeks and that didn't happen. (Of course, it's possible that they ran into bureaucratic inertia) It certainly wasn't the money; they were offering the highest figure I ever got from a school and more (by over $10,000) than I was making in my new job. This would be the last time I got a serious offer. I came close to joining the faculty of a Catholic college in the DC metro area a few years later. That, ironically, was probably my biggest disappointment; I have a BA from a Catholic college; I went on campus visits to 4 Catholic colleges and no offers. It wasn't money; for example, Providence College made it clear the max they would pay is $35K, far lower than my state-sponsored offers.

So looking for work, I had to look back at returning as an IT professional. (An industry research position was not an option; for example, IBM at that time was struggling.) As mentioned above, a lot of IT recruiters saw me as trying to use the company until an academic offer came along; it didn't matter what I said. Others discounted academic computing experience and considered my prior job skills as probably eroded. Still others refused to consider me for anything beyond entry-level work.

I finally found some APL work at an IBM subsidiary in the Dallas area, but I was really focusing on reinventing myself as an Oracle DBA. The problem was chicken or the egg in terms of experience. Finally, an EPA contractor was willing to offer me a position at the Chicago regional headquarters. They paid me peanuts, but I got my foot in the door. On literally my second day on the job, they lost their recompete on the contract (which basically meant I would be looking for a new job in a few months). I got my first extended gig at a private company which is now an Equifax subsidiary.  I spent 2.5 years there (and would probably still be there if the owners didn't sell). [The CEO worried that Accenture was a threat to their business model.] About a year into the job, I got unsolicited calls from a Coopers & Lybrand recruiter helping set up an Oracle Apps/EBS practice. I essentially transitioned from an in-house DBA to a consultant by 1997 and have mostly served in a contractor role since 2004.  I may expand on my experience in future posts, although my first blog post features some insights from my specialty as an Oracle Apps DBA.

Tip: You Should Consider Purging the DBA_Recyclebin

The Oracle Recyclebin feature introduced with version 10G is somewhat analogous to deletions from Windows recycle bin functionality. Essentially Microsoft obfuscates the location of the file and also marks its storage as available for other use by the system. There are third-party tools which may allow you to recover them, although the probability of recovery declines over time as the system has need for available space for other purposes. In a similar way, when an authorized Oracle user drops objects like tables and dependent objects, like indexes and constraints, they aren't necessarily removed from storage (assuming a default recyclebin parameter ON/TRUE); they are essentially renamed with a BIN$ prefix. You can identify the current OBJECT_NAME from DBA_RECYCLEBIN using OWNER, ORIGINAL_NAME; you can recover by FLASHBACK TABLE owner.original_name TO BEFORE DROP. (Note that there are some nuances and exceptions to recycle bin functionality (e.g., recovering dropped schemas or objects of the SYS user) and limited recovery options.) Also note that the ability to recover dropped objects lessen over time as space is needed for other purposes (e.g., inserts into same tablespace tables or user space quotas).

Oddly enough, the recycle bin issue came into play in an indirect way. To provide a context, the virtual container recycle bin will grow over time. You can control this in various ways, e.g., turning the initialization parameter off or executing the purge command.

In my current position, we often do selective schema exports (in addition to regularly scheduled exports or database backups) on request for development. These exports are scripted to the extent that fellow privileged administrators with limited Oracle background can run them. We also administer multiple databases that are similarly designed and structured, and the export task is repeated across database servers.

The wall time for the 2-schema export task run manually is for most servers within 15 minutes [Note that in this context, we are discussing data pump, i.e., expdmp, not exp.]. One day a system administrator ran the ad hoc task request on a third database instance when the export of the second schema seemed to hang for literally 3.5 hours. I analyzed the alert log; I tried export alternatives (if I excluded statistics and the table filter in the export parfile, I could do the export in less than 15 minutes). Development wasn't happy with the delays and pointed out no other DBA's were reporting similar issues with their servers; it must be local administrator "incompetence". It wasn't obvious why this server was behaving differently from the others; they had similar configurations, applied patches, etc.

I currently don't access to Metalink/My Oracle Support; I tried to search on abnormally long exports or statistics using Internet search. (Why did I pursue that? Because it seemed to be hanging on the initialization stage referencing statistics: ") Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS")  But most of the problems I found focusing on statistics were on the import, not the export stage.

A DBA colleague had stumbled across a discussion of dba_recyclebin. I don't know which source he found, but I have found this discussion on data migration using data pump which advises (without explanation) purging the DBA_recyclebin before running expdp.

What we found is literally thousands of objects in the recyclebin, the vast majority of which existed in the problem schema. My colleague ran purge dba_recyclebin from the internal account as sysdba, and the proof is in the pudding: the subsequent wall time for the problem schema export shrank to about 3 minutes.

It's still not clear why the dropped object problem was peculiar to this database instance given standard installation, patching and scripted application functions. But if you don't disable the recyclebin default, at least create and schedule periodically  a purge table script from DBA_RECYCLEBIN (e.g., using droptime filter). Chances are, you can't count on being able to recover long-dropped objects anyway.