Overview of the Parameter Files Flashcards
Overview of Parameter Files (2 Points)
+ To start a db instance, Oracle must read a server parameter file or a text initialization parameter file (Legacy Implementation and backup if server parameter file is not avaialble)
+ Instance and parameter file can exist even when a db itself does not exist.
Initialization Parameters (4 Points)
+ Are configuration parameters that affect the basic operation of an instance.
+ The instance reads initialization parameters from a file at startup
+ Oracle provides many initialization parameters to optimize diverse environmnets
+ Only a few parameters must be excplicitly set
Functional Groups of Initialization Parameters
+ Parameters that name entities such as files or directories
+ Parameters tga set limits for a process. db resource, or the db itself
+ Parameters that affect the capacity, such as the size of the SGA (known as variable parameters)
Variable parameters adjustments have the ability to improve db performance
Basic and Advanced Initialization Parameters
+ Tyipically 30 basic parameters need to be set to obtain reasonable performance.
+ In rare cases, modification to the advanced parameters may be required for optimal performance.
+ Oracle provides values in the starter initialization parameter file provided in the db software, or as created by the Database Configuration Assistant.
Server Parameter Files (6 Points)
+ Only Oracle Database reads and writes to the server parameter file
+ Only one server parameter file exists for a database. This file must reside on the db host
+ The server parameter file is binary and cannot be modified by a test editor
+ Initialization parameters stored in the server parameter file are persistant. Any changes ,made to the parameters while a db instance is running cna persist across instance shutdown and startup.
+ A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement and can also be created by the Database Configuration Assistant.
+ View V$SPPARAMETER can display current running parameters
Text Initialization Parameter Files (5 Points)
+ Is a text file that contains a list of initialization parameters.
+ A text initialization parameter file is text-based, not binary.
+ When starting up or shutting down a db, the text initialization parameter file must reside on the same host as the client application that connects to the db.
+ Oracle db can read but not write to the text initialization parameter file. To change values you must alter the file with a text editor.
+ Changes to the initialization parameter values by ALTER SYSTEM are only in effect for the current instance.
Modification of Initialization Parameter Values (4 Points)
+ Classifications of parameters are either static or dynamic.
+ Static parameters include DB_BLOCK_SIZE, DB_NAME, and COMPATIBLE.
+ Dynamic parameters are grouped into session-level or system-level parameters
+ The scope of a parameter file can be SCOPE=MEMORY, SCOPE=SPFILE, or SCOPE=BOTH
SPFILE save locations and naming convention
+ Standard save locations for the spfile are $ORACLE_HOME/database for Windows
$ORACLE_HOME/dbs for Linux
+ The name of the file format is: spfile[instanz].ora
SQL Command to change a value of the SPFILE
SQL statement:
alter system set parameter=wert [comment=’Kommentar’‚][scope=spfile] [sid=’sid’]
Creating a new SPFILE or init.ora File (3 Options)
\+ Create SPFILE from init.ora: create spfile [= 'sp-dateiname'] from pfile [= 'p-dateiname'] \+ Create init.ora file from SPFILE: create pfile [= 'p-dateiname'] from spfile [= 'sp-dateiname'] \+ create spfile from memory
Search paths for a parameter file
1) STARTUP pfile= …./…/
2) $ORACLE_HOME/dbs/spfile[sid].ora
3) $ORACLE_HOME/dbs/spfile.ora (This can be dangerous)
4) $ORACLE_HOME/dbs/init[sid].ora
