ITPub博客

首页 > 数据库 > Oracle > [20150929]检查数据块.txt

[20150929]检查数据块.txt

原创 Oracle 作者:lfree 时间:2015-09-29 08:09:33 0 删除 编辑

[20150929]检查数据块.txt

--一般检查数据库的块是否存在损坏,使用dbv程序。例子:
$ dbv file=/u01/app/oracle11g/oradata/test/system01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Tue Sep 29 07:48:28 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 106240
Total Pages Processed (Data) : 67096
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 16916
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 10114
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 12114
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 319181189 (3.319181189)

--但是如果文件很大检查就不方便,速度也不快,如果是检查多个文件,要一个一个输入,也是不方便。
--虽然dbv支持start,end参数,但是人为分割编程效率不高。简单一点,可以使用rman来完成这个工作。

RMAN> VALIDATE database ;
Starting validate at 2015-09-29 07:52:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=201 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=396 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00003 name=/u01/app/oracle11g/oradata/test/undotbs01.dbf
input datafile file number=00014 name=/u01/app/oracle11g/oradata/test/tbs_ts2_01.dbf
input datafile file number=00005 name=/u01/app/oracle11g/oradata/test/example01.dbf
input datafile file number=00007 name=/u01/app/oracle11g/oradata/test/tools01.dbf
input datafile file number=00009 name=/u01/app/oracle11g/oradata/test/testmssm01.dbf
channel ORA_DISK_2: starting validation of datafile
channel ORA_DISK_2: specifying datafile(s) for validation
input datafile file number=00002 name=/u01/app/oracle11g/oradata/test/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle11g/oradata/test/rman01.dbf
input datafile file number=00008 name=/u01/app/oracle11g/oradata/test/test01.dbf
input datafile file number=00010 name=/u01/app/oracle11g/oradata/test/undotbs02.dbf
input datafile file number=00012 name=/u01/app/oracle11g/oradata/test/tools01x.dbf
channel ORA_DISK_3: starting validation of datafile
channel ORA_DISK_3: specifying datafile(s) for validation
input datafile file number=00004 name=/u01/app/oracle11g/oradata/test/users01.dbf
input datafile file number=00013 name=/u01/app/oracle11g/oradata/test/tbs_ts1_01.dbf
input datafile file number=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:02:39
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            108544          13204083927
  File Name: /u01/app/oracle11g/oradata/test/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              108543
..... snap...

channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              1232
channel ORA_DISK_3: validation complete, elapsed time: 00:03:17
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12114        106242          13204083927
  File Name: /u01/app/oracle11g/oradata/test/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              67096
  Index      0              16916
  Other      0              10114

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              8175         147456          13203948833
  File Name: /u01/app/oracle11g/oradata/test/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              135887
  Index      0              1944
  Other      0              1450

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
13   OK     0              5889         6400            12695454522
  File Name: /u01/app/oracle11g/oradata/test/tbs_ts1_01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              376
  Index      0              0
  Other      0              135

Finished validate at 2015-09-29 07:55:59

--如果仔细看,可以发现开了3个并行。

RMAN> show DEVICE type ;
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;

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


--检查单个数据文件
RMAN> VALIDATE datafile 1 ;
Starting validate at 2015-09-29 08:00:01
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=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
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_3: starting validation of datafile
channel ORA_DISK_3: 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
------------ ------ -------------- ---------------
SPFILE       OK     0              2
channel ORA_DISK_3: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              1232
channel ORA_DISK_1: validation complete, elapsed time: 00:00:18
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12114        106242          13204084470
  File Name: /u01/app/oracle11g/oradata/test/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              67096
  Index      0              16916
  Other      0              10114
Finished validate at 2015-09-29 08:00:22


--另外11g还支持section size 参数,这样可以检查通过这个参数将文件分成几个部分同时检查(在打开并行的情况下)。

RMAN> VALIDATE section size 200M datafile 1;

Starting validate at 2015-09-29 08:02:10
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=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
validating blocks 1 through 25600
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_3: starting validation of datafile
channel ORA_DISK_3: specifying datafile(s) for validation
including current control file for validation
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
validating blocks 25601 through 51200
channel ORA_DISK_2: validation complete, elapsed time: 00:00:01
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
input datafile file number=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
validating blocks 51201 through 76800
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
validating blocks 76801 through 102400
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
channel ORA_DISK_3: validation complete, elapsed time: 00:00:02
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              1232
channel ORA_DISK_3: starting validation of datafile
channel ORA_DISK_3: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oracle11g/oradata/test/system01.dbf
validating blocks 102401 through 106240
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
channel ORA_DISK_2: validation complete, elapsed time: 00:00:01
channel ORA_DISK_3: validation complete, elapsed time: 00:00:00
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12114        106238          13204085021
  File Name: /u01/app/oracle11g/oradata/test/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              67096
  Index      0              16916
  Other      0              10110

Finished validate at 2015-09-29 08:02:16

--注意看~.
--使用rman的VALIDATE简单快捷不少。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1811265/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6294822