service_names配置不正确，导致dg创建失败

2018-11-14 16:17 abce 阅读(911) 评论(0) 编辑收藏举报

伙伴发来消息，创建dg后，主备一直无法进行日志同步。

以下是查看过程

备库的alert日志：

2018-11-13T17:47:36.231294+08:00
Errors in file /d12/app/oracle/diag/rdbms/orcls/test/trace/test_m000_20088.trc:
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
2018-11-13T17:47:36.311896+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcls/test/trace/test_m000_20088.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
2018-11-13T17:47:36.394797+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcls/test/trace/test_m000_20088.trc:
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
2018-11-13T17:47:36.480646+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcls/test/trace/test_m000_20088.trc:
ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl/users01.dbf'
2018-11-13T17:47:40.958801+08:00
 Started logmerger process
2018-11-13T17:47:40.979088+08:00
Managed Standby Recovery starting Real Time Apply
2018-11-13T17:47:41.067310+08:00
Parallel Media Recovery started with 4 slaves
2018-11-13T17:47:41.140323+08:00
Media Recovery Waiting for thread 1 sequence 49
Fetching gap sequence in thread 1, gap sequence 49-49
2018-11-13T17:47:41.938608+08:00
Completed: alter database recover managed standby database disconnect from session

可以看到在执行alter database recover managed standby database disconnect from session后，备库就一致hang在那里了。

standby库查看：

SQL>  select client_process, process, thread#, sequence#, status from v$managed_standby where client_process='LGWR' or process='MRP0';
 
CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
N/A      MRP0               1         49 WAIT_FOR_GAP
 
SQL> 

也可以看出，日志没有进行传输。

然后在primary库查看：

SQL>  select error from v$archive_dest;
 
ERROR
-----------------------------------------------------------------
 
ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor

查看显示tns有问题！
就让其测试tnsping的结果。结果显示都是通的。

继续查看主备的tnsnames.ora文件(二者配置相同)：

$ more tnsnames.ora
#primary
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
#standby
ORCLS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

可以看到primary库的service_name是orcl，standby库的service_name也是orcl。

这里就有问题了。因为默认service_names=DB_UNIQUE_NAME.DB_DOMAIN。一般在测试的时候是不设置db_domain的值的，service_names值就设置成了DB_UNIQUE_NAME。但是primary和standby是不可以具有相同的DB_UNIQUE_NAME的，因此这里standby的service_name就有问题了。

因此，需要修改primary和standby端的tnsnames.ora文件。