Database Alert log, OUI log, ASM Alert log, CRS Log, OS Watcher log and OS Messages Location Listed at one Place
When working as Remote DBA log files plays very important role in day to day monitoring and trouble shooting. Log file checking starts from Software installation and continue through out life of database. Now a days, Database is not just a single s/w to work upon, Real Application Cluster and Automatic Storage Management has became an integral part of it.
Sometimes remote DBA also needs to look into OS level logs as well. Here, I am trying to give a central place for all database related log files like Database Alert log, OUI log, ASM Alert log, CRS Log, OS Watcher log and OS Messages Location.
Let’s start from Oracle Database Software Installation. Oracle Universal Installer (OUI) is commonly used tool for s/w installation.
- Oracle Universal Installer (OUI) log Files (Oracle 9.2.0.X.X, 10.1.0.X.X, 10.2.0.X.X and 11.2.0.X.X ): The log files are in /logs directory.
In Unix the location of central inventory (oraInventory) can be found from the oraInst.loc file, which exists in the /var/opt/oracle or /etc/ (By default).
[[email protected] etc]$ pwd
/etc
[[email protected]]$ cat oraInst.loc
Oracle Installer Location File Location
Thu Oct 21 16:12:34 PDT 2010
inst_group=oracle
inventory_loc=/etc/oraInventory
Here, the inventory_loc is ‘/etc/oraInventory’
The log files are below:
/logs/InstallActions.log
/logs/oraInstall.out
/logs/oraInstall.err
/logs/silentInstall.log (only for Silent installations)
For 10.2.0.X.X to 11.2.0.X.X only
/cfgtoollogs/oui/InstallActions.log
/cfgtoollogs/oui/oraInstall.out
/cfgtoollogs/oui/oraInstall.err
/cfgtoollogs/oui/silentInstall.log (only for Silent installations)
- Database alert log: Database Alter log are files, where every database related activities are recorded like instance start, stop, redo log switching, ORA error etc.
A DBA need to check this file for two reasons.
a. To check health of Database.
b. Check details of any error in Database.In Oracle 11g Default path for Alter log files is
$ORACLE_BASE/diag/rdbms/{DBNAME}/{sid}/trace/alert_{sid}.log
Ex: /u01/app/oracle/diag/rdbms/db01/db001/trace/alert_db001.log
In Oracle 10g Environment, use below command to find path for alter log files.
SQL> show parameter background_dump_dest
NAME TYPE VALUE
background_dump_dest string /etc/oracle/diag/rdbms/testdb/testdb/trace
- Automatic Storage Management alert log files: ASM is used for providing redundancy and balancing data and load across different disks. Any issue related to ASM below is the path for alter log files (Oracle 11g).
$ORACLE_BASE/diag/asm/+asm/+ASM{instance number}/trace/ alert_+ASM {instance number}.log
Ex: /u01/app/oracle/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log
- Clusterware CRS alert log: Real application Cluster is used for high availability and work load distribution among diff-2 servers. To check health of cluster and trouble shooting below is path for alter log file.
The following sections describe the locations of the clusterware log files in Database Software Oracle 10g Release 1.
Cluster Ready Services Log Files
Cluster Ready Services (CRS) has daemon processes that generate log information. Log files for the CRS daemon (crsd) can be found in the following directories:
/crs/init
/crs/.log
Oracle Cluster Registry Log Files
The Oracle Cluster Registry (OCR) records log information in the following location:
/srvm/log/
Cluster Synchronization Services (CSS) Log Files
You can find CSS information that the OCSSD generates in log files in the following locations:
/css/log/ocssd.log
/css/init/.log
Event Manager Log Files
Event Manager (EVM) information generated by evmd is recorded in log files in the following locations:
/evm/log/evmdaemon.log
/evm/init/.log
Oracle High Availability Log Files
The Oracle RAC high availability trace files are located in:
$ORACLE_BASE//admin/hdump
Where $ORACLE_BASE is configured and $ORACLE_HOME/racg/log when $ORACLE_BASE is not available.
In Oracle 11g Release 1 onwards logs can be found at this location.
$GRID_HOME/log/{node name}/alert{node name}.log
Ex: /u01/app/11.2.0/grid/log/db02/alertdb02.log
- Diskmon log files : Disk mon log are basically to check any issue with the disk in the system.
$GRID_HOME/log/{node name}/diskmon/diskmon.lo*
Ex: /u01/app/11.2.0/grid/log/dmorldb02/diskmon/diskmon.log
- OS Watcher output files : OS watcher logs are helpful for remote DBA when Database Server is hang or reboot automatically. Below is path for Linux/Unix env.
/opt/oracle.oswatcher/osw/archive/
To get OS watcher data of specific date :
cd /opt/oracle.oswatcher/osw/archive
find . -name ‘12.01.13‘ -print -exec zip /tmp/osw_hostname.zip {} \;
where 12- year 01- Month 13-day
- Os message logfile: OS message log file are logs related to OS related messages. Path for Linux/Unix syatem is below.
/var/log/messages
Ref:http://www.dbas-oracle.com/2013/03/Database-Aler-log-OUI-log-ASM-Alert-log-CRS-Log-OS-Watcher-log-OS-Messges-Location-at-one-Place.html
Daily Activities of Oracle Database Administrator (DBA)
Daily Work of DBA:
1: Health check of the Database.
2: Viewing the Alert log file to analyze the ORA errors if any.
3: Tablespace Utilization.
4: Rebuilding of Indexes, if bulk load of data is inserted.
5: Viewing the temporary files, through the DBA_TEMP_FILES.
6: Database Growth Comparision.
7: User Management.
8: Backing up the archive log files.
9: Monitoring Backups.
10: Monitoring the log files, backups, database space usage and the use of system resources.
11: Exports/Imports
12: User Management
13: Monitoring Tablespace Segments
14: Monitoring Production Database Performance
15: Solving the ORA errors.
16: Take a COLD/RMAN backups at night time.
Weekly Work of DBA:
1: Growth of the Database.
2: Total full backup of the database through hot backup.
3: Taking logical backups, in case of physical backups failure.
4: Taking weekly Tablespace backup.
Monthly Work of DBA:
1: Index Rebuild.
2: Tablespace Reorganization.
Quarterly Work of DBA:
1: Patching
2: Database Reorganization
*** We can also check on call mailbox for any alerts/issues like ***
>> Tablespace alert
>> Server down
>> Mount point full
>> Backups fail
>> Listener down
# Shift handover report, it contains pending request.
# Verify all instances are online.
# Alert log error checking.
# Deploying the application code to production servers.
# Schema level refreshing by using exp/imp, expdp/impdp.
# Monitoring the databases by using Cron Tab.
# Daily resolving the tickets by using Remedy ticketing tool.
# Explaining the plans for particular table to tune the databases.
# Taking backups by using RMAN on daily and weekly basis.
# Monitoring the RAC instances by using Cron Tab.
# Supporting to TAF(Transparent Application Failover).
# Check all last night backups were successful.
# Check all database archiving are done.
# Check tablespaces should not be used more than 95%.================================================================
Daily DBA Checklist
ï Ensure that previous night’s backup is complete and there are no RMAN errors in the backup logs.
ï Ensure that any exports which are part of the backup are complete and the dump files compressed.
ï Check the alert log for any ORA- errors – also for messages like ‘Checkpoint not complete etc’.
ï Ensure that the cron job for truncating, saving and renaming alert logs is working – verify the same.
ï Ensure that the archive redo log files are compressed and have been deleted. Only files for current and previous day should be present.
ï All tablespaces should be less than 95% full – run the coalesce command on all tablespaces to reduce fragmentation. Ensure that space in the TEMP tablespace is released and is 100% free at the beginning of the day.
ï Enough contiguous free space is available in all tablespaces for objects to extend if required.
ï Backup the control file to trace so that every day we have a outline of the files and their locations for each database.
ï No objects are within 5 extents of the MAXEXTENTS storage parameter.
ï All core dumps are deleted from the $CDUMP area.
ï All *.trc files are deleted from the $UDUMP area.
ï Check the machine for any disks 100% full or nearing that value. If a disk has filled up use the ‘find’ command to determine files which have been recently created/modified . Ensure that all *.dmp files are in their proper locations and large *.dmp files have been compressed.
ï Truncate the listener.log file in the ORACLE_HOME/network/log location if the listener log has
increased to a size > than 500 MB. Ensure the space is released, otherwise ‘reload’ listener.
ï Run the ‘recently created/modified objects’ report to ensure that no unauthorised object creation/modification is taking place.
ï Ensure that there are no DBMS_JOBS with the status of failed or broken. Also last refresh times of all running jobs should be current.
ï Check to ensure that no objects exist in the database with the status ‘INVALID’
Oracle DBA Check List
Hello,
Here i am going to explain you daily, weekly, monthly responsibilities of Oracle DBA
Oracle DBA Checklist
Daily Procedures
- Verify all instances are up and running: Run daily reports via Enterprise manager Probe event or through Oracle Grid
- Look for any new alert log entries: Check for any ORA ñerrors in you alert log $ORACLE_HOME/admin/bdump/alert_ORACLE_SID.log
- Verify success of database backup
- Verify success of database archiving to tape
- Verify DBSNMP is running
- Verify free space in tablespaces
- Verify rollback segment
- Identify bad growth projection
- Verify whether archivelogs are backed up successfully to your tapes
- Verify rollback segment
- Identify space bound objects
- Process to review contention for CPU, memory, netowrk or disk resources
- Copy Archived logs to standby Database and Roll Forward
- Read DBA manuals, forums, journal and so on.
- Analyze tables and indexes if needed
Nightly Procedures
- Collect volumetric data : mk_Volfact.sql, analyze_comp.sql, pop_vol.sql
Weekly Procedures
- Look for objects that break rules
a. Check for Next_Extents ,
b. Check Existing Extents
c. Check missing PK
d. Check whether all indexes are using INDEXES tablespace
e. Check the consistency of the schema objects between production and test environment - Look for security Policy Violations
- Look for SQL * Net logs for errors, issues
a. Check Client side logs
b. Check server side logs - Archvie all alert logs to history
- Attend Weekly Meetings (Project status , discussions on issues and so on)
Monthly Procedures
- Look for harmful growth rates
- Review tuning opppurtunities : Cache hit ration, latch contention etc
- Look for I/O Contention
- Review fragmentation : row chaining etc
- Project Performance into the future
- Perform tuning and maintenance
- Provide monthly reports (this depends on company to company)
- Attend Monthly meetings
Quartely Procedures
- Provide Quartely Audit Reports if needed (This depends on company to company)
This is checking of all users, roles, privileges and so on
