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

This article mainly shows you "how to deal with the bad blocks in oracle", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to deal with bad blocks in oracle" this article.

The method of dealing with the occurrence of one or more bad blocks in an oracle data file. The following information is provided for each bad block when a bad block error occurs:

1. The absolute file number of the data file containing this bad block can be marked "AFN".

two。 The file name of the data file containing this bad block can be marked "FILENAME". (if you know the file number but do not know the file name, you can execute select name from v$datafile where file#=&AFN to get the file name. If the file number is not recorded in v$datafile and the AFN is larger than the value of the parameter db_files parameter, then the file may be a temporary file. If this is the case, you can execute select name from v$tempfile where file#= (& AFN-&DB_FILES_value)

3. The block number of a bad block in a data file can be marked "BL"

4. Tablespace numbers and tablespace names affected by bad blocks can be marked "TSN" and "TABLESPACE_NAME". You can execute select ts# "TSN" from v$datafile where file#=&AFN;select tablespace_name from dba_data_files where file_id=&AFN to query.

5. The block size in the table space where there are bad blocks can be marked as "TS_BLOCK_SIZE". For oracle9i, you can execute select block_size from dba_tablespace where tablespace_name= (select tablespace_name from dba_data_files where file_id=&AFN); to query the block size. For oracle7.8.0 and 8.1, each tablespace in the database has the same block size. For these versions, you can use show parameter db_block_size to display the block size.

For example: ora-1578 misreading information

ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)

ORA-01110: data file 22: / oracle1/oradata/V816/oradata/V816/users01.dbf

From the above misrecognition information, we can see that the absolute file number AFN is 22, the relative file number RFN is 7, the data block BL is 12698, and the file name FILENAME is / oracle1/oradata/V816/oradata/V816/users01.dbf. The table space number and table space name can be obtained from the above query.

To deal with bad block

There are many reasons for bad blocks, such as:

Bad IO hardware / firmware

OS problem

Oracle problem

Restoring databases that have performed "UNRECOVERABLE" or "NOLOGGING" operations may result in ORA-1578 errors in this case

When we encounter a bad block, we usually have no way to understand the root cause, and in most cases, the most urgent thing is to restart the database and get it up and running.

1. Determine the scope of bad block problems and determine whether they are persistent or temporary

If the problem is wide-ranging or the error is unstable, the key is to identify the cause first (check the hardware, etc.). This is very important. Because if there is an error in the underlying hardware. There is no point in restoring the system.

two。 Replace or remove any problematic or suspicious hardware

3. Determine which database objects are affected

4. Select the most appropriate database recovery / data rescue option

Determine the scope of the bad block problem

Every time a bad block error occurs, you should write down the complete error message and check the alarm log and trace file of the instance to understand any related errors. It is important to perform these steps first to assess whether the damage is a single block, an error caused by an UNRECOVERABLE operation, or a more serious problem.

It is also a good idea to use DBVERIFY to scan affected files and all important files so that you can check for other bad blocks to determine the scope of the problem. Once the list of corrupted file / block combinations has been identified, the following steps can be used to help determine what action should be taken:

1. Fully record the initial error and the details of the application in which the error occurred

two。 Timely save the extracted content from the first (FIRST) record in the alarm log to the hours before the problem and the current point in time

3. Save any trace files mentioned in the alarm log

4. Record any recent OS issues encountered

5. Record whether any special features are being used, such as ASYNC IO, fast write to disk options, etc.

6. Record the current backup location (date, type, etc.)

7. Record whether the database is in ARCHIVELOG mode, for example: running "ARCHIVELOG LIST" in SQL*Plus (or Server Manager)

Replace or remove suspected hardware

Most bad block problems are caused by faulty hardware. "if there is a hardware error or suspicious component, it is best to fix it, or to ensure that there is enough free space for recovery on a separate disk subsystem before performing a restore operation, you can use the following steps to move the data file:"

1. Make sure the file to be migrated is offline or the database instance is in MOUNT state (not open)

two。 Physically restore (or copy) the data file to a new location such as / newlocation/myfile.dbf

3. Inform Oracle of the new location of the file.

For example: ALTER DATABASE RENAME FILE'/ oldlocation/myfile.dbf' TO'/ newlocation/myfile.dbf'

(note that you cannot rename a temporary file, but delete it and recreate it in a new location)

4. Bring relevant data files / tablespaces online (if the database is open)

Note:

If there are multiple errors (not caused by NOLOGGING operations) or errors at the OS level of the affected file are temporary and erratic, then there is no point in doing anything without solving the underlying problem or preparing additional disk space.

If any special IO options, such as direct IO,async IO or similar, are used, it is best to disable them to eliminate the possibility that these options are the cause of a potential problem

Determine which objects are affected

Before deciding how to recover, it's best to determine which objects are affected, because bad blocks can occur in objects that are easy to recreate. For example, for bad blocks that occur in a table with only five rows of data, deleting and recreating the table may be much faster than performing a restore.

For each bad block, collect the information in the following table. The steps for doing this are described below.

1. Initial error

two。 Absolute file number AFN

3. Related file number RFN

4. Block number BL

5. Tablespace

6. Segment type

7. The owner of the section. Name

8. Related object

9. Restore option

In Oracle8/8i/9i/10g; absolute and related file numbers are usually the same, but may be different (especially if the database is migrated from Oracle7). Get the correct AFN and RFN numbers, or you may end up saving the wrong object.

The following query displays the absolute and related file numbers of the data files in the database:

SELECT tablespace_name, file_id "AFN", relative_fno "RFN" FROM dba_data_files

In Oracle8i/9i/10g: in addition to the above instructions on Oracle8, temporary files will be available from Oracle8i. The following query displays the absolute and related file numbers of temporary files in the database:

SELECT tablespace_name, file_id+value "AFN", relative_fno "RFN"

FROM dba_temp_files, v$parameter WHERE name='db_files'

In Oracle7: absolute file number and related file number use the same file number

Segment Type, owner, name, and Tablespace

Given the absolute file number "& AFN" and the block number "& BL" of the bad block, the following query displays the segment type, owner, and name of the object, and the database must be open to use this query:

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents

WHERE file_id = & AFN

And & BL between block_id AND block_id + blocks-1

If the bad block is in a temporary file, the above query will not return any data, and for temporary files, the segment type should be "TEMPORARY"

If the above query does not return rows, it may also be because the bad block is a segment header in the locally managed tablespace (Locally Managed Tablespace, LMT). When the bad block is a segment header block in LMT, the above query will generate a bad block message in alert.log, but the query will not fail. In this case, use the following query:

SELECT owner, segment_name, segment_type, partition_name

FROM dba_segments

WHERE header_file = & AFN and header_block = & BL

"related objects" and possible "recovery options" by segment type:

The related objects and the recovery options available depend on the SEGMENT_TYPE. For the most common segment types, additional queries and possible recovery options are as follows:

CACHE

If the segment type is CACHE, check again that you have entered the correct SQL statement and parameters.

Restore options: you may need to restore the database.

CLUSTER

If the segment type is CLUSTER, you should determine which tables it contains.

For example:

SELECT owner, table_name

FROM dba_tables

WHERE owner='&OWNER'

AND cluster_name='&SEGMENT_NAME'

Restore options:

You may need to restore the database if the owner is "SYS".

For the non-data dictionary cluster, possible options include:

Restore or rescue the data of all tables in cluster, and then recreate cluster and all its tables

The cluster may contain multiple tables, so it is best to gather information about each table in the cluster before making a decision.

INDEX PARTITION

If the segment type is INDEX PARTITION, record the name and owner, and then determine which partitions are affected:

SELECT partition_name

FROM dba_extents

WHERE file_id = & AFN

AND & BL BETWEEN block_id AND block_id + blocks-1

Then follow the steps for dealing with the INDEX section to continue with the following.

Restore options:

You can rebuild the index partition using the following statement:

ALTER INDEX xxx REBUILD PARTITION ppp

INDEX

If the segment type is INDEX, for non-dictionary INDEX or INDEX PARTITION, determine which table the index is in:

For example:

SELECT table_owner, table_name

FROM dba_indexes

WHERE owner='&OWNER'

AND index_name='&SEGMENT_NAME'

And determine whether the index supports constraints:

For example:

SELECT owner, constraint_name, constraint_type, table_name

FROM dba_constraints

WHERE owner='&TABLE_OWNER'

AND constraint_name='&INDEX_NAME'

Possible values for CONSTRAINT_TYPE include:

The P index supports primary key constraints.

U indexes support unique constraints.

If the index supports primary key constraints (type "P"), confirm that the primary key is referenced by any foreign key constraints:

For example:

SELECT owner, constraint_name, constraint_type, table_name

FROM dba_constraints

WHERE ritual ownership owner

AND ritual intact name name

Options:

If the owner is "SYS", you may need to restore the database.

For non-dictionary indexes, possible options include:

Restore or rebuild the index (any associated constraints are disabled / enabled)

ROLLBACK

If the segment type is ROLLBACK, because the bad block of the ROLLBACK segment requires special handling.

Option may require recovery of the database.

TYPE2 UNDO

TYPE2 UNDO is the undo segment of system management, and it is a special form of rollback segment. The bad blocks of these segments require special treatment.

Option may require recovery of the database.

TABLE PARTITION

If the segment type is TABLE PARTITION, record the name and owner, and then determine which partitions are affected:

SELECT partition_name

FROM dba_extents

WHERE file_id = & AFN

AND & BL BETWEEN block_id AND block_id + blocks-1

Then follow the steps for dealing with the TABLE section to continue with the following.

Options:

If all the bad blocks are in the same partition, one thing you can do at this point is to EXCHANGE the partition where the bad blocks are located with an empty table, which allows the application to continue to run (unable to access the data in the partition where the bad blocks are located), and then extract any uncorrupted data from the previous empty table

TABLE

If the owner is "SYS", you may need to restore the database.

For non-dictionary TABLE or TABLE PARTITION, determine which indexes exist in the table:

For example:

SELECT owner, index_name, index_type

FROM dba_indexes

WHERE table_owner='&OWNER' AND table_name='&SEGMENT_NAME'

And determine if there are any primary keys in the table:

For example: SELECT owner, constraint_name, constraint_type, table_name

FROM dba_constraints

WHERE owner='&OWNER'

AND table_name='&SEGMENT_NAME' AND constraint_type='P'

If there is a primary key, confirm that it is referenced by any foreign key constraints:

For example:

SELECT owner, constraint_name, constraint_type, table_name

FROM dba_constraints

WHERE ownerships ownership owners

AND ringing intact name names

Options:

If the owner is "SYS", you may need to restore the database.

For non-dictionary tables, possible options include:

Restore or salvage the data in the table (or partition) and then recreate the table (or partition) or ignore bad blocks (for example, using DBMS_REPAIR to mark problem blocks that need to be skipped)

IOT (index organization table)

Bad blocks in the IOT table should be handled as they are in the table or partitioned table.

The only exception is if the competition is damaged.

The competition for IOT tables is the table itself, which cannot be deleted and recreated.

Options:

If the owner is "SYS", you may need to restore the database.

For non-dictionary tables, possible options include:

Restore or rescue the data in the table (or partition) and then recreate the table (or partition) or ignore bad blocks (DBMS_REPAIR does not apply to IOT)

LOBINDEX

Determine which table LOB belongs to:

SELECT table_name, column_name

FROM dba_lobs

WHERE owner='&OWNER' AND index_name='&SEGMENT_NAME'

You may need to restore the database if the owner of the table is "SYS".

The LOB index cannot be rebuilt, so you must treat the problem as a bad block on the LOB column in the affected table.

Options:

You may need to restore the database if the owner is "SYS".

For non-dictionary tables, possible options include:

Restoring or salvaging the data in the table (and its LOB columns) and then recreating the table, ignoring bad blocks is usually not desirable unless any further DML operations on the problem columns in the table are unlikely.

LOBSEGMENT

Determine which table LOB belongs to:

For example:

SELECT table_name, column_name

FROM dba_lobs

WHERE owner='&OWNER'

AND segment_name='&SEGMENT_NAME'

If the owner of the table is "SYS", you may need to restore the database.

For non-dictionary tables, it may be difficult to find a specific row that references a corrupted LOB block because the reported error does not show which row of data in the table contains corrupted LOB data.

You can usually refer to the application log where the error occurred, any SQL_TRACE, the 10046 trace file for the session, if any, or by setting the event "1578 trace name errorstack level 3" in the session to see if it helps identify the current SQL/ binding / line.

For example:

ALTER SYSTEM SET EVENTS '1578 trace name errorstack level 3'

Then wait for the application to trigger the error and look for the trace file.

If you don't have any clues, you can build a PLSQL block, scan the problem table line by line to extract the LOB column data, and the scan will cycle until an error occurs. This method may take a while, but it should be able to find the primary key or ROWID of the data row that references the corrupted LOB block.

For example:

Set serverout on

Exec dbms_output.enable (100000)

Declare

Error_1578 exception

Pragma exception_init (error_1578,-1578)

N number

Cnt number:=0

Badcnt number:=0

Begin

For cursor_lob in (select rowid r, & LOB_COLUMN_NAME L)

From & OWNER.. & TABLE_NAME) loop

Begin

N: = dbms_lob.instr (cursor_lob.L, hextoraw ('AA25889911'), 1, 999999)

Exception

When error_1578 then

Dbms_output.put_line ('Got ORA-1578 reading LOB at' | |

Cursor_lob.R)

Badcnt: = badcnt + 1

End

Cnt: = cnt + 1

End loop

Dbms_output.put_line ('Scanned' | | cnt | | 'rows-saw' | | badcnt | |

'errors')

End

/

A corrupted LOB block may only appear as an old version (for consistent reads) and it has not been reused, in which case all rows in all tables are accessible, but once the block is recycled / reused, the LOB column cannot be inserted / updated.

Options:

If the owner is "SYS", you may need to restore the database.

For non-dictionary tables, possible options include:

Restore or salvage the data in the table (and its LOB columns) and then recreate the table or ignore bad blocks (DBMS_REPAIR cannot be used on LOB segments)

TEMPORARY

If the segment type is TEMPORARY, the bad block does not affect the persistent object. Check to see if the table space in question is being used as an TEMPORARY table space:

SELECT count (*) FROM dba_users

WHERE temporary_tablespace='&TABLESPACE_NAME'

Options:

In the case of TEMPORARY_TABLESPACE, you might be able to create a new temporary tablespace, switch all users to it, and then delete the problematic tablespace.

If it is not a temporary tablespace, the block is no longer read and reformatted the next time it is used-if the root cause of the problem has been resolved, the error should not occur again.

Normally, no restore is required, but if there may be a problem with the disk and the tablespace contains useful data, it is best to restore the affected files in the database

"No return rows"

If there is no extent containing bad blocks, first check the parameters used in the query again. If you determine that the file number and block number are correct and do not belong to an object in DBA_EXTENTS, do the following:

Check again whether the relevant files are temporary files. Note that the file number of the temporary file depends on the database initialization parameter DB_FILES, so any change to this parameter will change the absolute file number reported in the error.

DBA_EXTENTS does not contain blocks in the local management tablespace for local space management

If the time at which you run the query statement in the database is different from the point in time at which the error occurred, the problem object may have been deleted, so the query against DBA_EXTENTS may not display any rows.

If the error you are investigating is reported by DBVERIFY, DBV checks all blocks, regardless of whether they belong to an object. As a result, bad blocks may exist in the data file, but are not used by any objects.

Options:

Errors on unused Oracle blocks can be ignored because if the block is needed, Oracle creates a new block image (formatted), so any problems on that block will never be read.

If you suspect that the block may be a space management block, you can use the DBMS_SPACE_ADMIN package to help you check:

Exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY ('& TABLESPACE_NAME')

The above command writes inconsistencies to the trace file, but if it encounters a fatally bad block, it reports the following error:

ORA-03216: Tablespace/Segment Verification cannot proceed

Errors that occur on the bitmap space management block can usually be corrected by running the following command:

Exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS ('& TABLESPACE_NAME')

For each bad block, if you need to try and determine the actual cause of the bad block, it is also a good idea to collect the following physical evidence:

I) operating system HEX dump of bad blocks and blocks located on either side of them.

On UNIX:

Dd if=&FILENAME bs=&TS_BLOCK_SIZE skip=&BL-1 count=3 of=BL.dd

For example: for BL=1224:

Dd if=ts11.dbf bs=4k skip=1223 count=3 of=1223_1225.dd

On VMS:

Where XXXX= operating system block number (in 512 byte block)

To calculate this value, multiply the reported block number by "& TS_BLOCK_SIZE/512".

Ii) when in ARCHIVELOG mode, copy a secure copy of the archived log file before and after the error time, preferably including the log file a few hours before the error is reported. Also, save all copies of the problem data file before the error, because the previous data file image and redo records help to find out the cause of the error DBV can usually be used to check whether the problem exists in the backup copy of the file. Ideally, get a backup image of the data file that does not report a bad block, as well as all redo records from that point in time to shortly after the bad block time is first reported.

Iii) to obtain the Oracle dump of the problem block:

ALTER SYSTEM DUMP DATAFILE'& FILENAME' BLOCK & BL

(4) Select recovery option

Now, the best recovery option depends on the affected object. The instructions in section (3) above should have highlighted the main options available for each affected object. The actual recovery method you choose may include one or more of the following mixed methods:

Do you need to perform any restore operations?

If the error occurs in an TEMPORARY tablespace or in a block that no longer belongs to any database object, no action is required.

Can I use full recovery?

To select full recovery, the following conditions must be met:

The database is in ARCHIVELOG mode (the "ARCHIVELOG LIST" command displays Archivelog mode)

Have a good backup of the affected files. Note that in some cases, bad blocks may already exist but go undetected for a long time. If the most recent data file backup still contains bad blocks, you can try an earlier backup as long as you have all the necessary archive logs.

(you can usually use the DBV START= / END= option to check for corruption of a specific block located in a restored copy of a backup file)

All archive logs from the backup time to the current point in time are available

The current online logs are available and intact

The error is not caused by the recovery performed after running the NOLOGGING operation

If the above conditions are met, full recovery is usually the preferred method

But please note:

(a) if the transaction rollback has found that the bad block is on the object, rather than the rollback segment itself, the undo operation may have been abandoned. In this case, it may be necessary to rebuild the index / check data integrity after the restore is complete.

(B) if the file to be restored contains data from NOLOGGING operations performed since the last backup, these blocks will be marked as "bad blocks" if data files or database recoveries are used. In some cases, this can make the situation worse.

If a bad block persists after performing a database recovery, it means that all backups contain bad blocks, underlying errors persist, or the problem is reproduced through redo. In these cases, you need to select some other recovery options.

If you do not need to extract any data from the object itself, can you delete or recreate the object?

You can delete the object or recreate it from the script / recently exported copy. Once an object is deleted, the block in the object is marked as idle and the block is reformatted when it is assigned to a new object. It is wise to rename the table rather than delete it unless you are absolutely sure that the data in it is no longer needed.

For table partitions, only the affected partitions need to be deleted. For example: ALTER TABLE... DROP PARTITION...

DROP PARTITION may fail if the bad block affects the partition segment header, or if the file containing the partition header is offline. In this case, first replace it with a table with the same definition, and then you can still delete the partition.

For example: ALTER TABLE.. EXCHANGE PARTITION.. WITH TABLE..

The most common reconstructable object is the index. Always deal with table bad blocks before dealing with indexing problems in tables

For any segment, if you have the absolute file number and block number of a bad block, you can use the following method to quickly extract the object DDL:

Set long 64000

Select dbms_metadata.get_ddl (segment_type, segment_name, owner)

FROM dba_extents

WHERE file_id=&AFN AND & BL BETWEEN block_id AND block_id + blocks-1

Do you need to save the data before recreating the object?

If the problem is on a critical application table that is updated regularly, you may need to rescue as much data in the table as possible, and then recreate the table.

Is it advisable to ignore bad blocks currently?

In some cases, the most direct option may be to ignore the bad block and prevent the application from accessing it.

The last option

Restore a database or tablespace to an earlier point in time (through a point-in-time restore) or restore a cold backup before a bad block or use an existing exported file

Complete recovery

If the database is in ARCHIVELOG mode and has a good backup of the affected files, recovery is usually the preferred method. This is not guaranteed to solve the problem, but it does effectively solve most of the bad block problems. If the recovery causes a problem again, return to the above list of options and select a different method.

If you are using Oracle9i (or later), you can use the RMAN BLOCKRECOVER command to perform a block-level restore.

If you are using an earlier version of Oracle, you can perform a data file recovery (the rest of the database can continue to run), or a database recovery (you need to shut down the database)

If you are using Oracle 11g (or later), you can use Data Recovery Advisor (data recovery Guide).

Block level recovery

Since the Oracle9i version, RMAN allows individual blocks to be restored, while other parts of the database (including other blocks in the data file) can still be accessed normally. Note that block-level recovery can only fully restore the block to the current point in time. To use this option to restore a single block, you do not have to use RMAN for backup.

For example:

In fact, an ORA-1578 error occurred on block 30 of file 6, which may be due to a bad block caused by a media problem, and you have a good cold backup image of the file and have been restored to "... / RESTORE/filename.dbf". "assuming that all archive logs exist (in the default location), you can perform a block-level restore through RMAN using the following command sequence:"

Rman nocatalog

Connect target

Catalog datafilecopy '... / RESTORE/filename.dbf'

Run {blockrecover datafile 6 block 30;}

This operation will use the registered data file backup image and any required archive logs to perform block recovery, and only the problematic blocks will be restored to the current point in time.

Data file recovery

Data file recovery includes the following steps. If you have multiple files, repeat these steps for each file, or see Database recovery below. These steps can be used when the database is in the OPEN or MOUNTED state.

Take affected data files offline

For example: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE

Copy the file to a safe location (in case the backup is damaged)

Restore the latest backup of the file to an intact disk

Use DBVERIFY to check whether the restored file has bad blocks

Assuming the restored file is intact, rename and save the data file to the new location (if it is not the original location)

For example: ALTER DATABASE RENAME FILE 'old_name' TO' new_name'

Restore data files

For example: RECOVER DATAFILE 'name_of_file'

Bring the data file online

For example: ALTER DATABASE DATAFILE 'name_of_file' ONLINE

Database recovery

Database recovery usually consists of the following steps:

Shut down the database (using the option immediate or abort)

Copy the current copies of all files to be restored to a secure location

Restore backup files to an intact disk

Do not restore control files or online REDO log files

Use DBVERIFY to check restored files

Start the database to MOUNT state (startup mount)

Rename any data files that need to be relocated

For example: ALTER DATABASE RENAME FILE 'old_name' TO' new_name'

Make sure all necessary files are online

For example: ALTER DATABASE DATAFILE 'name_of_file' ONLINE

Restore the database

For example: RECOVER DATABASE

Open the database

For example: ALTER DATABASE OPEN

Once a full restore has been performed, it is best to check the database before allowing it to be used:

Run "ANALYZE VALIDATE STRUCTURE CASCADE" for each problem object to check for table / index mismatches. If any und operations have been abandoned, this command may show a mismatch and the index needs to be rebuilt.

Check the logical integrity of the data in the table at the application level.

Rebuild the index

When the corrupted object is a user index, if the underlying table is not corrupted, you can delete and rebuild the index.

If the underlying table is also corrupted, you should resolve the bad blocks of the table before rebuilding any indexes.

If the information collected indicates that the index has dependent foreign key constraints, you need to do the following:

ALTER TABLE DISABLE CONSTRAINT

Use the following command to rebuild the primary key

ALTER TABLE

DISABLE CONSTRAINT; DROP INDEX; CREATE INDEX. With appropriate storage clause ALTER TABLEENABLE CONSTRAINT

Enable foreign key constraints

ALTER TABLE ENABLE CONSTRAINT

For index partitions, execute the following command:

ALTER INDEX... REBUILD PARTITION...

Note:

(1) do not use the "ALTER INDEX.. REBUILD" command to rebuild a corrupted non-partitioned index, which is important because this usually attempts to build a new index from an existing index segment that contains a bad block.. "ALTER TABLE. REBUILD ONLINE" and "ALTER INDEX. REBUILD PARTITION." The new index is not built from the old index segment, so it can be used.

(2) if the new index contains a subset of the existing index, Create INDEX can use the data from the existing index, so if you have two corrupted indexes, you should delete both before rebuilding.

(3) when rebuilding the index, be sure to use the correct storage option.

Rescue the data in the table

If the damaged object is TABLE or CLUSTER or LOBSEGMENT, it must be understood that the data in the bad block has been lost. Some data may be salvaged from the HEX dump of the block, or from the columns covered by the index.

Because you may need to salvage data in bad blocks from the index, it is best not to delete any existing indexes until all the required data extraction is complete.

There are several ways to extract data from a table that contains bad blocks. Choose the most appropriate method, the details are described below. The purpose of these methods is to extract as much data as possible from accessible table blocks. In general, it is a good idea to rename a corrupted table so that you can create a new object with the correct name.

For example: RENAME TO

A method of extracting data around Bad blocks from Bad Block Table

(1) starting with Oracle 7.2 (including Oracle 8.0,8.1 and 9i), bad blocks in the table can be skipped.

This is by far the easiest way to extract table data, using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS or Event 10231

If the bad block is in the IOT overflow segment, the same method should be used, except that Event 10233 and full index scan are used.

Note that this method applies only if the wrapper of the block has been marked as a bad block. For example, if the block reports an ORA-1578 error. If the problem is an ORA-600 or other non-ORA-1578 error, you can usually use DBMS_REPAIR to mark the bad block in the table as "soft bad block." This way, when you access the block, the system displays an ORA-1578 error so that DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used.

Note: blocks marked as "bad blocks" by the FIX_CORRUPT_BLOCKS program will also be marked as "bad blocks" after any restore / restore operation.

To do this using DBMS_REPAIR, the steps are summarized as follows:

Use DBMS_REPAIR.ADMIN_TABLES to create management tables

Use DBMS_REPAIR.CHECK_OBJECT to find the problem block

Export all intact data in the problem block before it is damaged.

Use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark the problem blocks found as "bad blocks", and then they will display ORA-1578

If necessary, use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip the bad blocks in the table.

(2) starting with Oracle 7.1, ROWID range scanning can be used. The syntax for this function is complex, but you can use ROWID prompts to select data around bad blocks.

(3) if there is a primary key, you can select table data through this index. You can also select some data through any other index. This method is slow and takes a long time, and is usually used only in Oracle version 7. 0

(4) there are a variety of rescue programs / PLSQL scripts that can be used to rescue the data in the table. Compared with the above methods, these methods take longer to set up and use, but they can often deal with all kinds of bad blocks except ORA-1578.

Method of extracting data from a table that contains a corrupted LOBSEGMENT block:

DBMS_REPAIR cannot be used on LOB segments, and if the bad block LOB block is not referenced by any row in the table, you should be able to use CREATE TABLE as SELECT (CTAS) to create the table by choice, or export / delete / import the table as is.

If a bad block LOB block is referenced by a row, you should be able to select or export using a WHERE predicate that does not include the problem line

Note: the LOB column value of the problem row can be updated to NULL, so that the SELECT operation will no longer return an ORA-1578 error, but the bad block will wait to be reused, with the INSERT or UPDATE operation on the LOB column in the row, when the problematic block is reused, the ORA-1578 error will finally be reported, which is worse than the bad block in the known row. Therefore, you should set the LOB column to NULL. Exe only if you intend to recreate the table immediately.

Extract data from the bad block itself

Because the bad block itself is "corrupted", any data extracted from the block should be considered suspicious, and the main ways to obtain data rows from the bad block itself include:

For TABLE blocks, Oracle Support can use a tool that tries to interpret the contents of the blocks.

Use the existing index in the table to extract the column data covered by the index using the ROWID that falls in the bad block, which is described in the ROWID range scan article mentioned above near the end:

For Oracle8/8i, see Document 61685.1

For Oracle7, see Document 34371.1

You can use LogMiner on the redo stream to find the initial insert / update operation to load data into the problem block. The main factor here is the time when the data is actually put into the problem block. For example, row 2 may have been inserted yesterday, and row 1 may have been inserted a year ago.

Ignore bad blocks

When an error occurs, you can ignore the bad block and accept the reported error, or block access to the defective block row at the application level.

For example, if the problem block / row is in a child table, access to the corresponding row in the parent table can be blocked at the application level, so that the child row will never be accessed (but pay attention to cascading class constraints)

This may not be conducive to bulk access to data reports and other tasks, so the DBMS_REPAIR option described above is also a desirable way to prevent blocks from being accessed. Using this method to mark and skip bad blocks provides a short-term solution. As a result, you can try full data rescue and / or recovery when planning downtime, or set aside more time to try other recovery options on the second (cloned) database. Note, however, that marking a bad block with DBMS_REPAIR.FIX_CORRUPT_BLOCKS will cause the marked block to remain "bad" after recovery.

Ignoring bad blocks is a good choice for data that is aging rapidly and is about to be purged (for example, in a date-partitioned table, older partitions will be deleted at some point in time).

Ignore bad blocks on LOB segments

At the application level, corrupted LOB columns can be ignored until the table can be rebuilt. One way to ensure that the situation in the above warning does not occur is to ensure that the application can only access the data in the sub-table through the view on the table that contains WHERE predicates.

For example, suppose the table MYTAB (a number primary key,b clob) has one or more rows pointing to corrupted LOB data.

ALTER TABLE MYTAB ADD (BAD VARCHAR2 (1))

CREATE VIEW MYVIEW AS SELECT a,b FROM MYTAB WHERE BAD is null

Set BAD='Y' on any question line

If only MYVIEW accesses MYTAB, the row will never be visible and therefore cannot be updated, thus isolating bad block entries until the problem is resolved.

Obviously, this example is more of a design-time solution, but some applications may already have a similar mechanism and may access data only through a view (or through a RLS policy), providing some options to hide the problem row.

Warning for ignoring bad blocks

Although bad blocks can be ignored, it is important to note that bad blocks can still occur in the form of warnings / errors when running DBVERIFY,RMAN backups. Be sure to carefully record any bad blocks you will see in these tools, especially any blocks you expect to skip when using RMAN (for example, with MAX_CORRUPT set), and be sure to remove any "accept" options for errors after clearing the bad blocks.

For example, suppose the bad block has been processed to ignore the bad block and skip the problem line at the application level. RMAN may be configured to accept bad blocks during backup. The table is then recreated later during the table reorganization. If the RMAN configuration is not updated in time to reflect that there are no errors at present, RMAN may ignore some other bad blocks that occur later.

In addition, it is important to note that ignoring bad blocks in the table section may cause the query to return inconsistent results.

For example, a table with SKIP_CORRUPT set may have different results, depending on whether an index scan or table access is used, and other reports may simply report errors.

Note that if you ignore a bad block but use the DBMS_REPAIR.FIX_CORRUPT_BLOCKS flag, redo information is written to the bad block, which may limit subsequent recovery options.

The last option

If you have a standby environment (physical or logical), check it first.

No matter what type of block the problem occurs on, you can use a possible option to restore the database or problem tablespace to a point in time before the bad block. The difficulty with this option is that you don't always know when the problem first occurred.

DBVERIFY can usually be used to check whether there are bad blocks in restored files. In particular, the START= / END= DBV option can be used to quickly test the first time on the restored backup image to see if the problem block itself is wrong.

Some of the final options available for restore operations are listed below, when one or more of them occur:

You have lost very important data files (or bad blocks in the data files), and there is no normal backup of the problem files (no bad blocks)

Neither in ARCHIVELOG mode nor all archived logs since the file was created

The problem still recurs after full recovery

Last chance:

Note that if you lose all copies of the data file, but still have all the archive logs since the file was created, it is still possible to restore the file.

For example:

ALTER DATABASE CREATE DATAFILE '....' [as'...']

RECOVER DATAFILE '....'

ALTER DATABASE DATAFILE '....' ONLINE

If you encounter this situation, try to use these steps to recover the data file before continuing below.

If you reach this point, there is no other way to restore the file to the current point in time. At this point, it is best to close the instance and back up the current database so that you can still fall back to the current point in time after the selected measure fails. (for example, if a bad backup block is found).

Some of the options available are summarized as follows:

Restore to an early cold backup

For example, if you are in NOARCHIVELOG mode

Establish a clone database from a cold backup

And extract (export) the problem table

Or transfer problem tablespace

Restore the database to a consistent point in time using point-in-time recovery

Requires a good backup and any required archived logs

You must restore all files and roll forward the entire database to the appropriate point in time.

You can perform a point-in-time recovery in a cloned database, then transfer the problem tablespace to the problem database, or import the problem table from the clone database to the problem database using the export / import tool.

Point-in-time recovery of tablespace

You can perform a point-in-time recovery only on affected tablespaces.

Recreate the database from a logical export / copy

Need to have a good logical backup of the database

Note: to use this option, you must recreate the database.

As with other options, you can recreate it in the cloned database just to get a good image of the problem table.

In a word, making a good backup is the most important work of DBA.

The above is all the content of the article "how to deal with the bad blocks in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.