AI 助理
备案控制台
开发者社区 数据库 文章 正文

【DG】搭建DG时，报错：ORA-01017 invalid username password logon denied

2022-12-15 935
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 【DG】搭建DG时，报错：ORA-01017 invalid username password logon denied

现象

今天网友也碰到了adg中搭建，ORA-01017: invalid username/password; logon denied的报错，小麦苗远程处理了一下，总结的几点原因：

  • listener.ora 监听配置
  • tnsnames.ora 配置问题
  • 参数service_names的配置问题
  • 密码问题必须显式地指定密码

若有类似的问题，可以从这几个方面去排查。

主库为rac，备库为rac，主备端版本为11.2.0.4.0：

[ZFLHRDBDB1:oracle]:/oracle/app/oracle/product/11.2.0/db/dbs> rman target sys/lhr@TNS_DGPRI auxiliary sys/lhr@TNS_DGPHY
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 18 11:30:10 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDG (DBID=2836886746)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied


C:\Users\hdzyat>rman target sys/lhr@22.188.194.31/TESTDG auxiliary sys/lhr@22.188.194.64/TESTDG
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 18 11:30:10 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDG (DBID=2836886746)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied


C:\Users\hdzyat>sqlplus sys/lhr@22.188.194.64/TESTDG as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 18 11:30:37 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

C:\Users\hdzyat>rman target sys/lhr@22.188.194.64/TESTDG
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 18 12:47:27 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理

这是一个“貌似简单，实则不然”的报错排查处理过程。不要被表面现象所蒙蔽。
在使用RMAN的Active Database Duplicate创建物理DataGuard是抛出ORA-17627和ORA-17629错误，RMAN环境下的详细报错信息如下：

 ORA-17627: ORA-01017: invalid username/password; logon denied
 ORA-17629: Cannot connect to the remote database server

千万不要以为是由于输入了错误的用户名和密码导致的，而是因为“没有输入用户名和密码”。且看我的分解。

1.数据库版本信息

SYS@PROD> select * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE   11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2.再现问题现象
1）创建物理备库的RMAN脚本如下
注意此脚本本身不存在任何问题。

duplicate target database
for standby
from active database
DORECOVER
spfile
set db_unique_name='SBDB'
set log_archive_dest_1='location=/u01/app/oracle/arch/SBDB'
set log_archive_dest_2='service=PROD async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=PROD'
set standby_file_management='AUTO'
set fal_server='PROD'
set fal_client='SBDB'
set control_files='/u01/app/oracle/oradata/SBDB/control1.ctl','/u01/app/oracle/oradata/SBDB/control2.ctl'
set db_file_name_convert='PROD','SBDB'
set log_file_name_convert='PROD','SBDB'
set memory_target='0'
set sga_target='400M';

2）确认当前ORACLE_SID内容

PROD@secdb /home/oracle$ echo $ORACLE_SID
PROD

3）连接到AUXILIARY辅助实例创建物理备库

PROD@secdb /home/oracle$ rman target / auxiliary sys/oracle@sbdb

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 25 20:36:53 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=193323522)
connected to auxiliary database: PROD (not mounted)

RMAN>

RMAN> duplicate target database
2> for standby
from active database
3> 4> DORECOVER
5> spfile
6> set db_unique_name='SBDB'
7> set log_archive_dest_1='location=/u01/app/oracle/arch/SBDB'
8> set log_archive_dest_2='service=PROD async lgwr register valid_for=(online_logfile                ,primary_role) db_unique_name=PROD'
set standby_file_management='AUTO'
9> 10> set fal_server='PROD'
11> set fal_client='SBDB'
set control_files='/u01/app/oracle/oradata/SBDB/control1.ctl','/u01/app/oracle/oradat                a/SBDB/control2.ctl'
12> 13> set db_file_name_convert='PROD','SBDB'
14> set log_file_name_convert='PROD','SBDB'
set memory_target='0'
15> 16> set sga_target='400M';

Starting Duplicate Db at 20120425 20:37:26
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=135 device type=DISK

contents of Memory Script.:
{
 backup as copy reuse
 targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPROD' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB'  targetfile
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfilePROD.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileSBDB.ora'  ;
 sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfi                leSBDB.ora''";
}
executing Memory Script

Starting backup at 20120425 20:37:27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 device type=DISK
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/25/2012 20:37:28
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
continuing other job steps, job failed will not be re-run
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/25/2012 20:37:29
RMAN-03015: error occurred in stored script. Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/25/2012 20:37:29
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

RMAN> exit


Recovery Manager complete.

4）RMAN运行过程中报错信息内容

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/25/2012 20:37:28
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
continuing other job steps, job failed will not be re-run

3.问题原因
报错信息的确够直白：“无效的用户名/密码，禁止登陆”。
这种报错，直接定格到登陆的那一瞬间，命令如下：

PROD@secdb /home/oracle$ rman target / auxiliary sys/oracle@sbdb

正常理解的，这里貌似没有问题，前一部分“target /”表明使用操作系统认证的方式登录PROD数据库实例，后一半部分“auxiliary sys/oracle@sbdb”表明使用密码文件方式进行验证。并且，命令行提示信息上显示也已经成功登陆。因此这个报错信息很是诡异。

如果继续对比这两部分的登录方式，问题最有可能出现在前一部分，因为使用的是默认行为方式；另外后半部分是天衣无缝的。最终的原因的确出在这里，在使用Oracle的Active Database Duplicate功能创建物理DataGuard的过程中，连接target数据库必须“显式地指定密码”，即使是使用操作系统认证方式登录。

4.问题处理
原因找到后，处理本身就是水到渠成的事情。
正确的连接方式如下：

$ rman target sys/oracle@prod auxiliary sys/oracle@sbdb

OK，到此问题便完美的处理完毕了，直接执行后续的RMAN命令即可。

5.小结
这个问题处理过程告诉我们DBA一个道理，不要被表面现象所蒙蔽，有些提示信息是直白可信的，但是有些提示信息需要我们冷静的思考多种可能的原因，然后再一一尝试排查，必将有柳暗花明之日。

文章标签：
数据库备份
关系型数据库
数据库管理
数据安全/隐私保护
Oracle
数据库
小麦苗
目录
相关文章
小麦苗
|
Oracle 关系型数据库 数据安全/隐私保护
在Oracle中，ORA-01017 invalid username password; logon denied原因有哪些
在Oracle中，ORA-01017 invalid username password; logon denied原因有哪些
小麦苗
2345 0
姚远OracleACE
srvctl start database 失败 ORA-01017: invalid username/password； logon denied
现象
姚远OracleACE
140 0
@dailidong@
|
关系型数据库 MySQL SQL
[解决] Error Code: 1044. Access denied for user 'root'@'%' to database
今天在测试集群用的mysql上，遇到个权限的问题： SQLException : SQL state: 42000 com.mysql.jdbc.exceptions.
@dailidong@
5431 0
lfreeali
|
SQL 测试技术 数据库
0131 ORA-00942 and AUTHID CURRENT_USER
[20180131]ORA-00942 and AUTHID CURRENT_USER.txt --//偶尔写一个存储过程调用一些系统视图.经常遇到一些ORA-00942,有时候很烦.
lfreeali
1154 0
李博 bluemind
|
关系型数据库 数据库 Oracle
EBS在测试时报 APP-FND-01516: Invalid application username,password,or database.
李博 bluemind
2108 0
技术小阿哥
|
关系型数据库 Oracle
ORA-01045: user ICCS lacks CREATE SESSION privilege; logon denied
技术小阿哥
1991 0
最美的回忆
|
Oracle 关系型数据库 Windows
[Tips]:"RemoteOperationException: ERROR: wrong password for user" with Oracle 10g
最美的回忆
1267 0
科技小先锋
|
数据安全/隐私保护
ora-28002:the password will exprire
科技小先锋
953 0
技术小胖子
|
SQL Oracle 关系型数据库
ORA-01206: file is not part of this database - wrong database id
技术小胖子
3224 0
技术mix呢
|
网络协议 Oracle 关系型数据库
被拒--悲剧之ORA-01017: invalid username/password; logon denied 错误
技术mix呢
2162 0