Oracle 12cR2 Upgrade and the Alert Log

Oracle 12.2.0.1 offers a number of enhancements over version 12.1.0.2, 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 12.2.0.1 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 12.2.0.1, 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:


2018-02-18T11:07:08.388796-07:00

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 12.2.0.1 a pfile should be generated from the existing spfile, moved into place into the new 12.2.0.1 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 12.2.0.1 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 12.2.0.1 might look like this:


*.audit_trail='DB'
*.compatible='12.0.0'
*.control_files='/u01/oracle/system/fnerble/fnerble_1.ctl','/u01/oracle/mydata/fnerble/fnerble_2.ctl','/u01/oracle/myindex/fnerble/fnerble_3.ctl'
*.core_dump_dest='/u01/oracle/dumps/cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=8
*.db_files=1022
*.db_name='fnerble'
*.db_unique_name='fnerble'
*.db_writer_processes=2
*.diagnostic_dest='/u01/oracle/admin/12.2.0'
*.dml_locks=9000
*.log_archive_dest_1='LOCATION=/u01/oracle/archive/fnerble'
*.log_archive_dest_state_1='enable'
*.log_archive_format='fnerble%S_%T_%R.arc'
*.log_archive_max_processes=4
*.log_checkpoint_interval=10000000
*.memory_max_target=6442450944
*.memory_target=6442450944
*.nls_date_format='dd-MON-YYYY'
*.open_cursors=300
*.open_links=32
*.optimizer_dynamic_sampling=2
*.OPTIMIZER_INDEX_CACHING=0
*.OPTIMIZER_INDEX_COST_ADJ=100
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=0
*.processes=1500
*.query_rewrite_enabled='true'
*.remote_login_passwordfile='exclusive'
*.session_max_open_files=20
*.sga_max_size=4194304000
*.sga_target=0
*.standby_file_management='auto'
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=10800
*.undo_tablespace='UNDO_TS'
*.uniform_log_timestamp_format=false
*.workarea_size_policy='AUTO'

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 12.2.0.1. Knowing how to restore the original format is the key to keeping legacy utilities running.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles