A High Level Comparison Between Oracle and SQL Server – Part IV
SQL Server maintains a running log of its operations. This log includes information about the start-up and recovery events, user actions, backups, configuration changes, invalid login attempts, errors, warnings etc. Every time the SQL service starts up, a new log file is created. This log file is known as the SQL Server Error Log.
The error log is a prime source of information for DBAs troubleshooting incidents. By default SQL Server keeps the last six log files in a rolling fashion with the oldest one being deleted first. This default behaviour can be changed and SQL can be configured to keep a pre-defined number of files. The current error log file is named ERRORLOG (without any extension) while the one before it is named ERRORLOG.1, the one before that is ERRORLOG.2 and so on.
Oracle’s equivalent of SQL Server Error Log is the Alert Log file. Alert Log contains information about database start-up and shutdowns, instance recovery, configuration changes, internal database errors, initialisation parameter values etc.
Unlike the SQL error log, the Oracle alert log does not create a new file every time the instance is restarted. In fact Oracle maintains only one Alert Log file which can grow quite big as information is accumulated over time. An Alert Log will always have the name of alert_<instance>.log where <instance> the Oracle instance name. Like SQL Server DBAs, Oracle DBAs also use the Alert Log for troubleshooting purposes.
Both SQL Server error log and the Oracle alert log are plain ASCII text files that can be opened in any text editor. SQL Server Management Studio allows viewing the error log from a Windows interface and Oracle’s Enterprise Manager Database Control also enables viewing the alert log from a web based interface.
The location of the SQL Server error log is dictated by a registry entry. The default location is usually under the LOG directory in the SQL Server installation folder. For Oracle, the location of the alert log is determined by an initialisation parameter called BACKGROUND_DUMP_DEST and it is usually the bdump folder under the $ORACLE_HOME directory.