Connecting to ASM through the TNS Listener from a Client Desktop

Expert Oracle Tips by Jeff Hunter

March 16, 2011

By default, the Oracle Database Configuration Assistant (DBCA) does not configure the TNS listener to accept client requests to an ASM instance running from a different node. Access will be denied for clients like SQL*Plus, Perl DBI:DBD, and JDBC when attempting to connect to an ASM instance from a node other than the node running the ASM instance. When the service is created for an ASM instance, its status is BLOCKED:

[oracle@linux1 ~]$ lsnrctl status LISTENER_LINUX1 | grep ASM
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...

This limitation puts restrictions on scripts and other client tools that want to monitor and manage an ASM instance from a different node. Getting around this restriction however is an easy task that involves manually creating a service name for the ASM instance.

This article presents the steps required to access an ASM instance through the TNS listener from a client desktop. The database used in this article is a two-node Oracle RAC 10g clustered database where in fact there will two ASM instances (one ASM instance for each Oracle instance in the cluster). The database version is Oracle 10g Release 2 (10.2.0.3.0) running on CentOS 4.5 (or RHEL 4.5):

Node 1
Machine Name	linux1.idevelopment.info
Oracle SID	orcl1
ASM SID	+ASM1
ASM Global DB Name (service name)	+ASM

Node 2
Machine Name	linux2.idevelopment.info
Oracle SID	orcl2
ASM SID	+ASM2
ASM Global DB Name (service name)	+ASM

Modify the listener.ora for the ASM Instances

Database preparation

The first step is to modify the listener.ora file for the ORACLE_HOME running ASM on all nodes in the RAC cluster by adding a new service:

Node 1 - (listener.ora)

LISTENER_LINUX1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_LINUX1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME  = +ASM)
      (SID_NAME       = +ASM1)
      (ORACLE_HOME    = /u01/app/oracle/product/10.2.0/db_1)
    )
  )

Node 2 - (listener.ora)

LISTENER_LINUX2 =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521)(IP = FIRST))
    )
)

SID_LIST_LISTENER_LINUX2 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = +ASM)
      (SID_NAME       = +ASM2)
      (ORACLE_HOME    = /u01/app/oracle/product/10.2.0/db_1)
    )
)

Add ASM Entries to the tnsnames.ora File on the Client Machine

The next step is to add ASM entries to the tnsnames.ora file on the client machine that will be connecting to the ASM instance(s). The client machine in this example is named alex.idevelopment.info. A separate tnsnames entry will be created for each ASM instance in the two-node RAC. The two tnsnames entries for this example are named ORCL1_ASM1 and ORCL2_ASM2:

Client Node - (tnsnames.ora)

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.idevelopment.info)
    )
)

ORCL1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.idevelopment.info)
      (INSTANCE_NAME = orcl1)
    )
)

ORCL2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.idevelopment.info)
      (INSTANCE_NAME = orcl2)
    )
)

ORCL1_ASM1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = +ASM)
    )
)

ORCL2_ASM2 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = +ASM)
    )
)

ORCL_TAF =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = linux2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_taf.idevelopment.info)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
)

Bounce the TNS Listener

The final step is to bounce the Oracle TNS Listener. Remember that the listener process will need to be bounced on both of the nodes in the RAC cluster:

# -----------
# FROM linux1
# -----------
[oracle@linux1 ~]$ lsnrctl stop LISTENER_LINUX1
[oracle@linux1 ~]$ lsnrctl start LISTENER_LINUX1

# -----------
# FROM linux2
# -----------
[oracle@linux2 ~]$ lsnrctl stop LISTENER_LINUX2
[oracle@linux2 ~]$ lsnrctl start LISTENER_LINUX2

After restarting the TNS listener, the new service should be available from both nodes in the RAC cluster:

# -----------
# FROM linux1
# -----------
[oracle@linux1 ~]$ lsnrctl status LISTENER_LINUX1 | grep ASM
Service "+ASM" has 2 instance(s).
  Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...

# -----------
# FROM linux2
# -----------
[oracle@linux2 ~]$ lsnrctl status LISTENER_LINUX2 | grep ASM
Service "+ASM" has 2 instance(s).
  Instance "+ASM2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...

Test Access to ASM from the Client Machine

After the new ASM service(s) have been added and the TNS listener successfully restarted, test access to the ASM instances from the client machine using SQL*Plus:

ORA10G on alex: sqlplus "sys/<ASM_SYS_PASSWORD>@orcl1_asm1 as sysdba" @asm_diskgroups.sql

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
FLASH_RECOVERY_AREA      512   4,096    1,048,576 MOUNTED     EXTERN         596,985            339       .06
ORCL_DATA1               512   4,096    1,048,576 MOUNTED     EXTERN         597,017         40,784      6.83
                                                                     --------------- --------------
Grand Total:                                                               1,194,002         41,123



ORA10G on alex: sqlplus "sys/<ASM_SYS_PASSWORD>@orcl2_asm2 as sysdba" @asm_diskgroups.sql

Disk Group            Sector   Block   Allocation
Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
FLASH_RECOVERY_AREA      512   4,096    1,048,576 MOUNTED     EXTERN         596,985            339       .06
ORCL_DATA1               512   4,096    1,048,576 MOUNTED     EXTERN         597,017         40,784      6.83
                                                                     --------------- --------------
Grand Total:                                                               1,194,002         41,123

Get the Complete
Oracle Tuning Details

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,150 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Copyright © 1996 -2011 by Burleson Enterprises. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation.
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks