Re: changing server time which affects oracle time

From: Brian Dick <bdick_at_cox.net>
Date: Thu, 14 Oct 2004 09:29:19 -0400
Message-ID: <1qquekth3xqxo$.1efixfplqa0ee.dlg@40tude.net>

On 13 Oct 2004 13:16:13 -0500, Galen Boyer wrote:

> On Tue, 12 Oct 2004, bdick_at_cox.net wrote:
>> On 12 Oct 2004 09:00:27 -0500, Galen Boyer wrote:
>> 
>>> On Tue, 07 Sep 2004, igor.racicC_at_freesurf.fr_no_C wrote:

>>>> yls177 wrote:

>>>>> hi, oracle transaction time comes from the server time?
>>>>> Therefore, if we changed the oracle time, then inherently,
>>>>> this will affect the oracle and transaction time, right?
>>>>> currently, we have an issue here.  Some jobs were not run
>>>>> properly and we were advised by the other party that the
>>>>> only solution is to change the server time to past times, so
>>>>> as to enable the job to run. Afterwhich, we can update the
>>>>> time to the present time.  Any advise?

>>>>
>>>> Probably Sybrand gave you good answer.
>>>> But if you insist and know what you are doing, maybe
>>>> fixed_date parameter could be of help:
>>>>
>>>>
>>>> 1* alter system set fixed_date='2001-01-01-11-00-00'
>>>> SQL> /
>>>>
>>>> System altered.
>>>>
>>>> SQL> select sysdate from dual;
>>>>
>>>> SYSDATE
>>>> ---------
>>>> 01-JAN-01
>>>>
>>>> SQL> alter system set fixed_date=none ;
>>>>
>>>> System altered.
>>>>
>>>> SQL> select sysdate from dual;
>>>>
>>>> SYSDATE
>>>> ---------
>>>> 07-SEP-04

>>> 
>>> Okay,
>>> 
>>> On my local development instance, I did this for some testing.
>>> But, when I set it back using
>>> 
>>>   alter system set fixed_date=none
>>> 
>>> and then shutdown, on restart I get the error,
>>> 
>>>   ORA-00065: initialization of FIXED_DATE failed
>>> 
>>> Does anybody know how to fix this?
>> 
>> Did you try adding scope=both?
> 
> Yes.

metalink shows this as a bug that is still under investigation. Oracle is saving the parameter as fixed_date='NONE' when it should be saving the paramenter as fixed_date=NONE without the quotes. I was able to recreate the problem and a work-around.

The official way to recover is from a backup for your spfile, you can copy it over the spfile. Assuming you are using Windows, the spfiles are in the %ORA_HOME%\database directory. If you have a backup that is a pfile, start the instance with the pfile. Then use the following command.

create spfile from pfile='your.backup.pfile.ora';

I didn't have a backup and this is my work-around.

1. Edit the spfile with a binary editor.
2. Change the fixed_date='NONE' to fixed_date=1JAN00.
3. Run command startup.
4. Run command create pfile='c:\pfile.ora' from spfile.
5. Run command shutdown.
6. Edit c:\pfile.ora with a text editor.
7. Remove the fixed_date=* line.
8. Run command startup pfile='c:\pfile.ora'.
9. Run command create spfile from pfile='c:\pfile.ora'.
10. Run command shutdown.
11. Run command startup.

Good luck! Received on Thu Oct 14 2004 - 08:29:19 CDT