DATAGUARD主庫重做日誌不能同步至備庫 alert日中報ORA-12170故障處理
DATAGURAD主庫
系統：solaris 10
資料庫：10.2.0.4 RAC ASM
備庫：
物理備庫
其註冊在11G的監聽上。
注：該備庫主機上有一套11GRAC還有10G的備庫。
以下是詳細處理過程：
1、重做日誌不能同步至備庫，ALERT日誌中報如下錯：
Tue May 6 09:34:50 2014
Errors in file /oracle/admin/dbrac/bdump/dbrac1_lns1_23898.trc:
ORA-12170: TNS:Connect timeout occurred
LNS: Failed to archive log 32 thread 1 sequence 206745 (12170)
Tue May 6 09:38:55 2014
Error 12170 received logging on to the standby
Tue May 6 09:38:55 2014
Errors in file /oracle/admin/dbrac/bdump/dbrac1_arc3_8899.trc:
ORA-12170: TNS:Connect timeout occurred
FAL[server, ARC3]: Error 12170 creating remote archivelog file 'dbrac_standby'
FAL[server, ARC3]: FAL archive failed, see trace file.
2、透過排查，備庫IP能通，TNS配置正常，由於備庫的監聽HANG住，
a、檢視備庫監聽狀態發現監聽HANG住
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:09:22
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
^C 《《===========檢視監聽狀態一直卡住，只能CTRL+C
bash-3.00$ which lsnrctl
/oracle/app/db/product/11.2.0/db/bin/lsnrctl
bash-3.00$ lsnrctl stop
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:10:32
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
^C 《《===========停掉監聽一直卡住，只能CTRL+C
bash-3.00$
b、將監聽程式予以kill重啟後，重做日誌能正常同步至備庫。
bash-3.00$ ps -ef |grep lsnr
oracle 22160 1 0 Apr 06 ? 1:56 /oracle/app/asm/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle 26696 23355 0 10:10:09 pts/5 0:00 grep lsnr
oracle 13109 1 3 Apr 11 ? 2696:02 /oracle/app/db/product/11.2.0/db/bin/tnslsnr LISTENER -inherit
bash-3.00$ kill -9 13109
bash-3.00$ lsnrctl start
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:10:59
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /oracle/app/db/product/11.2.0/db/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Log messages written to /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
The listener supports no services
The command completed successfully
bash-3.00$
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:11:06
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 0 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
The listener supports no services
The command completed successfully
bash-3.00$
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 6 10:11:19 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter system register;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:11:35
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 0 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
Services Summary...
Service "dbtest" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
Service "dbtestXDB" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.00$
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:12:33
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 1 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
Services Summary...
Service "dbrac" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbrac" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbrac_standby" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbrac_standby_XPT" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbtest" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
Service "dbtestXDB" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
The command completed successfully
備庫監聽恢復正常，檢查主庫ALERT日誌，主庫重做日誌傳輸正常，詳細如下:
LNS: Standby redo logfile selected for thread 1 sequence 203309 for destination LOG_ARCHIVE_DEST_2
系統：solaris 10
資料庫：10.2.0.4 RAC ASM
備庫：
物理備庫
其註冊在11G的監聽上。
注：該備庫主機上有一套11GRAC還有10G的備庫。
以下是詳細處理過程：
1、重做日誌不能同步至備庫，ALERT日誌中報如下錯：
Tue May 6 09:34:50 2014
Errors in file /oracle/admin/dbrac/bdump/dbrac1_lns1_23898.trc:
ORA-12170: TNS:Connect timeout occurred
LNS: Failed to archive log 32 thread 1 sequence 206745 (12170)
Tue May 6 09:38:55 2014
Error 12170 received logging on to the standby
Tue May 6 09:38:55 2014
Errors in file /oracle/admin/dbrac/bdump/dbrac1_arc3_8899.trc:
ORA-12170: TNS:Connect timeout occurred
FAL[server, ARC3]: Error 12170 creating remote archivelog file 'dbrac_standby'
FAL[server, ARC3]: FAL archive failed, see trace file.
2、透過排查，備庫IP能通，TNS配置正常，由於備庫的監聽HANG住，
a、檢視備庫監聽狀態發現監聽HANG住
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:09:22
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
^C 《《===========檢視監聽狀態一直卡住，只能CTRL+C
bash-3.00$ which lsnrctl
/oracle/app/db/product/11.2.0/db/bin/lsnrctl
bash-3.00$ lsnrctl stop
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:10:32
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
^C 《《===========停掉監聽一直卡住，只能CTRL+C
bash-3.00$
b、將監聽程式予以kill重啟後，重做日誌能正常同步至備庫。
bash-3.00$ ps -ef |grep lsnr
oracle 22160 1 0 Apr 06 ? 1:56 /oracle/app/asm/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle 26696 23355 0 10:10:09 pts/5 0:00 grep lsnr
oracle 13109 1 3 Apr 11 ? 2696:02 /oracle/app/db/product/11.2.0/db/bin/tnslsnr LISTENER -inherit
bash-3.00$ kill -9 13109
bash-3.00$ lsnrctl start
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:10:59
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /oracle/app/db/product/11.2.0/db/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Log messages written to /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
The listener supports no services
The command completed successfully
bash-3.00$
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:11:06
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 0 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
The listener supports no services
The command completed successfully
bash-3.00$
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 6 10:11:19 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter system register;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:11:35
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 0 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
Services Summary...
Service "dbtest" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
Service "dbtestXDB" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.00$
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 06-MAY-2014 10:12:33
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 06-MAY-2014 10:10:59
Uptime 0 days 0 hr. 1 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/app/db/product/11.2.0/db/log/diag/tnslsnr/dbbak1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbbak1)(PORT=1521)))
Services Summary...
Service "dbrac" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbrac" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbrac_standby" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbrac_standby_XPT" has 1 instance(s).
Instance "dbrac1", status READY, has 1 handler(s) for this service...
Service "dbtest" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
Service "dbtestXDB" has 1 instance(s).
Instance "dbtest1", status READY, has 1 handler(s) for this service...
The command completed successfully
備庫監聽恢復正常，檢查主庫ALERT日誌，主庫重做日誌傳輸正常，詳細如下:
LNS: Standby redo logfile selected for thread 1 sequence 203309 for destination LOG_ARCHIVE_DEST_2
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30633755/viewspace-2127678/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫使用者不能登入 alert日誌報ORA-04031故障分析處理資料庫
- data guard中增加與刪除主備資料庫中的聯機重做日誌與備重做日誌檔案資料庫
- 資料庫alert日誌報ORA-03137 TTC 協議內部錯誤故障分析處理資料庫協議
- Dataguard從庫日誌不同步的原因
- Oracle DataGuard環境主備庫日誌組數和大小調整Oracle
- 【DATAGUARD】手工恢復備庫日誌中斷
- oracle alert日誌亂碼處理Oracle
- 聯機重做日誌、歸檔日誌、備用重做日誌
- 丟失重做日誌怎麼處理
- oracle 中 alert 報警日誌過大的處理方法Oracle
- DATAGUARD中手工處理日誌GAP
- DataGuard備庫刪除已經應用的日誌
- dataguard 主備庫出現gap
- dataguard主庫停監聽後還會把資料同步到備庫
- ORACLE 告警日誌alert過大的處理Oracle
- oracle dataguard資料同步故障處理一例Oracle
- 【故障處理】DBCA建庫詭異問題處理--rac環境不能建立rac庫
- 歸檔日誌滿導致的資料庫掛起故障處理資料庫
- DataGuard主備庫切換步驟
- dataguard故障處理一則
- oracle 之dataguard主庫系統崩潰之物理備庫切主庫Oracle
- SQLServer資料庫日誌太大處理方式SQLServer資料庫
- MYSQL 主從庫同步 異常處理彙總MySql
- DataGuard切換(主庫為Rac+備庫為Rac)
- 主庫RAC,備庫單節點ASM的dataguard搭建ASM
- 【DATAGUARD 學習】管理影響備庫的主庫事件事件
- oracle外部表記錄alert日誌&&資料庫執行報告Oracle資料庫
- 【故障處理】 DBCA建庫報錯CRS-2566
- MySQL 從庫日誌比主庫多MySql
- 11g 邏輯備庫簡單故障處理
- 備份之歸檔重做日誌備份
- 失敗登入行為輸出至alert日誌
- 歸檔日誌滿導致的資料庫掛起故障處理【轉載】資料庫
- 一次dataguard故障處理
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- 在DATAGUARD中手工處理日誌GAP的方法
- Oracle 11g Dataguard 暫停物理備庫的日誌傳輸Oracle
- 一個備份集同時恢出dataguard的主庫&備庫