Configure to run two-or-more databases in different timezones on the same machine -- part 1

Configure to run two-or-more databases in different timezones on the same machine.
Since sysdate will take from DB server timezone (OS level )

This could be achieved by configuring new listener
-- check the sysdate
alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS';
select sysdate from dual;
-- check current db time zone
SELECT DBTIMEZONE FROM DUAL;

--check the OS time zone
date +%z

1) create listnet using netca,
name is LISTENER_UTC8
port is 1525
this will add new listener to listener.ora file.

2) change tnsname.ora file ( change the port number to new listener port)

TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB)
    )
  )



3) Configure the timezone which required to be run DB ( E.x change to 08:00 timezone,assume Server is running different timezone)
Open terminal session (OS level  )
export TZ=UTC-08
lsnrctl stop LISTENER_UTC8
lsnrctl start LISTENER_UTC8

4) export TZ=UTC-08  ( same terminla which used step 3)
. oraenv TESTDB (your SID)
stop and start the database in current terminal
sqlplus / as sysdba


-- register the db for new listener using local_listener parameter

-- alter system set local_listener = 'TNS_ENTRY or fully tnsname discription text';

alter system set local_listener = 'TESTDB' scope=both;

SQL> shutdown immediate
SQL> startup



perl -e' printf "access:\t%s\nmodify:\t%s\nchange:\t%s\n", map scalar localtime $_,(stat shift)[8..10] ' oraclesid