Chapter 6: Managing Database Storage Structures
Chapter 6: Managing Database Storage Structures
In this chapter you will learn how to use Enterprise Manager
to explore the structure of your database. In addition, you will learn how to
make a number of changes to your database storage structure.
Approximately 1 hour
This tutorial covers the following topics:
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over
an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking
it.
Before you perform this tutorial, you should:
Back to Topic List
You can use Enterprise Manager to manage the storage structures
in your database. In this section you'll access the pages for different storage
structures to become familiar with their contents. In later sections you'll
use the pages to manage the storage structure in your database.
Follow the steps below to use Enterprise Manager to explore
the storage structure of your database:
| 1. |
Click Server on the Database Home page to access
the Server property page.
|
| 2. |
Click Control Files in the Storage section to
access the Control Files page.
The control files and their status is displayed on the
Control Files General page. There are two other property pages, in addition
to the General page, for viewing control file information. Click Advanced
to view additional information about the control file.
The Advanced page details information about the database
stored in the control file. Click Record Section to view information
stored in the control file.
The Record Section page displays detailed information
about the control file record section. Return to the Server page by clicking
the database link at the top of the page.
|
| 3. |
Click Tablespaces on the Server page to access
the Tablespaces property page. The Tablespaces page provides high-level
information about tablespace structure and space usage. You can see how
much space is allocated for the tablespace and what portion of it is used.
Select the Example tablespace and click View to display
more information about the tablespace.
You see more information about the tablespace. Return to the Server page
by clicking the database link at the top of the page.
|
| 4. |
Click Datafiles on the Server page to access
the Datafiles property page. The Datafiles page provides a list of the
datafiles that comprise your database.
Select the example01.dbf
datafile and click View.
You see more information about the datafile. Return to the Server page
by clicking the database link at the top of the page.
|
| 5. |
Click Redo Log Groups on the Server page to access
the Redo Log Groups property page. The Redo Log Groups page shows the
attributes of the redo log groups for your database.
Return to the Server page by clicking the database link
at the top of the page.
|
| 6. |
Click Archive Logs on the Administration page
to access the Archive Logs property page. The Archive Logs page shows
the attributes of the archived redo log files for your database.
Return to the Server page by clicking the database link at
the top of the page.
|
Back to Topic
List
In this section you will learn how to create a new tablespace
for your database.
Perform the steps listed below to create the FSDATA
tablespace. This tablespace will be used to store the tables of your database.
Repeat these steps to create the FSINDEX
tablespace which will be used for storage of the indexes in your database.
| 1. |
Click Tablespaces on the Server page to access
the Tablespaces property page.
Click Create.
|
| 2. |
The Create Tablespace page appears. Enter FSDATA
in the Name field. Select Locally Managed under the Extent Management
heading. Select Permanent under the Type heading. Select Read
Write under the Status heading. Click Add in the Datafiles
region of the page.
|
| 3. |
The Add Datafile page appears. Enter a file
name in the File Name field on the Add Datafiles page. Accept
the defaults for the File Directory and File Size or enter new values.
Select the option Reuse Existing File. Select Automatically
extend datafile when full (AUTOEXTEND) and specify an amount in the
Increment field by which you want to extend the datafile each time it
fills. Click Continue.
|
| 4. |
You are returned to the Create Tablespace General page.
Click the Storage tab.
|
| 5. |
The Storage page appears. Accept all of the defaults
on the Storage page. Click OK to create your tablespace.
|
| 6. |
You are returned to the Tablespaces page where you receive
a confirmation of the creation of the tablespace. You can view your new
tablespace in the Results region.
|
| 7. |
Repeat steps 2 through 6 to create the FSINDEX
tablespace. This tablespace will be used to store the indexes in your
database. Return to the Server page by clicking the database link
at the top of the page.
|
Back to Topic List
You can modify a tablespace using Enterprise Manager. You
can add additional space to the tablespace by adding datafiles or extending
the existing datafiles. In addition you can enable automatic extension so that
the tablespace will automatically extend when full. There are a number of other
changes you can make including changing space thresholds and changing the tablespace
status to offline.
In this example you will change the automatic extension increment
for the UNDOTBS1 tablespace
and specify an upper limit for the datafile belonging to the tablespace.
Follow the steps below to modify the UNDOTBS1
tablespace:
| 1. |
Click Tablespaces on the Server page to access
the Tablespaces property page. Select UNDOTBS1 and click Edit.
|
| 2. |
The Edit Tablespace page appears. Select the datafile
associated with the tablespace and click Edit.
|
| 3. |
The Edit Datafile page appears. Select Automatically
extend datafile when full (AUTOEXTEND). Specify 1
MB for the increment value and 60
MB for the maximum file size. Click Continue.
|
| 4. |
The Edit Tablespace page appears. Click Apply.
|
| 5. |
An update message is displayed confirming your change.
Click the database link to return to the Server page.
|
Back to Topic List
You can multiplex your redo log by adding additional members
to each redo log group.
Follow the steps below to add a new member to each of your
redo log groups:
| 1. |
Click Redo Log Groups in the Storage subsection
on the Server page to access the Redo Log Groups property page.
|
| 2. |
Select group 1 and click Edit. The Edit Redo
Log Group page appears.
|
| 3. |
Click Add in the Redo Log Members section. The
Add Redo Log Member page appears.
|
| 4. |
Enter redo01a.log
for the Name and enter the file directory. Click Continue.
Note: You can create your new member in the same directory, but
it is recommended that you store
members on separate drives. If there is a drive failure, you will still
have one member for the log writer to use.
|
| 5. |
You are returned back to the Edit Redo Log Group page.
Verify your entry and click Apply to add your new member.
|
| 6. |
You receive a confirmation of the addition of a new
member to your log group.
|
| 7. |
Return to the Redo Log Groups property page and repeat
steps 3 through 7 for each of your log groups using the file naming convention:
redo<group number>a.log.
Click the database link to return to the Server page.
|
Back to Topic List
Undo management entails storing the changes of database transactions
long enough to accommodate rollback, read consistency, and flashback features.
Auto-tuning of the undo retention period is enabled when you create your database.
In this section, you will perform the following tasks:
Using
the Undo Management Page
You can use the Undo Management page to view undo configuration,
including the auto-tuned undo retention period, its low threshold retention
period, and the name and size of the undo tablespace. Auto-tuning of undo retention
cannot be disabled.
You can access the Undo Management page as follows:
| 1. |
Click Automatic Undo Management in the Database
Configuration region to access the Automatic Undo Management page.
|
| 2. |
The Automatic Undo Management page is displayed. Under
the General tab, you can view the current undo settings for your instance
and use the Undo Advisor to analyze the undo tablespace requirements.
Click System Activity.
|
| 3. |
Under System Activity tab, you can look at the system
activity for a specified time period, including longest running query,
average and maximum undo generation rate, and queries failed due to low
retention.
Click the database link to return to
the Server page.
|
Back to Topic
Using
the Undo Advisor
The undo tablespace is set to auto-extend by default when
you use DBCA to configure your database. If your undo tablespace is not auto-extensible,
you may need to extend the tablespace. You can use the undo advisor to obtain
advise on the undo tablespace size.
You can access the undo advisor as follows:
| 1. |
Click Automatic Undo Management in the Database
Configuration region to access the Automatic Undo Management page.
|
| 2. |
The Automatic Undo Management page is displayed. Scroll
down the page to the Undo Advisor section. Click on the Analysis Time
Period drop-down menu and select Last One Day. Click Run Analysis.
|
| 3. |
The Analysis Results are displayed.
Click Show Graph to view a graphical representation.
|
| 4. |
The tablespace size required to meet your specifications
is displayed in the Analysis section as well as a graphical representation
of this information.
|
Back to Topic
In this tutorial, you learned how to:
 |
Review the structure of the control
files, tablespaces, datafiles, and redo log files. |
|
Create the FSDATA
tablespace. |
|
Modify the automatic extension
increment for a tablespace. |
|
Add another member to a redo
log group. |
|
Modify the undo parameters using
the Automatic Undo Management page in EM or the Undo Advisor. |
Back to Topic List
Place the cursor over this icon to hide all screenshots.
| 
Bookmark
