Shulou(Shulou.com)06/02 Report--

Due to UPS failure, the computer room lost power for many times in succession. After the problem was solved, it was found that a local test database was opened by Times error, ORA-1172, ORA-1151 error.

My colleague told me that a database could not be opened and could only be started to MOUNT status, so I tried to open it by connecting to the database with the following error:

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01172: recovery of thread 1 stuck at block 1669 of file 2

ORA-01151: use media recovery to recover block, restore backup if needed

It seems that a bad block has appeared in the data file, and it is estimated that it needs to be restored through backup. Check the basic situation of the database and find that although the database is in archive mode, it has not been backed up. No wonder, after all, this database is just a local test environment.

According to the error prompt, you can go to restore the database. However, to be on the safe side, back up the entire database environment first. In this way, even if there is a problem with the recovery process, you can at least restore the database to its final state without making the problem worse, and most importantly, with this recoverable site, you can try the recovery many times.

After the backup is completed, I carefully examined the alert file of the database and found that there were a lot of problems:

Completed: ALTER DATABASE MOUNT

Thu Jun 5 14:05:10 2008

ALTER DATABASE OPEN

Thu Jun 5 14:05:11 2008

Beginning crash recovery of 1 threads

Parallel recovery started with 7 processes

Thu Jun 5 14:05:11 2008

Started redo scan

Thu Jun 5 14:05:11 2008

Completed redo scan

23473 redo blocks read, 49 data blocks need recovery

Thu Jun 5 14:05:11 2008

Started redo application at

Thread 1: logseq 525, block 2, scn 6533560108

Thu Jun 5 14:05:11 2008

Recovery of Online Redo Log: Thread 1 Group 2 Seq 525 Reading mem 0

Mem# 0: / data/oradata/test08/redo02.log

Thu Jun 5 14:05:11 2008

RECOVERY OF THREAD 1 STUCK AT BLOCK 21 OF FILE 2

Thu Jun 5 14:05:11 2008

RECOVERY OF THREAD 1 STUCK AT BLOCK 1669 OF FILE 2

Thu Jun 5 14:05:11 2008

RECOVERY OF THREAD 1 STUCK AT BLOCK 813 OF FILE 2

Thu Jun 5 14:05:11 2008

Completed redo application

Thu Jun 5 14:05:11 2008

Hex dump of (file 3, block 21009) in trace file / opt/ora10g/admin/test08/bdump/test08_p000_4369.trc

Corrupt block relative dba: 0x00c05211 (file 3, block 21009)

Fractured block found during crash/instance recovery

Data in bad block:

Type: 6 format: 2 rdba: 0x00c05211

Last change scn: 0x0001.8570a63d seq: 0x2 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0xe8520602

Check value in block header: 0xf307

Computed block checksum: 0xe6c

Reread of rdba: 0x00c05211 (file 3, block 21009) found same corrupted data

Thu Jun 5 14:05:15 2008

Errors in file / opt/ora10g/admin/test08/bdump/test08_p002_4373.trc:

ORA-01172: recovery of thread 1 stuck at block 813 of file 2

ORA-01151: use media recovery to recover block, restore backup if needed

Thu Jun 5 14:05:15 2008

Errors in file / opt/ora10g/admin/test08/bdump/test08_p001_4371.trc:

ORA-10388: parallel query server interrupt (failure)

Thu Jun 5 14:05:15 2008

Errors in file / opt/ora10g/admin/test08/bdump/test08_p004_4377.trc:

ORA-10388: parallel query server interrupt (failure)

Thu Jun 5 14:05:15 2008

Errors in file / opt/ora10g/admin/test08/bdump/test08_p005_4379.trc:

ORA-10388: parallel query server interrupt (failure)

Thu Jun 5 14:05:15 2008

Errors in file / opt/ora10g/admin/test08/bdump/test08_p005_4379.trc:

ORA-01578: ORACLE data block corrupted (file # 1, block # 1895)

ORA-01110: data file 1:'/ data/oradata/test08/system01.dbf'

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1:'/ data/oradata/test08/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 482

ORA-10388: parallel query server interrupt (failure)

Thu Jun 5 14:05:15 2008

Aborting crash recovery due to slave death, attempting serial crash recovery

Thu Jun 5 14:05:15 2008

Beginning crash recovery of 1 threads

Thu Jun 5 14:05:15 2008

Started redo scan

Thu Jun 5 14:05:15 2008

Completed redo scan

23473 redo blocks read, 49 data blocks need recovery

Thu Jun 5 14:05:15 2008

Started redo application at

Thread 1: logseq 525, block 2, scn 6533560108

Thu Jun 5 14:05:15 2008

Recovery of Online Redo Log: Thread 1 Group 2 Seq 525 Reading mem 0

Mem# 0: / data/oradata/test08/redo02.log

Hex dump of (file 3, block 21009) in trace file / opt/ora10g/admin/test08/udump/test08_ora_4367.trc

Corrupt block relative dba: 0x00c05211 (file 3, block 21009)

Fractured block found during crash/instance recovery

Data in bad block:

Type: 6 format: 2 rdba: 0x00c05211

Last change scn: 0x0001.8570a63d seq: 0x2 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0xe8520602

Check value in block header: 0xf307

Computed block checksum: 0xe6c

Reread of rdba: 0x00c05211 (file 3, block 21009) found same corrupted data

RECOVERY OF THREAD 1 STUCK AT BLOCK 1669 OF FILE 2

Thu Jun 5 14:05:15 2008

Aborting crash recovery due to error 1172

Thu Jun 5 14:05:15 2008

Errors in file / opt/ora10g/admin/test08/udump/test08_ora_4367.trc:

ORA-01172: recovery of thread 1 stuck at block 1669 of file 2

ORA-01151: use media recovery to recover block, restore backup if needed

ORA-1172 signalled during: ALTER DATABASE OPEN...

Originally, when I saw that the problem was in the UNDO tablespace, I felt that the problem was not too serious, because if it was a UNDO tablespace, after all, I could try to force RESETLOG to open the database through implicit parameters. Although some data would be lost and the database would be in an inconsistent state, the database could be opened. Because it is a test database, it is acceptable to lose some data and inconsistent database state.

But judging from the alert file, there are also bad blocks in the data file of SYSTEM tablespace. It seems that the problem is not so simple.

Let's take a chance:

SQL > recover database

Media recovery complete.

This step is quite smooth. Let's see if we can open it directly. But I doubt whether this problem can be solved so easily:

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

As expected, the problem was not solved so easily. An ORA-3113 error occurred during the database opening process to see what happened in the alert file:

ALTER DATABASE RECOVER database

Thu Jun 5 15:33:16 2008

Media Recovery Start

Parallel recovery started with 7 processes

Thu Jun 5 15:33:17 2008

Recovery of Online Redo Log: Thread 1 Group 2 Seq 525 Reading mem 0

Mem# 0: / data/oradata/test08/redo02.log

Thu Jun 5 15:33:18 2008

Hex dump of (file 2, block 1095) in trace file / opt/ora10g/admin/test08/bdump/test08_p004_6330.trc

Corrupt block relative dba: 0x00800447 (file 2, block 1095)

Fractured block found during media recovery

Data in bad block:

Type: 2 format: 2 rdba: 0x00800447

Last change scn: 0x0001.81e8f3ea seq: 0x1 flg: 0x04

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x3f380250

Check value in block header: 0x43df

Computed block checksum: 0x696b

Reread of rdba: 0x00800447 (file 2, block 1095) found same corrupted data

Thu Jun 5 15:33:18 2008

Hex dump of (file 3, block 4417) in trace file / opt/ora10g/admin/test08/bdump/test08_p000_6322.trc

Corrupt block relative dba: 0x00c01141 (file 3, block 4417)

Fractured block found during media recovery

Data in bad block:

Type: 6 format: 2 rdba: 0x00c01141

Last change scn: 0x0001.856e4319 seq: 0x1 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x87540601

Check value in block header: 0x4a80

Computed block checksum: 0x3564

Reread of rdba: 0x00c01141 (file 3, block 4417) found same corrupted data

.

.

.

Hex dump of (file 3, block 36842) in trace file / opt/ora10g/admin/test08/bdump/test08_p006_6334.trc

Corrupt block relative dba: 0x00c08fea (file 3, block 36842)

Fractured block found during media recovery

Data in bad block:

Type: 6 format: 2 rdba: 0x00c08fea

Last change scn: 0x0001.856e421e seq: 0x1 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0xef620601

Check value in block header: 0x7673

Computed block checksum: 0xac7e

Reread of rdba: 0x00c08fea (file 3, block 36842) found same corrupted data

Thu Jun 5 15:33:20 2008

Media Recovery Complete (test08)

Thu Jun 5 15:33:21 2008

Completed: ALTER DATABASE RECOVER database

Thu Jun 5 15:33:30 2008

Alter database open

Thu Jun 5 15:33:31 2008

Beginning crash recovery of 1 threads

Parallel recovery started with 7 processes

Thu Jun 5 15:33:31 2008

Started redo scan

Thu Jun 5 15:33:31 2008

Completed redo scan

23473 redo blocks read, 0 data blocks need recovery

Thu Jun 5 15:33:31 2008

Started redo application at

Thread 1: logseq 525, block 2, scn 6533560108

Thu Jun 5 15:33:31 2008

Recovery of Online Redo Log: Thread 1 Group 2 Seq 525 Reading mem 0

Mem# 0: / data/oradata/test08/redo02.log

Thu Jun 5 15:33:31 2008

Completed redo application

Thu Jun 5 15:33:31 2008

Completed crash recovery at

Thread 1: logseq 525, block 23475, scn 6533584244

0 data blocks read, 0 data blocks written, 23473 redo blocks read

Thu Jun 5 15:33:31 2008

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=23, OS id=6336

Thu Jun 5 15:33:31 2008

ARC0: Archival started

ARC1 started with pid=24, OS id=6338

Thu Jun 5 15:33:31 2008

ARC1: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

Thread 1 advanced to log sequence 526

Thread 1 opened at log sequence 526

Current log# 3 seq# 526 mem# 0: / data/oradata/test08/redo03.log

Successful open of redo thread 1

Thu Jun 5 15:33:31 2008

ARC1: Becoming the'no FAL' ARCH

ARC1: Becoming the'no SRL' ARCH

Thu Jun 5 15:33:31 2008

ARC0: Becoming the heartbeat ARCH

Thu Jun 5 15:33:31 2008

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Jun 5 15:33:31 2008

SMON: enabling cache recovery

Thu Jun 5 15:33:33 2008

Errors in file / opt/ora10g/admin/test08/udump/test08_ora_5406.trc:

ORA-00600: internal error code, arguments: [2662], [1], [2238616959], [1], [2238756337], [8388637], [], []

Thu Jun 5 15:33:35 2008

Errors in file / opt/ora10g/admin/test08/udump/test08_ora_5406.trc:

ORA-00600: internal error code, arguments: [2662], [1], [2238616959], [1], [2238756337], [8388637], [], []

Thu Jun 5 15:33:35 2008

Error 600 happened during db open, shutting down database

USER: terminating instance due to error 600

Instance terminated by USER, pid = 5406

ORA-1092 signalled during: alter database open...

The error of ORA-600 [2662] occurred during the opening process. I was half relieved to see this error, because I dealt with a similar error a while ago. Since the current SCN is smaller than the SCN in the database, we can increase the current SCN by even EVENTS. For more descriptions, please refer to: ORA-600 (2662) error reproduction and resolution (1): http://yangtingkun.itpub.net/post/468/464682 and ORA-600 (2662) error reproduction and resolution (2): http://yangtingkun.itpub.net/post/468/464701

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.