关于Oracle-dataguard中physical standby无法接收primary database发过来的归档

实验环境：Oracle11.2.04，Linux Centos6.8    

架构环境：Oracle-DG双节点    主库instance_name：mysql1   备库instance_name：mysql2

主库db_unique_name：mysql1       备库db_unique_name：mysql2         

主备日志传输模式：是maximize availability（最高可用）实时同步

故障描述：搭建完OracleDG后备库无法接收到主库传来的归档日志，并且提示检测不到备库的心跳，主备之间的service_name及tnsnames.ora配置都正常，并且主备之间tnsping通讯正常，但是就是接收不到归档日志，并且有个奇怪的现象，只要备库重启归档就会被应用。

最后结论是因为备库的db_unique_name不正确造成的，将备库db_unique_name修改后正常。 

SQL>show parameter db_unique_name   
SQL>alter system set db_unique_name='mysql2' scope=spfile;

 

故障现象如下：

主库上alert日志显示错误如下，提示检测不到备库的心跳，但是tnsping mysql2 服务名可以ping通，说明网络配置无误，初步猜想还是数据库配置有问题。主库日志如下：

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dgstandby)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=MYSQL2)(INSTANCE_NAME=MYSQL2)(GLOBAL_NAME=MYSQL2)(CID=(PROGRAM=oracle)(HOST=dgprimary)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 02-SEP-2018 05:43:22
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
    
TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
Error 12514 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'mysql2'. Error is 12514.

主备之间互ping正常：

[oracle@dgstandby ~]$ tnsping mysql1

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 02-SEP-2018 05:52:00

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dgprimary)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MYSQL1)))
OK (10 msec)

[oracle@dgprimary ~]$ tnsping mysql2

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 02-SEP-2018 05:53:25

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dgstandby)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MYSQL2) (INSTANCE_NAME = MYSQL2) (GLOBAL_NAME = MYSQL2)))
OK (10 msec)

从以上主备之间互相tnsping的结果可以看出网络配置是正确的，问题应该出在主备数据库的配置问题上，实验如下：

从主库和备库相互远程连接实验
结果如下：
备连主
[oracle@dgstandby ~]$ sqlplus sys/oracle@mysql1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 2 05:55:24 2018

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 Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

主连备并且sys密码是正确的，但是现实连不上
[oracle@dgprimary ~]$ sqlplus sys/oracle@mysql2 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 2 05:56:22 2018

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: sys
Enter password: 
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

所以只有一个可能就是备库的Instance_name或DB_NAME、db_unique_name、ORACLE_SID配置不正确
检查主备的这几项参数

select instance_name,status,instance_role from v$instance;       ----查看instance_name要与ORACLE_SID因为instance_name是数据库的参数而ORACLE_SID系统配置环境变量的参数如果这两个参数不一致会造成TNS的错误
select name,open_mode,protection_mode,database_role from v$database;    -----查看数据库名字
show parameter db_unique_name      ---查看数据库去全局唯一名字

经查看发现备库全局名字不正确，应该是mysql2，所以赶紧修改db_unique_name参数重启数据库生效

alter system set db_unique_name='mysql2' scope=spfile;

然后备库上执行

startup nomount;
alter database mount standby databse;
alter database recover managed standby database using current logfile disconnect from session;     ---开启standby日志运用