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.

UKOUG Management & Infrastructure SIG – New Date

To blatantly steal this post from Martin Widlake, as I’m Deputy Chairman of the SIG, and I’m also presenting:

I ought to just mention that the UKOUG Management and Infrastructure SIG has moved from Tuesday September 20th to Tuesday September 27th (so two weeks from today). It had to be moved as we had a bit of a problem with the room booking. It will be in the usual venue of the Oracle City Office in London and is, of course, free to members of the UK Oracle User Group. {If you are not a member, you can come along for a fee – but if you are interested in coming along to see what a UKOUG Special Interest Group meeting is all about, send me a mail}.

So, if you fancy some free information about:

• Getting the best out of your intel hardware (and BIOS in general) {Steve Shaw from Intel}
• The latest on Oracle GRID and OEM {both presentations by customers not Oracle, one by Niall Litchfield and one by ‘Morrisons’,though Oracle supported us very well by finding one of the customers!)}
• A presentation and discussion on Outsourcing by Piet de Visser
•  A consideration of how deep into the technology real-world DBAs need to go to solve issues (Martin Widlake and myself)
• An Oracle support update

Well, register for the event and I’ll see you in two weeks!

A little rant about DBA’s

Well, a while ago I was doing some interviews for a client for a Production support DBA. This was for a short term contract to look after a few systems while the incumbent was off doing more interesting project work. The thing I discovered was the absolutely dire level of knowledge displayed by the interviewees about Oracle. Things that I regard as fundamental to the understanding of how Oracle works were simply unanswered.

Q: What’s the difference between and instance and a database?
Q: What does “nested loop” mean in an execution plan?
Q: Name the memory areas within an Oracle instance.

Several candidates answered these either very badly, or not at all. Not at all? Any you say you’ve been working with Oracle since Oracle 7 and you can’t answer these questions? You have been working with Oracle for 10 years and you can’t name the PGA? or ANY components within the SGA?

If you are reading this blog, I suspect that you know the answers to the above questions. You’re the sort of person who spends a little of their own time doing research. I don’t think the questions are demanding (or are they? please tell me they are not.) Who actually hires these people? Are these chancers and charletans deceiving everyone, or just deceiving themselves.

And they were all OCP certified. How? And how little does that certification mean in reality. A tick on a CV / resume to get it past the box tickers in HR and agents.

You know, I’m not angry, I’m just disappointed in the lack of professional standards that you sometimes come across in our industry.

(Note: this post has been delayed to protect the guilty)