Managing the Oracle Instance


Options



Before You Begin

Purpose

This tutorial provides information on starting up and shutting down the database instance. You learn how to view and modify initialization parameters. You also learn how to manage memory management settings.

Time to Complete

Approximately 1 hour

Background

An Oracle database system consists of an Oracle database and an Oracle instance. An Oracle instance (also known as a database instance) contains the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work. In this tutorial you learn how to manage your Oracle Database instance.

What Do You Need?

  • Oracle Database 12c Release 2

Prerequisites

Before starting this tutorial, you should have:

  • Installed Oracle Database 12c
  • Configured the HTTPS port for Enterprise Manager Database Express

Starting Up and Shutting Down the Database Instance

You may need to shut down the database instance to perform certain administrative tasks. To shut down and restart the database instance, perform the following steps:

  1. Open a terminal window as the oracle user. Execute the oraenv command to set the environment variables.

    $ . oraenv
    ORACLE_SID = [oracle] ? orcl
    The Oracle base has been set to /u01/app/oracle
    
  2. Log in to SQL*Plus as the SYSDBA user.

    $ sqlplus / as sysdba
    
    SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 25 10:28:17 2016
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL>
  3. Issue the SHUTDOWN command to close the database and shut down the instance.

    SQL> shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
  4. Issue the STARTUP command to start the instance and open the database.

    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 3355443200 bytes
    Fixed Size                  8791392 bytes
    Variable Size            1929382560 bytes
    Database Buffers         1409286144 bytes
    Redo Buffers                7983104 bytes
    Database mounted.
    Database opened.

Viewing and Modifying Initialization Parameters

The properties of an Oracle instance are specified using instance initialization parameters. When the instance is started, an initialization parameter file is read, and the instance is configured accordingly. To view the values of the initialization parameters by using Enterprise Manager Database Express, perform the following steps:

  1. Open your browser and specify the URL for Enterprise Manager Database Express.
    The address bar of the browser
    Description of this image
  2. Log in to Enterprise Manager Database Express as the SYSTEM user.

    Enterprise Manager login page
    Description of this image
  3. In the Configuration menu, select Initialization Parameters.

    Configuration menu
    Description of this image
  4. The Initialization Parameters page is displayed. The Current tab shows the parameter values that are in use. Click the SPFile tab to view parameters in the server parameter file.

    Initialization Parameters page
    Description of this image
  5. The SPFile tab of the Initialization Parameters page shows the parameter values in the server parameter file. The server parameter file is a binary file that can be written to and read by the database and is the recommended format for the initialization parameter file. Click Current to return to the Current tab.

    Initialization Parameters - SPFile page
    Description of this image
  6. To change the value of OPEN_CURSORS, search for the initialization parameter by entering "open" in the search window.

    Initialization Parameters - Current Page
    Description of this image
  7. Select open_cursors and click Set.

    Initialization Parameters page
    Description of this image
  8. Enter 400 in the Value field and click OK.

    Set Initialization Parameter page
    Description of this image
  9. Click OK in the Confirmation window.

    Confirmation window
    Description of this image
  10. Note that the Current tab still shows 300 which is the old value for open_cursors. Click SPFile to view the updated value in the server parameter file.

    Initialization Parameters - Current page
    Description of this image
  11. The SPFile tab shows that the value of open_cursors has been changed to 400 in the server parameter file. When the instance is restarted the new value will be used. Click the Current tab.

    Initialization Parameters - SPFile tab
    Description of this image
  12. Enter db_files in the search window to search for the db_files initialization parameter.

    Initialization Parameters - Current tab
    Description of this image
  13. Select db_files and click Set.

    Initialization Parameters - Current tab
    Description of this image
  14. Note that the only scope for this initialization parameter file is SPFile. This is a static initialization parameter and the value in the running instance cannot be changed. You must restart the instance for the new value to take affect. Enter 250 in the Value field and click OK.

    Set Initialization Parameter page
    Description of this image
  15. Click OK to confirm the change.

    Confirmation page
    Description of this image
  16. Note that the value for db_files for the running instance is still set to 200. Click the SPFile tab..

    Initialization Parameters - Current tab
    Description of this image
  17. The db_files value in the server parameter file is set to 250. When the instance is restarted the new value of 250 will be used.

    Initialization Parameters - SPFile tab
    Description of this image
  18. Click ORCL to return to the Database Home page.

Managing Memory

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. With automatic memory management 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. To have more direct control over the sizes of the SGA and instance PGA, you can disable automatic memory management and enable automatic shared memory management. Oracle recommends that you use automatic memory management unless you need to have more control over the SGA and instance PGA due to memory errors or specific application requirements. In the example below, Automatic Shared Memory Management is enabled.

Verifying that Automatic Shared Memory Management is Enabled

  1. In the Configuration menu of Enterprise Manager Database Express, select Memory.

    Enterprise Manager - Configuration Menu
    Description of this image
  2. The Memory Settings section provides information on the memory management settings. The value of Auto for Management Mode for SGA and PGA memory indicates that Automatic Shared Memory Management is enabled. The memory advisors assist in sizing the Oracle server memory by predicting the percentage of time saved for each potential memory size.

    Enterprise Manager - Memory Management page
    Description of this image

Want to Learn More?