1.4 Managing the Oracle Instance

«« Previous
Next »»

1. 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

2. 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.

Managing the Oracle Instance

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

Managing the Oracle Instance

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

Managing the Oracle Instance

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

Managing the Oracle Instance

3. 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.

Managing the Oracle Instance

2. Log in to Enterprise Manager Database Express as the SYSTEM user.

Managing the Oracle Instance

3. In the Configuration menu, select Initialization Parameters.

Managing the Oracle Instance

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.

Managing the Oracle Instance

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.

Managing the Oracle Instance

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

Managing the Oracle Instance

7. Select open_cursors and click Set.

Managing the Oracle Instance

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

Managing the Oracle Instance

9. Click OK in the Confirmation window.

Managing the Oracle Instance

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

Managing the Oracle Instance

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.

Managing the Oracle Instance

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

Managing the Oracle Instance

13. Select db_files and click Set.

Managing the Oracle Instance

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.

Managing the Oracle Instance

15. Click OK to confirm the change.

Managing the Oracle Instance

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

Managing the Oracle Instance

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.

Managing the Oracle Instance

18. Click ORCL to return to the Database Home page.

4. 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.

Managing the Oracle Instance

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.

Managing the Oracle Instance