The old Oracle database (8i) was switched to the read-only mode. The following messages are shown now in alert.log:
***Warning – Executing transaction without active Undo Tablespace
It’s possible, that the error is caused by Oracle bug 3270493 (EXCESSIVE QMNX TRACE FILES WHEN PLACING STANDBY IN READ ONLY MODE). Workaround is to set aq_tm_processes parameter to 0:
ALTER SYSTEM SET aq_tm_processes=0;
Sometimes it’s necessary to read the information from the alert log together with the timestamp of the error.
The very simple Unix-shell script helps to do this:
echo
echo 'Enter # of lines:'
read NUM
tail -$NUM alert_${ORACLE_SID}.log | awk '
BEGIN {prev="" ; ret=1 }
/^(...-|Error)/ { if ( prev !~ /^(...-|Error)/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '
This code could be assigned to some hotkey in Your favorite telnet application, so You just need to press something like “Alt+A” to see the quick review of the recent Oracle errors.
Example:
Enter # of lines:
1000
Sep 01 19:42:03 2009
Errors in fileARC0: Archiving not possible: error count exceeded
Sep 01 19:42:06 2009
Errors in file testsrv_arc0_23486.trc:
ORA-16038: log 1 sequence# 112 cannot be archived
ORA-19502: write error on file “”, blockno (blocksize=)
ORA-00312: online log 1 thread 1: ‘redo_g1_m1.log’
The following undocumented function could be used to write to the alert log:
execute sys.dbms_system.ksdwrt(code, message);
Parameters:
1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.
Example:
execute sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY')||'Hello!' );
create or replace trigger trg_delme
BEFORE UPDATE on frodo.delme
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
sys.dbms_system.ksdwrt(2,to_char(sysdate, 'Dy Mon DD HH24:MI:SS YYYY') );
end;