Identify Informational and Task Termination Messages iin Oracle 11g

In Oracle 11g (version 11.2.0.4), you can identify specific task termination messages such as those related to "non-default start-up parameters" or "Oracle background processes" using the "alert log" and "trace files". Here's how to proceed:

1. Viewing the Alert Log

   The alert log is a log file that records significant database events, such as startup, shutdown, errors, and non-default parameters. To identify messages related to task termination:

   • Locate the Alert Log: The alert log is typically located in the DIAGNOSTIC_DEST directory under the path:

     DIAGNOSTIC_DEST/diag/rdbms/<db_name>/<instance_name>/trace/alert_<instance_name>.log
             

   • Search for Specific Messages:
     • Non-default Start-up Parameters: Look for messages that indicate that non-default parameters were used during startup. These messages might look like:

       Non-default initialization parameters detected
                   

     • Oracle Background Processes: Search for messages related to the starting or stopping of background processes, such as:

       Starting background process ...
       Terminating background process ...
                   

2. Using Trace Files

   When an Oracle background process fails or there are issues, detailed information is usually written to trace files.

   • Trace File Location: Similar to the alert log, trace files are stored in the trace directory:

     DIAGNOSTIC_DEST/diag/rdbms/<db_name>/<instance_name>/trace
             

   • Check for Termination Messages: Open relevant trace files and search for error messages or detailed descriptions related to the termination of tasks or background processes.
3. Using Oracle Enterprise Manager (OEM)

   If you have Oracle Enterprise Manager configured, you can view alerts and diagnostic information through the OEM interface:

   • Navigate to the Performance or Diagnostics section.
   • Check the Alert Log viewer for non-default start-up parameters and background process termination messages.
4. Querying V$ Views

   You can also use SQL queries to check for issues:

   • V$PARAMETER: To check non-default parameters:
     

     SELECT name, value, isdefault 
     FROM v$parameter 
     WHERE isdefault = 'FALSE';
     

   • V$PROCESS: To get information about Oracle background processes:
     

     SELECT spid, program 
     FROM v$process 
     WHERE program LIKE '%(J%|P%)%';
     

By reviewing the alert log and trace files and using relevant SQL queries, you can diagnose and understand the reasons behind task terminations in Oracle 11g.
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: The following output contains an example of a database startup message.

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0

Starting up ORACLE RDBMS Version: 7.3.3.5.0.
System parameters with non-default values:
  processes                     = 200
  timed_statistics              = TRUE
  shared_pool_size              = 27214400
  shared_pool_reserved_size     = 1000000
  shared_pool_reserved_min_alloc= 10000
  nls_date_format               = DD-MON-RR
  control_files                 = /u1-3-5/cust_01.ctl, /u2-3-5/cust_02.ctl, /u3-3-5/cust_03.ctl
  compatible                    = 7.3.2.4.1.0
  log_buffer                    = 262144
  log_checkpoint_interval       = 10000000
  db_files                      = 1000
  checkpoint_process            = TRUE
  rollback_segments             = r02, r03, r04, r05, r06, r07, r08, r09, r10
  sequence_cache_entries        = 30
  sequence_cache_hash_buckets   = 23
  remote_os_authent             = TRUE
  remote_login_passwordfile     = NONE
  global_names                  = TRUE
  mts_service                   = cust
  mts_servers                   = 0
  mts_max_servers               = 0
  mts_max_dispatchers           = 0
  audit_trail                   = NONE
  sort_area_size                = 30000000
  sort_area_retained_size       = 30000000
  sort_direct_writes            = AUTO
  db_name                       = cust
  open_cursors                  = 200
  optimizer_mode                = RULE
  partition_view_enabled        = TRUE
  job_queue_processes           = 2
  optimizer_percent_parallel    = 100
  parallel_min_percent          = 25
  parallel_min_servers          = 4
  parallel_max_servers          = 32
  parallel_server_idle_time     = 10
  hash_area_size                = 60000000
  db_writers                    = 4

PMON started
DBWR started
LGWR started
CKPT started
RECO started
SMON started
SNP1 started
DB01 started
DB02 started
DB03 started
DB04 started

Sun Apr 25 01:22:11 1999
alter database mount exclusive
Sun Apr 25 01:22:12 1999
Successful mount of redo thread 1.
Sun Apr 25 01:22:12 1999
Completed: alter database mount exclusive
Sun Apr 25 01:22:12 1999
alter database open

These messages show all of the non-default start-up parameters and the starting of the Oracle background processes.

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
  

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, which will be discussed 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 small

Now 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.
The "ORA-01555: snapshot too old" error is still relevant and can occur in Oracle 11g. This error is typically related to insufficient undo data retention in the undo tablespace when a long-running query tries to access data that has been overwritten.
Reasons for ORA-01555 in Oracle 11g

1. Insufficient Undo Retention: The undo data required by a query has been overwritten by newer data due to a small undo tablespace or short undo retention period.
2. High DML Activity: Heavy updates, deletes, or inserts can lead to faster overwriting of undo data, causing this error.
3. Long-Running Queries: Queries that run for an extended period might attempt to read old data that has already been overwritten.

How to Address ORA-01555 in Oracle 11g

1. Increase the Size of the Undo Tablespace:
   • Ensure the undo tablespace has sufficient space to hold all undo data required for long-running queries.
   • Example:
     

     ALTER DATABASE DATAFILE 'undo_tablespace_datafile' RESIZE 2G;
     

2. Adjust the UNDO_RETENTION Parameter:
   • Increase the UNDO_RETENTION setting to retain undo data for a longer duration. This parameter is specified in seconds.
   • Example:
     

     ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=BOTH;
     

   • Note: If using Automatic Undo Management (AUM), Oracle will try to honor the UNDO_RETENTION setting but might still overwrite data if the undo tablespace runs out of space.
3. Optimize Long-Running Queries:
   • Try to optimize queries to run faster, reducing the risk of encountering the error.
   • Break large transactions into smaller chunks if possible.
4. Use Oracle Automatic Undo Management (AUM):
   • Ensure that Automatic Undo Management is enabled and correctly configured to manage undo data effectively.
   • Check if your undo tablespace is auto-extensible:
     

     SELECT tablespace_name, file_name, autoextensible 
     FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1';
     

The "ORA-01555: snapshot too old" error is a common issue when dealing with long-running queries and heavy DML operations, even in Oracle 11g, so it is essential to monitor and manage undo data effectively. Let me know if you need further guidance on managing undo or handling this error!