Changing sysdate
/ systimestamp.
----------------------------------------
1. Server
currently running with SGT timezone.
Conneting to the
db using client, client OS timezone is SGT.
Scenario 1 )
[oracle@standalone2
~]$ sqlplus manzoor/manzoor@source
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Jul 3 10:56:47 2015
Copyright (c)
1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database
11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select
systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
03-JUL-15
10.56.54.251769 AM +08:00
-- Its retuned
the SGT value.
Chaning the
timezone in client side.
[oracle@standalone2
~]$ export TZ=Asia/Calcutta
[oracle@standalone2
~]$ date
Fri Jul 3
08:27:45 IST 2015
[oracle@standalone2
~]$ sqlplus manzoor/manzoor@source
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Jul 3 08:27:49 2015
Copyright (c)
1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database
11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select
systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
03-JUL-15
10.57.54.919229 AM +08:00
-- Its retunred
SGT value, which means changing the tz on the client side will not make any
effect. The
systimestamp which retunred from the database server.
Sysdate &
Systimestamp function will make a call to the OS to get the time for the local
connection,
and for the
remote connection it will retrun the timzone in which the database started.
--> Shutdown
the database.
--> set
timezone to IST
--> start the
database
[oracle@rhel11gr2rac1
admin]$ export TZ=Asia/Calcutta
[oracle@rhel11gr2rac1
admin]$ sqlplus / as sysdba
SQL> startup
ORACLE instance
started.
Total System
Global Area 521936896 bytes
Fixed Size
2229944 bytes
Variable Size
159385928 bytes
Database Buffers
356515840 bytes
Redo Buffers
3805184 bytes
Database
mounted.
Database opened.
SQL> select
systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
03-JUL-15
08.30.28.585681 AM +05:30
-- Now from the
client make a remote connection the server and select the timestamp.
[oracle@rhel11gr2rac1
~]$ sqlplus manzoor/manzoor@source
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Jul 3 11:11:24 2015
Copyright (c)
1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database
11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select
systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
03-JUL-15
08.31.03.026900 AM +05:30
-- Now the
client returns the timestamp as per the setting in which db is restarted.
- You the below
query to check in which TZ the database is started.
sql> SET
SPACE 1 LINESIZE 80 PAGES 1000
sql> select
to_char(ORIGINATING_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS TZH:TZM') from
V$DIAG_ALERT_EXT WHERE trim(COMPONENT_ID)='rdbms' and inst_id=1
and MESSAGE_TEXT like ('PMON started with%') order by
originating_timestamp;
TO_CHAR(ORIGINATING_TIMEST
--------------------------
2015/06/24
10:59:45 +08:00
2015/06/24
11:13:54 +08:00
2015/06/24
11:18:18 +08:00
2015/06/24
11:21:32 +08:00
2015/06/24
11:37:08 +08:00
2015/07/03
10:23:00 +08:00
2015/07/03
08:30:11 +05:30
7 rows selected.
-- We could see
that the pmon process started with IST timezone. Hence the remote connections for
which db is registered dynamically with the listener will return the time based
on this timezone.
-----------
a) PMON only reads the OS environment variables when the database is started.
b) If TZ is changed after the database is started starts PMON will not pick up the changed value.
c) 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.
d) This means that connections via the dynamically registered service handler will continue to use the old TZ.
e) If the listener is restarted and a also has a static service registration, connection via the listener will use the TZ set in the listeners environment until PMON first re-registers the service handler.
When the listener has two service registrations, one static and one dynamic, then the dynamic one will be used.
Listener Registration
---------------------
a) The way registration typically works, The dispatcher registration is going to look for a default listener on the default port (1521)
on the hardware IP address. If things are not in that standard format, local_listener should be used to point the database to where the listener is.
Eg.
Scenario
1) DB is running with SG timezone. (Below output from local connection)
SQL> select systimestamp from dual;
SYSTIMESTAMP
-------------------------------------
30-OCT-15 08.39.16.829936 AM +08:00
2) Listener is running with SG timezone. (Below output from remote connection)
[oracle@rhel11gr2rac1 admin]$ sqlplus sys/admin@purchase as sysdba
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
30-OCT-15 08.39.56.457772 AM +08:00
3) Now restarting the listener with differnet Timezone eg (IST), which means db will continue to run in
SG timezone but only the listener will be running in IST timezone.
[oracle@rhel11gr2rac1 admin]$ export TZ=Asia/Kolkata
[oracle@rhel11gr2rac1 admin]$ lsnrctl stop listener_seccdb
[oracle@rhel11gr2rac1 admin]$ lsnrctl start listener_seccdb
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1600)))
STATUS of the LISTENER
------------------------
Alias listener_seccdb
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 30-OCT-2015 06:12:07
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oraInventory/diag/tnslsnr/rhel11gr2rac1/listener_seccdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel11gr2rac1.manzoor.com)(PORT=1600)))
Services Summary...
Service "purchase" has 1 instance(s).
Instance "purchase", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4) DB is statically registered in the listener, note that listener is running in port 1600 which is not the default (1521) port,
hence dynamic register to the listener will not happen. It has to be done by setting up local_listener parameter to
register db to the listener, here in our case we have registered the db statically in the listener.
Recall the point
"If the listener is restarted and a also has a static service registration, connection via the listener will use the TZ set in the
listeners environment until PMON first re-registers the service handler.
When the listener has two service registrations, one static and one dynamic, then the dynamic one will be used"
LISTENER_SECCDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1600))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gr2rac1.manzoor.com)(PORT = 1600))
)
)
SID_LIST_LISTENER_SECCDB =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/oracle/product/12.1.0.2/db_1)
(SID_NAME = purchase)
)
)
So as per above remote connection now will have the timezone as IST.
[oracle@rhel11gr2rac1 admin]$ sqlplus sys/admin@purchase as sysdba
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
30-OCT-15 06.19.03.769312 AM +05:30
(As you can see remote connection is showing as +5:30 Which is IST)
A) Point to note.
a) if the listener is running with default port then again a dynamic registraion will happen and pmon register the db
db to the listener with db timzone, hence once dynamic registration is done any further remote connection will
use the db timezone.
b) systimestamp will retrun as IST during remote connection and if any insert happen to any table it will be as IST.
Eg.
[oracle@rhel11gr2rac1 trace]$ sqlplus manzoor/ahamed@purchase
SQL> select systimestamp from dual;
SYSTIMESTAMP
------------------------------------
30-OCT-15 06.24.29.160008 AM +05:30
-Remote connection is establieshed and it is showing IST timezone since listener is runnig with IST timezone.
SQL> desc emp1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
JOIN_DATE DATE
SQL> insert into emp1 values (1,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into emp1 values (1,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format = 'DD-MM-YY HH24:MI:SS';
Session altered.
SQL> select * from emp1 order by 2;
EMPNO JOIN_DATE
---------- -----------------
1 30-10-15 06:25:52
1 30-10-15 06:27:23
--Now lets make local connection and insert an row.
[oracle@rhel11gr2rac1 ~]$ sqlplus manzoor/ahamed
SQL> insert into emp1 values (2,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp1 order by 2;
EMPNO JOIN_DATE
---------- -----------------
1 30-10-15 06:25:52
1 30-10-15 06:27:23
2 30-10-15 08:58:52
-- Now the data inserted in SG timezone.
-- Now lets run backup runing remote connection.
[oracle@rhel11gr2rac1 admin]$ rman target sys/admin@purchase
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 30 06:31:38 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PURCHASE (DBID=3978345698)
--As you can see the connection established at 6:31 which is IST time.
Triggered the backup.
RMAN> backup incremental level 1 database;
Starting backup at 30-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
--
In another session connect to the db and ran the below query to check the
alter session set nls_date_format = 'DD-MM-YY HH24:MI:SS';
SQL> select start_time, end_time, input_bytes, output_bytes/1024/1024/1024 "Output GB", INPUT_TYPE, status, ELAPSED_SECONDS/60/60 "Hours"
from V$rman_backup_job_details order by 1;
START_TIME END_TIME INPUT_BYTES Output GB INPUT_TYPE STATUS Hours
----------------- ----------------- ----------- ---------- ------------- ----------------------- ----------
13-09-15 07:17:02 13-09-15 07:17:11 3670016 .003456116 DATAFILE INCR COMPLETED .0025
13-09-15 08:24:14 13-09-15 08:24:22 3670016 .003456116 DATAFILE INCR COMPLETED .002222222
13-09-15 08:25:13 13-09-15 08:25:20 3670016 .003456116 DATAFILE INCR COMPLETED .001944444
13-09-15 08:27:07 13-09-15 08:27:14 3670016 .003456116 DATAFILE INCR FAILED .001944444
13-09-15 08:29:22 13-09-15 08:29:29 3670016 .003456116 DATAFILE INCR FAILED .001944444
13-09-15 08:33:09 13-09-15 08:33:16 3670016 .003456116 DATAFILE INCR COMPLETED .001944444
06-10-15 10:35:06 06-10-15 10:42:05 3339788800 2.75684404 DB INCR FAILED .116388889
30-10-15 06:00:49 30-10-15 08:31:56 2254438400 .57598114 DB INCR COMPLETED 2.51861111
30-10-15 06:33:08 30-10-15 09:03:17 382509056 .00970459 DB INCR RUNNING 2.5025
30-10-15 08:29:47 30-10-15 08:30:13 0 0 DB INCR FAILED .007222222
-- As you can see the above backup (status RUNNING ) started at 6:33 and the endtime is taken as SG time, so the number of
hours its running shows as 2.5 hours even though it is just started 1 minute back.
Lets query once the backup is completed.
Now backup is completed.
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:27
Finished backup at 30-OCT-15
START_TIME END_TIME INPUT_BYTES Output GB INPUT_TYPE STATUS Hours
----------------- ----------------- ----------- ---------- ------------- ----------------------- ----------
11-09-15 09:19:44 11-09-15 09:26:47 4993318912 1.68067169 DB FULL FAILED .1175
13-09-15 07:17:02 13-09-15 07:17:11 3670016 .003456116 DATAFILE INCR COMPLETED .0025
13-09-15 08:24:14 13-09-15 08:24:22 3670016 .003456116 DATAFILE INCR COMPLETED .002222222
13-09-15 08:25:13 13-09-15 08:25:20 3670016 .003456116 DATAFILE INCR COMPLETED .001944444
13-09-15 08:27:07 13-09-15 08:27:14 3670016 .003456116 DATAFILE INCR FAILED .001944444
13-09-15 08:29:22 13-09-15 08:29:29 3670016 .003456116 DATAFILE INCR FAILED .001944444
13-09-15 08:33:09 13-09-15 08:33:16 3670016 .003456116 DATAFILE INCR COMPLETED .001944444
06-10-15 10:35:06 06-10-15 10:42:05 3339788800 2.75684404 DB INCR FAILED .116388889
30-10-15 06:00:49 30-10-15 08:31:56 2254438400 .57598114 DB INCR COMPLETED 2.51861111
30-10-15 06:33:08 30-10-15 09:03:37 2254438400 .015541077 DB INCR COMPLETED 2.50805556
30-10-15 08:29:47 30-10-15 08:30:13 0 0 DB INCR FAILED .007222222
Now the backup is completed and shows it took 2.5 hours to complete as per report (so dont be surprised).
To check the environment in which a process is started you can
use below. (in Linux)
Linux
a)
Get the process id.
[oracle@rhel11gr2rac1 trace]$ ps -ef | grep tns
oracle
7045 1 0 06:12 ? 00:00:00
/oracle/product/12.1.0.2/db_1/bin/tnslsnr listener_seccdb –inherit
b)
View the environment details of that process.
(environment details of each process will be stored in
/proc/[process id]/environ binary file.
[oracle@rhel11gr2rac1 trace]$ strings
/proc/7045/environ | grep TZ
TZ=Asia/Kolkata
Solaris
a)
Get the process id
-bash-3.2$ ps -ef | grep pmon
oracle
11310 1 0 Oct
29 ? 0:02 ora_pmon_source
b)
View the environment details of that process.
-bash-3.2$ pargs -e 11310 | grep TZ
envp[18]: TZ=Asia/Kuala_Lumpur
Oracle Note ids
to refer
How to Change
the "Database Time" ( SYSDATE and SYSTIMESTAMP) To An Other Time /
Timezone (Doc ID 1988586.1)
Timestamps &
time zones - Frequently Asked Questions (Doc ID 340512.1)
We can also set
listener to show up more than 1 timezone. Refer
How
To setup TNS listener to Show More Than one Timezone (Doc ID 399448.1)
