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

Home



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


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):

Parameter file Description
1 spfile<SID>.ora SPFILE for the instance - cannot edit the binary file directly
        (Edit through: alter system set <parameter>=<value> scope=spfile)

2 init<DSID>.ora PFILE for the instance name SID - can edit it using text editor



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):
 
                                    -  SQL> create pfile from spfile;
   
                                    - SQL> create spfile from pfile;


The SCOPE parameter has three values MEMORY, SPFILE and BOTH. Let’s look at an example of each:

Alter system set db_2k_cache_size=100m SCOPE=SPFILE;

If you want to make a change to a parameter in the spfile without affecting the current instance, you can do so using the SCOPE=SPFILE option of the ALTER SYSTEM statement. This is useful when you want to make a change starting from the next startup and not for the current instance.

Alter system set db_2k_cache_size=100m SCOPE=MEMORY;

In the example above, the SCOPE=MEMORY tells Oracle to make the change for the life of the instance, and to change it back to the default value the next time the database is bounced.

Alter system set db_2k_cache_size=100m SCOPE=BOTH;

When you specify SCOPE=BOTH, the change will be made immediately, and Oracle will also make the change permanent, even after the database is bounced.

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

shared_pool_size                     big integer 216M

SQL> alter system set SHARED_POOL_SIZE = 400M scope=spfile;

then stop database i.e.

sql > shutdown immediate

and then start database instance

sql> startup

Then type the command at sql prompt as follows

sql> show parameter shared_pool_size

The output should show 400M

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

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)

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

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

Determine automatic PGA activated

Useful Queries

select status from v$instance;

select STARTUP_TIME from v$instance;

Oracle SQL query to know Oracle version

select value from v$system_parameter where name = 'compatible';

Oracle SQL query to know the path and name of spfile

select value from v$system_parameter where name = 'spfile';

Oracle SQL query to know the localization and number of control files

select value from v$system_parameter where name = 'control_files';

Oracle SQL query to show the database name.

select value from v$system_parameter where name = 'db_name';