Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted February 26, 2018

Oracle 12cR2 Upgrade and the Alert Log

By David Fitzjarrell

Oracle offers a number of enhancements over version, but one of those enhancements may throw a 'monkey wrench' into any alert log monitoring that may be in effect. It's a quiet change that won't be noticed until alert log monitoring scripts no longer generate reports. In the alert log timestamp changes to a format that most scripts aren't set up to read. It's called the Uniform Log Timestamp Format; it's easily changed with a new parameter that hasn't been widely mentioned. Let's change that.

Oracle decided, in version, to make the timestamp format in the alert log a bit more, well, universal. As such it created the Uniform Log Timestamp Format to replace the traditional timestamp found in alert logs in all prior releases of the RDBMS. The 'old' timestamp format looks like this:

Sun Feb 18 11:08:53 2018

The new format looks like this:


Scripts looking for the first format can't find that after an upgrade so reports stop being generated. Nothing is really wrong except the date stamp doesn't match the old format and such scripts can't find a new starting place to continue monitoring. Along with this new format came a new parameter, uniform_log_timestamp_format, which is set to true by default. Don't despair, this can be changed dynamically.

If the database is using an spfile the change is simple and immediate:

ALTER SYSTEM SET uniform_log_timestamp_format=FALSE SCOPE=BOTH;

Voila!! The timestamp format is changed and nothing more needs to be done. If, however, the database is running with a pfile the command changes to:

ALTER SYSTEM SET uniform_log_timestamp_format=FALSE SCOPE=MEMORY;

and the additional step of modifying the pfile needs to be completed to make the change last across database shutdown/startup operations.

It should be noted that when performing an upgrade to Oracle a pfile should be generated from the existing spfile, moved into place into the new home, and edited to include the uniform_log_timestamp_format=false entry. With that in place the timestamp won't change format from the first time the database is started under the Oracle kernel. An spfile can be created from the pfile, if desired, either before the actual upgrade utility is started or after all of the upgrade activity has completed. A modified init.ora file for might look like this:


Deprecated parameters, such as utl_file_dir, have been removed from the pfile, and any new parameters can be added to maintain feature availability, such as adaptive plans.

Change is good, but it can sometimes be unexpected, like the timestamp format change in Oracle release Knowing how to restore the original format is the key to keeping legacy utilities running.

See all articles by David Fitzjarrell

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM