Diagnostic Files in Oracle

Diagnostic Files in Oracle

Introduction:

 In this blog i am going to explain Diagnostic Files in Oracle To troubleshoot the database issues /errors/events/messages/alerts for that we have diagnostic files from 11g oracle introduced centralized location with the parameter

diagnostic_dest

sql>show parameter_dest   (it will dispaly the location)

  /u01/app/oracle/diag/rdbms/prod/prod/trace

bdump - background_dump_dest

udump - user_dump_dest

cdump - core_dump_dest  

$ cd /u01/app/oracle/diag/rdbms/prod/prod/trace

$ ls-ltr *.log

  In Diag folder contains Diagnostic Files in Oracle 

  1)Alert log
  2)Trace Files

Alert log :  format alert_SID.log (SID is the System Identifier in database name/instance name

 for instance prod

 eg: alert_prod.log

This alert log contains the following content

1)parameter changes

2)startup/shutdown

3)tablespace /datafiles creation /add with time stamp

 ORA -1555 snapshot too old
 ORA -0600 internal error

q)How to read alert log file in using in unix tail command

A) $ tail -100f alert_prod.log  (will read the last 100 lines while reading the tail the message will append)

 Trace Files :
 

For Every Background process in the event of error/failure will generate a trace file with process name and unix process id(PID) in Diagnostic Files in Oracle 

 $ ls -ltr *.trc    (.trc is the extension of trace file)

 the loaction of the trace file at the event will append in alert log file

 $more prod_arc2_5809.trc
 eg: found error : ORA-12541: TNS: NO LISTENER

TASKS

• Hosekeeping alert/tracefiles
• 
  
• we maintain months for retention for alert/trace files
• 
  
• monitoring alert log file with tail command
• 
  
• monitoring table space usage and adding data files
• 
  
• verifying instance status
• 
  
• database is up and running 
• 
  
• parameter changes
• 
  

Steps:


1)Take backup using CP command in unix

2)Truncate with cat command in unix

3)Zip the backup file in backup location

a) $ cd/u01/app/oracle/diag/rdbms/Prod/prod/trace

b)$ ls -lrth *.log (find the current size of the log file)

c)$ cp alert_prod.log /orabackup/alert_prod.log200720

d)$ cat/dev/null >alert_prod.log

e)$ cd /orabackup

f)$ ls -lrt

g)$ gzip alert_prod.log200720  (the file is zipped)

e) ls -lrth (we can check the zipped files and size)


NOTE:  if alert file is removed what happens ? operation will still continue no impact on instance ,in the event the new alert log will get create

NOTE: Info on Diagnostic Files in Oracle it may be differ in your environment like production,testing,development etc




THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME AND SUBSCRIBE ME