Record Oracle bad block Repair Process

Record Oracle bad block Repair Process

The following error was reported during RMAN cold backup yesterday when I received a backup call from my colleague. A tablespace backup failed.
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03009: failure of backup command on ch00 channel at 04/25/2015 22:02:30
ORA-19566: exceeded limit of 0 blocks upt blocks for file + DATA/dbrac/datafile/tbs_11.435678937

After analysis, it is found that bad blocks do not belong to any object (empty blocks). The following are the repair steps for this bad block:
1. Search for Bad blocks
1) Use RMAN to find bad Blocks
Verify the entire database:
Rman> backup validate check logical database;
Note: When the database version is earlier than 11 GB and is not in archive mode, the preceding commands must be executed in the mounted state of the database.
Verify a single datafile
Rman> backup validate check logical datafile 11;
Then run the following SQL statement to check the Bad blocks:
SQL> Select * from v $ database_block_corruption;

For example:
Validate. sh
#! /Bin/bash
Source/home/Oracle/. bash_profile
$ ORACLE_HOME/bin/rman log =/home/oracle/users/validate. log <EOF
Connect target/
Backup validate check logical datafile 11;
Exit;
EOF

2) use DBV to find bad blocks:
Dbv userid = system/system file = '+ DATA/dbrac/datafile/tbs_11.11.435678937' blocksize = 32768

2. Check whether the bad block does not belong to any object.
Select segment_name, segment_type, owner
From dba_extents
Where file_id = <Absolute file number>
And <shortupted block number> between block_id
And block_id + blocks-1;
For example:
Alter session force parallel query parallel 10;
Select segment_name, segment_type, owner
From dba_extents
Where file_id = 11
And 184959440 between block_id
And block_id + blocks-1;

3. confirm that the block exists in dba_free_space.
Select * from dba_free_space where file_id = <Absolute file number>
And <shortupted block number> between block_id and block_id + blocks-1;
For example:
Select * from dba_free_space where file_id = 11 and 184959440 between block_id and block_id + blocks-1;

4. Create a table
Create table s (
N number,
C varchar2 (4000)
) Nologging tablespace <tablespace name having the specified upt block> pctfree 99;
For example:
Create table users. s (
N number,
C varchar2 (4000)
) Nologging tablespace TBS_11 pctfree 99;

Select segment_name, tablespace_name from dba_segments
Where segment_name ='s ';

Select table_name, tablespace_name from dba_tables where table_name ='s ';

5. Create a trigger
Create or replace trigger corrupt_trigger
After insert on users. s
Referencing old as p_old new as new_p
FOR EACH ROW
DECLARE
Corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number (: new_p.rowid) = & blocknumber)
And (dbms_rowid.rowid_relative_fno (: new_p.rowid) = & filenumber) THEN
RAISE upt;
End if;
EXCEPTION
WHEN upt THEN
RAISE_APPLICATION_ERROR (-20000, 'upt block has been formatted ');
END;
/

6. Create tables with bad data blocks in the allocated space
Note:
I) Because ASSM automatically determines the size of the next partition, you need to create multiple tables and
Allocate space to these tables until the Bad blocks are allocated to one of the objects.
Ii) set AUTOEXTEND of datafile to OFF

1) Find the extent size of the Bad Block
Select BYTES from dba_free_space where file_id = <file no> and <symbol upt block no>
Block_id and block_id + blocks-1;

For example:
Alter database datafile '+ DATA/dbrac/datafile/tbs_11.435678937' autoextend off;

SQL> Select BYTES from dba_free_space where file_id = 11 and 184959440
2 block_id and block_id + blocks-1;

BYTES
----------
29360128

2) Keep allocate until the bad block is part of the S table.
If the output result in step 1 is 64 KB, run the following SQL statement:
Alter table users. s
Allocate extent (DATAFILE '+ DATA/dbrac/datafile/tbs_11.11.435678937' SIZE 64 K );

If the value is larger than 64 KB, use the following
BEGIN
For I in 1 .. 1000000 loop
Execute immediate 'alter table users. s allocate extent (DATAFILE '| ''' + DATA/dbrac/datafile/tbs_11.11.435678937 ''' | 'size 64 K )';
End loop;
End;
/

Use the following SQL statement to check whether a bad block already belongs to an object:
Select segment_name, segment_type, owner
From dba_extents
Where file_id = <Absolute file number>
And <shortupt block number> between block_id
And block_id + blocks-1;

For example:
Select segment_name, segment_type, owner
From dba_extents
Where file_id = 11
And 184959440 between block_id
And block_id + blocks-1;

3) insert data to initialize Bad blocks
Begin
FOR I IN 1 .. 1000000000 loop
For j IN 1 .. 100000 loop
Insert into users. s VALUES (I, 'values xxxxxxxxxxxx ');
End loop;
Commit;
End loop;
END;

7. verify whether there are bad Blocks
Rman> Backup validate check logical datafile <fileno>;
Or validate datafile <fileno> block <blockno reported upload upt>, <blockno reported upload upt>;
Select * from v $ database_block_corruption;
For example:
Validate datafile 11 block 184959440,184 961480,184961481;
Select * from v $ database_block_corruption;

8. When Step 1 confirms that the bad blocks have been eliminated, you can delete the test table.
Drop table users. s purge;

9. Switch multiple logs and checkpoints
Alter system switch logfile;
Alter system checkpoint;

10. delete a trigger
DROP trigger CORRUPT_TRIGGER;
DROP trigger corrupt_trigger1;
DROP trigger corrupt_trigger2;