Solutions to some of the problems in Oracle

oracle| Solve | The problem
Solutions to some of the problems in Oracle

In Oracle management and application, some problems inevitably arise. Typically, Oracle displays error labels and short descriptions, and we can handle the problem based on the information displayed. But sometimes the information is very small and it is a bit cumbersome to handle. In this paper, some problems are discussed, and the solutions are put forward according to some data and experience.



First, ORA-00604 error occurred at recursive SQL level

This information indicates that an error occurred while the database was executing an internal SQL statement. For example, to insert a row of data into a table, there is no space to expand. Oracle then goes to search for where the next expansion space can be built, how large it is, but not successful. In general, when a ORA-00604 error occurs, it is accompanied by other errors, such as: ORA-1547.

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

The most common reason for this error is that the parameter Open_cursors value in the database file Initsid.ora is too small. You can modify the Initsid.ora file, and the Open_cursors value is typically 255. After the modification, the Oracle is down and then restarted.

You can also set up and start event tracking for the database. Add a line to the Initsid.ora:

event = "00604 Trace name Errorstack"

Downtime and restart Oracle to make this event-tracking parameter work. This way, when a ORA-604 error occurs, 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. Depending on the operating system and the database, the values of these two parameters can be appropriately increased, and Oracle will be down and restarted.

-run out of space (accompanying ORA-1547 error). At this point, to add a new file to the tablespace, increase the size of the table space.

-Reached Max_extents (accompanied by ORA-1556 error). If so, modify the table to allow for more extensions. Please find the maximum value of max_extents from the technical manual. If the maximum value has been reached, the table must be unloaded (export) and then imported into the database using the Compress extents option.



Second, ORA-03106 fatal two-task communication protocol error

This information indicates that an error occurred while Oracle was working on network communications. For example, when a client application uses Sql*net to access the server database, the Oracle displays a ORA-03106 error.

First, you should check compatibility between the customer application and the database server, which is the most common reason for ORA-03106 errors. The developer/2000 V1.3 version is now found to be incompatible with Oracle V8.0.5 for Digital UNIX; Oracle V7.0.1.6 for Scounix is incompatible with Oracle V8.0.5 for Digital UNIX Wait a minute. Then check the NLS (character set) compatibility between the customer application and the database server. In the previous years, the Chinese character set on the computer was generally set to zhs16cgb231280, in recent years, the general set to ZHS16GBK, the English operating system under the general settings for US7ASCII. It is best to set the character set to the same type when the system is installed, which also facilitates the unloading and import of data between databases.

If the database link does not work and displays a ORA-03106 error, it may be a sql*net setup issue. To use a database link, the Global_names value in both database file Initsid.ora should be false, and the file Tnsnames.ora on the server should have a database alias for each other, which is the alias used to establish the database link. Especially in the cluster system composed of two machines, people often write only database virtual alias with machine virtual address in Tnsnames.ora, and forget to write the database true alias with machine real address. The database aliases involved in the actual application should be written to Tnsnames.ora.

In addition, the value of open_links in Initsid.ora typically defaults to 4, which needs to be increased appropriately when the application uses multiple database links.

You can also set up and start the Sql*net event tracking function, get the information generated when the ORA-03106 error occurs, and solve the problem in a targeted way.

In extreme cases, this problem indicates that the shared memory segment used by Oracle has crashed. You may need to use the Abort option to crash the database and release all semaphores (under Unix). Because Oracle uses semaphores to control the synchronization of all background processes. Semaphores is also used to control the dual-task communication between the user process and the shadow process. Because of the complexity of the problems involved in this case, the entire machine system can be crashed and restarted.



Iii. remove data from ORACLE8 and import into ORACLE7

DMP files that are unloaded from the ORACLE7 can be imported into the ORACLE8, but DMP files that are unloaded from ORACLE8 cannot be imported into ORACLE7. If you use the ORACLE7 utility, you cannot unload the ORACLE8 data. This is very inconvenient for users who are using multiple versions of Oracle.

In fact, ORACLE8 has taken this into account. In the server directory $oracle_home/rdbms/admin, there is a file Catexp7.sql, which is used to solve this problem. First, on the ORACLE8 server, log in to Oracle with the SYS account, and then run the Catexp7.sql file. The Oracle system then establishes some unloading views, which makes the ORACLE8 database as if it were a ORACLE7 database when it is unloaded. At this point, you can use the ORACLE7 utility to unload the ORACLE8 data directly, and then you can successfully import the ORACLE7.

When using the ORACLE7 utility to unload the ORACLE8 data directly, some things belonging to the ORACLE8 feature cannot be unloaded. Specific situation, you can refer to the relevant technical manuals, such as "Oracle8 Utilities".



Four, ORA-27101 Shared Memory Realm does not Exist

In the case of this error message, there is usually an error message: Ora-01034:oracle not available. The reason is that different oracle_home are used on the same server. This issue is often present on the ORACLE8.1.7 Server Edition.

First check the file Initsid.ora and Listener.ora, see ORACLE_SID and Oracle_home set correctly or not, ORACLE8.1.7 whether to use this parameter value to start and run. In a UNIX environment, the meaning of letter capitalization is not the same, and this should be noted. This error message may also occur if the oracle_home points to the 8.1.7 version and the database is established with 8.1.6 or version 8.1.5.

In a Windows system, this error can occur if the machine name or IP address is modified, and the machine name or IP address used at ORACLE8.1.7 startup is not a true machine name or IP address. You can view the file Oradim.log under the directory database to determine the reason for the content.

On servers that involve domains (domain), including Windows and UNIX, depending on the system setup, you may need to add a domain name after using the machine name.