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

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

伙伴发来消息,创建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文件。

上一篇:JAVA面试题


下一篇:Effective Java 第三版——24. 优先考虑静态成员类