Analysis and resolution of common ORACLE error codes ii

  • 2020-05-24 06:23:39
  • OfStack

The ORACLE tutorial you are looking at is: analysis and resolution of common error code for ORACLE 2.


--------------------------------------------------------------------------------

p align="JUSTIFY" > ORA-01578:Oracle data block corrupted(file # num,block # num)

Cause: when ORACLE accesses 1 data block, due to 1. Hardware I/O error; 2. Operating system I/O error or buffering problem; 3. Memory or paging problems; 4. ORACLE failed to access an unformatted system block; 5. When one of the above situations, such as partial overflow of data files, causes a bad logical block or a bad physical block, an error of ORA-01578 will be reported.

Solution: since ORACLE only reports errors when accessing the data file in question, the error may be reported later than the actual error time. If the error message of ORA-01578 indicates that the bad data block points to the user's own data file, the following methods can be used to solve the problem:


If the bad block detected by the SQL statement below appears on the index, the index needs to be rebuilt

SQL $# @ 62; Select owner,segment_name,segment_type from dba_extents where file_id=$#@60; F $# @ 62; and $# @ 60; B $# @ 62; between block_id and block_id + blocks - 1; ($# @ 60; F $# @ 62; And $# @ 60; B $# @ 62; Are the file number and block number of the bad block reported by ORA-01578.)


If a bad block appears on a table, first analyze whether it is a permanent bad block with the following statement (it is recommended to execute it twice more to help identify whether the data bad block is permanent (physical bad block on the hard disk) or random (caused by a memory or hardware error) :

SQL$#@62;Analyze table $#@60;table_name$#@62; validate structure cascade;

When this command is executed, the following results may occur:

ORA-01578: a permanent physical or logical block with the same parameters as the original error message; There are different parameters from the original error message, possibly related to memory, page space and I/O devices.

If the user has an up-to-date copy of the table, it is best to use this backup to restore the table, or to use event 10231 to fetch data other than the bad block:

$# @ 60; 1 $# @ 62; .close the database first

$# @ 60; 2 $# @ 62; . Edit init $# @ 60; sid $# @ 62; .ora file, add:

event= "10231 trace name context forever,level 10"

$#@60;3$#@62;.startup restrict

$# @ 60; 4 $# @ 62; Create a temporary table: SQL$#@62; create table errortemp as select * from error; (error is the table name of the bad table)

60 $# @ # @ 62. . Put event from init$#@60; sid $# @ 62; Delete and restart the database from the.ora file

$# @ 60; 6 $# @ 62; .rename bad table, make the temporary table rename the table name of the bad table

$# @ 60; 7 $# @ 62; .create INDEX and so on on the table

If the ORA-01578 error message indicates that the data block is pointing to a data dictionary or a rollback segment, you should contact ORACLE immediately to discuss a good solution.

The solutions discussed here are only one of the more common ones. For more specific solutions, please refer to the troubleshooting manual of ORACLE, which contains a list of possible methods for retrieving data other than bad blocks using the ROWID method, which is not discussed here.

The corresponding English is as follows:

Cause:The given data block was corrupted,probably due to program errors

Action:Try to restore the segment containing the given data block,This may involve dropping the segment and recreating it,If there is a trace file,report the messages recorded in it to customer support.


ORA-01628:max # of extents num reached for rollback segment num

Why it happens: this error usually occurs when 1 rollback segment and 1 tablespace have reached the limit set by the MAXEXTENTS parameter. Note that this MAXEXTENTS is not a hardware limit for the rollback segment or tablespace, the hardware limit depends on the value of the DB_BLOCK_SIZE parameter specified in the init.ora file at the time the database was created.

Solution: use the SQL command ALTER TABLESPACE... STORAGE (MAXEXTENTS XXXX) to increase MAXEXTENTS, of which the "XXXX value must be greater than the error message" referred to in Numbers, but not greater than LARGEST MAXEXTENT value, if you have already reached the LARGEST MAXEXTENT VALUE, the solution is to create a larger range of sizes, with options COMPRESS = Y Export tools export table, if there is space available table space, make a backup to the table first, Change its name with alter tablespace tablespace_name, and then load the table back to the database.

Look at where the error occurs, if it occurs on a rollback segment or index, it must be dropped and rebuilt, if it occurs in a temporary table space, modify the storage fields in the temporary table space, and you can fix the problem.

One example of error reporting is as follows:

ORA-1628:max # extents 50 reached for rollback segment RBS_1

The corresponding English is as follows:

Cause: An attempt was made to extend a rollback segment that already has reached its maximum size or space could not be allocated in the data dictionary to contain the definition of the object.

Action:If possible,increase the value of either the MAXEXTENTS or PCTINCREASE initialization parameters or find the data dictionary table lacking space and alter the storage parameters,as described in the Oracle8 Server Administrator's Guide.


Related articles: