关于UR=A的测试
关于UR=A的测试
2019-02-14 19:56 by AlfredZhao, … 阅读, … 评论, 收藏, 编辑
当数据库在nomount，mount或者restricted这类特殊状态下，同时动态监听显示状态为BLOCKED，客户端无法直接连接到实例，此时可通过配置UR=A进行连接。最常见的场景就是10g版本的RAC,配置OGG时需要访问ASM实例的情况（实测11.2版本的RAC ASM实例动态监听的显示状态为Ready，无需添加UR=A配置即可连接）。下面是测试过程：
1.测试10.2.0.5连接ASM
环境：Oracle 10.2.0.5 RAC + ASM
tnsnames.ora配置如下：
ASM =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.171)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = +ASM)(INSTANCE_NAME = +ASM1))
监听状态如下：
[oracle@rac1-server admin]$ lsnrctl statusLSNRCTL for Linux: Version 10.2.0.5.0 - Production on 14-FEB-2019 19:37:22Copyright (c) 1991, 2010, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias LISTENER_RAC1-SERVERVersion TNSLSNR for Linux: Version 10.2.0.5.0 - ProductionStart Date 12-FEB-2019 12:59:06Uptime 2 days 6 hr. 38 min. 15 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /s01/oracle/product/10.2.0/db_1/network/admin/listener.oraListener Log File /s01/oracle/product/10.2.0/db_1/network/log/listener_rac1-server.logListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.172)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.171)(PORT=1521)))Services Summary...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...Service "PLSExtProc" has 1 instance(s).Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "SYS$SYS.KUPC$S_1_20170912101328.JY.ORACLE.COM" has 1 instance(s).Instance "jy2", status READY, has 1 handler(s) for this service...Service "jy.oracle.com" has 1 instance(s).Instance "jy2", status READY, has 1 handler(s) for this service...Service "jyXDB.oracle.com" has 1 instance(s).Instance "jy2", status READY, has 1 handler(s) for this service...Service "jy_XPT.oracle.com" has 1 instance(s).Instance "jy2", status READY, has 1 handler(s) for this service...Service "orcl" has 2 instance(s).Instance "orcl1", status READY, has 2 handler(s) for this service...Instance "orcl2", status READY, has 1 handler(s) for this service...Service "orclXDB" has 2 instance(s).Instance "orcl1", status READY, has 1 handler(s) for this service...Instance "orcl2", status READY, has 1 handler(s) for this service...Service "orcl_XPT" has 2 instance(s).Instance "orcl1", status READY, has 2 handler(s) for this service...Instance "orcl2", status READY, has 1 handler(s) for this service...The command completed successfully[oracle@rac1-server admin]$
此时如果通过网络连接ASM实例，会报错ORA-12528：
[oracle@rac1-server admin]$ sqlplus sys/oracle@asm as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 14 19:25:52 2019Copyright (c) 1982, 2010, Oracle. All Rights Reserved.ERROR:ORA-12528: TNS:listener: all appropriate instances are blocking new connectionsEnter user-name:
修改tnsnames.ora，增加UR=A配置：
ASM =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.171)(PORT = 1521))(CONNECT_DATA =(UR=A)(SERVER = DEDICATED)(SERVICE_NAME = +ASM)(INSTANCE_NAME = +ASM1))
再次尝试通过网络连接ASM实例，可成功连接：
[oracle@rac1-server admin]$ sqlplus sys/oracle@asm as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 14 19:26:26 2019Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsSQL>
2.测试11.2.0.4连接ASM
环境：Oracle 11.2.0.4 RAC + ASM
tnsnames.ora配置如下：
ASM =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.107)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = +ASM)(INSTANCE_NAME = +ASM1)))
监听状态如下：
[grid@db01 admin]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-FEB-2019 19:41:59Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 13-FEB-2019 11:28:44Uptime 1 days 8 hr. 13 min. 14 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /opt/app/11.2.0/grid/network/admin/listener.oraListener Log File /opt/app/grid/diag/tnslsnr/db01/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.107)(PORT=1522)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.117)(PORT=1522)))Services Summary...Service "+ASM" has 1 instance(s).Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "A" has 1 instance(s).Instance "orcl1", status READY, has 1 handler(s) for this service...Service "B" has 1 instance(s).Instance "orcl1", status READY, has 1 handler(s) for this service...Service "orcl" has 1 instance(s).Instance "orcl1", status READY, has 1 handler(s) for this service...Service "orclXDB" has 1 instance(s).Instance "orcl1", status READY, has 1 handler(s) for this service...The command completed successfully[grid@db01 admin]$
在没有配置UR=A的情况下，就可以正常连接到ASM实例：
--没有指定as sysasm会报错ORA-15000：[oracle@db01 admin]$ sqlplus sys/oracle@asmSQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 14 19:23:57 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.ERROR:ORA-15000: command disallowed by current instance typeEnter user-name: ^C--指定后就可以正常连接：[oracle@db01 admin]$ sqlplus sys/oracle@asm as sysasmSQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 14 19:24:00 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Real Application Clusters and Automatic Storage Management optionsSQL>
由此验证了11.2.0.4环境下，通过网络连接ASM实例，不再需要UR=A的配置。
版权声明：本文为jyzhao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。