ORACLE Trace and Dump file Alert

Oracle Database Alerts;
-Trace and Dump file Alert
root> which sqlplus
root> pwd
root> find . -print|grep –i dbmspool.sql
background_dump_destination
# Cleanup trace files more than 7 days old
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7 -exec rm {} \;

Note that the first part of this script (before the –exec) displays all trace files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7

Using Trace Files

This section discusses the following trace file subjects:

• Background Thread Trace Files
• User Thread Trace Files
• Alert File
• Error Call Trace Stack

• $ORACLE_HOME/admin/db_name/bdump on UNIX operating systems
• %ORACLE_HOME%\admin\db_name\bdump on Windows NT and Windows 2000 operating systems

The Oracle database creates a different trace file for each background thread. The name of the trace file contains the name of the background thread followed by the extension .trc, such as:

• siddbwr.trc
• sidsmon.trc

sidlckn.trc	Trace file for the Global Cache Service Processes (LMSn). This trace file shows lock requests for other background processes.
sidlmdn.trc	Trace file for the LMDn process. This trace file shows lock requests.
sidlmon.trc	Trace file for the LMON process. This trace file shows the status of the cluster.
sidp00n.trc	Trace file for the parallel execution processes.

The sidalrt.log file is in the directory specified by the BACKGROUND_DUMP_DEST parameter in the initdb_name.ora initialization parameter file. If you do not set the BACKGROUND_DUMP_DEST parameter, the sidalrt.log file is generated in:

• $ORACLE_BASE/admin/db_name/bdump on UNIX operating systems
• %ORACLE_HOME%\admin\db_name\bdump on Windows NT and Windows 2000 operating systems

SELECT pid "Oracle Process Id",
       name
    from v$PROCESS, V$BGPROCESS
    where V$PROCESS.addr = V$BGPROCESS.paddr;

SQL>select round(sum(BYTES/1024/1024),2) as "Total SGA in Mb" from v$sgastat;

Total SGA in Mb
---------------


SQL>select round(sum(BYTES/1024/1024),2) as "Used SGA in Mb" from v$sgastat where NAME not like '%free%';

Used SGA in Mb
--------------

select * from v$parameter
where name like '%background_dump_%'
--Oracle free space script
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

-------------------
SELECT a.name, b.tablespace_name,
substr('Free : '||sum(b.bytes)/1024/1024,1,30) File_Size_MB
FROM dba_free_space b, v$database a
GROUP BY b.tablespace_name, a.name
UNION
SELECT a.name, b.tablespace_name,
substr('Total: '||sum(b.bytes)/1024/1024,1,30)
FROM dba_data_files b, v$database a
GROUP BY b.tablespace_name, a.name
ORDER BY 1,2,3
----------------------
rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;


CREATE VIEW SYS.FREE_SPACE AS
SELECT
TABLESPACE_NAME TABLESPACE,
FILE_ID,
COUNT(*) PIECES,
SUM(BYTES) FREE_BYTES,
SUM(BLOCKS) FREE_BLOCKS,
MAX(BYTES) LARGEST_BYTES,
MAX(BLOCKS) LARGEST_BLKS
FROM
SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
-----------------
rem tsfree.sql - Shows all free space within tablespaces.

Prompt be sure that you have run free_space.sql prior to this script

clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;

column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
COLUMN PIECES FORMAT 9,999 HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE FORMAT 999 HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE FORMAT 999 HEADING 'Percent|FREE|Contiguous';

ttitle left datevar right sql.pno -
center ' Instance Data File Storage' SKIP 1 -
center ' in ORACLE Megabytes (1048576 bytes)' -
skip skip;

BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT

select to_char(sysdate,'mm/dd/yy') today,
TABLESPACE_NAME,
PIECES,
(D.BYTES/1048576) FILE_MBYTES,
(F.FREE_BYTES/1048576) FREE_MBYTES,
((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
(F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
D.FILE_ID= F.FILE_ID AND
D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;


----------------------

Diagnostic Data	Previous Location 10g R2	ADR Location
Database Alert log File	BACKGROUND_DUMP_DEST	ADR HOME/alert /log.xml ADR HOME/trace/alert_.log
Background Process Trace Files	USER_DUMP_DEST	ADR HOME/trace
Foreground Process Trace Files	BACKGROUND_DUMP_DEST	ADR HOME/

-------------------------