[20150929]檢查資料塊.txt

lfree發表於2015-09-29

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章