Re: changing server time which affects oracle time

Brian Dick wrote:
>
> 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!

A nicer workaround that I found recently but can't remember who's to credit:

1. create a new pfile e.g. temp.ora with the following content: spfile=<path to your spfile>/spfile<SID>.ora *.NLS_DATE_FORMAT='DD.MM.YYYY' *.FIXED_DATE='01.06.2004'
2. startup nomount
3. alter system reset FIXED_DATE scope=spfile sid='*'
4. shutdown
5. startup

Benefits: No fiddling in the spfile, and a nice demonstration on how to use the alter system reset command correctly (the documentation isn't very clear on the subject that you *need* the sid part for reset).

The mechanics behind this is, that you can have the same parameter appear several times in your spfile/pfile, but only the *last* one is actually evaluated. So you can start with a temporary pfile that includes a spfile and thus repair your spfile.

The more I think about it, the more I believe I read this in Tom Kytes last book, but I'm not sure. Maybe someone has it has his/her desk and can clarify?

Regards,

Holger Received on Thu Oct 14 2004 - 09:04:16 CDT