| Lesson 4 | Informational and task termination messages |
| Objective | Identify specific informational and task termination messages. |
Informational and Task Termination Messages
I am working as an Oracle DBA using Oracle 12c.How do I identify specific task termination messages such as 1) non-default start-up parameters and 2)Oracle background processes.
As an Oracle DBA working with Oracle Database 12c, it's critical to be able to identify specific task termination messages in your alert logs. You'll find these messages instrumental in both routine maintenance tasks and troubleshooting scenarios.
Non-Default Startup Parameters: Oracle writes out non-default initialization parameters to the alert log during instance startup. These are parameters whose values have been explicitly set and differ from the default settings. An example entry would look like:
In this example, the db_cache_size parameter was set to '500M', which is not the default value. Each time you start your instance, Oracle writes all non-default parameters to the alert log. To find these messages, you can search for the string "ALTER SYSTEM SET" in your alert log.
Non-Default Startup Parameters: Oracle writes out non-default initialization parameters to the alert log during instance startup. These are parameters whose values have been explicitly set and differ from the default settings. An example entry would look like:
SYSTEM parameter value altered (session id = 3) Tue Sep 12 16:22:52 2023 ALTER SYSTEM SET db_cache_size='500M' SCOPE=SPFILE SID='*';
In this example, the db_cache_size parameter was set to '500M', which is not the default value. Each time you start your instance, Oracle writes all non-default parameters to the alert log. To find these messages, you can search for the string "ALTER SYSTEM SET" in your alert log.
Oracle Background Processes: Oracle logs messages related to the functioning and termination of its background processes. An understanding of these processes will aid in deciphering these messages. For instance, if a background process such as DBWn, LGWR, PMON, SMON, etc., terminates unexpectedly, you'll see a message like:
Similarly, when background processes start, they log messages to the alert log. Here is an example:
You can search for these messages by looking for specific process names like "LGWR", "DBWn", "PMON", "SMON", etc., in your alert log.
By monitoring the alert log regularly and understanding the task termination messages, you can ensure the smooth operation of your Oracle 12c database and take prompt corrective actions when necessary.
Tue Sep 12 16:22:52 2023 Process LGWR died unexpectedly.
Similarly, when background processes start, they log messages to the alert log. Here is an example:
Tue Sep 12 16:22:52 2023 Starting ORACLE instance (normal)
You can search for these messages by looking for specific process names like "LGWR", "DBWn", "PMON", "SMON", etc., in your alert log.
By monitoring the alert log regularly and understanding the task termination messages, you can ensure the smooth operation of your Oracle 12c database and take prompt corrective actions when necessary.
In this lesson, you will see examples of messages that fit into the category of information and task termination messages. As you just learned, these messages report on the status of the database, but are not necessarily critical to monitor, nor do they indicate serious problems.
Startup messages
View the code below to see an example of a database startup message.
Shutdown messages
Here's an example of what you would see in a database shutdown message.Mon Jun 7 02:50:40 1999 Completed: ALTER DATABASE CLOSE NORMAL Mon Jun 7 02:50:40 1999 ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT
Redo log switches
These messages show the date and time of the redo log switch, the thread number of the new log file, and the disk location of the current redo log file.Mon Jun 7 09:52:04 1999 Thread 1 advanced to log sequence 27332 Current log# 2 seq# 27332 mem# 0: /lu4.1-2/tom_log_02.dbf Mon Jun 7 09:55:32 1999 Thread 1 advanced to log sequence 27333 Current log# 3 seq# 27333 mem# 0: /lu1.1-2/tom_log_03.dbf Mon Jun 7 09:58:39 1999 Thread 1 advanced to log sequence 27334 Current log# 1 seq# 27334 mem# 0: /lu3.1-2/tom_log_01.dbf
Failure to extend a Rollback Segment Message
This is a task termination message. In this case, the rollback segments have filled, aborting the transaction.
(This is also sometimes classified as a severe error - you'll read more about it in the next lesson.)
Snapshot too old message
This is another task termination message. This indicates that a long-running UPDATE or INSERT has exhausted the rollback segments.ORA-01555: snapshot too old: rollback segment number 3 with name "R02" too smallNow take a look at the messages to which you will pay very close attention. The messages in the next lesson all indicate severe conditions that you will need to remedy.
