Introduction: Oracle Startup, Parameter Files and Oracle Cache Management

Introduction to Oracle Startup, Parameter File and Oracle Cache Management: System Global Area (SGA) and Program Global Area (PGA)

A) Oracle startup phrases:
Offline: Oracle instance processes and buffer do not exist
Nomount: Read parameter file, start processes, allocate memory buffer
Mount: Open control files
Open: Open all data files and online redo log files

B) Oracle initialization parameter file
- When Oracle database is started, one of the first things is to read the database initialization parameter file that contains values from either a PFILE (init<SID>.ora) or SPFILE. The parameter values that defines the overall instance configuration such as how much memory to allocate, the file locations of datafiles, optimization parameters and etc.
- Oracle instance is started by searching the default location for a parameter file with one of the following names (in this order):

No	Parameter file	Description
1	spfile<DBSID>.ora	SPFILE for the instance - cannot edit the binary file directly Edit through: alter system set <parameter>=<value> scope=spfile
2	spfile.ora
3	init<DBSID>.ora	PFILE for the instance name SID - can edit it using text editor
4	init.ora	Sample PFILE

The search is finished as soon as one of the files is found, and the instance is started using that profile, which is normally an SPFILE. If no SPFILE exists in the default directory, the instance is started with the standard initialization file.
- Profile located in $ORACLE_HOME\dbs (Linux/Unix) and $ORACLE_HOME\database (Windows)

- Backup of the PFILE and SPFILE (Recommended to sync the PFILE=SPFILE to allow parameters change with text editor):
   - create pfile=/path/to/backup.ora from spfile
   - create spfile from pfile=/path/to/backup.ora
- Shut down Oracle database to replace the spfile. As the file is in use the entire time the database is running and should never overwrite it during normal operations

C) Oracle Cache Management

1) System Global Area (SGA)
- Shared memory area that can be called by all Oracle process, contains buffer pool, shared pool, Java pool, large pool, stream pool and Redo buffer
- parameters can be change dynamically without system restart (SGA_MAX_SIZE, DB_CACHE_SIZE)

2) Program Global Area (PGA)
Process local memory which is assigned to exactly one process (shadow process)


D) Example:

1. Determine profile used (PFILE or SFILE)
In SQLPlus execute "show parameter spfile" if SPFILE been used the value will shown the path of the parameter file. If init<SID>.ora been used, the value will be null.

2. Determine Dynamic SGA activated
- DB_CACHE_SIZE not equal to 0
- DB_BLOCK_BUFFERS: obsolete and cannot use simultaneously with SGA_MAX_SIZE or
   DB_CACHE_SIZE

3. Determine automatic PGA activated