Hi Experts,
Could you please advise on this scenario.
OS - SUN OS 5.10
DB - Oracle EE 10g R2
Our sysadmin has set the system date to a past date (18-Mar-2015) using time machine tool, this was a request by application team to test certain logic on back date.
Now I am seeing 2 different results:--
svr01:DB2:/opt/app/oracle/admin >date
Wed Mar 18 12:30:39 EST 2015
svr01:DB1:/opt/app/oracle/admin >sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 18 12:29:47 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,sysdate from dual,v$database;
NAME SYSDATE
--------- ---------
DB1 18-MAR-15
SQL> exit
in the above it seems to be fine , Oracle database has taken system date. then checked with a user.
svr01:DB1:/opt/app/oracle/admin >sqlplus user/pwd_usr
SQL> select host_name,instance_name from v$instance;
SQL> select name,sysdate from dual,v$database;
NAME SYSDATE
--------- ---------
DB1 18-MAR-15
so for local connections it is working fine but if I check the remote connections it shows me today's date.
svr01:DB1:/opt/app/oracle/admin >sqlplus user/pwd_usr@DB1
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 18 12:42:58 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select host_name,instance_name from v$instance;
HOST_NAME INSTANCE_NAME
---------------- ------------------------------------
svr01 DB1
SQL> sho user;
USER is "user"
SQL> select sysdate from dual;
SYSDATE
---------
24-SEP-15
I checked the tnsnames and listener services to confirm if the connection being established is proper or not. It was fine, each time I can see increment in 'established' section, if I make a new connection, of command 'listener services' output .
The application team uses SQL Developer so they are also seeing the mismatch on local and remote connections.
I checked the parameter fixed_date also at instance level.
SQL> sho parameter fixed_date;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fixed_date string
it is set to none.
so not sure what could be the issue.
alternatively if the problem did not resolve , I will be using below procedure for syncing the system time with database time.
create or replace procedure change_fixed_date(dt varchar2) as
a varchar2(30);
begin
execute immediate 'alter session set nls_date_format=''dd-mm-yyyy hh24:mi:ss''';
execute immediate 'alter system set fixed_date='''||dt||'''';
select sysdate into a from dual;
dbms_output.put_line('a=' || a);
end;
/
so that the date can be passed as a parameter to change the fixed_date.
The date was changed only for oracle user at OS level by root, could this be a reason. ?
The strange thing is whenever I restart listener on server , the immediate connection (1st connection) shows me desired output (I mean remote connection shows local's date) but once I exit the session and start a new session I can see difference again.
Kindly advise.
Regards,
Hemant