ASM – Adding and dropping disks in one command

One of the possibilities within ASM that is not widely documented is the opportunity to add disks, drop disks and set the rebalance power all in one command.

One might wonder when you might be both adding and dropping disks simultaneously from the same disk group. Recently we have been migrating to a new storage array and this command comes in very useful in that situation

alter diskgroup data add disk '/dev/oracle/disk100','dev/oracle/disk101','dev/oracle/disk102'

drop disk '/dev/oracle/disk197','dev/oracle/disk198','dev/oracle/disk199'

rebalance power 5;

 

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.

Database SIG – Manchester 27th March

The UKOUG have done really well in organising the next Database Server SIG. It is being held at the City of Manchester Stadium – the home of Manchester City. That is the group of players bought on the cheap and managed by Roberto Mancini into the best team in Britain as will be proved when we win the Premier League in April.

They have got two of the worlds’ top Oracle speakers on stage. Actually that is slightly incorrect. They have one of the worlds’ top Oracle speakers talking twice and me also on stage.

The agenda looks very strong

Jonathan Lewis  – Beating the Oracle Optimizer
Phillip Brown – Experiences of a DBA
Jonathan Lewis  – Redo
Peter Homes  – Oracle 10g/11g Automatic Memory management  – cautionary tales
John Hallas – ADRCI (will try and add some new stuff to the talk I gave at the conference)

The date is March 27th and this is the link to it

As we have recently removed AMM from a large Peoplesoft database (10g) and an important EBS (11g)  database in the last couple of months because of problems whereby the constant  resizing of memory chunks eventually caused the databases to hang  I will be interested to hear what Peter has to say. In fact I did post an entry on the problems caused by AMM  – (the EBS database in this case).

I am looking forward to the day

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.