Usually I try not to mess with Oracle, and in return it tries not to mess with me. Relationships are difficult when both parties have pretty strong characters. But today Oracle was angry at me, and I needed to do something about it. So SSH here I go!!!
Well, actually a user was asking me if Oracle service was up or down. Basic checking on Enterprise Manager told me it was up. But I wanted to see the logs and look for unusual messages. Unfortunately this is not so easy to find, so here I am writing a small post to help whoever needs this information.
The error log location is customized for each install, so I can not point you to a single default location. It usually stays inside your ORA_HOME folder, somewhere around /diag/rdbms/<instance/something>/trace, but this could be anywhere on your filesystem. To find out where your Oracle log file is located, connect to your database by your prefered way, then query this:
show parameter background_dump_dest;
The main log file is “alert_orcl1.log”, so you could just locate/search for it too. When you find it, you will have to go back to Operating System and check the file/tail it. You can read more about this here. Also, if you are already troubleshooting, you could take a look at this great article about How to troubleshoot Oracle remote database connection. It’s a rare sight to see such a clean and complete article about some Oracle errors.
Also while we are here, you could read this article too if your Oracle is refusing connections. The query below will show you the configuration and current usage of your available connections.
select * from v$resource_limit where resource_name = 'processes';
And if you are having connection issues, it could be usefull to see who is currently connected on your database. The sample below is from this forum post.
select substr(a.spid,1,7) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#, substr(b.machine,1,15) box, substr(b.username,1,8) username, b.server, substr(b.osuser,1,8) os_user, substr(b.program,1,23) program from v$session b, v$process a where b.paddr = a.addr and type='USER' order by spid;
Have you noticed that I like to link whatever sources I find about the subjects posted here? I don’t intend to give just tips, but also link more source material to be used as research. Usually these articles have way more information than what I quote, so it is worth to take a look at them.