Analysis and Solution of common Oracle error codes
Analysis and Solution of common Oracle error codes
The ORACLE tutorial is to analyze and solve common Oracle error codes.
When using ORACLE, we often encounter ORACLE errors. For Beginners, these errors may be a bit vague and may not know how to handle these errors at the moment, I will analyze frequently used error codes one by one, hoping to help you find a reasonable solution to these errors, and hope you can put forward your different opinions. After all, as a means of communication, personal opinions are inevitably too biased, and there must be deficiencies, making mistakes inevitable. The purpose of this article is to promote and make progress together through mutual communication.
Cause: the preceding ORACLE error is caused by insufficient tablespace In the rollback segment, which is also the most common ORACLE error message of the ORACLE Data Administrator. When a user is performing a very large data operation, resulting in insufficient existing rollback segments, the table space of the available rollback segments is full and cannot be allocated, the preceding error occurs.
Solution: run the "alter tablespace tablespace_name add datafile filename SIZE size_of_file" command to ADD a TABLESPACE to the specified data. You can ADD one or more tablespaces as needed. Of course, this is also related to the bare disk device on your host. If there is no additional space for the bare disk device on your host, we recommend that you do not increase the size of the tablespace In the rollback segment. You can use the following statements to first query the remaining tablespace space:
If the extra space is large, you can append a large rollback segment to the tablespace to avoid the above errors. You can also use the following statement to check the rollback segment competition:
If the value of any class in count/sum (value) is greater than 1%, you should consider adding a rollback segment.
The corresponding English is as follows:
Cause: the tablespace in the temporary ORACLE segment is insufficient, because ORACLE always tries its best to allocate continuous space. This problem occurs when there is not enough allocable space or the allocation is not consecutive.
Solution: ORACLE uses the tablespace as a logical structure-unit, while the physical structure of the tablespace is a data file, which is physically created on the disk, all objects in the tablespace also exist on the disk. To add space to the tablespace, you must add data files. First, check the available space of the specified tablespace and use view SYS. DBA_FREE_SPACE. Each record in the view represents the shard size of the available space:
The returned information can preliminarily determine the maximum block of the available space, check whether it is smaller than the size mentioned in the error message, and then check the default tablespace parameters:
Use the following SQL command to modify the default storage value of the temporary tablespace:
[NextPage]
Increasing the size of the default value may solve the problem. You can also modify the size of your temporary tablespace to solve the problem:
After the alter tablespace command is run, the added space can be used without exiting the database or taking the TABLESPACE offline. However, once a data file is added, you cannot delete it. To delete it, you need to delete the tablespace.
An example of an error is as follows:
The corresponding English is as follows:
Cause: When ORACLE accesses a data block, due to 1. Hardware I/O errors; 2. Operating System I/O errors or buffer problems; 3. memory or paging problems; 4. ORACLE failed to access an unformatted system block; 5, part of the data file overflow and other situations such as a cause of the logical bad block or physical bad block, then it will report the ORA-01578 error.
Solution: Because ORACLE Reports an error only when it accesses a problematic data file, the error may be reported later than the actual error time, if the ORA-01578 error message prompts that the bad data block points to the user's own data file, use the following method to solve:
If the bad block found through the following SQL statement has an index, you only need to re-create the index.
(And are the file number and block number of the Bad block reported by the ORA-01578 respectively)
If the bad block appears on the table, use the following statement to check whether it is a permanent bad block (it is recommended to execute it once or twice to help identify whether the bad data block is permanent (the physical bad block on the hard disk) or random (caused by memory or hardware errors )):
After executing this command, the following results may appear:
ORA-01578: has the same parameter as the original error message, is a permanent physical or logical bad block; has a different parameter with the original error information, may be with memory, page space is related to the I/O device.
If you have the latest backup of this table, it is best to use this backup to restore the table, or use event 10231 to retrieve data other than bad blocks:
<1>. Shut down the database first.
<2> edit the init. ora file and add:
<3>. startup restrict
<4>. create a temporary table: SQL> create table errortemp as select * from error; (error is the name of the bad table)
<5>. Delete the event from the init. ora file and restart the database.
<6>. rename bad table: rename the temporary table to the name of the bad table.
<7>. Create Table Indexes
[NextPage]
If the ORA-01578 error message prompts that the bad data block points to a data dictionary or rollback segment, you should immediately contact ORACLE to discuss a good solution.
The solution mentioned here is only a common one. For more specific solutions, you can refer to the ORACLE troubleshooting manual, there are methods for retrieving data other than bad blocks using the ROWID method, which will not be described here.
The corresponding English is as follows:
Cause: this error is usually caused by a rollback segment and a tablespace that has reached the limit set by the MAXEXTENTS parameter. Note that MAXEXTENTS is not the hardware limit of the rollback segment or tablespace. The hardware limit depends on the value of the DB_BLOCK_SIZE parameter specified in the init. ora file when the database is created.
Solution: Use the SQL command ALTER TABLESPACE... STORAGE (maxextents xxxx) to add MAXEXTENTS, where the "XXXX" VALUE must be greater than the VALUE specified in the error message, but cannot be greater than the VALUE of largest maxextent. If it has reached the VALUE of largest maxextent, the solution is to re-create a large range and use the Export tool with COMPRESS = Y to Export the table. If the table space has available space, back up the table first, use alter tablespace tablespace_name to change its name, and then load the table back to the database.
Check where an error occurs. If it appears in a rollback segment or index, you must delete and recreate it. If it appears in a temporary tablespace, modify the storage field of the temporary tablespace, you can solve this problem.
An example of an error is as follows:
The corresponding English is as follows:
[NextPage]
Cause: this error is usually an internal ORACLE error and is only useful for OSS and ORACLE development. ORA-600 errors are often accompanied by a State dump of the trace file (system and process state) where system state storage will include information about the current object held by oracle rdbms, the Process status dump displays the objects held by the special process. When the process meets an error condition, it is often because some information is taken from a block held by the process, if we know the blocks held by these error processes, it is easy to track the source of the problem.
Solution: in general, this error cannot be solved. We only need to improve the system to solve this internal problem, such as adding hardware devices and adjusting system performance, using OPS (of course, OPS is not a good solution in a sense. The first variable of the ORA-600 error is used to mark the location of the error in the Code (the first variable in each part of the code is different), from the second to the fifth variables display additional information, tell the OSS code where an error occurs.
An example of an error is as follows:
Corresponding
[1]
The ORACLE tutorial is to analyze and solve common Oracle error codes. The English version is as follows:
Time-outs (timeout)
File Upload uption (the File is too old)
Failed data checks in memory (memory retrieval Failed)
Hardware, memory, or I/O errors (Hardware, memory, or disk error)
Incorrectly restored files (incorrect reconstruction file)
ORA-03113: end-of-file on communication channel
Cause: the communication ends abnormally, resulting in the termination of the communication channel.
Solution: 1> check whether the server process is abnormal or not. You can learn from alert. log
2>. Check whether the SQL * Net Driver is connected to the ORACLE executable program.
3>. Check whether the server network is normal, for example, the network is disconnected or unstable.
4>. check whether there are two nodes with the same name on the same network.
5>. Check whether duplicate IP addresses exist on the same network.
The corresponding English is as follows:
[NextPage]
Cause: this is because the mounted table or view does not exist. Most of the reason is that CATEXP. SQL is not running and the Export view cannot be executed. If CATEXP. SQL is already running, it may be a version error.
Solution: because some views shared by Import and Export run CATEXP. SQL to load (they have the same view) and do not generate a separate CATEXP. so that the view and Export code is not synchronized, it is difficult to maintain compatibility between each other, the user must establish their own Export application, so as to avoid ORA-00942 errors.
The corresponding English is as follows:
The preceding errors are common issues when using rollback segments. The ORA-01598 specifies that the currently used rollback segments are in the not online status and cannot be used, change it to the online status for use; The ORA-01636 indicates that the current rollback segment is already in the online status and can be used directly without gathering it.
We may also encounter the following problems during statistics: the status of a rollback segment is "Needs Recovery, this is because ORACLE fails to roll back a transaction that has not been committed in a transaction table. Generally, the reason is that a datafile or tablespace is in the offline status, an undo target is damaged, or a rollback segment is damaged. The solution is to set all tablespace and datafile to the online State. If it cannot be solved, do the following:
1>. Add event = "10015 trace name context forever lever 10" to initsid. ora ";
2> shut down the database and restart it;
3>. Under $ ORACLE_HOME/rdbms/log, locate the trace file generated when startup;
4>. In the trace file, find the following information: "error recovery tx (#, #) object #";
5>. Find the object name in the sys. dba_objects Table Based on object # (same as the object_id in the sys. dba_objects table;
6>. drop the object;
7>. In the init. ora file, put the rollback segment back into the rollback_segments parameter, delete the event; 8>. shutdown the database and restart it. In this case, the "Needs Recovery" problem should be completely solved; otherwise, the rollback segment is damaged.
Cause: the specified tablespace is full and cannot be expanded.
Solution: run the "alter tablespace add datafile" command to ADD file system files and original partitions, or increase the INITIAL size (for example, alter tablespace CDRS101 default storage (next 500 M pctincrease 1 )) it should be able to solve the problem. Otherwise, someone will use your tablespace to create a large data file, resulting in insufficient space usage.
An error example is as follows: ORA-1688: unable to extend table RMMCDR. LOCAL_CDR partition LOCAL_CDR101 by 460800 in tablespace CDRS101
The corresponding English is as follows:
Previous Page
[2]
