Oracle standby ORA-00600:[3020] ORA-10567
現象
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Red Hat Enterprise Linux Server release 5.3 (Tikanga) 64bit
在主庫執行將檔案從4G改小為2G後
ALTER DATABASE DATAFILE '/data/orcl/undotbs04.dbf' RESIZE 2000M;
Standby DB中
SELECT OPEN_MODE FROM V$database
READ ONLY WITH APPLY 變為了 READ ONLY
Standby alert.log
Wed Mar 25 13:24:25 2015
Errors in file /u01/product/diag/rdbms/orcldg/orcl/trace/orcl_mrp0_7554.trc (incident=592209):
ORA-00600: internal error code, arguments: [3020], [6], [2343], [25168167], [], [], ORA-10567: Redo is inconsistent with data block (file# 6, block# 2343, file offset is 38387712 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 6: '/data/orcl/undotbs03.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
Incident details in: /u01/product/diag/rdbms/orcldg/orcl/incident/incdir_592209/orcl_mrp0_7554_i592209.trc
Recovery Slave PR05 previously exited with exception 600
Wed Mar 25 13:24:26 2015
MRP0: Background Media Recovery terminated with error 448
Errors in file /u01/product/diag/rdbms/orcldg/orcl/trace/orcl_pr00_7689.trc:
ORA-00448: normal completion of background process
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Trace dumping is performing id=[cdmp_20150325132426]
Recovered data files to a consistent state at change 8924400385
Errors in file /u01/product/diag/rdbms/orcldg/orcl/trace/orcl_pr00_7689.trc:
ORA-00448: normal completion of background process
Errors in file /u01/product/diag/rdbms/orcldg/orcl/trace/orcl_mrp0_7554.trc:
ORA-00600: internal error code, arguments: [3020], [6], [2343], [25168167], [], [], [],
ORA-10567: Redo is inconsistent with data block (file# 6, block# 2343, file offset is 38387712 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 6: '/data/orcl/undotbs03.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'
MRP0: Background Media Recovery process shutdown (orcl)
Wed Mar 25 13:24:28 2015
Sweep [inc][592249]: completed
Sweep [inc][592209]: completed
Sweep [inc2][592249]: completed
Sweep [inc2][592209]: completed
手動執行alter database recover managed standby database using current logfile disconnect 報錯依舊。關閉standby再開啟問題依舊
處理:
主庫 alter tablespace UNDOTBS1 begin backup;
主庫 Scp /data/orcl/undotbs03.dbf -> 從庫
主庫 alter tablespace UNDOTBS1 end backup;
主庫 alter database create standby controlfile as ‘/data/standby.ctl’
主庫 Scp /data/standby.ctl -> 從庫
從庫 startup
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/data/orcl/standbylog15.log') size 50M;
……
alter database recover managed standby database using current logfile disconnect
恢復OK
Bug 11689702 ORA-600 [3020] during recovery after datafile RESIZE (to smaller size)
This note gives a brief overview of bug 11689702.
The content was last updated on: 28-JUN-2013
Click here for details of each of
the sections below.
Affects:
Product (Component) |
Oracle Server (Rdbms) |
Range of versions believed to be affected |
Versions >= 11.2 but BELOW 12.1 |
Versions confirmed as being affected |
|
Platforms affected |
Generic (all / most platforms affected) |
Fixed:
This issue is fixed in |
||||
Symptoms: |
Related To: |
|||
Description
Media recovery (either at a standby or standard media recovery)
may get stuck and stop due to an ORA-600 [3020] following a datafile
resize operation (to resize a datafile smaller) on the primary.
Rediscovery Notes:
Look for all the following:
- Media recovery fails with ORA-600 [3020],[<file#>],[<block#>]
- Any attempt to restart media recovery fails with exactly the
same error (most likely this is a primary/standby congifuration
and the media recovery is failing with ORA-600 [3020] on the
standby)
- look at the alert log for the time period covering the media
recovery session, and search for the ORA-600 [3020] message,
then:
1. see which log seq# was being applied at the time of the
ORA-600 [3020], and then look at
2. see which filename it was reported for:
ORA-1110: data file <file#>: '<datafile_name>'
then look at the alert log messages that were generated while
that log seq# was current and originally being written to,
there should be a resize operation for that datafile:
"alter database datafile '<datafile_filename>' resize <n>"
The resize should be resizing the datafile smaller, but it
will only be possible to confirm this if there are other
resize operations for the same file earlier in the alert log
to prove the datafile initially had a larger size just prior
to this resize.
- the main ORA-600 [3020] tracefile (not the incident tracefile)
should have the following contents:
-----
KCOX_FUTURE: CHANGE IN FUTURE OF BLOCK
and
RECOVERY STUCK AT BLOCK <block#> OF FILE <file#>
and
CHANGE ...
(this is a dump of the individual redo change vector which
recovery got stuck on and could not apply)
and
ORA-01110: data file <file#>: '<datafile_name>'
ORA-10560: block type ...
and
buffer tsn: ...
(this is a dump of the cache header stored at the front of
the block, which the redo change could not be applied to)
and
DUMP REDO
(a dump of the recent redo, filtered for the file#/block#)
END OF DUMP REDO
and
ORA-00600: [3020], [<file#>], [<block#>], [<DBA>]
ORA-10567: Redo is inconsistent with data block (file# ...
ORA-10564: tablespace <tablespace_name>
ORA-01110: data file <file#>: '<datafile_name>'
ORA-10560: block type ...
-----
examine the redo dump, and find the redo record containing the
change vector (for file#/block#) which recovery got stuck on -
it should appear near the end of the redo dump, then go backwards
from that redo record, to find the immediately previous change to
file#/block#, and note the scn of that redo record, then at that
same scn there should be a datafile resize marker redo record
which shinks the file (ie old size is greater than new size).
Eg:
REDO RECORD - Thread:1 RBA: 0x000004.00001394.0198 LEN: 0x0128 VLD: 0x01
SCN: 0x0000.00072bbd SUBSCN: 43 02/28/2011 11:49:14
CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x0100000c OBJ:64299 SCN:0x0000.00072bbd
SEQ:42 OP:11.3 ENC:0 RBL:0
...
REDO RECORD - Thread:1 RBA: 0x000004.00001395.00d0 LEN: 0x0044 VLD: 0x01
SCN: 0x0000.00072bbd SUBSCN: 43 02/28/2011 11:49:14
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.4 ENC:0
Datafile resize marker - file: 4 old size: 12800 new size: 12672
...
REDO RECORD - Thread:1 RBA: 0x000004.00001396.0010 LEN: 0x0150 VLD: 0x05
SCN: 0x0000.00072bbf SUBSCN: 1 02/28/2011 11:49:14
Getting a Fix
Use one of the "Fixed" versions listed above
(for Patch Sets / bundles use the latest version available as
contents are cumulative - the "Fixed" version listed above is
the first version where the fix is included)
or
Click here for suggestions on how to get a fix for this issue
HOOKS INCLUDE:FIXHELP OERI:3020 "SQL:RESIZE" LIKELYAFFECTS XAFFECTS_11.2.0.1 XAFFECTS_V11020001 AFFECTS=11.2.0.1 XAFFECTS_11.2.0.2 XAFFECTS_V11020002 AFFECTS=11.2.0.2 XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_OERI TAG_RECOVERY TAG_STANDBY OERI RECOVERY STANDBY FIXED_11.2.0.2.5 FIXED_11.2.0.2.BP13 FIXED_11.2.0.2.GIPSU05 FIXED_11.2.0.3 FIXED_12.1.0.1 FIXED_WIN:B202P14
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support. |
References
Bug:11689702 (This link will only
work for PUBLISHED bugs)
Note:245840.1 Information on the
sections in this article
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25583515/viewspace-2146416/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORA-00600 3020 和ORA-10567 問題定位
- ora-00600 3020 問題分析處理
- Stuck recovery of database ORA-00600[3020] (Doc ID 283269.1)Database
- STANDBY DATABSE UNDO ERROR(ORA-10564、ORA-10567、ORA-01110)Error
- Oracle physical standbyOracle
- Oracle的快照standbyOracle
- ORACLE10G 物理standby轉為邏輯standbyOracle
- 邏輯STANDBY上的ORA-00600: internal error code, arguments: [krvtadc], [], [], [], [], []Error
- Oracle DG Standby Database型別OracleDatabase型別
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- Oracle cascading standby ParametersOracle
- oracle Physical Standby failover stepOracleAI
- Oracle Standby系統相關Oracle
- oracle 之dataguard standby 切換Oracle
- Oracle Standby資料庫建立Oracle資料庫
- Oracle 9I dataguard(standby)Oracle
- oracle錯誤之ORA-00600Oracle
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- Oracle10g RAC配置standbyOracle
- 建立Local Physical Standby Oracle9i standby 資料庫筆記Oracle資料庫筆記
- ORACLE DUPLICATE建立物理standby資料庫Oracle資料庫
- 【DataGuard】Oracle 11g physical standby switchoverOracle
- Oracle10G Physical Standby Database setupOracleDatabase
- 建立Oracle 11g logical standbyOracle
- Oracle Standby系統管理與維護Oracle
- 獨立開啟Oracle的Standby庫Oracle
- Oracle10g logical standby 建立Oracle
- Create Logical Standby For Oracle 10GOracle 10g
- Oracle10g_RAC_Standby_MAA 3.0Oracle
- Oracle 11g Cross platform Active StandbyOracleROSPlatform
- Oracle Standby Redo Log實驗兩則Oracle
- Oracle DataGuard Standby database ID mismatch錯誤OracleDatabase
- Oracle 9i Logical Standby與Physical standby歸檔恢復區別Oracle
- Oracle recover current redo ORA-00600:[4193] (oracle 故障恢復current redo日誌ORA-00600:[4193]報錯)Oracle
- oracle 10g physical standby 切換操作Oracle 10g
- oracle 12c 支援級聯 standby dataguardOracle
- ORACLE10g DataGuard 配置Physical Standby DatabaseOracleDatabase
- Oracle10gR2 Logical Standby (zt)Oracle