ORA-01172: recovery of thread string stuck at block string of file string

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:([email protected] (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 )

 

 69 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply