Monday, February 10, 2014

ORA-15260 and ORA-15027

I am writing this post to show the trivial mistakes we could do while transitioning from 10g/11gR1 ASM to 11gR2 ASM/Grid Infrastructure.Yes I did make it as well !
  
       $ sqlplus / as sysdba
     SQL> ALTER diskgroup DATA mount;
         ALTER diskgroup DATA mount
         *
         ERROR AT line 1:
         ORA-15032: NOT ALL alterations performed
         ORA-15260: permission denied ON ASM disk GROUP

 
   This is not because you have some wrong privileges for the disks or diskgroups but we have got to the old habit of logging in as sysdba to ASM instance.Login as sysasm and this will go away, Starting from Oracle 11g, SYSASM role should be used to administer the ASM instances.

  
  
         $ sqlplus / as sysasm
     SQL> ALTER diskgroup DATA mount;
   
         Diskgroup altered.

       
       
        When I tried to drop the data diskgroup I get this error ORA-15027, I did login as sysasm.
       
       
         $ sqlplus / as sysasm
    SQL> drop diskgroup DATA including contents;
       
         *

         ERROR at line 1:
         ORA-15039: diskgroup not dropped
         ORA-15027: active use of diskgroup "DATA" precludes its dismount
 
        The next obvious thing I do is to check the V$ASM_CLIENT to see the active sessions. But without any results.
       
        
        SQL> select * from v$asm_client;

          no rows selected
        
         The reason for this is that I have the spfile for the ASM instance in the same diskgroup and its used by the instance. I had to move the spfile out of the diskgroup , in my case out of ASM since this is the only diskgroup we have. The issue got resolved after this.

        
        
           SQL> create pfile='$ORACLE_HOME/dbs/init+ASM.ora' from spfile='';
     SQL> shutdown immediate;
     SQL> startup pfile=$ORACLE_HOME/dbs/init+ASM.ora
     SQL> drop diskgroup DATA including contents;

No comments: