UKOUG Tech13

I attended the UKOUG Tech13 conference this week, along with over 1,000 other Oracle Technical Professionals (and some less-professional too), to learn what I could about how everyone else is doing stuff with Oracle, and how it works. And to share some experiences.

I wasn’t disappointed.

As with everything these days, there was a lot of activity on Twitter about the 4 day conference, and one of the sponsors, RittmanMead, had knocked up a rather wonderful tweet visualisation (click here to see how – magical):

UKOUG13_Tweet_Feed

As you can see, there are some of the worlds best-known Oracle techies on the list of top speaker tweets, but I am as pleased as anything to have been in the top 10 most tweeted speakers at the end of the 1st day (although I suspect that’s because I knew where the pub was, rather than too much about my 1st presentation)

Get involved with the Oracle community, in whatever form. It leads to professional and personal development, real insights about what Oracle really does (rather than what the marketeers say), and some good friendships. And possibly liver disease judging by the amount of alcohol consumed by all. ūüôā

@chandlerdba

SYSMAN password change

Some days you just forget the dot all of the i’s.

I had just installed a new RAC cluster, got it all up and running and was using DBCONSOLE to check the system out – no access to the Production Grid Control for this cluster yet. I then made a few more configuration changes and restarted one of the nodes. I was rather surprised that the console could no longer access the system. It was claiming the instance was down, and asking for server logins to allow restart. I was quite sure the instance was available, mainly because I was connected using SQL Developer and executing queries.

So, what went wrong? What config had changed before I restarted the nodes? I checked my notes and… I was hardening passwords. One of the passwords I changed was the SYSMAN password. However, I had completely neglected to inform the EM agent for the console that I had changed the password! Idiot.

cd $ORACLE_HOME/<node_database>/sysman/config
vi emoms.properties
change:
 - oracle.sysman.eml.mntr.emdRepPwd=<clear-text-password>
 - oracle.sysman.eml.mntr.emdRepPwdEncrypted=FLASE

emctl stop dbconsole
emctl start dbconsole
…and all is well again

This blog entry was brought to you by Pierrot.

The Dunning-Kruger Effect – I don’t know

Interviewing people is always a rich source of anecdotes, or should that be anec-dont’s? Whatever.

When interviewing for a technical specialist, such as a senior Oracle DBA, it seems important to ask a number of technical questions to ascertain the competency the individual has in relation to Oracle and the technologies related to Oracle. This is where the Dunning-Kruger effect comes into play beautifully.

Kruger and Dunning proposed that, for a given skill, incompetent people will:

  1. tend to overestimate their own level of skill;
  2. fail to recognize genuine skill in others;
  3. fail to recognize the extremity of their inadequacy;
  4. recognize and acknowledge their own previous lack of skill, if they are exposed to training for that skill;

So, lets ask a technical question of the candidate:

Under what circumstances will the optimizer decide not to use an index?

Here’s 4 extremely confident actual answers from 4 different candidates. This was the extent of their answers, although some (but not all) did elaborate further:

  • when you do a count(*)
  • when the columns you are selecting are not indexed
  • I query using a table and build a SQL Profile to bypass the index
  • it depends on the join condition

All candidates were OCP certified with at least 10 years worth of Oracle experience on their CV’s, applying for a senior position. To quote Darwin: “Ignorance more commonly begets confidence than does knowledge”. I like to think that it’s our brains protecting us from ourselves.

I mean… how hard is it to say “I don’t know” ? For some nationalities, that would appear to be impossible. A long time ago I spent several weeks teaching programmers in a country whose native/official language was not English. Given I am from the North of England, there are some who would say that this is also the case for me, given my notable regional accent. The combination of my idiosyncratic dialect, combined with the unnamed-county’s locals’ inability to lose face by saying “What you talkin’ about Neil?”, like Gary Coleman might have, meant that the second week of training was spent going through exactly the same materials as the first week, as it took me that long to realise they didn’t have a clue what I was saying half of the time. If only one of them had been brave enough to say (or I had been a little smarter and realised) then it would have save a whole lot of pain. However, the locals might never come understand what happens when you get “your knickers in a twist.”

NOTE: I do appreciate that I could be a Dunning-Kruger “victim” and these blogs merely indicative of immense incompetence. I’ll let you decide. ūüėź

Why ITIL CAB’s do not work as expected

ITIL (Information Technology Infrastructure Library) does work. It works very well and is becoming more and more widely adopted, but does not always work the way that management thinks it works.

The basic premise of ITIL is to put structure and process around the business of running IT. It is a collection of guiding principles which, if appropriately adopted into a company will help run the IT function in a reasonable process-driven fashion. It’s largely common sense, which helps.

However, after lots of experience with all of the different aspects of ITIL I have decided that it doesn’t really work the way management think it does. This is especially relevant to Change Management.

The premise behind Change Management is that technical staff are prevented from making non-Standard changes to the IT systems without prior authorisation from all relevant stakeholders.

Where this falls down is 2 fold.

– The definition of Standard change needs to be very carefully identified, documented and locked-down. It never is, leading to techies making personal decision about what constitutes standard change. This definition is always enthusiastically elastic, until something snaps when it will become frustratingly draconian (for a while).

– The stakeholders rarely understand the nature and true impact and risk of the changes taking place, so the CAB must accept the opinion of the techie (see above). The net result of a Change Advisory Board (CAB) is to authorise the technical staff to make change whilst putting the burden of responsibility for the change onto the management. Not the techie. If the changes goes wrong, then it is fault of the approving manager for not assessing the risk correctly, or for approving the change to take place at the wrong time or in the wrong way. However, the techie gets off scott free in all of this.

As long as the process is followed, we can get away with murder.

Exposing the Oracle Alert Log to SQL

I’ve been spending some time working in Apex recently, building a small app to draw together the monitoring of application and infrastructure components into a single easy-to-visualise tool. As part of that, I wanted to be able to read and report on the alert log. Traditionally, that would have meant creating an external table to point to the alert log and reading it that way, with lots of string manipulation and regular expressions to try to pull out useful bits of information. However, Oracle 11G had made that a lot easier. Step forward X$DBGALERTEXT. This is the decoded version of the XML version of the Alert log, and as such provides lots of lovely columns to filter by, rather than a single line of text to decode. Particularly useful (for me) is the MESSAGE_LEVEL. Is this line of text informational (16), or critical (1), or something in between? Of course, each “normal” line of text is still available in the MESSAGE_TEXT column.

SQL> desc x$dbgalertext;
 Name                           Type
 ------------------------------ --------------------------------------------------------
 ADDR                           RAW(4)
 INDX                           NUMBER
 INST_ID                        NUMBER
 ORIGINATING_TIMESTAMP          TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP           TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                VARCHAR2(64)
 COMPONENT_ID                   VARCHAR2(64)
 HOST_ID                        VARCHAR2(64)
 HOST_ADDRESS                   VARCHAR2(46)
 MESSAGE_TYPE                   NUMBER
 MESSAGE_LEVEL                  NUMBER
 MESSAGE_ID                     VARCHAR2(64)
 MESSAGE_GROUP                  VARCHAR2(64)
 CLIENT_ID                      VARCHAR2(64)
 MODULE_ID                      VARCHAR2(64)
 PROCESS_ID                     VARCHAR2(32)
 THREAD_ID                      VARCHAR2(64)
 USER_ID                        VARCHAR2(64)
 INSTANCE_ID                    VARCHAR2(64)
 DETAILED_LOCATION              VARCHAR2(160)
 PROBLEM_KEY                    VARCHAR2(64)
 UPSTREAM_COMP_ID               VARCHAR2(100)
 DOWNSTREAM_COMP_ID             VARCHAR2(100)
 EXECUTION_CONTEXT_ID           VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE     NUMBER
 ERROR_INSTANCE_ID              NUMBER
 ERROR_INSTANCE_SEQUENCE        NUMBER
 VERSION                        NUMBER
 MESSAGE_TEXT                   VARCHAR2(2048)
 MESSAGE_ARGUMENTS              VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES        VARCHAR2(128)
 SUPPLEMENTAL_DETAILS           VARCHAR2(128)
 PARTITION                      NUMBER
 RECORD_ID                      NUMBER

Very handy. Just add your own view, synonym and permissions to read the view, and you’re away…

create view v_$alert_log as select * from x$dbgalertext;
create public synonym v$alert_log for sys.v_$alert_log;
grant select on v$alert_log to whomever...

  1* select message_text from v$alert_log where ...;

MESSAGE_TEXT
-----------------------------------------------------------------
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side pfile /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora
System parameters with non-default values:
  processes                = 200
  sessions                 = 322
  sga_max_size             = 2G
  pre_page_sga             = TRUE
  nls_language             = "ENGLISH"
  nls_territory            = "UNITED KINGDOM"
  filesystemio_options     = "SetAll"
  sga_target               = 2G
  control_files            = "/u02/oradata/orcl/control01.ctl"
.
[snip]
.
  aq_tm_processes          = 1
  diagnostic_dest          = "/u20/apps/oracle"
PMON started with pid=2, OS id=2492
PSP0 started with pid=3, OS id=2494
VKTM started with pid=4, OS id=2512 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=5, OS id=2520
DIAG started with pid=6, OS id=2522
...etc...

SYSTIMESTAMP INTERVAL Processing

…or how to calculate dates in Oracle.

This is just a quick post to try to encourage the use of the INTERVAL function when adjusting (sys)timestamps (or dates). I thought this would be better expressed through a quick script with comments to show how using the traditional Oracle method of calculating fractions of a day can cause problems and make you have to think more than necessary.

There are 2 main issues with using the traditional Oracle method of calculating date/time changes. Firstly, it’s strange. You have to calculate fractions of a day. 1 second is 1/86400, 1 minute is 1/1440 [maybe 1/(24*60) expresses it better]. Secondly, with timestamps it causes an implicit conversion to a date type, with all of the unintended consequences that implicit conversion carries.

Lets run the script and see what happens:

Script Output

> -- Lets just set the date format explicitly before we start. > alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' session SET altered.
> -- Lets start simple and add a day. > select systimestamp, systimestamp + 1 from dual
SYSTIMESTAMP  SYSTIMESTAMP+1             ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 10-DEC-2011 18:39:53  
> -- Hang on, doing this the traditional Oracle way has implicitly cast > -- the timestamp into a sysdate format. We need to convert it back. > -- Lets try that again, but add 3 hours instead > select systimestamp, to_timestamp( > systimestamp + (3/24), >      'DD-MON-YYYY HH24:MI:SS' ) from dual
SYSTIMESTAMP  TO_TIMESTAMP(SYSTIMESTAMP+(3/24),'DD-MON-YYYYHH24:MI:SS') ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.39.53.000000000
> -- So we have an implicit conversion, and we've lost the timestamp precision. > -- Now lets add 3 hours, 5 minutes 10.5 seconds. >select systimestamp, to_timestamp( > systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60), > 'DD-MON-YYYY HH24:MI:SS') from dual
SYSTIMESTAMP  TO_TIMESTAMP(SYSTIMESTAMP+(3/24)+(5/(24*60))+(10.5/24/60/60),'DD-MON-YYYYHH24:MI:SS') ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.45.04.000000000
> -- And we completely lost the .5 in the 10.5 as we've converted to a sysdate. > -- It was rounded up. You might also have noticed that I have used some > -- different date processing to calculate fractions of a day. > -- There are many different way to calculate time in Oracle: > -- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc, > -- It's all tricky to understand and standards vary from company to company, > -- if the company actually has a standard. > -- There is a better way, using the INTERVAL function. It goes like this: > -- Lets start simple and add a day.
> select systimestamp, systimestamp + INTERVAL '1' day from dual
SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'1'DAY ------------- --------------------------- 09-DEC-11 18.39.53.042000000 +00:00 10-DEC-11 18.39.53.042000000 +00:00
> -- So far so good. And we haven't lost the data type either! > -- No implicit conversion to break our code in the future. > -- Now lets try to add 3 hours
> select systimestamp, systimestamp + INTERVAL '3' hour from dual
SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'3'HOUR ------------- ---------------------------- 09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.39.53.042000000 +00:00
> -- Note the indicator is always singular. > -- Now lets add 3 hours, 5 minutes 10.5 seconds
> select systimestamp, > systimestamp + INTERVAL '3' hour > + INTERVAL '5' minute >                    + INTERVAL '10.5' second >       from dual
SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'3'HOUR+INTERVAL'5'MINUTE+INTERVAL'10.5'SECOND ------------- ------------------------------------------------------------------- 09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.45.03.542000000 +00:00                                 
> -- Spot on - and the precision is correct too > -- My mam/mum/mom* could read it and work out what was going on. > -- But she is an OCA** (* delete as applicable) (**not really) > -- There you go. Get your company to use this nomenclature and you're home free. > -- No more strange time-base calculations or implicit conversion errors. > -- It also works with DATE types too.

Original Script set echo on
-- Lets just set the date format explicitly before we start. alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; -- Lets start simple and add a day. select systimestamp, systimestamp + 1 from dual; -- Hang on, doing this the traditional Oracle way has implicitly cast -- the timestamp into a sysdate format. We need to convert it back. -- Lets try that again, but add 3 hours instead select systimestamp, to_timestamp(        systimestamp + (3/24),                                    'DD-MON-YYYY HH24:MI:SS' ) from dual; -- So we have an implicit conversion, and we've lost the timestamp precision. -- Now lets add 3 hours, 5 minutes 10.5 seconds. select systimestamp, to_timestamp(        systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60),                                    'DD-MON-YYYY HH24:MI:SS') from dual; -- And we completely lost the .5 in the 10.5 as we've converted to a sysdate. -- It was rounded up. You might also have noticed that I have used some -- different date processing to calculate fractions of a day. -- There are many different way to calculate time in Oracle: -- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc, -- It's all tricky to understand and standards vary from company to company, -- if the company actually has a standard. -- There is a better way, using the INTERVAL function. It goes like this: -- Lets start simple and add a day. select systimestamp, systimestamp + INTERVAL '1' day from dual; -- So far so good. And we haven't lost the data type either! -- No implicit conversion to break our code in the future. -- Now let's try to add 3 hours select systimestamp, systimestamp + INTERVAL '3' hour from dual; -- Note the indicator is always singular. -- Now lets add 3 hours, 5 minutes 10.5 seconds select systimestamp, systimestamp + INTERVAL '3' hour                                   + INTERVAL '5' minute                                   + INTERVAL '10.5' second                      from dual; -- Spot on - and the precision is correct too -- My mam/mum/mom* could read it and work out what was going on. -- But she is an OCA** (* delete as applicable) (**not really) -- There you go. Get your company to use this nomenclature and you're home free. -- No more strange time-base calculations or implicit conversion errors. -- It also works with DATE types too.

Industry Experience

I don’t get it. Why do so many jobs and contracts seem to insist upon having experience in a particular industry when, in the overwhelming majority of cases, the specific industry in which we work has no bearing upon the nature of our work.

I have worked across many industries, but each time I talk to a recruitment agent I get similar questions: “Have you worked in X industry?”, “I won’t put you forward for Y unless you have worked for Z”.

It’s the wrong question. Have I worked in Media? Investment Banking? Accountancy? Property? Logistics? It doesn’t matter. No, really. It doesn’t. I have worked in all of those industries and a few more besides, and the nature of the industry was largely irrelevant. A friend recently suggested that you need Investment Banking experience so you understand the inordinate¬†bureaucracy and dreadful boredom that come with working for an Investment Bank. A little unkind, but I know where he’s coming from.

What is relevant is the type and nature of systems with which you are working. Are they mission critical? Zero downtime? Very High Transaction rate? Enormous Data Warehouses? Hundreds or Thousands of databases? These questions have relevance. A high transaction rate OLTP in a Bank is very similar to a high transaction rate OLTP Web Retailer.¬† The challenge with these systems is a different to that of an enormous data warehouse, but it’s still fundamentally an RDBMS. Data is data is data. We don’t need industry experience – it doesn’t help us in the same way as it helps Business Analysts or Project Managers or even Developers.

The recruitment problem for DBA’s is that recruiters don’t know the difference between OLTP and Data Warehousing; a large proportion simply keyword match (the great ones don’t! – and there are genuinely great recruiters out there, in small numbers) so you need to ensure you have all of the relevant keywords on your CV – I have even been asked to amend my CV to put Word and Excel on there! WTF? Unfortunately you also need to be careful, otherwise you’re probably getting job adverts sent through for Cobol Programmers, Websphere Guru’s and all manner of support and helpdesk staff. I removed IBM Assembler Programmer from my CV about 10 years ago, although I suspect there are not too many jobs left for that skill set now.

%d bloggers like this: