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