Oracle 11g RAC remote database login time and OS time difference 16 hours solution

Oracle 11g RAC remote database login time and OS time difference 16 hours solution

Help colleagues install the RAC database of Oracle 11.2.0.4 for project stress testing. The operating system is red flag server 3.

Today, my colleague suddenly called to say that the database time is 16 hours different from the OS time. The problem is that the two nodes log on locally and check that the sysdate and OS time are the same, but remotely log on to other nodes, the time difference between the database time and the OS time is 16 hours, and it is suspected that there is a problem with the database time zone.

After investigation, the database time zone is indeed not the GMT + 8, stop the application, and start to modify the database time zone.

Alter database set time_zone = '+ 8:00 ';

Restart the instance and query again. The time zone of the database has been modified. However, if you remotely log on to other nodes, the database time and OS time are still 16 hours different.

Run the following SQL statement:

Select distinct imezone from dual;

The displayed time zone is-, which is the West eight area. Here, the problem is finally known.

It should be because the correct time zone was not selected during database installation, resulting in a 16-hour gap between the database time and OS time. In retrospect, the "general purpose" was selected during the installation ", this error occurs only when you do not pay too much attention to the database Time Zone When configuring database parameters.

How can we solve this problem? Use the following command to search multiple parties:

Srvctl setenv database-d <dbname>-t TZ = EAT-8

Then restart the database instance and remotely log on to other nodes again. The database time is consistent with the OS time. At this point, the problem has been solved. We also hope that you will not make such mistakes.

The following is an excerpt from some online database Time Zone explanations for your reference.

Dates & Calendars-Frequently AskedQuestions [ID 227334.1]

Why is my SYSDATE time not the same as my system clock on Unix? Sysdate is just a system call to the OS to get the time (a "gettimeofday" call ).

Sysdate does NOT use timezones in the database (select dbtimezone, sessiontimezone from dual. But OS (unix level) TZ settings DO alter the time that the OS will pass on to Oracle.

To debug:

Telnet to the unix box and connect using sqlplus in the telnet session:

1) once trought the listener using a tnsnames alias

Select to_char (sysdate, 'dd-MON-YY HH24: MI: ss') from dual;

2) once trough a "local" ORACLE_SID connection

Select to_char (sysdate, 'dd-MON-YY HH24: MI: ss') from dual;

If the result is different then it means that the listener is started with a different TZ

Then you current user env->; stop and start listener with the TZ you want.

If you are using RAC then use 'srvctl setenv database-d <dbname>;-t TZ = <the TZ you want>; 'to define the correct TZ.

Oracle 11g RAC local time and listener connection time are different