When you see ORA-01578, which indicate corrupted blocks in alert.log file and trace file got generated.
When you migrate, move datafiles or copy files from one server to another it’s always good practice to run db verify utility and make sure that there would not be any block corruption in the datafile wrapper.
Most of the time our database consist many data files layout on the file system, the best way I run is create a shell script and fire it.
In this exercise I will show you how to create script and run it on unix platform.
Running db verify utility and setting up block checksum parameter for oracle database
Step 1: Connect to the database
[oracle@affpora01 backup]$ sqlplus /'as sysdba'
SQL*Plus: Release 9.2.0.6.0 - Production on Thu May 29 15:12:37 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production
Setup 2: Setting up your environment
SQL> set heading off
SQL> set pagesize 90
SQL> set feedback off
SQL> spool dbverify.sh
Note:
Generate db verify scrip for execution, remember you can use logfile=logfilename.log but It won’t be get appended and over written that’s why I didn’t use it in my script. Make sure you mentioned full path of dbv utility if you are running through scrip, like in my case.
Step 3: Create dbv syntax using sql select statement .
SQL> select 'dbv file='||NAME||' blocksize='||BLOCK_SIZE||';' from v$datafile;
dbv file=/oradata/tables/orcl/system01.dbf blocksize=8192
dbv file=/oradata/undo/orcl/undotbs01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/cwmlite01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/drsys01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/example01.dbf blocksize=8192
dbv file=/oradata/indexes/orcl/indx01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/odm01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/tools01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/users01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/xdb01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/mv01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/fast01.dbf blocksize=8192
dbv file=/oradata/tables/orcl/therm0191.dbf blocksize=8192
dbv file=/oradata/indexes/orcl/indx201.dbf blocksize=8192
dbv file=/oradata/indexes/orcl/mv02.dbf blocksize=8192
SQL> spool off
SQL> Exit;
Step 4: Now open unix file. and delete the all lines starting with SQL>. and save the file.
$ vi dbverify.sh
dbv file=/oradata/tables/orcl/system01.dbf blocksize=8192;
dbv file=/oradata/undo/orcl/undotbs01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/cwmlite01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/drsys01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/example01.dbf blocksize=8192;
dbv file=/oradata/indexes/orcl/indx01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/odm01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/tools01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/users01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/xdb01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/mv01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/fast01.dbf blocksize=8192;
dbv file=/oradata/tables/orcl/therm0191.dbf blocksize=8192;
dbv file=/oradata/indexes/orcl/indx201.dbf blocksize=;
dbv file=/oradata/indexes/orcl/mv02.dbf blocksize=8192;
Step 5: Change the file permission and give it to execute.
$chmod +x dbverify.sh
Step 6: Now run the dbverify.sh file, the result will how you on the screen one file after another.
[oracle@affpora02 DBFAIL]$ nohup ./dbverify.sh 1>dbverify.log 2>&1 &
[oracle@affpora02 DBFAIL]$ tail -f dbverify.log
DBVERIFY: Release 9.2.0.6.0 - Production on Thu May 29 15:31:55 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = xdb01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 98
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 363
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2145
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 10194
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 2949237358 (0.2949237358)
DBVERIFY: Release 9.2.0.6.0 - Production on Thu May 29 15:31:55 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 128000
Total Pages Processed (Data) : 51373
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 39422
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1659
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 35546
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3019709153 (0.3019709153)
DBVERIFY: Release 9.2.0.6.0 - Production on Thu May 29 15:31:57 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = tools01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 8
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1272
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 6840 (0.6840)
[oracle@affpora02 DBFAIL]$
Step 7: We have to make sure that there would be no error and no corruption, take very closer look at Total Pages Marked Corrupt like in the output this is your block corruption and it should be 0, as in my case.
$ grep -i Corrupt dbverify.log
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
Total Pages Marked Corrupt : 0
$ grep -i check dbverify.log
Block Checking: DBA = 12741730, Block Type = KTB-managed data block
Page 158818 failed with check code 6101
Block Checking: DBA = 75506094, Block Type = KTB-managed data block
Page 8622 failed with check code 6101
Now Below query will find out which block is corrupted
SQL> select dbms_utility.data_block_address_file(12741730) file#, dbms_utility.data_block_address_block(12741730) block# from dual;
FILE# BLOCK#
---------- ----------
3 158818
SQL> col OWNER||'.'||SEGMENT_NAME format a35
SQL> select owner||'.'||segment_name, segment_type from dba_extents where file_id = 3 and 158818 between block_id and block_id + blocks - 1 and rownum = 1;
OWNER||'.'||SEGMENT_NAME SEGMENT_TYPE
----------------------------------- ------------------
W330.ALLOCSPC TABLE
SQL> select dbms_utility.data_block_address_file(75506094) file#, dbms_utility.data_block_address_block(75506094) block# from dual;
FILE# BLOCK#
---------- ----------
18 8622
SQL> col OWNER||'.'||SEGMENT_NAME format a35
SQL> select owner||'.'||segment_name, segment_type from dba_extents where file_id = 18 and 8622 between block_id and block_id + blocks - 1 and rownum = 1;
OWNER||'.'||SEGMENT_NAME SEGMENT_TYPE
----------------------------------- ------------------
W330.AMHE_INTERFACE_TXNS TABLE
Step 8: setting block checksum parameters
There are two init parameters that help and assist in block corruptions.
1. DB_BLOCK_CHECKSUM: default value of this parameter is true and helps to detect any I/O or disk error occur in data file header, oracle does not check corruption in memory, it check corruption on disk only
SQL> alter system set db_block_checksum=full;
System altered.
SQL> alter system set db_block_checksum=true;
System altered.
2. DB_BLOCK_CHECKING: default value is false for all tablespaces except system tablespace which is true in all cases. This parameter detect damage that occur in memory, this parameter check corruption everytime the buffer that having block is accessed and could take up to 10% cpu performance.
SQL> alter system set db_block_checking=true;
System altered.
Conclusion:
DBVERIFY (dbv) you should run after moving data files from machine to machine on a same platform to verify the data files pages (blocks) if any corruption in the header or in the blocks, dbverify does not check logical corruptions there should be some other mechanism you will use for logical corruption check like analyze validate structure
