Oracle occupies high system resources and sqlplus does not respond

You cannot log on to the TSP platform .. Later, we restarted tomcat, but still could not log on. After checking, there is a problem with the database. Go to the top of the oracle server. The oracle process occupies a large amount of resources, the remaining id is 0.0%, and the load rapidly increases to 156 .. I tried to enter the oracle sys account to check whether there is a loop statement connection, but sqlplus did not respond after I press ENTER for half a day, and the resources continued to rise, resulting in high load!

Later, google finally found the problem after half a day-sqlplus had no response to suspend processing, resulting in a half-day old oracle bug-my oracle version was 10.2.0.1. Later, we noticed that the system runs for 198 days. Khan! It is the same as the network document ..

The following is taken from the network:

In fact, this bug may occur as long as the running days of the Linux x86 host are multiples of 24.8, because the time () function value is null, resulting in an infinite endless loop, thus exhausting the cpu.

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 (securecrtto upload the p4612267_10201_linux.zip file. 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/export toollogs/opatch/opatch-2009_Jan_13_11-06-27-HKT_Tue.log

Lsinventory Output file location:/u01/app/oracle/product/10.2.0/db_1/export toollogs/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

 

This is the real reason why the system resources are too high !!

This article is from the "double.51.com" blog