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

--前一阵子遇到的测试,链接如下:

--//根据链接的讨论,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 链接: --//主库: 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