>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:

  • $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/

————————-

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s