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.
