远程访问ASM

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:远程访问ASM

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、动态监听
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实例。

此条目发表在 Oracle ASM 分类目录。将固定链接加入收藏夹。

远程访问ASM》有 1 条评论

  1. 惜分飞 说:

    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