How a Oracle Data Block Corruption.
Oracle Database provides different methods for detecting and correcting data block corruption. One method of correction is to drop and re-create an object after the corruption is detected. However, this is not always possible or desirable. If data block corruption is limited to a subset of rows, then another option is to rebuild the table by selecting all data except for the corrupt rows.
Another way to manage data block corruption is to use the DBMS_REPAIR package. You can use DBMS_REPAIR to detect and repair corrupt blocks in tables and indexes. You can continue to use objects while you attempt to rebuild or repair them.
Any corruption that involves the loss of data requires analysis and understanding of how that data fits into the overall database system. Depending on the nature of the repair, you might lose data, and logical inconsistencies can be introduced. You must determine whether the repair approach provided by this package is the appropriate tool for each specific corruption problem.
Followed by Step by Steps:
Pre requisites : Primary database should be FORCE LOGGING mode. Otherwise, it will correct the data file in standby database if application creates the table with NO LOGGING option.
Check FORCE LOGGING mode.
SQL> select status,instance_name,database_role from v$instance,v$database;
SQL> select FORCE_LOGGING from v$database;
If it is showing NO, do below step
SQL> alter database force logging on;
SQL> select FORCE_LOGGING from v$database;
SQL> select * from v$database_block_corruption;
Step 1. In DC (10.0.2.206) On primary database, The tablespace should be in backup mode.
SQL> Alter tablespace ACCT_DETAILS_2 begin backup;
copy the file to some other location for backup
$ cp /u01/datafiles/undo_rrb2_02_datafile.dbf /u01/dbfilecorr
SQL>Alter tablespace ACCT_DETAILS_2 end backup;
Step 2. Before doing next step stop MRP in DR(Standby database)
SQL> @odg_disable_rec.sql (OR)
SQL> recover managed standby database cancel;
Step 3. scp the data file from primary database to standby database.
In DC (Primary database) do the following
$ scp -p /u01/dbfilecorr/acct_details_2_01_datafile.dbf oracle@10.2.2.206:/u02/dbfilecorr
Step 4. Move the data file to correct file system on standby database.
In DR(Standby database) follow the below steps.
mv /u02/dbfilecorr/acct_details_2_01_datafile.dbf /u02/datafiles/acct_details_2_01_datafile.dbf.bkp
mv /u02/dbfilecorr/acct_details_2_01_datafile.dbf /u02/datafiles/acct_details_2_01_datafile.dbf
Step 5. Restart the standby database.
SQL>shutdown immediate
SQL>startup nomount;
SQL>alter database mount standby database;
Step 6.
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel
Step 7. Verify that the block corruption is resolved in standby database.
This is for DR side
$dbv file='/u02/datafiles/acct_details_2_01_datafile.dbf' blocksize=8192----------------------- It will take some time, Total Pages Marked Corrupt should be 0
This is for MIS side
Below is output
O/P: DBVERIFY: Release 10.2.0.4.0 - Production on Tue Jun 21 15:33:13 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u02/datafiles/acct_details_2_01_datafile.dbf
DBV-00200: Block, DBA 210390388, already marked corrupt
DBVERIFY - Verification complete
Total Pages Examined : 1024000
Total Pages Processed (Data) : 912071
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 10
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2540
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 109379
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 2924532591 (6.2924532591)
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
SQL> SELECT tablespace_name,segment_type,owner,segment_name FROM dba_extents WHERE file_id =50 AND 675188 BETWEEN block_id AND block_id + blocks - 1;
SQL> select file#, block#, first_time, next_time from v$archived_log, v$database_block_corruption where CORRUPTION_CHANGE# between first_change# and next_change# and
CORRUPTION_TYPE='NOLOGGING';
Thanks & Comment Please.
