Home » RDBMS Server » Performance Tuning » Oracle Database Stop working
Oracle Database Stop working [message #301669] Thu, 21 February 2008 05:20 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
We are working in Oracle 10g on Linux Debian 32 bit, A week back we had a problem that we were unable to access database by any tool of oracle locally as well as remotly in the morning, Everything was fine previously . when i try to write any command server was too slow and when i use sqlplus it is unable to run no reply then i use top command where i find CPU is too busy by USer using 40% and system using 25% in VMSTAT it was quite clear that there are runnable queue waiting for CPU time. But we could not find out the session which is doing so . When we kill Oracle user processes then CPU was fine after that i went to see Oracle alert log but no clue in that then i see ADDM report, Last snap shot was at 2:00 AM after an hour no snap was taken by Oracle. Now i do not know what happened to the database.

How to find about the status when no tool is wiorking and what can be the problem

Report message to a moderator

Re: Oracle Database Stop working [message #301682 is a reply to message #301669] Thu, 21 February 2008 06:05 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Try looking for any recent trace/dump files.

HTH.
Michael

Report message to a moderator

Re: Oracle Database Stop working [message #301720 is a reply to message #301682] Thu, 21 February 2008 07:59 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
No clue in trace file,

Report message to a moderator

Re: Oracle Database Stop working [message #301921 is a reply to message #301720] Fri, 22 February 2008 05:47 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Is there any one who can tell me that Oracle stop working and there is no clue in trace files then how to find out and secondly in the situation when oracle process is taking much resources and hogging CPU, how to find out the main processes

Report message to a moderator

DATABASE STOP WORKING [message #301922 is a reply to message #301669] Fri, 22 February 2008 05:52 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
We are working in Oracle 10g on Linux Debian 32 bit, A week back we had a problem that we were unable to access database by any tool of oracle locally as well as remotly in the morning, Everything was fine previously . when i try to write any command server was too slow and when i use sqlplus it is unable to run no reply then i use top command where i find CPU is too busy by USer using 40% and system using 25% in VMSTAT it was quite clear that there are runnable queue waiting for CPU time. But we could not find out the session which is doing so . When we kill Oracle user processes then CPU was fine after that i went to see Oracle alert log but no clue in that then i see ADDM report, Last snap shot was at 2:00 AM after an hour no snap was taken by Oracle. Now i do not know what happened to the database.

How to find about the status when no tool is wiorking and what can be the problem.

No clue in trace file and could not find out the reaon it also happened two months back

Report message to a moderator

Re: Oracle Database Stop working [message #301962 is a reply to message #301669] Fri, 22 February 2008 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/

Which have you tried & what were the results?

Report message to a moderator

Re: Oracle Database Stop working [message #301971 is a reply to message #301962] Fri, 22 February 2008 09:29 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,
Thank you for your help

But these document is related to Performance tuning when database is up.
I experience a problem that database itself stuck in processes and we had to kill processes of oracle. We were unable to even check whether database is up because it does not let us logging in the sqlplus. At this point of time how can any one check the bottleneck.

Report message to a moderator

Re: Oracle Database Stop working [message #301972 is a reply to message #301669] Fri, 22 February 2008 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What is version of Oracle to FOUR decimal places?
What is the results from the OS uptime command?

what does the following reveal

$ strace sqlplus

[Updated on: Fri, 22 February 2008 09:36] by Moderator

Report message to a moderator

Re: Oracle Database Stop working [message #301985 is a reply to message #301972] Fri, 22 February 2008 10:55 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
gettimeofday({1203699096, 408515}, NULL) = 0
gettimeofday({1203699096, 408654}, NULL) = 0
write(9, "\0\250\0\0\6\0\0\0\0\0\336\255\276\357\0\236\n \1\0\0\4"..., 168) = 168
read(10, "\0\213\0\0\6\0\0\0\0\0\336\255\276\357\0\201\n \1\0\0\4"..., 2064) = 139
open("/u01/app/oracle/product/10.2.0/db_4/rdbms/mesg/oraus.msb", O_RDONLY) = 8
fcntl64(8, F_SETFD, FD_CLOEXEC) = 0
lseek(8, 0, SEEK_SET) = 0
read(8, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
lseek(8, 512, SEEK_SET) = 512
read(8, "d\32\2135\307[\360v\224\206C\226t\255\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(8, 1024, SEEK_SET) = 1024
read(8, "\30\0$\0005\0>\0I\0V\0`\0k\0u\0\203\0\241\0\312\0\323\0"..., 512) = 512
lseek(8, 31232, SEEK_SET) = 31232
read(8, "\r\0\356\3\0\0V\0\357\3\0\0r\0\360\3\0\0\215\0\361\3\0"..., 512) = 512
close(Cool = 0
lseek(4, 512, SEEK_SET) = 512
read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251"..., 512) = 512
lseek(4, 5120, SEEK_SET) = 5120
read(4, "\r\0\351\0\0\0V\0\352\0\0\0\220\0\353\0\0\0\240\0\356\0"..., 512) = 512
write(1, "ERROR:\n", 7) = 7
write(1, "ORA-01017: invalid username/pass"..., 51) = 51
write(1, "\n\n", 2) = 2
lseek(4, 512, SEEK_SET) = 512
read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251"..., 512) = 512
lseek(4, 4608, SEEK_SET) = 4608
read(4, "\17\0\240\0\0\0b\0\241\0\0\0v\0\242\0\0\0\211\0\253\0\0"..., 512) = 512
write(1, "Enter user-name: ", 17) = 17
read(0,
"\n", 1024) = 1
write(9, "\0\n\0\0\6\0\0\0\0@", 10) = 10
close(9) = 0
close(10) = 0
close(7) = 0
getcwd("/home/oracle", 256) = 13
getuid32() = 1001
open("/etc/passwd", O_RDONLY) = 7
fcntl64(7, F_GETFD) = 0
fcntl64(7, F_SETFD, FD_CLOEXEC) = 0
_llseek(7, 0, [0], SEEK_CUR) = 0
fstat64(7, {st_mode=S_IFREG|0644, st_size=1464, ...}) = 0
mmap2(NULL, 1464, PROT_READ, MAP_SHARED, 7, 0) = 0xb6f83000
_llseek(7, 1464, [1464], SEEK_SET) = 0
munmap(0xb6f83000, 1464) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
rt_sigprocmask(SIG_BLOCK, [], NULL, Cool = 0
waitpid(10937, NULL, WNOHANG) = 10937
rt_sigprocmask(SIG_UNBLOCK, [], NULL, Cool = 0
rt_sigreturn(0xb6f83000) = 0
close(7) = 0
socket(PF_INET, SOCK_DGRAM, IPPROTO_IP) = 7
setsockopt(7, SOL_SOCKET, SO_REUSEADDR, [1], 4) = 0
bind(7, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("127.0.0.1")}, 16) = 0
getsockname(7, {sa_family=AF_INET, sin_port=htons(1066), sin_addr=inet_addr("127.0.0.1")}, [16]) = 0
getpeername(7, 0xbfa205a8, [16]) = -1 ENOTCONN (Transport endpoint is not connected)
getsockopt(7, SOL_SOCKET, SO_SNDBUF, [109568], [4]) = 0
getsockopt(7, SOL_SOCKET, SO_RCVBUF, [109568], [4]) = 0
fcntl64(7, F_SETFD, FD_CLOEXEC) = 0
fcntl64(7, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
gettimeofday({1203699097, 239879}, NULL) = 0
pipe([8, 9]) = 0
pipe([10, 11]) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0xb6dfeae8) = 10938
close(Cool = 0
close(11) = 0
read(10, "NTP0 10938\n", 64) = 11
fcntl64(10, F_SETFD, FD_CLOEXEC) = 0
gettimeofday({1203699097, 271831}, NULL) = 0
write(9, "\0:\0\0\1\0\0\0\0019\1,\f\1\10\0\20\0O\230\0\0\1\0\0\367"..., 58) = 58
write(9, "\1\1\0\0\6\0\0\0\0\0(DESCRIPTION=(ADDRESS="..., 257) = 257
read(10, "\0 \0\0\2\0\0\0\0019\f\1\10\0\20\0\1\0\0\0\0 AA\0\0\0\0"..., 2064) = 32
gettimeofday({1203699097, 272387}, NULL) = 0
gettimeofday({1203699097, 272510}, NULL) = 0
write(9, "\0\250\0\0\6\0\0\0\0\0\336\255\276\357\0\236\n \1\0\0\4"..., 168) = 168
read(10, "\0\213\0\0\6\0\0\0\0\0\336\255\276\357\0\201\n \1\0\0\4"..., 2064) = 139
open("/u01/app/oracle/product/10.2.0/db_4/rdbms/mesg/oraus.msb", O_RDONLY) = 8
fcntl64(8, F_SETFD, FD_CLOEXEC) = 0
lseek(8, 0, SEEK_SET) = 0
read(8, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
lseek(8, 512, SEEK_SET) = 512
read(8, "d\32\2135\307[\360v\224\206C\226t\255\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(8, 1024, SEEK_SET) = 1024
read(8, "\30\0$\0005\0>\0I\0V\0`\0k\0u\0\203\0\241\0\312\0\323\0"..., 512) = 512
lseek(8, 31232, SEEK_SET) = 31232
read(8, "\r\0\356\3\0\0V\0\357\3\0\0r\0\360\3\0\0\215\0\361\3\0"..., 512) = 512
close(Cool = 0
lseek(4, 512, SEEK_SET) = 512
read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251"..., 512) = 512
lseek(4, 5120, SEEK_SET) = 5120
read(4, "\r\0\351\0\0\0V\0\352\0\0\0\220\0\353\0\0\0\240\0\356\0"..., 512) = 512
write(1, "ERROR:\n", 7) = 7
write(1, "ORA-01017: invalid username/pass"..., 51) = 51
write(1, "\n\n", 2) = 2
lseek(4, 512, SEEK_SET) = 512
read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251"..., 512) = 512
lseek(4, 4608, SEEK_SET) = 4608
read(4, "\17\0\240\0\0\0b\0\241\0\0\0v\0\242\0\0\0\211\0\253\0\0"..., 512) = 512
write(1, "Enter user-name: ", 17) = 17
read(0,
"\n", 1024) = 1
write(9, "\0\n\0\0\6\0\0\0\0@", 10) = 10
close(9) = 0
close(10) = 0
close(7) = 0
getcwd("/home/oracle", 256) = 13
getuid32() = 1001
open("/etc/passwd", O_RDONLY) = 7
fcntl64(7, F_GETFD) = 0
fcntl64(7, F_SETFD, FD_CLOEXEC) = 0
_llseek(7, 0, [0], SEEK_CUR) = 0
fstat64(7, {st_mode=S_IFREG|0644, st_size=1464, ...}) = 0
mmap2(NULL, 1464, PROT_READ, MAP_SHARED, 7, 0) = 0xb6f83000
_llseek(7, 1464, [1464], SEEK_SET) = 0
munmap(0xb6f83000, 1464) = 0
close(7) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
rt_sigprocmask(SIG_BLOCK, [], NULL, Cool = 0
waitpid(10938, NULL, WNOHANG) = 10938
rt_sigprocmask(SIG_UNBLOCK, [], NULL, Cool = 0
rt_sigreturn(0xb7ec18f0) = 0
socket(PF_INET, SOCK_DGRAM, IPPROTO_IP) = 7
setsockopt(7, SOL_SOCKET, SO_REUSEADDR, [1], 4) = 0
bind(7, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("127.0.0.1")}, 16) = 0
getsockname(7, {sa_family=AF_INET, sin_port=htons(1066), sin_addr=inet_addr("127.0.0.1")}, [16]) = 0
getpeername(7, 0xbfa205a8, [16]) = -1 ENOTCONN (Transport endpoint is not connected)
getsockopt(7, SOL_SOCKET, SO_SNDBUF, [109568], [4]) = 0
getsockopt(7, SOL_SOCKET, SO_RCVBUF, [109568], [4]) = 0
fcntl64(7, F_SETFD, FD_CLOEXEC) = 0
fcntl64(7, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
gettimeofday({1203699097, 957606}, NULL) = 0
pipe([8, 9]) = 0
pipe([10, 11]) = 0
clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0xb6dfeae8) = 10939
close(Cool = 0
close(11) = 0
read(10, "NTP0 10939\n", 64) = 11
fcntl64(10, F_SETFD, FD_CLOEXEC) = 0
gettimeofday({1203699097, 988857}, NULL) = 0
write(9, "\0:\0\0\1\0\0\0\0019\1,\f\1\10\0\20\0O\230\0\0\1\0\0\367"..., 58) = 58
write(9, "\1\1\0\0\6\0\0\0\0\0(DESCRIPTION=(ADDRESS="..., 257) = 257
read(10, "\0 \0\0\2\0\0\0\0019\f\1\10\0\20\0\1\0\0\0\0 AA\0\0\0\0"..., 2064) = 32
gettimeofday({1203699097, 989419}, NULL) = 0
gettimeofday({1203699097, 989541}, NULL) = 0
write(9, "\0\250\0\0\6\0\0\0\0\0\336\255\276\357\0\236\n \1\0\0\4"..., 168) = 168
read(10, "\0\213\0\0\6\0\0\0\0\0\336\255\276\357\0\201\n \1\0\0\4"..., 2064) = 139
open("/u01/app/oracle/product/10.2.0/db_4/rdbms/mesg/oraus.msb", O_RDONLY) = 8
fcntl64(8, F_SETFD, FD_CLOEXEC) = 0
lseek(8, 0, SEEK_SET) = 0
read(8, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
lseek(8, 512, SEEK_SET) = 512
read(8, "d\32\2135\307[\360v\224\206C\226t\255\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(8, 1024, SEEK_SET) = 1024
read(8, "\30\0$\0005\0>\0I\0V\0`\0k\0u\0\203\0\241\0\312\0\323\0"..., 512) = 512
lseek(8, 31232, SEEK_SET) = 31232
read(8, "\r\0\356\3\0\0V\0\357\3\0\0r\0\360\3\0\0\215\0\361\3\0"..., 512) = 512
close(Cool = 0
lseek(4, 512, SEEK_SET) = 512
read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251"..., 512) = 512
lseek(4, 5120, SEEK_SET) = 5120
read(4, "\r\0\351\0\0\0V\0\352\0\0\0\220\0\353\0\0\0\240\0\356\0"..., 512) = 512
write(1, "ERROR:\n", 7) = 7
write(1, "ORA-01017: invalid username/pass"..., 51) = 51
write(1, "\n\n", 2) = 2
write(1, "SP2-0157: ", 10) = 10
lseek(4, 512, SEEK_SET) = 512
read(4, "\245\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
lseek(4, 1024, SEEK_SET) = 1024
read(4, "\26\0*\0R\0h\0\201\0\236\0\350\0\374\0\n\1#\1?\1M\1\251"..., 512) = 512
lseek(4, 4096, SEEK_SET) = 4096
read(4, "\f\0\202\0\0\0P\0\206\0\0\0a\0\207\0\0\0~\0\210\0\0\0\225"..., 512) = 512
brk(0x8103000) = 0x8103000
write(1, "unable to CONNECT to ORACLE afte"..., 63) = 63
brk(0x80fb000) = 0x80fb000
write(9, "\0\n\0\0\6\0\0\0\0@", 10) = 10
close(9) = 0
close(10) = 0
brk(0x80fa000) = 0x80fa000
brk(0x80f4000) = 0x80f4000
close(7) = 0
close(6) = 0
close(5) = 0
close(3) = 0
close(4) = 0
munmap(0xb6ddb000, 143360) = 0
munmap(0xb6d1e000, 4096) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
rt_sigprocmask(SIG_BLOCK, [], NULL, Cool = 0
waitpid(10939, NULL, WNOHANG) = 10939
rt_sigprocmask(SIG_UNBLOCK, [], NULL, Cool = 0
rt_sigreturn(0xb6d1e000) = 0
exit_group(1) = ?
ABOVE IS THE OUTPUT OF strace sqlplus
VERSION IS 10.2.0.1

Report message to a moderator

Re: Oracle Database Stop working [message #302044 is a reply to message #301669] Fri, 22 February 2008 15:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>write(1, "ORA-01017: invalid username/pass"..., 51) = 51
>it does not let us logging in the sqlplus
You would have a much better chance at being productive if you used a valid username/password combination.

What is the result from the OS uptime command?

Report message to a moderator

Re: Oracle Database Stop working [message #302290 is a reply to message #302044] Mon, 25 February 2008 03:44 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
09:43:27 up 9 days, 21:30, 2 users, load average: 0.10, 0.05, 0.00

Report message to a moderator

Re: Oracle Database Stop working [message #302971 is a reply to message #302290] Wed, 27 February 2008 08:14 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
So anybody knows that what is the problem and if no tool is running. How to find out and kill specific oracle process when all Oracle process have same cpu% used.

Report message to a moderator

Re: Oracle Database Stop working [message #302982 is a reply to message #301669] Wed, 27 February 2008 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You may have been impacted by bug as found in MetaLink Note:338461.1 which impacts V10.2.0.1 on Linux

Report message to a moderator

Re: Oracle Database Stop working [message #303471 is a reply to message #302982] Fri, 29 February 2008 08:19 Go to previous message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
But this bug is when database is inaccessible after 200 days of its startup but

It hapeen to my db database on 26-DEC-2007 AND THEN 15-FEB 2008

Does it happens earlier in some server.

Report message to a moderator

Previous Topic: drop partition slow
Next Topic: SQL statement in the SGA
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 28 10:29:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.