The Oracle tutorial you are looking at is: analysis and resolution of common Oracle error codes.
In the process of using Oracle, we will often encounter some Oracle errors, for beginners, these errors may be a little fuzzy, and may not know how to deal with the resulting errors, I used to appear in the more frequent error code one by one to make analysis, Hopefully it will help you find a reasonable way to solve these mistakes, and hope you can put forward your different opinions. After all, as a means of communication, personal opinions unavoidably too biased, and there must be deficiencies, mistakes are inevitable. The purpose of writing this article is to promote and make progress together through mutual communication.
Cause: The Oracle error described above is caused by a lack of space in the rollback segment, which is also the most common Oracle error message for Oracle data administrators. This error occurs when the user is doing a very large data operation that causes the existing rollback segment to be insufficient so that the allocated rollback segment table space is full and cannot be allocated.
Workaround: Add table space to the specified data using the "ALTER tablespace tablespace_name ADD datafile filename SIZE size_of_file" command, and add one or more tablespaces, depending on the situation. Of course this and also with your host on the bare disk equipment, if you host the bare disk device has no extra use space, suggest you do not lightly increase the size of the rollback segment table space, you can use the following statements to query the remaining tablespace space how many:
If the extra space is more, you can appropriately append a large rollback segment to the table space to use, so as to avoid the above errors. You can also use the following statement to check the rollback segment competitive situation:
If any class in Count/sum (value) is greater than 1%, you should consider adding rollback segment.
The corresponding English is as follows:
Reason: There is not enough space in the Oracle Temporary segment table because Oracle always allocates contiguous space as much as possible, as long as there is not enough unallocated space or the distribution is discontinuous.
Workaround: We know that because Oracle takes tablespaces as logical structures-cells, and the physical structure of tablespaces is data files, data files are physically created on disk, and all the objects in the table space are on disk, the data files must be added in order to add space to the tablespace. Look at the free space in the specified table space, using the view sys.dba_free_space, each record in the view represents the size of the free space fragment:
The returned information can initially determine the maximum block of free space, see if it is smaller than the size mentioned in the error message, and then look at the default table space parameters:
Modify the default storage value for the temporary segment table space by using the following SQL command:
[NextPage]
Appropriately increasing the size of the default value may resolve the error problem, or you can fix the problem by modifying the user's temporary table space size:
Using the Alter TABLESPACE command, once completed, the increased space can be used without exiting the database or taking the tablespace offline, but note that once you have added a data file, you cannot delete it, and to delete it, you delete the tablespace.
An example of an error is as follows:
The corresponding English is as follows:
Reason: When Oracle accesses a block of data, due to 1, hardware I/O error, 2, os I/O error or buffering problem, 3, memory or paging problem, 4, Oracle attempts to access an unformatted system block failed; 5, Part of the data file overflow, such as one of the above situations caused the logical bad block or physical bad block, then will report ORA-01578 error.
Solution: Because Oracle only has access to the problem of the data file will be an error, so the time of the error is likely to be more than the actual errors of time later, if the ORA-01578 error message indicates that the data bad block points to the user's own data files, the following methods to solve:
If the bad block detected by the following SQL statement appears on the index, you can simply rebuild the index
(and the file number and block number of the bad blocks that were reported by ORA-01578)
If the bad block appears on the table, first use the following statement to analyze whether it is a permanent bad block (one or two more are recommended) to help identify the data bad block is permanent (physical bad block on the hard disk) or random (caused by memory or hardware errors):
After you execute this command, you may receive the following results:
ORA-01578: Has the same parameter as the original error message, is a permanent physical or logical bad block, has different parameters than the original error message, and may be related to memory, page space, and I/O devices.
If the user has the latest backup of this table, it is best to use this backup to restore the table, or to use event 10231来 to remove data other than the bad block:
<1> Close the database first
<2> Edit Init.ora file, add:
<3>.startup Restrict
<4> Create a temporary table: Sql>create table Errortemp as SELECT * from error; (Error is table name of bad table)
<5> Remove the event from the Init.ora file and restart the database
<6>.rename bad table, rename the table name of the temporary table into a bad table
<7> CREATE index on a table, etc.
[NextPage]
If the ORA-01578 error message indicates that the data bad block points to a data dictionary or a rollback segment, you should immediately contact Oracle to discuss a good solution.
The solution here is only a more common one, and some more specific solutions can look at Oracle's troubleshooting manual, which contains Zhuo Shui and methods for using the rowID method to remove data outside of the bad block, as described here.
The corresponding English is as follows:
Cause: This error is usually a rollback segment and a table space has reached the limit set by the maxextents parameter. Note that this maxextents is not the hardware limit for the rollback segment or table space, and the hardware limit depends on the value of the Db_block_size parameter specified in the Init.ora file when the database was created.
WORKAROUND: Use SQL command alter tablespace ... STORAGE (maxextents xxxx) to increase the maxextents, where the "XXXX" value must be greater than the value indicated in the error message, but not greater than the largest maxextent if it has reached largest maxextent VALUE, the solution is to recreate the larger scope size, using the Export tool with the option compress=y, if the table space has free space, first make a backup of the table, change its name with alter Tablespace Tablespace_name , and then mount the table back to the database.
To see where the error occurred, if it appears on the rollback segment or index, you must delete and rebuild it, and if it appears in the temporary table space, modify the storage field in the temporary tablespace to resolve the problem.
An example of an error is as follows:
The corresponding English is as follows:
[NextPage]
Reason: This error is usually an internal error of Oracle and is only useful for OSS and Oracle development. ORA-600 errors are often accompanied by a state dump of the trace file (System State and process State), the system state store will include information about the current object held by the Oracle RDBMS, and the process state dump will show the objects held by the special process, and when the process conforms to an error condition, Often because some information is taken from a block that it holds, it is easy to trace the source of the problem if we know the block the error process holds.
Workaround: In general, this error can not be solved by itself, only by improving the system itself to solve this internal problem, such as adding hardware devices, adjusting system performance, using OPS (of course, OPS is not a good solution in a sense) and so on. 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), displaying additional information from the second to fifth variable, telling the OSS code where an error occurred.
An example of an error is as follows:
corresponding to the
[1] [2] Next page
The Oracle tutorial you are looking at is: analysis and resolution of common Oracle error codes. English is as follows:
Time-outs (timeout)
File corruption (files too old)
Failed data checks in memory (memory retrieval failed)
Hardware,memory,or I/O errors (Hardware, memory, or disk error)
Incorrectly restored files (wrong rebuild file)
Ora-03113:end-of-file on communication channel
Cause: Communication is terminated abnormally, resulting in communication channel termination
Solution: 1> Check if there is a service process abnormal panic, can be learned from the Alert.log
2> Check sql*net driver is connected to an Oracle executable
3> Check that the server network is normal, such as network or unstable
4>: Check if there are two nodes with the same name on the same web
5>: Check if there is a duplicate IP address on the same web
The corresponding English is as follows:
[NextPage]
Reason: This is because the loaded table or view does not exist, most of the catexp.sql is not yet running, the export view cannot be performed, and if Catexp.sql is already running, it may be a version error.
Workaround: Because some views of import and export sharing are loaded by running Catexp.sql (they have the same view) and do not generate separate catexp.sql, resulting in a different view from the export code, making it difficult to maintain compatibility with each other, users must build their own expo RT application, so as to avoid ORA-00942 errors.
The corresponding English is as follows:
All of the above errors are common problems when using rollback segments, ORA-01598 indicate that the status of the rollback segment currently in use is not online and cannot be used; ORA-01636 indicates that the current rollback segment is already online State, which can be used directly without having to set it again.
We may also encounter the following problems when doing statistics: a rollback segment State is "Needs Recovery", which is caused by the failure of Oracle to return a list of things that have not been submitted. Usually the reason for a datafile or tablespace is to be destroyed in the state of offline or a goal of undo or rollback segment destroyed. The solution is to put all the tablespace and datafile into the online state, if not resolved, do the following work:
1>. Add event= "10015 Trace name Context Forever lever 10" in Initsid.ora;
2>.shutdown database and reboot;
3> under $oracle_home/rdbms/log, trace file generated at startup is found.
4>. In the trace file, locate the following information "Error recovery TX (#,#) object #";
5>: The name of the object is identified in the Sys.dba_objects table according to the object# (same as object_id in the Sys.dba_objects table);
6> Drop the object off;
7>: In the Init.ora file, put the rollback segment back in the rollback_segments parameter, delete the Event;8>.shutdown database and restart. At this time "Needs Recovery" problem should be completely solved, otherwise it is rollback segment was destroyed.
Reason: The specified tablespace space has been occupied and cannot be extended.
Workaround: Add File System files and original partitions using the ALTER tablespace add datafile command, or increase the size of the initial (for example, alter tablespace CDRS101 default storage (next 500M Pctincrease 1) should be able to resolve otherwise someone using your tablespace creates a larger data file that causes your tablespaces to be out of space.
An example of an error 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
prev [1] [2]
