Re: 9i Newbie : spfile, sql-backtrack questions

In article <a562ad94.0204111001.235c1a0f_at_posting.google.com>, gulab_jaiswal_at_yahoo.com says...
>
>Hello All,
>
>Recently I upgraded a 8.1.7 32 bit db to 9.0.1 64 bit.
>Upgrade went smoothly and app is functioning fine.
>
>However I still continue to use my init.ora files.
>Where does spfile come in the picture ? I do not have it right now.
>I am misiing something . Can some help? Do i need to
>create my spfile once I upgraded the DB ? Will my init files continue to work ?
>
>Also I hear BMC datatools sql backtrack does not support spfile yet.
>Is it true ? Has anyone tried to use this product ?
>
>Any help will be appreciated.
>
>Thx in advance
>
>Gullu

You need to use the CREATE SPFILE command to create an SPFILE from an existing PFILE. The syntax is in the 9i doco. The command can be issued from the SQL prompt even if an instance is not started. If you create the SPFILE in the default location and name ($ORACLE_HOME/dbs/spfile{SID}.ora on Unix, %ORACLE_HOME%\database\spfile{SID}.ora, you can then issue the startup command and Oracle will use the SPFILE. The sequence of files for Unix (convert to NT format just mentioned if that's what you're using, though I doubt it since you're on 64 bit) looked for if you just use the command STARTUP is:

1. $ORACLE_HOME/dbs/spfile{SID}.ora
2. $ORACLE_HOME/dbs/spfile.ora
3. $ORACLE_HOME/dbs/init{SID}.ora

If you want to put the SPFILE in another location, you can (even though I'd suggest you stick to the defaults) by having the line SPFILE=your_own_name_and_location_goes_here into a PFILE.

Once you have your SPFILE, you cannot edit it directly (like with vi) but instead use the extended ALTER SYSTEM command to write to it:

SQL> ALTER SYSTEM SET <parameter_name> = <parameter_value> SCOPE=SPFILE | MEMORY | BOTH; Scope SPFILE writes only to the SPFILE, and only takes effect when you restart the database. Only needed for the few remaining static parameters (like UTL_FILE_DIR). Scope MEMORY affects the currently running instance and will be lost when the instance is shut down. Needed for temporary fixes only. Scope BOTH affects the currently running instance and is also written to the SPFILE.

Can't tell you anything about the BMC product. Maybe someone else can fill the hole here.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Thu Apr 11 2002 - 14:12:03 CDT