A Unix Shell Script for Generating CSV Files from Oracle

Over my last several gigs, I was once asked to generate CSV's from a key database schema that could be loaded into Excel spreadsheets and archived for an Oracle database to be decommissioned. The context here is for a 12.2 Oracle database running on a dated version of Unix. Note that the solution I provide here doesn't include complexities of BLOB, CLOB, or other special field types.

There are some complexities in the context here. One is that although 12.2 and above have a CSV markup mode that can facilitate generate of simple structured tables in sqlplus, I ran into practical issues of a maximum size of concatenated strings (say, >4K character records) and embedded line feeds and ca::rriage returns (e.g., emails in varchar2 fields) which resulted in a violation of the one line per record objective. So I used the Unix shell tr command to strip these from the target file--but that left me with the problem of how to terminate the records. So basically I used the tilde (~) at the end of my concatenated string to  mark the end of records and at the end replace the tilde's with line feeds using the tr command. This left me with another issue to consider: what if tilde's are embedded in your column data? I use the Oracle replace function to replace any tilde's in the data; if carriage returns, line feeds, and/or tilde's cannot be stripped or substituted, you may need to use a different approach.

I generate a PL/SQL script on the fly basically to work around the concatenated string limit in sqlplus; I use a caret (^) as my field delimiter and enclose fields in double-quotes. The first line of the CSV file consists of delimited column names. I assume here the use of a DBA-privileged OPS$ account (i.e., sqlplus / ) with the SID and Oracle binaries set in the environment and you are working in a write-privileged working directory, that you have sufficient storage available to accommodate multiple working copies of the target CSV file.

 Finally, I assume an external file tablist in the working directory, consisting of capitalized target schema table names (one per line) with the schema name overlaying SOME_SCHEMA in the script.

#!/bin/bash

myschema=SOME_SCHEMA

cat tablist | while read newtab

do

echo "set heading off" > myscript0.sql

echo "set echo on" >> myscript0.sql

echo "set feedback off">> myscript0.sql

echo "set pagesize 0" >> myscript0.sql

echo "select column_name from dba_tab_columns" >> myscript0.sql

echo "where owner='${myschema}' and table_name='${newtab}'">> myscript0.sql

echo "order by column_id;">> myscript0.sql

echo "exit;" >> myscript0.sql

sqlplus -s / @myscript0 > mytab

i=0

cat mytab | while read myrec

do

if [[ $i == 0 ]]; then mystring2="${myrec}";

else mystring2="${mystring2}^${myrec}"

fi

((i=$i+1))

echo $mystring2>mystr2

done

`

pstring=`cat mystr2`

echo "set linesize 32766" > newscript.sql

echo "set serveroutput on size unlimited" >> newscript.sql

echo "DECLARE" >> newscript.sql

echo newtab $newtab

echo "TYPE arraytable is table of ${myschema}.${newtab}%rowtype;" >> newscript.sql

echo "myarray arraytable;" >> newscript.sql

echo "CURSOR c IS" >> newscript.sql

echo "select $mystring from ${myschema}.${newtab};" >> newscript.sql

echo "BEGIN" >> newscript.sql

echo "OPEN c;" >> newscript.sql

echo "LOOP" >> newscript.sql

echo "FETCH C BULK COLLECT into myarray limit 10000;" >> newscript.sql

echo "FOR i in 1 .. myarray.COUNT" >> newscript.sql

echo LOOP >> newscript.sql

echo "DBMS_OUTPUT.PUT_LINE(" >> newscript.sql

j=1

cat mytab | while read mycolumn

do

if [[ $j == 1 ]]; then echo "'\"'||replace(myarray(i).${mycolumn},'~','')||'\"'" >> newscript.sql ;

else echo "||'^\"'||replace(myarray(i).${mycolumn},'~','')||'\"'" >> newscript.sql ;

fi

((j=1+$j))

done

echo "||'~');" >> newscript.sql

echo "END LOOP;" >> newscript.sql

echo "EXIT WHEN c%NOTFOUND;" >> newscript.sql

echo "END LOOP;" >> newscript.sql

echo "END;" >> newscript.sql

echo "/" >> newscript.sql

echo "exit" >> newscript.sql

echo "${pstring}~" > temp.csv

sqlplus -s / @newscript >> temp.csv

nbrw=`wc -l < temp.csv`

nbrs=$(( ${nbrw} - 3 ))

sed -n 1,${nbrs}p temp.csv > ${newtab}.csv

tr -d '\r'< ${newtab}.csv > temp2.csv

tr -d '\n'< temp2.csv > temp.csv

tr -s '~' '\n' < temp.csv > ${newtab}.csv

done

Snipping the Alert Log and Working With Unix

 For over 20 years as an Oracle DBA, I have worked with multiple versions of Windows as my backserver platform, but primarily with several versions of Unix (Solaris, HP/UX, AIX, and Digital Unix) and Linux. I have written and/or maintained hundreds of Unix shell scripts, including Bourne, Korn (my early preference), C shell, and bash. I'm so familiar with scripting that I routinely use on my Windows PC's at home; I licensed a copy of MKS Toolkit years back and eventually transitioned to Cygwin and/or the Windows 10 Subsystem for Linux. It's difficult to explain all the ways it can be useful, but for instance I might want to timestamp filenames to ensure uniqueness and facilitate archival, search and retrieval. I can grep textfiles, use the powerful, flexible vi editor to edit, or utilize tools like sed and awk.

An exhaustive review of the practical issues I've faced with Unix and Oracle in professional life is beyond the scope of this post. It seems every day spawns some practical issue. Let me give a simple example. In a traditional Oracle auditing, plaintext audit files of SYSDBA actions are accumulated in an audit destination directory.  Security guidelines may require encryption of these files; they may also require these be maintained for at least a year. The first time I tried to list all the audit files for encryption (e.g., *.aud), I got a "too many files" message. There are some workarounds; e.g., ls | while read fn; do ls -l $fn; done or find . -exec ls -l {} \; .[The encryption logic is separate but think of operating within the do loop or exec logic. For example, you can filter audit filetypes and/or reorganize those files.].

One will often encounter nuances. A recent example was seen in my 2 recent posts on generating randomized Oracle passwords from shell script where the first script could run in Linux and Cygwin, but not in Solaris at work.[Yes, I know you can do similar things using Oracle functionality. I might cover that in a future post.]  One recent example dealt with a simple concept of working with the date function. What's the motivation?  I am aware there are tools like Oracle Enterprise Management Cloud Control which can be configured to alert DBAs of exceptional conditions. But there might be reasons why OEM isn't installed at a client location and/or you need to run some kind of custom alert, often based on Oracle warnings or errors, appearing in the database alert log.

You might think DBAs check alert logs, but let me give an example from personal experience. While I was living in Santa Clara, I worked for a consulting company which had been engaged by a television station in San Francisco. They were running Oracle Applications/E-Business Suite, one of my specialties. Certain email notifications from the applications weren't happening, and they couldn't figure out why. They had engaged consultants from Redwood City (global home for Oracle, although they recently announced they were relocating corporate headquarters to Texas) who hadn't resolved the issue. So, of course, the first thing I check is the database alert log--and I immediately noticed that a certain tablespace had been trying to extend literally for over 4 months, including the Redwood City engagement. (I would also assume they got nowhere with Oracle Support.) I immediately added or extended a relevant datafile and long story short, it resolved their functionality issue.

Now really I might not need the whole alert log (which could go back several months). I want to snip off the current day's entries (if any) from alert log. (I can then grep the snipped daily file for warning or error patterns; I'm not including that in this post.)

Note that in my snippet logic below, the timestamp format can differ, even among two Oracle servers at work, so you might need to tweak the date format.

But a simple question motivated the post: what if you want to snippet more than today's entries? Or maybe you want to track any entries since close of business yesterday. One solution, which I won't describe in detail here, is to offset SYSDATE and transform the results via TO_CHAR and a relevant date mask, capture the result in an environmental variable or work file (say, turning off headings, pagesize, feedback, tweaking linesize, etc.)

Consider the  following excerpt from a Cygwin session (for common format strings, see here):

 ~
$ date
Sat Mar  6 14:42:06 EST 2021 ~
$ date -d "-18 hours"
Fri Mar  5 20:42:41 EST 2021
$ date -d "yesterday"
Fri Mar  5 14:43:01 EST 2021
~
$ date -d "-3 days"

Wed Mar  3 14:43:35 EST 2021

$  date -d "-3 days" +'%m-%d-%Y %H:%M'

03-03-2021 14:59

But what if you don't have a -d option like my Solaris system? You can use the TZ environmental variable, e.g., for yesterday,

 ~

$ TZ=$TZ+29 date

Fri Mar  5 15:03:24 America_New_York 2021

Note 29=24 hours + offset from GMT. For EST, the offset is 5 hours (4 for EDT)

Okay, for the (old copied) timestamp on my Solaris system in the alert log for format purposes:

Wed Mar 03 00:40:29 2021

So here's my bash script excerpt:

#!/bin/bash

export SCR_HOME=.../scripts

export ALERT=/oracle/app/diag/... /alert_sid.log

STR=`date +'%a %b %d.*%Y'`

export START=`grep -in "$STR" $ALERT| head -1 | cut -f1 -d:`

export ARG=$START,\$p

sed -n $ARG < $ALERT > $SCR_HOME/ndaily

where ndaily is the current day's alert entries

A Second Script For Generating Randomized Oracle Passwords

 A couple of posts back I published a password-generation script I wrote enforcing password complexity constraints (e.g., at least 2 each of lower-case and upper-case characters, special characters, digits) This was a general, easily customizable script; Oracle. of course, has nuanced password policies, depending on the product (e.g., middleware in running OEM cloud control); it might have restrictions, say, on the first character of the password  or restrict the use of special characters. 

You don't have to use a script, of course. For example, there are password generators for most password manager software (e.g., KeePass)) or online (e.g., my SQL Server colleague uses Norton). Why use a script? Well, for instance, I've worked in environments which restricted URL's and it's easier to integrate with related scripts. For example, when I worked as a NASA contractor, I used OPR via Source Forge with the idea of program/password independence (avoiding cleartext passwords in scripts). I've seen other ways of storing passwords, such as password-protected pdf files or spreadsheets (see, e.g., here).

There are other things that come into place into general password policies. For example, my Solaris administrator used a dictionary check to qualify OS passwords, and I found my original script didn't work for my Unix account. Another colleague, not using my script, ran into a similar problem, somehow triggering the dictionary check. I basically suggested that she try overwriting password vowels with consonants. So, for instance, from a script perspective, you might adjust the source character strings and random operator arguments to the lengths of the modified character strings.

Note that the use of special characters in complex passwords can be parsed by Oracle; usually you'll want to use double-quotes around complex passwords. One specific notable example is "@"; the familiar Oracle user knows that Oracle uses @ for Oracle networking connections, so if you don't use quotes, it might read the rest of your password as a net service name in tnsnames.ots. So chances are,  if you run into connection issues, you might have forgotten to use double-quotes in entering your password

A number of government agencies, for example, will require Oracle STIG compliance. The STIGs might require regular password rotations, e.g., every 2 months or sooner, a minimum length (say, 15 characters or more), complexity rules (a mix of character types), restrictions on password reusability, etc.

So what motivated a second password script? Even though the first script worked in Linux and Cygwin, it didn't work in the Solaris version of bash.  The key issue involved a way to scramble a string using all characters in the string. A command I used to do that in the first script wasn't available in Solaris, at least the version I'm using. So basically say I have a string of 16 characters, so the subscripts range from 1 to 16  (or equivalently 0 to 15). So I want a string of 1 to 16 in some random order, without repeating an integer and including every integer. Now I can easily generate one random integer in that range and run that process 16 times. But there's a good chance I'll run into duplicates: e;g.,

~ [Cygwin home]

$ for ((i=1;i<=100;i++)); do echo $(($RANDOM%16)); done

11

2

4

7

1

6

10

5

14

10

0

1

2

5

15

4

3

11

7

9

13

7

4

12

4

11

15

7

2

6

4

10

3

6

0

7

5

6

13

13

7

3

2

5

1

4

11

14

15

6

5

10

3

5

10

1

12

0

5

13

7

3

8

9

12

9

12

7

3

9

9

12

12

2

0

4

5

11

6

14

11

0

2

14

13

2

7

14

15

6

8

9

13

1

15

9

3

2

5

11

So the general idea is to generate a long enough string so each subscript (0-15) appears at least once and then in order of their first appearance in the string: using the above example, 11,2,4,7,1,6,10,5,14,0,15,3,9,13,12,8. I arbitrarily set the length of the string to 1000 characters. 

In this variation of the script I generate q 16-character password, include 4 characters each of 4 types (upper case, lower, digit and special) The password is first displayed to demonstrate complexity policy compliance by character type. I then reshuffle the characters by subscript as described above and display the password in final sequence.

#!/bin/bash

ls=abcdefghijklmnopqrstuvwxyz

us=ABCDEFGHIJKLMNOPQRSTUVWXYZ

ds=0123456789

cs=\#\$\_\@\!

ts=$ls$us$ds$cs

myctr=0

pw=''

npw=''

for (( c=1; c<=4; c++ ))

do

   p=$(($RANDOM % 26 ))-1

   pw=$pw${ls:$p:1}

done

for (( c=1; c<=4; c++ ))

do

   p=$(($RANDOM % 26 ))-1

   pw=$pw${us:$p:1}

done

for (( c=1; c<=4; c++ ))

do

   p=$(($RANDOM % 10 ))-1

   pw=$pw${ds:$p:1}

done

for (( c=1; c<=4; c++ ))

do

   p=$(($RANDOM % 5 ))-1

   pw=$pw${cs:$p:1}

done

echo $pw

for ((i=1; i<=1000; i++)); do echo $(($RANDOM % 16)); done | cat -n | sort -uk2 | sort -nk1 | cut -f2- > tmpsrt

cat tmpsrt | while read mysubs

do

npw=$npw${pw:$mysubs:1}

myctr=$myctr+1

if [[ $myctr -eq 16 ]]; then echo $npw; fi;

done

$ ./genpwd2

bbtuARCC9465@_$_

6_tC54C@Rubb$_9A

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.