[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