Oracle DBA – A lifelong learning experience

Posts Tagged ‘ASM’

New ASM power levels in 11.2.0.2 and beyond

Posted by John Hallas on February 13, 2015

I recently saw the following command in a script that was to be run and thought an error had been made and the power level should have been 5 not 500.

ALTER DISKGROUP DATA REBALANCE POWER 500;

Upon doing some research it was not a mistype but a new method of disk balancing which came in from 11.2.0.2

Previously setting the power limit from 0 to 11 basically caused an additional number of ARBx process to be created to match the power level and these were removed once the rebalance had finished.

That was a nice simple situation which I had no problem with. The range was adequate and I normally used between 4 and 7 depending on the usage of the system and any performance impact that might be caused. The impact was easy to monitor using a variety of tools as top or glance on a *nix platform

Now from 11GR2 onwards and when a database has disk group ASM compatibility set to 11.2.0.2 or greater the operational range of values is 0 to 1024 for the rebalance power. Note that if the value of the POWER clause is specified larger than 11 for a disk group with ASM compatibility set to less than 11.2.0.2, then a warning is displayed and a POWER value equal to 11 is used for rebalancing. Second point to note is that if a disk group is altered to a higher RDBMS value this operation cannot be reversed.

So what does that mean in practise? Well in my eyes it seems to be a basic change but it now seems very hard, well-nigh impossible to see the impact that the re-balance is having on the server and consequently I do not see the advantages of it other than possibly on massively high-end systems. Read the rest of this entry »

Advertisements

Posted in 11g new features, ASM, Oracle | Tagged: , , | 2 Comments »

Adding new ASM disks – what is best practise?

Posted by John Hallas on January 15, 2013

According to My Oracle Support note – “How To Add a New Disk(s) to An Existing Diskgroup on RAC (Best Practices). [ID 557348.1]” you should create a test diskgroup using new storage before adding it to an existing diskgroup. That seems eminently sensible, although it is not something I normally do. It proves you can access the disk, and if there is a conflict (i.e the disk is already mapped and in use elsewhere) you are not risking your production DATA diskgroup. I have pasted the note info at the bottom of this post but basically you just create  a new diskgroup and add the new disk to it. If all is OK then drop the diskgroup and add the new disk to your existing diskgroup.

However the downside of that is that you can hit non-published bug:12398300 which is a duplicate of bug:12356910 (also non-published). Diskgroup Mount Hangs with RBAL Waiting on ‘GPnP Get Item’ and ‘enq: DD – contention’ [ID 1375505.1].  Note: This issue so far has been reported on RAC 11.2.0.2.3 and 11.2.0.3 environments which is where we saw it (RAC – 11.2.0.3 clusterware – 11.2.0.1 rdbms)

Simply the ALTER DISKGROUP MOUNT just hangs and has to be interrupted (CTRL-C). No errors in the ASM alert log

Killing the ora.gpnpd on the node when ASM is blocked in the gpnp wait, permits not having  to stop the ASM instance. For details, please see Note:1392934.1. Otherwise, restart the ASM instance that is causing the lock condition.

The fix will be included in future 11.2.0.3.x Patch Set Updates (PSUs) but no patches yet exists (at the time this article was written – Nov.9.2011). Also likely there will be patch requests for the fix to be included on top of existing 11.2.0.2 PSU, but none yet exist.

So I think I will stick with what I have always done, best practise or not.

--From Node 1

. oraenv

-- specify ASM instance from node 1

+ASM1

-- sudo -u oracle sqlplus may not work when run the first time so run:

sudo -u oracle ls

sudo -u oracle sqlplus / as sysasm

CREATE DISKGROUP TEST EXTERNAL REDUNDANCY DISK '' [DISK ''];

SELECT STATE, NAME FROM V$ASM_DISKGROUP;

-- from node 2

. oraenv

+ASM2

sudo -u oracle sqlplus / as sysasm

 

ALTER DISKGROUP TEST MOUNT;

SELECT STATE, NAME FROM V$ASM_DISKGROUP;

 

-- if all ok then

-- from node 2

alter diskgroup test dismount;

 

-- from node 1

DROP DISKGROUP TEST;

Now we can add the disk to the desired diskgroup safely.

 

-- From node 1

. oraenv

+ASM1

sudo -u oracle sqlplus / as sysasm

-- check disks visible in v$asm_disk

-- header_status should be CANDIDATE or FORMER

set lines 120 pages 100

column path format a20

SELECT name, path, mode_status, state, header_status, os_mb, free_mb

FROM v$asm_disk ORDER BY name, path;

-- check diskgroups

select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from v$asm_diskgroup;

-- add disks to appropriate diskgroups

alter diskgroup x add disk '/dev/hdiskX';

-- monitor rebalance

set lines 170

select * from v$asm_operation;

select GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB from v$asm_diskgroup;

Posted in ASM, Oracle | Tagged: , , , , | 2 Comments »

The Mother of all ASM scripts

Posted by John Hallas on March 6, 2012

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. Read the rest of this entry »

Posted in ASM, Oracle, scripts | Tagged: , , | 13 Comments »

Much faster performance with ASM – a real world example

Posted by John Hallas on October 9, 2009

We have a  Peoplesoft database that  manages HR for ~80,000 staff.  Oracle 10.2.03.  Peoplesoft Tools 8.49 PS HR 9 HPUX 11.31 Itanium

There has been long running problems caused by I/O bottlenecks writing down to Hitachi XP SAN disks. We were using filesystem storage and disk_asynch_io was set to false

The root cause of our problem was that the Time and Labour batches contend when they need to acquire exclusive access to write dirty blocks to data files during truncate operations. 

David Kurtz has written about a specific Peoplesoft feature around truncate http://www.go-faster.co.uk/docs.htm#local_write_wait

 We migrated to using ASM disk and achieved significant benefits.

 The testing stages were

1)       Prepare a 2 hour Time and labour batch run which would generate significant throughput

2)       Capture a baseline at 10.2.0.3

3)       Upgrade to 10.2.0.4 and retest

4)       Upgrade to 10.2.0.4 and migrate to ASM (11.1.0.7) and retest

5)       Upgrade to 11.1.0.7 and migrate to ASM (11.1.0.7) and retest

 

Test 3 did not show any difference at all. We were still blocked on disk, which was expected. We probably would have remained at 10.2.0.3 but gone to ASM but there are problems with seeing ASM disk when using 10.2.0.3 (Metalink Note 434500, ORA-15059) so we had the option of applying a fix to get ASM working with 10.2.0.3 or upgrading to 10.2.0.4. We decided that the benefits of getting onto a terminal release and suggestions from Oracle that small changes in the truncate process had been made between 10.2.0.3 and 10.2.0.4 out-weighed the overhead of any application testing that we needed to do.

Test 4 gave us big performance benefits. Test 5 was not as clear cut but we knew some code had to be optimised and we did not have the luxury of time to address the issues. Remaining on 10g minimised the application testing, certainly compared to the testing require to upgrade to 11g.

The benefits can be seen quite clearly in the graph below

asynch_io

This shows a 2 hour period where a constant throughput of T&L jobs were being processed. In parallel a constant online load was being simulated

The black line shows throughput and elapsed time. The run consists of a number of jobs which take 2 hours to complete and batch time is around the 450 – 600 second mark

The same processing is then run with 10.2.0.4 database and ASM. disk_aysnch_io is set to FALSE. The jobs complete 40 minutes faster and elapsed time is around the 300-400 second mark but showing spikes and inconsistent performance

Finally the orange line shows 10.2.0.4 database and ASM. disk_aysnch_io =TRUE. The processing time has now been reduced to just over an hour and the elapsed time is significantly better at an average of 250 seconds and with a very consistent profile.

As with all things in life we have now moved onto the next bottleneck which is the wait flashback buf free caused because we have enabled flashback logging and as the redo log buffer fills up we wait for flashback to be written out. However this is a miniscule issue compared to the original problems.  We have taken flashback off some tablespaces primarily used for PS temporary tables (aka working storage tables) although that does give us an issue when when rebuilding the primary database after a DataGuard failover using flashback logs

I hope this real world example is of use

Posted in ASM, Oracle | Tagged: , , , , , , , , | 1 Comment »

Managing control files in ASM

Posted by John Hallas on September 28, 2009

Just a couple of notes on how to create multiple ASM control files and moving control files toa different diskgroup.

Assuming we already have a ASM control file in the DATA diskgroup and we wish to add one to the FRA diskgroup

 SQL>show parameter control_files
+DATA/SID/controlfile/current.730.698514673
alter system set control_files='+DATA','+FRA' scope=spfile;
shutdown immediate;
startup nomount;
exit
rman target
/restore controlfile from '+DATAHR/MHRPRD1A/CONTROLFILE/current.730.698514673';
alter database mount;
alter database open;
SQL>show parameter control_files
+DATA/SID/controlfile/current.730.698514673, +FRAHR/SID/controlfile/current.766.698514673

 
                                                                           

Here is an example of how to migrate from one ASM diskgroup to another

Add the new disk discovery path in (if needed), create a diskgroup and add disks

alter system set asm_diskstring='/dev/oracle','/dev/oracle_hr/r*';

create diskgroup DATANEW  external redundancy disk '/dev/oracle_hr/rdisk1','/dev/oracle_hr/rdisk2';

SQL>show parameter control_files

control_files                        string      +DATA10/SID/controlfile/c urrent.796.695384211
alter system set CONTROL_FILES = '+DATANEW' scope=spfile

shutdown
rman target /
startup nomount
restore controlfile  from  '/+DATA/SID/controlfile/current.796.695384211';
alter database mount;
alter database open;

Posted in ASM, Oracle | Tagged: , , | 5 Comments »

Oracle 11G Release 2 available (Linux only though)

Posted by John Hallas on September 2, 2009

Linux only at the moment

 http://www.oracle.com/technology/products/database/oracle11g/index.html

  11GR2 documentation

 http://www.oracle.com/pls/db112/homepage

 11GR2 new features

 http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/toc.htm

  new features index page shows the following (of which I will be looking at ASM first)

 1.1 Application Development

1.10.4.3 XMLIndex Enhancements

Posted in 11g new features, ASM | Tagged: , , , | Leave a Comment »

ASM space marked as internal – use check all repair

Posted by John Hallas on July 30, 2009

The problem was that the FRA diskgroup seemed to be using a lot of space and and yet there was hardly  files on  disk as far as I could tell. HP Itanium – 11.1.0.7

ASM Disk Groups
===============

     Group Group Name                State           Type      Total GB Free GB
---------- ------------------------- --------------- ------- ---------- -------
         1 DATA                      MOUNTED         EXTERN        2581     109
         2 FRA                       MOUNTED         EXTERN        1602     127

 
Group Disk Header    Mode     State           Redundancy   Total MB    Free MB Disk Name                      Failure Gr
----- ---- --------- -------- --------------- ---------- ---------- ---------- ------------------------------ ----------
    2    1 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0001                       FRA_0001
    2    2 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0002                       FRA_0002
    2    4 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0004                       FRA_0004
    2    5 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0005                       FRA_0005
    2    6 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0006                       FRA_0006
    2    7 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0007                       FRA_0007
    2    8 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0008                       FRA_0008
    2    9 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0009                       FRA_0009
    2   10 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0010                       FRA_0010
    2   11 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0011                       FRA_0011
    2   12 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0012                       FRA_0012
    2   13 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0013                       FRA_0013
    2   14 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0014                       FRA_0014
    2   15 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0015                       FRA_0015
    2   16 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0016                       FRA_0016
    2   17 MEMBER    ONLINE   NORMAL          UNKNOWN         91138          0 FRA_0017                       FRA_0017
    2   18 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      65242 FRA_0018                       FRA_0018
    2   19 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      65112 FRA_0019                       FRA_0019

 I had removed one disk from FRA and forced a rebalance but that still did not release space. I had checked the FRA disks using asmcmd but that only showed 2 online logs which were 2Gb each (we were not in archive log mode).

I had checked the diskgroup using the EM Gui which issued the command  

ALTER DISKGROUP FRA CHECK ALL:

but still no success. Looking at the documentation the check command taking NOREPAIR as the default should have reported any issues into the ASM alert log.  Which indeed it did (and I would have seen them if I had bothered to look)

Wed Jul 29 16:37:55 2009
SQL> ALTER DISKGROUP FRA CHECK DISK FRA_0001,FRA_0002,FRA_0004,FRA_0005,FRA_0006,FRA_0007,FRA_0008,FRA_0009,FRA_0010,FRA_0011,FRA_0012,FRA_0013,FRA_0014,FRA_0015,FRA_0016,FRA_0017,FRA_0018,FRA_0019
WARNING: Deprecated privilege SYSDBA for command 'ALTER DISKGROUP CHECK'
kfdp_checkDsk(): 33
NOTE: disk FRA_0001, used AU total mismatch: DD=4294965892, AT=2283
kfdp_checkDsk(): 34
NOTE: disk FRA_0002, used AU total mismatch: DD=4294966012, AT=2283
kfdp_checkDsk(): 35
NOTE: disk FRA_0004, used AU total mismatch: DD=4294966210, AT=2283
kfdp_checkDsk(): 36
NOTE: disk FRA_0005, used AU total mismatch: DD=4294966625, AT=2283
kfdp_checkDsk(): 37
NOTE: disk FRA_0006, used AU total mismatch: DD=4294965615, AT=2280
kfdp_checkDsk(): 38
NOTE: disk FRA_0007, used AU total mismatch: DD=4294964292, AT=2283
kfdp_checkDsk(): 39
NOTE: disk FRA_0008, used AU total mismatch: DD=4294967237, AT=2284
kfdp_checkDsk(): 40
NOTE: disk FRA_0009, used AU total mismatch: DD=4294965595, AT=2280
kfdp_checkDsk(): 41
NOTE: disk FRA_0010, used AU total mismatch: DD=4294966758, AT=2279
kfdp_checkDsk(): 42
NOTE: disk FRA_0011, used AU total mismatch: DD=4294966324, AT=2279
kfdp_checkDsk(): 43
NOTE: disk FRA_0012, used AU total mismatch: DD=4294966743, AT=2279
kfdp_checkDsk(): 44
NOTE: disk FRA_0013, used AU total mismatch: DD=4294964514, AT=2278
kfdp_checkDsk(): 45
NOTE: disk FRA_0014, used AU total mismatch: DD=4294965734, AT=2278
kfdp_checkDsk(): 46
NOTE: disk FRA_0015, used AU total mismatch: DD=4294965204, AT=2279
kfdp_checkDsk(): 47
NOTE: disk FRA_0016, used AU total mismatch: DD=4294965599, AT=2279
kfdp_checkDsk(): 48
NOTE: disk FRA_0017, used AU total mismatch: DD=4294964950, AT=2280
kfdp_checkDsk(): 49
NOTE: disk FRA_0018, used AU total mismatch: DD=25896, AT=2278
kfdp_checkDsk(): 50
NOTE: disk FRA_0019, used AU total mismatch: DD=26026, AT=2280
WARNING: deprecated use of ALTER DISKGROUP CHECK arguments
SUCCESS: ALTER DISKGROUP FRA CHECK DISK FRA_0001,FRA_0002,FRA_0004,FRA_0005,FRA_0006,FRA_0007,FRA_0008,FRA_0009,FRA_0010,FRA_0011,FRA_0012,FRA_0013,FRA_0014,FRA_0015,FRA_0016,FRA_0017,FRA_0018,FRA_0019

From the command line I then ran the comand

ALTER DISKGROUP FRA CHECK ALL REPAIR;

and I had immediate success. It took about 1 minute and corrected the issues and then released the space that had been grabbed internally.

     Group Group Name                State           Type      Total GB Free GB
---------- ------------------------- --------------- ------- ---------- -------
         1 DATA                      MOUNTED         EXTERN        2581     109
         2 FRA                       MOUNTED         EXTERN  1602   1,562

Group Disk Header    Mode     State           Redundancy   Total MB    Free MB Disk Name                      Failure Gr
----- ---- --------- -------- --------------- ---------- ---------- ---------- ------------------------------ ----------
    2    1 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0001                       FRA_0001
    2    2 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0002                       FRA_0002
    2    4 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0004                       FRA_0004
    2    5 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0005                       FRA_0005
    2    6 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0006                       FRA_0006
    2    7 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88855 FRA_0007                       FRA_0007
    2    8 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88854 FRA_0008                       FRA_0008
    2    9 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0009                       FRA_0009
    2   10 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0010                       FRA_0010
    2   11 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0011                       FRA_0011
    2   12 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0012                       FRA_0012
    2   13 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88860 FRA_0013                       FRA_0013
    2   14 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88860 FRA_0014                       FRA_0014
    2   15 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0015                       FRA_0015
    2   16 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88859 FRA_0016                       FRA_0016
    2   17 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0017                       FRA_0017
    2   18 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88860 FRA_0018                       FRA_0018
    2   19 MEMBER    ONLINE   NORMAL          UNKNOWN         91138      88858 FRA_0019                       FRA_0019

 

SQL> alter diskgroup fra check all repair
NOTE: starting check of diskgroup FRA
kfdp_checkDsk(): 51
WARNING: disk FRA_0001, changing DD used AUs from 4294965892 to 2283
kfdp_checkDsk(): 52
WARNING: disk FRA_0002, changing DD used AUs from 4294966012 to 2283
kfdp_checkDsk(): 53
WARNING: disk FRA_0004, changing DD used AUs from 4294966210 to 2283
kfdp_checkDsk(): 54
WARNING: disk FRA_0005, changing DD used AUs from 4294966625 to 2283
kfdp_checkDsk(): 55
WARNING: disk FRA_0006, changing DD used AUs from 4294965615 to 2280
kfdp_checkDsk(): 56
WARNING: disk FRA_0007, changing DD used AUs from 4294964292 to 2283
kfdp_checkDsk(): 57
WARNING: disk FRA_0008, changing DD used AUs from 4294967237 to 2284
kfdp_checkDsk(): 58
WARNING: disk FRA_0009, changing DD used AUs from 4294965595 to 2280
kfdp_checkDsk(): 59
WARNING: disk FRA_0010, changing DD used AUs from 4294966758 to 2279
kfdp_checkDsk(): 60
WARNING: disk FRA_0011, changing DD used AUs from 4294966324 to 2279
kfdp_checkDsk(): 61
WARNING: disk FRA_0012, changing DD used AUs from 4294966743 to 2279
kfdp_checkDsk(): 62
WARNING: disk FRA_0013, changing DD used AUs from 4294964514 to 2278
kfdp_checkDsk(): 63
WARNING: disk FRA_0014, changing DD used AUs from 4294965734 to 2278
kfdp_checkDsk(): 64
WARNING: disk FRA_0015, changing DD used AUs from 4294965204 to 2279
kfdp_checkDsk(): 65
WARNING: disk FRA_0016, changing DD used AUs from 4294965599 to 2279
kfdp_checkDsk(): 66
WARNING: disk FRA_0017, changing DD used AUs from 4294964950 to 2280
kfdp_checkDsk(): 67
WARNING: disk FRA_0018, changing DD used AUs from 25896 to 2278
kfdp_checkDsk(): 68
WARNING: disk FRA_0019, changing DD used AUs from 26026 to 2280
SUCCESS: check of diskgroup FRA found no errors
WARNING: deprecated use of ALTER DISKGROUP CHECK arguments
SUCCESS: alter diskgroup fra check all repair
Thu Jul 30 10:31:33 2009

 

I would have expected the gui to have reported errors but it didn’t. Equally I can see no good reason  for not fixing errors it identified when a check is run. That was the default in 10G  so  there must be a good reason why it was changed. Perhaps it had a performance impact which I would not have noticed on a pretty empty diskgroup. The other reason is that it might make the assumption that the DBA would want a good backup before repairing.

As we had nothing in there and we backup ASM metadata every day then I was happy to go ahead. https://jhdba.wordpress.com/2009/06/11/script-to-backup-asm-metadata/

 So the lessons learned are

  1. Use the check all feature
  2. Check the alert log afterwards
  3. Then use the check all repair statement from command line

Posted in 11g new features, ASM, Oracle | Tagged: , , , , , , | 3 Comments »

The ASM script of all ASM scripts !

Posted by John Hallas on February 1, 2009

The asm information script I use which gives me everything I think I need in one go.

If there are any queries that others find useful please comment on them and I will add them to the script.

 

Credit where credit is due. I think Alan Cooper wrote the original version, although it has been amended since then.

 

set wrap off

set lines 120

set pages 999

col “Group Name”   form a25

col “Disk Name”    form a30

col “State”  form a15

col “Type”   form a7

col “Free GB”   form 9,999

 

prompt

prompt ASM Disk Groups

prompt ===============

select group_number  “Group”

,      name          “Group Name”

,      state         “State”

,      type          “Type”

,      total_mb/1024 “Total GB”

,      free_mb/1024  “Free GB”

from   v$asm_diskgroup

/

 

prompt

prompt ASM Disks

prompt =========

 

col “Group”          form 999

col “Disk”           form 999

col “Header”         form a9

col “Mode”           form a8

col “Redundancy”     form a10

col “Failure Group”  form a10

col “Path”           form a19

 

select group_number  “Group”

,      disk_number   “Disk”

,      header_status “Header”

,      mode_status   “Mode”

,      state         “State”

,      redundancy    “Redundancy”

,      total_mb      “Total MB”

,      free_mb       “Free MB”

,      name          “Disk Name”

,      failgroup     “Failure Group”

,      path          “Path”

from   v$asm_disk

order by group_number

,        disk_number

/

 

prompt

prompt Instances currently accessing these diskgroups

prompt ==============================================

col “Instance” form a8

select c.group_number  “Group”

,      g.name          “Group Name”

,      c.instance_name “Instance”

from   v$asm_client c

,      v$asm_diskgroup g

where  g.group_number=c.group_number

/

 

prompt

prompt Current ASM disk operations

prompt ===========================

select *

from   v$asm_operation

/

 

prompt

prompt free ASM disks and their paths

prompt ===========================

select header_status , mode_status, path from V$asm_disk

where header_status in (‘FORMER’,’CANDIDATE’)

/

 

clear columns

Posted in ASM, Oracle, scripts | Tagged: , , | 4 Comments »

ASM – WARNING: Deprecated privilege SYSDBA for command xxx

Posted by John Hallas on December 12, 2008

The error message WARNING: Deprecated privilege SYSDBA for command ‘ALTER DISKGROUP MOUNT’ can be seen in an asm alert log if the command has been run as a sysdba user rather than a sysasm user

From the Oracle manual :-

SYSASM is a system privilege that enables the separation of the SYSDBA database administration privilege from the ASM storage administration privilege. Access to the SYSASM privilege is granted by membership in an operating system group that is designated as the OSASM group. This is similar to SYSDBA and SYSOPER privileges, which are system privileges granted through membership in the groups designated as the OSDBA and OSOPER operating system groups. You can designate one group for all of these system privileges, or you can designate separate groups for each operating system privilege.

You can divide system privileges during ASM installation, so that database administrators, storage administrators, and database operators each have distinct operating system privilege groups. Use the Custom Installation option to designate separate operating system groups as the operating system authentication groups for privileges on ASM. Table 3-1 lists the operating system authentication groups for ASM, and the privileges that their members are granted:

Table 3-1 Operating System Authentication Groups for ASM

Group

Privilege Granted to Members

OSASM

SYSASM privilege, which provides full administrative privilege for the ASM instance.

OSDBA for ASM

SYSDBA privilege on the ASM instance. This privilege grants access to data stored on ASM, and in the current release, grants the SYSASM administrative privileges.

OSOPER for ASM

SYSOPER privilege on the ASM instance.

 

If you do not want to divide system privileges access into separate operating system groups, then you can designate one operating system group as the group whose members are granted access as OSDBA, OSOPER, OSASM, and OSDBA for ASM, and OSOPER for ASM privileges. The default operating system group name for all of these is dba. You can also specify OSASM, OSDBA for ASM, and OSOPER for ASM when you perform a custom installation of ASM. Furthermore, you can specify OSDBA and OSOPER when performing a custom database installation.

Whether you create separate operating system privilege groups or use one group to provide operating system authentication for all system privileges, you should use SYSASM to connect to and administer an ASM instance. In Oracle 11g release 1, both SYSASM and SYSDBA are supported privileges; however, if you use the SYSDBA privilege to administer an ASM instance, then Oracle will write warning messages to the alert log, indicating that the SYSDBA privilege is deprecated on an ASM instance for administrative commands. In a future release, the privilege to administer an ASM instance with SYSDBA will be removed.

 

server:/app/oracle/diag/asm/+asm/+ASM/trace $ sqlplus / as sysasm;

SQL*Plus: Release 11.1.0.7.0 – Production on Fri Dec 12 14:13:42 2008

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

SQL> show user

USER is “SYS”

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

server:/app/oracle/diag/asm/+asm/+ASM/trace $ id

uid=500(oracle) gid=500(dba)

 

 

Posted in ASM, Oracle | Tagged: , , , | 1 Comment »

asm 11g compatibility settings and conversion to H.A.R.D format

Posted by John Hallas on December 12, 2008

When using ASM with 11g and taking the default settings from a DBCA build the following line can be seen in the asm alert_log

Spfile /app/oracle/product/11.1.0/asm/dbs/spfile+ASM.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format

Firstly H.A.R.D. refers to Oracle’s Hardware Assisted Resilient Data (H.A.R.D) Initiative which Metalink note 227671.1 summarises as

To use HARD validation, all datafiles and log files are placed on HARD-compliant storage.

The user must also enable the HARD validation feature on the storage, using the vendor-provided interface. When Oracle writes data to the storage, the storage system validates the data. If it appears to be corrupted, then the write is rejected with an error.

The sql commands here are taken from an excellent note on the Pythian site by Alex Gorbachev http://www.pythian.com/blogs/1078/oracle-11g-asm-diskgroup-compatibility.

In short the compatibility settings allow ASM to manage databases from both 10g and 11g. As this was an 11g environment only I felt comfortable in amending the parameters.

The compatible.asm parameter defines the minimum version of the asm software that is using the disk groups. As the format of the metadata on the disk changes from asm 10 to asm 11 then this setting defines that format. This will be non-reversible.

rdbms.compatible defines the minimum version of the rdbms compatible parameter for any databse that uses the asm disk groups.

col COMPATIBILITY form a10

col DATABASE_COMPATIBILITY form a10

col NAME form a20

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBIL DATABASE_C

———— ——————– ———- ———-

1 FRA 10.1.0.0.0 10.1.0.0.0

2 DATA 10.1.0.0.0 10.1.0.0.0

SQL> col value form a10

select group_number, name, value from v$asm_attribute;

no rows selected

SQL> alter diskgroup FRA set attribute ‘compatible.asm’=’11.1’;

Diskgroup altered.

alter diskgroup DATA set attribute ‘compatible.asm’=’11.1’

Diskgroup altered.

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBIL DATABASE_C

———— ——————– ———- ———-

1 FRA 11.1.0.0.0 10.1.0.0.0

2 DATA 11.1.0.0.0 10.1.0.0.0

SQL> select group_number, name, value from v$asm_attribute;

GROUP_NUMBER NAME VALUE

———— ——————– ———-

1 disk_repair_time 3.6h

1 au_size 8388608

1 compatible.asm 11.1.0.0.0

1 compatible.rdbms 10.1.0.0.0

2 disk_repair_time 3.6h

2 au_size 8388608

2 compatible.asm 11.1.0.0.0

2 compatible.rdbms 10.1.0.0.0

8 rows selected.

alter diskgroup data set attribute ‘compatible.rdbms’=’11.1’

alter diskgroup fra set attribute ‘compatible.rdbms’=’11.1’

alter diskgroup data set attribute ‘compatible.rdbms’=’11.1’

alter diskgroup fra set attribute ‘compatible.rdbms’=’11.1’

select group_number, name, value from v$asm_attribute;

GROUP_NUMBER NAME COMPATIBIL DATABASE_C

———— ——————– ———- ———-

1 FRA 11.1.0.0.0 11.1.0.0.0

2 DATA 11.1.0.0.0 11.1.0.0.0

Restart the database and lo and behold, no spfile compatibility error

Posted in ASM, Oracle | Tagged: , , , , , | Leave a Comment »