Saturday, January 26, 2013

In ASM instance, ORA-01031: insufficient privileges and Connected to an idle instance, when connecting with conn / as sysasm in oracle 11.2


 
Cause:-
============
1) Setting up incorrectly all the home paths
2) you are not using a user which is in the asmadmin group

"Connected to an idle instance" is for incorrect set up of ASM instance .


Solution:-

We  need to export all the home paths corectly
ORACLE HOME should be grid home and also Grid home should be grid home..
export ORACLE_SID as +ASM1/+ASM2 ....

See following scenarios

1)      Wrong …..
==========================

[oracle@rac1 ~]$
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 26 20:36:07 2013

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

SQL>
SQL> conn / as sysasm
ERROR:
ORA-01031: insufficient privileges


2)      Wrong …..
==========================


[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@rac1 ~]$ export ORACLE_SID=+ASM
[oracle@rac1 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 26 20:46:29 2013

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

SQL> conn / as sysasm
Connected to an idle instance.
SQL>
SQL> exit


3)      Right …..
==========================


[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@rac1 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 26 20:47:10 2013

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

SQL> conn / as sysasm
Connected.
SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  TRUE
ASMSNMP                        TRUE  FALSE FALSE

SQL>
SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
+ASM1            STARTED

SQL>
SQL>


2 comments:

Anonymous said...

thanx it did work..
but how to make sys user work as sysasm an log in through OEM..!!??

ki-tae said...

Thanks, It worked

BUT

when i log in as sysdba
i should change the environmental variables appropriate for os user oracle

and when i log in as sysasm
i should do that for os user asm

should i change the envrionment everytime ?