Oracle: view Error Log and connection issues.

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.

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,
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.



About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in Linux, Oracle and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s