Oracle 8i RAC database common error diagnostics highlights ORA-1652 to ORA-1655
Simply put, the ORA-165X error message means that the tablespace does not have enough space to allocate, so an error is reported. These errors include a ora-1652, A ora-1653, A ora-1654, and a ora-1655.
ORA-165X errors may be the most common ORACLE error message during routine maintenance, and you need to understand the environment and conditions that the problem is generated when resolving these errors. In fact, ORACLE uses tablespaces as logical structural units, while the physical structure of tablespaces is data files. Data files are physically created on the disk, and all objects in the tablespace are stored on the disk. To increase space for a tablespace, you must increase the size of the data file or the size of the original database file.
When the o r a-1 6 5 X error message is generated, the problem may be that the specified tablespace lacks allocable space. The error message provides two parameters: s I Z E, prompting the maintenance personnel that the system does not find the number of Oracle blocks, while TABLESPACE prompts the database administrator to need the TABLESPACE. Oracle always tries its best to allocate continuous space. Although sometimes the tablespace may have enough allocable space, if the space is not continuous, the error will still occur.
Sometimes, when we insert a table, we get an error of insufficient space for other tablespaces (that is, the tablespace not included in the table to be inserted, this is because the data dictionary, rollback segment, and temporary segment of the system may be used for insert and other operations. The following objects may have insufficient space.
(1) Data Dictionary
If the data dictionary object needs to be extended but the s y s t e m tablespace does not have enough space available, the o r a _ 1 6 5 x error will occur. In this case, the o r a _ 6 0 4 error occurs before the ORA_165x error occurs. For example, to create a table to expand t a B $ and S Y S T E M tablespace does not have enough space, the created table will get the error o r a-6 0 4 and o r a _ 1 6 5 x.
(2) tables and Indexes
If extra space is required to meet the insertion or modification of an object, the o r a _ 1 6 5 x error will occur. If this error occurs when a table or index is created, the specified storage parameter or tablespace default storage parameter needs to be modified.
(3) rollback segments
If the error occurs in the rollback segment, the o r a-1 6 5 0 error always occurs before the o r a-1 6 5 x error. O r a-1 6 5 0 indicates that the rollback segment cannot be extended. The error of o r a-1 6 5 x indicates that the reason is the lack of space. O r a-1 6 5 0 error message:
ORA-1650 "unable to extend rollback segment"
(4) Temporary Section
The temporary segment is created by the Oracle kernel, which indicates that the user completes a sorting operation. Operations performed BY the user (such as creating an index, executing a query with an order by field or a long connection statement) may result in insufficient temporary segments (usually created in temporary tablespace.
For more information about how to expand a tablespace, see how to expand the tablespace section in this article.
ORA-01555 (snapshot too old)
Ora-01555 error is a common error in the big data environment of the telecom industry. This error causes the application to fail. For example, this error may stop a batch processing task that runs late at night and then cause other tasks dependent on the task to fail. This prevents users from obtaining the required information in a timely manner (for example, the report is not printed, and the data is not exported ). Although this error usually occurs in large tasks, it also occurs in small tasks.
ORA-1555 is usually an accidental error. Sometimes, after this error occurs, re-running the task may not encounter similar errors. This error does not occur immediately. A task that has been running for a long time may have been running for several hours before it fails ). Simply re-running the task does not guarantee its success. It may still fail after running for a period of time.
The root cause of ORA-1555 errors is that oracle needs to ensure read consistency. Read consistency means that when multiple users modify rows in a data block, these blocks change to "dirty" or are in change until they are confirmed. Before being confirmed, they are visible to all statements in the transaction, but invisible to other transactions or statements. Once confirmed, all subsequent transactions or statements are visible. But the statement before the transaction is confirmed cannot see the modifications, because these modifications have not yet occurred.
For example, the transaction T 1 (such as the exp operation on a large table) starts at 2 2: 0 0 and the transaction T 2 (such as the update operation on the same large table) starts at 2 2: since T 1 needs to traverse a large table, it takes a long time to read the table, T 2 may update data in the same table based on indexes. In this way, T2 may be completed in a few seconds, and T 1 may take a long time to run, assuming 4 0 minutes. When T 1 reaches the location where T 2 has been modified (a new change can be identified based on the current s c n timestamp), even though the write done by T 2 has been confirmed, however, to ensure read consistency, it does not read the modified data. It only accesses data at 2: 0, at 2: 2: the changes made when 0 is 1 cannot be read. T 1 reads the data before the change from the rollback segment to ensure read consistency. However, because the transaction T2 has been committed, oracle considers that the rollback segment used by the T2 transaction can be reused. When the rollback segment is too small or the transaction is relatively dense, oracle may overwrite the rollback segment of the original T2 transaction with a new transaction. When the T1 transaction reads data modified by T2, the data before the modification cannot be found from the rollback segment, then the ORA-1555, snapshot too old error will be reported.
In 9i, because undotablespace is introduced, as long as the undotablespace is set to be large enough and the undo_retention is set to be large enough (greater than the time required to complete a large firm ), it is possible to completely solve the ORA-1555 error (but too much undotablespace will cause system performance degradation ).
ORA-04031
Ora-4031 errors are another common oracle error. For more complex applications, the size of the shared pool is very important for Oracle performance. The Shared Pool stores data dictionary high-speed buffering and fully resolved or compiled PL/SQL blocks and SQL statements. When we try to allocate large blocks of continuous memory in the Shared Pool, Oracle first refreshes all the objects currently not used in the pool to merge idle memory blocks. A ORA-04031 error occurs if the request is still not met by a large enough single block of memory. When this error occurs, you get the following error message:
Error: ORA 4031
Text: unable to allocate % s bytes of shared memory (% s, % s, % s ).
The following initialization parameters will affect the occurrence of this error:
Shared_pool_size
The Shared pool is composed of three parts: Dictionary cache (including definition of data Dictionary, such as table structure and permission), Library cache (including Shared SQL cursor, the original SQL code, execution plan, stored procedure, and session information), and Control structure. Its size is controlled by the initialization parameter shared_pool_size. Its function is to cache the parsed SQL statements so that they can be reused and no longer need to be parsed.
Shared_pool_reserved_size
It is used to reserve a region in the shared pool to place some large objects (such as large software packages). If this parameter is not set, by default, the system retains 5% of the shared pool space for storing large objects.
Shared_Pool_reserved_alloc
The value of this parameter controls the reserved memory allocation. If a large part of the Shared Pool cannot be found in the idle list, the memory will be allocated a larger space from the Reserved List. The default value is sufficient for most systems. If you increase the value, the Oracle server will allow less allocation from the Reserved List and request more memory from the shared pool list. This parameter is an implicit parameter after Oracle 8i.
When a ORA-04031 error occurs, consider adding the values of the preceding three parameters, especially shared_pool_size. However, if the value of shared_pool_size is already large (greater than 350 M ), the cause of the ORA-4031 error is probably because of oracle Database bugs or applications failing to use bind variable well ). In this case, you should update the database with the latest patch or adjust the application (Use as many bind variables as possible). Simply increasing the size of shared_pool cannot solve the problem.
You can also use the following SQL to query shared_pool usage, pay special attention to the request_failures value, if this value is not 0, it means that the ORA-4031 error may be caused by too many fragments in shared_pool.
SELECT free_space, avg_free_size, used_space,
Avg_used_size, request_failures, last_failure_size
FROM v $ shared_pool_reserved;
In this case, the shared_pool can be refreshed. Log on to sqlplus as a sys user.
SQL> alter system flush shared_pool;
Note:
This command clears the current shared_pool to temporarily solve the problem of ora-4031 errors, but clearing the shared_pool will cause temporary database performance degradation. If error 4031 is caused by oracle bugs or poor application writing, refreshing shared_pool constantly is not a good solution.
6.5 ORA-600
A ORA-600 is a serious error that is an oracle internal error that records the error information ORA-600 in the warning log and trace file, and typically the database generates a core file or trace file. As follows:
Errors in file C: \ oracle \ ora81 \ RDBMS \ trace \ stomedSMON. TRC:
ORA-00600: internal error code, arguments: [4136], [0], [], [], [], [], [], [], []
ORACLE Instance stomed (pid = 6)-Error 600 encountered while recovering transaction (2, 48 ).
Weds Feb 25 12:54:38 2004
Errors in file C: \ oracle \ ora81 \ RDBMS \ trace \ stomedSMON. TRC:
ORA-00600: internal error code, arguments: [4136], [0], [], [], [], [], [], [], []
The ORA-600 is an internal error generated by the Oracle kernel when basic normal check fails, such as an attempt to read a disk that does not exist in SYS. FET $. Unlike other Oracle errors, any two ORA-600 errors cannot point to the same error state. The ORA-600 represents a variety of internal errors. In addition, ORA-600 errors may also come along with other ORA errors (for example, when the system encounters bad blocks, a ORA-1578 error will also occur ). When a ORA-600 error is displayed, parameters (argument) enclosed in square brackets are often followed ). Based on the actual internal error, these parameters represent different events. In addition, different Oracle versions, these parameters represent different events. However, there is no specific documentation to describe this. Generally, you can only ask senior analysts from the Oracle Technical Support Department to explain this. In general, the first parameter provides the actual error information (for example, 1579 indicates a block interruption error), while other parameters provide other information (for example, in a block interruption error, displays the number of blocks to be read by Oracle and the number of actually read blocks ).
In general, ORA-600 errors cause the system to generate one or more trace files that provide important diagnostic information. Before handling such errors, you should try to learn more about the errors, such:
• What is the exact information or parameter (especially the first parameter) When an error occurs.
• Does the system no longer respond during the error.
• Whether the system performance is negatively affected during the error period
• The number of active tasks (active sessions or processes) during the error period ).
• Which DML/DDL statements are run for these activities.
• Are there any background or foreground processes killed during the error.
• Whether any unknown hardware or operating system faults have occurred (a note of all OS Patches may be required
).
• How many trace files are generated by the system (these files may have to be sent to the Oracle Technical Support Department ).
Typically, ora-600 errors should be presented to oracle for analysis, but many times we can analyze ora-600 errors ourselves to speed up processing. The analysis method is to put the error information of the ora-600 to metalink (metalink.oracle.com is the Technical Support site of oracle, there are a lot of documents and oracle problem handling cases, and provide powerful query function). For example, on metalink, enter:
ORA-00600 [4136], [0], [], [], [], [], [], []
Then, we can find many descriptions and cases of similar errors. Based on these information, we may also find out the real cause of the error and obtain the correct solution.
