oracle (ur=a),关于UR=A的测试 - osc_o8pkds53的个人空间 - OSCHINA - 中文开源技术交流社区...

最新推荐文章于 2022-09-27 16:40:04 发布
最新推荐文章于 2022-09-27 16:40:04 发布
阅读量293 收藏
点赞数
文章标签： oracle (ur=a)

当数据库在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 status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 14-FEB-2019 19:37:22

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER_RAC1-SERVER

Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date 12-FEB-2019 12:59:06

Uptime 2 days 6 hr. 38 min. 15 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /s01/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /s01/oracle/product/10.2.0/db_1/network/log/listener_rac1-server.log

Listening 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 sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 14 19:25:52 2019

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Enter 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 sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 14 19:26:26 2019

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL>

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 status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-FEB-2019 19:41:59

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 13-FEB-2019 11:28:44

Uptime 1 days 8 hr. 13 min. 14 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/11.2.0/grid/network/admin/listener.ora

Listener Log File /opt/app/grid/diag/tnslsnr/db01/listener/alert/log.xml

Listening 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@asm

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 14 19:23:57 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-15000: command disallowed by current instance type

Enter user-name: ^C

--指定后就可以正常连接：

[oracle@db01 admin]$ sqlplus sys/oracle@asm as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 14 19:24:00 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL>

由此验证了11.2.0.4环境下，通过网络连接ASM实例，不再需要UR=A的配置。

weixin_39759182
关注
oracle (ur=a),tnsname中UR=A配置使用 | YallonKing
weixin_35715190的博客
04-11 767
当数据库nomount,mount或者restricted时，动态监听显示状态为BLOCKED时，客户端可通过配置UR=A进行连接。如下：SQL> startup nomount;ORACLE instance started.Total System Global Area 939495424 bytesFixed Size 2233960 bytesV...
时域特征偏度_时域特征提取 - osc_d8t0zzig的个人空间 - OSCHINA - 中文开源技术交流社区...
weixin_34268617的博客
01-17 772
频域特征提取：https://blog.csdn.net/qq_37240982/article/details/107425375时频域特征提取：https://blog.csdn.net/qq_37240982/article/details/107425452时域、频域、时频域特征提取matlab程序的zip：https://www.90pan.com/b1994356密码：6rq1 时域特...
oracle (ur=a),监听_(UR=A)
weixin_29969209的博客
04-11 326
[oracle@wfdb ~]$ rman targetsys/lf0320tcsge@WF auxiliary sys/lf0320tcsge@WF_166Recovery Manager: Release 11.2.0.4.0 -Production on Sun Oct 9 14:57:47 2016Copyright (c) 1982, 2011, Oracle and/or itsaff...
tnsnames.ora文件中UR=A作用
hellokidss的博客
05-16 3102
最近在做数据库迁移并升级，采用了duplicate方式。以往都是在目标端做目标库的静态监听。但该方法需要在目标端reload监听，当目标端所在主机有其他实例在跑时，reload监听会影响其他库提供服务。通过请教同事及查询资料，发现了UR=A这个参数。总结如下：1、源端配置UR=A时，可远程连接非open的目标端实例，并进行管理操作；2、目标端在没有注册静态监听时，若监听为非默认监听（名称不是lis...
tnsnames.ora中UR=A配置使用
cqszpx的博客
07-23 476
在 Oracle Database 10g 中开始使用新的UR=A参数 --------------------------------------- 如果正在使用实例注册，则listener.ora文件中默认为没有任何数据库条目，可以在侦听程序中手工配置数据库条目， 或者 可以使用新的Oracle Database 10g参数UR=A更新tnsnames.ora文件。该参数允许只进行管理性的连接。 1.在动态监听中，只有设置了(UR=A)才能够远程访问ASM实例； 2.在静态监听中，无论是否设置(U
Oracle中e的函数,oracle中的函数 - osc_6ebpcui3的个人空间 - OSCHINA - 中文开源技术交流社区...
最新发布
06-08
您的问题是关于 Oracle 数据库中的 e 函数。e 函数是 Oracle 中的一个数学函数，它返回自然对数的底数 e 的值，e 的值约为 2.71828。在 Oracle 中，您可以使用 e 函数在 SQL 语句中计算自然对数。 例如，如果您想...
python中circle(40、steps=3)_Python语法 - osc_l1gtz377的个人空间 - OSCHINA - 中文开源技术交流社区...
weixin_36331854的博客
03-01 745
import time;import turtle;# a = 23423432;b = 2342343452.23452;c = 234234323452.23452;d = 234293432.23452;# print(format(a,"10X"));# print(format(b,"<10.2f"));# print(format(c,"<10.2f"));# print(...
TF-IDF的Matlab程序,TF-IDF算法简析 - osc_sjg81se7的个人空间 - OSCHINA - 中文开源技术交流社区...
weixin_34401781的博客
03-26 400
TF-IDF算法可用来提取文档的关键词，关键词在文本聚类、文本分类、文献检索、自动文摘等方面有着重要应用。算法原理TF：Term Frequency，词频IDF：Inverse Document Frequency，逆文档频率词频(TF)：某一个词在该文件中出现的频率计算方法为： 逆文档频率(IDF)：总文件数目除以包含该词的文件数目计算方法为： 分母加1是为了防止该词不在语料库中而导致被除数为零...
mysql查询95031班人数_MySQL的查询练习 - osc_1ngzqx2h的个人空间 - OSCHINA - 中文开源技术交流社区...
05-31
你需要查询95031班的人数，可以使用如下的SQL语句： ``` SELECT COUNT(*) FROM student WHERE class='95031'; ``` 其中，`student`是你的学生表名，`class`是存储班级信息的列名。这条SQL语句将返回95031班的人数...
监听_(UR=A)
cjh81378066的博客
10-09 233
[oracle@wfdb ~]$ rman target sys/lf0320tcsge@WF auxiliary sys/lf0320tcsge@WF_166 Recovery Mana...
网络连接串的UR=A 起什么作用
cqsizhuang的博客
07-06 350
大家一定配置过DG，或者数据库复制，要复制的目标，一般我们都是使用的静态监听，不用静态监听，可以吗 ？ 或者，了解一下 UR=A ，可以有 另外的收获 。
Draft
01-11 65
select name,sequence#,first_change#,next_change# from v$archived_log select checkpoint_change# from v$database; select checkpoint_change# from v$datafile; select checkpoint_change# from v$datafile_...
asm磁盘加载小记
jc_benben的专栏
09-25 1997
今天在弄数据库的时候asm磁盘组没起来， 启动asm时候提示 SQL> startup ORA-00099: warning: no parameter filespecified for ASM instance ORA-01081: cannot start already-runningORACLE - shut it down first SQL> shutdown immedi
ORA-15000: command disallowed by current instance type
喝醉酒的小白
05-20 2109
asm不能启动到Mount 。。。
ASM 增加磁盘操作
rgb_rgb的博客
06-25 1151
sgcrm5*sgcrm5-/home/oracle >export ORACLE_SID=sgcrm5 sgcrm5*sgcrm5-/home/oracle >sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jun 25 23:48:59 2012 Copyright (c) 1982, 2
Recover from diskgroup failure using the 12.1.0.2 “mount restricted force for recovery” feature - An
sqlora的专栏
09-27 738
Recover from diskgroup failure using the 12.1.0.2 “mount restricted force for recovery” feature - An Example (Doc ID 1968642.1)
Oracle之ADG与DG的区别？
热门推荐
qq_29718979的博客
07-20 3万+
在上云后的Oracle数据灾备场景中，我们经常听到DBA迁移工程师讲到“在这个项目中用ADG进行数据实时备份，ADG比DG更好！”。究竟ADG作Oracle数据灾备的优势在什么地方？ 一、ADG主要解决了DG时代读写不能并行的问题 DG时代的数据同步方式如采用Redo Log的物理方式，则数据库同步数据快、耗用资源低，但存在一个大问题。 ...
asmcmd: command disallowed by current instance type 解决方法
tianshibaijia
09-29 174
运行asmcmd命令报错： [oracle@racdb2 ~]$ asmcmd asmcmd: command disallowed by current instance type 错误提示很明显： 实例类型不对。 RAC有2个实例，Oracle实例和ASM实例。 在pfile参数里面设置的是ORACLE实例，这个是默认值。所以如果不指定ORACLE_SID的情况下，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值