请问刘大，这个是dataguard的bug吗？

zhuqibs

oracle：11.2.0.1
platform：redhat linux 5.7

在 ASM和filesystem之间做dataguard，遇到问题！

（1）按照步骤建立dataguard后，起初没有问题

（2）切换后，出现问题，主库（就是原来的从库），不断报ORA-16047，

（3）网上查询后，metalink查询后，都是说这是个非常简单的报错，就是log_archive_dest_n和从库的db_unique_name没有一致，
（4）但无论我怎么检查，他们都是一致的，现在贴出我的配置，请刘大指正。

主库（就是原来的从库）：

*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='oracle.com'
*.db_file_multiblock_read_count=8
*.db_file_name_convert='+DATA','/oradata/myasm_dg'
*.db_name='myasm'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=2558525440
*.db_unique_name='myasm_dg'
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=myasmXDB)'
*.fal_client='STD'
*.fal_server='PRI'
*.instance_number=1
*.log_archive_config='DG_CONFIG=(myasm_dg,myasm)'
*.log_archive_dest_1='location=/oradata/myasm_dg/arch valid_for=(all_logfiles,all_roles) db_unique_name=myasm_dg'
*.log_archive_dest_2='service=pri valid_for=(online_logfiles,primary_role) db_unique_name=myasm LGWR ASYNC REOPEN=10'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA','/oradata/myasm_dg','+FRA','/oradata/myasm_dg'

从库（就是原来的主库）：
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='oracle.com'
*.db_file_multiblock_read_count=8
*.db_file_name_convert='/oradata/myasm_dg','+DATA'
*.db_name='myasm'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=2558525440
*.db_unique_name='myasm'
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=myasmXDB)'
*.fal_client='PRI'
*.fal_server='STD'
*.instance_number=1
*.log_archive_config='DG_CONFIG=(myasm,myasm_dg)'
*.log_archive_dest_1='location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=myasm'
*.log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=myasm_dg LGWR ASYNC REOPEN=10'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/oradata/myasm_dg','+DATA','/oradata/myasm_dg','+FRA'

主库（原来的从库）：
SQL> select dest_name,error from v$archive_dest;

DEST_NAME                      ERROR
------------------------------ -----------------------------------------------------------------
LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2             ORA-16047: DGID mismatch between destination setting and target
                               database

LOG_ARCHIVE_DEST_3
LOG_ARCHIVE_DEST_4

alert文件中：
Tue Apr 23 18:07:35 2013
Errors in file /u02/app/oracle/diag/rdbms/myasm_dg/myasm_dg/trace/myasm_dg_arc1_16959.trc:
ORA-16047: DGID mismatch between destination setting and target database
PING[ARC1]: Heartbeat failed to connect to standby 'pri'. Error is 16047.

tns文件：(单机做的，比较简单）
pri =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test.linux.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myasm.oracle.com)
    )
  )


std =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test.linux.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myasm_dg.oracle.com)

监听文件：
SID_LIST_LISTENER=
   (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = myasm.oracle.com)
        (ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = myasm)
      )
      (SID_DESC =
        (GLOBAL_DBNAME = myasm_dg.oracle.com)
        (ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = myasm_dg)
      )
   )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test.linux.com)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

Maclean Liu(刘相兵

Error:   ORA-16047  (ORA-16047)
Text:    DGID mismatch between destination setting and target database
---------------------------------------------------------------------------
Cause: The DB_UNIQUE_NAME specified for the destination did not match the DB_UNIQUE_NAME at the target database.
Action: Make sure the DB_UNIQUE_NAME specified in the LOG_ARCHIVE_DEST_n parameter matches the DB_UNIQUE_NAME parameter defined at the destination.

给出 /u02/app/oracle/diag/rdbms/myasm_dg/myasm_dg/trace/myasm_dg_arc1_16959.trc 和 alert .log 压缩打包上传

zhuqibs

trace文件，和主机和从机上的alert文件

zhuqibs

这个报错很奇怪，完全关闭主从机，重新启动，主机open，从机open read only and apply时

查看v$archive_dest_status，主机的log_archive_dest_2的error是没有的，而且主从机归档日志是相等的，

也就是说，日志在重启时，可以传输（我在关闭时，两边日志是不相等的），但是只要一个alter

system  switch logfile; 立马alert报错，log_archive_dest_2的error也显示错误16047，真是让人抓狂。

Maclean Liu(刘相兵

action plan :

1
在2个节点上均
alter system set log_archive_config='DG_CONFIG=(myasm,myasm_dg)' ;

alter system switch logfile ;

并观察


2、

action plan:

如步骤1不奏效则 ，在2个节点上

ALTER SYSTEM SET log_archive_config='NODG_CONFIG';
alter system switch logfile ;

并观察


3、

在2个节点上做 ：


tnsping  pri
tnsping std


sqlplus  system/密码@pri

show parameter db_unique_name

sqlplus  system/密码@std

show parameter db_unique_name


lsrnctl status

Maclean Liu(刘相兵

重点做这个：

在2个节点上做 ：


tnsping  pri
tnsping std


sqlplus  system/密码@pri

show parameter db_unique_name

sqlplus  system/密码@std

show parameter db_unique_name


lsrnctl status

zhuqibs

（1）首先，alter system set log_archive_config='DG_CONFIG=(myasm,myasm_dg)' ;

alter system switch logfile ;

主从库工作正常，v$archive_dest没有报错，但alert还是报错；

（2）ALTER SYSTEM SET log_archive_config='NODG_CONFIG';
alter system switch logfile ;

归档无法传递到从库，但RFS成功；
ALTER SYSTEM SET log_archive_config='NODG_CONFIG' SCOPE=BOTH;
Wed Apr 24 22:35:31 2013
RFS[12]: Opened log for thread 1 sequence 83 dbid -62037650 branch 813487945
Archived Log entry 306 added for thread 1 sequence 83 rlc 813487945 ID 0xfc914212 dest 2:
Wed Apr 24 22:35:36 2013
Media Recovery Log +FRA/myasm/archivelog/2013_04_24/thread_1_seq_83.326.813623731
Media Recovery Waiting for thread 1 sequence 84

主库报错：ora-16009
Errors in file /u02/app/oracle/diag/rdbms/myasm_dg/myasm_dg/trace/myasm_dg_nsa2_17895.trc:
ORA-16009: invalid redo transport destination
Error 16009 for archive log file 2 to 'pri'
Errors in file /u02/app/oracle/diag/rdbms/myasm_dg/myasm_dg/trace/myasm_dg_nsa2_17895.trc:
ORA-16009: invalid redo transport destination

（3）tnsping都成功，sqlplus 都能连上
standby：
[oracle@test ~]$ sqlplus sys/oracle@pri as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 24 22:42:07 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn sys/oracle@std as sysdba
Connected.

primary：
[oracle@test ~]$ sqlplus sys/oracle@pri as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 24 22:43:28 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn sys/oracle@std as sysdba
Connected.


[oracle@test ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-APR-2013 22:45:04

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-APR-2013 10:30:59
Uptime                    1 days 12 hr. 14 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.linux.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "myasm.oracle.com" has 3 instance(s).
  Instance "myasm", status UNKNOWN, has 1 handler(s) for this service...
  Instance "myasm", status READY, has 1 handler(s) for this service...
  Instance "myasm_dg", status READY, has 1 handler(s) for this service...
Service "myasmXDB.oracle.com" has 2 instance(s).
  Instance "myasm", status READY, has 1 handler(s) for this service...
  Instance "myasm_dg", status READY, has 1 handler(s) for this service...
Service "myasm_dg.oracle.com" has 2 instance(s).
  Instance "myasm_dg", status UNKNOWN, has 1 handler(s) for this service...
  Instance "myasm_dg", status READY, has 1 handler(s) for this service...
The command completed successfully

Maclean Liu(刘相兵

  db_name                  = "myasm"
  db_unique_name           = "myasm"
  service_names            = "myasm"




  db_name                  = "myasm"
  db_unique_name           = "myasm_dg"

  service_names            = "myasm"


你的2个实例的service_name 都是 myasm ，这不太正常


SQL> alter system set db_unique_name='TESTMAC1' scope=spfile;

系统已更改。

SQL> startup force;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS insta
ORACLE 例程已经启动。

Total System Global Area  981250048 bytes
Fixed Size                  2261200 bytes
Variable Size             503320368 bytes
Database Buffers          469762048 bytes
Redo Buffers                5906432 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter db_unique

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_unique_name                       string
TESTMAC1
SQL> show parameter service_name

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
service_names                        string
TESTMAC1

SQL> show parameter db_name

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_name                              string
TESTMAC

Maclean Liu(刘相兵

Service "myasm.oracle.com" has 3 instance(s).
  Instance "myasm", status UNKNOWN, has 1 handler(s) for this service...
  Instance "myasm", status READY, has 1 handler(s) for this service...
  Instance "myasm_dg", status READY, has 1 handler(s) for this service...



==> myasm.oracle.com 这个服务下面居然还有 myasm_dg 这个实例， 显然是不正常的。

不要再忽略细节了！！

zhuqibs

恩，是的，原来以为
从库设置db_unique_name=myasm_dg就可以了，service_name会自动跟随db_unique_name变到myasm_dg，但是没想到复制的参数文件中定死了，没有跟过去。

看来，复制参数文件，项目太多会忽视某些内容啊，以后一定注意了，谢谢刘大啊：）

zhuqibs

原来用 sqlplus sys/oracle@myasm_dg as sysdba 连通 standby，是用静态监听连上的，服务名错了，都浑然不知，惭愧啊！

uj12best

其实不考虑切来切去的话，备库就设FAL_CLIENT,FAL_SERVER,STANDBY_MANAGER,STANDBY_ARCHIVE_DEST四个参数就够了，非常方便。