Reading Alert Log Files using Shell

Method 1:
This method is the simplest one and it just get the lines containing ORA in their lines.

cat alert_ABCD.log | grep 'ORA' | more

Sample Output:

WARNING: inbound connection timed out (ORA-3136)
ORA-00604: error occurred at recursive SQL level 1
ORA-03113: end-of-file on communication channel
WARNING: inbound connection timed out (ORA-3136)

Method 2:
This is little complex method using awk command.

cat alert_ABCD.log | awk '
BEGIN{date=""}
/ORA-/ {print date; print $0;next}
{date=$0;next}'

Sample Output: date and timestamp is included.

Wed May 20 14:41:59 2009
WARNING: inbound connection timed out (ORA-3136)
Error stack returned to user:
ORA-00604: error occurred at recursive SQL level 1
Error stack returned to user:
ORA-03113: end-of-file on communication channel
Thu May 21 17:00:10 2009
WARNING: inbound connection timed out (ORA-3136)
Thu May 21 17:03:23 2009

Method 3:
This is the more complex method which used some patterns to find and present the alert log errors.

cat alert_ABCD.log | awk '
/ORA-/ {print last_date; print $0}
/^[F-W][a-u][d-u] [A-S][a-u][b-y] *[0-9]* [0-2][0-9]:[0-5][0-9]:[0-5][0-9] 20[0,1][0-9]/ {last_date = $0 }'

Some explanations:
First 3 characters are Mon/Tue/Wed/Thu/Fri/Sat/Sun so I "simplyfy" my pattern search by looking the lowest and highest first character: F (of Fri) and W (of Wed); same for second, ...
Then a space
Then the month: Jan/Feb/Mar/... So same idea: A (of April) to S (of September), then a to u, and so on
Then 1 or 2 spaces and 1 or 2 digits and a space
Then the time: hour: 00 to 23 then a ":" then minute: 00 to 59 and so on, then a space
And lastly the year, I chose to search for 2000 to 2019 

Sample Output: Only the error lines will be displayed. Output is more accurate than both above outputs.

Wed May 20 14:41:59 2009
WARNING: inbound connection timed out (ORA-3136)
Thu May 21 16:44:35 2009
ORA-00604: error occurred at recursive SQL level 1
Thu May 21 16:44:35 2009
ORA-03113: end-of-file on communication channel
Thu May 21 17:00:10 2009
WARNING: inbound connection timed out (ORA-3136)

Cheers!!!!!!!!!!!!

12. Install Crystal Reports on Report Server or client

 

coming sooon!!!!!!!!!!

11. Install configure Cobol to compile Cobol programs

coming soon!!!!!!!!!!!!!!!!

How to fix - ORA-12514

This simple two part procedure will help to diagnose and fix the most common sqlnet and tnsnames configuration problems.

1. Test communication between the client and the listener

We will use tnsping to complete this step. It's a common misconception that tnsping tests connectivity to the instance. In actual fact, it only tests connectivity to the listener.

Here, we will use it to prove that a) the tnsnames.ora has the correct hostname and port, and b) that there is a listener listening on the specified host and port. Run tnsping:

tnsping <your_tns_entry_name>If it is successful you will see something like this:

oracle@bloo$ tnspinng scr9

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP) (HOST = bloo)(PORT = 1521))) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = scr9)))
OK (40 msec)
If not, here are some common errors, and some suggestions for fixing them:

TNS-03505: Failed to resolve name
The specified database name was not found in the tnsnames.ora, onames or ldap. This means that tnsping hasn't even got as far as trying to make contact with a server - it simply can't find any record of the database that you are trying to tnsping. Make sure that you've spelled the database name correctly, and that it has an entry in the tnsnames.ora.

If you have a sqlnet.ora, look at for the setting NAMES.DEFAULT_DOMAIN. If it is set, then all entries in your tnsnames.ora must have a matching domain suffix.

TNS-12545: Connect failed because target host or object does not exist
The host specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with 'ping <hostname>'. If ping returns 'unknown host', speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get 'host unreachable', again speak to your network person, the problem could be down to a routing or firewall issue.

TNS-12541: TNS:no listener
The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.

tnsping hangs for a long time
I've seen this happen in situations where there is something listening on the host/port - but it isn't an oracle listener. Make sure you have specified the correct port, and that your listener is running. If all looks ok, try doing a 'netstat -ap | grep 1521' (or whatever port you are using) to find out what program is listening on that port.

2. Attempt a connection to the instance
Once you have proven that the tnsnames is talking to the listener properly, the next step is to attempt a full connection to the instance. To do this we.ll use sqlplus:

sqlplus [username]/[password]@<your_tns_entry_name>
If it works you will successfully log into the instance. If not, here are some common errors:

ORA-01017: invalid username/password; logon denied
This is actually a good error in these circumstances! Even though you didn't use the correct username or password, you must have successfully made contact with the instance.

ORA-12505: TNS:listener does not currently know of SID given in connect
Either the SID is misspelled in the tnsnames, or the listener isn't listening for it. Check the tnsnames.ora first. If it looks ok, do a 'lsnrctl status' on your server, to see what databases the listener is listening for.

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
This is quite a common error and it means that, while the listener was contactable, the database (or rather the service) specified in the tnsnames wasn't one of the things that it was listening out for.
Begin by looking at your tnsnames.ora. In it, you will a setting like SERVICE_NAME=<name>.

If you are running a single instance database (ie. not RAC), and you are sure that you are not using services, it might be easier to change SERVICE_NAME= to SID= in your tnsnames. Using service names is the more modern way of doing things, and it does have benefits, but SID still works perfectly well (for now anyway).
If you would prefer to continue using service names, you must first check that you have not misspelled the service name in your tnsnames. If it looks alright, next check that the listener is listening for the service. Do this by running 'lsnrctl services' on your server. If there isn't an entry for your service, you need to make sure that the service_names parameter is set correctly on the database.