Home » RDBMS Server » Server Administration » How to find the alert log path when database is down. (Oracle 11.2.0.4,Linux 2.6)
How to find the alert log path when database is down. [message #620547] Mon, 04 August 2014 04:16 Go to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Hi,

How to find alert log file path when database is down.Say for example when try to login to database i got the below error .
Connected.
SQL>
SQL>
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

In this case , how to find the path of alert log file ?

Regards,
Srikanth

[Updated on: Mon, 04 August 2014 04:17]

Report message to a moderator

Re: How to find the alert log path when database is down. [message #620550 is a reply to message #620547] Mon, 04 August 2014 04:27 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It's in the diagnostic dest. Check the (s)pfile.
Re: How to find the alert log path when database is down. [message #620553 is a reply to message #620547] Mon, 04 August 2014 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have it in init.ora or spfile.
It is in "background_dump_dest" (if you set it) or in "diagnostic_dest"/diag/rdbms/<base>/<INSTANCE>/alert

[Updated on: Mon, 04 August 2014 05:08]

Report message to a moderator

Re: How to find the alert log path when database is down. [message #620556 is a reply to message #620553] Mon, 04 August 2014 04:48 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Thanks Roach and Michel for your quick answer.
Re: How to find the alert log path when database is down. [message #620557 is a reply to message #620556] Mon, 04 August 2014 05:10 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
I came to know database was down.

So i used ps -ef|grep pmon to find if database instance was running.It was running
[oracle@cpusir01 ~]$ ps -ef|grep pmon
oracle   19846     1  0 Jul09 ?        00:34:47 ora_pmon_klmno
oracle   27045 24284  0 11:44 pts/2    00:00:00 grep pmon

later when i logged into database and checked i was getting below error.
Connected.
SQL>
SQL>
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

When i try to start the database using startup command, i saw some out of memory error in database.

after that i used ps -ef|grep pmon to check database it was not running
[oracle@cpusir01 rdbms]$ ps -ef|grep pmon
oracle   27715 24284  0 12:39 pts/2    00:00:00 grep pmon

note: -- I could not find alert log path in spfile and alert log file under diagnostic_dest"/diag/rdbms/<base>/<INSTANCE>/alert

Will rebooting the server will resolve this problem ?

Regards,
Srikanth

[Updated on: Mon, 04 August 2014 05:13]

Report message to a moderator

Re: How to find the alert log path when database is down. [message #620561 is a reply to message #620557] Mon, 04 August 2014 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
note: -- I could not find alert log path in spfile and alert log file under diagnostic_dest"/diag/rdbms/<base>/<INSTANCE>/alert


Copy and paste what did to try to get them.

Re: How to find the alert log path when database is down. [message #620567 is a reply to message #620561] Mon, 04 August 2014 06:37 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Michel,
We have requested Unix team to reboot the server and it is in progress.

Regards,
Srikanth
Re: How to find the alert log path when database is down. [message #620586 is a reply to message #620567] Mon, 04 August 2014 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
find / -name alert_{$ORACLE_SID}.log 2>/dev/null
Re: How to find the alert log path when database is down. [message #620590 is a reply to message #620567] Mon, 04 August 2014 08:59 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
srikanth02 wrote on Mon, 04 August 2014 13:37
Michel,
We have requested Unix team to reboot the server and it is in progress.

Regards,
Srikanth


Out of curiosity : Why reboot?
In the years I'm working with Unix I did not find many cases where a system would benefit from reboot.
A few exceptions are:
- kernel updates (for older unixes)
- Hardware failure (shutdown -> replace -> startup)
Re: How to find the alert log path when database is down. [message #620614 is a reply to message #620590] Mon, 04 August 2014 10:36 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Hi Martin,

Database was down due to out of memory issue. This i came to know when i try to startup the database
using startup command.I could not bring the database up even after killing process.So i requested for
server reboot and thus database came up.

Hi Blackswan,
find / -name alert_{$ORACLE_SID}.log 2>/dev/null 

From where ( path) this find command has to run ?

Regards,
Srikanth

Re: How to find the alert log path when database is down. [message #620618 is a reply to message #620614] Mon, 04 August 2014 11:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
$PATH
Re: How to find the alert log path when database is down. [message #620620 is a reply to message #620614] Mon, 04 August 2014 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
From where ( path) this find command has to run ?


Can you just think a tiny second before posting a question?
This is a stupid one.
Why?
What does the command do?
Try to think.

Re: How to find the alert log path when database is down. [message #620628 is a reply to message #620620] Mon, 04 August 2014 11:42 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Michel,
Quote:
Can you just think a tiny second before posting a question?
This is a stupid one.
Why?
What does the command do?
Try to think.

Are you Blackswan ? Is Blckswan and Michel are same and by mistake you used Michel account to reply my thread ?

I know the answer but wanted to verify with you .There is nothing in clarifying ours doubts.I give respect to elders
but you are taking over advantage.What if i write something odd here ? well i dont do that and dont force me do.

When Blackswan can answer my question what the hell happens to you ?? Are you mad to reply like that ?
well if you think you are elder to all and expert in oracle then look for some other oracle forums .

Well there is an option to use "ignore all message " in orafaq .Dont force me to use that button.

Re: How to find the alert log path when database is down. [message #620632 is a reply to message #620628] Mon, 04 August 2014 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
When Blackswan can answer my question what the hell happens to you ??


So
1/ You don't know what the command do
2/ If you could think a little bit you'd see that BlackSwan and I answered about the same time and so I didn't see her answer, but this would not change my post
3/ There is no doubt, you don't understand what the command do

I give you 2 hints.
1/ The answer could be /tmp, $HOME, $ORACLE_HOME...
2/ Can you do a "cd $PATH"?

So I ask you once again my question "What does the command do?", answer if you can.

Quote:
Are you Blackswan ? Is Blckswan and Michel are same and by mistake you used Michel account to reply my thread ?


No, we are not, in opposite to you we do not use many accounts to post, we have one and only one. Only you are a liar and a fraud here.

Quote:
Well there is an option to use "ignore all message " in orafaq .Dont force me to use that button.


Laughing I'm scared. Who will be the most embarrassed if you do so, you or me?

Re: How to find the alert log path when database is down. [message #620634 is a reply to message #620632] Mon, 04 August 2014 11:58 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Quote:
Who will be the most embarrassed if you do so, you or me?

Laughing Think a bit .You will realise it.

[Updated on: Mon, 04 August 2014 11:59]

Report message to a moderator

Re: How to find the alert log path when database is down. [message #620662 is a reply to message #620634] Mon, 04 August 2014 22:19 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
You are indeed kind of ignorant fellow Srikanth if this is your behavior with Mr Michel.
Anyhow you could do if using 11g version:-
adrci
show alert
Re: How to find the alert log path when database is down. [message #620666 is a reply to message #620662] Tue, 05 August 2014 03:45 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Michel,

Now you know why i am using several accounts in orafaq. All because of you ....

Re: How to find the alert log path when database is down. [message #620673 is a reply to message #620666] Tue, 05 August 2014 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Me? Not just you!

Re: How to find the alert log path when database is down. [message #620674 is a reply to message #620673] Tue, 05 August 2014 04:28 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Michel,

Pls re-read what i have written.I am not saying that you are having have multiple accounts in orafaq.I am trying to say i have several accounts all because of you.

I am not your enemy and you are not mine too.I am trying to say when others could answer my question ,why are you involving in that
and asking too many questions.Here the fight starts.

Please do not repeat like that.

Re: How to find the alert log path when database is down. [message #620675 is a reply to message #620674] Tue, 05 August 2014 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't understand what I wrote.
You are your own enemy.

Re: How to find the alert log path when database is down. [message #620679 is a reply to message #620675] Tue, 05 August 2014 05:49 Go to previous messageGo to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
I am not enenmy to anyone.Lets not fight like a kid Smile
Re: How to find the alert log path when database is down. [message #620720 is a reply to message #620666] Tue, 05 August 2014 10:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:

Balaji/Ajit/Jack/Mohan/Mathew/Suhas/Srini/william/Srikanth,
Who is he ? I don't know him.You are blaming me.


srikanth02 wrote on Tue, 05 August 2014 14:15

Now you know why i am using several accounts in orafaq.


More?

http://www.orafaq.com/forum/mv/msg/193854/620343/#msg_620343

Even more? Re-read your own topics.

[Updated on: Tue, 05 August 2014 10:28]

Report message to a moderator

Re: How to find the alert log path when database is down. [message #620791 is a reply to message #620720] Wed, 06 August 2014 03:49 Go to previous messageGo to next message
zengmuansha
Messages: 26
Registered: April 2012
Junior Member
export trace=$ORACLE_BASE/diag/rdbms/$SERVICE_NAMES/$ORACLE_SID/trace
Re: How to find the alert log path when database is down. [message #620794 is a reply to message #620791] Wed, 06 August 2014 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wrong!
Read what has been said at the top of this topic.
SERVICE_NAMES does not exist.

Re: How to find the alert log path when database is down. [message #621022 is a reply to message #620557] Thu, 07 August 2014 14:05 Go to previous messageGo to next message
tim2boles
Messages: 38
Registered: August 2008
Location: Clarksburg, WV
Member
Ok...going back to the original topic I would include the following ideas.

1) Documentation : It would be nice to create a document that contains the information you need for "quick" lookup if necessary. I keep one that has information such as server name, databases on server, primary contact for server, backup schedule, location of diagnostic directories, backup strategy, scheduled server reboots and similar information.

2) Generally you can find this information (as stated by others) within the spfile. Normally I will start where most likely I can find the ORACLE_HOME and go from there. If things are setup as "normal" you can look in the oratab. Which for me being on a Linux machine is under /etc/

$cat /etc/oratab

testdb:/code11_16/oracle/product/10.2.0:N

So now I can go to the ORACLE_HOME directory and look under the dbs directory

$cd /code11_16/oracle/product/10.2.0/dbs
$ls -l inittestdb.ora

If I grep for diag and dump depending on the version of oracle. I can find the alert file generally in one of those directory structures. Simple enough to use find to look for it.

Regards
Tim






Re: How to find the alert log path when database is down. [message #621112 is a reply to message #621022] Sat, 09 August 2014 03:36 Go to previous message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Thank you Tim and others who bothered to answer my questions.
hats off to you all !!!

[Updated on: Sat, 09 August 2014 03:36]

Report message to a moderator

Previous Topic: Unable to move objects from SYSTEM tablespace
Next Topic: wt is the diff b/w exclusive Lock And Dead Lock?
Goto Forum:
  


Current Time: Thu Mar 28 09:26:41 CDT 2024