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
