ORACLE 19c DGBROKER配置加备库报ORA-16664,ORA-1017错误

郑全

在我们已经配置好DG的情况下，去添加到BGROKER中，结果一直无法启用配置，

如下：

1.当前配置
DGMGRL> show configuration;

Configuration - ocm

  Protection Mode: MaxPerformance
  Members:
  proddg01 - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 0 seconds ago)


2.添加备库PRODDG02

DGMGRL> add database proddg02 as connect identifier is proddg02;
Database "proddg02" added



3.查看状态

DGMGRL> show configuration;

Configuration - ocm

  Protection Mode: MaxPerformance
  Members:
  proddg01 - Primary database
    proddg02 - Physical standby database (disabled)
      ORA-16905: The member was not enabled yet.

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 26 seconds ago)


发现有报错：ORA-16905: The member was not enabled yet.

4.启用配置
DGMGRL> enable configuration;
Enabled.

5.再去查看配置
DGMGRL> show configuration;

Configuration - ocm

  Protection Mode: MaxPerformance
  Members:
  proddg01 - Primary database
    proddg02 - Physical standby database
      Error: ORA-16664: unable to receive the result from a member

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 102 seconds ago)

配置的状态反而报错，
    Error: ORA-16664: unable to receive the result from a member

6.检查PRODDG02的状态
   DGMGRL> show database proddg02 statusreport

Error: ORA-16664: unable to receive the result from a member

7.检查主库日志
   [oracle@host01 trace]$ tail -10 alert_PRODDG.log
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
2023-11-18T16:49:18.630849+08:00
TT04 (PID:30951): SRL selected for T-1.S-32 for LAD:2
2023-11-18T16:49:21.473988+08:00
Thread 1 advanced to log sequence 33 (LGWR switch)
  Current log# 5 seq# 33 mem# 0: /u01/app/oracle/oradata/PRODDG/std05.log
2023-11-18T16:49:21.562645+08:00
ARC2 (PID:30390): Archived Log entry 159 added for T-1.S-32 ID 0x3e283b54 LAD:1
2023-11-18T16:49:21.796144+08:00
TT03 (PID:30949): SRL selected for T-1.S-33 for LAD:2

没有看到什么错误

8.检查主库BROKER的日志
  Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               ocm                               Warning  ORA-16607: one or more members have failed
  Primary Database            proddg01                          Success  ORA-0: normal, successful completion
  Physical Standby Database   proddg02                            Error  ORA-16664: unable to receive the result from a member

   看来是无法接收到PRODDG02的信息

9.检查备库报警日志的信息
   [oracle@host02 trace]$ tail -10 alert_PRODDG.log
ORA-01017: invalid username/password; logon denied
2023-11-18T16:55:32.910759+08:00
Errors in file /u01/app/oracle/diag/rdbms/proddg02/PRODDG/trace/PRODDG_nsv0_6388.trc:
ORA-01017: invalid username/password; logon denied
2023-11-18T16:56:32.967804+08:00
Errors in file /u01/app/oracle/diag/rdbms/proddg02/PRODDG/trace/PRODDG_nsv0_6388.trc:
ORA-01017: invalid username/password; logon denied
2023-11-18T16:57:21.794341+08:00
Errors in file /u01/app/oracle/diag/rdbms/proddg02/PRODDG/trace/PRODDG_nsv0_6388.trc:
ORA-01017: invalid username/password; logon denied

[oracle@host02 trace]$ tail -10 PRODDG_nsv0_6388.trc
krsu_pre11_pwd_conn: OCI_DBVER attribute value retrieval failed error=1017

*** 2023-11-18T16:57:23.251401+08:00 (CDB$ROOT(1))
krsu_wallet_pwd_connect: OCISessionBegin failed. Error -1
krsu_dump_oci_emsg: Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
NSV0: Connect descriptor used is: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRODDG01.example.com)(INSTANCE_NAME=PRODDG)))
NSV0: Failed to connect to remote database proddg01. Error is ORA-1017
NSV0: Connect attempt to database proddg01 failed, status = ORA-1017.
Failed to send message to site proddg01. Error code is ORA-1017.


看来是PRODDG02访问PRODDG01，报口令错误 ORA-1017

10.检查备库DGBROKER的日志

[oracle@host02 trace]$ tail -20 drcPRODDG.log
。。。
2023-11-18T17:00:34.428+08:00
Connection to database proddg01 returns ORA-1017.
Please check if database proddg01 is using a remote password file,
its remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and the SYS or SYSDG password is the same as this database.
Failed to connect to remote database proddg01. Error is ORA-1017
Failed to send message to member proddg01. Error code is ORA-1017.


11.根据报错，检查对应参数
    SYS@PRODDG> conn sys/oracle_4U@proddg01 as sysdba
Connected.
SYS@proddg01> show parameter remote_login_passwordfile

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile             string         EXCLUSIVE
SYS@proddg01>
SYS@proddg01> conn sys/oracle_4U@proddg02 as sysdba
Connected.
SYS@proddg02> show parameter remote_login_passwordfile

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile             string         EXCLUSIVE
SYS@proddg02>

看结果，参数都没有问题，密码也没有问题。

这个问题比较奇怪。日志也能过去，就是不能配DGBROKER



12.检查备库上的DGBROKER配置情况

DGMGRL> connect sys/oracle_4U@proddg02
Connected to "proddg02"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - ocm

  Protection Mode: MaxPerformance
  Members:
  proddg01 - Primary database
    Error: ORA-1017: invalid username/password; logon denied

    proddg02 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 0 seconds ago)

DGMGRL>



看起来，又确实是用户密码错误。

13.分析日志传输账号
   默认使用 SYS，SYSDG，也可以自定义，通过 redo_transport_user 去指定用户。

   检查两边该参数设置情况：
   SYS@PRODDG>conn sys/oracle_4U@proddg01 as sysdba
Connected.
SYS@proddg01>show parameter redo_

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                     string
SYS@proddg01>conn sys/oracle_4U@proddg02 as sysdba
Connected.
SYS@proddg02>show parameter redo_

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                     string         c##user1
SYS@proddg02>

看来备库这个参数设置了，而主库没有了，这个是前面解决SQLPLUS 连接问题，做的一个尝试，没有去掉

SYS@proddg02>alter system set redo_transport_user='';

System altered.


14.再去验证，已经OK
   DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - ocm

  Protection Mode: MaxPerformance
  Members:
  proddg01 - Primary database
    proddg02 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 55 seconds ago)

--看到不再是ORA-16664 错误了

--再等一会，再看，已经正常，各报错日志，也已经正常。

DGMGRL> show configuration;

Configuration - ocm

  Protection Mode: MaxPerformance
  Members:
  proddg01 - Primary database
    proddg02 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 37 seconds ago)

总结一下：
    这个错误，主要是前面解决SQLPLUS登录PRODDG02报 ORA-1017错误时，修改了   redo_transport_user 参数，后面解决了ORA-1017问题后，主库取消了 redo_transport_user  参数，但备库没有取消该参数，导致该问题。解决办法，就是把备库的该参数也取消设置即可。

    该问题，也可以参见官方 note id:

2769024.1