Purging Oracle ADR (Automatic Diagnostic Repository) logs

 

When ADR logs become unexpectedly large, or too many (as in too many databases for one host), then manual purging is necessary. Following Scripts will be of handy during this time.

#!/bin/ksh # Purge ADR logs # Call oraenv first (or at least set $ORACLE_HOME) # -- Customize the following variables D1=1440    # to purge older than one day D5=7200    # to purge older than five days D7=10080   # to purge older than seven days DD=        # set to your ADR_BASE (Ex. /app/oracle) HP=        # set to your ADR_HOME (Ex. diag/rdbms/<dbname>/<instname>) $ORACLE_HOME/bin/adrci <<_EOF set base $DD set homepath $HP show homes purge -age $D7 -type alert purge -age $D7 -type incident purge -age $D7 -type trace purge -age $D7 -type cdump quit _EOF

Daily check on the database before business volume picks up

Session Count

SQL>     SELECT a.inst_id, COUNT (1) "Session Count"      FROM sys.gv_$session a     GROUP BY a.inst_id SQL> /    INST_ID Session Count ---------- -------------          1            67          2           105

Long Operation – Time Remaining is greater than Zero.

SQL> l     SELECT * FROM sys.gv_$session_longops     WHERE  TIME_REMAINING > 0

DBMS Jobs

SELECT job,        SUBSTR (what, 1, 30),        schema_user,        last_date,        next_date,        broken,        NVL2 (this_date, 'Y', 'N') running   FROM dba_jobs s ORDER BY schema_user, last_date

Buffer Gets

Replace the v.sql text with the frequent SQL statements important to your application.

SELECT v.inst_id,        ROUND (v.buffer_gets / v.executions, 1) avggets,        v.executions,        v.module,        SUBSTR (v.sql_text, 1, 60) querysql   FROM sys.gv_$sql v WHERE v.buffer_gets > 0    AND v.executions > 0    AND v.executions IS NOT NULL    AND DECODE (executions, 0, NULL, (v.buffer_gets / v.executions)) > 10    AND (v.sql_text LIKE 'FROM TBL_ABC%'      OR  v.sql_text LIKE 'FROM TBL_XYZ%')  ORDER BY v.inst_id, avggets DESC;

Oracle Alert Monitoring

 

Here is the list of Alters in general to be considered for Oracle Alerts Monitoring.

Severity	Oracle MessageNo	Message
critical	ORA-00376	file %s cannot be read at this time
critical	ORA-00449	background process '%s' unexpectedly terminated with error %s
critical	ORA-00490	PSP process terminated with error
critical	ORA-00600	internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
critical	ORA-01110	data file %s: '%s'
critical	ORA-01113	file %s needs media recovery
critical	ORA-01114	IO error writing block to file %s (block # %s)
critical	ORA-01115	IO error reading block from file %s (block # %s)
critical	ORA-01122	database file %s failed verification check
critical	ORA-01157	cannot identify/lock data file %s - see DBWR trace file
critical	ORA-01171	datafile %s going offline due to error advancing checkpoint
critical	ORA-01186	file %s failed verification tests
critical	ORA-01208	data file is an old version - not accessing current version
critical	ORA-12535	TNS:operation timed out
critical	ORA-01251	Unknown File Header Version read for file number %s
critical	ORA-01259	unable to delete datafile %s
critical	ORA-01578	ORACLE data block corrupted (file # %s, block # %s)
critical	ORA-1632	max # extents (%s) reached in index %s.%s
critical	ORA-1652	unable to extend temp segment by %s in tablespace %s
critical	ORA-1653	unable to extend table %s.%s by %s in tablespace %s
critical	ORA-01658	unable to create INITIAL extent for segment in tablespace %s
critical	ORA-16038	log %s sequence# %s cannot be archived
critical	ORA-1683	unable to extend index %s.%s partition %s by %s in tablespace %s
critical	ORA-01688	unable to extend table %s.%s partition %s by %s in tablespace %s
critical	ORA-02068	following severe error from %s%s
critical	ORA-03113	end-of-file on communication channel
critical	ORA-04031	unable to allocate %s bytes of shared memory (\%s\",\"%s\",\"%s\",\"%s\")"
critical	ORA-07445	exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]
critical	ORA-17500	ODM err:%s
critical	ORA-17502	ksfdcre:%s Failed to create file %s
critical	ORA-17503	ksfdopn:%s Failed to open file %s
critical	ORA-19504	failed to create file \%s\""
critical		terminating instance due to error
critical		PMON failed to acquire latch, see PMON dump
warning	ORA-
exclude	ORA-00001	Unique constraint violated
exclude	ORA-00028	your session has been killed
exclude	ORA-00060	deadlock detected while waiting for resource
exclude	ORA-00601	cleanup lock conflict
exclude	ORA-00604	error occurred at recursive SQL level %s
exclude	ORA-00918	column ambiguously defined
exclude	ORA-01001	invalid cursor
exclude	ORA-01013	user requested cancel of current operation
exclude	ORA-01017	invalid username/pwd; logon denied
exclude	ORA-01034	oracle not available
exclude	ORA-01089	immediate shutdown in progress
exclude	ORA-01119	error in creating database file '%s'
exclude	ORA-01403	no data found
exclude	ORA-01551	extended rollback seg, pinned blocks released
exclude	ORA-01555	snapshot too old
exclude	ORA-01575	timeout waiting for space mgmt resource
exclude	ORA-01591	lock held by in-doubt distributed transaction
exclude	ORA-01595	error freeing extent %s of rollback seg %s
exclude	ORA-01634	rollback seg number '%s' is about to go offline
exclude	ORA-01722	invalid number
exclude	ORA-01841	(full) year must be between -4713 and +9999, and not be 0
exclude	ORA-01843	not a valid month
exclude	ORA-02050	transaction %s rolled back, some remote DBs may be in-doubt
exclude	ORA-02052	remote transaction failure at %s
exclude	ORA-02063	preceding %s%s from %s%s (error recvd from remote db link)
exclude	ORA-02067	transaction or savepoint rollback required
exclude	ORA-02068	following severe error from %s%s (a severe error recvd from indicated db link)
exclude	ORA-02091	transaction rolled back
exclude	ORA-02291	integrity constraint violated - parent key not found
exclude	ORA-03106	fatal two-task communication protocol error
exclude	ORA-03113	end-of-file on communication channel
exclude	ORA-03297	file contains used data beyond requested RESIZE value
exclude	ORA-04060	insufficient privs to execute stored proc
exclude	ORA-04061	existing state of stored proc has been invalidated
exclude	ORA-04062	timestamp of stored proc has been changed
exclude	ORA-04063	object has errors
exclude	ORA-04064	not executed, invalidated stored proc
exclude	ORA-04065	not executed, altered or dropped stored proc
exclude	ORA-04066	non-executable object
exclude	ORA-04067	not executed, stored proc does not exist
exclude	ORA-04068	existing state of packages has been discarded
exclude	ORA-06508	PL/SQL: could not find program unit being called
exclude	ORA-06512	at %s line %s (backtrace msg as the stack is unwound by unhandled exeptions)
exclude	ORA-1031	insufficient privs (an attempt was made to chg the current username/pwd)
exclude	ORA-10387	parallel query server interrupt (normal)
exclude	ORA-1142	cannot end online bkup - none of the files are in bkup mode
exclude	ORA-1146	cannot start online bkup - file is already in bkup mode
exclude	ORA-12008	error in snapshot refresh path
exclude	ORA-12012	error on auto execute of job %s
exclude	ORA-12203	TNS: unable to connect to destination
exclude	ORA-12535	TNS: operation timed out
exclude	ORA-1537	cannot add datafile %s - file already part of db
exclude	ORA-1636	rollback seg %s is already online
exclude	ORA-29549	class %s.%s has changed, Java session state cleared
exclude	ORA-3217	invalid option for alter of TEMPORARY TABLESPACE
exclude	ORA-959	tablsp %s does not exist
exclude	ORA-00600	internal error code, arguments: [12235] Oracle process has no purpose in life.