Home >Database >Mysql Tutorial >azure 云上 oracle11.2.0.4里dataguard归档日志传输 1034 问题详

azure 云上 oracle11.2.0.4里dataguard归档日志传输 1034 问题详

WBOY
WBOYOriginal
2016-06-07 14:50:311403browse

1 , dataguard 搭建好后,归档日志传输不过去 去查看master库上面的日志 tail –f /data/oracle/diag/rdbms/test_m1/powerdes/trace/alert_powerdes.log,显示信息如下: Sun May 08 00:34:17 2016 Error 1034 received logging on to the standby PING[ARC

 

1dataguard搭建好后,归档日志传输不过去

去查看master库上面的日志

tail –f /data/oracle/diag/rdbms/test_m1/powerdes/trace/alert_powerdes.log,显示信息如下:

Sun May 08 00:34:17 2016

Error 1034 received logging on to the standby

PING[ARC2]: Heartbeat failed to connect to standby 'test_m2'. Error is1034.

 

 

2tnsping earch_m2是通的

[oracle@azure_test_dbm1_3_111 admin]$ tnsping test_m3

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 08-MAY-2016 09:13:42

 

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 = 192.168.3.112)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test_m2)))

OK (0 msec)

[oracle@azure_test_dbm1_3_111 admin]$

 

3,去standby备库上,Check检查下service_name,看到service_names确实是test_m2

看起来service_name也没用错,如下所示:

[oracle@azure_test_dbm1_3_112 admin]$ rlwrap sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:15:27 2016

 

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> show parameter name;

 

NAME                                        TYPE       VALUE

------------------------------------ ----------- ------------------------------

cell_offloadgroup_name               string

db_file_name_convert                   string     /oracle/app/oracle/oradata/pow

                                                         erdes, /oracle/app/oracle/orad

                                                         ata/powerdes

db_name                                 string     powerdes

db_unique_name                            string     test_m2

global_names                         boolean FALSE

instance_name                                string     powerdes

lock_name_space                 string

log_file_name_convert                  string     /data/oracle/oradata/powerdes,

                                                          /data/oracle/oradata/pwerdes

 

NAME                                        TYPE       VALUE

------------------------------------ ----------- ------------------------------

processor_group_name                string

service_names                                 string     test_m2

SQL>

 

文章来源blog地址:http://blog.csdn.net/mchdba/article/details/51344246,谢绝转载


4,在master库、standby库,通过sqlplus登录报错

# 主库登录sqlplus报错

[oracle@azure_test_dbm1_3_111 admin]$ sqlplus sys/testsys@test_m2 as sysdba                                                                                                       

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:19:48 2016

 

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:

 

# 备库sqlplus登录报错

[oracle@azure_test_dbm1_3_112 admin]$ sqlplus sys/testsys@test_m2 as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:18:39 2016

 

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:

 

 

都报错:ORA-12514:TNS:listener does not currently know of service requested in connect descriptor这个问题一般就涉及到的是tnsnames.ora里面的service_name没有写对了。

 

5,替换service_name,问题解决

回忆这个备库的搭建过程,我刚建库dbca的时候,设置的service_name是powerdes,我在配置dataguard的时候,修改了参数文件initpowerdes.ora,有在里面新设置*.db_unique_name=test_m2,再次create spfile frompfile;然后以新的参数文件启动数据库后,看到service_names变成了test_m2了,而我就在tnsnames.ora里面设置了新的service_names名字test_m2,这个新的test_m2没有生效结果报错ORA-12514了。

 

因此,我要将没有生效的test_m2换成原来的powerdes,需要换的地方有2个,一个是listener.ora,一个是tnsnames.ora,主库master库和备库standby库都要修改如下所示:

#主库master库修改1个文件tnsnames.ora

# tnsnames.ora文件

[oracle@azure_test_dbm1_3_111 admin]$ vim tnsnames.ora

test_m2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.112)(PORT = 1521))                                                                                                            

    )

    (CONNECT_DATA =

      (SERVICE_NAME = powerdes)

    )

  )

 

#备库standby库需要修改2个文件listener.oratnsnames.ora

# listener.ora文件

[oracle@azure_test_dbm1_3_112 admin]$ vim listener.ora

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SID_NAME = powerdes)

      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)

    )

  )

 

 

ADR_BASE_LISTENER = /oracle/app/oracle

 

# tnsnames.ora文件

[oracle@azure_test_dbm1_3_112 admin]$ vim tnsnames.ora

test_m2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.112)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = powerdes)

    )

  )

 

 

修改完配置后,然后重启lsnrctl监听服务,再在备库使用sqlplus登录test_m2,登录成功:

[oracle@azure_test_dbm1_3_112 admin]$ sqlplus sys/testsys@test_m2 as sysdba                                                                                                   

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:34:51 2016

 

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>

 

再去看主库master库的后台alert日志,就会发现已经有开始传输归档日志的记录了:

……

******************************************************************

LGWR: Setting 'active' archival fordestination LOG_ARCHIVE_DEST_2

******************************************************************

LGWR: Standby redo logfile selected toarchive thread 1 sequence 26

LGWR: Standby redo logfile selected forthread 1 sequence 26 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 26 (LGWRswitch)

 Current log# 2 seq# 26 mem# 0:/oracle/app/oracle/oradata/powerdes/redo02.log

Sun May 08 01:12:22 2016

Archived Log entry 22 added for thread 1sequence 25 ID 0xcf7feffa dest 1:

ARC0: Standby redo logfile selected forthread 1 sequence 25 for destination LOG_ARCHIVE_DEST_2

Destination LOG_ARCHIVE_DEST_2 isSYNCHRONIZED

……

 

PS:这里问题比较奇怪,我上次这里service_name需要修改成新的参数文件里面的test_m2才能归档日志传输到备库standby上面,但是这次确需要保持原来的service_name。看来这里面还有别的奥妙所在。需要去探索清楚了。


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn