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.
This chapter discusses the following:
|
Exploring the Storage Structure of Your Database | |
|
Creating a New Tablespace | |
|
Modifying a Tablespace | |
|
Reclaiming Space in your Tablespace | |
|
Multiplexing the Redo Log | |
|
Managing Undo in your Database | |
Move your mouse over this icon to show all screenshots.
You can also move your mouse over each individual icon to see only the screenshot
associated with it.
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 Administration on the Database Home page to access the Administration property page.
|
| 2. |
Click Controlfiles in the Storage section to access the Controlfiles page. The control files and their status is displayed on the Controlfiles 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 Administration page by clicking the database link at the top of the page.
|
| 3. |
Click Tablespaces on the Administration 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 Administration page by clicking the database link at the top of the page.
|
| 4. |
Click Datafiles on the Administration 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 Administration page by clicking the database link at the top of the page.
|
| 5. |
Click Redo Log Groups on the Administration 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 Administration 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 redo log groups for your database.
Return to the Administration page by clicking the database link at the top of the page.
|
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 Administration page to access the Tablespaces property page. Click Create.
|
| 2. |
The Create Tablespaces 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 Create Tablespace: 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 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 the Thresholds tab.
|
| 6. |
The Thresholds page appears. Accept the defaults or enter new values. Click OK to create your tablespace.
|
| 7. |
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.
|
| 8. |
Repeat steps 2 through 7 to create the FSINDEX
tablespace. This tablespace will be used to store the indexes in your
database.
|
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 Administration 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.
|
You can use the segment advisor to advise on which objects are good candidates for shrink operations. You can then follow recommendations and shrink objects as necessary. You can run the advisor at the tablespace or object level.
In this section, you will perform the following tasks:
|
Using the Segment Advisor | |
|
Shrinking Database Objects | |
Follow the steps below to invoke the segment advisor at the tablespace level:
| 1. |
Click Advisor Central in the Related Links region on the Database Home page.
|
| 2. |
The Advisor Central page appears. Click Segment Advisor to invoke the segment advisor.
|
| 3. |
The Segment Advisor property page appears. Select Tablespaces and click Continue.
|
| 4. |
The Segment Advisor: Tablespaces page appears. Click Add.
|
| 5. |
The Tablespaces: Add page appears. Select the EXAMPLE tablespace and click OK.
|
| 6. |
The Segment Advisor: Tablespaces property page appears with the EXAMPLE tablespace listed. Click Next.
|
| 7. |
The Segment Advisor: Options page appears. Select Unlimited and click Next.
|
| 8. |
The Segment Advisor: Schedule page appears. Select Standard from the Schedule Type drop-down menu and click Next.
|
| 9. |
The Segment Advisor: Review page appears. Select Submit to submit the job.
|
| 10. |
The Advisor Central page appears listing the advisor related tasks that have run. Select the Segment Advisor task that you submitted and click View Result.
|
| 11. |
The Segment Advisor Task page appears showing the recommendations for your selected tablespace. To implement one or more recommendations, select the recommendation. Follow the steps in the Shrinking Database Objects section to shrink the objects.
|
You can shrink objects in the tablespace by performing the following steps:
| 1. |
Select the Shrink Options at the bottom of the page. You can choose to Compact Segments and Release Space which releases freed space to the tablespace or you can Compact Segments only. Click Schedule Implementation to run the shrink operation.
|
| 2. |
The Schedule Implementation page appears. Accept a start time of Immediately and click Submit.
|
| 3. |
The Scheduler Jobs page appears showing the status of the job. You can click Refresh to update the page until the status of the job is Completed. On successful completion, you can navigate back to the Tablespace page and compare the current used space with the previous.
|
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 on the Administration page to access the Redo Log Groups property page.
|
| 2. |
Select a group and click Edit, or click the group number link. 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.
|
| 5. |
The Edit Redo Log Group page is again displayed. 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.
|
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 | |
|
Using Oracle Recommendations for Managing Undo | |
|
Extending the Undo Tablespace | |
|
Using the Undo Advisor | |
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 also use this page to set the low threshold retention or to extend your tablespace. This might become necessary when your database transaction rate increases or when you need to create a flashback recovery strategy.
You can access the Undo Management page as follows:
| 1. |
Click Administration on the Database Home page to access the Administration property page.
|
| 2. |
Click Undo Management in the Instance region to access the Undo Management page.
|
The current recommendations for your undo configuration are summarized in the Recommendations section of the Undo Management page. You can update the analysis by specifying a past time period for Oracle to analyze system activity as follows:
| 1. |
Click Administration on the Database Home page to access the Administration property page.
|
| 2. |
Click Undo Management in the Instance region to access the Undo Management page.
|
| 3. |
Enter a time period and click Update Analysis.
|
The Oracle server will automatically extend the undo tablespace when space is needed if the AUTOEXTEND feature is enabled for the tablespace. You might need to manually extend the undo tablespace when the AUTOEXTEND tablespace feature is disabled. You might discover this through proactive planning with the undo advisor. You might also need to extend the undo tablespace when you receive an undo tablespace alert (warning or critical), or when you receive a long query alert or "snapshot too old" error.
You can extend the undo tablespace as follows:
| 1. |
Click Administration on the Database Home page to access the Administration property page.
|
| 2. |
Click Undo Management in the Instance region to access the Undo Management page.
|
| 3. |
Click Edit Undo Tablespace in the Recommendations section.
|
| 4. |
The Edit Tablespace page appears. Select a datafile and click Edit to extend the tablespace.
|
| 5. |
The Edit Datafile page appears. Enter the new size in the File Size field. Note: In this example, the size was increased to 45 MB. Click Continue.
|
| 6. |
The Edit Tablespace page is redisplayed with your new value. Click Apply.
|
You can use the undo advisor to advise on the undo tablespace size and the low threshold undo retention setting. The top of the page shows the current auto-tuned undo retention time and undo tablespace size.
You can access the undo advisor as follows:
| 1. |
Click Administration on the Database Home page to access the Administration property page.
|
| 2. |
Click Undo Management in the Instance region to access the Undo Management page.
|
| 3. |
Click Undo Advisor.
|
| 4. |
The Undo Advisory page is displayed.
|
Move your mouse over this icon to hide all screenshot