搜索
首页数据库mysql教程基于rman的坏块恢复

转载请注明出处 http://blog.csdn.net/guoyjoe/article/details/30965303 实验步骤如下: 1、使用rman备份全库 Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jun 8 15:30:35 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All

转载请注明出处 http://blog.csdn.net/guoyjoe/article/details/30965303

实验步骤如下:

1、使用rman备份全库

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jun 8 15:30:35 2014

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

connected to target database: PROD (DBID=254815294)

RMAN> backup database;

Starting backup at 08-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=142 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=00001 name=/u01/app/oracle/oradata/PROD/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD/tp01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JUN-14
channel ORA_DISK_1: finished piece 1 at 08-JUN-14
piece handle=/u01/app/oracle/product/11.2.0/dbs/01pab691_1_1 tag=TAG20140608T153040 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
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 08-JUN-14
channel ORA_DISK_1: finished piece 1 at 08-JUN-14
piece handle=/u01/app/oracle/product/11.2.0/dbs/02pab69q_1_1 tag=TAG20140608T153040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JUN-14 

(1)针对gyj_t1表的20869号块做测试,现在可以查出记录 

gyj@PROD> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1;

        ID NAME       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ---------- ------------------------------------ ------------------------------------
         1 oracledba                                     5                                20869
[root@jfdb ~]# su - oracle
[oracle@jfdb ~]$ bbed parfile=par.txt
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Jun 8 15:33:10 2014

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 5   block 20869
        FILE#           5
        BLOCK#          20869

BBED> map /v
 File: /u01/app/oracle/oradata/PROD/tp01.dbf (5)
 Block: 20869                                 Dba:0x01405185
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub1 spare1_kcbh                         @2       
    ub1 spare2_kcbh                         @3       
    ub4 rdba_kcbh                           @4       
    ub4 bas_kcbh                            @8       
    ub2 wrp_kcbh                            @12      
    ub1 seq_kcbh                            @14      
    ub1 flg_kcbh                            @15      
    ub2 chkval_kcbh                         @16      
    ub2 spare3_kcbh                         @18      

 struct ktbbh, 72 bytes                     @20      
    ub1 ktbbhtyp                            @20      
    union ktbbhsid, 4 bytes                 @24      
    struct ktbbhcsc, 8 bytes                @28      
    sb2 ktbbhict                            @36      
    ub1 ktbbhflg                            @38      
    ub1 ktbbhfsl                            @39      
    ub4 ktbbhfnx                            @40      
    struct ktbbhitl[2], 48 bytes            @44      

 struct kdbh, 14 bytes                      @100     
    ub1 kdbhflag                            @100     
    sb1 kdbhntab                            @101     
    sb2 kdbhnrow                            @102     
    sb2 kdbhfrre                            @104     
    sb2 kdbhfsbo                            @106     
    sb2 kdbhfseo                            @108     
    sb2 kdbhavsp                            @110     
    sb2 kdbhtosp                            @112     

 struct kdbt[1], 4 bytes                    @114     
    sb2 kdbtoffs                            @114     
    sb2 kdbtnrow                            @116     

 sb2 kdbr[1]                                @118     

 ub1 freespace[8038]                        @120     

 ub1 rowdata[30]                            @8158    

 ub4 tailchk                                @8188    


BBED> p kcbh
struct kcbh, 20 bytes                       @0       
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x01405185
   ub4 bas_kcbh                             @8        0x00176fed
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xc140
   ub2 spare3_kcbh                          @18       0x0000

BBED> m /x ff offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/app/oracle/oradata/PROD/tp01.dbf (5)
 Block: 20869            Offsets:   14 to  525           Dba:0x01405185
------------------------------------------------------------------------
 ff0640c1 00000100 00003945 0000eb6f 17000000 00000200 32008051 40010700 
 20008b02 00002c49 c0002301 02000080 0000876d 17000300 1a007503 0000bf1f 
 c0002501 0c000120 0000ed6f 17000000 00000000 00000001 0100ffff 14007a1f 
 741f741f 00000100 7a1f0000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 5, Block 20869:
current = 0xc1be, required = 0xc1be
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle/oradata/PROD/tp01.dbf
BLOCK = 20869

Block 20869 is corrupt
Corrupt block relative dba: 0x01405185 (file 0, block 20869)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x01405185
 last change scn: 0x0000.00176fed seq: 0xff flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x6fed0601
 check value in block header: 0xc1be
 computed block checksum: 0x0


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            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED

(4)确定不能查gyj_t1表的记录
gyj@PROD> alter system flush buffer_cache;

System altered.

gyj@PROD> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1;
select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 20869)
ORA-01110: data file 5: &#39;/u01/app/oracle/oradata/PROD/tp01.dbf&#39;
RMAN> backup validate datafile 5;

Starting backup at 08-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD/tp01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              114          64000           1659211   
  File Name: /u01/app/oracle/oradata/PROD/tp01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              58883           
  Index      0              4221            
  Other      0              782             

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_2750.trc for details
Finished backup at 08-JUN-14
4、查5号文件的坏块
gyj@PROD> select FILE#,BLOCK#,BLOCKS,CORRUPTION_CHANGE#,CORRUPTION_TYPE from v$database_block_corruption where file#=5;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         5      20869          1                  0 FRACTURED

5、使用rman恢复坏块 

RMAN> blockrecover datafile 5 block  20869;

Starting recover at 08-JUN-14
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbs/01pab691_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/01pab691_1_1 tag=TAG20140608T153040
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 08-JUN-14
gyj@PROD> select FILE#,BLOCK#,BLOCKS,CORRUPTION_CHANGE#,CORRUPTION_TYPE from v$database_block_corruption where file#=5;

no rows selected
gyj@PROD> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1;

        ID NAME       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ---------- ------------------------------------ ------------------------------------
         1 oracledba                                     5                                20869
声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
MySQL中有什么触发器?MySQL中有什么触发器?Apr 23, 2025 am 12:11 AM

MySQL触发器是与表相关联的自动执行的存储过程,用于在特定数据操作时执行一系列操作。1)触发器定义与作用:用于数据校验、日志记录等。2)工作原理:分为BEFORE和AFTER,支持行级触发。3)使用示例:可用于记录薪资变更或更新库存。4)调试技巧:使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。5)性能优化:避免复杂操作,使用索引,管理事务。

您如何在MySQL中创建和管理用户帐户?您如何在MySQL中创建和管理用户帐户?Apr 22, 2025 pm 06:05 PM

在MySQL中创建和管理用户账户的步骤如下:1.创建用户:使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配权限:使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正权限错误:使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然后重新分配权限;4.优化权限:使用SHOWGRA

MySQL与Oracle有何不同?MySQL与Oracle有何不同?Apr 22, 2025 pm 05:57 PM

MySQL适合快速开发和中小型应用,Oracle适合大型企业和高可用性需求。1)MySQL开源、易用,适用于Web应用和中小型企业。2)Oracle功能强大,适合大型企业和政府机构。3)MySQL支持多种存储引擎,Oracle提供丰富的企业级功能。

与其他关系数据库相比,使用MySQL的缺点是什么?与其他关系数据库相比,使用MySQL的缺点是什么?Apr 22, 2025 pm 05:49 PM

MySQL相比其他关系型数据库的劣势包括:1.性能问题:在处理大规模数据时可能遇到瓶颈,PostgreSQL在复杂查询和大数据处理上表现更优。2.扩展性:水平扩展能力不如GoogleSpanner和AmazonAurora。3.功能限制:在高级功能上不如PostgreSQL和Oracle,某些功能需要更多自定义代码和维护。

您如何在MySQL中执行加入操作?您如何在MySQL中执行加入操作?Apr 22, 2025 pm 05:41 PM

MySQL支持四种JOIN类型:INNERJOIN、LEFTJOIN、RIGHTJOIN和FULLOUTERJOIN。1.INNERJOIN用于匹配两个表中的行并返回符合条件的结果。2.LEFTJOIN返回左表的所有行,即使右表没有匹配。3.RIGHTJOIN与LEFTJOIN相反,返回右表的所有行。4.FULLOUTERJOIN返回两表中所有符合或不符合条件的行。

MySQL的性能与高负载下的其他RDBM相比如何?MySQL的性能与高负载下的其他RDBM相比如何?Apr 22, 2025 pm 05:37 PM

MySQL在高负载下的性能与其他RDBMS相比各有优劣。1)MySQL通过InnoDB引擎和优化策略如索引、查询缓存和分区表在高负载下表现良好。2)PostgreSQL通过MVCC机制提供高效并发读写,Oracle和MicrosoftSQLServer则通过各自的优化策略提升性能。通过合理的配置和优化,MySQL可以在高负载环境中表现出色。

解释InnoDB缓冲池及其对性能的重要性。解释InnoDB缓冲池及其对性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通过缓存数据和索引页来减少磁盘I/O,提升数据库性能。其工作原理包括:1.数据读取:从BufferPool中读取数据;2.数据写入:修改数据后写入BufferPool并定期刷新到磁盘;3.缓存管理:使用LRU算法管理缓存页;4.预读机制:提前加载相邻数据页。通过调整BufferPool大小和使用多个实例,可以优化数据库性能。

MySQL与其他编程语言:一种比较MySQL与其他编程语言:一种比较Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

功能强大的PHP集成开发环境

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )专业的PHP集成开发工具

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具