1.5 Managing Database Storage Structure


1. Overview


Purpose

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

Time to Complete

Approximately 1 hour

Introduction

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.

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

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.

Managing Database Storage Structure

2. Select Control Files in the Storage menu.

Managing Database Storage Structure

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.

Managing Database Storage Structure

4. Click ORCL to return to the Database Home page.
  • Viewing Online Redo Log Information
1. Select Redo Log Groups in the Storage menu.

Managing Database Storage Structure

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.

Managing Database Storage Structure

3. Click ORCL to return to the Database Home page.
  • Viewing Archived Redo Log Information
1. Select Archive Logs in the Storage menu.

Managing Database Storage Structure

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

Managing Database Storage Structure

3. Click ORCL to return to the Database Home page.
  • Viewing Tablespace and Data File Information
1. Select Tablespaces in the Storage menu.

Managing Database Storage Structure

2. The Tablespaces page is displayed.

Managing Database Storage Structure

3. Expand one of the tablespaces to view information about the datafiles allocated to the tablespace.

Managing Database Storage Structure

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

3. Creating a Tablespace


A database is divided into logical storage units called tablespaces, which group related logical structures such as tables, views, and other database objects. 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.

Managing Database Storage Structure

2. On the Tablespaces page, click Create.

Managing Database Storage Structure

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.

Managing Database Storage Structure

4. Enter a file name in the Datafiles field and click the green plus symbol.

Managing Database Storage Structure

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.

Managing Database Storage Structure

6. Accept the default block size and automatic extent allocation. Click the right arrow.

Managing Database Storage Structure

7. Accept the default block size and automatic extent allocation. Click the right arrow.

Managing Database Storage Structure

8. Accept the default of Automatic for Segment Space Management and None for Compression. Click OK.

Managing Database Storage Structure

9. Click OK on the Confirmation page.

Managing Database Storage Structure

10. The new tablespace is listed on the Tablespaces page.

Managing Database Storage Structure

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

Managing Database Storage Structure

2. Expand the tablespace name of the tablespace you want to configure.

Managing Database Storage Structure

3. Select the datafile. In the Actions menu, select "Edit Auto Extend."

Managing Database Storage Structure

4. Select "Auto Extend." Click OK.

Managing Database Storage Structure

5. Click OK on the Confirmation page.

Managing Database Storage Structure

6. The Tablespaces page indicates that the datafile for the APPTS tablespace is set to automatically extend.

Managing Database Storage Structure

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

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

Managing Database Storage Structure

2. Select the first redo log group and click "Add Member."

Managing Database Storage Structure

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.

Managing Database Storage Structure

4. Click OK on the Confirmation page.

Managing Database Storage Structure

5. The Redo Log Groups page shows the new member in Redo Log Group 1.

Managing Database Storage Structure

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.

Managing Database Storage Structure

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

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

Managing Database Storage Structure

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.

Managing Database Storage Structure

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.

Managing Database Storage Structure

4. Click Change Analysis Parameters to change the values used in the undo configuration analysis and by the Undo Advisor.

Managing Database Storage Structure

5. Select Last Week in the Analysis Period menu and click OK.

Managing Database Storage Structure

6. Click OK on the Confirmation page .

Managing Database Storage Structure

7. The Undo Statistics Summary and Undo Advisor sections reflect the analysis for the last week.

Managing Database Storage Structure

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