Timestamp Format Change
There’s an interesting change in the alert.log since Oracle Database 12.2.0.1: The format of the timestamps has changed.
If you compare the timestamps in Oracle 12.1 vs Oracle 12.2 you’ll recognize the change immediately:
| Oracle 12.1 | Oracle 12.2 |
Wed Feb 08 15:39:29 2017 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Wed Feb 08 15:39:29 2017 ALTER DATABASE OPEN Wed Feb 08 15:39:29 2017 Ping without log force is disabled. Starting background process TMON |
2017-05-29T14:09:17.064493+02:00 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT 2017-05-29T14:09:17.252853+02:00 ALTER DATABASE OPEN 2017-05-29T14:09:17.266894+02:00 Ping without log force is disabled: instance mounted in exclusive mode. Endian type of dictionary set to little |
Revert to the old format?
In some cases you may wish to revert to the old display format, for instance if you extract information from the alert.log and rely on the old timestamp format. In case you’d like to change it please use the init.ora/spfile parameter UNIFORM_LOG_TIMESTAMP_FORMAT. Default setting is TRUE. Once you switch it to FALSE the timestamp in the alert.log is in pre-Oracle-12.2 format dynamically.
ALTER SYSTEM SET uniform_log_timestamp_format=FALSE SCOPE=BOTH;
Completed: ALTER DATABASE OPEN 2017-05-29T14:29:42.174973+02:00 Shared IO Pool defaulting to 64MB. Trying to get it from Buffer Cache for process 20948. =========================================================== Dumping current patch information =========================================================== Patch Id: 25862693 Patch Description: DATABASE BUNDLE PATCH: 12.2.0.1.170516 (25862693) Patch Apply Time: 2017-05-19T17:49:59+02:00 Bugs Fixed: 23026585,24336249,24385983,24923215,24929210,24942749,25036474, 25099758,25110233,25410877,25417050,25427662,25429959,25459958,25547901, 25569149,25600342,25600421,25606091,25655390,25662088,25662101,25728085, 25823754 =========================================================== Mon May 29 14:37:08 2017 ALTER SYSTEM SET uniform_log_timestamp_format=FALSE SCOPE=BOTH; Mon May 29 14:37:25 2017 Thread 1 advanced to log sequence 16 (LGWR switch) Current log# 1 seq# 16 mem# 0: /u02/oradata/CDB2/redo01.log
Oracle Clusterware, ASM, Grid Infrastructure?
Luckily Anil Nair, our RAC PM, has been blogged about the influence of this new setting on the Clusterware and ASM logs:
Further Information
- Oracle 12.2.0.1 Documentation: UNIFORM_LOG_TIMESTAMP_FOPRMAT
- Upgrade Blog: New SPFILE Parameters in Oracle Database 12.2.0.1
–Mike
PS: Thanks to Roderick for CC:ing me and for filing a doc bug 26145504 to get this added into the next revision of the Upgrade Guide as Behavior Change.
Thanks Mike, hopefully there is a way to get the ‘usual’ format 🙂
Mike,
Do you by any chance know a way to convert this date to a timestamp in Oracle
I personally like the change but our external tables on alert log for mining now starts suffering as I could not find a format converter with T in the middle between date and time. previously we were able to convert it using to_date function now it is not possible.
Coskan,
have you tried: V$diag_alert_ext
If this works for you (ORIGINATING_TIMESTAMP, NORMALIZED_TIMESTAMP) then credits must go to Connor McDonald:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1352202934074
SQL> select originating_timestamp, message_text
2 from V$diag_alert_ext
3 where originating_timestamp > cast(sysdate-3/24 as timestamp);
Cheers,
Mike