Neil Chandler's DBA Blog

A resource for Database Professionals

The trouble with Timezones & Grid Infrastructure

leave a comment »

When installing Oracle Grid Infrastructure 11.2 (and all other releases), you need to make sure that you have all of the server setting correct and to standard before you do the install. One that bit me recently was the timezone setting. The Red Hat 6.4 server(s) in question has the correct file in /etc/localtime (copied from /usr/share/zoneinfo/whatever). If I type in date, I get the reply in the correct timezone (GMT/BST as I’m in London), so all seems correct.

However, the slack Unix Sysadmin (which might or might not have been me) had not put the correct setting in /etc/sysconfig/clock. Unfortunately, when you install Grid Infrastructure, the setting is read from /etc/sysconfig/clock and embedded into a Grid Inforastructure config file. $GRID_HOME/crs/install/s_crsconfig_hostname_env.txt

### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.

### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services





If you change this entry, and you should check with Oracle Support if this is OK for your site, and you will need to restart Grid Infrastructure. The one thing about this that I really don’t like is that Oracle is storing a runtime configuration file in a an install directory. Does it do that anywhere else?

Written by Neil Chandler

11th April 2014 at 10:51

Online index problem – ORA-08104

with 3 comments

So, you’re creating (or rebuilding) an index ONLINE on a busy system. Your session dies, or it becomes necessary to kill the command, you may find that Oracle does not (always manage to) automatically clean up after itself.

CREATE INDEX my_ind ON my_table (mycol ASC)

ORA-01013: user requested cancel of current operation

select * from user_indexes where index_name = 'my_ind';

my_ind     NORMAL

OMG! WTF! TLA’s! The index is there, even though I cancelled the create statement! Lets drop it…

drop index my_ind;
 ERROR at line 1:
 ORA-08104: this index object <B>79722</B> is being online built or rebuilt

So, HOW do I sort out this mess? Use DBMS_REPAIR!

  1  declare
  2  lv_ret BOOLEAN;
  3  begin
  4  lv_ret := dbms_repair.online_index_clean(79722);
  5* end;

select * from user_indexes where index_name = 'ind_name';

no rows selected

Bang! and the index (or, rather, left-over temporary extents from the build attempt) is gone, ready for you to try again.

Written by Neil Chandler

12th March 2014 at 23:57


Here’s a good tip In SQL*Plus, that I have learned only recently; You’re in the middle of typing in some SQL or a PL/SQL Anonymous block, and can’t remember the column name, or other table information? At the start of the next line type “#desc <table_name>” and the table is described but you keep in the same place in your edit. No need to stop.

NEIL @ orcl > declare
 2 l_dt date;
 3 l_txt varchar2(50);
 4 begin
 5 #desc test_tab1

 Name Null? Type
 ------------------ -------- --------------------------------------------
 DATE_COL                    DATE
 TEXT_COL                    VARCHAR2(50)
 PAD_COL                     CHAR(50)
 5 l_date := sysdate;
 6 select text_col into l_txt.... 
 7 .
NEIL @ orcl >

You can teach an old dog, old tricks. Neat time saver.

Written by Neil Chandler

11th December 2013 at 07:08

Posted in Uncategorized

SQL Plan Management – 12C dumb feature

leave a comment »

In Oracle 11G, Oracle introduced SQL Plan Management (SPM). It is excellent (I love it to bits). It allows you to create Baselines against SQL which lock-down the SQL execution plan. No more plan flips. More consistency. Perfect.

Whenever some Baselined SQL is ran, Oracle still parses it and compares the parsed output to the accepted (Evolved) baselines. If the newly parsed plan is better, a new baseline is added to DBA_SQL_PLAN_BASELINES but is NOT accepted. This means that you need to spend time manually accepting the baseline; running the command DBMS_SPM.EVOLVE_SQL_BASELINE plan and checking the new plan. IF you want it, and/or Oracle evaluates that is it a better plan for that particular set of bind variables, the plan is accepted and becomes a candidate to be used by future execution of your SQL. Complete control over your execution plans.

So, Oracle, what’s all this about in Oracle 12C, eh?

In Oracle 12C there’s a new SPM Evolve advisor task. “By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window” - So, it runs every night and by default it runs DBMS_SPM.EVOLVE_SQL_BASELINE for all new baselines created today and automatically accepts the new plans.

BY DEFAULT? NO! the Oracle, NO!

That is precisely what I don’t want from baselines – Oracle making it’s own mind up about plans without any input from me. I’m using baselines to stop Oracle changing its mind. To explicitly limit the number of paths allowed by the Optimizer to ones I know about and with which I am comfortable. Don’t introduce functionality to do the opposite.

So, immediately following the installation of 12C, I would recommend running (you need to be SYS for this):

           (PARAMETER_NAME = 'TIME_LIMIT') ) );

------------------------- ----------
ACCEPT_PLANS              TRUE
TIME_LIMIT                3600

Then run:

    'ACCEPT_PLANS', 'false');

OK, back where we were, with any baselines fixed in place and doing what I want them to do! Not change.

Written by Neil Chandler

8th December 2013 at 09:57

UKOUG Tech13

leave a comment »

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):


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. :-)


Written by Neil Chandler

7th December 2013 at 10:03

Posted in Management, UKOUG

Tagged with , , ,

Oracle’s Locking Model – Multi Version Concurrency Control

leave a comment »


Written by Neil Chandler

1st December 2013 at 15:42

UKOUG Presentations for Tech13

leave a comment »

I’m giving 2 presentation this year at the UK Oracle User Group Tech13 Conference

The first is a discussion about the reason why you do or do not need to do low level detail traces and block dumps, and argument with Martin Widlake (and the audience hopefully). This is at 4pm on “Super” Sunday 1st.
Presentation 1 Abstract

I will also be presenting Why did my plan change? A run through of the top few reasons why Oracle (un)expectedly changed your execution plan, and what you can do about it using SQL Plan Management. This is on Tuesday 3rd December at 17:45.
Presentation 2 Abstract

I hope to see you there, or in the Pub at a MOB* (probably propping up the bar in the Peveril of the Peak given it’s close and good)


*Manchester Oracle Beers, a sister evening to the London Oracle Beers

Written by Neil Chandler

29th November 2013 at 06:25

Posted in UKOUG


Get every new post delivered to your Inbox.

%d bloggers like this: