酷酷娱乐网

站内广告

[20190718]12c坏块处理一例.txt

时间:2020-09-15 18:00:02   作者:酷酷娱乐网   来源:www.kukuyl.com  
内容摘要:


[20190718]12c坏块处理一例.txt


--//今天做一些测试时遇到system表空间出现......

[20190718]12c坏块处理一例.txt

--//今天做一些测试时遇到system表空间出现一个坏块。自己分析处理看看。

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0

RMAN> backup database format '/tmp/backup/full20190718_%U';
Starting backup at 2019-07-18 21:17:32
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
input datafile file number=00005 name=D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF
input datafile file number=00007 name=D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 2019-07-18 21:17:34
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/18/2019 21:17:41
ORA-19566: exceeded limit of 0 corrupt blocks for file D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF
input datafile file number=00010 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF
input datafile file number=00008 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF
input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF
input datafile file number=00036 name=D:\APP\ORACLE\ORADATA\TEST\TEST01P\LFREE05X.DBF
channel ORA_DISK_1: starting piece 1 at 2019-07-18 21:17:42
channel ORA_DISK_1: finished piece 1 at 2019-07-18 21:18:17
piece handle=D:\TMP\BACKUP\FULL20190718_1OU707FM_1_1 tag=TAG20190718T211733 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF
input datafile file number=00006 name=D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 2019-07-18 21:18:17
channel ORA_DISK_1: finished piece 1 at 2019-07-18 21:18:52
piece handle=D:\TMP\BACKUP\FULL20190718_1PU707GP_1_1 tag=TAG20190718T211733 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/18/2019 21:17:41
ORA-19566: exceeded limit of 0 corrupt blocks for file D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
--//cdb数据库数据文件D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF备份出现问题。

2.dbv分析看看:
d:\>dbv file=D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
DBVERIFY: Release 12.2.0.1.0 - Production on Thu Jul 18 21:19:55 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
Page 186 is marked corrupt
Corrupt block relative dba: 0x004000ba (file 1, block 186)
Bad check value found during dbv:
Data in bad block:
 type: 0 format: 2 rdba: 0x000000ba
 last change scn: 0x0000.0000.00000000 seq: 0x1 flg: 0x05
 spare3: 0x0
 consistency value in tail: 0x00000001
 check value in block header: 0xa7ba
 computed block checksum: 0x5c2d
--//视乎检查和不一致。

DBVERIFY - Verification complete
Total Pages Examined         : 115200
Total Pages Processed (Data) : 78206
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 14791
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4576
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17626
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 9263501 (0.9263501)

SYS@test> @ which_obj 1 186
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
----- ------------ -------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS   I_TS#                       INDEX        SYSTEM                   0          1        184      65536          8            1

--//TS#的索引i_ts$损坏,什么会这样?

BBED> set dba 1,187
        DBA             0x004000bb (4194491 1,187)
--//注:我使用windows下的bbed,数据块存在+1的偏移。

BBED> dump /v count 12
 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1)
 Block: 187                               Offsets:    0 to   11                            Dba:0x004000bb
-----------------------------------------------------------------------------------------------------------
 00a20000 ba000000 00000000                                              l .?.?......
<32 bytes per line>        
--//还是1个空块,前面是00a2.注如果有数据的块前面应该是06a2.
BBED> dump /v dba 1,186  count 12
 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1)
 Block: 186                               Offsets:    0 to   11                            Dba:0x004000ba
-----------------------------------------------------------------------------------------------------------
 06a20000 b9004000 ae6f0000                                              l .?.?@.畂..
<32 bytes per line>
----
BBED> set dba 1,187
        DBA             0x004000bb (4194491 1,187)

BBED> sum apply
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 1, Block 187:
current = 0xfb97, required = 0xfb97

BBED> verify
DBVERIFY - Verification starting
FILE = D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
BLOCK = 186

DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 1
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

3.再次验证:
RMAN> validate datafile 1;
Starting validate at 2019-07-18 21:38:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17627        115200          9270549
  File Name: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              78206
  Index      0              14791
  Other      0              4576

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              1148
Finished validate at 2019-07-18 21:38:39
--//OK,应该修复完成。

2.我估计我可能某个时间执行错误,修改了dba=1,186的数据块。
BBED> info
 File#  Name                                             Size(blks)
 -----  ----                                             ----------
     1  D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF                   0
     2  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSTEM01.DBF           0
     3  D:\APP\ORACLE\ORADATA\TEST\SYSAUX01.DBF                   0
     4  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\SYSAUX01.DBF           0
     5  D:\APP\ORACLE\ORADATA\TEST\UNDOTBS01.DBF                  0
     6  D:\APP\ORACLE\ORADATA\TEST\PDBSEED\UNDOTBS01.DBF          0
     7  D:\APP\ORACLE\ORADATA\TEST\USERS01.DBF                    0
     8  D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF           0
     9  D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF           0
    10  D:\APP\ORACLE\ORADATA\TEST\TEST01P\UNDOTBS01.DBF          0
    11  D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF            0
    28  D:\APP\ORACLE\ORADATA\TEST\TEST02P\SYSTEM01.DBF           0
    29  D:\APP\ORACLE\ORADATA\TEST\TEST02P\SYSAUX01.DBF           0
    30  D:\APP\ORACLE\ORADATA\TEST\TEST02P\UNDOTBS01.DBF          0
    31  D:\APP\ORACLE\ORADATA\TEST\TEST02P\USERS01.DBF            0

--//猜测最大的可能就是本来想修改dba=11,186块不小心修改了dba=1,186.

SCOTT@test01p> @ which_obj 11 186
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
----- ------------ -------------- ------------ ------------------------- ---------- ---------- ---------- ---------- ------------
SCOTT T1                          TABLE        USERS                   1         11        184      65536          8           11

--//虽然是测试环境,做这些修改操作一定要小心。突然想起bbed的修改有log.bbd记录看看里面的内容看看:
--//看到如下记录:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Dec 10 20:27:51 2018

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************
....
--//时间太久了,回忆不起来当时的一些细节了。
set dba 11,186
set dba 1,187
~~~~~~~~~~~~~~~~
map /v
p offset 36
dump /v offset 36 count 4
dump /v offset 264
m /x 12 offset 36
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 36.
m /x 12 offset 264
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 264.
m /x 11 offset 48
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 48.
m /x 58 offset 52
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 52.
m /x 80 offset 56
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 56.
m /x 11 offset 92
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 92.
m /x 58 offset 96
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 96.
m /x 80 offset 100
#modify /x 00 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 100.
m /x 5802 offset 60
#modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 60.
m /x c002 offset 62
#modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 62.
m /x 5802 offset 104
#modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 104.
m /x c200 offset 106
#modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 106.
m /x 2f5e offset 272
#modify /x 0000 filename 'D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF' block 187. offset 272.
set dba 11,187
--//可以发现在下划线处我输入错误,导致后续的操作错误的数据块。

BBED> set dba 1,187
        DBA             0x004000bb (4194491 1,187)

--//选择offset=36,272,106,验证看看:
BBED> dump  offset 36 count 2
 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1)
 Block: 187                Offsets:   36 to   37       Dba:0x004000bb
---------------------------------------------------------------------
 1200

 <64 bytes per line>

BBED> dump  offset 272 count 2
 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1)
 Block: 187                Offsets:  272 to  273      Dba:0x004000bb
---------------------------------------------------------------------
 2f5e
 <64 bytes per line>

BBED> dump offset 106 count 2
 File: D:\APP\ORACLE\ORADATA\TEST\SYSTEM01.DBF (1)
 Block: 187                Offsets:  106 to  107      Dba:0x004000bb
---------------------------------------------------------------------
 c200
 <64 bytes per line>
--//可以发现当时确实操作错误!!看来做这些修复工作一定要小心小心再小心!!

--//我看了笔记最大可能自己在家里做truncate恢复时操作。
--//上班后测试链接如下,可以发现偏移基本可以对上。
[20181212]truncate的另类恢复5.txt->http://blog.itpub.net/267265/viewspace-2284984/

本文章url:https://www.kukuyl.com/new/30000596.html

相关文章

CopyRight 2018 - 2020 http://www.kukuyl.com 酷酷娱乐网 All Rights Reserved .