KelloggsDBA

What is a users default shell

2022-01-11T01:35:00.001-08:00

username=myuser

out="$(grep "^${username}:" /etc/passwd)"

IFS=:

set -- ${out}

echo "Dir: $6"

echo "Shell: $7"

username=myuser

awk -v user=$username -F: '($1 == user) {print $6}' /etc/passwd

Grep this or that

2019-10-03T02:13:00.001-07:00

grep 'this\|that'

look for a subset of filesystems

df -h | grep 'this\|that'

Find newest file in a unix directory

2019-09-07T12:13:00.001-07:00

Files and directories have a number of attributes.

Modification time can be an issue when looking for the 'latest' directory. I wrote a shell script that was creating new directories and also writing files into the latest directory depending on the switches past in. I found if I started the two processes at exactly the same time. The oldest directory was modified last, completely stuffing up my sort later looking for the newest directory.

find . -name "partname*" -maxdepth 1 -printf '%T@ %p\0' 2>/dev/null | sort -z -nr

find
-name I only want certain files or directories
-maxdepth 1 only look at this level not recursively
-printf
%T modification time
@ seconds since Jan. 1, 1970, 00:00 GMT

%p file name
\0 the terminator rather than \n (newline) to avoid problems with strange filenames

2>/dev/null don't include errors in the output
| (pipe) then do the next command I give you

sort
-z because I used \0 in the previous command don't look for a linefeed
-n sort numerically
-r put the largest number at the top

But modification time was an issue so I shifted to ctime - which is change time (not creation)

change time –ctime %C
modify time –mtime %T

(access time –atime %A )

Change time is set on creation and alters when there is a chmod, chown or mv on the file.

find . -name "partname*" -maxdepth 1 -printf '%C@ %p\0' 2>/dev/null | sort -z -nr

So in order to scrape off the latest file system name this completes in my scripts with:

IFS= read -r -d $'\0' line < < (find . -name "partname*" -maxdepth 1 -printf '%C@ %p\0' 2>/dev/null | sort -z -nr )
FullPathToNewestDirectory="${line#* }"

IFS Internal Field Separator - make this the delimiter

read

-r backslash is not an escape character
-d $'\0' line take the entire input as one line (the space between the -d and $ is important)

${line#* } the space after * matters here ${parameter#word the pattern word will be removed from the beginning of the value of $parameter. It's called "Remove Smallest Prefix Pattern" because it will remove the shortest matching prefix string that matches the pattern in word (with ## in place of # it removes the longest matching prefix string).

< < Note the space between these two redirect symbols

So now I have a variable that looks like

/dir/dir/dir (I'm looking for a list of directories, yours might be /dir/dir/file it's all the same)

and I only want the last part of this path

echo $FullPathToNewestDirectory | awk -F/ '{ print $(NF ) }'

echo show contents of the variable

| (pipe) then do the next command I give you

awk

-F/ the fields separator is / (the lack of space between F and / is important)

'{ print $(NF ) }' NF is number of fields of the current record, printing $NF means printing the last one.

I use NF because I'm not always sure server to server how many levels of directories I might have:

IFS= read -r -d $'\0' line < < (find $BACKUPHOME -name "backupdir_partname*" -maxdepth 1 -printf '%C@ %p\0' 2>/dev/null | sort -z -nr )
FullPathToNewestDirectory="${line#* }"
ActualDirectory=`echo $FullPathToNewestDirectory | awk -F/ '{ print $(NF ) }'`

If you plan to work with this in a script you need to consider that if there are no files or directories where you are looking you will end up with an empty (but set) variable, this might mess up anything coming later. You need to be sure you handle that/

I found it important to test for

-z ${FullPathToNewestDirectory:+x}

This tests that not only is the parameter set but also that it isn't null.

As I've been writing this I've been trying to recall what prevented me from using ls to get where I needed to be. I know It caught me out and why may come back to me. If it does, I will update again but why not see if it take you where you need to go first

ls -ABc

Generate a random string - really useful for random passwords for new user creation

2019-08-09T12:53:00.000-07:00

cat /dev/urandom | strings --bytes 1 | tr -d '\n\t ' | head -c32

Conditional awk - for when grep is finding duplicates

2019-07-12T14:48:00.000-07:00

My files that contain information about my databases often look like this (It's a dirty way to maintain a database inventory on a server with multiple databases when the database product itself doesn't have a concept of inventory):

Mydatabasename:/path/to/product/home:/path/to/conf/myfile.conf
Anotherdatabasename:/path/to/product/home:/path/to/conf/myfile2.conf

When the database name appears in 2 lines of the file a simple grep would bring back multiple lines:

mydb=$1
DBHome=grep -w $1 ${myfilelistingdbs}| awk -F ":" '{print $2}'

So do THIS instead:

mydb=$1
awk -F ":" -vx=$mydb '$1==x {print $2}' ${myfilelistingdbs}

It says: if the first field matches this then tell me second field contents

this solves problem of db name popping up in a home path

e.g
database_cola:/data/database_pepsi/product:/data/database_pepsi/conf/database_cola.conf
database_pepsi:/data/database_pepsi/product:/data/database_pepsi/conf/database_pepsi.conf

A grep on database_pepsi returned two product homes (and upset my scripts expecting only one line!)
Awk on first field means you only get one product home returned

How to change the attributes of a file in linux

2019-02-12T13:19:00.000-08:00

I often want to change the timestamp of files when testing scripts so I can see whether the logic works with different ages of files etc.

use touch:

touch -a -m -t 201512180130.09 myfile

-a change the access time
-m change the modification time
-t STAMP use [[CC]YY]MMDDhhmm[.ss] instead of current time

Removing blank lines or replacing white spaces in a file

2018-12-24T14:21:00.000-08:00

in vim:

:g/^$/d

: put into a state for editing with regex commands

g for every line in the file

/ let's start

^ from the start of the line

$ to the end of the line (^$ together is a special instruction that says blank line)

/ next instruction

d delete it

with vi

:g/^[ ]*$/d

there's a space in between those square brackets

vim

:g/^\s$/d

\s means white spaces

remove blank lines from a file with sed:
sed -i '/^$/d' myfile.txt

Replace white spaces with commas:

:%s/\s\+/,/g

replace a word in vi

:g;myword;s;;replaceitwiththis;g

For replacing a word after a word see this post

Here is a useful link for regex expressions:

https://www.w3schools.com/jsref/jsref_obj_regexp.asp

for vim:

http://vimdoc.sourceforge.net/htmldoc/pattern.html

SED replacing the word after a word in a file

2018-11-13T13:01:00.000-08:00

There is an application that writes a particular command into a log file whenever it runs. And that command includes a password switch. Which means the password is joyfully sat in a log file. Even though you have to be able to log onto the server, and have permissions to read the log, it is still not ideal. To strip the password out of the log I run:

sed -i 's/--password[^ ]* /--password replaced /g' myfile.txt

it says find the string "--password morecharacters " and change it to "--password replaced "

Lets break it down:

-i - edit file in place

's/regexp/replacement/g'
Attempt to match regexp against the pattern space. If successful, replace that portion matched with replacement.

[^ ]* regular expression for match every character except a space - note there is a space after the ^ and before the ] and after the *

so it is saying match the word --password and whatever comes after it ignoring the first space

and up to the next space.

g do it for all iterations found in the file (global replace)

cat myfile.txt:

here be my command --password mypassword --anotherswitch and more words

sed -i 's/--password[^ ]* /--password replaced /g' myfile.txt

cat myfile.txt:

here be my command --password replaced --anotherswitch and more words

Oracle report all users, their roles and privileges

2018-10-31T14:21:00.001-07:00

I took the sql for listing privileges recursively for Oracle users from Rene Nyffenegger's site, it is very good but only works for one user at a time as it stands, I wanted to run a report that outputted the same way for every user in my database. So it needed to go into a PL/SQL loop.

set serveroutput on

declare

TYPE re_type IS TABLE OF varchar2(1024);
v_statement varchar2(1024);
v_result re_type;

BEGIN

for x in ( select username from dba_users)
LOOP

v_statement := 'select concat(lpad('' '', 2*level), granted_role) line1 from (select null grantee, username granted_role from dba_users where username = ''' || x.username || ''' union select grantee, granted_role from dba_role_privs union select grantee, privilege from dba_sys_privs ) start with grantee is null connect by grantee = prior granted_role';

execute immediate v_statement BULK COLLECT INTO v_result;

for indx in v_result.FIRST .. v_result.LAST
loop DBMS_OUTPUT.PUT_LINE(v_result(indx));

end loop;

END LOOP;

END;

/

You get a formatted output like:

MYUSER1
    MYROLE_SESSION
      ALTER SESSION
      CREATE SESSION
    CONNECT
      CREATE SESSION
    MYROLE1
    MYROLE2
MYUSER2
    CONNECT
      CREATE SESSION
    MYROLE1
    MYROLE3

Particular thanks to Pete for seeing me past my quotes and array woes

using find to find files but ignore lost+found

2017-09-19T13:36:00.001-07:00

Because it took me a while to work this out and I know I'll never remember how I did it:

e.g.

/usr/bin/find /path/to/search/ -name "myfile_name_in_some_form*.tar.gz" -not $ -path ".lost+found" -prune $ -print -mtime +92 -exec ls {} \;

delete my backup files over 92 days old:

/usr/bin/find /backup/mydir/ -name "mybackupfile*.tar.gz" -not $ -path ".lost+found" -prune $ -print -mtime +92 -exec rm {} \;

An alternative:

/usr/bin/find /backup/mydir/ \! -readable -prune -o -name "mybackupfile_*.tar.gz" -print -mtime +92 -exec rm {} \;

Finding Oracle tablespaces with mixed autoextensible datafiles

2017-02-27T13:42:00.000-08:00

Two ways to identify tablespaces where some datafiles are autoextend true and some are false.

(Mixing these can cause issues with monitoring, so try to keep them consistent.)

SELECT DISTINCT a.tablespace_name
FROM dba_data_files a
WHERE a.autoextensible ='YES'
AND EXISTS

(SELECT DISTINCT b.tablespace_name

FROM dba_data_files b
WHERE b.autoextensible ='NO'
AND b.tablespace_name=a.tablespace_name )
ORDER BY tablespace_name;

SELECT ddf2.tablespace_name
FROM

(SELECT DISTINCT ddf.tablespace_name,ddf.autoextensible
FROM dba_data_files ddf
GROUP BY ddf.tablespace_name,ddf.autoextensible
) ddf2
GROUP BY ddf2.tablespace_name
HAVING count(1) = 2
ORDER BY ddf2.tablespace_name;

ORA-00600: internal error code, arguments: [kkqctdrvCVM(2): More than one CV!]

2016-11-18T12:34:00.001-08:00

Oracle Database 11g 11.2.0.3.0

ORA-00600: internal error code, arguments: [kkqctdrvCVM(2): More than one CV!], [], [], [], [], [], [], [], [], [], [], []

Sql causing error
/* SQL Analyze(27,1) */ select "foo"."foo" "foo .....

Call Stack
kgeadse kgerinv_internal kgerinv kgeasnmierr kkqctdrvCVM

This error comes out of SQL Tuning Advisor, if you aren't using the tuning advisor disable it:

select client_name, status,attributes,service_name
from dba_autotask_client;

BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

select client_name, status,attributes,service_name

from dba_autotask_client;

Using LPAD with dbms_redefinition to add leading zeros

2016-11-17T13:08:00.002-08:00

In the previous post I showed how to change a column definition in a table from varchar to num (or vice-versa) using dbms_redefinition

I had reason to need to pad out a converted varchar column with leading zeros.

begin
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'MYSCHEMA',
orig_table => 'MYTABLE',
int_table => 'INT_MYTABLE',
col_mapping => ' ORDERNUM ORDERNUM ,
LPAD (to_char (COLUMN_TO_BE CHANGED),3,''0'') COLUMN_TO_BE CHANGED ,
LOAD LOAD' ,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID );
end;
/

LPad will left pad a field for you, so here I am saying take my number column, turn it into a varchar (see post ) and stick leading zeros on the data that is already in it to make it 3 characters.

For the process you need to follow to actually make it all happen go to the previous post, but am I writing this here because I just know at some point I'm going to go "I know I've done that with redef before, but how the hell did I do it" . So am I going to leave this here so I kick myself when my own post comes up in the google search that I do - you know that feeling too I'm sure !!!

Change column from number to varchar2 using DBMS_REDEF

2016-06-24T15:24:00.000-07:00

This does a to_char but it also works with a to_num if you want to go from a varchar to a number.

STEP 1 :
Add disk space required

This needs to be enough to repeat the size of the table and its indexes, it is temporary storage for the period of the change only.

STEP 2:
Redo

The redefinition is going to churn through redo, make sure you have enough logs and they are large enough or you will get a bottle neck that will slow it up. If your db is in archivelog mode then also make sure you have a cunning plan to deal with them too.

STEP 3:
Think about a backout plan

I took an export of my table at the start because I had the luxury to do so. I love a belt and braces approach to change.

STEP 4 :
count original table - alway nice to be able to prove you haven't lost rows when you finish!

select count (*) from myschema.mytable;
record result

STEP 5:
Note what you have already

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed from dba_tab_columns
where table_name = 'MYTABLE'
order by column_id;

1 ORDERNUM NUMBER 22 8 N dd/mm/yyyy hh:mm:ss
2 COLUMN_TO_BE CHANGED NUMBER 22 3 N dd/mm/yyyy hh:mm:ss
3 LOAD TIMESTAMP(6) 11 N dd/mm/yyyy hh:mm:ss

STEP 6 :
create an interim table

create myschema.INT_mytable the same as the existing table but with COLUMN_TO_BE CHANGED  as a varchar2 instead of a number (There is no need to create the indexes, but I did include the constraints)

CREATE TABLE myschema.INT_mytable
(
ORDERNUM NUMBER(8) NOT NULL,
COLUMN_TO_BE CHANGED    VARCHAR2(3 CHAR) NOT NULL,
LOAD TIMESTAMP(6) NOT NULL
)
TABLESPACE mytablespace
other clauses as per your original table definition
;

STEP 7:
check it

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed
from dba_tab_columns
where table_name = 'MYTABLE'
and column_id =2;
2 COLUMN_TO_BE CHANGED NUMBER 22 3 N dd/mm/yyyy hh:mm:ss

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed
from dba_tab_columns
where table_name = 'INT_MYTABLE'
and column_id =2;
2 COLUMN_TO_BE CHANGED VARCHAR2 12 N

STEP 8
check redef will work

begin
DBMS_REDEFINITION.can_redef_table('MYSCHEMA', 'MYTABLE');
end;
/

STEP 9
Start the redefinition

I parallelised this based on cat /proc/cpuinfo | grep processor | wc -l x 4

set echo on;
spool redef.txt
ALTER SESSION FORCE PARALLEL DML PARALLEL 32;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 32;
ALTER SESSION ENABLE RESUMABLE;
set timing on;
begin
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'MYSCHEMA',
orig_table => 'MYTABLE',
int_table => 'INT_MYTABLE',
col_mapping => ' ORDERNUM   ORDERNUM ,
to_char (COLUMN_TO_BE CHANGED) COLUMN_TO_BE CHANGED ,
LOAD LOAD' ,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID );
end;
/

STEP 10:
What else do you want to keep as a part of the redefinition, you need to think about this for yourself based on what is relevant.

set serveroutput on;
DECLARE
error_count pls_integer := 0;
begin
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( UNAME => 'MYSCHEMA',
ORIG_TABLE => 'MYTABLE',
INT_TABLE => 'INT_MYTABLE',
COPY_INDEXES => DBMS_REDEFINITION.cons_orig_params,
COPY_TRIGGERS => FALSE,
COPY_CONSTRAINTS => FALSE,
COPY_PRIVILEGES => TRUE,
IGNORE_ERRORS => FALSE,
COPY_STATISTICS => TRUE,
NUM_ERRORS => error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || error_count);
end;
/

errors := 0

STEP 11:
finish the redefinition

begin
DBMS_REDEFINITION.finish_redef_table(
uname => 'MYSCHEMA',
orig_table => 'MYTABLE',
int_table => 'INT_MYTABLE');
end;
/

STEP 12:
check counts and do checks

select count (*) from MYSCHEMA.MYTABLE;
same result as STEP 4

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed from dba_tab_columns
where table_name = 'MYTABLE'
order by column_id;

1 ORDERNUM NUMBER 22 8 N dd/mm/yyyy hh:mm:ss
2 COLUMN_TO_BE CHANGED VARCHAR2 12 N dd/mm/yyyy hh:mm:ss
3 LOAD TIMESTAMP(6) 11 N dd/mm/yyyy hh:mm:ss

select column_id, column_name, data_type, data_length, data_precision, nullable, last_analyzed
from dba_tab_columns
where table_name = 'MYTABLE'
and column_id =2;
2 COLUMN_TO_BE CHANGED VARCHAR2 12 N 21/05/2016 18:00:32

check parallel on table and indexes - all the same as original
synonyms still there
grants still there
constraints and indexes still there

STEP 13:
drop the interim table

DROP TABLE MYSCHEMA.INT_MYTABLE;

NOTE:

it took a while for me to get my syntax spot on, if you get failure after the redefinition has started you need to abort the process to be able to restart it:

begin
DBMS_REDEFINITION.abort_redef_table(
uname => 'MYSCHEMA',
orig_table => 'MYTABLE',
int_table => 'INT_MYTABLE');
end;

EXPDP using QUERY with to_timestamp or to_date

2016-05-20T09:39:00.000-07:00

For expdp with select queries using to_timestamp or to_date I've had a bit of game getting the apostrophes to play happy bunny. Within a parfile I was getting nowhere, so back to my easy friend ksh (run against an 11.2 database):

#!/bin/ksh

#set the environment
export PATH=$PATH:/usr/local/bin
export ORACLE_SID=MYSID
export ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

export NLS_LANG="English_United Kingdom.AL32UTF8"

#piping password in is good practise for not seeing it when doing a ps -ef | grep expdp
PASSWORD=myuserpassword
echo $PASSWORD | $ORACLE_HOME/bin/expdp myuser \
DIRECTORY=MY_DPUMP_DIR \
CONTENT=ALL \
COMPRESSION=ALL \
DUMPFILE=mydumpfilename.dmp \
LOGFILE=mylogfilename.log \
EXCLUDE=STATISTICS \
tables=schemaowner.table_name \
QUERY=\"where column_name \> to_timestamp$\'2015-11-10 00:00:00:000000\',\'YYYY-MM-DD HH24:MI:SS:FF\'$\"means the parameter is simply ignored

Notes:

Whilst out of habit I set NLS_LANG parameter, in expdp it is not really needed any more. See utilities manual for further advice

DIRECTORY: remember for datapump you need to define the directory in oracle:

Create directory MY_DPUMP_DIR as '/myfilesystem/mydirectory';
grant read, write on directory MY_DPUMP_DIR to myuser;
select * from dba_directories;

My other parameters like CONTENT, COMPRESSION, EXCLUDE are there for reasons relevant to what I was doing at the time. You need to think about what you need for the type of file you require, where it is going to be imported to and what is actually needed there.

http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#i1006293

and finally, watch your spaces, I found that leaving spaces like this:

VERSION = 11.1.0 \

caused the parameter to be ignored
whereas:

VERSION=11.1.0 \

it is was taken into account

How to write messages to the Oracle alert log and custom trace files - dbms_system.ksdwrt

2016-04-24T15:07:00.001-07:00

I've been finding this useful on occasion because I have plenty of jobs reading and reporting on alert log errors, so it's saved me having to think of another way to yell "hey DBA" :

exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');

example:

exec dbms_system.ksdwrt(2, 'ORA-09999 hey you wake up and investigate this');

You can put a wrapper around the call and grant execute on the function:

create or replace function write_alert_log(log_or_trace in number, text_message in varchar2) return number

begin

SYS.DBMS_SYSTEM.KSDWRT(log_or_trace,text_message);

return 0;

exception

when others then

return 1;

end;

Talent and intellect is never displayed in disparagement (aka the worst flashlight I have ever owned)

2016-01-31T13:35:00.000-08:00

A simple statement appeared on twitter

"This feature request scares me, but I reserve the right to be wrong."

This request was to allow sql statements to be run on multiple databases at the same time through a tool. So a bit like OEM allows you to do multi database reporting or even patching. Bearing in mind that anyone connected to a database via a tool will only be able to do what they actually have access to do, I don't really see this as a problem. My response was:

"With privilege comes responsibility"

I can have a staging server that can attach to multiple databases, I can write a simple shell script to run through all those database and execute whatever I like within the access privileges of the user I connect with. Simples. I could create a patch plan to patch an entire estate at the same time. No problem. I could write a report to run on a production group of databases that does *this. (Hold that thought in mind, it will resurface.)

Some of the twitter circle within which I roam decided that 'people' would destroy the planet with such dangerous functionality:

"If it was my product I'd be worried about giving 'em so much rope"

So who gets to decide the length of rope the common DBA can hang themselves with? My feeling is: the access level they are granted - nothing like an rm -r * as the oracle software owner to guarantee a long night ahead - who allowed the Oracle DBA access to the server they had to install the software on?! Fools, fools everywhere!

I think that the role of experienced people within a community, if they choose to engage, is to offer advice and to share experience but not to judge.

"We should judge"

I wonder if perhaps more practical comments might be "experience has shown me this could easily cause problems" or "this sort of functionality should be fairly hard to 'switch on' or have high clarity"

But the general ensuing conversation was around: 'people' are idiots, 'people' would break stuff with it.

Would they? All of them? Some might. But then but some people will also chown -R oracle:dba * on a production server at \ (Got root?! How he lol'd as he rebuilt the server ... not)

But is it right to say you can't have potentially useful functionality because you could potentially break something with it?

"..that's what people asking for {the} capability will do.."

Will they? Some might get bitten but if something is well designed then mistakes are less likely and people who forge ahead and do something that is a bad idea....well shit happens and we learn by mistakes (or preferably practise in a sandpit) not by being protected from ourselves.

So back to *this

"The relevance is, that's what people want to do to their 60 db's simultaneously"

Do they? Maybe they want to do something useful and productive too.

"My experience has shown me that people do this"

Not with this tool, because that functionality isn't there.

And I suspect it never will be there, but that's ok because my answer to the person asking for it would be: there's more than one way to skin a cat and have a look at your other options for running actions on multiple databases if that's what you need to do.

But to the experienced, highly skilled, highly talented people out there: Who made you the idiot police?

Show me someone that's never screwed up and I'll show you someone that's never pushed themselves or worked dog tired under stress. On huge estates wrong services or servers sometimes get shut down. Admins are flogged in public or occasionally dismissed.

Should Jonathan Lewis have been considered unsafe to be let loose with sqlplus because he ran a 'bad' query. Should the idiot police say 'this is what people will do if we allow them to'

No of course not. And Jonathan himself says about it 'hey world look at this, my bad, probably best if you don't do the same' by sharing his experience. (His comments about the poor quotes and the part reproduction of the shared information is a side issue).

I think it is usually best not to say 'why on earth do you want to do that you fool?' but to say 'what do you want to achieve here?' (This has generally been the art of response to worrying questions on Ask Tom that was Tom and seems to continue now)

Sometimes we have to remember the very real pressure of a shouty manager saying they want some information from every database by 4pm today and that so often, when the most bizarre JFDIs come in, there is no option to say 'well that's dumb' but we just have to work out a way to do something with least damage all round.

Sometimes the real world pressures can make questions look foolish and sometimes the true talent of an individual is the way in which those 'foolish' questions are answered.

But true talent and intellect is never displayed in disparagement.

But back to tools: "TOOLS DO WHATEVER YOU USE THEM FOR" (Special thanks to the ever gracious @thatjeffsmith for that link balm)

And some people will use them in a way that will make you face-palm

And others will never fail to surprise you.

My experience of UKOUG TECH15 (UK Oracle User Group Technology Conference 2015)

2015-12-16T14:56:00.001-08:00

This year's UK Oracle User Group Technology Conference took place in Birmingham.

I went on the middle day of the 3 (4 including super Sunday) day event.

The agenda is huge on every day. There are 7 streams: Database, Apex&DB Dev, Big Data and Warehousing, Business Analytics, Engineered Systems, Integrations and Collaboration, Operating Systems, Virtualisation, Web&Mobile apps, Keynotes and General Cross Topic. And within those streams there are different presentations happening all day, each day - that's a lot to choose from.

So remembering that Apps15 and JDE15 are also happening at the same time you start to get a feel for how huge it is.

In fairness I rarely use this word but....it's awesome (!)

I sat down with a print out of the streams I was interested in and a highlighter pen and planned my day in advance.

I plumped for:

Women in IT Breakfast : Debra Lilley and Evolution
Advanced ASH Analytics ASH Masters: Marcin Przepiorowski
Keynote: Oracle Database 12c What's next?
Oracle Flashback: Connor McDonald
Adaptive dynamic sampling : Christian Antognini
Interpreting AWR Reports: Franck Pachot
Just Don't Do it: Jonathon Lewis
Evolution of performance Management: Oracle 12c Adaptive optimization: Nelson Calero

The mobile app for the conference works well, you tell it your agenda and it tells you where to go and when, simples. The full agenda is on it to look through, the speaker lists and you can leave feedback for the presentations through it.

It was a full day 8am to 5:30pm with little break. I admit to being brain fried by the end of the day. Whilst all the presentations were worthwhile, I have to say that Connor and Franck made the day worth its weight in membership fee and travel time.

I find it hard to explain the value of this type of training...yes, it is training. Some of it is sound bite, some of it is in-depth, all of it excites me, all of it motivates me to want to use these things in my day to day work and yes, they motivate me to work smarter....I suspect most people attending feel the same way.

I'm not sure you could keep up the level of presentation attendance I did in one day for 3 (or 4) full days but there are other 'diversions'.

There was a set of challenges through the 'conference explorer' for each day to enter a prize draw (and get scores on the doors), some involved visiting exhibitor stands, getting selfies, tweeting stuff, finding stuff in the app, meeting new people. I quite like things like that.

The loudest exhibitor stand seemed to by the golfing area that had putting for pounds. Clever draw. As popular as the Scalextric a few years ago.

There was the drinks in the conference hall after the days work and that was followed by the Big Birmingham Bash. I didn't stay for the bash and I know very little of it, I suspect what happens in Birmingham stays in Birmingham!!

Badges have become quite the thing and there were many 'in house' and 'out of house' ones to be collected.

Part of the draw for me for the UKOUG is the incredible community it is. I now know people that go to these events I am proud to call my friends. Of course the good and the great are there to be spoken to (So many ACEs, Masters, OakTable members) but also the people I talk to a lot on social media (there may be some cross over there!). The people that help each other as problems present themselves across the year. The person that responds to a twitter beg when you really can't find something in a manual, the ones who write all those great blog posts you've been cribbing off all year. It's a great opportunity to give them a hug/whiskey/wide berth* (delete as appropriate based on last interaction.)

As I said to one twitter friend, you'll spot me I'm playing 'Where's Wally' but in monochrome!

One year I swear I will get there for the full conference! Now that really would be quite awesome but I think I would have to go for the full red and white stripes.

If you are a member of the UKOUG and get a conference pass but don't use it: huge mistake - no, seriously - just go - get that training and networking in.

If you aren't a member of the UKOUG you should look at joining. If you are working for a company there are many ways you can talk to them about justifying the cost of membership.

For me this one day taught me that we can use Flashback data archive now and it is a good solution for some of my archiving woes. I found out I knew more about Sharding and other up and coming changes than the guy from Oracle that comes into my company to brief us on new features. I got more confident about the way I interpret AWR, and I have already seen Adaptive Sqlplans in explain plans I've been looking at and thought "ahhhh I know what you are and how you got there!"

It's all good.

sending sql output to a csv file

2015-10-15T14:42:00.000-07:00

So I would previously have been doing something like this

spool myfile.csv;
set heading off
set pages off
select column1_name||','||column2_name||','||column3_name||','||column4_name
from mytable order by 1;
spool off

which is fine but I had to build up the statement each time.

Step in Tom, I found this procedure tucked away in an asktom page from 2000

sqlplus / as sysdba
grant execute on utl_file to system;
create or replace directory DPUMP_TMP as '/tmp';
grant read,write on directory DPUMP_TMP to system;

CREATE OR REPLACE procedure SYSTEM.dump_table_to_csv( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
/

exec SYSTEM.DUMP_TABLE_TO_CSV ('schema.tablename','DPUMP_TMP','myfilename.csv');

If you want to read more about Tom talking about unloading data to a flat file go here http://tkyte.blogspot.co.uk/2009/10/httpasktomoraclecomtkyteflat.html

Oracle Midlands - ClubOracle

2015-05-21T14:49:00.000-07:00

It as taken me a while to get to an Oracle Midlands event - this was the 9th and I've been saying I'd go since number 1.. The venue in Birmingham was easy to get to from the train station, there is easy parking at the venue (once you are through city centre traffic!)

I was surprised at the number of attendees. There were 30 there and I think there would have been more if it weren't for the dreadful weather on the day combined with the unusually heavy traffic.

The group was really friendly and I was pleased to see people I immediately recognised both from twitter feeds and from previous jobs.

The first speaker at #om9 was Joel Goodman. I think that was a bit of a scoop. Joel is amazing. He is a walking oracle encyclopaedia combined with some of the best training (and presentation) skills I have ever known. He trained me on Oracle 8i Backup and Recovery more years back than I am prepared to admit to.

His session was on Oracle Distributed Transactions. Clear and concise fundamentals including2 phase commits and the killer "this is how it can bite you" information.

I knew by 7:05pm my train ticket had paid for itself when the fog started to clear on some of the reasons for in-doubt transactions and the chain of table locks. Another one of those 'I'm sure I knew this once but I really ought to know it all the time" moments.

There was a break for a snack and a chat, a which point the penny dropped for a few of the people I'd been talking to like old friends who I actually was and that they talk to me on-line fairly regularly. And the same for me as people introduced themselves. I love how friendly DBAs are.

The second session was Datapump. I'll kick back on this I thought, I know this one. Wrong! Rich Harrison pulled the "oh, I didn't know that" out of the bag a number of times. He is also a really smooth presenter and one to watch out for in the future. The next day at work a colleague was having a slow running export nightmare and I was all "did you know you can do this?" ! Felt good.

So If you are in the Midlands region (or passing through), I recommend this user group. It is free to attend (sponsored by Redstack Techologies) and in the evening. What's not to like.

Have a look at the up and coming events here.

Security Presumptive

2015-05-15T13:22:00.000-07:00

We have the auditors currently visiting the office. They don't mix with anyone at all, from what I can see, apart from the one man that is the 'go between'. They work out of a meeting room not very far away from my desk.

I feel it necessary to say before I tell you why I'm telling you about this that I work in a very secure building. It is surrounded by high wire fences, cameras and sound detectors. A secure site within an already secure site. It's safe to say it is secure! There are very few people that can get in and generally we all know each other fairly well, if not intimately then at least by sight or on "you left your card in the coffee machine" terms.

To get to my internal office door you have to go through 3 sets of secure doors requiring a personal level of access and also past the glass window of the 24 hours manned security office. It's safe to say by the time you get to my office door you have passed security. There is nothing in my office of merit or value to be secured! There used to be something behind the door to secure. That thing is now many, many miles away. Any security left on my office door is there because a jobsworth didn't take it away with 'the thing'.

So back to my auditor non-interactive-'friend'. Everyday, many times a day she opens my office door with her secure access. My office doors are double doors. The right hand door is next to the security point. The left hand door is not (this is relevant!).

Some office incumbents noticed many moons ago that no one uses the left hand door. People push the right hand door, it is locked, they then try their secure access method and if it says 'come on in' they then open the door. An office incumbent took the bolt off the left hand door. So now the office incumbents walk through the left hand door. It can be done backwards whilst balancing a laptop and a hot cup of tea without the need to swing the body part attached to a pass at a reader or punch anything into a keypad with your nose. Others use their pass and come through the locked right hand door.

So back to my auditor non-interactive-'friend': everyday, many times a day, she opens the right hand door with her secure access. Today I watched her use her secure access and then open the left hand door. She has been there at least 2 weeks so this is progress but she still hasn't thought to just push the door first.

There is another door that leads to a meeting room with a key card reader on it. (Again, there is nothing of merit or value behind this door, even the furniture behind it wouldn't sell at a car boot!) The door lock does not work, men with hammers are aware of this and on a regular basis they come to it, bang their hammers, scratch their heads and leave again. The key card reader beeps when you do stuff to it with your access and its light turns green if it likes you.

For 2 weeks I have watched my non-interactive-auditor-'friend' use her secure access to go through the door, never once doubting that it is locked. Although bizarrely there is no handle on the door the other side and she must just push the door to get out!!!

So my point is: just because something asks you for a password you shouldn't assume that it is secure. Does it care what that password is? What happens if you try to get in without the password? The appearance of secure does not mean it is. You might have userids, roles, profiles, passwords, key readers, whatever, but what if, as I found recently, one table has clear text passwords in that could (note the past tense here - sorted) be read by a multitude of people?! Or what if people that are fed up with dealing with security on a daily basis can undo a bolt and no one is alerted?!

I am desperate to tell my non-interactive-auditor-'friend' to just walk through the door but I'm also now totally wrapped up in watching to see how many weeks it takes her to twig.

I am also torn about what this tells me about her approach to looking at security. Life for many would be jolly easy if our non-interactive-auditor-'friend' just puts a lovely large green tick on a box to say that she tried the security and it all worked fine and moves onto her next job. But whilst I think/hope/know the baskets of eggs I am responsible for are safe from the foxes, I would really like her to find any security holes in anything because there are lots of people out there that are constantly pushing at the left hand door to see if it opens all on its own and I want to bolt it solid before they even try.

Yey! Another year of UKOUG

2015-04-10T14:59:00.001-07:00

I had to 'use up' my SIG day that I get with my Bronze UKOUG membership and I'd missed all the good ones (just kidding!) so I went to the RAC Cloud Infrastructure and Availability SIG (they have to get a decent acronym for that!)

One of the main reasons for choosing it was actually because it was a late starter/finisher: 2:30pm to 8pm

It meant I could get a later train to London, without battling the crowds of commuters and yet still be able to get a train home at a reasonable time.

In fact I had a few hours to myself first which I made the most of here and here

I love the comradery of the SIGs. It is so motivating to talk to people doing similar jobs, with similar issues in similar companies. It is quite therapeutic to hear of other big companies having virtualisation battles, big and small companies having design problems, communication issues etc.

It's great to talk to people in companies that have actually bought the big Exadatas and are using them. If nothing else it starts you wondering whether a company switch might be worth considering for the star studded infrastructure or not, when you realise a lot of the things that make a work day have a sigh happen at many other places in the same way.

These few hours always provide amazing training. This time for me, the winners were the changes to ASM in 12c, particularly flex ASM. Trace File Analyser - which is more than it says on the tin! And using Linux control groups to manage virtualised resources.

(That and getting a chance to spend some time with @ChanderDBA and @MDWidlake - thank you both. There was food and beer - I know, what's not to love!)

Before I arrived I had resigned myself to it being my last ever, thinking it was just too much money to renew membership. Within 30 minutes of being there I knew I was going to renew again. It seriously makes that much difference to my motivation to be a great DBA.

I went back to the office the next day stuffed to the gills with renewed drive and decided to ask, yet again, if my company would pay the membership. And yes, yes they have. It's at least 6 weeks since the bossman said yes and I'm still hopping happy and actually doing more (if that is possible) and volunteering for extras.

It's a win-win for me and the company.

My company gain from my renewed motivation, the extra knowledge I've picked up and that I am keener at work because them renewing my membership makes me feel valued.

I gain from my renewed motivation, I am happier, I feel like I am part of a community still and it makes me want to share more with the DBA community.

The UKOUG is a positive thing. There are no losers.

He who asks is a fool for five minutes

2015-03-15T09:20:00.000-07:00

He who asks is a fool for five minutes, but he who does not ask remains a fool forever.

The older I get, the more I know...how much I don't know. This stands true for me at work, home, and life in general.

Sometimes I feel so confused with life and its happenings that I'm sure everybody else already must know everything I do and then they have their intelligence on top of that. I find I think like this quite a lot at work. But the same seems to apply to baking, gardening, decorating, suduko, crosswords, laundry without total destruction etc.

The nature of work in technology means spending a lot of time searching for information on how to do a lot of technical stuff. This searching naturally leads to many technical forums. Often I see, what on the face of it seem to be, fairly simple questions. These questions are usually responded to by someone who considers themselves to be an expert with a RTFM.

I'll accept that as ok, especially if they leave a link to go and have a read. But what if they still don't understand, what if they need extra guidance, what if the question they asked to begin with wasn't worded well? Do they deserve to be called a lazy idiot? Should they be told to go away and read it again?

Whilst this is aimed at the technical, it's true anywhere in life. None of us were born knowing how to do things, we all had to learn but why do some people believe that because they learnt the hard way so must everyone else?

If I say to my friend "my cakes don't rise as well as yours what am I doing wrong?" I know she will say "What recipe are you using? Is the method the same as mine? I do this". Why do so many online user groups and forums do the complete opposite?

I believe in freely sharing information and that it is ok to ask others to do the same. I also know that whilst I like to consider myself a special individual, I am not. I know if I have a burning question, someone else probably has it too.

Times I have asked questions in halls full of parents like "do they need the packup in a carrier to throw away after lunch or in a backpack" and felt an idiot for asking as 100 burning eyes give me the 'isn't it obvious' look. But on the way out someone always says "I'm sooooo glad you asked that, I had no idea either"

I don't care if I ask a dumb question, I think this is less dumb than never knowing the answer. And I am happy to be polite and understanding to anyone who asks a question that I know the answer to.

In a forum I think I have 2 choices: be helpful and polite or don't get involved.

The perceived anonymity of the on-line world has turned many people into rude, uncaring individuals. Trolling, whilst not liked, is seen as part of life. It doesn't have to be this way.

So just for the record, I am planning to continue to ask as many questions as I can for the rest of my life. I may ask how I can find out, I may just ask for a direct answer and I don't mind whether you give me one or tell me how I can find out.

In return I commit to answering as many questions as I can in a polite way and this sometimes might involve a diversionary route to a source of information, yes occasionally that might include a polite 'I don't know but something might popup on Google (other search engines are available) just might'.

For those of you against me in this quest to accept that some people aren't superhuman all knowing beings :

DBSNMP.BSLN_INTERNAL invalid ORA-12012 ORA-04063

2015-01-14T14:42:00.003-08:00

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-04063: package body "DBSNMP.BSLN_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DBSNMP.BSLN_INTERNAL"
ORA-06512: at line 1

Tested fix on Oracle version 11.2.0.3.7

I've been getting the error in the alert log since the database was built using an rman clone.

I could force the error by running:

As sys:
exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

select log_date,status from dba_scheduler_job_run_details
where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date;

I tried the recommended work around from Oracle to recreate the dbsnmp user:

@?/rdbms/admin/catnsnmp.sql
@?/rdbms/admin/catsnmp.sql

This did not fix it.

Oracle recommended I apply a patch for bug 13637859

This did not fix it.

I rolled back the patch, restored dbsnmp as it was and started a rethink. There were 2 distinct problems happening:

The first one is that the package DBSNMP.BSLN_INTERNAL is invalid:

select substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type,
status from dba_objects where object='BSLN_INTERNAL';

DBSNMP BSLN_INTERNAL PACKAGE BODY INVALID

alter package DBSNMP.BSLN_INTERNAL compile;

Warning: Package altered with compilation errors.

select * from dba_errors where OWNER='DBSNMP';

DBSNMP BSLN_INTERNAL PACKAGE BODY 1 1910 7
PLS-00201: identifier 'DBMS_JOB' must be declared
ERROR 201

DBSNMP BSLN_INTERNAL PACKAGE BODY 2 1910 7
PL/SQL: Statement ignored
ERROR 0

So DBMS_JOB has a problem, well execute was revoked from public on DBMS_JOB on this database.

grant execute on sys.dbms_job to dbsnmp;
alter package DBSNMP.BSLN_INTERNAL compile;
revoke execute on sys.dbms_job from dbsnmp;

select substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type,
status from dba_objects where object_name='BSLN_INTERNAL';

DBSNMP BSLN_INTERNAL PACKAGE BODY VALID

exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

select log_date,status from dba_scheduler_job_run_details
where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date;

Job Failed

So I have a valid package now but my job is still failing. If your database was not cloned you might find this grant and recompile will fix your problem. (The revoke of the privilege again afterwards was fine, it seemed to only need it to recompile, not to actually run)

My second issue was caused when cloning a new db using rman.

select * from DBSNMP.BSLN_BASELINES;
nnnnnnnn OLDSOURCEDBNAME 0 xxxxxxxxxxxxxxxxxxxx Y ACTIVE dd/mm/yyyy

There was only one row in my table and that had the name of the old database in it, I removed it:

delete from DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME='OLDSOURCEDBNAME ';
commit;

exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);
select log_date,status from dba_scheduler_job_run_details
where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date;
succeeded

This removal of one line is going to be how the Oracle recommended work around for this problem fixes it, you should probably use the Oracle way not my hack. But for me Oracle Enterprise Grid/Cloud/OEM/yet another name change for OEM used DBSNMP and I didn't want to upset it on my production system, which is why I deleted the row rather than doing:

@?/rdbms/admin/catnsnmp.sql
@?/rdbms/admin/catsnmp.sql

I found at least 3 blogs with pieces of all this information in but none with it together that solved my issue.

Remember here we have considered:

An invalid package caused by revoking execute on DBMS_JOB fixed by granting execute directly to DBSNMP.
An entry in DBSNMP.BSLN_BASELINES table carried over from an old source database on clone build.
The Oracle Workaround covered in Oracle Support Doc ID 1413756.1 ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
An Oracle Patch for Bug 13637859

Please speak to Oracle Support if you are in anyway unsure that you have the same issue and always make sure you have a good backup before you change anything.

Make a Blogger Bloglist Blog Roll widget NoFollow

2014-02-15T14:59:00.000-08:00

Having a blog roll (blog list widget) on a Blogger blog has its pros and cons.

I use mine a lot, I like to see when my favourite reads have updated and it's a habit for me to look there, despite using Bloglovin too. So for me it's about me not the reader experience. There are cons to having it there like you have to keep it up to date, what if a 'bad' site takes over a url on your list and you are inadvertently sending your readers there. Also, they can look at bit outdated these days too. But I also like them on other sites as I will follow links to other blogs for a new read.

But in terms of SEO and Page Rank for your own blog it can have problems as it creates a follow link to all the sites on your blog roll. Follow links aren't always bad, and Google ought to be taking their own code into account with their PR calculations but some places I read are shouting "Bloglist followlinks BAD", so why not just make it nofollow and be done with it.

Step 1: on the left of your main blogger dashboard page for your blog, choose 'TEMPLATE'

Step 2: BACKUP YOUR CURRENT TEMPLATE NOW. Here's how if you are now sure. Do not do anything else until you have a backup in the bag.

Step 2.5: Re-read Step 2 and do it

Step 3: Choose 'EDIT HTML'

Step 4: Choose JUMP TO WIDGET, then BLOGLIST1

Step 5: There will be 3 small dots on the line of code for the widget bloglist1, click on them once and the code will expand.

Step 6: But it only expands to 3 lines, there are now 3 more dots on the centre line, click on those once

Step 7: Now you see the full widget code, there are 3 lines with the entry target='blank', find these

Step 8: Add the code rel='nofollow' to those 3 lines, just like below

Step 9: Save the changes

Done.

It's not hard.

You might want to consider removing the list altogether, but if you want to hang onto it, then this is the way to make it NoFollow.

Remember you should make any badges or other sidebar links NoFollow too, unless you have a very specific reason for not doing so.