首页 » ORACLE 9i-23ai » How to fixed oracle table block corrupted have dead transaction

How to fixed oracle table block corrupted have dead transaction

  • 2020/01/18
  • ORACLE 9i-23ai
  • 139 views
  • How to fixed oracle table block corrupted have dead transaction已关闭评论

如何修复有事务的表上发生了坏块， 此时undo 中的事务会一直active， 无法清理，简单记录如何手动清理

---模拟
SQL> create table test.t1 as select * from dba_objects where rownum<10; Table created. SQL> alter system checkpoint;
System altered.
 
SQL> @rowid_scan test.t1 1=1

    RFILE#     BLOCK#       ROW#        DBA DUMP_COMMAND
---------- ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         4        299          0  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          1  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          2  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          3  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          4  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          5  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          6  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          7  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .
         4        299          8  0x100012B alter system dump datafile 4 block 299; -- @dump 4 299 .

9 rows selected.


SQL> @ls users

TABLESPACE_NAME                   FILE_ID FILE_NAME                                                                        EXT         MB      MAXSZ
------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ----------
USERS                                   4 /oradata/test/users01.dbf                                                        YES          5   32767.98
USERS2                                  5 /oradata/test/users2_1.dbf                                                       NO          10
USERS2                                  6 /oradata/test/users2_2.dbf                                                       NO          10

SQL> delete from test.t1;
9 rows deleted.

SQL> alter system checkpoint;
System altered.

SQL> shutdown abort
ORACLE instance shut down.

Note:
做了事务以后做了检查点，直接abort

使用bbed 把数据块标记成坏块

BBED> set dba 4,299
        DBA             0x0100012b (16777515 4,299)

BBED> m /x ff offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oradata/test/users01.dbf (4)
 Block: 299              Offsets:   14 to  525           Dba:0x0100012b
------------------------------------------------------------------------
 ff047098 00000100 00005155 0100b096 10000000 00000300 32002801 0001ffff

启动实例查看db alert log

Wed Jun 02 15:54:50 2021
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1181021834
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Wed Jun 02 15:54:54 2021
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 221 KB redo, 70 data blocks need recovery
Started redo application at
 Thread 1: logseq 12, block 3
Recovery of Online Redo Log: Thread 1 Group 3 Seq 12 Reading mem 0
  Mem# 0: /oradata/test/redo03.log
Completed redo application of 0.11MB
Completed crash recovery at
 Thread 1: logseq 12, block 446, scn 1148300
 70 data blocks read, 70 data blocks written, 221 redo k-bytes read
Thread 1 advanced to log sequence 13 (thread open)
Thread 1 opened at log sequence 13
  Current log# 1 seq# 13 mem# 0: /oradata/test/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[2759] Successfully onlined Undo Tablespace 5.
Undo initialization finished serial:0 start:544684 end:544704 diff:20 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Hex dump of (file 4, block 299) in trace file /oracle/app/oracle/diag/rdbms/orcl11g/test/trace/test_smon_2743.trc
Corrupt block relative dba: 0x0100012b (file 4, block 299)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0100012b
 last change scn: 0x0000.001096db seq: 0xff flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x96db0609
 check value in block header: 0x9886
 computed block checksum: 0x0Database Characterset is ZHS16GBK
Reading datafile '/oradata/test/users01.dbf' for corruption at rdba: 0x0100012b (file 4, block 299)
Reread (file 4, block 299) found same corrupt data (no logical check)

Errors in file /oracle/app/oracle/diag/rdbms/orcl11g/test/trace/test_smon_2743.trc  (incident=10909):
ORA-01578: ORACLE data block corrupted (file # 4, block # 299)
ORA-01110: data file 4: '/oradata/test/users01.dbf'
Incident details in: /oracle/app/oracle/diag/rdbms/orcl11g/test/incident/incdir_10909/test_smon_2743_i10909.trc
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jun 02 15:54:54 2021
QMNC started with pid=20, OS id=2765
Completed: ALTER DATABASE OPEN
Starting background process CJQ0
ORACLE Instance test (pid = 13) - Error 1578 encountered while recovering transaction (3, 2) on object 87377. <<<<<
Errors in file /oracle/app/oracle/diag/rdbms/orcl11g/test/trace/test_smon_2743.trc:
ORA-01578: ORACLE data block corrupted (file # 4, block # 299)
ORA-01110: data file 4: '/oradata/test/users01.dbf'
Wed Jun 02 15:54:55 2021
CJQ0 started with pid=21, OS id=2777
Starting background process SMCO
Wed Jun 02 15:54:56 2021
SMCO started with pid=22, OS id=2779
Dumping diagnostic data in directory=[cdmp_20210602155456], requested by (instance=1, osid=2743 (SMON)), summary=[incident=10909].
Wed Jun 02 15:54:56 2021
Sweep [inc][10909]: completed
Hex dump of (file 4, block 299) in trace file /oracle/app/oracle/diag/rdbms/orcl11g/test/incident/incdir_10909/test_m000_2781_i10909_a.trc
Corrupt block relative dba: 0x0100012b (file 4, block 299)
Fractured block found during validation
Data in bad block:
 type: 6 format: 2 rdba: 0x0100012b
 last change scn: 0x0000.001096db seq: 0xff flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x96db0609
 check value in block header: 0x9886
 computed block checksum: 0x0
Reread of blocknum=299, file=/oradata/test/users01.dbf. found same corrupt data
Reread of blocknum=299, file=/oradata/test/users01.dbf. found same corrupt data
Reread of blocknum=299, file=/oradata/test/users01.dbf. found same corrupt data
Reread of blocknum=299, file=/oradata/test/users01.dbf. found same corrupt data
Reread of blocknum=299, file=/oradata/test/users01.dbf. found same corrupt data
Wed Jun 02 15:55:06 2021
drop tablespace undotbs1
ORA-1548 signalled during: drop tablespace undotbs1...

提示发现坏块，清理undo

--切UNDO

SQL> create undo tablespace undotbs2 datafile '/oradata/test/undotbs02.dbf' size 50m;
Tablespace created.

SQL> alte database set undo_tablespace=undotbs2 

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> startup force;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  459304960 bytes
Fixed Size                  2254224 bytes
Variable Size             197134960 bytes
Database Buffers          255852544 bytes
Redo Buffers                4063232 bytes
Database mounted.
Database opened.

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU3_1723003836$' found, terminate dropping tablespace

SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU3_1723003836$' scope=spfile;

System altered.

SQL> startup force;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  459304960 bytes
Fixed Size                  2254224 bytes
Variable Size             197134960 bytes
Database Buffers          255852544 bytes
Redo Buffers                4063232 bytes
Database mounted.
Database opened.
SQL>

SQL> drop tablespace undotbs1;
Tablespace dropped.

如果您遇到了坏块无法修复，需要帮助，可联系 www.anbob.com 上联系方式
— enjoy —

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：