As long as the parameter file (initialization parameter file) is in the default location startup command can be executed without the ‘pfile=’ parameter.

Platform-specific default location is $ORACLE_HOME/dbs on UNIX, $ORACLE_HOME\database on NT.

Using SPFILE is advised by Oracle to startup database.

-------------------------------------------------------
Doc Id of Metalink 166601.1 says on SPFILE and PFILE:

A server parameter file (SPFILE) can be thought of as a repository for initialization parameters that is maintained on the machine where the Oracle database server executes. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This eliminates the need to manually update initialization parameters to make changes effected by ALTER SYSTEM statements persistent.

It also provides a basis for self-tuning by the Oracle database server.

1. Creation of the Spfile from a Pfile First of all you have to create a server parameter file (spfile).
2. The spfile must be created from a traditional text initialization parameter file (pfile).
3. You must have the SYSDBA or the SYSOPER system privilege to create a spfile.

The command can be executed either before or after instance startup.

CREATE SPFILE [= '<spfile_name>'] FROM PFILE [= '<pfile_name>'];

Example:
SQL> CREATE SPFILE FROM PFILE = 'initV901.ora';

Creates a spfile called spfileV901.ora in the platform-specific default location
$ORACLE_HOME/dbs on UNIX, $ORACLE_HOME\database on NT.

If you do not specify spfile_name, Oracle uses the platform-specific default server parameter filename. If spfile_name already exists on the server, this statement will overwrite it. When using a default server parameter file, you start up the database without referring to the file by name. If you do specify spfile_name, you are creating a non-default server parameter file. In this case, to start up the database, you must first create a single-line traditional parameter file that points to the server parameter file, and then name the single-line file in your STARTUP command.

If the database is using a spfile and you want to create a new one in the same location you get an

ORA-32002: cannot create SPFILE already being used by the instance

Starting Up the Database

When you issue the STARTUP command with no PFILE clause, Oracle reads the initialization parameters from a SPFILE in a platform-specific default location.

Note:
For UNIX, the platform-specific default location for the spfile or pfile is: $ORACLE_HOME/dbs For Windows NT and Windows 2000 the location is: $ORACLE_HOME\database

In the platform-specific default location, Oracle locates your initialization parameter file by examining filenames in the following order:
1. spfile$ORACLE_SID.ora
2. spfile.ora
3. init$ORACLE_SID.ora

You can also specify the SPFILE in a PFILE. This is the only way to start the instance with an SPFILE in a non default location. The entry in the PFILE would look like:

SPFILE = /database/startup/spfileora1.ora

3.Checking if Spfile is used by Startup or Pfile

The simplest way to check if you have used a spfile or pfile to start the database execute the following command in sqlplus

SQL>show parameter pfile
NAME TYPE VALUE
-------- ----------- ----------------------------------------------
spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

This shows the parameter file with which the database was started.

4.Problems on NT On NT you have for every database a dedicated service.

This services is created with oradim.exe and this program doesn't support spfile with Version 9.0.1 There are Workarounds for this problem.
1. You can remove the pfile entry ORA_<SID>_PFILE from the registry.
2. In your init.ora you can add an entry spfile=<spfile_name>.

--------------------------------------------------
Startup nomount

The instance starts, but does not mount the control file or open the database.
This status of the instance enables the following tasks
1. To create a new database or
2. A new control file or
3. To recover control file.

Start an instance

– reads initialization parameter file
When Oracle starts an instance, it reads the initialization parameter file to determine the values of initialization parameters. Then, it allocates an SGA, which is a shared area of memory used for database information, and creates background processes. At this point, no database is associated with these memory structures and processes.

Mount the database

– reads control file to identify the data files
Mounting of the database means associating the started instance with the specified database by Oracle. The instance mounts the control file for a database but does not open the database.

In clustered and massively parallel systems (MPS), Real Application Clusters enable multiple instances to mount a single database.

The instance mounts a database to associate the database with that instance. To mount the database, the instance finds the database control files and opens them. Control files are specified in the CONTROL_FILES initialization parameter in the parameter file used to start the instance. Oracle then reads the control files to get the names of the database's datafiles and redo log files.

At this point, the database is still closed and is accessible only to the database administrator. The database administrator can keep the database closed while completing specific maintenance operations. However, the database is not yet available for normal operations.

In the case of clustered database

When Oracle is to allow allows multiple instances to mount the same database concurrently, then the database administrator can use the initialization parameter CLUSTER_DATABASE to make the database available to multiple instances. The default value of the CLUSTER_DATABASE parameter is false. Versions of Oracle that do not support Real Application Clusters only allow CLUSTER_DATABASE to be false.

If CLUSTER_DATABASE is false for the first instance that mounts a database, thne only that instance can mount the database. If CLUSTER_DATABASE is set to true on the first instance, then other instances can mount the database if their CLUSTER_DATABASE parameters are set to true. The number of instances that can mount the database is subject to a predetermined maximum, which you can specify when creating the database.

In the case of standby database

The standby database is constantly in recovery mode. To maintain your standby database, you must mount it in standby mode using the ALTER DATABASE statement and apply the archived redo logs that your primary database generates.

You can open a standby database in read-only mode to use it as a temporary reporting database. You cannot open a standby database in read/write mode.

In the case of a cloned database

When you perform tablespace point-in-time recovery, you mount the clone database and recover the tablespaces to the desired time, then export metadata from the clone to the primary database and copy the datafiles from the recovered tablespaces

This mode of the database helps perform the following tasks

1. Renaming datafiles.
2. Adding, dropping, or renaming redo log files
3. Enabling and disabling redo log archiving options
4. Performing full database recovery

Open the database

When you open the database, Oracle opens the online datafiles and online redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you reopen the database.

If any of the datafiles or redo log files are not present when you attempt to open the database, then Oracle returns an error. You must perform recovery on a backup of any damaged or missing files before you can open the database.

The default for startup is to OPEN the database in user mode

Shutdown database

Oracle closes the database.
Oracle Un-mounts the database.
Then Oracle shuts down the instance.

Close Database

When you close a database, Oracle writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively. Next, Oracle closes all online datafiles and online redo log files. (Any offline datafiles of any offline tablespaces have been closed already. If you subsequently reopen the database, any tablespace that was offline and its datafiles remain offline and closed, respectively.) At this point, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed but still mounted.

Un-mount Database

After the database is closed, Oracle un-mounts the database to disassociate it from the instance. At this point, the instance remains in the memory of your computer.
After a database is un-mounted, Oracle closes the control files of the database.

Startup clauses

The startup clauses let you mount and open the database so that it is accessible to users.

MOUNT

Use the MOUNT clause to mount the database. Do not use this clause when the database is mounted.

MOUNT STANDBY DATABASE

Specify MOUNT STANDBY DATABASE to mount a physical standby database. As soon as this statement executes, the standby instance can receive archived redo logs from the primary instance and can archive the logs to the STANDBY_ARCHIVE_DEST location.

MOUNT CLONE DATABASE

Specify MOUNT CLONE DATABASE to mount the clone database.

OPEN

Use the OPEN clause to make the database available for normal use. You must mount the database before you can open it.

If you specify only OPEN, without any other keywords, the default is OPEN READ WRITE NORESETLOGS.

READ WRITE

Specify READ WRITE to open the database in read/write mode, allowing users to generate redo logs. This is the default.

RESETLOGS

Specify RESETLOGS to reset the current log sequence number to 1 and discards any redo information that was not applied during recovery, ensuring that it will never be applied. This effectively discards all changes that are in the redo log, but not in the database.

You must specify RESETLOGS to open the database after performing media recovery with an incomplete recovery using the RECOVER clause or with a backup control file. After opening the database with this clause, you should perform a complete database backup.

NORESETLOGS

Specify NORESETLOGS to retain the current state of the log sequence number and redo log files.

Restriction on Resetting Logs

You can specify RESETLOGS and NORESETLOGS only after performing incomplete media recovery or complete media recovery
with a backup control file. In any other case, Oracle uses the NORESETLOGS automatically.

MIGRATE
Use the MIGRATE clause only if you are upgrading from Oracle release 7.3.4 to the current release. This clause instructs Oracle to modify system parameters dynamically as required for the upgrade. For upgrade from releases other than 7.3.4, you can use the SQL*Plus STARTUP MIGRATE command.

READ ONLY

Specify READ ONLY to restrict users to read-only transactions, preventing them from generating redo logs. You can use this clause to make a physical standby database available for queries even while archive logs are being copied from the primary database site.

Restrictions on the OPEN

01. You cannot open a database READ ONLY if it is currently opened READ WRITE by another instance.
02. You cannot open a database READ ONLY if it requires recovery.
03. You cannot take tablespaces offline while the database is open READ ONLY. However, you can take datafiles offline and online, and you can recover offline datafiles and tablespaces while the database is open READ ONLY.

Shutdown Database

The final step in database shutdown is shutting down the instance. When you shut down an instance, the SGA is removed from memory and the background processes are terminated

The three steps to shutting down a database and its associated instance are
1. Close the database.
2. Unmount the database.
3. Shut down the instance.

There are different modes to shutdown the database.

Shutting Down with the NORMAL Option

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL option.

SHUTDOWN NORMAL

Normal database shutdown proceeds with the following conditions:

No new connections are allowed after the statement is issued.
Before the database is shut down, Oracle waits for all currently connected users to disconnect from the database. The next startup of the database will not require any instance recovery procedures.

Shutting Down with the IMMEDIATE Option

Use immediate database shutdown only in the following situations:

01. To initiate an automated and unattended backup
02. When a power shutdown is going to occur soon
03. When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off
04. To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE option

SHUTDOWN IMMEDIATE

Immediate database shutdown proceeds with the following conditions:

01. No new connections are allowed.
02. No new transactions allowed to be started, after the statement is issued.
03. Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
04. Oracle does not wait for users currently connected to the database to disconnect.
05. Oracle implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

Shutting Down with the TRANSACTIONAL Option

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL option.

SHUTDOWN TRANSACTIONAL

Transactional database shutdown proceeds with the following conditions:

01. No new connections are allowed,
02. nor are new transactions allowed to be started, after the statement is issued.
03. After all transactions have completed, any client still connected to the instance is disconnected.
04. At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

Shutting Down with the ABORT Option

You can shut down a database instantaneously by aborting the database's instance. If possible, perform this type of shutdown only in the following situations:

1. The database or one of its applications is functioning irregularly and none of the other types of shutdown works.
2. You need to shut down the database instantaneously (for example, if you know a
power shutdown is going to occur in one minute).
3. You experience problems when starting a database instance.

When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT option:

SHUTDOWN ABORT

An aborted database shutdown proceeds with the following conditions:

1. No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
2. Current client SQL statements being processed by Oracle are immediately terminated.
3. Uncommitted transactions are not rolled back.
4. Oracle does not wait for users currently connected to the database to disconnect. Oracle implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.