Three methods for SQL plus to respond in Linux

OracleDatabaseSqlplusNo response is pending. The online query results show that the oracle bug is caused. In fact, this bug may occur as long as the Linux x86 host runs for a multiple of 24.8 days, because time () if the function value is null, an infinite endless loop is generated, consuming the cpu. Next we will introduce the solution.

Three solutions:

1) restart the host;

2) patch set, for example, update to 10.2.0.4;

3) apply a temporary patch 4612267 to the bug.

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

Reference:

Doc ID: 338461.1 SQL * Plus 10.2.0.1 Hangs, When System Uptime Is Long Period of Time

Doc ID: 4612267.8 Bug 4612267-OCI client spins when machine uptime> = 249 days

The third method is 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/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 patching?
  
  
 
  
  
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/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.

Note:

 
 

  
  
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. -- in an endless loop
  
  
 
  
  
Times (NULL) =-1825782405
  
  
 
  
  
Times (NULL) =-1825782405
  
  
 
  
  
Times (NULL) =-1825782405
  
  
 
  
  
Times (NULL) =-1825782405
  
  
 
  
  
Times (NULL) =-1825782405
  
  
 
  
  
Times (NULL) =-1825782405
  
  
 
  
  
Times (NULL) =-1825782405
 
 

I will introduce the problem that sqlplus does not respond in Linux. I hope this introduction will bring you some gains!