ASMCMD-08102: NO CONNECTION TO ASM; COMMAND REQUIRES ASM TO RUN

Platform         Microsoft Windows x64 (64-bit)
Product Version  11.2.0.2

DETAILED PROBLEM DESCRIPTION

+ASM instance is up/running and only accessible through EMGrid Control and cannot use 

sqlplus to connect to +ASM instance, but asmcmd fails with the following error:

 
ASMCMD-08102: no connection to ASM; command requires ASM to run

C:\Users\vmummadi>set oracle_home=C:\Oracle\Grid11gR2
C:\Users\vmummadi>SET ORACLE_SID=+ASM
C:\Users\vmummadi>asmcmd
Connected to an idle instance.
ASMCMD> lsdg
ASMCMD-08102: no connection to ASM; command requires ASM to run

 Enabled asmcmd tracing and the trace further shows:

C:\Users\vmummadi>set DBI_TRACE=1
C:\Users\vmummadi>set oracle_home=C:\Oracle\Grid11gR2
C:\Users\vmummadi>asmcmd
   DBI 1.602-ithread default trace level set to 0x0/1 (pid 7412) at DBI.pm line 273 via asmcmdshare.pm line 201
   -> DBI->connect(dbi:Oracle:, , ****, HASH(0x44ca968))
   -> DBI->install_driver(Oracle) for MSWin32 perl=5.010000 pid=7412 ruid=0 euid=0
      install_driver: DBD::Oracle version 1.20 loaded from C:/Oracle/Grid11gR2/perl/site/lib/DBD/Oracle.pm
            !! ERROR: '12560' 'ORA-12560: TNS:protocol adapter error (DBD ERROR: OCIServerAttach)' (err#0)
         DBI connect('','',...) failed: ORA-12560: TNS:protocol adapter error (DBDERROR: OCIServerAttach)
Connected to an idle instance.
ASMCMD>

check listener:

C:\Users\vmummadi>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 07-MAR-2013 08:49:35
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
---------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                22-FEB-2013 20:39:32
Uptime                    12 days 12 hr. 10 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Oracle\Grid11gR2\network\admin\listener.ora
Listener Log File         C:\Oracle\diag\tnslsnr\ATLAS\listener\alert\log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<omitted here>)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<omitted here>)(PORT=1521)))
Services Summary...
Service "+asm" has 1 instance(s).
 Instance "+asm", status READY, has 1 handler(s) for this service...
...
The command completed successfully

Errors:

The errors are misleading, including the ORA-12560 (TNS:protocol adapter error).

The clue to the underlying problem is coming from the ORA-1031 (insufficient privileges) 

during sqlplus "/ as sysasm"

C:\> set oracle_home=C:\Oracle\Grid11gR2

C:\> SET ORACLE_SID=+ASM
C:\> echo %ORACLE_SID%
+ASM
C:\> echo %ORACLE_HOME%
C:\Oracle\Grid11gR2
C:\> sqlplus "/ as sysasm"
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 13 12:03:09 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges 

Also, check if "SQLNET.AUTHENTICATION_SERVICES = (NTS)" is already set in the server's 

sqlnet.ora file

Solutions:

User is not part of the ORA_DBA OS group:

C:\Users\vmummadi>echo %username%
vmummadi
C:\Users\vmummadi>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
-------------------------------------------------------------------------------
.... <-- other users listed here except 'vmummadi'

NT AUTHORITY\SYSTEM
The command completed successfully.

The problem is to do with harden security in 11g that affects non-dba OS users with 

connections to Oracle instances.

If the user is not a member of the ORA_DBA OS group, then it either needs to connect to 

the ASM instance using the ASM orapw file (either using his/her own account or using the 

Oracle SYS account and as sysasm privilege) or be added to the OS ORA_DBA group.

Other workarounds include :

Add the non-dba OS user to the ASM's orapw file and grant it 'SYSASM' privileges:
Follow this -                                                                                                                                     http://docs.oracle.com/cd/E11882_01/server.112/e25494/dba007.htm#ADMIN12478
Allow the non-dba user to connect to the ASM instance using the SYS Oracle user account 
(for this, the non-dba user must know what is the password for SYS): 

Example: sqlplus sys@<hostname or IP address of the host>:<port were the listener
lis  tens>/+ASM as sysasm
Make the non-dba OS user a member of the ORA_DBA OS group so it can use bequeath connections 
(ie, not using the listener) to the asm instance (ie, sqlplus "/ as sysasm")                                                                                                                                                                       
             Oracle Support -ID 1537484.1   Reference: Bug 16470328 

Using Snapshot Standby Database On Oracle 11g

A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use. 

Snapshot database has following characteristics

1. Snapshot standby database receives and archives, but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.

3. Data from the primary database is always protected as the archives are being received and stored in place.

4. All local updates will be discarded when snapshot database is converted back to physical standby database.

5. If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.

6. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

7. After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.

8. Snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.

Once the snapshot standby is activated this database diverges from its primary database over time because redo data from the primary database is not applied.Again local updates to the snapshot standby database will cause additional divergence.

--> Steps to convert Physical Standby Database to the Snapshot Standby Database:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

1) If not already configured , configure flash recovery area as given below 

a) Set the size for recovery area. 

Alter system set db_recovery_file_dest_size=<size> 

b) Set Flash recovery area. 

Alter system set db_recovery_file_dest=<path> 

2) Bring the physical standby database to mount stage.  

3) Stop managed recovery if it is active.  

4) Convert physical standby database to snapshot standby database.  

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;  

The database is dismounted during conversion and must be restarted. 

Once the database is restarted  any transaction can be executed . 

SQL> select open_mode,database_role from v$database; 

OPEN_MODE                                  DATABASE_ROLE 
----------                                         ---------------- 
READ WRITE                                 SNAPSHOT STANDBY

Remember a guaranteed restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

--> Steps to convert the Snapshot Standby Database to the Physical Standby Database


1. Shutdown the snapshot standby database. 

2. Bring the database to the mount stage. 

3. Issue the command 

ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

4. Shutdown the database and mount it

SQL> select open_mode,database_role from v$database; 

OPEN_MODE        DATABASE_ROLE 
----------              ---------------- 
MOUNTED          PHYSICAL STANDBY 

5. Start the media recovery process.

Once a snapshot standby database has been converted back into a physical standby database and restarted, Redo Apply can be started and all redo received by the snapshot standby database will be applied to the physical standby database.

Flashback Database is used to convert a snapshot standby database back into a physical standby database. Any operation that cannot be reversed using Flashback Database technology will prevent a snapshot standby from being converted back to a physical standby.

If you are using the Data Guard Broker, you should use the below command to convert a physical standby database to a snapshot standby database and back.

DGMGRL> CONVERT DATABASE <db_unique_name> TO {SNAPSHOT | PHYSICAL} STANDBY;