[转帖]主库到standby报错解决：Error 12154 received logging on to the standby ORA-12154_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 3980 | 回复: 2

主题： [转帖]主库到standby报错解决：Error 12154 received logging on to the standby ORA-12154

FBIfanshao

注册用户

等级：新兵
经验：77
发帖：3
精华：0
注册：2016-10-1
状态：离线
发送短消息息给FBIfanshao

加好友发送短消息息给FBIfanshao

发消息

发表于：

2019-9-19 15:34:50 | [全部帖] [楼主帖]

楼主

背景：
笔者建库无数，但今天对Oracle 11.2.0.3库使用duplicate新建standby后，主库redo无法通过RFS传输至standby

主库alert.log报错：

Thu Jun 14 14:05:20 2018
Error 12154 received logging on to the standby

主库报错生成trc文件：

*** 2018-06-14 14:25:20.581
Redo shipping client performing standby login
OCIServerAttach failed -1
.. Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified
'
OCIServerAttach failed -1
.. Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified
'
OCIServerAttach failed -1
.. Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified
'
*** 2018-06-14 14:25:20.585 4320 krsh.c
Error 12154 received logging on to the standby
*** 2018-06-14 14:25:20.585 915 krsu.c
Error 12154 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'agile9dg'
Error 12154 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'agile9dg'
*** 2018-06-14 14:25:20.585 4320 krsh.c
PING[ARC2]: Heartbeat failed to connect to standby 'agile9dg'. Error is 12154.
*** 2018-06-14 14:25:20.585 2932 krsi.c
krsi_dst_fail: dest:2 err:12154 force:0 blast:1

主库 v$archive_dest
error依旧是 ORA-12154

分析：

ORA-12154此类问题通常为主库通过log_archive_dest_2使用tnsnames.ora中standby TNS文件无法访问到备库导致，

但检查：tnsnames.ora，密码文件 ,各参数，防火墙，selinux等设置均正常
问题确实奇怪，查找官方真有相关问题说明： Oracle Database - Enterprise Edition - Version 9.0.1.0 to 12.2.0.1 [Release 9.0.1 to 12.2]

Information in this document applies to any platform.

SYMPTOMS 症状和我遇到完全一样

When adding or changing the parameter log_archive_dest_ to point to a newly created standby database, the archiver process for the new destination reports the following error in the alert log
Corresponding archiver trace file may show:
Query on V$ARCHIVE_DEST shows the following:
Please note that this behavior seems to have changed in 11.2, the tnsnames.ora is now being read by the ARC processes when a new remote destination is added. It is unclear when exactly this was changed.
Added a new standby database and updated the tnsnames.ora with a new TNS alias for the new standby.

tnsnames.ora，但instance和arch进程都没有重启

The ARC processes read the tnsnames.ora only once during process initialization, any updates to the tnsnames.ora after startup will not be known to the ARC process and hence the error

tnsnames.ora文件，arc进程启动后tnsn1. Shutdown and restart the primary database instance. 方法1. 关闭重启instance

This will cause a (short) outage of the primary database and may not be feasible for this reason.

2. Use a connect descriptor for the 'service' parameter. 方法2. chive_dest_2 使用具体指明的serivice参数，而不使用tns别名方式

Instead of using a TNS alias for the service parameter (which requires a lookup of the tnsnames.ora file) one can use the connect descriptor itself.
Assume the following (new) entry in the tnsnames.ora on the primary node:
REMOTE_DEST_NEW = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDBY) ) )
alter system set log_archive_dest_2 = 'service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standbynode)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STDBY)))"' ;

ps -ef|egrep "ora_arc.*_${ORACLE_SID}"|grep -v grep |while read user pid junk
do
echo "kill -9 $pid"
done

解决：
研究了一下采用方法2“LOG_ARCHIVE_DEST_2使用具体指明的service参数 ” 不重启instance和arc进程的方法最保险，方法2修改：
原参数：log_archive_dest_2='SERVICE=agile9dg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=agile9dg'
新参数：alter system set log_archive_dest_2 ='service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.17.47)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=agile9)))" LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=agile9dg'
修改后传输正常

alert.log：
Thu Jun 14 14:25:44 2018
ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.17.47)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=agile9)))" LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=agile9dg' SCOPE=BOTH;
Thu Jun 14 14:26:07 2018
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected for thread 1 sequence 1490 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 1490 (LGWR switch)
Current log# 2 seq# 1490 mem# 0: /data/oradata/agile9/log2agile9.ora

trc文件：

*** 2018-06-14 14:26:10.214
Redo shipping client performing standby login
*** 2018-06-14 14:26:10.248 4645 krsu.c
Logged on to standby successfully
Client logon and security negotiation successful!
Redo shipping client performing standby login

至此，问题解决。背景：

笔者建库无数，但今天对Oracle 11.2.0.3库使用duplicate新建standby后，主库redo无法通过RFS传输至standby

主库alert.log报错：

Thu Jun 14 14:05:20 2018
Error 12154 received logging on to the standby

主库报错生成trc文件：

主库 v$archive_dest
error依旧是 ORA-12154

分析：

ORA-12154此类问题通常为主库通过log_archive_dest_2使用tnsnames.ora中standby TNS文件无法访问到备库导致，

Information in this document applies to any platform.

SYMPTOMS 症状和我遇到完全一样

tnsnames.ora，但instance和arch进程都没有重启

The ARC processes read the tnsnames.ora only once during process initialization, any updates to the tnsnames.ora after startup will not be known to the ARC process and hence the error

tnsnames.ora文件，arc进程启动后tnsn1. Shutdown and restart the primary database instance. 方法1. 关闭重启instance

This will cause a (short) outage of the primary database and may not be feasible for this reason.

2. Use a connect descriptor for the 'service' parameter. 方法2. chive_dest_2 使用具体指明的serivice参数，而不使用tns别名方式

Instead of using a TNS alias for the service parameter (which requires a lookup of the tnsnames.ora file) one can use the connect descriptor itself.
Assume the following (new) entry in the tnsnames.ora on the primary node: REMOTE_DEST_NEW = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STDBY) ) )
alter system set log_archive_dest_2 = 'service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standbynode)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STDBY)))"' ;

ps -ef|egrep "ora_arc.*_${ORACLE_SID}"|grep -v grep |while read user pid junk
do
echo "kill -9 $pid"
done

trc文件：

至此，问题解决。

本版精华
热门帖子

操作引用/回复

联动大白

注册用户

等级：列兵
经验：91
发帖：0
精华：0
注册：2015-5-27
状态：离线
发送短消息息给联动大白

加好友发送短消息息给联动大白

发消息

发表于：

2019-12-2 0:30:00 | [全部帖] [楼主帖]

2 楼

为了方便大家阅读，我对文章中错误号来解释一下吧！

Error Id: ORA-12154

Title: TNS:could not resolve the connect identifier specified

Description:

TNS:could not resolve the connect identifier specified

Action:

- If you are using local naming (TNSNAMES.ORA file): - Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA) - Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible. - Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file. - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable. - If you are using directory naming: - Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA). - Verify that the LDAP directory server is up and that it is accessible. - Verify that the net service name or database name used as the connect identifier is configured in the directory. - Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier - If you are using easy connect naming: - Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA). - Make sure the host, port and service name specified are correct. - Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.

Cause:

A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

也许你已明白，但对一个人有用也是我存在的理由！^_^ By:持之以恒的大白

-- 来自：

操作引用/回复

Bobo226

注册用户

等级：上尉
经验：548
发帖：0
精华：0
注册：2020-1-7
状态：离线
发送短消息息给Bobo226

加好友发送短消息息给Bobo226

发消息

发表于：

2023-6-26 14:37:50 | [全部帖] [楼主帖]

3 楼

一般在10%至30%，极速飞艇开奖直播剩余的物质超过1000种，比较重要的有300多种，其他重要的成分有酒石酸，果胶，矿物质和单宁酸等。虽然这些物质所占的比例不高，却是酒质优劣的决定性因素。质优味美的，澳洲幸运5开奖直播是因为它们能呈现一种组织结构的平衡，使人在味觉上有无穷的享受。甜起泡酒，就是香甜型的起泡酒

操作引用/回复

总帖数

每页帖数

1/1页

返回列表

用户登录

Weblogic中间件技术论坛

Tuxedo中间件技术论坛

数据库论坛

Java论坛

Linux/unix论坛

网站地图