一天兩次，PING[ARC1]: Heartbeat failed to connect to standby

dawn009發表於2014-07-18
AI
       昨天一天遇到兩次主備庫的PING[ARC1]: Heartbeat failed to connect to standby的日誌傳輸同步問題，兩次雖都報的 Heartbeat failed to connect to standby，但原因各不相同。

       第一次是自己剛搭建的實驗庫，開啟主庫對備庫的日誌傳輸alert檔案中就出現：
Fri Sep 15 18:54:06 2011
Error 12154 received logging on to the standby
Fri Sep 15 18:54:06 2011
Errors in file /u01/app/oracle/admin/testdb/bdump/testdb_arc1_9592.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
PING[ARC1]: Heartbeat failed to connect to standby 'testdg'. Error is 12154.

        一般附帶ORA-12154是主備庫身份識別的問題，基本還是那幾個配置點的問題，是需要檢查的幾項：
        我的環境中使用了相同的例項名，相同的檔案路徑（因為這樣簡單），區別只在db_unique_name和tnsnames.ora中

        1.name的引數
--主庫
NAME                     TYPE      VALUE
------------------------ --------- ------------------------------
db_file_name_convert     string
db_name                  string    testdb
db_unique_name           string    testdb
global_names boolean     FALSE
instance_name            string    testdb
lock_name_space          string
log_file_name_convert    string
service_names            string    testdb
--備庫
NAME                     TYPE      VALUE
------------------------ --------- ------------------------------
db_file_name_convert     string
db_name                  string    testdb
db_unique_name           string    testdg
global_names boolean     FALSE
instance_name            string    testdb
lock_name_space          string
log_file_name_convert    string
service_names            string    testdb

        2.listener.ora
--主庫
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.92)(PORT = 1530))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = testdb)
      (OBILLDBLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (SID_NAME = testdb)
    )
  )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
--備庫
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.93)(PORT = 1530))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = testdb)
      (OBILLDBLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (SID_NAME = testdb)
    )
  )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

        3.tnsnames.ora
--主備庫一樣
testdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.92)(PORT = 1530))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = testdb)
    )
  )
testdg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.93)(PORT = 1530))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = testdb)
    )
  )

--listener和tnsnames配完後應該測試下，tnsping下對方，用sqlplus sys/passsword@testdb as sysdba來連線下試試

        4.fal_client和fal_server的引數
SQL> show parameter fal
--主庫
NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
fal_client      string  testdb
fal_server      string  testdg
--備庫
NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
fal_client      string  testdg
fal_server      string  testdb

        5.archive的引數
SQL> show parameter archive
--主庫
NAME                       TYPE    VALUE
-------------------------- ------- ------------------------------
log_archive_config         string  DG_CONFIG=(testdb,testdg)
log_archive_dest_1         string  LOCATION=/u02/archive/testdb VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb
log_archive_dest_2         string  LOCATION=/u02/stdlog/testdb VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdb
log_archive_dest_3         string  SERVICE=testdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdg
log_archive_dest_state_1   string  enable
log_archive_dest_state_2   string  enable
log_archive_dest_state_3   string  enable
--備庫
NAME                       TYPE    VALUE
-------------------------- ------- ------------------------------
log_archive_config         string  DG_CONFIG=(testdb,testdg)
log_archive_dest_1         string  LOCATION=/u02/archive/testdb VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdg
log_archive_dest_2         string  LOCATION=/u02/stdlog/testdb VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdg
log_archive_dest_3         string  SERVICE=testdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb
log_archive_dest_state_1   string  enable
log_archive_dest_state_2   string  enable
log_archive_dest_state_3   string  enable

--以上5部分配置正確應該是沒有問題的，見鬼的是我在配置完全正確的情況下，錯誤依然，重啟主備庫，錯誤消失，備庫開始接收和應用日誌。果然重啟是最好的解決問題辦法。

        第二次出現在生產備庫，報警很明顯，是password file出現了問題，從主庫傳一個password file過來即可，但見鬼的是我們並沒有改過密碼，也沒動過password file

Thu Sep 15 21:12:02 2011
Errors in file /u02/app/oracle/admin/db/bdump/db_arc3_639170.trc:
ORA-01017: invalid username/password; logon denied
Thu Sep 15 21:12:02 2011
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
Thu Sep 15 21:12:02 2011
Errors in file /u02/app/oracle/admin/db/bdump/db_arc3_639170.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC3]: Heartbeat failed to connect to standby 'dg2'. Error is 16191.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1222267/，如需轉載，請註明出處，否則將追究法律責任。

相關文章