STARTUP arguments

This post enlightens the usage of options with the STARTUP command of an Oracle database. When using the PFILE argument of the STARTUP command, the init.ora parameter does not necessarily be in $ORACLE_HOME/dbs directory - but can be located anywhere else while the actual path along with the file name has to be mentioned as a value for the PFILE argument. The SPFILE.ORA can be located in only one place, $ORACLE_HOME/dbs. Irrespective of wherever the PFILE is located a "CREATE SPFILE FROM PFILE" will create the SPFILE only in $ORACLE_HOME/dbs directory.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\10.2.0\DATABASE\SPFI
                                                 LEORCL10.ORA
SQL> startup force
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             100664148 bytes
Database Buffers          155189248 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.

SQL>
SQL> startup spfile="C:\Documents and Settings\oracle_and_unix\Desktop\SPFILEORCL10.ORA"
SP2-0714: invalid combination of STARTUP options

SQL> startup force spfile="C:\Documents and Settings\oracle_and_unix\Desktop\SPFILEORCL10.ORA"
SP2-0714: invalid combination of STARTUP options

SQL> startup pfile="C:\Documents and Settings\oracle_and_unix\Desktop\initorcl10.ora"
ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> startup force pfile="C:\Documents and Settings\oracle_and_unix\Desktop\initorcl10.ora"
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             104858452 bytes
Database Buffers          150994944 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.

SQL> sho parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile="C:\Documents and Settings\oracle_and_unix\Desktop\initorcl10.ora"
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             104858452 bytes
Database Buffers          150994944 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SQL> create spfile from pfile;

File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             100664148 bytes
Database Buffers          155189248 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\10.2.0\DATABASE\SPFI
                                                 LEORCL10.ORA
SQL>