备案控制台
探索云世界
开发者社区 数据库 文章 正文

[20160704]Block recover using RMAN.txt

2016-07-04 793
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20160704]Block recover using RMAN.txt --总结一下使用rman恢复坏块. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION      ...

[20160704]Block recover using RMAN.txt

--总结一下使用rman恢复坏块.

1.环境:
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table emp2 tablespace tea as select * from emp ;
Table created.

SCOTT@book> select rowid from emp2 where rownum<=1;
ROWID
------------------
AAAXKZAAHAAAACDAAA

SCOTT@book> @ &r/rowid AAAXKZAAHAAAACDAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
     94873          7        131          0 7,131                alter system dump datafile 7 block 131 ;


2.备份:
RMAN> backup datafile 7 format '/home/oracle/backup/datafile_7_20160704.bak';
Starting backup at 2016-07-04 09:53:39
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=134 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=145 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=00007 name=/mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-07-04 09:53:40
channel ORA_DISK_1: finished piece 1 at 2016-07-04 09:53:41
piece handle=/home/oracle/backup/datafile_7_20160704.bak tag=TAG20160704T095340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-07-04 09:53:41

Starting Control File and SPFILE Autobackup at 2016-07-04 09:53:41
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_07_04/o1_mf_s_916307621_cqmjf5o4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-07-04 09:53:42

3.破坏使用dd命令:

$ dd if=/dev/null of=/mnt/ramdisk/book/tea01.dbf bs=8192 count=1 seek=131 conv=notrunc
0+0 records in
0+0 records out
0 bytes (0 B) copied, 1.5781e-05 seconds, 0.0 kB/s
--注意if使用/dev/null 不行.另外注意加conv=notrunc,不要在生产系统做这个测试.

$ dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf bs=8192 count=1 seek=131 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 3.4219e-05 seconds, 239 MB/s

SCOTT@book> select * from emp2 where rownum<=2;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
--//因为数据还在缓存中,如果清除缓存,就报错了.

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select * from emp2 where rownum<=2;
select * from emp2 where rownum<=2
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

SCOTT@book> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         7        131          1                  0 ALL ZERO

--//使用rman检查:
RMAN> validate  datafile 7;
Starting validate at 2016-07-04 10:02:24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    FAILED 0              169          2305            13243764038
  File Name: /mnt/ramdisk/book/tea01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1924
  Index      0              0
  Other      1              211

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_59407.trc for details
Finished validate at 2016-07-04 10:02:25

$ dbv file=/mnt/ramdisk/book/tea01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Jul 4 10:03:19 2016

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

DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/tea01.dbf
Page 131 is marked corrupt
Corrupt block relative dba: 0x01c00083 (file 7, block 131)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined         : 2304
Total Pages Processed (Data) : 1924
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 210
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 169
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 358862150 (3.358862150)

4.使用rman修复:
RMAN> blockrecover datafile 7 block 131;

Starting recover at 2016-07-04 10:04:24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/datafile_7_20160704.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/datafile_7_20160704.bak tag=TAG20160704T095340
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2016-07-04 10:04:26

SCOTT@book> select * from emp2 where rownum<=2;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30


--如果坏块很多,可以使用blockrecover corruption list;
--重复测试:
$ dd if=/dev/zero of=/mnt/ramdisk/book/tea01.dbf bs=8192 count=1 seek=131 conv=notrunc
--//注意一个细节,dbv可以检查文件,但是错误不会出现视图v$database_block_corruption;
--//同样select * from emp2 where rownum<=2;报ORA-01578,有时候也不出现在视图v$database_block_corruption中.
SCOTT@book> select * from emp2 where rownum<=2;
select * from emp2 where rownum<=2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 131)
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

SCOTT@book> select * from v$database_block_corruption;
no rows selected

--//仅仅在rman下使用validate  datafile 7;或者validate  database或者validate tablespace才可以.
RMAN> validate tablespace tea;
Starting validate at 2016-07-04 10:09:50
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    FAILED 0              169          2305            13243764038
  File Name: /mnt/ramdisk/book/tea01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1924
  Index      0              0
  Other      1              211

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_59407.trc for details
Finished validate at 2016-07-04 10:09:51

SCOTT@book> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         7        131          1                  0 ALL ZERO


--//这个测试blockrecover corruption list;忽略.

SCOTT@book> @ &r/convrdba 7 131
RDBA16               RDBA
-------------- ----------
       1c00083   29360259

--还可以直接使用rdba地址.
--recover datafile 7 29360259; 不行
--recover tablespace tea dba 0x1c00083 ; 不行,必须使用十进制数.

RMAN> recover tablespace tea dba 29360259 ;
Starting recover at 2016-07-04 10:15:15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/datafile_7_20160704.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/datafile_7_20160704.bak tag=TAG20160704T095340
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2016-07-04 10:15:18

SCOTT@book> select * from v$database_block_corruption;
no rows selected

SCOTT@book> select * from emp2 where rownum<=2;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

--实际上还可以这样写:
RMAN> recover datafile 7 block 131;
Starting recover at 2016-07-04 10:18:01
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/datafile_7_20160704.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/datafile_7_20160704.bak tag=TAG20160704T095340
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
--//感觉这个更加人性化.

5.总结:
blockrecover datafile &file# block &block#
blockrecover corruption list;
recover tablespace tea dba 29360259;
recover datafile &file# block &block#

--//注意一个问题就是仅仅在rman下使用命令validate ,有问题的块才会记录在v$database_block_corruption视图中.
--//以此做一个全面总结.

文章标签：
关系型数据库
Oracle
数据库管理
Linux
缓存
lfreeali
目录
相关文章
玄学酱
9.2. dump / restore
玄学酱
1058 0
玄学酱
76.2. dump / restore
玄学酱
1108 0
lfreeali
|
Oracle 关系型数据库 Linux
[20171208]rman与truncate3.txt
[20171208]rman与truncate3.txt --//前几天测试truncate表依旧备份一部分信息,测试几次确定备份8extent.当时的测试几个extents是相邻的.
lfreeali
874 0
lfreeali
|
Oracle 关系型数据库 Linux
[20171206]rman与truncate2.txt
[20171206]rman与truncate2.txt --//上午测试发现truncate的表在做rman备份时还要做8个extents的备份. --//不知道自己的猜测是否正确,选择一个使用UNIFORM SIZE 的表空间测试看看.
lfreeali
1050 0
lfreeali
|
Oracle 关系型数据库 Shell
[20171121]rman backup as copy 2.txt
[20171121]rman backup as copy 2.txt --//昨天测试backup as copy ,备份时备份文件的文件头什么时候更新.是最后完成后还是顺序写入备份文件.
lfreeali
993 0
lfreeali
|
Oracle 关系型数据库 Linux
[20171121]rman backup as copy.txt
[20171121]rman backup as copy.txt --//上个星期做数据文件块头恢复时,提到使用rman备份数据文件时,文件头数据库信息是最后写入备份集文件的,在filesperset=1的情况 --//下写入备份集文件中的倒数第2块就是文件头的备份.
lfreeali
1201 0
科技探索者
|
关系型数据库 内存技术 Oracle
ora-16038:日志无法归档 ora-00312 ORA-19809: limit exceeded for recovery files ora-19804
科技探索者
1089 0
科技小能手
|
Oracle 关系型数据库 数据库管理
Fractured block found during backing up datafile
科技小能手
1276 0
技术小胖子
|
Oracle 关系型数据库 开发工具
利用dbms_backup_restore函数来恢复数据文件
技术小胖子
1139 0
lfreeali
|
Oracle 关系型数据库 数据库
[20170616]recover copy of datafile 6.txt
[20170616]no copy of datafile 6 found to recover.txt --//最近几天一直被这个问题纠缠,我虽然不知道问题在哪来,还是找到简单的解决方法,做1个记录: --//链接:http://www.
lfreeali
1127 0

热门文章

最新文章

  • 1
    Linux查看进程的内存占用情况
  • 2
    大咖云集，技术宅开趴倒计时 —— 2017 Kubernetes Meetup | 成都站
  • 3
    ubuntu安装KVM虚拟机管理virt-manager
  • 4
    OpenCV4之C++入门详解 （上）
  • 5
    vue2中computed中无法获取到this
  • 6
    Visual Basic快速入门
  • 7
    HDU 4968 Improving the GPA
  • 8
    这56家公司共同发力智慧城市
  • 9
    书写高质量JavaScript代码的要义（The Essentials of Writing High Quality JavaScript）翻译
  • 10
    linux驱动开发--字符设备：信号量
  • 1
    【软件工程】融通未来的工艺：深度解析统一过程在软件开发中的角色
    13
  • 2
    IBM SPSS Modeler分类决策树C5.0模型分析空气污染物数据
    13
  • 3
    【软件工程】走进瀑布模型：传统软件开发的经典之路
    13
  • 4
    数据分享|R语言用lme4多层次（混合效应）广义线性模型（GLM），逻辑回归分析教育留级调查数据（下）
    11
  • 5
    【软件工程】走近演化过程模型：软件开发的不断进化之路
    84
  • 6
    【Mybatis】深入学习MyBatis：概述、主要特性以及配置与映射
    13
  • 7
    【MySQL】数据库规范化的三大法则 — 一探范式设计原则
    15
  • 8
    r语言中对LASSO回归，Ridge岭回归和弹性网络Elastic Net模型实现（下）
    12
  • 9
    【MySQL】数据库中为什么使用B+树不用B树
    8
  • 10
    【MySQL】SQL优化
    10

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）