How can you run two or more databases in different time zones on the same machine? To explain this tip, I have set up two databases: PROD1 that runs under time zone GMT+1 and PROD2 that runs under GMT+2. Both are on the same Solaris box.
When someone wants to run the query "select sysdate from dual" against PROD1 and wants to return the current date and time in the GMT+1 time zone, one can simply adjust the OS time zone to the desired one, which we all know. The problem comes when you want to run another database on the same machine with a different time zone: in our case, PROD2 with GMT+2.
When you run a query on Unix such as "select sysdate from dual" it will return the system current date and time, which is the same as the OS current date and time. When "select sysdate from dual" is run through SQL*Net, Oracle will use the time zone based on the setting of the UNIX environment variable "TZ" for the user who started the listener.
So, using this fact, this tip will use multiple listeners--each one started with the desired "TZ" environment variable set for the user who starts the listeners. Note: This tip only works when all the users use SQL*NET or NET8 to connect to the database. For local users (without tnsnames) set your "TZ" variable before invoking the SQL session.
Here is the demo:
- Prepare two separate listeners. For example, Listener_GMT1 and Listener_GMT2 were configured for PROD1 and PROD2. They should run on different ports.
- Start the listeners (usually using "oracle" as the login).
$export TZ=GMT+1 $date Sat Apr 27 18:51:05 GMT 2002 $lsnrctl listener_GMT1
This listener message shows the timestamp in the GMT+1 time zone.
$export TZ=GMT+2 $date Sat Apr 27 17:57:01 GMT 2002
The OS date here is one hour different. (Don't worry about the minutes; I am a little slow in typing.)
- When the client connects via tnsnames remotely and runs "select sysdate from dual", the results are:
C: >sqlplus test/test@prod_gmt1 Sqlplus > alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS'; Sqlplus > select sysdate from dual; SYSDATE ------------------- 04-27-2002 18:51:05
When connecting to PROD2:
C: >sqlplus test/test@prod_gmt2 Sqlplus > alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS'; Sqlplus > select sysdate from dual; SYSDATE ------------------- 04-27-2002 17:57:01
The above example uses "select sysdate from dual," which is used to make it easy to understand the tip. The real application of this setup comes when applications or application servers depend on sysdate ("current date and time") to use it as a timestamp for transactions, for report generation, or as a baseline for some kind of analytics-based sysdate.
Geoff H. writes: DBA's utilising this method of changing the time zone should make sure that when the listeners are started, the O/S account running LSNRCTL has set the TNS_ADMIN variable up on Unix (not sure about other O/S). The TNS_ADMIN variable tells the Oracle where to look for the listener.ora file.
Anthony F. responds to Geoff (above): "Why? I assume Geoff H.'s reasoning is that there exists two ORACLE_HOME's on the server owned by the same unix account. (Not good practice by the way.) If you're using the same ORACLE_HOME (ie, one set of executables) to run two instances, then let lsnrctl find the listener.ora file in the default location of $ORACLE_HOME/network/admin. You can have many listeners defined in a listener.ora--there's no need to confuse the install. For local users you could also set the TWO_TASK env var to an appropriate connect string/service which points to the desired listener. Therefore you do not need to set the TZ var for each client."
Geoff H. responds: It is possible to have more than one Oracle Home on a Unix server, and it is not unusual. Secondly, it is quite a valid practice and a reasonable practice to have one software owner for your Oracle software. It does not make any difference to Oracle whatsoever. If you have to perform regression testing for two different timezones on the same server against Oracle 7 and 8i for instance, you would have to have 2 Oracle Homes.
Which brings me to the next point. Yes you can have all of your listeners defined in one LISTENER.ORA file, however where your server may have multiple application development areas, if you are doing testing through Listeners set at different time zones, it is easier to manage the listeners separately. I would prefer to cause the least amount of interruption of the services to our developers. At our site we allow the developers to SUDO as the Oracle superuser and restart the listeners with the timezone offset they require. If you require separate listeners in this way and set up more than one LISTENER.ORA file, you need to ensure that the TNS_ADMIN environment variable is set to allow the user who is invoking 'lsnrctl' to locate the correct LISTENER.ORA file.
For More Information
- What do you think about this tip? E-mail the Editor at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.