[20170215]ORA-00088与DG Gap监测与解决4

[20170215]ORA-00088与Data Guard Gap Detection and Resolution4.txt

--前一阵子遇到的测试,链接如下:
http://blog.itpub.net/267265/viewspace-2133106/
http://blog.itpub.net/267265/viewspace-2133107/
http://blog.itpub.net/267265/viewspace-2133250/

--//根据链接的讨论,zergduan给出的结论是:

1. auto gap resolution
通过主库和备库之间的进程心跳来完成传输gap日志，也通过心跳来检测gap

2. FAL
也就是11g之前 FAL client 和 FAL server两个参数，11g开始只有一个参数 FAL Server
通过扫描控制文件发现gap，通过FAL来解决gap

3. 手动解决
由dba来手动传输并注册gap的归档日志，或者通过增量备份前滚恢复解决gap.

--//我前面的测试不设置fal_client,fal_server,log_archive_config,db_unique_name.甚至db_unique_name配置错误,oracle依旧能传
--//输监测gap,传输日志并应用.

--//按照http://blog.itpub.net/267265/viewspace-2133106/不设置fal相关参数,这样实际上使用的是auto gap resolution监测.
--//今天通过遇到的问题再次探讨使用fal的情况.

--//这几天一直在折腾这些测试,再次遇到问题.不过这次遇到的是ora-00088错误.

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试环境出现错误:
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
PING[ARC0]: Heartbeat failed to connect to standby 'bookdg'. Error is 88.
Archived Log entry 875 added for thread 1 sequence 526 ID 0x4fb7d86e dest 1:
FAL[server, ARC0]: Error 88 creating remote archivelog file 'bookdg'
FAL[server, ARC0]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance book - Archival Error. Archiver continuing.
Error 88 for archive log file 1 to 'bookdg'
Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_nsa2_25936.trc:
ORA-00088: command cannot be executed by shared server
FAL[server, ARC0]: Error 88 creating remote archivelog file 'bookdg'
FAL[server, ARC0]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance book - Archival Error. Archiver continuing.

$ cat /u01/app/oracle/diag/rdbms/book/book/trace/book_nsa2_25936.trc
....
*** 2017-02-15 08:17:22.483
Destination is specified with ASYNC=61440
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'

*** 2017-02-15 08:17:24.203
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
Error 88 attaching RFS server to standby instance at host 'bookdg'
Error 88 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'bookdg'
*** 2017-02-15 08:17:24.218 4329 krsh.c
Error 88 for archive log file 1 to 'bookdg'
*** 2017-02-15 08:17:24.218 2917 krsi.c
krsi_dst_fail: dest:2 err:88 force:0 blast:1
ORA-00088: command cannot be executed by shared server

--//开始感觉又是口令文件出问题,我重新设置口令并且拷贝口令文件到备机并且改名到对应实例,问题依旧.
--//再仔细看出现提示:
ORA-00088: command cannot be executed by shared server

$ oerr ora 88
00088, 00000, "command cannot be executed by shared server "
// *Cause:  Debug command issued on shared server.
// *Action: Reissue the command using a dedicated server.

3.从提示看我配置的tnsnames.ora没有使用dedicated server:
--//检查发现确实没有使用,注解部分是我后面加入的.
BOOKDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = book)
#      (server=dedicated)
    )
  )

--//加入(server=dedicated)后,确实问题消失.很明显问题在于fal机制的连接是专用服务模式.我自己犯了一个很低级的错误,
--//配置tns name时,没有加入(server=dedicated).

4.为什么以前没有问题呢?
--//自己很容易联想到以前的测试:[20161212]ezconnect与共享服务模式.txt 链接:http://blog.itpub.net/267265/viewspace-2130292/
--//主库:
SYS@book> show parameter dispatchers
NAME            TYPE      VALUE
--------------- --------- -------------------------------------
dispatchers     string    (PROTOCOL=TCP) (SERVICE=book,bookXDB)
max_dispatchers integer

--// 缺省应该是(PROTOCOL=TCP) (SERVICE=bookXDB),没有book服务.我以前测试使用ezconnect连接优先使用shared server模式.
--// 当tnsnames.ora没有指定(server=dedicated),而配置支持共享服务模式时,orale优先使用共享服务模式.

$ sqlplus sys/oracle@bookdg as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 15 09:52:28 2017
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
SYS@bookdg> select saddr,sid,paddr,status,server from v$session where sid in (select sid from v$mystat);
SADDR                   SID PADDR            STATUS   SERVER
---------------- ---------- ---------------- -------- ---------
0000000085FAA240        133 0000000085CE4430 ACTIVE   SHARED

--//可以看到这个时候连接使用的是SHARED.修改tnsnames.ora配置:

BOOKDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = book)
      (server=dedicated)
    )
  )

$ rlsql sys/oracle@bookdg as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 15 09:54:34 2017
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
SYS@bookdg> select saddr,sid,paddr,status,server from v$session where sid in (select sid from v$mystat);
SADDR                   SID PADDR            STATUS   SERVER
---------------- ---------- ---------------- -------- ---------
0000000085895C20        197 0000000085CEDAA8 ACTIVE   DEDICATED

--//也就是配置tnsnames.ora最好显示指定连接模式是shared还是dedicated.

5.实际上如果你没有配置使用共享服务模式,也不存在这个问题:

--//主库配置tnsnames.ora如下:
BOOKDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = book)
#      (server=dedicated)
    )
  )

--//注意是修改备库的dispatchers参数,不是主库的!!自己又犯浑了.^_^.
SYS@bookdg> show parameter dispatchers
NAME             TYPE     VALUE
---------------- -------- -------------------------------------
dispatchers      string   (PROTOCOL=TCP) (SERVICE=book,bookXDB)
max_dispatchers  integer

SYS@bookdg> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=bookXDB)' scope=memory;
System altered.
--//取消服务名book.

--//注意从主库连接:
$ rlsql sys/oracle@bookdg as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 15 10:03:19 2017
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
SYS@bookdg> select saddr,sid,paddr,status,server from v$session where sid in (select sid from v$mystat);
SADDR                   SID PADDR            STATUS   SERVER
---------------- ---------- ---------------- -------- ---------
0000000085895C20        197 0000000085CEDAA8 ACTIVE   DEDICATED

--//可以发现是DEDICATED连接模式.
--//这样传输日志也正常就ok了.

6.总结:
--//1.以后配置dg,或者网络连接tnsnames.ora配置最好显示的指定连接模式是shared还是dedicated.不能依靠缺省设置.
--//2.另外我的测试在修改这些参数时并没有影响当前的日志传输与应用,比如我修改tnsnames.ora或者修改
--//dispatchers='(PROTOCOL=TCP)(SERVICE=book,bookXDB)' ,仅仅在网络出现问题或者出现gap时,或者重启dg时问题才会再现.
--//3.另外注意的问题还有一些应用比如rman连接必须使用dedicated.

--//假设配置shared模式.
$ rman target sys/oracle@book
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 15 10:18:55 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-28547: connection to server failed, probable Oracle Net admin error

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2133551/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2133551/