Analysis and resolution of common ORACLE error codes (ii)

2020-05-06 11:51:00
OfStack
                    The ORACLE tutorial you are looking at is: analysis and resolution of common ORACLE error codes (ii). Es2en-01578 :Oracle data block corrupted(file # num,block # num)

Cause: when ORACLE accesses a data block, due to 1. Hardware I/O error; 2. Operating system I/O error or buffering problem; 3. Memory or paging asks 

The topic; 4. ORACLE failed to access an unformatted system block; 5. One of the above situations, such as partial overflow of data files, caused a bad logical block or 

Physical block, this will report ORA-01578 error. 

Solution: because ORACLE only reports errors when accessing the data file in question, it is possible that the error will be reported later than the actual time of the error, such as 

If the ORA-01578 error message indicates that the data block points to the user's own data file, the following solution is used: 

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

SQL>Select owner,segment_name,segment_type from dba_extents where file_id= and    between block_id and 

block_id + blocks - 1; (and  are the file number and block number of the bad block reported by ORA-01578, respectively)

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 perform one or two more executions to help identify the data block as permanent (hard disk 
)
Physical bad blocks on) or random (caused by memory or hardware errors) : 

SQL>Analyze tablevalidate structure cascade;

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

Es62en-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 with memory, 

page space is related to I/O devices. 

If the user has an up-to-date backup 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: 

.close 
 first

Edit init.ora file, add: 

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

.startup restrict

Create a temporary table: SQL>create table errortemp as select * from error; (error is the name of the bad table)

Delete event from init.ora and restart database 

.rename bad table, make the temporary table rename the name of the bad table 

.create 
 such as INDEX on the table

If the ORA-01578 error message indicates that the data block points to a data dictionary or a rollback segment, you should contact ORACLE immediately to discuss an 

Good solution. 

The solution described here is only one of the more common ones. For more specific solutions, you can check out the troubleshooting manual for ORACLE, which contains chapters and 

The ROWID method to fetch data other than a bad block is not covered here. 

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 typically occurs when a rollback segment and a tablespace have reached the limit set by the MAXEXTENTS parameter. Note that this MAXEXTENTS is not 

Is the hardware limit for the rollback segment or tablespace, and 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, where the "XXXX" value must be greater than 

The value indicated in the error message cannot be greater than LARGEST MAXEXTENT, if it has reached LARGEST MAXEXTENT VALUE, the solution is 

To recreate a larger range size, export the table using the Export tool with the option COMPRESS=Y. If the table space is available, make a standby 
 for the table
Copy, change its name with alter tablespace tablespace_name, and then load the table back to the database. 

See where its errors occur, if they occur on a rollback segment or index, they must be dropped and rebuilt, if they occur on a temporary table space, modify the temporary table empty 

This problem can be solved by storing the fields between. 

An example of error reporting is 

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.