签到成功

知道了

CNDBA社区CNDBA社区

12C SYSAUX表空间坏块 索引 重建

2018-03-14 21:48 3167 0 原创 数据坏块
作者： Marvinn
原文链接：http://www.cndba.cn/Marvinn
本文链接：http://www.cndba.cn/Marvinn/article/2681

12C备份PDB 某个数据文件，数据库文件SYSAUX发现坏块http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681

RMAN> backup pluggable database pdb001 datafile 29;

Starting backup at 2018-03-14 20:50:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 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=00018 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_undotbs1_dwx0fm75_.dbf
input datafile file number=00017 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_sysaux_dwx0fm74_.dbf
input datafile file number=00016 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_system_dwx0fm6b_.dbf
input datafile file number=00020 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/test1.dbf
input datafile file number=00019 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/test.dbf
input datafile file number=00029 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
channel ORA_DISK_1: starting piece 1 at 2018-03-14 20:50:23
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/14/2018 20:50:26
ORA-19566: exceeded limit of 0 corrupt blocks for file /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_sysaux_dwx0fm74_.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=00029 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
channel ORA_DISK_1: starting piece 1 at 2018-03-14 20:50:26
channel ORA_DISK_1: finished piece 1 at 2018-03-14 20:50:27
piece handle=/u01/oracle/12.2.0/dbs/bcstpqci_1_1 tag=TAG20180314T205022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/14/2018 20:50:26
ORA-19566: exceeded limit of 0 corrupt blocks for file /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_sysaux_dwx0fm74_.dbf

DBV命令检查http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681http://www.cndba.cn/Marvinn/article/2681

oracle@12cDG:/u01/oracle/12.2.0/network/admin>dbv file=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_sysaux_dwx0fm74_.dbf

DBVERIFY: Release 12.2.0.1.0 - Production on Wed Mar 14 20:54:57 2018

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

DBVERIFY - Verification starting : FILE = /data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_sysaux_dwx0fm74_.dbf
Page 524 is marked corrupt
Corrupt block relative dba: 0x0100020c (file 4, block 524)
Bad header found during dbv: 
Data in bad block:
 type: 100 format: 2 rdba: 0x73626673
 last change scn: 0x6b61.646b.67626864 seq: 0x62 flg: 0x67
 spare3: 0x6a64
 consistency value in tail: 0x00000001
 check value in block header: 0x6b67
 computed block checksum: 0x1b77



DBVERIFY - Verification complete

Total Pages Examined         : 126720
Total Pages Processed (Data) : 23886
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 25138
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 48045
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 11878
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17772
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 544649065 (0.544649065)

从上述输出可知，当前文件是第524块存在坏块...

查看数据文件的FILE#
21:05:17 JWT@pdb>select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
        17        524          1                  0 CORRUPT            6

Elapsed: 00:00:00.00
从上可知是17号文件,块524

查看坏块是索引还是表数据
21:16:56 JWT@pdb>SELECT tablespace_name, segment_type, owner, segment_name
21:17:25   2  FROM dba_extents
21:17:25   3          WHERE file_id = &fileid
21:17:25   4  and &blockid between block_id AND block_id + blocks - 1;
Enter value for fileid: 17
old   3:         WHERE file_id = &fileid
new   3:         WHERE file_id = 17
Enter value for blockid: 524
old   4: and &blockid between block_id AND block_id + blocks - 1
new   4: and 524 between block_id AND block_id + blocks - 1

TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ------------------------------------------------------------
SYSAUX                         INDEX              SYS                            WRI$_DBU_USAGE_SAMPLE_PK

哈哈，比较幸运...是个索引，重建一个就好了..

21:21:59 > alter session set container=pdb001;

Session altered.

Elapsed: 00:00:00.07
21:22:07 SYS@orcl>alter index WRI$_DBU_USAGE_SAMPLE_PK rebuild tablespace sysaux;

Index altered.

Elapsed: 00:00:00.57

验证是否存在坏块
select * from v$database_block_corruption;
21:22:16 SYS@orcl>SELECT tablespace_name, segment_type, owner, segment_name
21:22:23   2  FROM dba_extents
21:22:23   3          WHERE file_id = &fileid
21:22:23   4  and &blockid between block_id AND block_id + blocks - 1;
Enter value for fileid: 17
old   3:         WHERE file_id = &fileid
new   3:         WHERE file_id = 17
Enter value for blockid: 524
old   4: and &blockid between block_id AND block_id + blocks - 1
new   4: and 524 between block_id AND block_id + blocks - 1

no rows selected

Elapsed: 00:00:00.11

再次备份，成功
oracle@12cDG:/u01/oracle/diag/rdbms/orcl/orcl/trace>           rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Mar 14 21:36:37 2018

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

connected to target database: ORCL (DBID=1481723203)

RMAN> backup pluggable database pdb001 datafile 29;

Starting backup at 2018-03-14 21:36:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=89 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=00018 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_undotbs1_dwx0fm75_.dbf
input datafile file number=00017 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_sysaux_dwx0fm74_.dbf
input datafile file number=00016 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/o1_mf_system_dwx0fm6b_.dbf
input datafile file number=00020 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/test1.dbf
input datafile file number=00019 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/test.dbf
input datafile file number=00029 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
channel ORA_DISK_1: starting piece 1 at 2018-03-14 21:36:53
channel ORA_DISK_1: finished piece 1 at 2018-03-14 21:37:18
piece handle=/u01/oracle/12.2.0/dbs/bdstpt3l_1_1 tag=TAG20180314T213652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00029 name=/data/datafile/ORCL/5A5847E139AF054EE05375B029ACCC94/datafile/jwt.dbf
channel ORA_DISK_1: starting piece 1 at 2018-03-14 21:37:18
channel ORA_DISK_1: finished piece 1 at 2018-03-14 21:37:19
piece handle=/u01/oracle/12.2.0/dbs/bestpt4e_1_1 tag=TAG20180314T213652 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2018-03-14 21:37:19

Starting Control File and SPFILE Autobackup at 2018-03-14 21:37:19
piece handle=/u01/oracle/12.2.0/dbs/c-1481723203-20180314-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2018-03-14 21:37:20

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Marvinn

Marvinn

关注

路漫漫其修远兮、吾将上下而求索

  • 99
    原创
  • 0
    翻译
  • 2
    转载
  • 36
    评论
  • 访问：458456次
  • 积分：449
  • 等级：中级会员
  • 排名：第12名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ