Monday, December 5, 2016

Is it possible to temporarily change the system time on my server, without the Oracle instance complaining?

Yes, it's possible. Oracle doesn't care about the wall-clock time, and will keep the redo log stream intact, regardless of what time the hosting server is set to.

The whole procedure is quite simple:

1. shutdown the database and listener
sqlplus / as sysdba
shutdown immediate
exit
lsnrctl stop
2. as root, change the system time. In my example, I am setting it to 10 days into the future:
Thu Dec 15 12:27:00 CET 2016
  [root@myserver ~]# date
  Thu Dec 15 12:27:01 CET 2016


3. Disable NTP på serveren:
  service ntpd stop

4. Open the instance and the listener
sqlplus / as sysdba
startup
exit
lsnrctl start

5. Verify that the database has picked up the new system time
SQL> select startup_time from v$instance;

STARTUP_TIME
-------------------
15.12.2016 12:28:17

6. Have a look at the archived logs:
select name,sequence#, archived,applied, status, completion_time from v$archived_log;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION_TIME
------------------------------------------------------------------------- ---------- --------- ---------- --- -------------------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016 11:50:48
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016 11:50:59
Force a log switch and note the time stamp of the last log:
alter system archive log current;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION_TIME
------------------------------------------------------------------------- ---------- --------- ---------- --- -------------------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016 11:50:48
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016 11:50:59
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc  12540 YES       NO         A   15.12.2016 12:33:06 

Change back again:

7. shutdown the database and the listener
sqlplus / as sysdba
shutdown immediate
exit
lsnrctl stop
8. enable ntp to have the server syncronized with the current time:
service ntpd start
9. Open the database
sqlplus / as sysdba
startup
exit
lsnrctl start
10. Check the archived logs again. Note that the log sequence doesn't care what the registered time was at the time of the log switch:

select name,sequence#, archived,applied, status, completion_time from v$archived_log;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION
------------------------------------------------------------------------- ---------- --------- ---------- --- ----------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc  12540 YES       NO         A   15.12.2016

alter system archive log current;

select name,sequence#, archived,applied, status, completion_time from v$archived_log;

NAME                                                                      SEQUENCE# ARCHIVED  APPLIED    STA COMPLETION
------------------------------------------------------------------------- ---------- --------- ---------- --- ----------
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12538__1480935048084626_.arc  12538 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12539__1480935059227231_.arc  12539 YES       NO         A   05.12.2016
/u04/fra/mydb/archivelog/2016_12_15/o1_mf_1_12540__1481801585872720_.arc  12540 YES       NO         A   15.12.2016 
/u04/fra/mydb/archivelog/2016_12_05/o1_mf_1_12541__1480937880624137_.arc  12541 YES       NO         A   05.12.2016 

No comments:

Post a Comment