Oracle database does not respond

Oracle version:
10.2.0.1.0

SQL * Plus-version:
10.2.0.1.0

This may cause no response to all locally initiated connections to the Oracle Database Server.

 

Symptom:

After the SQL plus command is switched to Oracle, the SQL plus command is not reflected and no error is reported. The SQL plus command is suspended.

[Root @ localhost ~] # Su-Oracle

[Oracle @ localhost ~] $ Sqlplus # No response here .. No response no matter how long

Other commands in Oracle can be executed.


View information through SAR

Linux 2.6.9-22. elsmp (economy 2)


At 09:43:13 CPU % USER % nice % System % iowait % idle

09:43:15 pm all 46.77 0.00 53.23 0.00 0.00

09:43:17 pm all 47.00 0.00 53.00 0.00 0.00

09:43:19 pm all 43.20 0.00 54.37 2.43 0.00

Average: All 45.63 0.00 53.54 0.82 0.00


End sqlplus

View system status through Sar 2 4


At 09:44:15 CPU % USER % nice % System % iowait % idle

09:44:17 pm all 0.00 0.00 0.00 0.00 0.00

09:44:19 pm all 0.00 0.00 0.00 0.00 0.00

09:44:21 pm all 0.00 0.00 0.00 100.00 0.00

09:44:23 pm all 0.00 0.00 0.00 0.00 0.00

Average: All 0.00 0.00 0.00 100.00 0.00

However, this problem may occur in about 60 days after uptime200.

[Oracle @ XXXXX ~] $ Uptime
16:48:26 up 200 days, 2 users, load average: 0.00, 0.03, 0.10

 

The problem is basically located as a bug in the Oracle client software,
Involved versions: Oracle 10.2.0.1.0
This is the uptime> 50 days, that is, there may be no response after running sqlplus, the main reason is that the time overflow error. In fact, this bug may be caused as long as the Linux x86 host runs for a multiple of 24.8 days (the system runs for 198 days), because the time () function value is null, resulting in an infinite endless loop, this consumes the CPU.

Running strace tool shows: $ strace/Oracle/home/bin/sqlplus-V 2> & 1 | less ...... old_mmap (null, 385024, prot_read | prot_write, map_private | map_anonymous,-1, 0) = 0x41794000 gettimeofday ({1122996561,411 035}, null) = 0 access ("/usr/local/UD/CONF/sqlnet. ora ", f_ OK) =-1 enoent (no such file or directory) Access ("/usr/local/UD/lib/Oracle/Network/admin/sqlnet. ora ", f_ OK) =-1 enoent (no such file or directory) Access ("/usr/local/UD/CONF/sqlnet. ora ", f_ OK) =-1 enoent (no such file or directory) Access ("/usr/local/UD/lib/Oracle/Network/admin/sqlnet. ora ", f_ OK) =-1 enoent (no such file or directory) fcntl64 (-1218313656, f_setfd, fd_cloexec) =-1 ebadf (bad file descriptor) it is looping on the Times () function. -- times (null) =-1825782405 times (null) =-1825782405 times (null) =-1825782405 times (null) =-1825782405 times (null) =-1825782405 times (null) =-1825782405 times (null) =-1825782405

Solution:

1. restart the system.

2. Create a temporary patch 4612267 for the bug.

3. Upgrade the Oracle client to 10.2.0.2.0 (this version has been released on the official website to solve this problem ).

4. If you downgrade to 9i, 9i will never have this problem; or downgrade to 10.1.0.4 (not fully tested ).

The first method does not completely solve the problem and will remain the same later. The third and fourth methods require a long upgrade time and a long database suspension. The current production environment is not suitable for the time being;

I am using the second method, patching. According to Oracle official documentation, oracle11 has fixed the problem.

The following describes how to install and verify the pache 4612267 patch package:

Stop listening, dbconsole, and database first

$ lsnrctl stop  $ emctl stop dbconsole   $ sqlplus / as sysdba   SQL> shutdown immediate

Note: dbconsole is stopped when Oracle em is installed. If it is not installed, no interference is required.

Install patch

$ Mkdir $ oracle_base/patches $ CD $ oracle_base/patches $ RZ (upload the p4612267_10201_linux.zip file in securecrt. Other upload methods are also supported) $ unzip p4612267_10201_linux.zip $ CD 4612267/$ ORACLE_HOME/opatch apply invoking opatch 10.2.0.1.0... please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/APP/Oracle/product/10.2.0/db_1') is the local system ready for patc Hing? Do you want to proceed? [Y | n] Y (enter y here) User responded with: y... applysession adding interim patch '000000' to inventory the local system has been patched and can be restarted. opatch succeeded.

Verify patch

$ $ORACLE_HOME/OPatch/opatch lsinventory  Invoking OPatch 10.2.0.1.0   Oracle interim Patch Installer version 10.2.0.1.0   Copyright (c) 2005, Oracle Corporation. All rights reserved..   Oracle Home       : /u01/app/oracle/product/10.2.0/db_1   Central Inventory : /u01/app/oracle/oraInventory   from           : /u01/app/oracle/product/10.2.0/db_1/oraInst.loc   OPatch version    : 10.2.0.1.0   OUI version       : 10.2.0.1.0   OUI location      : /u01/app/oracle/product/10.2.0/db_1/oui   Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch-2009_Jan_13_11-06-27-HKT_Tue.log   Lsinventory Output file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory-2009_Jan_13_11-06-27-HKT_Tue.txt   --------------------------------------------------------------------------------   Installed Top-level Products (2):   Oracle Database   10g             10.2.0.1.0   Oracle Database 10g Products                        10.2.0.1.0   There are 2 products installed in this Oracle Home.   Interim patches (1) :   Patch 4612267      : applied on Tue Jan 13 11:05:10 HKT 2009      Created on 5 Oct 2005, 13:48:00 hrs US/Pacific      Bugs fixed:        4612267   --------------------------------------------------------------- OPatch succeeded. 

Start the database, listener, and dbconsole

$ sqlplus / as sysdba   SQL> startup   $ lsnrctl start   $ emctl start dbconsole 

If necessary, you can delete the patch and stop the database before deleting it.

$ CD $ oracle_base/patches/4612267 $ ORACLE_HOME/opatch rollback-ID 4612267 invoking opatch 10.2.0.1.0... please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/APP/Oracle/product/10.2.0/db_1') is the local system ready for patching? Do you want to proceed? [Y | n] Y (enter y here) User responded with: y... rollbacksession removing interim patch '000000' from inventory the local system has been patched and can be restarted. opatch succeeded.

Execute the above patch command and you will find that the patch has been deleted.