Test Case for 11gR2 Role Separation issue on HP-UX – help wanted

On any HPUX (11:31) system where grid infrastructure has been applied with 2 software owners – in our case grid and oracle  – oracle’s best practise for GI implementations – RAC or standalone systems.

 The standard  unix account we are using  is , testuser (although in this case he is in the DBA group).

 This user is a member of the following groups :

 uid=664(testuser) gid=500(dba)

 Test Server       = server

Test database     = dbatest

 Login to the testuser user

1. setsid to set Oracle environment to dbatest

 The ASM luns are owned by the grid user as below :

  

[testuser@server][dbatest]/dev/grid $ls -ltr
total 0
crw-rw----   1 grid       asmdba      13 0x00000a Apr 27 12:38 disk002
crw-rw----   1 grid       asmdba      13 0x000009 Apr 27 22:00 disk001
crw-rw----   1 grid       asmdba      13 0x00000b Apr 28 12:44 disk003

 logon onto unix server as testuser

Login into the database as “sqlplus / as sysdba”

Set database environment variables as appropriate (OH, SID,PATH)

 Database bounced to clear cache out (shared pool flush would also work)

Huge asm_rbal_trace file with text UNINDENT OF DISK

You may notice a large and growing trace file in diag dest which contains numerous lines starting with the phrase “NOTE: Unident of disk ” followed by a disk path.

*** 2012-04-17 18:31:10.759
NOTE:Unident of disk:/dev/oracle_hr/rdisk130
NOTE:Unident of disk:/dev/oracle_hr/rdisk131
NOTE:Unident of disk:/dev/oracle_hr/rdisk132
NOTE:Unident of disk:/dev/oracle_hr/rdisk133
NOTE:Unident of disk:/dev/oracle_hr/rdisk134
NOTE:Unident of disk:/dev/oracle_hr/rdisk135
NOTE:Unident of disk:/dev/oracle_hr/rdisk136
NOTE:Unident of disk:/dev/oracle_hr/rdisk137
NOTE:Unident of disk:/dev/oracle_hr/rdisk138
NOTE:Unident of disk:/dev/oracle_hr/rdisk139
NOTE:Unident of disk:/dev/oracle_hr/rdisk129

*** 2012-04-17 19:05:20.540
NOTE:Unident of disk:/dev/oracle_hr/rdisk130
NOTE:Unident of disk:/dev/oracle_hr/rdisk131
NOTE:Unident of disk:/dev/oracle_hr/rdisk132
NOTE:Unident of disk:/dev/oracle_hr/rdisk133
NOTE:Unident of disk:/dev/oracle_hr/rdisk134
NOTE:Unident of disk:/dev/oracle_hr/rdisk135
NOTE:Unident of disk:/dev/oracle_hr/rdisk136
NOTE:Unident of disk:/dev/oracle_hr/rdisk137
NOTE:Unident of disk:/dev/oracle_hr/rdisk138
NOTE:Unident of disk:/dev/oracle_hr/rdisk139
NOTE:Unident of disk:/dev/oracle_hr/rdisk129

Curing unevenly balanced ASM diskgroups to reduce poor file distribution

Back in Nov 2011 I posted a question on the Oracle-L mailing group about my perception that ASM disk rebalances seemed to be required on DATA diskgroups in ASM  (never FRA, presumably because the FRA had lots of similar sized objects (flashlogs archivelogs  etc)) and even after rebalancing there seemed to be a permanent imbalance between disks of the same size. I was querying the effectiveness of the rebalancing operation.

There was some good responses including a script that Dave Herring had created, based on  key MOS articles: 818171.1 (Identifying Files with Imbalances), 351117.1 (Troubleshooting ASM Space Issues), 367445.1 (Advanced Balance and Space Report on ASM). Manual rebalancing  would normally not be required, because ASM automatically rebalances disk groups when their configuration changes. You might want to do a manual rebalance operation if you want to control the speed of what would otherwise be an automatic rebalance operation.

I have been chasing this down and have now come across Bug 7699985: UNBALANCED DISTRIBUTION OF FILES ACROSS DISKS.

It appears to be a problem in 11.1.0.7 and I know a number of sites have reported it. Despite  repeated manual rebalance operations there can be a variance of up to 10% between different disks of the same diskgroup, even if they are the same size. It is fixed in 11.2.0.1.  The workaround is to set the _asm_imbalance_tolerance parameter to be 0 rather than the default of 3.This controls the hundredths of a percentage of inter-disk imbalance to tolerate. Then rebalance the disks manually and reset the parameter back again to 3 as you don’t need to leave it  balancing all the time.

SQL> @asm_imbalance   (from Report the Percentage of Imbalance in all Mounted Diskgroups (Doc ID 367445.1)

@asm_imbalance.sql

 Columns Described in Script               Percent Minimum
                                 Percent Disk Size Percent  Disk Diskgroup
Diskgroup Name                 Imbalance  Varience    Free Count Redundancy
------------------------------ --------- --------- ------- ----- ----------
DATA                                 9.0        .5    15.2    84 EXTERN

SYS@+ASM SQL>l
    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min,
    max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
SY@+ASM SQL>/

NAME                               AU(Mb)        MIN        MAX    AVG
------------------------------ ---------- ---------- ---------- ------
DATA                                    1       7728      11599   8026

alter diskgroup data rebalance power 4;

NAME                               AU(Mb)        MIN        MAX    AVG
------------------------------ ---------- ---------- ---------- ------
DATA                                    1       7734      11483   8026 – no difference

alter system set "_asm_imbalance_tolerance"=0;
alter diskgroup data rebalance power 4;
NAME                               AU(Mb)        MIN        MAX        AVG
------------------------------ ---------- ---------- ---------- ----------
DATA                                    1       8020       8062   8026  - Now nicely rebalanced

Excellent Optimizer Statistics articles

For anybody who is interested in reading about optimizer statistics and gaining a clear understanding on what they can do and how they can be managed then I suggest reading the following two white papers

Part 1 – Understanding Optimizer Statistics

Part 2 – Best Practises for Gathering Optimizer Statistics

Part 2 contains the best, most easily understood explanation of the problems with bind variable peaking and how they were addressed by using adaptive cursor sharing that I have seen.

Overall both documents are well written with good explanations and diagrams and I think anybody who has any interest in the Oracle Database engine and the tuning of databases for both consistency and performance should make these articles a must read. 

 

 

Interview questions – one approach

In my last post I talked about doing a lot of interview questions lately. I have been thinking of changing my standard set of questions anyway so I thought it would be a good idea to write about the questions I have been asking and the responses I get.

Firstly let me see that the question itself is not important, what is more important is where it leads me and does it help me find out more about the candidate than I knew previously. Secondly, there is no right or wrong answer. If the interviewee does not have a clue but makes an educated guess based on other knowledge that is much better than just saying ‘I don’t know’, or what is more common, humming and aarring for 5 minutes hoping the answer will pop into his/her head.  PS I think aarring is a genuine word but I don’t think it is spelt like that.

Someone else normally asks about other areas such as Dataguard, RAC, builds, RMAN, patching etc. Not because I am not interested or aware but so that we get two different viewpoints. I tend to loosely ask about ‘performance’. I do not think I have seen a CV recently that does not claim to have tuned a database to perfection. Normally on further inquiry it breaks down to making the redo logs 500Mb instead of 100K or something similar.   I start off asking about a 10046 trace and what values you can provide to it to give different output. If that goes OK I ask about what a 10053 trace gives you (optimizer choices looked at and chosen)).

Alter system v Alter database – which to guess at

I have been doing a lot of interviewing recently and I have a set of questions which are quite generic but I like to use them to lead into areas where I can probe a bit deeper if the interviewee allows me to get  that far by answering the question correctly in the first place.

It struck me the other day that almost everyone almost guesses as to whether a SQL command begins with alter system or alter database if they do not know the exact syntax.  Now don’t get me wrong, I am not interested in asking OCP type questions  and wanting the exact syntax of a command but I like the candidate to be able to show he might have used it in the past. A good example of this is when talking about DataGuard and starting recovery on the standby side. If I am getting a warm feeling that the person might have actually done this before, which does not always align with  what the CV might suggest, then I might ask if they can remember the exact syntax to use.  I would be looking for some of the keywords in ‘alter database recover managed standby database using current logfile disconnect’ – not something that trips of the tongue easily.

The Mother of all ASM scripts

Back in 2009 I posted a script which I found very useful to review ASM disks. I gave that post the low-key title of The ASM script of all ASM scripts. Now that script has been improved I have to go a bit further with the hyperbole and we have the The Mother of all ASM scripts.  If it ever gets improved then the next post will just be called ‘Who’s the Daddy’.

I have been using the current script across all our systems for the last 3 years and I find it very useful, a colleague, Allan Webster, has added a couple of improvements and it is now better than before.

The improvements show current disk I/O statistics and a breakdown of the types of files in each disk group and the total sizes of that filetype. The I/O statistics are useful when you have a lot of databases, many of which are test and development and so you do not look at them as that often. It just gives a quick overview that allows you to get a feel if anything is wrong and to see what the system is actually doing. There are also a few comments at the beginning defining the various ASM views available.

Speeding up the gathering of incremental stats on partitioned tables

11G introduced incremental  global stats and the table WRI$_OPTSTAT_SYNOPSIS$ contains synopsis data for use in maintaining the global statistics. This table can grow very large and Robin Moffat has produced a good blog  post about  the space issues  - note we both worked at the same site so it is the same DW being discussed by both of us.

Apart from the space usage that Robin refers to, another worrying aspect is the time taken when gathering stats on a partitioned table and most of that time is taken by running a delete statement

DELETE
FROM SYS.WRI$_OPTSTAT_SYNOPSIS$
WHERE SYNOPSIS# IN
(SELECT H.SYNOPSIS#
FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ H
WHERE H.BO# = :B1
AND H.GROUP# NOT IN
(SELECT T.OBJ# * 2
FROM SYS.TABPART$ T
WHERE T.BO# = :B1
UNION ALL
SELECT T.OBJ# * 2
FROM SYS.TABCOMPART$ T
WHERE T.BO# = :B1))

I will demonstrate the problem and a simple solution and you will be able to see the significant performance improvements achieved.

My blog analysis of 2011

WordPress send out a nice email with lots of details about your blog in the last year. Apart from visits growing month on month two  points interested me. My most popular posts were all written several years ago and the ‘purging stats from SYSAUX’ is definitely my most widely read blog post. The reason for later posts not being as popular is that they are not as detailed. That is due to my change of role where I am managing a lot more and not having the time to devote a day or more to a problem and put  up a very detailed analysis. However in response to that I would suggest that I am putting a more eclectic set of posts up as I pick up on interesting problems we see on site and post about them, although not necessarily having done all the research myself. That helps me keep abreast of the technology and still be very closely linked in with what is happening, sufficiently well-informed as to be able to produce a post on the subject.

These are the posts that got the most views in 2011.

• 1 Purging statistics from the SYSAUX tablespace 15 comments May 2009
• 2 RMAN backup script – example – logging output 3 comments March 2008
• 3 ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver error 6 comments July 2008
• 4 11g – library cache mutex X – known bug 14 comments March 2008
• 5 Where has consistent=y gone when using Datapump export 3 comments December 2008

The second surprise of the WordPress summary is that I only created 17 new posts. That has really caught me out as I thought I had been more prolific than that. I will certainly try harder in 2012.

Nice notebook style blog site

It is not often that I would post just to mention someone else’s blog but there is always a first time.

In the idle time that generally happens between Xmas and New Year I was catching up with various mails that I  had marked as ‘follow-up – today’, which is quite a misnomer for me as I rarely follow-up and never on the same day. I came across a post from Karl Arao on Oracle-L in which he mentioned a  script stored on a site he runs.

Karl describes it as “Just another web notebook about Oracle,Linux,Troubleshooting,Performance,etc..etc” and that is a good summary. It just contains excerpts of notes and lots of links to other web articles. It just seems a different format to most blogs and I found it interesting to browse down the r/h menu list and look at a few interesting notelets. I suspect mots of us have something similar, mine is a Word document called vodafone_notes.do although it is 5 years and 6 sites since I worked there. The difference is that this is an online shared utility and that is what attracted it to me.

Karl no doubt will be sending me as hamper as a thank you for directing thousands of hits to his site.

My next 2 posts will cover  :-

• altering WRI$_OPTSTAT_SYNOPSIS  tables to speed up the gathering of stats
• adding a trigger to detect when a standby database is open, in which case it will  to turn off the dataguard broker.

There I have committed myself  – but note that I am using the nowait option of commit so it might be a while

Best wishes to my readers in 2012.