Alert log error message related to listener down on a remote host

The alert log of an Oracle database is an indenspensible source for diagnosing many problems. Any error messages from it deserve investigation proactively. Last night I had a chance to expose myself of some views that I am not very familar with during the process of understanding alert log error messages.

The error messages from the alert log I recieved in email were as follows:

------
proddb12 on bspdm15a : Mon Aug 11 21:50:19 PDT 2008 Message from red_alert ...

Below are ORA- errors found in alert log (/opt/app/p1fwd1d2/oracle/admin/proddb12/bdump/alert_proddb12.log). See log for more information.

ORA-12012: error on auto execute of job 171
ORA-12541: TNS:no listener
ORA-12012: error on auto execute of job 183
ORA-12541: TNS:no listener
ORA-12012: error on auto execute of job 170

-----

To see what those jobs are, I query the dba_jobs view:

select job, what from dba_jobs
where job in (171,183,170)
/

JOB
----------
WHAT
--------------------------------------------------------------------------------
183
dbms_refresh.refresh('"GOLD_GPRS"."BORDER_PLMN"');

170
dbms_refresh.refresh('"GOLD_GPRS"."DG_CATEGORY"');

171
dbms_refresh.refresh('"GOLD_GPRS"."DG_SPEC_GROUP"');



From the above results, it can be concluded that those jobs are releated to snaphot feature.I subsequently checked the dba_refresh and dba_snapshots views, and was able to find out the refresh group those jobs are responsible for and the dblink that the refresh group used:

1 select refgroup, job from dba_refresh
2* where job in (183,170,171)
SQL> /

REFGROUP JOB
---------- ----------
21 183
11 170
12 171



SQL> ;
1* select name, refresh_group, master_link from dba_snapshots
SQL> /

NAME REFRESH_GROUP MASTER_LINK
-------------------- ------------- ----------------------------------------
...
BORDER_PLMN 21 @PRODDB_GOLD_GPRS_LINK.EDC.COMPANY.NET
DG_CATEGORY 11 @PRODDB_GOLD_GPRS_LINK.EDC.COMPANY.NET
DG_SPEC_GROUP 12 @PRODDB_GOLD_GPRS_LINK.EDC.COMPANY.NET
...


Querying the dba_db_links, got the following:

select db_link, host from dba_db_links
/

DB_LINK HOST
------------------------------ ----------
PROD.EDC.COMPANY.NET proddb
SDB.EDC.COMPANY.NET p3fwdar
PRODDB_GOLD_GPRS_LINK.EDC.COMPANY.NET PRODDB
P0FUSWKS.WDC.COMPANY.NET p0fuswks
PRODDB_CSGNOR_LINK.EDC.COMPANT.NET PRODDB


Finally checked the availability of PRODDB by tnsping:

$ tnsping proddb

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 11-AUG-2008 22:41:41

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU=32767) (ADDRESS = (PROTOCOL = TCP)(HOST = p1fd1d1.edc.company.net)(PORT = 1547)) (CONNECT_DATA = (SERVICE_NAME = proddb)))
TNS-12541: TNS:no listener

At this point, from the snapshot point of view it was obvious that the listener in the master database server was unavailable at that time, which caused we see 'TNS:no listener' in the snapshot database alert logs. Later on we did receive email from other DBAs saying that maintenance tasks were going on the master database server.

So in this case, the alert log message of a database revealed problems of a remote database.