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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted February 26, 2018

WEBINAR:
On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Oracle 12cR2 Upgrade and the Alert Log

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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