Windows Database Archiving - Spfile/Pfile

[daljitsb]

Windows 2003 Oracle 10.1.0.2

I have this database which I changed from no archive to archive mode.
The database starts and stops with service 'OracleServiceMYDB' which is currently using SPFILE

I have archive parameter added to pfile:
log_archive_dest_1 = "location=D:\oracle\product\10g\oradata\MYDB\archive"
log_archive_format=ARC%S_%R.%T

----
I need to change the 'OracleServiceMYDB' service to start and stop the database using PFILE. Also I need to keep the flexibility to be able to change the SPFILE for making dynamic Parameter changes without bringing the database down when possible.
----

This is the only db on the server.
ORACLE_SID is defined in the registry

This is how the service was created on mydb creation:
D:\oracle\product\10g\Db_1\bin\oradim.exe -new -sid MYDB -startmode manual -spfile
D:\oracle\product\10g\Db_1\bin\oradim.exe -edit -sid MYDB -startmode auto -srvcstart system

Thanks
-D

[dbasan]

----
I need to change the 'OracleServiceMYDB' service to start and stop the database using PFILE. Also I need to keep the flexibility to be able to change the SPFILE for making dynamic Parameter changes without bringing the database down when possible.
----

-D

When ever you want to start the db with pfile use :

>startup pfile ####option



When ever you are making any changes to the parameter see that you set the scope option by specifying scope=Both to take effect in spfile as well as in pfile. If you want the effect only in spfile set it to scope=spfile other wise leave it to scope=memory for that startup until shutdown.

[daljitsb]

When ever you want to start the db with pfile use :

>startup pfile ####option

Thanks for your help. This will surely help.
The option you mentioned works when I manually start the db.
But I need the windows service to 'OracleServiceMYDB' to start using the pfile. Currenlty it starts it using spfile.
-D

[davey23uk]

if you start up with a pfile though, you cant do your 'dynamic' changes.

btw spfiles dont allow any more dynamic changes that a pfile does, just stops you having to edit a pfile

[dbasan]

Thanks for your help. This will surely help.
The option you mentioned works when I manually start the db.
But I need the windows service to 'OracleServiceMYDB' to start using the pfile. Currenlty it starts it using spfile.
-D

that is not possible since the default is spfile, if you want to overcome that use manual pfile. Pfile is for DBA's who have set the database and may not make any dynamic changes.

Still if you want that way may be you have to go back to 8i R2.

[tomcat]

If you rename or remove the spfile, won't it go back to looking for a pfile? Works that way in unix at least...

[jmodic]

You don't need to remove/rename the spfile - simply tell the service which pfile/spfile to use. Either use oradim utility

oradim -edit -SID mysid -PFILE path_and_name_of_mypfile

or create/edit the ORA_SID_PFILE registry key.

But frankly, I don't understand why do you want to use pfile at all. Why don't you simply modify the offended parameters in spfile and let the database continue to use spfile?