Managing Database Storage Structures


Options



Before You Begin

Purpose

In this tutorial, you learn how to use Enterprise Manager Express to explore the structure of your database. In addition, you learn how to make a number of changes to your database storage structure.

Time to Complete

Approximately 1 hour

Background

An Oracle database is made up of physical and logical structures. Physical structures are visible in the operating system. An example of a physical structure is an operating system file that stores data on a disk. Logical structures are defined and known to the Oracle Database server, but are not known to the operating system. An example of a logical structure is a tablespace. In this tutorial you will view logical structures in your database and understand how they relate to physical structures in the operating system.

What Do You Need?

  • Oracle Database 12c Release 2

Prerequisites

Before starting this tutorial, you should have:

  • Installed Oracle Database 12c Release 2

Viewing Database Storage Structure Information

An Oracle database is made up of physical and logical structures. Physical structures are defined in the operating system. Physical files that store data on a disk are an example of a physical structure. Logical structures are defined in the database and are not known in the operating system. To view storage structure information, perform the following steps:

Viewing Control File Information

  1. Enter the URL for Enterprise Manager Database Express in your browser and log in as the SYSTEM user.

    Enterprise Manager Login page
    Description of this image
  2. Select Control Files in the Storage menu.

    Storage Menu
    Description of this image
  3. The Control Files page is displayed. The Control File Information section provides information about the creation and most recent modification to the control file. In the List of Control Files section, the control file names are provided. More than one file name indicates that the control file is multiplexed. The Control File Sections section provides information about the data that is stored in the control file.

    Control Files page
    Description of this image
  4. Click ORCL to return to the Database Home page.

Viewing Online Redo Log Information

  1. Select Redo Log Groups in the Storage menu.

    Storage Menu
    Description of this image
  2. The Redo Log Groups page is displayed. This page lists the redo log groups that have been configured for your database. A value of Current in the Status column indicates the group that is currently being written to. If the redo log groups are multiplexed, more than one member is shown for the group.

    Note: In this tutorial, the current group is Redo Log Group 3. The current group may differ when you perform this tutorial.

    Redo Log Groups page
    Description of this image
  3. Click ORCL to return to the Database Home page.

Viewing Archived Redo Log Information

  1. Select Archive Logs in the Storage menu.

    Storage menu
    Description of this image
  2. The Archive Logs page is displayed. If your database is not in ARCHIVELOG mode, you will not have any archived redo log files.

    Archive Logs page
    Description of this image
  3. Click ORCL to return to the Database Home page.

Managing the Online Redo Log

The online redo log files contain entries that are used to recover transactions in the event of an instance failure and may also be used in recovery from media failure. To ensure that redo entries are not lost in the event of a disk drive failure, redo log files should be multiplexed so that each group has multiple members.To multiplex the redo log file, perform the following steps:

  1. Select "Redo Log Groups" in the Storage menu.

    Storage menu
    Description of this image
  2. Select the first redo log group and click "Add Member."

    Redo Log Groups page
    Description of this image
  3. Enter a directory location in the File Directory field and a file name in the File Name field. Click OK. Note that redo log file members should be stored on separate disk drives so that at least one member will be accessible if you have a disk drive failure.

    Add Member window
    Description of this image
  4. Click OK on the Confirmation page.

    Confirmation window
    Description of this image
  5. The Redo Log Groups page shows the new member in Redo Log Group 1.

    Redo Log Groups page
    Description of this image
  6. Repeat steps 2 through 4 for log groups 2 and 3. The Redo Log Groups page should now show that each group has two members.

    Redo Log Groups page
    Description of this image
  7. Click ORCL to return to the Database Home page.

Managing Undo Data

Oracle Database uses undo data to roll back transactions, to provide read consistency, as part of database recovery, and to enable features such as Oracle Flashback Query. Oracle Database automatically ensures that undo data that is in use by an active transaction is never overwritten until that transaction has been committed. The database server maintains and automatically tunes an undo retention period to ensure the success of Oracle Flashback features and for read consistency for long-running queries. Auto-tuning of the undo retention period is enabled when you create your database. To view undo data information, perform the following steps:

  1. Select Undo Management in the Storage menu.

    Storage menu
    Description of this image
  2. The Undo Management Details page is displayed. The "Low Undo Retention Threshold" setting shows the minimum undo retention as configured in the UNDO_RETENTION initialization parameter. A "Retention Guaranteed" setting of Yes indicates that the database server should not overwrite unexpired undo data even if current DML transactions must fail to accomodate this setting. A setting of Yes may be necesssary to support Oracle Flashback Query. If you receive "Snapshot Too Old Errors" you may need to increase the value of UNDO_RETENTION or increase the size of the undo tablespace.

    Undo Management Details page
    Description of this image
  3. Scroll to the right to the view the Undo Advisor graph. The graph shows the minimum size requirement for the undo tablespace given a particular undo retention time. You can change the undo tablespace to a fixed size if necessary. Changing to a fixed size may be required to support Oracle Flashback operations.

    Undo Advisor graph
    Description of this image
  4. Click Change Analysis Parameters to change the values used in the undo configuration analysis and by the Undo Advisor.

    Undo Management Details page
    Description of this image
  5. Select Last Week in the Analysis Period menu and click OK.

    Change Analysis Parameters page
    Description of this image
  6. Click OK on the Confirmation page .

    Confirmation window
    Description of this image
  7. The Undo Statistics Summary and Undo Advisor sections reflect the analysis for the last week.

    Undo Statistics Summary and Undo Advisor
    Description of this image
  8. Click ORCL to return to the Database Home page.

Managing Tablespaces

A database is divided into logical storage units called tablespaces, which group related logical structures such as tables, views, and other database objects.

Viewing Tablespace and Data File Information

  1. The pluggable database should be open for the Enterprise Management Database Express to work. If the pluggable database is open, skip to step 2. If not, open the pluggable database by performing the steps below and proceed to step 2.

    Open a terminal window, execute the oraenv command to set the environment variables and connect to the multitenant container database orcl.

    $ . oraenv
    ORACLE_SID = [oracle] ? orcl
    The Oracle base has been set to /u01/app/oracle
    $ sqlplus / as sysdba
    
    SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 25 05:10:37 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>

    Alter the session and set container as pdb (orclpdb in this case). Open the pluggable database and issue the SQL statement that returns the port that is configured for EM Express for PDB.

    SQL> ALTER SESSION SET CONTAINER=orclpdb;
    
    Session altered.
    
    SQL> alter pluggable database orclpdb open;
    
    Pluggable database altered.
    
    SQL> SELECT dbms_xdb_config.gethttpsport() from dual;
    
    DBMS_XDB_CONFIG.GETHTTPSPORT()
    ------------------------------
    			  5502
  2. Enter the URL for the Enterprise Manager Database Express for PDB in your browser and log in as the SYSTEM user.

    Enterprise Manager login page for PDB
    Description of this image
  3. Select Tablespaces in the Storage menu.

    Storage menu
    Description of this image
  4. The Tablespaces page is displayed.

    Tablespaces page
    Description of this image
  5. Expand one of the tablespaces to view information about the datafiles allocated to the tablespace.

    Tablespaces page
    Description of this image
  6. Click ORCL to return to the Database Home page.

Creating a Tablespace

A number of tablespaces are automatically created when you create the database. To create a new tablespace, perform the following steps:

  1. Select Tablespaces in the Storage menu.

    Storage menu
    Description of this image
  2. On the Tablespaces page, click Create.

    Tablespaces page
    Description of this image
  3. Enter a name for the new tablespace in the Name field. Accept the defaults for Tablespace type, Bigfile, and Status. Click the right arrow.

    Create Tablespace window - Step 1
    Description of this image
  4. Enter a file name in the Datafiles field and click the green plus symbol.

    Create Tablespace window - Step 2
    Description of this image
  5. The file appears in the File Name list. Deselect "Auto Extend" and click the right arrow. Note: You will set the datafile to autoextend in the Modifying a Tablespace section.

    Create Tablespace window - Step 2
    Description of this image
  6. Accept the default block size and automatic extent allocation. Click the right arrow.

    Create Tablespace window - Step 3
    Description of this image
  7. Accept the default value for Logging. Click the right arrow.

    Create Tablespace window - Step 4
    Description of this image
  8. Accept the default of Automatic for Segment Space Management and None for Compression. Click OK.

    Create Tablespace window - Step 5
    Description of this image
  9. Click OK on the Confirmation page.

    Confirmation page
    Description of this image
  10. The new tablespace is listed on the Tablespaces page.

    Tablespaces page
    Description of this image

Modifying a Tablespace

This section shows you how to configure a tablespace to automatically extend when it reaches the defined limit. To configure a tablespace to automatically extend, perform the following steps:

  1. If you are not on the Tablespaces page, select Tablespaces in the Storage menu.

    Storage menu
    Description of this image
  2. Expand the tablespace name of the tablespace you want to configure.

    Tablespaces page
    Description of this image
  3. Select the datafile. In the Actions menu, select "Edit Auto Extend."

    Tablespaces page - Actions menu
    Description of this image
  4. Select "Auto Extend." Click OK.

    Auto Extend Setting of Datafile appts.dbf page
    Description of this image
  5. Click OK on the Confirmation page.

    Confirmation page
    Description of this image
  6. The Tablespaces page indicates that the datafile for the APPTS tablespace is set to automatically extend.

    Tablespaces page
    Description of this image
  7. Click ORCL / ORCLPDB to return to the Database Home page.

Want to Learn More?