UKOUG 2012 – Day 3

Today was Unconference day at UKOUG which was a pot-pourri of talks all lasting about 20 minutes each. The talks were interesting although sparsely attended, which was actually a benefit as there was plenty of opportunity for interaction and indeed complete disregard of the main topic under discussion.

I didn’t actually get to see any scheduled presentations but I had seen enough in the previous two days to keep me going for a while.

So adios UKOUG at Birmingham and welcome to the Tech conference in Manchester next December.

UKOUG 2012 – Day 2

A deep dive into Dataguard is a tough way to start the day off but Emre Baransel handled it well. I jotted down about 6 or 7 takeaways for later investigation. Much of the talk was about tuning the redo log apply and I must admit that I don’t consider we have many problems in that area across the estate but it is still worth reviewing. One topic that stuck me as looking at in more detail was the ability to recover a standby database from a primary backup. Chatting to a few people afterwards they had all heard of the capability to do that but had never tested it.

Maria Colgan is fanatastic. Nuff said. Very, very knowledgeable about the optimizer and a great speaker. She just seems so enthusiastic about what can be a very dry subject. Too many notes for me to mention but one worth highlighting is a tip when you wonder if your hint was used or even seen. Take a 10053 trace and search for ‘Dropping Hints’ which will be found right at the end. The used and error values will give you the answer to your question.
PS nice but firm shutting up of someone in the audience who liked the sound of his own voice too much.

Owen Ireland ran through an introduction to Goldengate which despite using GG was quite informative and helpful and had lots of pointers to useful resources. If looking at GG then I suggest you get the slides from the UKOUG library

Before lunch Larry Carpenter was talking about a new 12C feature which allows Data Guard to transfer to a remote (a long way away and therefore suffering from potential latency) standby. Far Sync allows a lightweight database to be sited locally but not that close that it will be affected with a primary site failover. A lightweight server with minimal memory, cpu and storage can be used (and it can host several Far Sync instances). Sync (max availability) transfer to there and that performs async (max performance to the remote standby database. It seems a good idea if the DR site is hundreds or thousands of miles away.
There will also be a DG administrator role in12C that will only be able to manage DG but not touch or see data.

My second Carlos Sierra talk of the conference was about the SQL Health Check script which is like a poor man’s SQLTEXPLAIN. However this script does not create any database objects so can be run on production systems without a change control. Well worth investigating, all you do is plug a sql_id in and then review the html page that is output.

Each year I try and attend a session about a subject that is new to me and this year I decided on Exalytics. The presenter was Robin Moffatt who gave a very accomplished overview of the toolset (which must be heaven for Oracle sales staff as it involved licenses for OBIEE, Times10, Exalytic hardware and Goldengate (optional)). Robin’s presentation was very professional and gave a really good overview, however the real value was where Robin talked about what worked and what didn’t and he obviously had plenty of experience of both aspects.

A good day spoilt by feeling ill during the late afternoon and evening.

UKOUG 2012 – Day One

Yes, I know I had the wrong year in the title, at least I cannot be accused of looking backwards all the time.

The UKOUG conference comes around again and the first two presentations re-confirmed why I attend. Jonathan Lewis was talking about generating data for test cases and I realised that I could generate the data for an interesting issue at work involving the non use of an index and a histogram with a bucket size of 60. It wasn’t so much that the data was hard to create it was that it had just not occurred to me to do so. I have seen Jonathan speak a number of times and I think I enjoyed this the most in that it was all real-world and practicable whereas sometimes I drift off as dives deeper than I either need or care to go.

My second eureka moment was when Graham Woods was talking about dynamic connection pooling room the middleware tier and the effect it has on the database and it really did make me wonder about how some of our tiered apps manage that, something that is relatively invisible to me at the moment but doesn’t have to be. My key takeaways were the lines ‘the apps tier was invented to queue’ and also ‘one of the most expensive oracle operations you can perform on a database is to connect’.

To contrast the two positives I will throw in two negatives, the perennial lack of seating at the ICC when trying to eat lunch and the obsession this year by the UKOUG with mobile apps. I normally use their web agenda app to book my sessions and then print it off. That has all been changed this year in favour of mobile apps. Not very good with a Blackberry and not particularly well thought through or implemented in my opinion. However I was even more surprised to find out that the session feedback forms have been binned in favour of inputting your data from a mobile app. I can see the reasons in terms of collating the data and saving cost but not everyone has a Smartphone and the experience on Blackberry is distinctly lacking. In  fact I met another two friends at the evening reception who both had Blackberries and had experienced the same difficulties and had just given up on it.  Just call me a Luddite but I did not see many people inputting data at the end of the sessions I attended.

Carl Dudley did a good talk on auditing which chimes with some work I am doing at the moment around the ‘db, extended’ parameter for the audit-trail parameter and writing to the syslog file. From what he was saying I think there might be some differences between *nix and windows environments – My experience is solely on *unix whereas Carl was talking about Wintel. If you do nothing else from this blog then have a look at the new (11GR2) v$xml_audit_trail view.

My second sql test case session of the day was Carlos Sierra on the various permutations of SQLTEXPLAIN. The options were somewhat confusing but there was no doubt that it is a powerful tool set. I will be interested to see what the SQL Health Check talk is about tomorrow because it sounds to be something similar.

My final session was Edgars Rudans talking about Grid Infrastructure upgrade from 11.2.0.1 to 11.2.0.3. I wonder how many people in the room had missed out the ‘on AIX’ in the presentation title that was not printed in the schedule, as I did. However it did shed light on similar issues we had experienced on HPUX, especially around the requirement for  several scripts to be run directly as root and not as sudo root which we normally do.

A very good day and a pageful of things to look at when I get back to the office on Thursday.

UKOUG 2011 Part Deux

Day 2 of the UKOUG conference at the ICC in Birmingham and back into the fray.

First up was Thomas Presslie talking about Dataguard fast start failover. How he managed to demonstrate transactions and network connectivity using whisky and toilet paper could not be done full justice in a blog – it had to be seen to be believed.

It did make me want to do more with FSFO, especially noting how easy the setup was using OEM. However my belief that the database is only part of the end solution and failing that over to a second datacentre after a network flicker may leave the application stack in a mess does still concern me. Co-incidentally I have a requirement to set up a second standby configuration cascaded from a physical standby but keeping the 3^rd database perhaps one hour behind whilst the standby is in real time apply mode with no lag. That might give us a chance to determine the status of the data before a logical corruption (user error) had occurred. Much more likely to be of value is flashback query but we are going to look at both avenues. It is highly unlikely we would ever be in a position to flashback the database.

Julian Dyke then talked for an hour about RAC trouble-shooting (mostly 11.2.0.2) and the time flew past. I made quite a few notes of things to think about. The pros and cons of putting the scan addresses in /etc/hosts (HPUX) to be used in the event of a DNS failure was one thought. Looking at the exectask function and the scripts used to call various function was another action I took for myself. Another was a big list of asmcmd commands, some of which I did not recognise. I think they must have come in with 11GR2 which I have not really used myself although we are using it on site.

Tanel Poder’s biggest ever problem was next up. I had seen this presentation last year and knew the answer but how he got there was still interesting. The use of the HPUX command kitrace (similar to dtrace on Solaris – see reply below for more details) reminded me that I was going to look at that in some detail but have never got around to it. As my site is likely to be moving away from HPUX sooner rather than later perhaps there is not much point now.

After lunch John Beresniewicz was talking about ASH outliers. Quite mathematically based, which is always a challenge for me but he will be posting a script (possibly via Doug Burn’s blog) which he has developed as another means of dissecting and analysing ASH data.

Michael Salt’s talk on indexes was full of real world examples and there were lots of nice little hints and tips, none of which were earth-shattering but all of which were good practise and I found it a useful reminder of what I should be doing when looking at code. On the same theme two slots later Tony Hasler was presenting a beginners guide to SQL tuning.   I have never seen Tony present before but I really liked both his style and the content. A lot of information thrown in and good explanations of various autotrace outputs. I will definitely be downloading his presentation to run through it and see what I can put to further use. Whilst I do not think I am expert in the field of SQL tuning, indeed far from it, I do like to think I know what to look for. Sometimes listening to others you realise in the same lecture both how much you already know and how little you actually follow best practises. There is no real substitute from looking at code and trying to improve performance. For a lot of us who have a very wide-ranging DBA role then that opportunity to practise odes not appear often enough which is why it is good to review and refresh your approach now and then.

At every conference I like to try and hear something new or touch on an area that is outside my day job. John King’s talk on Edition Based Redefinition was just that. I am not really in a position to take advantage of the ability to let users run differing sets of code and then migrate them across to a new release in a seamless manner, all without any outages or interruption to service. However I could see how useful it could be, especially in the world of the Apps DBA, say for EBS. Apparently no less a person than Tom Kyte referred to EBR as the ‘killer feature’ within 11GR2.  John had an easy, comfortable manner  and the time flew past, so much so that he had to be dragged kicking and screaming from the stage by the next presenter.

All in all another good day, rounded off with a couple of beers with work colleagues and a few presenters, all with plenty of Oracle chat included.

UKOUG 2011 – Day 1

The first day of UKOUG conference at the ICC in Birmingham and there were a lot of familiar faces are around. Due to a lot of traffic I missed the first presentation I wanted to see which was from Kyle Hailey on SQL tuning. I will have to download the presentation later. I did make it to Greg Rahn on the SQL Monitoring report and that was well worth the time spent. Whilst I am familiar with the functionality, he opened my eyes by providing  a number of examples of what he would look at first to try and determine a better resolution. His presentation style was comfortable and he had a small number of examples which covered quite a lot of scenarios. He did point out it was only to be used if you had paid for the Tuning and Diagnostic pack but as it is turned on by default I did wonder how many use it without any further consideration.

ADR presentation done and dusted

I was pleased with my the presentation on ADR I gave yesterday at the Unix SIG in Reading. The timing was bang on at 60 minutes and the audience had not seen or used many of the features I discussed so that was a bonus. I had the benefit of being able to give the same talk two days earlier to a team of DBAs at work and I learned a lot from the comments I received and that made for a better presentation yesterday.

One question I was asked afterwards was ‘ are Health Monitor and Support Workbench Enterprise Edition features or are they also available in Standard Edition?’. As we do not use Standard Edition I could only hazard a guess that these were tools that were advantageous to Oracle support as well as users and therefore I figured that they would be included in both editions. I have since looked at the list of features for 11GR1 and can see nothing that suggests that the features are licenseable or are different between editions. If anybody knows different then please feel free to correct me.

The presentation is available in PDF format from the UKOUG site,  provided you have a member access that is.

Upcoming ADR presentation at UKOUG

I am presenting a talk around the use of the Automatic Diagnostic Repository at the UKOUG SIG in Reading on 22nd September 2011. I will be covering, amongst other things, the management of files, the Health Monitor, incidents and problems and the Support Workbench utility. I am hoping that, whilst everyone will probably be already aware of ADR, some of the things I mention might be new or have not been fully looked at before. The Health Monitor was new to me and the ease of use of the Support Workbench when raising an SR is certainly the way forward.

I will also be quite critical of how ADR has been delivered, particularly in respect of the management of diagnostic data and the trace and alert logs that are generated. ADR currently lacks features such as the management of the alert log which still needs external management using such commands as logrotate on unix. The standard alert log has not been replaced by an alert log in xml format (log.xml) and the old alert log is only created for backward compatibility and is not guaranteed to be available in the future. Listener logs are not purged and there are ongoing problems in removing core dumps (on HPUX at least). Overall I will be suggesting that ADR is not as automatic as it could be but some of the additional features other than file management are well worth investigating.

I look forward to meeting fellow UKOUG members and hopefully I may learn a few new things about ADR myself if I get good audience interaction.

UKOUG Conference 2011 – Call for papers

The UKOUG Conference to be held in Birmingham (5th – 7th December 2011 ) is looking for presentations on: real life business experiences; in depth product analysis, new product overviews and everything else in between.

 New for 2011: in order to enhance the agenda there are new topic listings, so to get familiar with what to submit your presentation under click here.

Reminder of how to apply
 
1) Complete your Speaker Application form, please note there are new questions this year
2) Submit your abstracts: The judging abstract is to be of 50 – 200 words and a marketing abstract of no more than 400 characters (including spaces and quotation on your intended presentation.
3) Refer to the speaker guidelines for tips, if you have any questions or need a refresher.

As previously, your abstract(s) will then be evaluated by the agenda planning committee, comprised of members of the UK Oracle User Group community and confirmation of whether your abstract has been selected onto the agenda will be sent via email in September.

Submission deadline: Sunday 5th June 2011

As a first-time speaker last year I did not find it  too intimidating, athough I was in one of the smaller  halls, not the main stage areas and  would encourage anyone to try it. I think the best advice would be to ensure that you get chance to practise your speech in advance, either to work colleagues or a UKOUG SIG.  I found that presenting to a group on non-database people at work including  architects, managers and unix admins was very helpful and much better than trying the talk out with fellow DBAs where you felt the need to either miss out the simpler bits or elaborate and get side-tracked from the planned speech.

Good luck to all those who submit entries and to those who are chosen to speak.

How many mistakes can you make when diagnosing a problem

Last week I attended a talk by Daniel Fink at UKOUG around having a plan to sort out problems and perhaps the key point was –‘listen to the problem before setting off down the investigatory path’. I blogged at the time  that I thought it was an interesting  talk and I wanted to try and run through something similar at work.

This blog entry is perhaps a good example of how not to approach a problem but equally holds some technical information that may be of use to others in a similar situation.

I was approached by a developer who asked me about charactersets as he was running a query on two databases both of which had an external table  which came from the same source and yet he was only returning 3 rows out on one database and 4 rows on the other. The column he had narrowed it down to contained a £ sign and so he had assumed it was a characterset issue. In fact his initial mail  stated

Problem field is C21_DPST_RFRNC2….
As a workaround this problem we have used following regular expression when creating a table at source so that it filters out the special characters.
regexp_replace(Regexp_replace(trim(lower(C21_DPST_RFRNC2)),'[^a-z,_, ,A-Z,0-9,@,&,$,£,%,'']',' '),'[[:space:]]{1,}',' ')

 Mistake one – blind acceptance by DBA of what developer had said.

My immediate thought was ‘oh sh……’ we have made a mess of the charactersets at some time and that is going to be a big issue. Given that I have just presented about standards and charactersets was one of the subjects that I touched upon that was not a good place to be.

Archived logs stuck ‘in-memory’ when building a standby

We had to build a new copy of a physical standby database (11.1.0.7) and the RMAN duplicate command had completed  successfully so all we needed to do was to let the archived logs catchup. After an hour or so this was the status as seen from v$archived_log

column "First Time" format A40
column applied format A10
column "Next Time" format A40
set linesize 120
set pagesize 1000

select   thread#, sequence#,       applied,
to_char(first_time,'DD-MON-YY:HH24:MI:SS') "First Time",
to_char(next_time,'DD-MON-YY:HH24:MI:SS') "Next Time"
from      v$archived_log
UNION
select   NULL,NULL,' ',NULL,null FROM DUAL
UNION
select   null,null,
db_unique_name,
database_role,
open_mode
from      v$database
/

and the output was (long list but necessary to see both threads). What baffled us why why there were in-memory logs and yet later logs  had been applied.