Oracle Configuration File

Oracle configuration file:

Oracle reads configuration parameters from either server parameter file (SPFILE) or text initialization parameter file (PFILE). When you start database instance, it tries to read SPFILE in default location. Else it searches for pfile. The search order is as under:

• spfileORAACLE_SID.ora
• spfile.ora
• initORACLE_SID.ora

The default location for spfile and pfile is OH/dbs. If ASM is present, SPFILE default location is the same disk group as the datafiles.Alter system has optional scope clause that specifies the scope of the change

• scope=spfile : The change is applied only to spfile only which impliesNo change is made to the current instance. For both dynamic and static parameters, the change is effective at the next startup and is persistent.
• SCOPE=memory The change is applied in memory only which impliesThe change is made to the current instance and is effective immediately. For dynamic parameters, the effect is immedidate , but is not persistent as the server parameter file is not updated. This specification is not allowed for static parameters
• SCOPE=both  The change is applied to both server parameter file and memory which implies The change is made to the current instance and is effective immediately. For dynamic parameters, the effect is persistent. For static parameters, this specification is not allowed.

The default is both if SPFILE is used for startup and MEMORY if pfile is used for startup.
You can use deferred keyword for dynamic parameters, indicating to defer the change for the future sessions.

Managing the Oracle Instance

Stages of database startup and shutdown:

• Startup nomount:
  •  Initialization parameter file is read
• startup open
  •  datafiles and redo log files are read
• startup mount
  •  control files are read

Oracle shutdown options:

• Shutdown normal
• no new connections are allowed

before database is shutdown, the database waits for
all connected users to disconnect recovery is not required on startup

• shutdown immediate
• no new connections are allowed, nor are new transactions allowed to be started after the statement is issuedAny uncommited transactions are rolled back Database rollbacks active transactions and disconnects all users Recovery is not required on startup
• shutdown transactional
• Prevents clients from losing work and at the same  time does not require all users to log off ( no new transactions are allowed, existing transactions are completed and users disconnected after their transactions have completed)no recovery is required
• shutdown abort

Issue this command when
You have problems starting a database instance
When you have to shut down the database instantaneously
No new connections are allowed nor are any new transactions allowed

Current SQL statements are immediately terminated
Uncomitted transactions are not rolled back
Oracle disconnects all the connected users
Recover is required on startup
shutdown timeout
If recovery is required, Oracle starts SMON (system
monitor process) to do the recovery after opening the database.