Oracle DBA Tips Corner
Return to the Oracle DBA Tips Corner.
|
Oracle DBA Tips Corner
|
Connecting to ASM through the TNS Listener from a Client Desktop
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
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):
Modify the listener.ora for the ASM Instances
Add ASM Entries to the tnsnames.ora File on the Client Machine
Bounce the TNS Listener
Test Access to ASM from the Client Machine
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
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.
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
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)
)
)
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)
)
)
)
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...
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
Tuesday, 29-Jan-2008 21:42:49 EST
Page Count: 7908