Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people do not know what to do about how to format damaged blocks that do not belong to any segment in oracle database. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Symptoms of the problem:

1. Rman backup failed with ORA-19566 error, and the reported bad block does not belong to any object

2. Dbverify shows that there are bad blocks

3. The bad block does not belong to any object.

Second, reason analysis:

RMAN and DBV continue to report bad blocks until they are reused and reformatted.

3. Solution:

One possible way to solve such problems is as follows. Note that it does not guarantee that the problem is solved, but it is known to solve the problem in several cases. If you report a lot of block corruption in a data file, prompt for the bad block number that has been reported in blocknumber in step 6.

When an object is recreated, blocks that have been allocated to it (or even damaged blocks) become free space (free space). They are there waiting to be reassigned to an object that needs extra space. Once they are reassigned to a new extent for use on an object, only if any DML operation uses these blocks (even if the damaged blocks are in free space, they now need to be allocated), they will be reformatted before the DML operation changes and uses them.

It is important to note that extent's simple allocation unformatted blocks. The extent assigned in step 7 and step 8 perform the DML operation, using the blocks allocated in step 7, so that the bad blocks are reused and formatted.

In this document, we try to reformat the bad blocks manually.

Step 1-identify the corrupted data file

Damage can be reported at the application layer, such as DBV and RMAN, or alert.log.

For example, the following information can be obtained during your RMAN backup:

RMAN-03009: failure of backup command on nm4501 channel at 04/29/2005 09:44:41

ORA-19566: exceeded limit of 0 corrupt blocks for file E:\ xxxx\ test.ORA.

The bad block is in the file E:\ xxxx\ test.ORA.

Step 2-run DBV/Rman verification and check for bad blocks on the affected data file

Run dbverify on the data file that reports bad blocks.

# dbv userid= {system/password} file= {full path filename} logfile= {output filename}

Step 3-check whether the block is part of an object-for cases where the damaged block size is small

Query dba_extents to confirm whether the bad block belongs to any object.

SQL > select segment_name, segment_type, owner from dba_extents where file_id = and between block_id and block_id + blocks-1

If the block does not belong to any object, query dba_free_space to confirm that the bad block belongs to the free space of the data file.

SQL > Select * from dba_free_space where file_id= and between block_id and block_id + blocks-1

Step 4-A better way to find the affected block and verify that it belongs to any segment is to use RMAN, which is both useful and convenient.

If you have already run rman validation in step 2, go directly to the sqlplus script given below to confirm the object.

$rman target / nocatalog or $rman target sys/ nocatalog run {allocate channel D1 type disk; allocate channel D2 type disk -multiple channels may be allocated for parallelizing purposes-- depends: RMAN-Min (MAXOPENFILES, FILESPERSET)-- Defaults: MAXOPENFILES = 8 FILESPERSET = 64-allocate channel dn type disk Backup check logical validate database

Note: if RDBMS is less than 11g and is in non-archive mode, the database must be in mounted mode

* the RMAN command "backup check logical validate database" must be run and completed before taking any further action.

The "v$database_block_corruption" view will be populated when this command is completed (file-based).

If it is not completed, you may get invalid / incomplete information in the next steps.

Step 5-create a virtual table as a user other than SYS or SYSTEM (user)

SQL > connect scott/password

Create a virtual table in the tablespace that contains the data file with bad blocks and use the nologging option to prevent redo records from being generated:

SQL > create tables (n number, c varchar2 (4000)) nologging tablespace

Different storage parameters can be used to adapt to a specific environment.

We use PCTFREE 99 to speed up the formatting of the block

Verify that the table is created in the correct tablespace by querying user_segments:

QL > select segment_name,tablespace_name from user_segments where segment_name='S'

Note that in 11gR2, due to the concept of delay segment creation, the user_segments query mentioned above may not be reported. In this case, query USER_TABLES

SQL > select segment_name,tablespace_name from user_tables where segment_name='S'

Step 6-create a trigger on a virtual table that throws an exception once a bad block is reused

Connect as sysdba and create the following triggers:

Note that when prompted for the file number, enter the relevant file number (rfile# value in v$datafile)

CREATE OR REPLACE TRIGGER corrupt_trigger AFTER INSERT ON scott.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 corrupt; END IF; EXCEPTION WHEN corrupt THEN RAISE_APPLICATION_ERROR (- 20000, 'Corrupt block has been formatted'); END; /

When prompted for the block number, enter the block number for the bad block.

When prompted for a file number, enter the relevant file number of the corrupted data file (the rfile# value in v$datafile).

Step 7-allocate space for tables in the affected data file.

Please note:

1) if this is an ASSM tablespace, you may need to repeat this step several times. That is, create multiple tables and allocate multiple extent.

And check the dba_extents periodically to ensure that the free space is now allocated to a virtual table.

This is because ASSM will automatically determine the size of the next extent

2) it is recommended to ensure that the data file AUTOEXTEND is set to OFF to prevent its growth.

First find the extent size by querying dba_free_space

SQL > Select BYTES from dba_free_space where file_id= and between block_id and block_id + blocks-1; BYTES-- 65536

In this case, its size is 64K. Therefore, assign the extent as follows:

SQL > alter table scott.s allocate extent (DATAFILE'e:\ xxxx\ test.ORA' SIZE 64K)

If you have more than one 64K free extent in this data file, you may need to use this loop:

BEGIN for i in 1... 1000000 loop EXECUTE IMMEDIATE 'alter table scott.s allocate extent (DATAFILE' | |''E:\ xxxx\ test.ORA''' | | 'SIZE 64K)'; end loop; end; /

Continue to allocate space until bad blocks become part of scott.s-check using the following query:

SQL > select segment_name, segment_type, owner from dba_extents where file_id = and between block_id and block_id + blocks-1

Step 8-insert data into the virtual table to format the block

Sample code (depending on the size of the table space, the number of loops may change):

BEGIN FOR i IN 1.. 1000000000 LOOP INSERT / * + APPEND * / INTO scott.s select I, lpad ('REFORMAT',3092,' R') from dual; commit; END LOOP; END

Or

BEGIN FOR i IN 1... 1000000000000 LOOP INSERT INTO scott.s VALUES (iMagneticx'); END LOOP; END; /

Or use the following code that contains 2 loops:

Begin FOR i IN 1... 1000000000 loop for j IN 1.. 1000 loop Insert into scott.s VALUES; end loop; commit; END LOOP; END

Each insert of a row into a table triggers a trigger, and once a row of data is inserted into a bad block, an ORA-20000 exception is generated.

Step 9-determine the bad blocks in the data file by running DBV and Rman backups

Run dbverify or RMAN to verify again on the corrupted data file (or the entire database). It does not show block damage.

Make sure you do two manual log switches or checkpoints so that the information in memory is written to disk.

RMAN backup does not report any errors on this block.

Before running a real backup operation, you can rerun the RMAN validate command on the data file and check that v$database_block_corruption no longer shows that the block is marked as corrupted.

For the database version of Backup validate check logical datafile,

For database version > = 11gR1

Rman > Backup validate check logical datafile

Or

Rman > validate datafile block,

Once it's done

SQL > SELECT * FROM V $DATABASE_BLOCK_CORRUPTION

Step 10-Delete the virtual table created in step 4

SQL > DROP TABLE scott.s

If the version is 10gr1 or above, use the purge option to empty the Recycle Bin

Step 11-perform manual log switching and checkpoint

Perform two log switches and checkpoints to write blocks formatted in memory to disk and to prevent dbverify from reporting errors

SQL > Alter system switch logfile;-- > Do this couple of time SQL > Alter system checkpoint

Step 12-Delete the trigger created in step 6

SQL > DROP triggercorrupt_trigger; after reading the above, have you mastered how to format damaged blocks that do not belong to any segment in the oracle database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.