In this third part of a five-part series on Oracle Database XE administration, you will learn about initialization parameters and how to connect to the database. This article is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
When a database instance starts, the memory for the Oracle instance is allocated and one of two types of initialization parameter files is opened: either a text-based file called init<SID>.ora(known generically asinit.oraor a PFILE), or a server parameter file (otherwise known as an SPFILE). The instance first looks for an SPFILE in the default location for the operating system (e.g.,$ORACLE_HOME/dbson Linux) as eitherspfile<SID>.oraorspfile.ora. If neither of these files exists, the instance looks for a PFILE with the nameinit<SID>.ora. Alternatively, theSTARTUPcommand can explicitly specify a PFILE to use for startup. For a default installation of Oracle Database XE, the name of the SPFILE isspfileXE.ora.
Initialization parameter files, regardless of the format, specify parameters such as file locations for trace files, control files, filled redo log files, and so forth. They also set limits on the sizes of the various structures in the SGA discussed earlier in this chapter as well as how many users can connect to the database simultaneously.
As of Oracle Database 10g, and of course Oracle Database XE, Oracle categorizes the 258 initialization parameters (in Oracle Database XE) into two broad categories: basic and advanced. There are 29 basic parameters. Most databases should only need to have these adjusted, if at all, to run efficiently. The other advanced parameters only need to be adjusted when the Oracle documentation specifically calls for the adjustment under special circumstances. Many parameters are automatically adjusted based on the settings of other parameters. For example, the parameterSGA_TARGET, which specifies the total size of the SGA, automatically sizes five other parameters that control SGA memory areas:DB_CACHE_SIZE,SHARED_POOL_SIZE,LARGE_POOL_SIZE,JAVA_POOL_SIZE, andSTREAMS_POOL_SIZE.
Until Oracle9i Database, using theinit.orafile was the only way to specify initialization parameters for the instance. Although it is easy to edit with a text editor, it has some drawbacks. If a dynamic system parameter is changed at the command line with theALTER SYSTEMcommand, the DBA must remember to change theinit.orafile so that the new parameter value will be in effect the next time the instance is restarted.
An SPFILE makes parameter management easier and more effective for the DBA. If an SPFILE is in use for the running instance, anyALTER SYSTEMcommand that changes an initialization parameter can change the initialization parameter automatically in the SPFILE, change it only for the running instance, or both. No editing of the SPFILE is necessary or even possible without corrupting the SPFILE itself. A default installation of Oracle Database XE uses an SPFILE. Oracle best practices recommend using an SPFILE and backing it up whenever you make changes to an initialization parameter.
To view the values of all of the initialization parameters using the Oracle Database XE home page, click the Administration ? About Database icons. Next, select the Parameters checkbox and click the Go button. In the rare case where you need to change an initialization parameter, you can use theALTER SYSTEMcommand using SQL*Plus or the SQL Commands page. Here is an example of querying the value of thePROCESSESsystem parameter, then increasing the number of processes and therefore the number of users that can connect to the database:
SQL> show parameter processes
NAME TYPE VALUE --------------------------- --------- ------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 job_queue_processes integer 4 log_archive_max_processes integer 2 processes integer 40 SQL> alter system set processes = 100; alter system set processes = 100 *
ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set processes=100 scope=spfile;
The firstALTER SYSTEMcommand failed because you cannot change the value forPROCESSESwhile the instance is running. Using theSCOPE=SPFILEoption, the value forPROCESSES changes in the SPFILE only and will take effect the next time the database starts.