Tom,
Although you gave a method to load alert file into DB, but the goal of alert file is to log the errors when the DB goes wrong, so it's not right to manage the alert file on top of the DB layer. For example, when your DB goes down or halt, how could you get the alert message from the DB?
It should be up to the OS level to check the alert files.
I use the shell script to rename the alert file for backup, then scan the important keywords in the file to generate the alert message (email or pager) to DBAs. On the other hand, the Oracle will generate a new alert file which will be backuped and scaned when the next scheduled time comes.

Would you have any issues with using an external table to access an alert log from the DB?

Hi Tom: Is it bad to rename the alert log (e.g. mv alert_sid.log alert_sid.old) in a regular basis?
I want to do it for my check alert log script.
Oracle creates a new one if the file doesn't exists but I want to be safe.
Thank You,
Martin.

Hi,
You mentioned that we can rename alert log without a problem.
I thought that oracle keeps the file open throught out its life (until it is shut down). Hence that would mean even if we rename the file it would keep writing to the renamed file since the renamed file would just be a hard link to the orignal inode, since the running oracle process woule never know that the file was renamed.

Could you please tell me where am I going wrong , since I know I am wrong since I have tested it. It works as you mentioned.

Following is the output from my test:

HOP7) fuser alert_HOP7.log1
alert_HOP7.log1: 44884 47740 48510 51344 51602 51860 52118 52376 52634 52892 53150
HOP7) fuser alert_HOP7.log
alert_HOP7.log:
HOP7)

You see the old file still has process attached to it but the new file does not have any process attached to it.

Hi,

Then
why does fuser show the old alert log file still used by the processes

HOP7) fuser alert_HOP7.log1
alert_HOP7.log1: 44884 47740 48510 51344 51602 51860 52118 52376 52634 52892 53150
HOP7)

HOP7) ps -ef|grep 44884
oracle 44884 1 0 Feb 29 - 0:21 ora_pmon_HOP7
oracle 194860 181500 2 16:00:07 p6 0:00 grep 44884
HOP7) ps -ef|grep 47740
oracle 47740 1 0 Feb 29 - 0:15 ora_dbw0_HOP7
oracle 75384 181500 3 16:00:12 p6 0:00 grep 47740
HOP7) ps -ef|grep 48510
oracle 48510 1 1 Feb 29 - 3:29 ora_lgwr_HOP7
oracle 106416 181500 1 16:00:17 p6 0:00 grep 48510
HOP7) ps -ef|grep 51344
ps oracle 51344 1 0 Feb 29 - 0:51 ora_ckpt_HOP7
oracle 208240 181500 2 16:00:22 p6 0:00 grep 51344
-eHOP7) ps -ef|grep 51602
oracle 51602 1 0 Feb 29 - 0:51 ora_smon_HOP7
oracle 208242 181500 1 16:00:27 p6 0:00 grep 51602
HOP7) ps -ef|grep 51860
oracle 51860 1 0 Feb 29 - 0:00 ora_reco_HOP7
oracle 296136 181500 2 16:00:32 p6 0:00 grep 51860
HOP7) ps -ef|grep 52118
oracle 52118 1 0 Feb 29 - 0:00 ora_snp0_HOP7
oracle 310896 181500 2 16:00:37 p6 0:00 grep 52118
HOP7)

and so on

Does alert log file size have any impact on performance. For example, if alert log file size is so huge oracle process has to open a BIG file and write the content, then close it. Right?

Getting back to the original question... OEM is kind of made for this kind of thing. We have a standard "Production DB" event we register against our production databases, one of the tests is for Alert log activity. I can't recall what the criteria are, but basically if something boring gets cut to the alert log, you get no notification, an "interesting" event makes a nice little yellow flag pop up and a "very interesting" (like an ORA-600) makes a red flag pop up (and email/page you if you want it to).

I used to be kind of down on OEM ("GUI - phooey!") but hey, it works, you're already paying for it, and most of us probably have better things to do with our life (...or at least the part of our life our employeers are paying us for...) than reinvent this wheel.

P.S. lots of other cool event test in there as well - broken jobs, full disks etc. you can even get it to watch for corrupt blocks.

I found this on the web. Is this a sensible alternative? IT certainly looks neat and tidy. Obviously needs amending for background_dump_dest etc I am using 8.1.6
Thanks

CREATE TYPE Alert_Row_Type AS OBJECT
(line NUMBER(10),
text VARCHAR2(2000)
);
/

CREATE TYPE Alert_Type IS TABLE OF Alert_Row_Type;
/
CREATE OR REPLACE FUNCTION Get_Alert RETURN Alert_Type IS
Alert_Tab Alert_Type := Alert_Type();
l_file UTL_FILE.file_type;
l_line NUMBER(10) := 1;
l_text VARCHAR2(2000);
BEGIN
l_file := UTL_FILE.fopen('C:\oracle\admin\TSH1\bdump', 'tsh1ALRT.LOG', 'r');
BEGIN
LOOP
utl_file.get_line(l_file, l_text);
Alert_Tab.Extend;
Alert_Tab(Alert_Tab.Last) := Alert_Row_Type(l_line, l_text);
l_line := l_line + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UTL_FILE.fclose(l_file);
RETURN Alert_Tab;
END;
/
CREATE OR REPLACE VIEW diy$alert AS
SELECT *
FROM Table(Cast(Get_Alert() As Alert_Type));

then you can select from the view diy$alert.

Starting with Oracle 8i there is another possibility to have the errors logged out of alert log. Could you please comment SERVERERROR event? I had used AFTER SERVERERROR triggers, and they worked well, but I had to consider switching the trigger off during e.g. migration procedures.

Is it possible to query the table alert_log for ORA-
errors and if found, to read few lines ahead of it
to print the timestamp of the error

Thanks

Is there a simple way to compare the dates in alert log
in character format and sysdate-15/24/60 


SQL> select to_date(substr(text,1,24), 'Dy Mon DD HH24:MI:SS YYYY'), to_date(substr(text,1,24), 'Dy Mon DD HH24:MI:SS YYYY'), sysdate-90
  2  from alert_log where text like '___ ___ __ __:__:__ 20__%' and rownum < 100;
 
TO_DATE(SUBSTR(TEXT,1,24 TO_DATE(SUBSTR(TEXT,1,24 SYSDATE-90
------------------------ ------------------------ ------------------------
Wed Mar 02 11:46:34 2005 Wed Mar 02 11:46:34 2005 Wed Mar 09 14:33:44 2005
Wed Mar 02 11:47:53 2005 Wed Mar 02 11:47:53 2005 Wed Mar 09 14:33:44 2005
Wed Mar 02 11:47:53 2005 Wed Mar 02 11:47:53 2005 Wed Mar 09 14:33:44 2005
Wed Mar 02 11:48:11 2005 Wed Mar 02 11:48:11 2005 Wed Mar 09 14:33:44 2005
Wed Mar 02 11:48:11 2005 Wed Mar 02 11:48:11 2005 Wed Mar 09 14:33:44 2005
Wed Mar 02 11:48:11 2005 Wed Mar 02 11:48:11 2005 Wed Mar 09 14:33:44 2005
Thu Mar 03 07:07:18 2005 Thu Mar 03 07:07:18 2005 Wed Mar 09 14:33:44 2005


SQL> r
  1  select to_date(substr(text,1,24), 'Dy Mon DD HH24:MI:SS YYYY'), to_date(substr(text,1,24), 'Dy Mon DD HH24:MI:SS YYYY'), sysdate-90
  2* from alert_log where text like '___ ___ __ __:__:__ 20__%' and to_date(substr(text,1,24), 'Dy Mon DD HH24:MI:SS YYYY') < sysdate-90
from alert_log where text like '___ ___ __ __:__:__ 20__%' and to_date(substr(text,1,24), 'Dy Mon DD HH24:MI:SS YYYY') < sysdate-90
                                                                       *
ERROR at line 2:
ORA-01846: not a valid day of the week
 

The following script seems to work.
It however prints all the timestamp along with
ORA-,timestamp pair. I like to list only the 
<ORA-,timestamp> pair. Is this simple to do


SQL> l
  1  select text,
  2         lag(text,1) over (order by r) start_time
  3    from
  4    (
  5    select rownum r, text
  6    from alert_log
  7    where
  8      (
  9      text like 'ORA-%'
 10      or
 11      text like '___ ___ __ __:__:__ 20__%'
 12      )
 13      and line
 14      >
 15      (
 16      select min(line) from alert_log where
 17       case
 18        when text like '___ ___ __ __:__:__ 20__%' then
 19        to_date(substr(text,1,25),'Dy Mon DD HH24:MI:SS YYYY')
 20       end
 21       > sysdate-90
 22      )
 23*   )
 

I meant ORA- and the closest timestamp as the <ora-,timestamp> pair.

I am getting a output like

Wed May 25 15:50:42 2005
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...

Wed May 25 15:50:43 2005
Wed May 25 15:50:42 2005

Wed May 25 15:50:47 2005
Wed May 25 15:50:43 2005

Wed May 25 15:50:47 2005
Wed May 25 15:50:47 2005

I like to trim the output so that I get only the
ORA- and the closest timestamp. I like not to
print timestamps without any ORA- line. Can this be done

Hi,

The following is the tail-end of my Alert.log:

Wed Aug 3 09:05:25 2005
ARC0: Beginning to archive log# 4 seq# 506005
ARC0: Completed archiving log# 4 seq# 506005
Wed Aug 3 09:29:30 2005
Thread 1 advanced to log sequence 506007
Current log# 2 seq# 506007 mem# 0: /shared/tagtrak/database/redo/online/redo02.log
Wed Aug 3 09:29:30 2005
ARC0: Beginning to archive log# 1 seq# 506006
ARC0: Completed archiving log# 1 seq# 506006
Wed Aug 3 09:53:29 2005
Thread 1 advanced to log sequence 506008
Current log# 3 seq# 506008 mem# 0: /shared/tagtrak/database/redo/online/redo03.log
Wed Aug 3 09:53:29 2005
ARC0: Beginning to archive log# 2 seq# 506007
ARC0: Completed archiving log# 2 seq# 506007
Wed Aug 3 10:18:08 2005
Thread 1 advanced to log sequence 506009
Wed Aug 3 10:18:08 2005
ARC0: Beginning to archive log# 3 seq# 506008
Wed Aug 3 10:18:08 2005
Current log# 4 seq# 506009 mem# 0: /shared/tagtrak/database/redo/online/redo04.log
Wed Aug 3 10:18:14 2005
ARC0: Completed archiving log# 3 seq# 506008
Wed Aug 3 10:43:16 2005
Thread 1 advanced to log sequence 506010
Wed Aug 3 10:43:16 2005
ARC0: Beginning to archive log# 4 seq# 506009
Wed Aug 3 10:43:16 2005
Current log# 1 seq# 506010 mem# 0: /shared/tagtrak/database/redo/online/redo01.log
Wed Aug 3 10:43:22 2005
ARC0: Completed archiving log# 4 seq# 506009
Wed Aug 3 11:11:18 2005
Thread 1 advanced to log sequence 506011
Current log# 2 seq# 506011 mem# 0: /shared/tagtrak/database/redo/online/redo02.log
Wed Aug 3 11:11:18 2005
ARC0: Beginning to archive log# 1 seq# 506010
ARC0: Completed archiving log# 1 seq# 506010


Does each timestamp in the log apply to the message or messages that follow it? e.g. for the final 3 lines in the above, does Wed Aug 3 11:11:18 2005 apply to both ARC0: Beginning to archive log# 1 seq# 506010 AND ARC0: Completed archiving log# 1 seq# 506010 ?

I ask, as I would have thought that the beginning and completion of archiving a redo log would be slow enough to have a different beginning and completion timestamp.

So, does

Wed Aug 3 11:11:18 2005
ARC0: Beginning to archive log# 1 seq# 506010
ARC0: Completed archiving log# 1 seq# 506010

mean that the 2 ARC0 messages were emitted so close together that Oracle thought, "No point putting the timestamp twice; the 2 ARCOs happened, too close together to measure."?

Hi Tom,

We are getting these ORA-01847 error messages in alert.log. Is there any way we can prevent these errors from being recorded in alert.log since they are user data entry errors for date fields. And these errors are not recorded always, they are recorded in alert.log only in some cases

Thu Oct 6 00:12:25 2005
Errors in file /sbclocal/app/oracle/admin/INMECP1/udump/inmecp1_ora_28295.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01847: day of month must be between 1 and last day of month Thu Oct 6 00:12:26 2005 Errors in file /sbclocal/app/oracle/admin/INMECP1/udump/inmecp1_ora_28295.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01847: day of month must be between 1 and last day of month Thu Oct 6 02:07:34 2005 Errors in ....

Is it being recorded in alert.log since the error is being generated at by a recursive SQL executed as part of the orignal SQL query ?

Because when I tried this

alter session set nls_date_format='mm/dd/yyyy hh:mi:ss AM'

select * from issue a where a.VALID_TO = '01/00/2005 12:00:00 AM'
The above Ora-1847 error in not being recorded in alert.log, it is only returned to the user

Is there any way we can avoid this error from being recorded in alert.log ?

Anto

> show parameter event

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 10499 trace name context forev
er, level 1

The only event which is set is event 10499

Event 10499
Event 10499 was introduced in release 9.2.0.3 to allow customers to revert to the earlier behavior of NUMBER datatypes that have no explicit setting of their scale value. Any non-zero setting of the event level will enable the old behavior.

Those errors are logged from users running reports in Business Objects against Oracle 9204 Database. Most of these reports use star transformation, even though that may not be relevant here.

What I meant is that I am not able to reproduce that error either from sqlplus or BO . But we are getting this error in alert.log once in a while, while users are running reports using BO(business objects) against Oracle database. But there is no oracle setting either at the BO level or session level

Since those errors are getting logged to alert.log, dbservices micromuse alert goes off due to that and they in turn contact us for the same, even though the error is a USER error and can be safely ignored. So was wondering why these type of errors are getting logged to alert.log and whether there is any way to prevent that from happening.

Maybe I will be better off, creating a TAR with oracle, for the same.

Anto

Hi Tom.
At the beginning of this page you wrote:
"this should be a package. the package would remember
where I left off bytewise so that I could call a refresh
that would load just NEW lines instead of the entire file
again.
That way, if I kept a session open for a while --
i could load it once and just refresh during the day."

My Question :
I would liketo load the file only once per day, and i want
to see only the NEW lines that had been added to that
file. how can i make it "remember" the last line ?

Thank You.

Hi Tom,

From the alert.log

ARC0: Evaluating archive log 5 thread 1 sequence 13334
ARC0: Beginning to archive log 5 thread 1 sequence 13334
Creating archive destination LOG_ARCHIVE_DEST_1: '/sbclocal/app/oracle/admin/SMLICP1/arch/SMLICP1_0000013334.arc'
Wed Jan 25 09:29:01 2006
ARC0: Completed archiving log 5 thread 1 sequence 13334
Wed Jan 25 14:18:54 2006
ORA-1652: unable to extend temp segment by 640 in tablespace TEMP1

Obviously some bad SQL using maybe a merge join cartesian join must have consumed the whole TEMP1 tablespace

TEMP1 tablespace is quite big for us - around 30 GB, since ours is a datawarehouse

Is there any way we can find out this offending SQL which is using a bad plan - from V$SQL or any other dictionary views like that ?

thanks
Anto

I was able to get the sql and other user details of the SQL which gave this TEMP1 error, from v$SQL and V$SQLTEXT based on the timings, number of rows(0 in this case, since it errored out) and other big numbers in statistics

thanks
Anto

Hi Tom,

I know the word 'obviously' was not the right word there.

But it was almost certain in our case/project - from past similar experiences - whenever we had a issue with such big TEMP tablespace running out of space, it was due to a merge join cartesian due to a missing join, by the business user

thanks
Anto

Thanks for the link. That was very useful.

But v$SQL provides a wealth of information(without any extra overhead) from which, one can narrow down the offending SQL - in particular, the last_load_time,sql_text,rows_processed,disk_reads,elapsed_time,sorts,parsing_user_id etc gives enough clue to narrow down the SQL(may not be always true for a new user of the database/project). Even in this case as well, it was indeed a merge cartesian join, due to a missing join

thanks
Anto

Hi Tom,

In our environment, there are a lot of big queries - SELECT statements that are doing huge sorts using 'group by' and which uses temp_space(from v$sql_plan) of the order of hundreds of MegaBytes and sometimes even a few gigabytes. Those have not given any error, so far.

As you pointed out before - almost every time, it is the smallers SELECT SQLs which are using a CARTESIAN join which is failing, even though from V$SQL_PLAN it looks as if, these consumed only around 50 or 60 MB of TEMP_SPACE

Is there any reason for this behaviour - why big GROUP BY works fine even after consuming big temp space, whereas smaller cartesian joins fail even after consuming 50 or 60 MB of temp_space ?

thanks
Anto

Database version is 9204

Anto

Thanks for that info

I should have checked oracle metalink first. I have got into the habit of first asking in asktom.oracle.com rather than first checking in oracle metalink


SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

gives TEMP space usage by various sessions while sort is going on

I guess there is no way to know the TEMP space usage, once SQLs are completed

Anto

Hi Tom,

As posted before - regarding Ora-01847 errors being logged into alert.log - which in normal circumstances should not have happened unless we have some event set

We are actually getting a Ora-604 always preceding this ora-1847 and may have been caused by this ora-1847

ORA-00604: error occurred at recursive SQL level 1
ORA-01847: day of month must be between 1 and last day of month

Is this due to this "ORA-00604" error, that these errors are getting logged to alert.log ?

Does all ORA-006XX errors get logged to alert.log ?

thanks
Anto

Hi,

We get those errors logged into alert.log when Business Objects Webi users running reports against the oracle database enters date in the wrong format - (for eg: >12 for month)

Those errors are not logged into alert.log when the same input date is entered wrongly from sqlplus - so maybe BO webi is setting something for each webi connection which is forcing those errors to be logged to alert.log

Those 2 lines are the only 2 lines in alert.log wrt this error. The first few lines of the trace file :

Dump file /sbclocal/app/oracle/admin/SMCMISP1/udump/smcmisp1_ora_24195.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /sbclocal/app/oracle/product/9.2.0.4.0.1
System name: SunOS
Node name: sstm2155por
Release: 5.8
Version: Generic_117350-12
Machine: sun4us
Instance name: SMCMISP1
Redo thread mounted by this instance: 1
Oracle process number: 98
Unix process pid: 24195, image: oracle@sstm2155por (TNS V1-V3)

*** SESSION ID:(153.6174) 2006-06-13 05:42:42.219
ORA-00604: error occurred at recursive SQL level 1
ORA-01847: day of month must be between 1 and last day of month
Dump of memory from 0x0000000528D753B4 to 0x0000000528D7579E
528D753B0 494E5345 5254202F 2A2B2041 [INSERT /*+ A]
528D753C0 5050454E 44204259 50415353 5F524543 [PPEND BYPASS_REC]
528D753D0 55525349 56455F43 4845434B 202A2F20 [URSIVE_CHECK */ ]
528D753E0 494E544F 20225359 53222E22 5359535F [INTO "SYS"."SYS_]
528D753F0 54454D50 5F304644 39443639 42365F31 [TEMP_0FD9D69B6_1]
528D75400 31464332 42333222 2053454C 45435420 [1FC2B32" SELECT ]
528D75410 2F2A2B20 53454D49 4A4F494E 5F445249 [/*+ SEMIJOIN_DRI]
528D75420 56455220 2A2F2022 5441475F 4C564C5F [VER */ "TAG_LVL_]
528D75430 42555345 4E54222E 22425553 454E545F [BUSENT"."BUSENT_]
528D75440 4C455645 4C5F4944 22202243 30222C22 [LEVEL_ID" "C0","]
528D75450 5441475F 4C564C5F 42555345 4E54222E [TAG_LVL_BUSENT".]
...
...

thx
Anto

Thanks

Anto

Hi,

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 48 5103
RECURSIVE EXECUTION SYS_LE_3_0
RECURSIVE EXECUTION SYS_LE_3_1
TEMP TABLE TRANSFORMATION <<<<<<<<<<
SORT GROUP BY 48 15 K 5103
FILTER
NESTED LOOPS OUTER 48 15 K 5101 :Q1066015007 P->S QC (RANDOM)
NESTED LOOPS 48 14 K 5100 :Q1066015007 PCWP
NESTED LOOPS 48 12 K 3900 :Q1066015007 PCWP
HASH JOIN 48 11 K 2700 :Q1066015007 PCWP
HASH JOIN 48 9 K

Before sending the trace to support, a question

The above is the initial few lines of the explain plan when dates are given in the right format, for the problem query.

Is it the fact that this error is generated during the process of TEMP table transformation for this query, that is forcing this error to get logged to alert.log ?

I guess that is the case since, after giving

alter session set star_transformation_enabled=false

and giving dates in the wrong format for the SQL query - this error is not getting logged to alert.log

btw - This error was getting logged not only from Webi reports(as incorrectly mentioned by me before) but even from TOAD sessions - so it is not the issue with BO Webi reports, but I guess is happening since the error is getting generated during the TEMP table transformation phase

thx
Anto

Thanks Tom for your help

The response from Oracle Support from opening TARs have been NOT that encouraging so far - not from my current client at least. I have stopped lodging TARs with Oracle support for the past 2 years or so - normally that is a waste of time

Maybe it depends on the client and the type of support

I have created a TAR - will let you know if we manage to find the cause and workaround from Oracle support

thanks
Anto

Hi

this line in your code

l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );

what is '0A' ???

cheers

Hi,

This one seems to be a basic question but i am expecting an detailed explanation for what is a thread and what role it plays with ref to database.

The following creates an external table and a view which selects lines concerning ORA- errors. Works for me.

CREATE OR REPLACE DIRECTORY BDUMP AS 'D:\oraadmin\<SID>\bdump'

CREATE TABLE ALERTLOG
(
MSG_LINE VARCHAR2(1000 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY BDUMP
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
nobadfile nologfile nodiscardfile
skip 0
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
MSG_LINE (1:1000) CHAR(1000)
)
)
LOCATION (BDUMP:'alert_<SID>.log')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING

CREATE OR REPLACE FORCE VIEW ALERT_LOG_ERRORS
(LINENO, MSG_LINE, THEDATE, ORA_ERROR)
AS
select "LINENO","MSG_LINE","THEDATE","ORA_ERROR"
from (
select *
from (
select lineno,
msg_line,
thedate,
max( case when ora_error like 'ORA-%'
then rtrim(substr(ora_error,1,instr(ora_error,' ')-1),':')
else null
end ) over (partition by thedate) ora_error
from (
select lineno,
msg_line,
max(thedate) over (order by lineno) thedate,
lead(msg_line) over (order by lineno) ora_error
from (
select rownum lineno,
substr( msg_line, 1, 132 ) msg_line,
case when msg_line like '___ ___ __ __:__:__ ____'
then to_date( msg_line, 'Dy Mon DD hh24:mi:ss yyyy' )
else null
end thedate
from alertlog
)
)
)
)
where ora_error is not null
order by thedate

Dear Tom,

Are there any dbms packages that can send messages to alert log file?

regards,
Dean

Hi Tom,

Recently there was a memory upgrade to on of our production boxes. Our shutdown and startup scripts didn't work, so when the machine came back up the database didn't. I logged in and started it and everything is fine.

However, ever since then, we are seeing this is the alert log

ARC1: Thread not mounted

Do you have any idea what this means? We don't know if this is a problem or not. Thanks.

Hi Tom,
How to analyze the alert log files...?generally on what type of errors we need to concentrate..?

Thanks and Regards
Srikanth

We are planning a strategy for backing up alert log files,
trace files, and other log files such as listener.log, sqlnet.log etc.

Can you point to any links where help can be found to develop strategy.

After reading the below link:-

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1352202934074#16054145222006

I have following questions.

1- What if Oracle is in between writing the alert log and at the same time we "mv" the alert log.

2- How can we backup trace files cleanly, because we dont know when oracle closes them? Same question is for listener.log and sqlnet.log files.

We need these backups for our record purposes, and in case of any S.R. at metalink, if they require an old trace file , we would be able to provide. Also, we need to avoid disk full messages on the disks where these files are stored.

Hi Tom

I would like to monitor the alert log, and would like to have a detailed documentation of the structure of the alert log.
Do You know of any?

Br
Kim

What we have learnt the hard way is that monitoring ORA- errors does not guarantee database integrity. Corrupt blocks do not result in any ORA- errors but are critical. So now we check for keyword "corrupt". There could be other error messages that we could still not be looking for because Oracle has poor error reporting standards. Unless Oracle standardizes, we will always be at risk.

I am facing a rather peculiar problem here with timestamps between instance and the alert logs. The alert log timestamps are 5 hours behind. The OS timestamp, the instance timestamp are all same. But the alert log shows a different timestamp. 
Can you help me in identifying the reason behind it or how to resolve it, please ?


SQL> ! tail -5 /dborafiles/PRAM/PRAMU02/admin/bdump/alert_PRAMU02.log
Use ALTER SYSTEM SET db_recovery_file_dest_size command
to add space. DO NOT manually remove flashback log files
to create space.
*************************************************************
SUCCESS: diskgroup PRAMU_A06615_BACKUP_ASM was mounted
SQL>
SQL>
SQL>
SQL> alter system switch logfile;

System altered.
SQL>
SQL>
SQL>
SQL>  ! tail -5 /dborafiles/PRAM/PRAMU02/admin/bdump/alert_PRAMU02.log
SUCCESS: diskgroup PRAMU_A06615_BACKUP_ASM was mounted
Fri Apr  4 09:06:32 2008
Thread 2 advanced to log sequence 6714
  Current log# 16 seq# 6714 mem# 0: +PRAMU_A06615_DATA1_ASM/PRAMU/onlinelog/redo16a.rdo
  Current log# 16 seq# 6714 mem# 1: +PRAMU_A06615_DATA1_ASM/PRAMU/onlinelog/redo16b.rdo
SQL>
SQL>
SQL>
SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
04-APR-08 15.06.48.250600 +01:00
SQL>
SQL>
SQL>
SQL> ! date
Fri Apr  4 15:06:52 BST 2008
SQL>
SQL>
SQL>

Hi Tom,

When I look at the listener.log for an application such as SQL Developer, (another one we have is Aqua Data Studio), the Program= is null, and the first Host=__jdbc__.

i.e.
03-SEP-2009 07:44:50 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=sure999))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.99)(PORT=2454)) * establish * TEST * 0

I like Toad better, as it populates these:
03-SEP-2009 08:47:08 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=C:\Toad\TOAD.exe)(HOST=CDL73)(USER=sure999))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.99)(PORT=2777)) * establish * TEST * 0

While both of these applications populate the v$session variables such as machine, terminal, client_info, it would be nice to have the listener.log properly populated as well, as the listener.log contains some valuable auditing information. Short of a new feature request to the vendor, is there a way of populating the listener.log fields on connection to the database?

Is there a command to populate the listener.log entries?

For V$SESSION it is dbms_application_info.
Is it some library call within the compiled client binary, or is there a way of manually setting it via a command?

I'm referring to the PROGRAM and the (first) HOST entry in the listener.log.

For example, you can see from the below that I'm connecting to the database via sqlplus.exe, sqlplusw.exe, and TOAD (all from my PC). It also shows my PC name (CDL73), and my IP address (the second HOST=), which is really useful for auditing.


SQLPlus from Windows C:\ prompt
-------------------------------
07-SEP-2009 11:30:41 * (CONNECT_DATA=(SID=TXT2STOP)(CID=(PROGRAM=C:\Oracle\product\10.1.0\bin\sqlplus.exe)(HOST=CDL73)(USER=sure006))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.66)(PORT=2059)) * establish * TXT2STOP * 0


SQLPlus for Windows (sqlplusw)
-------------------
07-SEP-2009 11:33:47 * (CONNECT_DATA=(SID=TXT2STOP)(CID=(PROGRAM=C:\Oracle\product\10.1.0\bin\sqlplusw.exe)(HOST=CDL73)(USER=sure006))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.66)(PORT=2068)) * establish * TXT2STOP * 0


TOAD
----
07-SEP-2009 11:36:06 * (CONNECT_DATA=(SID=TXT2STOP)(CID=(PROGRAM=C:\Toad\TOAD.exe)(HOST=CDL73)(USER=sure006))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.66)(PORT=2070)) * establish * TXT2STOP * 0


Even logged in on the server locally and invoking SQLPlus populates this with program name. The HOST= value is set to our server name, which is also helpful.


Locally from Unix prompt
------------------------
07-SEP-2009 11:35:04 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TXT2STOP.ctru.auckland.ac.nz)(CID=(PROGRAM=sqlplus)(HOST=txt2stop2.ctru.auckland.ac.nz)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.109)(PORT=29477)) * establish * TXT2STOP.ctru.auckland.ac.nz * 0


However, when I log in to an application that use the JDBC thin client to connect to the database, the PROGRAM is blank (PROGRAM=), and the (first) HOST=__jdbc__. This isn't good... I want to know the program and the computer/server name as above.


For example:


SQL Developer (from my PC)
-------------
07-SEP-2009 11:36:52 * (CONNECT_DATA=(SID=TXT2STOP)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=sure006))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.66)(PORT=2072)) * establish * TXT2STOP * 0


Aqua Data Studio (from my PC)
----------------
07-SEP-2009 11:37:43 * (CONNECT_DATA=(SID=txt2stop)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.66)(PORT=2073)) * establish * txt2stop * 0


JBoss (running locally on the server)
-----
07-SEP-2009 11:59:31 * (CONNECT_DATA=(SID=TXT2STOP)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=130.216.120.109)(PORT=58705)) * establish * TXT2STOP * 0


Ideally, I'd like to see:
PROGRAM=SQLDeveloper HOST=CDL73 (my PC name)
PROGRAM=AquaDataStudio HOST=CDL73
PROGRAM=JBoss HOST=txt2stop2.ctru.auckland.ac.nz


You can also see from above that Aqua Data Studio and Jboss didn't even populate the USER= field of the listener.log entry, which is most annoying.

Which leads me back to my question,... is it possible to set the PROGRAM=, HOST= fields so they display correctly in the listener.log when a connection is made?

My SQL query against the listener.log works wonderfully well. I just wish the entries had all the information I wanted!

Maybe there is a better way of getting this information (i.e. user, ip address, program, time of connection, etc). The listener.log has a plethora of information that is often overlooked. It seems a very efficient place to start looking for it.

Hopefully this explains better what I'm trying to achieve.

Hi Tom,

This is related to an alert log issue that i faced few hours ago.

1) Today i realised that ORACLE had stopped writing to alert log. The last date in the alert log was June 20 10:00 PM.

2) We have an alert log renaming mechanism. This process renames the alert log as per date and creates a new one.

3) There was no new alert log for today's date.

4) We checked the database. Everything was fine. BDUMP directory is properly set.

5) Today at 9.00 AM we found a new alert log with today's date and with content starting at June 21 00:30 AM.

6) I did a switch logfile and checked that the data has been updated in the alert log.

My Questions are

1) What led oracle to stop writing to the alert log from June 20 10:00 PM to June 21 00:30 AM.

2) What made oracle create a new alert log at June 21 9.00 AM with content starting from June 21 00:30 AM. This should have happened at June 21 00:00 AM. But it didnt.

Why oracle behaved like this.

The DB is 9.2.0.4.0.

Kindly advice.

Tom, I would like to know if all errors in the alert.log file start with the prefix 'ORA-' ... even errors associated with ASM PLS and others, would bring an ORA- message as well. I guess my question is can I search in the alert log for 'ORA-' and would that be all I need to capture issues within the DB (10g/11g).
Thanks

Tom,

I am using your above procedure load_alert to accomplish something similar , however its failing at 
... insert into alert_log 
due to utl_raw.cast_to_varchar2 as I see the lines in my file are much longer than 4000 bytes. 

SQL> exec load_myfile;
BEGIN load_myfile; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
ORA-06512: at "FUSER.LOAD_MYFILE", line 26
ORA-06512: at line 1

In my case I don't need the characters beyond 4k, can you please recommend a viable option.

Thanks.

l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
        exit when (nvl(l_current,0) = 0);

        insert into alert_log
        ( line, text )
        values
        ( l_line, 
          utl_raw.cast_to_varchar2( 
              dbms_lob.substr( l_bfile, l_current-l_last+1, 
                                                    l_last ) )
        );

So i verified as per your feedback, each line returns about ~100 bytes or less, with the exception of line 12 of the file - returning about 12,356 characters ( l_current-l_last+1) on the utl_raw.cast_to_varchar2 function output.

I tried different options however I wasn't able to figure out just for that line how can i pass fewer than 4k through the substr. For now I am using a workaround, but it stinks and don't even wanna mention it :-)

As always your solutions greatly appreciated.

if ( l_current-l_last+1 > 4000 )
then
   l_length := 4000;
else
   l_length := l_current-l_last+1;
end if;

.....
 utl_raw.cast_to_varchar2( 
              dbms_lob.substr( l_bfile, l_length, l_last ) )
........

...if you're on db version 11.2 (and onwards).
Not sure why it's undocumented.

Ohh, I was making it too complicated, thanks for clearing it up.

Walter.

In 11g, I use sys.alert_log_view in a script which sends me email containing the errors encountered since the last time the script ran. The key columns I use are "originating_timestamp" and "message_text". No need to access the filesystem containing the actual alert log.

I filter out certain types of messages which I've deemed inconsequential. Works quite well.

select message_text from sys.X$DBGALERTEXT
where rownum < 5;

Would be good if a new answer could address

V$diag_alert_ext

or

x$dbgalertext

.