Sometimes we have to face the situation where our Oracle database is locked by the huge processes that hit its resources (memory or CPU). I am not a database Administrator but I got the situation where one of our databases production went no response and no one else who could take the action at the time.
The database process which has been kill only changed the status to the "KILLED" no disappear from the session process lists.
For the newbie in database field, as a 'root' person from the OS, I only use the OEM to kill the process by the console, but it was not going succeeded. Meanwhile, the application user has hard complained.
And finally, I got the answer by asking someone expert and googling too...
Here is the steps ..
1. Login as oracle user to the system
2. login as sysdba to the oracle database
$ pwd
/export/home/oracle
$ sqlplus / as sysdba;
3. find the process pid by its simple query.SQL> SELECT s.sid,p.spid,s.osuser,s.program FROM v$process p, v$session s WHERE p.addr = s.paddr; . . .
SID SPID OSUSER PROGRAM
------- ------- --------------- ----------
97 12005 sysadmin jrew.exe
135 7853 appluser TOAD.exe
152 11337 appluser TOAD.exe
111 8158 appluser TOAD.exe
123 8077 appluser TOAD.exe
118 12484 appluser TOAD.exe
149 11639 appluser TOAD.exe
. . .
4. From other session, kill the session from the os with oracle or root user.root@dbserver1 # bash
root@dbserver1 # kill -9 7853 11337 8158 8077 12484 12484
Eventually, the process can be done by the SQL command, but I choose by the OS command than an SQL. This is only a bit of my sharing and the place where I can discover if I face the same case.
The database process which has been kill only changed the status to the "KILLED" no disappear from the session process lists.
For the newbie in database field, as a 'root' person from the OS, I only use the OEM to kill the process by the console, but it was not going succeeded. Meanwhile, the application user has hard complained.And finally, I got the answer by asking someone expert and googling too...
Here is the steps ..
1. Login as oracle user to the system
2. login as sysdba to the oracle database
$ pwd
/export/home/oracle
$ sqlplus / as sysdba;
3. find the process pid by its simple query.SQL> SELECT s.sid,p.spid,s.osuser,s.program FROM v$process p, v$session s WHERE p.addr = s.paddr; . . .
SID SPID OSUSER PROGRAM
------- ------- --------------- ----------
97 12005 sysadmin jrew.exe
135 7853 appluser TOAD.exe
152 11337 appluser TOAD.exe
111 8158 appluser TOAD.exe
123 8077 appluser TOAD.exe
118 12484 appluser TOAD.exe
149 11639 appluser TOAD.exe
. . .
4. From other session, kill the session from the os with oracle or root user.root@dbserver1 # bash
root@dbserver1 # kill -9 7853 11337 8158 8077 12484 12484
Eventually, the process can be done by the SQL command, but I choose by the OS command than an SQL. This is only a bit of my sharing and the place where I can discover if I face the same case.
