Oracle ORA-00604 error Case Study

ORA-00604 error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables ).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle customer support.

ORA-00604: An error occurred while recursion a SQL Layer

Cause: an error occurs when you run a recursive SQL statement that applies to operations on internal tables or data dictionaries.

Solution: If the stack where the preceding error is located can be repaired, fix it and continue running. Otherwise, contact Oracle customer service. Of course, that is the official solution of oracle. I once remember a master SUMMARY OF THE ORA-00604/ORA-04031 problem to solve:

Modify init. ora

Add

_ Db_handles_cached = 0
 

And restart the database.

Analysis: ORA-00604 information indicates an error occurred when the database executes an internal SQL statement. For example, if you want to insert a row of data into the table, there is no scalable space. Oracle went to search where to create the next extended space. It had many sizes but failed. Generally in the case of ORA-00604 errors, but also with other errors, such as: ORA-1547.

First, check the warning file alertsid. log to find information about the ORA-600 class.

The most common cause of this error is that the value of the open_cursors parameter in the database file initsid. ora is too small. You can modify the initsid. ora file. The value of open_cursors is generally 255. After modification, go down to Oracle and restart.

You can also set and start the database event tracking function. Add the following line to initsid. ora:

Event = "00604 trace name errorstack"
 

And restart Oracle to make the event tracking parameter take effect. As a result, when a ORA-604 error occurs again, the information is saved in the trace file.

Other causes of ORA-604 errors may be:

-In initsid. ora, the parameter dc_free_extents or row_cache_enqueues is too low. You can add the values of these two parameters according to the operating system and database conditions, and restart oracle.

-Run out of space (accompanied by ORA-1547 error ). In this case, you need to add a new file to the table space to increase the tablespace size.

-Max_extents reached (with ORA-1556 errors ). In this case, you need to modify the table to allow more extensions. Find the maximum value of max_extents in the Technical Manual. If the maximum value has been reached, you must use the compress extents option to unload the table (export) and import it into the (import) database.

 

Case 1: An error occurred while performing recursive query in Oracle

Problem description: I often encounter ORA-00604 and ORA-01000 (enabling the maximum number of cursors) errors. However, when I check the Code, all result sets and statement objects are closed in the last block (I am using JDBC ). The query I run is an oracle recursive query (starting from this and using this connection ). Can you tell me where the problem occurred and under what circumstances will the above error occur?

Solution: The open_cursors parameter value in the init. ora file is too low. The default value of this parameter is very low (50 ). It should be set to 200 or higher. Even if you disable the result set, but you have not disabled the SQL statement in Java code, this problem will occur.

If yes, make sure that your active connection pool is enabled (for performance reasons); otherwise, set it to No.

Ask your database administrator to monitor the database and check the entries using the V $ open_cursors and V $ sysstat data dictionary views.

 

Case 2: an exp error case

Problem description: the customer's Linux system, RedHat Enterprise Edition (RHEL 3.0). Database, 9ir2 installed, upgraded some time ago. The current version is 9204.

The customer is ready to export exp. There is no space in the system before. Some space is added to the system first. Linux LVM is relatively easy to use. Although the file system uses ext3, it has to be suspended temporarily.

Failed to export. The system reports an error:

EXP-00056: Oracle error 942 encountered
ORA-00942: Table or view does not exist
EXP-00000: Export terminated unsuccessfully
 

Many may be familiar with this error.

Oh, by the way, the customer said that the database has been upgraded. First, I guess there is a problem with the upgrade? After all, I have read a lot of questions about the failure of upgrading on the forum.

Run the $ ORACLE_HOME/rdbms/admin/catpatch. SQL script.

At the same time, make sure to increase the size of the java_pool_size and shared_pool_size parameters. If you do not try again, the time will be delayed. Do not make low-level errors.

SQL> shutdown immediate;
SQL> startup migrate;
SQL> @? /Rdbms/admin/catpatch. SQL
 

View the spool logs. the second compilation error was found and re-executed. wait ...... at this point, I complained that the CPU was not fast enough and the memory was not large enough.

There is no error in this log. You can't expect it... the user connection reports an error:

Error at line 1:
ORA-00604: Error occurred at recursive SQL Level 1
ORA-04045: errors during recompilation/revalidation of lbacsys. lbac_events
ORA-06508: PL/SQL: cocould not find Program unit being called
ORA-06512: At Line 2
ORA-06508: PL/SQL: cocould not find Program unit being called
ORA-06512: At Line 2
 

It is found that connect/As sysdba can still be logged in.

It seems that the status of lbacsys. lbac_events is faulty. Join in. How can I compile it? My wishful thinking is @? /Rdbms/admin/utlrp. SQL is no problem after it is executed. It is useless and the error persists. At that time, I felt dizzy. This system has not been backed up yet. It seems a little troublesome (I am complaining about the customer and never let the backup happen. I always say "wait a moment ", as a DBA, it is also sad that you do not pay attention to it, right? Although I have stolen a backup, I still did it during the last upgrade.) Check the Metalink on the Internet. The network speed is good here. lbacsys. lbac_events is used as a keyword to find the following information:

The reason for this problem seems to be an upgrade for label-Security
Even if it's not installed. // The label security is not installed. Why is the patch updated by default?
 

Solution:

Shutdown immediate;
Startup migrate;
Alter view lbacsys. lbac $ all_table_policies compile;
Alter package lbacsys. lbac_events compile body;
Shutdown immediate;
Startup;
 

The support staff said this was a bug. but common users are invisible. don't worry, look for it again. The maillist at suse.com also found a similar case. It seems that it is okay and I have a bottom in my mind.

Follow the preceding steps to re-check and OK.

To sum up

It is actually a very technical case. First, you should at least test whether export is available during the previous upgrade (export has become a sign of successful upgrade !) Second, the preparation was not enough, and it was too busy. fortunately, it is not a key system that users can tolerate. oracle always said that Microsoft is a bad company, but they are really enough. there are many bugs.

 

Case 3: recursive SQL errors when using network applications

Problem description: The following error occurs when I use a network application.

ORA-00604: an error occurs when recursive sql1 level.

ORA-04031: unable to allocate 4200 bytes of shared memory, "rbks_bk_info", "sga_heap", "library cache ".

What do these error messages mean? How can I solve them? Are they in applications or databases?

Solution: You should use ORACLE versions earlier than Oracle 8.1.7.4. The first error message tells you that the SQL statement executed by Oracle against your behavior fails. ORA-4031 tells you why it fails. ORA-4031 error message means you didn't get enough free space. You can add your shared_pool_size and restart the database again. This bug has been fixed in the subsequent patch package. If you are not using this version, you can apply the patch package.

 

Case 4: Oracle diagnosis using SQL _trace

Problem description: many times, when we perform database operations, such as drop user and drop table, we often encounter such errors.

ORA-00604: Error occurred at recursive SQL Level 1.

This kind of reminder is often useless. This case provides an idea and method for your reference.

1. Drop user problems

Exit after the following error is reported

ORA-00604: Error occurred at recursive SQL Level 1

ORA-00942: Table or view does not exist.

It is necessary to briefly describe recursive SQL errors.

We know that when we issue a simple command

Oracle Database should parse this command in the background and convert it into a series of background operations for Oracle database.

These background operations are collectively referred to as recursive SQL.

For example, a simple DDL command such as create table, Oracle database in the background, in fact, to convert this command to insert operations for the underlying table such as OBJ $, Tab $, Col $. oracle's work may be much more complicated than we sometimes think.

2. Tracking Problems

We know that Oracle provides the SQL _trace function.

It can be used to track the background recursive operations of Oracle databases.

Through the tracking file, we can find the problem

The output after formatting (tkprof) is as follows:

The following statement encountered a error during parse:
Delete from sdo_geom_metadata_table where sdo_owner = 'wapcomm'
Error encountered: ORA-00942
 

Oracle first presents the error information, we see that the ORA-00942 error is caused by the sdo_geom_metadata_table/view does not exist, the problem can be located.

For this type of errors, the solution to the problem after the problem is located depends on the specific cause of the problem.

3. Problem locating

In this case, use Metalink to get the following explanation:

Problem description
The Oracle Spatial option has been installed and you are encountering
The following errors while trying to drop a user, who has no spatial tables,
Connected as system:
Error at line 1:
ORA-00604: Error occurred at recursive SQL Level 1
ORA-00942: Table or view does not exist
ORA-06512: At line 7
A 942 error trace shows the failing SQL statement:
Delete from sdo_geom_metadata_table where sdo_owner =''
Solution description
 

(1)

Create a synonym for sdo_geom_metadata_table under system which points
MDSYS. sdo_geom_metadata_table.
 

In this example, you can create a synonym for MDSYS. sdo_geom_metadata_table, which is relatively simple.

(2)

Now the user can be dropped connected as system.
Related Documents
ORA-604 and ORA-942 reported during drop user Casca
 

4. Actual handling

MDSYS. sdo_geom_metadata_table is a spatial object.

If the spatial option is not used, delete it.

SQL> connect/As sysdbaconnected.
SQL> select * From dba_sdo_geom_metadata order by owner;
Select * From dba_sdo_geom_metadata order by owner
*
Error at line 1:
ORA-00942: Table or view does not exist
ORA-04063: View "MDSYS. dba_sdo_geom_metadata" has errors
SQL> select object_name from dba_objects where object_name like '% SDO % ';
Object_name
All_sdo_geom_metadata
All_sdo_index_info
All_sdo_index_metadata
Dba_sdo_geom_metadata
Dba_sdo_index_info
Dba_sdo_index_metadata
....
Dba_sdo_geom_metadata
Dba_sdo_index_info
...
Sdo_within_distance
User_sdo_geom_metadata
User_sdo_index_info
User_sdo_index_metadata
88 rows selected.
SQL> drop user MDSYS cascade;
User dropped.
SQL> select owner, type_name from dba_types where type_name like 'sdo % ';
No rows selected
SQL>
SQL> alter session set SQL _trace = true;
Session altered.
SQL> drop user wapcomm;
User dropped.
SQL> alter session set SQL _trace = false;
Session altered.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0-64bit Production
With the partitioning Option
Jserver release 8.1.7.4.0-64bit Production
 

In this case, the user can smoothly drop

5. Summary

SQL _trace can be used to track many background operations of the database, which is helpful for us to find out the problem. In many cases, we want to study the internal activities or background operations of Oracle, or through SQL _trace, SQL _trace/10046 is one of the most effective diagnostic tools provided by Oracle.

 

Case 5: recursive SQL 2 failure error during table update

Problem description: The table failed to update and a ORA-00604 error message was generated. This error occurs at level 2 of recursive SQL.

Solution: Unfortunately, this error does not tell you what to do when an Oracle database error occurs. When you execute an SQL statement, Oracle database will do a lot of things behind the scenes. For example, consider the following SQL statement:

Update EMP set sal = Sal * 1.05 where empno = 1001;
 

This SQL statement increases the employee's salary by 1001 for 5%. When you execute this statement, Oracle queries the data directory to determine whether the table exists or whether you use the synonym. Once it finds the database object, Oracle queries the data dictionary to determine whether you have the permission to access this object. So how does Oracle interact with data dictionaries? It executes an SQL statement of its own. These SQL statements that Oracle executes for you are called "recursive" SQL statements. Your initial SQL statement is 0. The recursive SQL statement that Oracle executes for you is Level 1. Sometimes, a recursive SQL statement may cause its own recursive SQL statement, which is Level 2.

In your case, a 2-level Recursive SQL statement is being executed and has encountered problems. To solve the problem, you need to find out the errors caused by recursive SQL statements. To do this, you must start tracing in the session. First, execute the following SQL statement:

Alter session set SQL _trace = true;
 

Then, execute your update statement. You will see a ORA-604 error. Next, execute the following statement:

Alter session set SQL _trace = false;
 

Now, go to the path on the starting parameter user_dump_dest that you have defined for the database. There should be a file with the current timestamp. That is the Tracing file you generated. You can open the file and check recursive SQL statements, including the one that causes the error. (Close to the end of the Tracing file ).

 

 

Case 6: ORA-00604 error when connecting database user

Problem description: When I tried to connect to a database user, I got the following error message: ORA-00604: recursive SQL Level 1 error. However, if I use the database administrator role, the user can connect to the database. System users can connect, but Scott cannot connect.

Solution: Oracle has done a lot of work behind the scenes for you. It performs this work throughout the entire process of its own SQL statements. Any SQL statement released to you by Oracle is a "recursive SQL" statement. There should be a lot of SQL statements that may cause problems. What I suggest you do is set SQL _trace = true in the init. ora file and restart the database. Then copy the ORA-604 error. This will generate a large number of tracing files for all user processes in your user_dump_dest directory. When an error occurs, immediately shut down the database and set SQL _trace to false. Then start the database again. Now with the trace file, you can find the ORA-604 error information in the trace file generated in the user_dump_dest directory. That's where you'll find out which recursive SQL statement produces a ORA-604 error and the actual error. Your solution depends on statements and actual errors.

 

Case 7: someone moved the dependencie $ table and crash.

Today, someone asked me if I could recover from this problem. I don't think I can. in the open times ORA-01092 number error, I checked the event does not have this appropriate event ah, I recommend not to use a full recovery, but it seems that there is no backup, run in noarchivelog mode.

Content obtained from the TRC file:

Kcra: buffers claimed = 0/0, eliminated = 0
ORA-00704: bootstrap process failure.
ORA-00604: Error occurred at recursive SQL Level 1
ORA-01502: Index 'sys. I _dependency1' or partition of such index is in unusable state
Oerr ora 704.
00704,000 00, "bootstrap process failure"
// * Cause: Failure in processing Bootstrap data-see accompanying error.
// * Action: contact your customer support representative.
The trace generated when SQL _trace is Enabled:
Parsing in cursor #9 Len = 84 Dep = 2 uid = 0 Oct = 3 lid = 0 Tim = 18446744073254091198
HV = 2287793623 ad = '66f6c06c'
Select O. Name, U. name from OBJ $ o, user $ u where O. OBJ # =: 1 and O. Owner # = U. User #
End of stmt
Parse #9: C = 0, E = 343, P = 0, Cr = 0, Cu = 0, MIS = 1, r = 0, DEP = 2, OG = 0, tim = 18446744073254091193
Exec #9: C = 0, E = 186, P = 0, Cr = 0, Cu = 0, MIS = 0, r = 0, DEP = 2, OG = 4, tim = 18446744073254091456
Fetch #9: C = 0, E = 28019, P = 2, Cr = 5, Cu = 0, MIS = 0, r = 1, DEP = 2, OG = 4, tim = 18446744073254119501
Stat #9 Id = 1 CNT = 1 pid = 0 Pos = 1 OBJ = 0 op = 'nested loops'
Stat #9 Id = 2 CNT = 1 pid = 1 Pos = 1 OBJ = 18 op = 'table access by index rowid OBJ #(18 )'
Stat #9 Id = 3 CNT = 1 pid = 2 Pos = 1 OBJ = 36 op = 'index unique scan OBJ #(36 )'
Stat #9 Id = 4 CNT = 1 pid = 1 Pos = 2 OBJ = 22 op = 'table access cluster OBJ #(22 )'
Stat #9 Id = 5 CNT = 1 pid = 4 Pos = 1 OBJ = 11 op = 'index unique scan OBJ #(11 )'
ORA-00704: bootstrap process failed
ORA-00604: recursive SQL Layer 1 error
ORA-01502: Index 'sys. I _dependency1' or the partition of this type of index is unavailable
Exec #1: c = 109375, E = 5578667, P = 44, Cr = 616, Cu = 1, MIS = 0, r = 0, DEP = 0, OG = 4,
Tim = 18446744073255895570
Error #1: Err = 1092 Tim = 23012387
 

DBAs must be careful in their work, and must review the review during batch processing.

 

Supplement:

Later, I used anysql unloader to restore the data, and spent 24 hours with the customer. Finally, they said OK.

Eygle and chensq have also studied this problem. They have come up with a better solution to this problem, but in the end, the original library must be unavailable and exp/IMP must be transferred to other libraries, I used aul to help customers restore data, with a data volume of over 30 GB.

 

Case 8: ORA-00604: errors produced by recursive SQL

Problem description: I have a pro * C program that sometimes gives the following error information:

ORA-00604: errors generated at recursive SQL Level 1

Can you tell me why, when, and possible solutions?

Solution: No matter when you execute a query, the system will execute some queries in the background to determine many things. For example, "Do you have the permission to execute this query ?", "Do you want to access this object ?". The queries executed by these systems are called "recursive SQL ". Sometimes, a recursive SQL statement needs to call its own recursive SQL statement. Then these recursive SQL statements are at another level, level 2.

You will not see recursive SQL statements in SQL * Plus. The best way to view them is to enable tracing in the session. Start SQL * Plus and execute the following commands:

Alter session set SQL _trace = true;
 

Then run your process until it crashes. Continue and disable SQL * Plus. Now, go to the user_dump_dest directory. A Tracing file will be generated for you. View information about ora errors in the Tracing file. This is the root cause of the problem. Correcting a ora error prevents a ORA-600 error from reappearing.

Most ORA-600 errors can be corrected by logging on to sys and running catalog and catproc from ORACLE_HOME/rdbms/admin.