Startup Database

You can start up a database instance with SQL*Plus, Recovery Manager, or Enterprise Manager.

When you issue the SQL*Plus STARTUP command, the database attempts to read the initialization parameters in following order:
spfile$ORACLE_SID.ora
spfile.ora
init$ORACLE_SID.ora
From Non Default location:
STARTUP PFILE = ‘C:oracle1dbsinit.ora’;
SPFILE = ‘C:oracle1dbsorcl3spfile.ora’;
STARTUP PFILE = ‘C:oracle1dbsorcl3init.ora’;
Startup an Instance:
C:SQLPLUS /NOLOG
SQL>CONNECT username/password AS SYSDBA
SQL>STARTUP;
Start the instance, and mount and open the database. This is Normal Process.
STARTUP NOMOUNT; 
Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files.
STARTUP MOUNT;
Start the instance and mount the database, but leave it closed. This state allows for certain DBA activities, but does not allow general access to the database.
STARTUP RESTRICT;
Allowing access for database administrators only. You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.
Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:
ALTER SYSTEM DISABLE RESTRICTED SESSION;
or
ALTER SYSTEM ENABLE RESTRICTED SESSION;
STARTUP FORCE;
Force the instance to start after a startup or shutdown problem, or start the instance and have complete media recovery begin immediately.
Note: If an instance is running, STARTUP FORCE shuts it down with mode ABORT before restarting it.
STARTUP OPEN RECOVER;
If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP
Note: If you attempt to perform recovery when no recovery is required, Oracle Database issues an error message.
To check Database is Started with Pfile or Spfile:
SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type”
FROM sys.v_$parameter WHERE name = ‘spfile’;
or
Show parameter spfile; it will results null
or
Directly Query with the V$parameter view;
Shutting Down a Database:
To initiate database shutdown, use the SQL*Plus SHUTDOWN command. Control is not returned to the session that initiates a database shutdown until shutdown is complete. Users who attempt connections while a shutdown is in progress receive a message like the following:
ORA-01090: shutdown in progress – connection is not permitted
SHUTDOWN NORMAL: It will wait for all currently connected users to diconnect then shutting down the database. This process will not required any instance recovery in next startup. Normal Clause is optional. It can be performed with simply shutdown command.
SHUTDOWN IMMEDIATE: Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.
SHUTDOWN TRANSACTIONAL: After all transactions have completed, the instance is disconnected. It prevents clients from losing work, and at the same time, does not require all users to log off.
SHUTDOWN ABORT: It will terminate the current transaction. Uncommitted transactions are not rolled back in this case. The database implicitly disconnects all connected users. The next startup will require instance recovery.

–Provide listing of currently active users
SELECT   NVL (username, ‘SYS’) username, COUNT (*)
FROM v$session
GROUP BY username
ORDER BY username;