一、动态监听
1.监听文件
[oracle@node1 admin]$ more /u01/oracle/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
2.监听状态
[oracle@node1 admin]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 18-JAN-2012 13:38:42 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 18-JAN-2012 13:32:49 Uptime 0 days 0 hr. 5 min. 53 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/oracle/network/admin/listener.ora Listener Log File /u01/oracle/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status BLOCKED, has 1 handler(s) for this service... Service "+ASM_XPT" has 1 instance(s). Instance "+ASM", status BLOCKED, has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "chf" has 1 instance(s). Instance "chf", status READY, has 1 handler(s) for this service... Service "chfXDB" has 1 instance(s). Instance "chf", status READY, has 1 handler(s) for this service... Service "chf_XPT" has 1 instance(s). Instance "chf", status READY, has 1 handler(s) for this service... The command completed successfully
3.客户端tns文件[有部分测试加上了(UR=A)]
vm_asm =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
(INSTANCE_NAME = +ASM)
(UR=A) #分存在和不存在测试
)
)
vm_chf =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = chf)
(INSTANCE_NAME = chf)
)
)
4.无测试(UR=A)
C:\Users\XIFENFEI>tnsping vm_asm TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 - 2012 22:15:10 Copyright (c) 1997, 2010, Oracle. All rights reserved. 已使用的参数文件: e:\oracle\11_2_0\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM))) OK (10 毫秒) C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:15:14 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务 请输入用户名: ERROR: ORA-12560: TNS: 协议适配器错误 请输入用户名: ERROR: ORA-12560: TNS: 协议适配器错误 SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus --通过下面的数据库实例测试，证明动态监听是正常工作的，可以访问数据库 C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_chf as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 21:55:03 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- chf
5.含(UR=A)测试
C:\Users\XIFENFEI>tnsping vm_asm TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 - 2012 22:16:49 Copyright (c) 1997, 2010, Oracle. All rights reserved. 已使用的参数文件: e:\oracle\11_2_0\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM) (UR=A))) OK (20 毫秒) C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:16:52 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME -------------------------------- +ASM
二、静态监听
1.监听文件
[oracle@node1 admin]$ more listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = chf)
(ORACLE_HOME = /u01/oracle)
(SID_NAME = chf)
)
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME = /u01/oracle)
(SID_NAME = +ASM)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
)
)
2.监听状态
[oracle@node1 admin]$ lsnrctl status LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 18-JAN-2012 13:53:52 Copyright (c) 1991, 2007, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.30)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production Start Date 18-JAN-2012 13:51:48 Uptime 0 days 0 hr. 2 min. 4 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/oracle/network/admin/listener.ora Listener Log File /u01/oracle/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.30)(PORT=1521))) Services Summary... Service "+ASM" has 2 instance(s). Instance "+ASM", status UNKNOWN, has 1 handler(s) for this service... Instance "+ASM", status BLOCKED, has 1 handler(s) for this service... Service "+ASM_XPT" has 1 instance(s). Instance "+ASM", status BLOCKED, has 1 handler(s) for this service... Service "chf" has 2 instance(s). Instance "chf", status UNKNOWN, has 1 handler(s) for this service... Instance "chf", status READY, has 1 handler(s) for this service... Service "chfXDB" has 1 instance(s). Instance "chf", status READY, has 1 handler(s) for this service... Service "chf_XPT" has 1 instance(s). Instance "chf", status READY, has 1 handler(s) for this service... The command completed successfully
3.无(UR=A)测试
C:\Users\XIFENFEI>tnsping vm_asm TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 - 2012 22:11:34 Copyright (c) 1997, 2010, Oracle. All rights reserved. 已使用的参数文件: e:\oracle\11_2_0\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM))) OK (20 毫秒) C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:11:06 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME -------------------------------- +ASM
4.含(UR=A)测试
C:\Users\XIFENFEI>tnsping vm_asm TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 - 2012 22:12:49 Copyright (c) 1997, 2010, Oracle. All rights reserved. 已使用的参数文件: e:\oracle\11_2_0\network\admin\sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM) (UR=A))) OK (10 毫秒) C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:12:53 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME -------------------------------- +ASM
5.easy connect访问asm实例
C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@192.168.1.30/+asm as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:27:42 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME -------------------------------- +ASM
三、总结
1.在动态监听中，只有设置了(UR=A)才能够远程访问ASM实例；
2.在静态监听中，无论是否设置(UR=A)均可远程访问ASM实例，甚至可以使用easy connect方法方法ASM实例。
How to connect to ASM instance from a remote client (Oracle Net) [ID 340277.1]
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 Oracle Database - Version: 10.2.0.3 and later [Release: 10.2 and later] Enterprise Manager Base Platform - 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<sid> file for your ASM instance (typically $ORACLE_HOME/dbs/orapw+ASM) c) Rename the file to orapw<sid>.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" <OR> sqlplus "sys/kbcook@asm as sysdba" <OR> sqlplus "sys@asm as sysdba" ... then supply the password when prompted