5 Managing the Oracle Instance

About Initialization Parameters

Managing an Oracle instance includes configuring parameters that affect the basic operation of the Oracle instance. These parameters are called initialization parameters. The Oracle instance reads initialization parameters from a file at startup.

During installation, when you select a preconfigured database workload available in DBCA, the initialization parameters are optimized for typical use in the environment that you specified. As the number of database users increases and the workload increases, you might have to alter some initialization parameters. You can make these changes using the Initialization Parameter page in Database Control, or by using an advisor provided by Oracle Database, such as the Memory Advisor. See "Optimizing Memory Usage with the Memory Advisors" for more information.

After being read from a file, initialization parameters are retained in memory, where the values for many of them can be changed dynamically. There are two types of parameter files. The type of file used to start the instance determines if dynamic initialization parameter changes persist across database shutdown and startup. The parameter file types are:

• Server parameter file

  The server parameter file is the preferred form of initialization parameter file, and is a binary file that can be written to and read by the database. It must not be edited manually. It is stored on the host computer on which Oracle Database is running. Changes are made when you use Database Control to modify one or more initialization parameters, or when Oracle Database itself makes changes for self-tuning purposes. Any changes to it persist across database shutdown and startup operations.

  Note:

  When changing an initialization parameter in the server parameter file, you can also specify that the in-memory value be changed, so that your change is reflected immediately in the current instance. If you do not change the in-memory value, then the change does not take effect until you shut down and restart the database.

• Text initialization parameter file

  A text initialization parameter file is a text file that can be read by the Oracle instance, but it is not written to by the instance. You can change a text initialization parameter file with a text editor, but changes do not take effect until you restart the Oracle instance. When you start the instance with this type of file, you can still change many initialization parameters dynamically with Database Control, but only for the current instance. Unless you also edit the text initialization parameter file and make the same change, the change is lost when you restart the database instance.

You can use SQL statements to create the following:

• A server parameter file from a text initialization file

• A server parameter file from the current (in-memory) values of all initialization parameters

• A text initialization parameter file from a server parameter file

When you create the database with DBCA, a server parameter file is created. This file is then used each time the database is started.

About Background Processes

The background processes of the Oracle instance manage memory structures, asynchronously perform I/O to write data to a file on a disk, and perform general maintenance tasks. The background processes consolidate functions that would otherwise be handled by multiple Oracle Database programs running for each user process. They monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.

The background processes that are present depend on the features that are being used in the database. Some fundamental background processes are described in Table 5-1.

About Server and Client Processes

In addition to background processes, Oracle Database creates server processes that handle the connection requests of user or client processes. A user connection is composed of the following distinct pieces:

• A client program acting on behalf of the user, such as Oracle Enterprise Manager (Enterprise Manager), SQL*Plus, or an application

• A server process that handles the connection to the database on behalf of the client program, and that performs much of the work for the client program, such as parsing and running SQL statements, and retrieving and returning results to the client program

Server processes can be either dedicated or shared. When server processes are dedicated, Oracle Database is running in dedicated server mode. When server processes are shared, Oracle Database is running in shared server mode. In dedicated server mode, each client process has its own server process. Although a dedicated server process is good for long-running queries and administrative tasks, an idle process or too many dedicated processes can result in an inefficient use of resources.

Using shared server mode eliminates the need for a dedicated server process for each user connection, requires less memory for each user connection, and enables more users to access the database. Shared server mode is more efficient at supporting multiple client programs making frequent short-running queries.

About Instance Memory Structure

The sizes of the instance memory structures affect database performance and are controlled by initialization parameters.

Upon installation, you can let the database manage memory for you automatically, or you can choose to manually configure the instance memory structures. If you choose manual memory management, then Oracle Database provides advisors to help you determine appropriate values for memory parameters. For both manual and automatic memory management, Oracle Database sends alerts that identify memory sizing problems that require your attention.

The following sections describe the two important memory areas that you can monitor and size:

• System Global Area

• Program Global Area

About Instance Startup and Shutdown

After installation, the Oracle instance is started, and the database is open for access by users with database accounts. At some point, you may want to shut down and restart the instance. This section describes the startup and shutdown processes.

The phrases "starting up and shutting down the Oracle instance" are often used interchangeably with "starting up and shutting down the database."

This section contains the following topics:

• About Administration Privileges for Startup and Shutdown

• About Instance Startup

• About Instance Shutdown

Shutting Down and Starting Up Using Oracle Enterprise Manager Database Control

You can shut down and start the Oracle instance from the Database Home page of Oracle Enterprise Manager Database Control (Database Control).

To shut down and start the Oracle instance using Database Control:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. In the General section of the Database Home page, click Shutdown.

   
   Description of the illustration hp-shutdown-r2.gif
   
   

   The Startup/Shutdown Credentials page appears.

   
   Description of the illustration shutdown_credentials.gif
   
   

3. Enter credentials as follows:

   1. Enter the host computer credentials of the user who installed Oracle Database, or of any user who is authorized to use SQL*Plus.

   2. Enter the database credentials consisting of the user name SYS and the password that you assigned to SYS during the installation.

   3. In the Connect As list, choose the value SYSOPER.

4. (Optional) Select the Save as Preferred Credential option if you want these credentials to be automatically filled in for you the next time that this page appears.

5. Click OK.

   A confirmation page appears.

6. Click Yes to start the SHUTDOWN IMMEDIATE operation on the database.

   The Startup/Shutdown: Activity Information page appears, informing you that the database is being shut down.

7. After a short period (approximately 2 minutes), click Refresh to be returned to the Database Home page.

   The Database Home page indicates that the database instance status is Down.

   
   Description of the illustration database_down.gif
   
   

8. Click Startup to start the database.

   The Startup/Shutdown Credential page appears again.

9. Enter the credentials, and then click OK.

   A confirmation page appears.

10. Click Yes.

    The Startup page appears, indicating that the database is being started up. When startup is complete, the Login page appears.

11. Log in to the database (and to Database Control).

    The Database Home page appears indicating that the database instance status is Up.

Shutting Down and Starting Up Using the Windows Services Program

On Microsoft Windows, you can also start and shut down your Oracle database using the Services program in Control Panel. You must start or stop the following services:

• OracleServiceSID, which is your Oracle instance.

• OracleORACLE_HOMETNSListener, which is your listener. The listener is required for clients to connect to your database.

• OracleDBConsoleSID, which enables clients to connect to Database Control.

In the preceding service names, SID refers to the system identifier for the instance and ORACLE_HOME refers to the Oracle home name.

To start or stop Oracle Database services:

1. Do one of the following:

   • On Windows XP and Windows Server 2003, click Start, and then select Control Panel.

   • On Windows 2000 Server, click Start, select Settings, and then select Control Panel.

   The Control Panel window opens.

2. Double-click the Administrative Tools icon, and then double-click the Services icon.

   The Services window opens, displaying all Windows services that are available on your system.

3. Locate the Oracle Database services listed at the beginning of this section. For example, if your SID is orcl, then locate the following services:

   • OracleServiceORCL

   • OracleOraDb11g_home1TNSListener

   • OracleDBConsoleorcl

4. Start or stop all three services, using the following steps for each service:

   1. Select the service name.

   2. In the Action menu, click Start or Stop.

About Memory Management

Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. The memory that must be managed is the System Global Area (SGA) memory and the instance Program Global Area (PGA) memory. The instance PGA memory is the collection of memory allocations for all individual PGAs.

Beginning with Oracle Database 11g release 1 (11.1), you can let the database manage the SGA memory and instance PGA memory completely. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. In this memory management mode, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

To have more direct control over the sizes of the SGA and instance PGA, use the Memory Advisors page of Database Control to disable automatic memory management and enable automatic shared memory management.

• With automatic shared memory management, you set target and maximum sizes for the SGA. Oracle Database then tunes the total size of the SGA to your designated target, and dynamically tunes the sizes of all SGA components.

• When you enable automatic shared memory management, you also implicitly enable automatic PGA memory management. With automatic PGA memory management, you set a target size for the instance PGA. The database then tunes the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs.

If you want complete control of individual SGA component sizes, then use the Memory Advisors page of Database Control to disable both automatic memory management and automatic shared memory management. This is called manual shared memory management. In this mode, you set the sizes of several individual SGA components, thereby determining the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.

Manual shared memory management mode is intended for experienced DBAs only. Note that in this mode, automatic PGA memory management remains enabled.

Table 5-3 summarizes the various memory management modes that you can set using the Memory Advisors page of Database Control.

If you choose the basic installation option when you install the database, then automatic memory management is enabled. If you choose advanced installation, then DBCA enables you to select from the three memory management modes. Oracle recommends that you enable automatic memory management.

Whichever memory management mode you choose, you may have occasion to adjust memory settings as demands on the database or on its host computer change. Reasons why you adjust memory settings include the following:

• You receive a memory-related alert or error message.

• You receive a memory-related recommendation from Automatic Database Diagnostic Monitor (ADDM).

• You want to change the amount of memory allocated to accommodate future growth in memory demand.

You can use a memory advisor to help you adjust memory sizes. See "Modifying Memory Settings – Automatic Shared Memory Management" for an example of using a memory advisor.

Enabling Automatic Memory Management

If you did not enable automatic memory management when you installed and configured your database, then Oracle recommends that you do so after installation, unless you are an experienced DBA with specific reasons to manually tune memory sizes. With automatic memory management, the Oracle instance dynamically tunes all memory components to optimize performance as the workload changes.

To enable automatic memory management:

1. Go to the Database Home page, logging in as user SYS.

   See "Accessing the Database Home Page".

   Note:

   You must log in as user SYS to change the maximum memory size, which is a static initialization parameter.

2. At the top of the page, click Server.

   The Server subpage appears.

3. In the Database Configuration section, click Memory Advisors.

   The Memory Advisors page appears with the SGA subtab (in the middle of the page) displayed by default.

4. If the Maximum SGA Size field contains a positive number that is greater than or equal to the desired total amount of memory to allocate to the database, then skip to Step 10.

5. In the Maximum SGA Size (MB) field, enter the maximum permissible size for database memory.

   Use the sum of the current sizes of the SGA and instance PGA as a guideline, and optionally add some room for growth.

6. Click Apply.

   A confirmation page appears, indicating that you must restart the database.

7. Click Yes and follow the guided instructions to restart the database.

8. After you have initiated the database restart, wait a few minutes and then click Refresh.

   The Database Home page appears.

9. Return to the Memory Advisors page.

   You might need to log out and log back in to Enterprise Manager to get this page to display correctly after the database restart.

10. Next to Automatic Memory Management Disabled, click Enable.

    The Enable Automatic Memory Management page appears.

11. In the field titled Total Memory Size for Automatic Memory Management, enter the desired amount of memory to allocate to the database, and then click OK.

    A confirmation message appears.

Modifying Memory Settings – Automatic Memory Management

Modifying memory settings for automatic memory management involves using the Memory Advisors page of Database Control to change the total memory size setting. The procedure for changing this setting differs depending on whether your desired new setting exceeds the current setting for maximum memory size. If the new setting exceeds the maximum memory size setting, then you must first increase the maximum memory size accordingly.

To modify total memory size (new value does not exceed maximum memory size):

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server.

3. In the Database Configuration section, click Memory Advisors.

   The Memory Parameters page appears.

   
   Description of the illustration mem_param_auto.gif
   
   

4. Do one of the following:

   • In the Total Memory Size field, enter a new value that is less than or equal to the value in the current Maximum Memory Size field.

   • Click Advice to use the Memory Advisor to help you choose a new size.

     The Memory Size Advice page appears.

     
     Description of the illustration memory_size_advice.gif
     
     

5. If you chose to use the Memory Advisor, then click a point on the curve to change the total memory size. Otherwise, go to Step 7.

   The new total memory size is displayed below the graph.

6. Continue clicking the curve until the desired value is displayed, then click OK.

   You are returned to the Memory Advisors page.

7. Click Apply.

   A confirmation message appears.

To modify total memory size (new value exceeds maximum memory size):

1. Go to the Database Home page, and log in as user SYS with the SYSDBA privilege.

   See "Accessing the Database Home Page".

   Note:

   You must log in as user SYS to change the maximum memory size, which is a static initialization parameter.

2. At the top of the page, click Server.

3. In the Database Configuration section, click Memory Advisors.

   The Memory Advisors page appears.

4. In the Maximum Memory Size (MB) field, enter a new value for the maximum total memory to allocate to the database.

   This value must be greater than or equal to the desired new value for total memory size.

5. Click Apply.

   A confirmation page appears, indicating that you must restart the database.

6. Click Yes and follow the guided instructions to restart the database.

7. After you have initiated the database restart, wait a few minutes, then click Refresh.

   The Database Home page appears.

8. Return to the Memory Advisors page.

9. Do one of the following:

   • In the Total Memory Size field, enter a new value that is less than or equal to the value in the current Maximum Memory Size field.

   • Click Advice to use the Memory Advisor to help you choose a new size.

     The Memory Size Advice page appears.

10. If you chose to use the Memory Advisor, then click a point on the curve to change the total memory size. Otherwise, go to Step 12.

    The new size is displayed below the graph.

11. Continue clicking the curve until the desired value is displayed, then click OK.

    You are returned to the Memory Advisors page.

12. Click Apply.

    A confirmation message appears.

Modifying Memory Settings – Automatic Shared Memory Management

Modifying memory settings for automatic shared memory management involves using the Memory Advisors page of Database Control to change the total SGA size. This section assumes that automatic memory management is disabled, that automatic shared memory management is enabled, and that the desired new value for the total SGA size does not exceed the current value for maximum SGA size.

To modify the total SGA memory size:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Database Configuration section, click Memory Advisors.

   The Memory Advisors page appears. Scroll down to the SGA subpage.

   
   Description of the illustration mem_current_allocation.gif
   
   

4. In the Current Allocation section, do one of the following:

   • In the Total SGA Size (MB) field, enter a new total size for the SGA.

   • Click Advice to use the SGA Advisor to help you choose a new SGA size.

     The SGA Size Advice window opens.

     
     Description of the illustration mem_allocation_advice.gif
     
     

     Click a point on the curve to change the total SGA size. The new SGA size is displayed below the graph. Continue clicking the curve until the desired value is displayed, and then click OK.

     This example graph shows that at the current time, increasing the total SGA size is not expected to provide any gain in performance.

     Note:

     The settings on this page can be modified only if Automatic Memory Management is disabled.

5. If you chose to use the SGA Size Advisor, then click a point on the curve to change the total memory size. Otherwise, go to Step 7.

   The new size is displayed below the graph.

6. Continue clicking the curve until the desired value is displayed, then click OK.

   You are returned to the Memory Advisors page.

7. Click Apply.

   A confirmation message appears.

Modifying Memory Settings – Automatic PGA Memory Management

Modifying memory settings for automatic PGA memory management involves using the Memory Advisors page of Database Control to modify the instance PGA size. This section assumes that automatic memory management is disabled, and that automatic PGA memory management is enabled.

To modify the instance PGA size:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. At the top of the page, click Server to view the Server subpage.

3. In the Database Configuration section, click Memory Advisors.

   The Memory Advisors page appears, with the SGA subpage shown.

4. Click PGA to view the PGA subpage.

   
   Description of the illustration mem_param_pga.gif
   
   

5. Do one of the following:

   • In the Aggregate PGA Target field, enter a new size for the instance PGA.

   • Click Advice to use the PGA Advisor to help you choose a new instance PGA size.

   See Oracle Database Performance Tuning Guide for information about sizing the instance PGA.

6. When you have specified the new instance PGA size, click Apply.

   A confirmation message appears.