Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Adjusting data backwards slowly in HPUX10.20

kenny chia
Regular Advisor

Adjusting data backwards slowly in HPUX10.20

Hi
I have a ntp server which is running HPUX10.20, lets call it server 1.
I have other ntp clients being synchronised to server1. The ntp clients include Oracle database servers
Unfortunately, server1 is 15 minutes ahead of the official time. How do I adjust server1 time backwards safely? I understand that it is not safe to adjust an Oracle database server time backwards
Note that in HPUX10.20, the date command does not have the -a option
All Your Bases Are Belong To Us!
12 REPLIES
Yogeeraj_1
Honored Contributor

Re: Adjusting data backwards slowly in HPUX10.20

hi,

indeed it is not safe to adjust the Oracle Database time backward, especially if the gap is so big!

We did it some times back. This should be how you must proceed:

1. shutdown the database
2. wait for 15 minutes.
3. Change the time backwards (use set_parms date_time).
4. restart the server and database

hope this helps!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
kenny chia
Regular Advisor

Re: Adjusting data backwards slowly in HPUX10.20

Is there any other way other than to shutdown the clients? I have about 7+ databases running in a wafer fab plant. Shutting them down (for even 15 minutes) may not be possible
All Your Bases Are Belong To Us!
Yogeeraj_1
Honored Contributor

Re: Adjusting data backwards slowly in HPUX10.20

hi again,

You will then have wait for a time when you have less load to do.

make sure your scheduled JOBs (DBMS_JOBS) are not affected by this change...

You will have to do a proper assessment of all your applications and database and prepare this change properly!

Ask yourself questions...
e.g.
what will be the impact of the number of data entered for a particular process doubles for a 15 minutes time interval? What happens to my KPIs? etc.

if this is not a problem, you can safely do the changes.

A server restart will only ensure that all logs are in sync..

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Indira Aramandla
Honored Contributor

Re: Adjusting data backwards slowly in HPUX10.20

Hi Kenny Chia,

Strictly speaking one should bounce the database when you change the server time.
Changing the system date will not cause corruption. You could get problems with point in time recovery and flash back queries if you set the time backwards. Oracle is generally not affected by dates as it uses the SCN to record changes not the date timestamp.

Most of the time, if you have DBMS_JOBS, they are the ones really affected. They will not run as desired and will be hung. Especially when you wanted to the server time switched back. And there could be application that will be effected.

Really well designed applications, when timestamping data, should either record the timezone used or store the date/time in UCT.

If you can afford the downtime, it would probably be wise to shutdown the database, change the time, and reboot the server as Yogeeraj mentioned.

This will ensure that ALL daemons (and other stuff) are working in (and logging with) the same (new) time.


Indira A
Never give up, Keep Trying
Trond Haugen
Honored Contributor

Re: Adjusting data backwards slowly in HPUX10.20

Using the method yogeeraj suggests is a good way.
If this is a critical system (as you would rather not shut the db down for 15 min) I would strongly recommend upgrading to a supported version of HP-UX.
Also if correct time is critical you should sync time with a reliable source.

Regards,
Trond
Yogeeraj_1
Honored Contributor

Re: Adjusting data backwards slowly in HPUX10.20

hi again,

have a look at the following metalink document too:
Note: 77370.1
Subject: What are the effects of changing the system clock on an Oracle Server instance?

hope this helps too!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Gerhard Roets
Esteemed Contributor

Re: Adjusting data backwards slowly in HPUX10.20

Hi Kenny

Look at xntpd but the catch is also talk to your oracle guys ... xntpd gives you the ability to "slow" the time on your machine.

The safest way is off course the 15 minute outage method. This is not always possible especially if the date goes ours off.

Regards
Gerhard
A. Clay Stephenson
Acclaimed Contributor

Re: Adjusting data backwards slowly in HPUX10.20

If this were me, I would write a small C program using the adjtime() system call to slew the clock backwards. This is a perfectly safe method even with an Oracle database because the seconds always count forward BUT adjtime() changes the rate at which the seconds tick forward so that the system gradually corrects itself to "real" time.

Man 2 adjtime for details. This is probably safer than running the 10.20 version of ntpdate because if you write the program, you know exactly what it does.
If it ain't broke, I can fix that.
Florian Heigl (new acc)
Honored Contributor

Re: Adjusting data backwards slowly in HPUX10.20

ntpd is able to slow down the system time by microseconds, so the shift will happen very gracefully.
You should even be able to set the speed it uses to do it.
In question or doubt, crosspost to comp.protocols.time.ntp - they should be able to tell You within minutes.


otoh - can't You 'hold time'* after You finished backups? this probably would be the best moment.


regards,
florian


*I remember some women also ask for that - is oracle female?
yesterday I stood at the edge. Today I'm one step ahead.
kenny chia
Regular Advisor

Re: Adjusting data backwards slowly in HPUX10.20

I will write the following program to slow down the time on server1 by 250,000usec. It will run every 10 minutes. I will test and check the results

#include
int main()
{
struct timeval forward;
forward.tv_sec = 0;
forward.tv_usec = -250000;

if (adjtime(&forward, (struct timeval *)NULL) == -1)
perror("adjtime() failure");
}
All Your Bases Are Belong To Us!
kenny chia
Regular Advisor

Re: Adjusting data backwards slowly in HPUX10.20

Hi all, does adjtime and ntpd sets the hardware clock too? In linux, there is a system (OS) clock and hardware (BIOS) clock. Is this the case for HPUX??
All Your Bases Are Belong To Us!
Yogeeraj_1
Honored Contributor

Re: Adjusting data backwards slowly in HPUX10.20

hi,

have a look at the following 2 threads:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=534810
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=77667


hope this helps!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)