Validating Database Files and Backups

简介:

                            Validating Database Files and Backups


一 验证的目的
1 检测坏块和丢失的文件
2 判断备份是否能够恢复

二 验证命令
1 validate
2 backup....validate
3 restore ......validate

三 使用validate 命令检测坏块

physical corruption and logical corruption 区别
physical corruption:
     被称为介质损坏
    数据库不识别所有快
    数据块校验和无效
    块头和块尾不匹配
logical corruption:
      数据块的内容逻辑上不一致,比如row piece 和索引条目。如果rman探测到逻辑上的坏块,oracle记录 坏块到告警文件和服务器会话跟踪文件。

   注意:
a  默认backup命令为每一个块产生校验和,如果指定nochecksum选项,rman创建备份时,不会为块产生校验和。
b  默认rman不检测数据块逻辑上的损坏,除非在backup命令中指定 check logical参数。 rman测试数据和索引块以寻找逻辑上的损坏,
   发现之后记录到告警文件。
   
四 使用 VALIDATE 命令检测坏块
1 validate命令能干啥?
手动检测数据库文件,物理和逻辑上的损坏 。

2 注意事项
该命令和backup validate 执行相同的检测,不过该命令可以指定某些大数据文件进行检测

3 使用方式
a 验证所有控制文件,数据文件,参数文件
  validate database;
b 验证某个备份集
  validate backupset 22;
c 检测数据文件中的某个块
 validate datafile 1  block 10; 
 validate datafile 4 block 10 to 13;
d 验证归档
 validate archivelog all;
 validate archivelog sequence between 82 and 94;
 
4 数据文件的并行化验证
rman提供了对大数据文件的并行处理方法,即把大数据文件分成几个file section并行处理,并配置和分配多个通道处理这些file section
实现方式是运行validate 命令时指定section size 参数
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
validate datafile 1 section size 1200M;
}

五 使用backup validate命令验证数据库文件
1 backup validate 能干啥?
a 检验数据文件物理和逻辑上的坏块
b 确保所有的数据文件位于正确的位置
2 注意事项
a 该命令不能和参数backupset、maxcorrupt、proxy联合使用。
b 该命令执行过程不产生备份集或者镜像copy 仅仅是读取之前的数据库备份文件。
3 使用方式
a 物理上对数据文件和归档日志文件备份进行坏块验证
 backup validate database archivelog all;
b 逻辑上对数据文件和归档日志文件备份进行验证。
 backup validate check logical database archivelog all;
c 对归档进行物理上坏块的验证
backup validate archivelog all;
d 对归档进行逻辑上坏块的验证
backup validate check logical archivelog all;

六使用RESTORE ... VALIDATE命令再恢复之前验证备份
 1 该命令能干啥
 验证rman能否从备份中恢复一个特定文件或者一组文件
 
 2 注意事项
 a 数据库必须open 或者mount
 b 发起该命令前数据文件不能offline
 
 3 使用方式
restore spfile  validate;
restore controlfile validate;
restore database validate;
restore archivelog all validate;
restore archivelog sequence nn validate 
restore archivelog sequence  between nn and nn validate;
七 restore ....preview命令
1 该命令用于列出恢复所需要的文件列表,但是该命令不读取这些备份文件,以确认是否能恢复. 
2 命令restore database preview;
 

八 实验
  1 列出当前数据库进行恢复所需要的文件列表
  
RMAN> restore database preview;

Starting restore at 2012-09-13 20:01:45
using channel ORA_DISK_1
using channel ORA_DISK_2


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
379     Full    1.02G      DISK        00:03:16     2012-09-13 19:59:20
        BP Key: 379   Status: AVAILABLE  Compressed: NO  Tag: TAG20120913T195604
        Piece Name: /backup/20120913_ebnl4bil_1_1
  List of Datafiles in backup set 379
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 2505766    2012-09-13 19:56:05 /oracle/CRM/system01.dbf
  2       Full 2505766    2012-09-13 19:56:05 /oracle/CRM/sysaux01.dbf
  5       Full 2505766    2012-09-13 19:56:05 /oracle/CRM/pos.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
377     Full    1.46G      DISK        00:02:55     2012-09-13 19:56:14
        BP Key: 377   Status: AVAILABLE  Compressed: NO  Tag: TAG20120913T195604
        Piece Name: /backup/20120913_eanl4bil_1_1
  List of Datafiles in backup set 377
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  3       Full 2505765    2012-09-13 19:56:05 /oracle/CRM/undotbs01.dbf
  4       Full 2505765    2012-09-13 19:56:05 /backup/users01.dbf
  6       Full 2505765    2012-09-13 19:56:05 /oracle/CRM/erp.dbf
  10      Full 2505765    2012-09-13 19:56:05 /oracle/CRM/undotbs02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
378     Full    1.10M      DISK        00:00:09     2012-09-13 19:59:17
        BP Key: 378   Status: AVAILABLE  Compressed: NO  Tag: TAG20120913T195604
        Piece Name: /backup/20120913_ecnl4bof_1_1
  List of Datafiles in backup set 378
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  7       Full 2505832    2012-09-13 19:59:12 /oracle/CRM/user01.dbf


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
380     7.50K      DISK        00:00:00     2012-09-13 19:59:28
        BP Key: 380   Status: AVAILABLE  Compressed: NO  Tag: TAG20120913T195928
        Piece Name: /backup/20120913_ednl4bp0_1_1

  List of Archived Logs in backup set 380
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    81      2505738    2012-09-13 19:55:18 2505842    2012-09-13 19:59:27
List of Archived Log Copies for database with db_unique_name CRM
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
427     1    82      A 2012-09-13 19:59:27
        Name: /oracle/archive/1_82_791488634.dbf

428     1    83      A 2012-09-13 20:01:13
        Name: /oracle/archive/1_83_791488634.dbf

429     1    84      A 2012-09-13 20:01:16
        Name: /oracle/archive/1_84_791488634.dbf

430     1    85      A 2012-09-13 20:01:19
        Name: /oracle/archive/1_85_791488634.dbf

431     1    86      A 2012-09-13 20:01:22
        Name: /oracle/archive/1_86_791488634.dbf

432     1    87      A 2012-09-13 20:01:22
        Name: /oracle/archive/1_87_791488634.dbf

433     1    88      A 2012-09-13 20:01:23
        Name: /oracle/archive/1_88_791488634.dbf

434     1    89      A 2012-09-13 20:01:23
        Name: /oracle/archive/1_89_791488634.dbf

435     1    90      A 2012-09-13 20:01:24
        Name: /oracle/archive/1_90_791488634.dbf

436     1    91      A 2012-09-13 20:01:25
        Name: /oracle/archive/1_91_791488634.dbf

437     1    92      A 2012-09-13 20:01:25
        Name: /oracle/archive/1_92_791488634.dbf

Media recovery start SCN is 2505765
Recovery must be done beyond SCN 2505832 to clear datafile fuzziness
Finished restore at 2012-09-13 20:01:45

  2 对当前数据文件物理和逻辑上的是否有坏块进行验证
  RMAN> validate database; 

Starting validate at 2012-09-13 20:02:35
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/oracle/CRM/system01.dbf
input datafile file number=00002 name=/oracle/CRM/sysaux01.dbf
input datafile file number=00005 name=/oracle/CRM/pos.dbf
channel ORA_DISK_2: starting validation of datafile
channel ORA_DISK_2: specifying datafile(s) for validation
input datafile file number=00004 name=/backup/users01.dbf
input datafile file number=00003 name=/oracle/CRM/undotbs01.dbf
input datafile file number=00006 name=/oracle/CRM/erp.dbf
input datafile file number=00010 name=/oracle/CRM/undotbs02.dbf
channel ORA_DISK_2: validation complete, elapsed time: 00:01:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              2            12810           2382873   
  File Name: /oracle/CRM/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              12798          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              4623         223046          2291202   
  File Name: /backup/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              147962          
  Index      0              36144           
  Other      0              34311          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              2            12801           2291966   
  File Name: /oracle/CRM/erp.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              12798          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10   OK     0              2            12805           2506060   
  File Name: /oracle/CRM/undotbs02.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              12798          

channel ORA_DISK_2: starting validation of datafile
channel ORA_DISK_2: specifying datafile(s) for validation
input datafile file number=00007 name=/oracle/CRM/user01.dbf
channel ORA_DISK_2: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              58           641             1910641   
  File Name: /oracle/CRM/user01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5               
  Index      0              0               
  Other      0              577            

channel ORA_DISK_2: starting validation of datafile
channel ORA_DISK_2: specifying datafile(s) for validation
including current SPFILE in backup set
channel ORA_DISK_2: validation complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
channel ORA_DISK_2: starting validation of datafile
channel ORA_DISK_2: specifying datafile(s) for validation
including current control file for validation
channel ORA_DISK_2: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              664             
channel ORA_DISK_1: validation complete, elapsed time: 00:01:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              14114        96058           2506060   
  File Name: /oracle/CRM/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              61712           
  Index      0              13154           
  Other      0              7020           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              17015        69165           2506002   
  File Name: /oracle/CRM/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              12067           
  Index      0              8199            
  Other      0              31839          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              63999        64000           0         
  File Name: /oracle/CRM/pos.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              1              

Finished validate at 2012-09-13 20:04:21

RMAN> validate archivelog sequence between 82 and 94;

Starting validate at 2012-09-13 20:43:20
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting validation of archived log
channel ORA_DISK_1: specifying archived log(s) for validation
input archived log thread=1 sequence=82 RECID=427 STAMP=793915273
input archived log thread=1 sequence=83 RECID=428 STAMP=793915276
input archived log thread=1 sequence=84 RECID=429 STAMP=793915280
input archived log thread=1 sequence=85 RECID=430 STAMP=793915282
input archived log thread=1 sequence=86 RECID=431 STAMP=793915282
input archived log thread=1 sequence=87 RECID=432 STAMP=793915283
input archived log thread=1 sequence=88 RECID=433 STAMP=793915284
channel ORA_DISK_2: starting validation of archived log
channel ORA_DISK_2: specifying archived log(s) for validation
input archived log thread=1 sequence=89 RECID=434 STAMP=793915284
input archived log thread=1 sequence=90 RECID=435 STAMP=793915285
input archived log thread=1 sequence=91 RECID=436 STAMP=793915285
input archived log thread=1 sequence=92 RECID=437 STAMP=793915288
input archived log thread=1 sequence=93 RECID=438 STAMP=793916449
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    82      OK     0              11              /oracle/archive/1_82_791488634.dbf
1    83      OK     0              1               /oracle/archive/1_83_791488634.dbf
1    84      OK     0              2               /oracle/archive/1_84_791488634.dbf
1    85      OK     0              2               /oracle/archive/1_85_791488634.dbf
1    86      OK     0              1               /oracle/archive/1_86_791488634.dbf
1    87      OK     0              1               /oracle/archive/1_87_791488634.dbf
1    88      OK     0              1               /oracle/archive/1_88_791488634.dbf
channel ORA_DISK_1: starting validation of archived log
channel ORA_DISK_1: specifying archived log(s) for validation
input archived log thread=1 sequence=94 RECID=439 STAMP=793916489
channel ORA_DISK_2: validation complete, elapsed time: 00:00:01
List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    89      OK     0              8               /oracle/archive/1_89_791488634.dbf
1    90      OK     0              1               /oracle/archive/1_90_791488634.dbf
1    91      OK     0              1               /oracle/archive/1_91_791488634.dbf
1    92      OK     0              2               /oracle/archive/1_92_791488634.dbf
1    93      OK     0              211             /oracle/archive/1_93_791488634.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
List of Archived Logs
=====================
Thrd Seq     Status Blocks Failing Blocks Examined Name
---- ------- ------ -------------- --------------- ---------------
1    94      OK     0              8               /oracle/archive/1_94_791488634.dbf
Finished validate at 2012-09-13 20:43:21

  3 验证这些文件能否进行有效恢复

RMAN> restore database validate;

Starting restore at 2012-09-13 20:41:08
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_2: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /backup/20120913_eanl4bil_1_1
channel ORA_DISK_2: reading from backup piece /backup/20120913_ecnl4bof_1_1
channel ORA_DISK_2: piece handle=/backup/20120913_ecnl4bof_1_1 tag=TAG20120913T195604
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: validation complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting validation of datafile backup set
channel ORA_DISK_2: reading from backup piece /backup/20120913_ebnl4bil_1_1
channel ORA_DISK_1: piece handle=/backup/20120913_eanl4bil_1_1 tag=TAG20120913T195604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:57
channel ORA_DISK_2: piece handle=/backup/20120913_ebnl4bil_1_1 tag=TAG20120913T195604
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: validation complete, elapsed time: 00:00:56
Finished restore at 2012-09-13 20:42:05


RMAN> restore archivelog sequence between 84 and 94 validate;

Starting restore at 2012-09-13 20:39:09
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: scanning archived log /oracle/archive/1_84_791488634.dbf
channel ORA_DISK_2: scanning archived log /oracle/archive/1_85_791488634.dbf
channel ORA_DISK_1: scanning archived log /oracle/archive/1_86_791488634.dbf
channel ORA_DISK_2: scanning archived log /oracle/archive/1_87_791488634.dbf
channel ORA_DISK_1: scanning archived log /oracle/archive/1_88_791488634.dbf
channel ORA_DISK_1: scanning archived log /oracle/archive/1_89_791488634.dbf
channel ORA_DISK_2: scanning archived log /oracle/archive/1_90_791488634.dbf
channel ORA_DISK_1: scanning archived log /oracle/archive/1_91_791488634.dbf
channel ORA_DISK_2: scanning archived log /oracle/archive/1_92_791488634.dbf
channel ORA_DISK_1: scanning archived log /oracle/archive/1_93_791488634.dbf
channel ORA_DISK_2: scanning archived log /oracle/archive/1_94_791488634.dbf
Finished restore at 2012-09-13 20:39:11
  
 ---------------上内容参考oracle备份恢复用户指导-------------










本文转自 zhangxuwl 51CTO博客,原文链接:http://blog.51cto.com/jiujian/990112,如需转载请自行联系原作者
目录
相关文章
|
存储 算法 Python
Crystallographic Information File
Crystallographic Information File(CIF)是一种用于存储晶体结构信息的标准文件格式,通常用于存储X射线衍射、中子衍射、电子衍射等晶体结构分析的数据。CIF文件包含了晶胞参数、原子坐标、晶体对称性等结构信息,是进行晶体结构分析和制备的基础数据。
513 1
|
Oracle 关系型数据库
Creating ASMSNMP User reports ORA-15306
In this Document  Symptoms   Cause   Solution Applies to: Oracle Server - Enterprise Edition - Version: 11.
1365 0
Creating a CDB Using Oracle Managed Files
Creating a CDB Using Oracle Managed Files
110 0
|
SQL 监控 Go
backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.
昨天在检查YourSQLDba备份时,发现有台数据库做备份时出现了下面错误信息,如下所示:   yMaint.ShrinkLog   Log Shrink   --  ============================================================...
1096 0
|
NoSQL 关系型数据库 PHP
|
关系型数据库 MySQL Shell
Database Restoration Solutions
Alibaba Cloud ApsaraDB is an on-demand database service that helps you cope with constantly changing business requirements, and frees you up from database administrative and management tasks.
1410 0