年归档：2022

oracle启动报ORA-600 kdBlkCheckError故障解决

发表于 2022 年 9 月 17 日 由 惜分飞

数据库启动报ORA-600 kdBlkCheckError错误

SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [144], [38504]
进程 ID: 17516
会话 ID: 14 序列号: 5

根据ORA-600 kdBlkCheckError的经验,这个错误是3号文件的144号block逻辑不一致导致.通过dbv检查该文件

Microsoft Windows [版本 10.0.19044.1949]
(c) Microsoft Corporation。保留所有权利。

C:\Users\XFF>dbv file=H:\BaiduNetdisk\oradata\XFF\UNDOTBS1.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 9月 17 10:51:32 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\ORADATA\SPECTRA\UNDOTBS1.DBF
页 112 失败, 校验代码为 18018
Block Checking: DBA = 12583056, Block Type = System Managed Segment Header Block
ERROR: SMU Segment Header Corrupted.  Error Code = 38504
ktu4smck: SCN commited txn list is not sorted.
  previous txn slot=23, scn=0x0000.ee917d05
  offending txn slot=18, scn=0x0000.ee916272
  TRN CTL:: seq: 0x0c3f chd: 0x0017 ctl: 0x0018 inc: 0x00000000 nfb: 0x0001
            mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00c087c8.0c3f.05 scn: 0x0000.ee9160d2
            Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00c087c8.0c3f.05  ext: 0xe  spc: 0x1594
    uba: 0x00000000.0bfe.03  ext: 0x12 spc: 0x1eb8
    uba: 0x00000000.0b20.04  ext: 0x4  spc: 0x1d2e
    uba: 0x00000000.c6e5.01  ext: 0x2  spc: 0x1f84
    uba: 0x00000000.0000.00  ext: 0x0  spc: 0x0
  TRN TBL::
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub       bcl     cmt
  -----------------------------------------------------------------------------------------
   0x00  9 0x00  0x62878b  0xffe4  0x0000.ee917f13  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031369
   0x01 10 0x80  0x62887e  0x001f  0x0000.ee917efe  0x00c087d6  0x0000.000.00000000  0x00000001 0x00000000  38
   0x02  9 0x00  0x628871  0xffde  0x0000.ee917cce  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031373
   0x03  9 0x00  0x62865f  0x0000  0x0000.ee916279  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x04  9 0x00  0x628823  0x0011  0x0000.ee916083  0x00c087d4  0x0000.000.00000000  0x00000001 0x00000000  1663031371
   0x05  9 0x00  0x6287b3  0x0000  0x0000.ee917e56  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x06  9 0x00  0x628893  0x001c  0x0000.ee916288  0x00002338  0x0000.000.00000000  0x00000000 0x00000000  1663031371
   0x07  9 0x00  0x628820  0x0011  0x0000.ee917f66  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031373
   0x08  9 0x00  0x628833  0x000c  0x0000.ee917eaf  0x00c087d4  0x0000.000.00000000  0x00000001 0x00000000  1663031371
   0x09  9 0x00  0x628815  0x0000  0x0000.ee917e72  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x0a  9 0x00  0x628863  0x0002  0x0000.ee917cc1  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031373
   0x0b  9 0x00  0x62870c  0x0008  0x0000.ee916085  0x00c087d4  0x0000.000.00000000  0x00000001 0x00000000  1663031372
   0x0c  9 0x00  0x62881a  0x0005  0x0000.ee917ff2  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x0d  9 0x00  0x6289fb  0x000f  0x0000.ee917d1d  0x00c087d4  0x0000.000.00000000  0x00000001 0x00000000  1663031375
   0x0e  9 0x00  0x6287d8  0x000a  0x0000.ee91638a  0x00c087d6  0x0000.000.00000000  0x00000001 0x00000000  1663031371
   0x0f  9 0x00  0x62880c  0x001b  0x0000.ee91619e  0x00003003  0x0000.000.00000000  0x00000000 0x00000000  1663031370
   0x10  9 0x00  0x6287e6  0x0013  0x0000.ee9161fc  0x00c087d5  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x11  9 0x00  0x62863b  0x0019  0x0000.ee916354  0x00c087d5  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x12  9 0x00  0x6287d4  0x0010  0x0000.ee916272  0x00c087d6  0x0000.000.00000000  0x00000001 0x00000000  1663031371
   0x13  9 0x00  0x628470  0x0007  0x0000.ee9160b2  0x00c087d5  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x14  9 0x00  0x6287a4  0x001e  0x0000.ee91627f  0x00c087d6  0x0000.000.00000000  0x00000001 0x00000000  1663031372
   0x15  9 0x00  0x628797  0x000a  0x0000.ee9162bb  0x00c087c9  0x0000.000.00000000  0x00000001 0x00000000  1663031368
   0x16  9 0x00  0x6287ad  0x0005  0x0000.ee917f6c  0x00c087d4  0x0000.000.00000000  0x00000001 0x00000000  1663031371
   0x17  9 0x00  0x6287b5  0x0012  0x0000.ee917d05  0x00c087d7  0x0000.000.00000000  0x00000001 0x00000000  1663031373
   0x18  9 0x00  0x628719  0x000b  0x0000.ee916136  0x00c087d4  0x0000.000.00000000  0x00000001 0x00000000  1663031373
   0x19  9 0x00  0x628783  0x0006  0x0000.ee916363  0x00c087d5  0x0000.000.00000000  0x00000001 0x00000000  1663031370
   0x1a  9 0x00  0x6287d8  0xffff  0x0000.ee917d97  0x00c087cb  0x0000.000.00000000  0x00000001 0x00000000  1663031375
   0x1b  9 0x00  0x6287d7  0x0022  0x0000.ee916043  0x00c087d5  0x0000.000.00000000  0x00000001 0x00000000  1663031373
   0x1c  9 0x00  0x62880e  0x0005  0x0000.ee917db7  0x00002338  0x0000.000.00000000  0x00000000 0x00000000  1663031373
   0x1d  9 0x00  0x6287b7  0x0003  0x0000.ee9161e1  0x00c087d4  0x0000.000.00000000  0x00000001 0x00000000  1663031373
   0x1e  9 0x00  0x6287f6  0x0015  0x0000.ee9162e6  0x00002338  0x0000.000.00000000  0x00000000 0x00000000  1663031368
   0x1f  9 0x00  0x6287ad  0x0003  0x0000.ee917eae  0x00003003  0x0000.000.00000000  0x00000000 0x00000000  1663031372
   0x20  9 0x00  0x6287b0  0x0003  0x0000.ee9163a5  0x0000133b  0x0000.000.00000000  0x00000000 0x00000000  1663031368
   0x21  9 0x00  0x62886a  0x0001  0x0000.ee916056  0x00c087d5  0x0000.000.00000000  0x00000001 0x00000000  1663031373
  EXT TRN CTL::
  usn: 2
  sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
  sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
  EXT TRN TBL::
index extflag  extHash  extSpare1  extSpare2
---------------------------------------------
   0x00  0x00000000 0x00000000 0x00000000  0x00000000
   0x01  0x00000000 0x00000000 0x00000000  0x00000000
   0x02  0x00000000 0x00000000 0x00000000  0x00000000
   0x03  0x00000000 0x00000000 0x00000000  0x00000000
   0x04  0x00000000 0x00000000 0x00000000  0x00000000
   0x05  0x00000000 0x00000000 0x00000000  0x00000000
   0x06  0x00000000 0x00000000 0x00000000  0x00000000
   0x07  0x00000000 0x00000000 0x00000000  0x00000000
   0x08  0x00000000 0x00000000 0x00000000  0x00000000
   0x09  0x00000000 0x00000000 0x00000000  0x00000000
   0x0a  0x00000000 0x00000000 0x00000000  0x00000000
   0x0b  0x00000000 0x00000000 0x00000000  0x00000000
   0x0c  0x00000000 0x00000000 0x00000000  0x00000000
   0x0d  0x00000000 0x00000000 0x00000000  0x00000000
   0x0e  0x00000000 0x00000000 0x00000000  0x00000000
   0x0f  0x00000000 0x00000000 0x00000000  0x00000000
   0x10  0x00000000 0x00000000 0x00000000  0x00000000
   0x11  0x00000000 0x00000000 0x00000000  0x00000000
   0x12  0x00000000 0x00000000 0x00000000  0x00000000
   0x13  0x00000000 0x00000000 0x00000000  0x00000000
   0x14  0x00000000 0x00000000 0x00000000  0x00000000
   0x15  0x00000000 0x00000000 0x00000000  0x00000000
   0x16  0x00000000 0x00000000 0x00000000  0x00000000
   0x17  0x00000000 0x00000000 0x00000000  0x00000000
   0x18  0x00000000 0x00000000 0x00000000  0x00000000
   0x19  0x00000000 0x00000000 0x00000000  0x00000000
   0x1a  0x00000000 0x00000000 0x00000000  0x00000000
   0x1b  0x00000000 0x00000000 0x00000000  0x00000000
   0x1c  0x00000000 0x00000000 0x00000000  0x00000000
   0x1d  0x00000000 0x00000000 0x00000000  0x00000000
   0x1e  0x00000000 0x00000000 0x00000000  0x00000000
   0x1f  0x00000000 0x00000000 0x00000000  0x00000000
   0x20  0x00000000 0x00000000 0x00000000  0x00000000
   0x21  0x00000000 0x00000000 0x00000000  0x00000000
页 144 失败, 校验代码为 38504

…………

DBVERIFY - 验证完成

检查的页总数: 161280
处理的页总数 (数据): 0
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 161277
处理的总页数 (段)  : 9
失败的总页数 (段)  : 0
空的页总数: 1
标记为损坏的总页数: 4
流入的页总数: 2
加密的总页数        : 0
最高块 SCN            : 4002695098 (0.4002695098)

C:\Users\XFF>

可以确认是由于SMU Segment Header异常，导致数据库无法正常启动，通过数据库层面设置,规避数据库启动访问该block,数据库正常启动正常,并顺利导出数据

Thu Sep 15 11:02:23 2022
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 2863639551
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Thu Sep 15 11:02:31 2022
alter database open upgrade
Thread 1 opened at log sequence 660107
  Current log# 2 seq# 660107 mem# 0: H:\BAIDUNETDISK\ORADATA\XFF\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Undo initialization finished serial:0 start:74439375 end:74439375 diff:0 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Completed: alter database open
发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

Oracle Recovery Tools快速解决sysaux文件不能online问题

发表于 2022 年 8 月 29 日 由 惜分飞

又一客户sysaux表空间对应的数据文件离线(file 6 为测试表空间数据可以不要)

Tue Jul 26 11:33:41 2022
alter database datafile 2 offline drop
Completed: alter database datafile 2 offline drop
Tue Jul 26 11:35:26 2022
alter database datafile 6 offline drop
Completed: alter database datafile 6 offline drop
Tue Jul 26 11:36:04 2022
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 19 processes
Started redo scan
Completed redo scan
 read 14595 KB redo, 954 data blocks need recovery
Started redo application at
 Thread 1: logseq 52560, block 31365
Recovery of Online Redo Log: Thread 1 Group 3 Seq 52560 Reading mem 0
  Mem# 0: D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG
Completed redo application of 6.50MB
Completed crash recovery at
 Thread 1: logseq 52560, block 60555, scn 4397986801
 954 data blocks read, 954 data blocks written, 14595 redo k-bytes read
Tue Jul 26 11:36:11 2022
Thread 1 advanced to log sequence 52561 (thread open)
Thread 1 opened at log sequence 52561
  Current log# 1 seq# 52561 mem# 0: D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jul 26 11:36:11 2022
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Tue Jul 26 11:36:14 2022
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file d:\XXXX\datas\diag\rdbms\XXXX\XXXX\trace\XXXX_ora_8476.trc  (incident=275156):
ORA-25319: 队列表重新分区已中止
Incident details in: d:\XXXX\datas\diag\rdbms\XXXX\XXXX\incident\incdir_275156\XXXX_ora_8476_i275156.trc
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Tue Jul 26 11:36:23 2022
QMNC started with pid=50, OS id=11200 
Tue Jul 26 11:36:23 2022
Trace dumping is performing id=[cdmp_20220726113623]
 XDB UNINITIALIZED: XDB$SCHEMA not accessible 
Tue Jul 26 11:36:27 2022
Completed: ALTER DATABASE OPEN

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 OFFLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 OFFLINE

7月份offline datafile 2,然后open数据库一直运行至今,数据库一直无法进行备份,需要我们进行解决

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            D:\APP\DATAS\product\11.2.0.4\dbhome_1\RDBMS
最早的联机日志序列     55557
当前日志序列           55559

SQL> recover datafile 2;
ORA-00279: 更改 4397905894 (在 07/25/2022 18:26:58 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\XXXX\DATAS\FLASH_RECOVERY_AREA\XXXX\ARCHIVELOG\2022_08_29\O1_MF_1_52560_%U_.ARC
ORA-00280: 更改 4397905894 (用于线程 1) 在序列 #52560 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

数据库为非归档,常规方法无法直接online datafile 2,对于这样的情况,使用Oracle Recovery Tools工具,进行快速修改文件头信息

20220829214608
20220829214902

查询文件头信息

SQL> set pages 1000
SQL> set linesize 150
SQL> select ts#,file#,TABLESPACE_NAME,status,
  2  to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,
  3  to_char(checkpoint_change#,'9999999999999999') "SCN",
  4  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
  5  from v$datafile_header;

       TS#      FILE# TABLESPACE_NAME                STATUS  CREATE_TIME         SCN               RESETLOGS SCN     FUZ

---------- ---------- ------------------------------ ------- ------------------- ----------------- ----------------- ---

         0          1 SYSTEM                         ONLINE  2010-03-30 10:07:48        4599488977            947455 NO
         1          2 SYSAUX                         ONLINE  2010-03-30 10:07:52        4599488977            947455 YES
         2          3 UNDOTBS1                       ONLINE  2010-03-30 11:07:21        4599488977            947455 NO
         4          4 USERS                          ONLINE  2010-03-30 10:08:04        4599488977            947455 NO
         6          5 XXXX                           ONLINE  2020-05-29 09:45:48        4599488977            947455 NO

并且尝试online datafile 2

SQL> recover datafile 2;
ORA-00283: 恢复会话因错误而取消
ORA-01122: 数据库文件 2 验证失败
ORA-01110: 数据文件 2: 'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF'
ORA-01207: 文件比控制文件更新 - 旧的控制文件

由于ctl中的关于datafile2 的信息没有更新，因此数据文件的信息比ctl中的新,无法正常recover,需要重建ctl

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 1.3195E+10 bytes
Fixed Size                  2188168 bytes
Variable Size            1.0301E+10 bytes
Database Buffers         2885681152 bytes
Redo Buffers                5738496 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XXXX" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2336
  7  LOGFILE
  8    GROUP 1 'D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\XXXX\DATAS\ORADATA\XXXX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\XXXX\DATAS\ORADATA\XXXX\SYSTEM01.DBF',
 13    'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF',
 14    'D:\XXXX\DATAS\ORADATA\XXXX\UNDOTBS01.DBF',
 15    'D:\XXXX\DATAS\ORADATA\XXXX\USERS01.DBF',
 16    'D:\XXXX\DATAS\ZYSPACE\XXXX.DBF',
 17    'E:\XXXX\DATAS\BACKUP\XXXXX.DBF'
 18  CHARACTER SET ZHS16GBK
 19  ;
CREATE CONTROLFILE REUSE DATABASE "XXXX" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01229: ???? 2 ??????
ORA-01110: ???? 2: 'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF'

由于redo中信息也不对,重建需要使用resetlogs方式进行

SQL> CREATE CONTROLFILE REUSE DATABASE "XXXX" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2336
  7  LOGFILE
  8    GROUP 1 'D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\XXXX\DATAS\ORADATA\XXXX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\XXXX\DATAS\ORADATA\XXXX\SYSTEM01.DBF',
 13    'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF',
 14    'D:\XXXX\DATAS\ORADATA\XXXX\UNDOTBS01.DBF',
 15    'D:\XXXX\DATAS\ORADATA\XXXX\USERS01.DBF',
 16    'D:\XXXX\DATAS\ZYSPACE\XXXX.DBF',
 17    'E:\XXXX\DATAS\BACKUP\XXXXX.DBF'
 18  CHARACTER SET ZHS16GBK
 19  ;

控制文件已创建。

后续处理

SQL> alter database datafile 6 offline drop;

数据库已更改。

SQL> recover database using backup controlfile;
ORA-00279: ?? 4599488977 (? 08/29/2022 20:59:25 ??) ???? 1 ????
ORA-00289: ??: D:\XXXX\DATAS\FLASH_RECOVERY_AREA\XXXX\ARCHIVELOG\2022_08_29\O1_MF_1_55279_%U_.ARC
ORA-00280: ?? 4599488977 (???? 1) ??? #55279 ?


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG
已应用的日志。
完成介质恢复。
SQL> alter database open resetlogs;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\XXXX\DATAS\ORADATA\XXXX\TEMP01.DBF' REUSE;

表空间已更改。

数据导出成功
20220829220338

发表在 小工具 | 标签为 , , | 评论关闭

Oracle Recovery Tools修复空闲坏块

发表于 2022 年 8 月 8 日 由 惜分飞

我们经常遇到由于某种原因,表上面有坏块,通过event或者dbms包跳过坏块,然后重建该表之后,但是dbv和rman检查坏块依旧存在(而且导致常规情况下rman无法正常备份),最近在Oracle Recovery Tools工具中增加的这种异常数据块修复功能,通过试验重现类似故障:
创建表并进行破坏

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期一 8月 8 14:00:34 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace ts_test datafile 'e:/oradata/test/ts_test.dbf' size 128M autoextend on;

表空间已创建。

SQL> create table t_xifenfei tablespace ts_test
  2  as
  3  select * from dba_objects;

表已创建。

SQL> alter system checkpoint;

系统已更改。

SQL> set pages 100
SQL> select file_id,block_id,blocks from dba_extents where segment_name='T_XIFENFEI';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         5        128          8
         5        136          8
         5        144          8
         5        152          8
         5        160          8
         5        168          8
         5        176          8
         5        184          8
         5        192          8
         5        200          8
         5        208          8
         5        216          8
         5        224          8
         5        232          8
         5        240          8
         5        248          8
         5        256        128
         5        384        128
         5        512        128
         5        640        128
         5        768        128
         5        896        128
         5       1024        128
         5       1152        128
         5       1280        128

已选择25行。


SQL> SELECT COUNT(1) FROM T_XIFENFEI;

  COUNT(1)
----------
     86048

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。


C:\Windows\system32>dd of=e:/oradata/test/ts_test.dbf  if=/dev/zero bs=1k seek=1419 conv=notrunc count=1
rawwrite dd for windows version 0.6beta3.
Written by John Newbigin <jn@it.swin.edu.au>
This program is covered by terms of the GPL Version 2.

notrunc
1+0 records in
1+0 records out


SQL> startup
ORACLE 例程已经启动。

Total System Global Area 3206836224 bytes
Fixed Size                  2285512 bytes
Variable Size             754974776 bytes
Database Buffers         2432696320 bytes
Redo Buffers               16879616 bytes
数据库装载完毕。
数据库已经打开。
SQL> select count(1) from t_xifenfei;
select count(1) from t_xifenfei
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 177)
ORA-01110: 数据文件 5: 'E:\ORADATA\TEST\TS_TEST.DBF'

跳过坏块重建该表

SQL> alter session set events '10231 trace name context forever, level 10';

会话已更改。

SQL> create table t_xifenfei_bak tablespace ts_test
  2  as select * from t_xifenfei;

表已创建。

SQL> select count(1) from t_xifenfei_bak;

  COUNT(1)
----------
     85968

SQL> drop table t_xifenfei purge;

表已删除。

SQL> rename t_xifenfei_bak to t_xifenfei;

表已重命名。

SQL> select count(1) from t_xifenfei;

  COUNT(1)
----------
     85968

检查坏块情况
通过rman和dbv检查,均表明file 5 block 177为坏块

C:\Users\XFF>dbv file=E:\ORADATA\TEST\TS_TEST.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期一 8月 8 17:25:57 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = E:\ORADATA\TEST\TS_TEST.DBF
页 177 标记为损坏
Corrupt block relative dba: 0x014000b1 (file 5, block 177)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x014000b1
 last change scn: 0x0000.000ebc27 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xbc270602
 check value in block header: 0x5b2a
 computed block checksum: 0xbb32



DBVERIFY - 验证完成

检查的页总数: 16384
处理的页总数 (数据): 2456
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 155
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 13772
标记为损坏的总页数: 1
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 967616 (0.967616)

RMAN> backup validate check logical datafile 5;

启动 backup 于 08-8月 -22
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=118 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00005 名称=E:\ORADATA\TEST\TS_TEST.DBF
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:01
数据文件列表
=================
文件状态 标记为损坏 空块 已检查的块 高 SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              13744        16384           967621
  文件名: E:\ORADATA\TEST\TS_TEST.DBF
  块类型 失败的块 已处理的块
  ---------- -------------- ----------------
  数据       1              2457
  索引      0              0
  其他      0              183

验证找到一个或多个损坏的块
有关详细信息, 请参阅跟踪文件 C:\APP\XFF\diag\rdbms\test\test\trace\test_ora_22284.trc
完成 backup 于 08-8月 -22

SQL> select * from v$database_block_corruption ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         5        177          1                  0 CHECKSUM

查询坏块所属对象
没有查询到该坏块所属对象,证明该block为游离块[不属于任何数据对象,是空闲块,但是损坏]

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
输入 file_id 的值:  5
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 5
输入 block_id 的值:  177
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 177 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

未选定行

通过Oracle Recovery Tools工具进行修复
20220808173940

再次检查坏块
通过工具修复之后,dbv和rman检查均正常

C:\Users\XFF>rman target /

恢复管理器: Release 11.2.0.4.0 - Production on 星期一 8月 8 17:59:26 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

已连接到目标数据库: TEST (DBID=2410248200)

RMAN> backup validate check logical datafile 5;

启动 backup 于 08-8月 -22
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=54 设备类型=DISK
通道 ORA_DISK_1: 正在启动全部数据文件备份集
通道 ORA_DISK_1: 正在指定备份集内的数据文件
输入数据文件: 文件号=00005 名称=E:\ORADATA\TEST\TS_TEST.DBF
通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:01
数据文件列表
=================
文件状态 标记为损坏 空块 已检查的块 高 SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              13745        16384           967621
  文件名: E:\ORADATA\TEST\TS_TEST.DBF
  块类型 失败的块 已处理的块
  ---------- -------------- ----------------
  数据       0              2456
  索引      0              0
  其他      0              183

完成 backup 于 08-8月 -22


C:\Users\XFF>dbv file=E:\ORADATA\TEST\TS_TEST.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期一 8月 8 17:56:45 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = E:\ORADATA\TEST\TS_TEST.DBF


DBVERIFY - 验证完成

检查的页总数: 16384
处理的页总数 (数据): 2456
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 183
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 13745
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 967621 (0.967621)


SQL> select * from v$database_block_corruption ;

未选定行
发表在 小工具 | 标签为 , , , , | 评论关闭