Managing the Oracle Instance

Overview

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

Introduction

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.

Software Requirements

The following is a list of software requirements:

  • Oracle Database 12c

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 and log in as the oracle user. Change directories to the /usr/local/bin directory and execute the oraenv command to set the environment variables.

  2. Log in to SQL*Plus as the SYSDBA user.

  3. Issue the SHUTDOWN command to close the database and shut down the instance.

  4. Issue the STARTUP command to start the instance and open the database.

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.
  2. Log in to Enterprise Manager Database Express as the SYSTEM user.

  3. In the Configuration menu, select Initialization Parameters.

  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.

  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.

  6. To change the value of OPEN_CURSORS, search for the initialization parameter by entering "open" in the search window.

  7. Select open_cursors and click Set.

  8. Enter 400 in the Value field and click OK.

  9. Click OK in the Confirmation window.

  10. The Current tab shows the updated value for open_cursors. Click SPFile to view the updated value in the server parameter file.

  11. The SPFile tab shows that the value of open_cursors has been changed to 400 in the server parameter file. Click the Current tab.

  12. Enter db_files in the search window to search for the db_files initialization parameter.

  13. Select db_files and click Set.

  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.

  15. Click OK to confirm the change.

  16. Note that the value for db_files for the running instance is still set to 200. Click the SPFile tab..

  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.

  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. To manage the SGA and instance PGA, perform the following steps:

Verifying that Automatic Memory Management is Enabled

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

  2. The Memory Settings section provides information on the memory management settings. The value of Auto for Memory Management indicates that Automatic 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.

Summary

In this tutorial, you learned to:

  • Start up and shut down the database instance
  • View and modify initialization parameters
  • View memory settings

Resources

  • Oracle Database 2 Day DBA 12c Release 2 (12.1)

Credits

  • Lead Curriculum Developer: Veerabhadra Rao Putrevu
  • Original Curriculum Developers: Donna Keesling, Salome Clement
  • Other Contributors: Bert Rich

To navigate this Oracle by Example tutorial, note the following:

Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

To navigate to a particular section in this tutorial, select the topic from the list.