I got ” ORA-01172: recovery of thread string stuck at block string of file string ” error in Oracle database.
ORA-01172: recovery of thread string stuck at block string of file string
Details of error are as follows.
ORA-01172: recovery of thread string stuck at block string of file string Cause: Crash recovery or instance recovery could not apply a change to a block because it was not the next change. This can happen if the block was corrupted and then repaired during recovery. Action: Do a RECOVER DATAFILE for the file containing the block. If this does not resolve the problem then restore the file from a backup and recover it. SQL> alter database open ; alter database open * ERROR at line 1: ORA-01172: recovery of thread 1 stuck at block 456 of file 63 ORA-01151: use media recovery to recover block, restore backup if needed
recovery of thread string stuck at block string of file string
This ORA-01172 is related to the Crash recovery or instance recovery could not apply a change to a block because it was not the next change. This can happen if the block was corrupted and then repaired during recovery.
To solve this error, firstly recover database as follows.
SQL> recover database; Media recovery complete.
Now try to open database as follows.
SQL> alter database open; Database altered.
Second case is as follows.
The MGMTDB is getting crashed with following errors which reported in logs.
alert_-mgmtdb.log:
Mon Nov 10 13:26:28 2014
Hex dump of (file 0, block 84) in trace file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_1138.trc
Corrupt block relative dba: 0x00000054 (file 0, block 84)
Completely zero block found during control file block read
Control file block read for fixed table access has failed due to concurrent controlfile updates.
This is not a real data corruption. Please retry the query.
Mon Nov 10 13:26:28 2014
Errors in file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_1138.trc:
ORA-00202: control file: '+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267'
Errors in file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_1138.trc (incident=7345) (PDBNAME=RWSAI_CLUSTER):
ORA-00227: corrupt block detected in control file: (block 84, # blocks 1)
ORA-00202: control file: '+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267'
Incident details in:<ORACLE_BASE>/rdbms/_mgmtdb/-MGMTDB/incident/incdir_7345/-MGMTDB_ora_1138_i7345.trc
Mon Nov 10 13:27:04 2014
Hex dump of (file 0, block 16) in trace file <ORACLE_BASE>/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_m000_3672.trc
Corrupt block relative dba: 0x00000010 (file 0, block 16)
Completely zero block found during control file block read
Control file block read for fixed table access has failed due to concurrent controlfile updates.
This is not a real data corruption. Please retry the query.
...
Mon Nov 10 13:27:44 2014
Read from controlfile member '+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267' has found a corrupted block (blk# 16, cf seq# 31193)
Reread from mirror side 'SYSTEMI12' returns corrupted data
Hex dump of (file 0, block 16) in trace file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_lgwr_31029.trc
Corrupt block relative dba: 0x00000010 (file 0, block 16)
Fractured block found during control file block reread from mirror
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.0000.00000000 seq: 0x0 flg: 0x00
spare3: 0x0
consistency value in tail: 0x00001501
check value in block header: 0x0
block checksum disabled
Reread from mirror side 'SYSTEMI11' returns corrupted data
Hex dump of (file 0, block 16) in trace file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_lgwr_31029.trc
..
Errors in file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ckpt_31033.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01122: database file 9 failed verification check
ORA-01110: data file 9: '+SYSTEMI/_MGMTDB/06F2D89A7AE1703BE053F2A9E80ADC3D/DATAFILE/users.274.862816533'
ORA-01210: data file header is media corrupt
Errors in file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ckpt_31033.trc (incident=7353) (PDBNAME=CDB$ROOT):
ORA-1242 [] [] [] [] [] [] [] [] [] [] [] []
Incident details in: <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/incident/incdir_7353/-MGMTDB_ckpt_31033_i7353.trc
USER (ospid: 31033): terminating the instance due to error 1242
....
Aborting crash recovery due to error 1172
Mon Nov 17 10:43:04 2014
Errors in file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_28126.trc:
ORA-01172: recovery of thread 1 stuck at block 38272 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Mon Nov 17 10:43:04 2014
Errors in file <ORACLE_BASE>/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_28126.trc:
ORA-01172: recovery of thread 1 stuck at block 38272 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN /* db agent *//* {6:36505:2} */...
Mon Nov 17 10:43:06 2014
License high water mark = 4
Mon Nov 17 10:43:06 2014
USER (ospid: 28494): terminating the instance
Mon Nov 17 10:43:06 2014
Instance terminated by USER, pid = 28494
MGMTDB_ora_1138.trc :
*** 2014-11-10 13:26:28.323
*** SESSION ID:(28.15708) 2014-11-10 13:26:28.323
*** CLIENT ID:() 2014-11-10 13:26:28.323
*** SERVICE NAME:(rwsai_cluster) 2014-11-10 13:26:28.323
*** MODULE NAME:(ologgerd@rwsaj11 (TNS V1-V3)) 2014-11-10 13:26:28.323
*** ACTION NAME:() 2014-11-10 13:26:28.323
*** CLIENT DRIVER:() 2014-11-10 13:26:28.323
*** CONTAINER ID:(3) 2014-11-10 13:26:28.323
Hex dump of (file 0, block 84)
Dump of memory from 0x00007FE1FDC5BE00 to 0x00007FE1FDC5FE00
7FE1FDC5BE00 0000C200 00000054 00000000 01010000 [….T………..]
7FE1FDC5BE10 00000000 00000000 00000000 00000000 […………….]
Repeat 1021 times
7FE1FDC5FDF0 00000000 00000000 00000000 00000001 […………….]
Corrupt block relative dba: 0x00000054 (file 0, block 84)
Completely zero block found during control file block read
Control file block read for fixed table access has failed due to concurrent controlfile updates.
This is not a real data corruption. Please retry the query.
Detected a media corrupt controlfile block (block# 84) in ‘+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267’
DDE rules only execution for: ORA 202
—– START Event Driven Actions Dump —-
—- END Event Driven Actions Dump —-
—– START DDE Actions Dump —–
Executing SYNC actions
—– START DDE Action: ‘DB_STRUCTURE_INTEGRITY_CHECK’ (Async) —–
Successfully dispatched
—– END DDE Action: ‘DB_STRUCTURE_INTEGRITY_CHECK’ (SUCCESS, 0 csec) —–
Executing ASYNC actions
—– END DDE Actions Dump (total 0 csec) —–
*** 2014-11-10 13:26:28.334
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
—– Error Stack Dump —–
ORA-00202: control file: ‘+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267’
—– Current SQL Statement for this session (sql_id=209h1kk1w8tfj) —–
SELECT fs.freespace, df.totalspace FROM (select round(sum(bytes) / 1048576) TotalSpace FROM dba_data_files WHERE tablespace_name = ‘SYSMGMTDATA’) df, (select round(sum(bytes) / 1048576) FreeSpace FROM dba_free_space WHERE tablespace_name = ‘SYSMGMTDATA’) fs
MGMTDB_ckpt_31033.trc:
*** 2014-11-10 12:40:30.019 *** CLIENT ID:() 2014-11-10 12:40:30.019 *** SERVICE NAME:() 2014-11-10 12:40:30.019 *** MODULE NAME:() 2014-11-10 12:40:30.019 *** ACTION NAME:() 2014-11-10 12:40:30.019 *** CLIENT DRIVER:() 2014-11-10 12:40:30.019 2014-11-10 12:40:30.019047 :kjcipctxinit(): (pid|psn)=(19|1): initialised and linked pctx 0x8c46b7e8 into process list *** 2014-11-10 12:40:33.515 *** SESSION ID:(246.56668) 2014-11-10 12:40:33.515 *** SERVICE NAME:() 2014-11-10 12:40:33.515 *** CONTAINER ID:(1) 2014-11-10 12:40:33.515 ksfdrfms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d134a4e00 blkno=1 nbytes=32768 ksfdafReadMirror: Read success from mirror side=1 logical extent number=0 disk=SYSTEMI4 path=AFD:SYSTEMI4 Mirror I/O done from ASM disk AFD:SYSTEMI4 ksfdrnms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d134a4e00 nbytes=32768 ksfdafReadMirror: Read success from mirror side=2 logical extent number=1 disk=SYSTEMI8 path=AFD:SYSTEMI8 Mirror I/O done from ASM disk AFD:SYSTEMI8 ksfdrnms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d134a4e00 nbytes=32768 ksfdafReadMirror: Read success from mirror side=3 logical extent number=2 disk=SYSTEMI12 path=AFD:SYSTEMI12 Mirror I/O done from ASM disk AFD:SYSTEMI12 ksfdrnms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d134a4e00 nbytes=32768 Using pga_aggregate_limit of 2048 MB Control file enqueue hold time tracking dump at time: 2797 *** 2014-11-10 13:15:41.018 1: 530ms (rw) file: kcrf.c line: 11079 count: 140733193388033 total: 530ms time: 1764 Read from controlfile member '+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267' has found a corrupted block (blk# 1118, cf seq# 31193) *** 2014-11-10 13:27:47.282 ksfdrfms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d13ad3400 blkno=1118 nbytes=16384 ksfdafReadMirror: Read success from mirror side=1 logical extent number=0 disk=SYSTEMI8 path=AFD:SYSTEMI8 Mirror I/O done from ASM disk AFD:SYSTEMI8 <======== AFD used for ASM disk. Reread from mirror side 'SYSTEMI8' returns corrupted data Hex dump of (file 0, block 1118) ... Corrupt block relative dba: 0x0000045e (file 0, block 1118) Bad header found during control file block reread from mirror Data in bad block: type: 162 format: 3 rdba: 0x00002fb2 last change scn: 0x0000.0000.d200d722 seq: 0x0 flg: 0x00 spare3: 0xd200 consistency value in tail: 0x00001501 check value in block header: 0xd722 block checksum disabled ksfdrnms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d13ad3400 nbytes=16384 ksfdafReadMirror: Read success from mirror side=2 logical extent number=1 disk=SYSTEMI1 path=AFD:SYSTEMI1 Mirror I/O done from ASM disk AFD:SYSTEMI1 Reread from mirror side 'SYSTEMI1' succeeded ksfdrnms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d13ad3400 nbytes=16384 ksfdafReadMirror: Read success from mirror side=3 logical extent number=2 disk=SYSTEMI5 path=AFD:SYSTEMI5 Mirror I/O done from ASM disk AFD:SYSTEMI5 Reread from mirror side 'SYSTEMI5' succeeded ksfdrnms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d13ad3400 nbytes=16384 Control file block # 1118 was rewritten to repair corruption Read from controlfile member '+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267' has found a corrupted block (blk# 17, cf seq# 31193) ksfdrfms:Mirror Read file=+SYSTEMI/_MGMTDB/CONTROLFILE/current.260.862816267 fob=0x8ae19c68 bufp=0x7f7d13ad3600 blkno=17 nbytes=16384 ksfdafReadMirror: Read success from mirror side=1 logical extent number=0 disk=SYSTEMI12 path=AFD:SYSTEMI12 Mirror I/O done from ASM disk AFD:SYSTEMI12 Reread from mirror side 'SYSTEMI12' returns corrupted data Hex dump of (file 0, block 17) .. *** 2014-11-10 13:27:48.075 USER (ospid: 31033): terminating the instance due to error 1242 ksuitm: waiting up to [5] seconds before killing DIAG(31001)
Due to bug 20010980.
Linux 2.6.24 and above kernel has changed the iodone function signature and AFDcode hasn’t been modified with that change. This is causing the mishandling of disk IO errors.
The bug fix included in 12.1.0.2 GIPSU03,request/apply patch 20010980 for your platform/version
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )