Applies to:

Oracle Net Services - Version: 10.1.0.4.0 and later   [Release: 10.1 and later ]
Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.1.0.6   [Release: 9.2 to 11.1]
Enterprise Manager for RDBMS - Version: 10.2.0.3 and later    [Release: 10.2 and later]
Enterprise Manager Grid Control - Version: 10.2.0.4 and later    [Release: 10.2 and later]
Information in this document applies to any platform.

Goal

Connecting to ASM instance remotely using Oracle Net and tools like SQL*Plus.

This note will resolve help resolve the following errors when trying to connect to an ASM isntance remotely

ORA-12505 TNS:listener could not resolve SID given in connect descriptor
ORA-12541: TNS:no listener
ORA-15000: command disallowed by current instance type
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
ORA-1031: insufficient privileges

Solution

This solution cannot be accomplished using Network Manager (GUI)

The solution is to
   * Edit the listener.ora on ASM server
   * Edit the tnsnames.ora on the client
   * Setup your remote login password for your ASM instance on the ASM server
   * Set your SYS password using ORAPWD for the ASM instance
   * Use the properly formatted connect string to connect with your tool

1) Edit the listener.ora on ASM server

   a) Logon to the ASM/Database server

   b) Locate your listener.ora (typically located in the $ORACLE_HOME/network/admin)

   c) Add a SID_LIST_LISTENER entry for your ASM instance (see example below)

EXAMPLE

SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
         (SID_NAME = +ASM)
         (ORACLE_HOME = c:\oracle\app\product\11.1.0\db_1)
      )
   )

d) Stop the listener

lsnrctl stop

e) restart the listener

lsnrctl start

2) Edit the tnsnames.ora on the client

   a) Logon to the client machine that will be used to connect to the ASM instance
      NOTE: the client machine can also be the ASM/Database server (ie for DBCONTROL)

   b) Locate your tnsnames.ora (typically in the $ORACLE_HOME/network/admin)

c) Add an entry (tnsalias) for your ASM instance (see example)

EXAMPLE

ASM =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = kbcook-1)(PORT = 1521))
      (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SID_NAME = +ASM)
         (UR=A)
      )
   )

3) Setup your remote login password for your ASM instance on the ASM server

   a) Logon to the ASM/Database server

   b) Locate the parameter file for your ASM instance (typically $ORACLE_HOME/dbs/init+ASM.ora [unix] or $ORACLE_HOME/dbs/init+ASM.ora [windows] )

   c) Edit the parameter file and add

         remote_login_passwordfile = exclusive ... for stand alone ASM setups
         remote_login_passwordfile = shared ... for ASM setups that also use Real Application Cluster (RAC)

   d) Save the file

NOTE: It may be required that an PFILE be created from an SPFILE in order to be able to edit the file properly ... once the line have been added ... the process can be reversed

   For more details Note 249664.1 Pfile vs SPfile ... may be used

4) Set your SYS password using ORAPWD for the ASM instance

   a) Logon to the ASM/Database server

   b) Locate your orapw file for your ASM instance (typically $ORACLE_HOME/dbs/orapw+ASM)

   c) Rename the file to orapw.old

   d) Run orapwd to reset the password (see example below)

EXAMPLE

mv "orapw+ASM" "orapw+ASM.old"
orapwd file=orapw+ASM password=kbcook

5) Use the properly formatted connect string for your tool

Connnect to the ASM instance using the password (#4 above) and the tnsalias (#2 above)

EXAMPLES

C:\oracle\app\product\11.1.0\db_1\BIN\SQLPLUS.EXE "sys/kbcook@asm as sysdba"

sqlplus "sys/kbcook@asm as sysdba"

sqlplus "sys@asm as sysdba" ... then supply the password when prompted

References

NOTE:249664.1 - Pfile vs SPfile

显示相关信息 相关的

产品

Oracle Database Products > Oracle Database > Net Services > Oracle Net Services
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Enterprise Management > Enterprise Manager Consoles, Packs, and Plugins > Managing Databases using Enterprise Manager > Enterprise Manager for RDBMS
Enterprise Management > Enterprise Manager Consoles, Packs, and Plugins > Enterprise Manager Grid Control > Enterprise Manager Grid Control

关键字

LISTENER.ORA; TNSNAMES.ORA; LSNRCTL; ORAPWD; ASM; REMOTE_LOGIN_PASSWORDFILE; CLIENT MACHINE

错误

ORA-12541; ORA-15000; ORA-1031; ORA-12528; ORA-12505

返回页首

How to connect to ASM instance from a remote client (Oracle Net) [ID 340277.1]

Applies to:

Goal

Solution

References