Database Alert.log or ASM Alertlog File Showing Wrong Date or Wrong Datetime or Wrong time (Doc ID 1557371.1)

APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.1 and later Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Information in this document applies to any platform. GOAL To solve the issue of wrong datetime or wrong time in the Database Alert.log or ASM Alertlog file showing on RAC/Grid/Standalone environment May not have any issue with sysdate systimestamp. Most of the time, this issue is seen after recent server reboot SOLUTION  The time used for the message in the alertlog is the same as "sysdate" for the process that is writing to the alert.log. Alert.log messages are not written by "Oracle" they are written by the Oracle process that is responsible for that error/message (including user sessions, ASM processes, or any other background processes). This has nothing to do with the database timezone, session time etc. So one has to check the OS level TZ settings and Grid/Cluster level Timezone settings, if those are proper, because in some situations incorrect timezone on RAC and Grid may also causes the wrong datetime in alert.log Possible Action plan is to go through the entire Note 227334.1 and Note 340512.1 check the timezone and date settings are proper. Please refer the section "My Alert.log has wrong time for some or all entry's" in the Note 227334.1 present in the References Section Please refer the section "How do I see & set the TZ environment variable in a RAC environment" in the Note 227334.1 Please refer the section "How to see and define the timezone when using Grid Infrastructure (start the database using crsctl )?" in the Note 227334.1 Please refer the section "How to define the timezone for ASM logging (including ASM messages in the alert.log of the database)?" in the Note 227334.1 Sometimes this issue can happen with the listener and database time may not match, as explained in the Note Note 227334.1 in section "My Alert.log has wrong time for some or all entry's", In that case you can follow the below action plan shutdown listener shutdown database set ORACLE_HOME and ORACLE_SID properly check and set the OS level TZ settings then start the database then start the listener Please go through the entire Note 227334.1 and Note 340512.1 check the timezone and date settings are proper. If you are in the RAC/CRS/GRID Infrastructure, Please also follow the Notes below Note 1390015.1   Incorrect SYSDATE shown when connected via Listener in RAC Note 1209444.1   How To Change Timezone for 11gR2 Grid Infrastructure

Incorrect SYSDATE shown when connected via Listener in RAC (Doc ID 1390015.1)

To Bottom

---

APPLIES TO: Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Information in this document applies to any platform. Affected Database: Oracle SYMPTOMS Incorrect SYSDATE shown when connected via listener   Restart of database and listener did not help    OS defined TZ timezone setting is correct, but still not picked up by listener CAUSE - Listener might have a different Envs variable setting for Timezone - Listener is started by LSNRCTL instead of SRVCTL or vice versa - PMON only reads the OS environment variables when the database is started. If TZ is changed after the database is started PMON will not pick up the changed value. When dynamic database service registration is enabled to a listener, PMON both registers the service name and also the environment variables for the database associated with that service name. This means that connections via the dynamically registered service handler will continue to use the old TZ timezone setting. If the listener is restarted and also has a static service registration, connection via the listener will use the TZ set in the listener until PMON first re-registers the service handler again. When the listener has two service registrations, one static and one dynamic, then the dynamic one will be used. SOLUTION For 11.2.0.2 and above the TZ entry in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt sets to correct time zone! This enables resources started under the Grid Infrastructure home (listener, databases) using the timezone set in here and so has no requirement anymore to set TZ for DB and listener via setenv. For more details see: How To Change Timezone for 11gR2 Grid Infrastructure ( Document 1209444.1) 1) Check the environment variables set for database and listener in your CRS configuration srvctl getenv database -d <dbname> srvctl getenv nodeapps -n <nodename> -- If you are 11.2, then use 'getenv listener' instead of 'getenv nodeapps': srvctl getenv listener 2) Check the current environment variables used for PMON and Listener Refer: How to Check the Environment Variables for an Oracle Process Document 373303.1 3) Compare and if required set the TZ setting with the above Note: On HP-UX, if TZ is not present, then the value (default: EST5EDT) contained in /etc/default/tz is used. Also see Document 1209444.1 to check timezone current settings on the OS. 4) If required set the correct TZ value for the database via SRVCTL as follows srvctl setenv database -d <dbname> -t 'TZ=<the TZ you want>' srvctl setenv nodeapps -n crmnode1 -t 'TZ=<the TZ you want>' -- If you are 11.2, then use 'setenv listener' instead of 'setenv nodeapps': srvctl setenv listener -l <listenername> -t 'TZ=<the TZ you want>' 5) Restart the CRS 6) Start the database instance and listener via SRVCTL. Restarting the database instances will lead to re-registration of the services and the environment variables to the listener. REFERENCES NOTE:1531653.1 - How to use multiple timezones with one Oracle RAC database NOTE:227334.1 - Dates & Calendars - Frequently Asked Questions NOTE:373303.1 - How to Check the Environment Variables for an Oracle Process NOTE:1209444.1 - How To Change Timezone for Grid Infrastructure BUG:18967747 - DATABASE TIMEZONE IS 16 HOURS AHEAD OF OS TIME WHEN CONNECTED VIA LISTENER