| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 10g Release 2 (10.2) Part Number B14196-02 |
|
|
View PDF |
An Oracle database is made up of physical and logical structures. Physical structures are those that can be seen and operated on from the operating system, such as the physical files that store data on disk.
Logical structures are created and recognized by the Oracle database server and are not known to the operating system. The primary logical structure in a database, a tablespace, contains physical files. The applications developer or end user may be aware of the logical structure, but is not usually aware of this physical structure. The DBA must understand the relationship between the physical and logical structures of a database.
The Oracle database server can automate the management of its structure. Furthermore, Oracle Enterprise Manager provides a Web-based GUI to enable easy management and monitoring of your database.
To view database storage structure, navigate to the Storage section of the Administration Home page (see Figure 5-2, "Database Administration Page"). You can click the links shown in Figure 6-1 to access the storage pages.
The following sections provide a closer look at the database storage structure:
A control file tracks the physical components of the database. It is essential to the functioning of the database. Because of the importance of the control file, Oracle recommends that the control file be multiplexed. In other words, the control file should have multiple identical copies. For databases created with DBCA, three copies of the control file are automatically created and kept in sync with each other.
If any control file fails, then your database becomes unavailable. As long as you have a control file copy, however, you can shut down your database and re-create the failed control file from the copy, then restart your database. Another option is to delete the failed control file from the CONTROL_FILES initialization parameter and restart your database with the remaining control files.
Click Control Files to open the Control Files general page. This page shows whether your database has a multiplexed control file. The Advanced and Record Section pages give you more detailed information about your control file. Explanations of this information are contained in the online Help.
Every Oracle database has a set of two or more online redo log files. The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries, which are also called redo records.
The redo log records all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. To protect against a failure involving the redo log itself, Oracle multiplexes the redo log so that two or more identical copies of the online redo log can be maintained on different disks.
Your database's redo log consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of that group. Each group is defined by a number, such as Group 1.
The database log writer process writes redo records from the memory buffer to a redo log group until the group fills up or a log switch operation is requested. The log writer then writes to the next group. The log writer performs this action in a circular fashion so that the oldest group is overwritten by the most recent redo records.
Click Redo Log Groups to open the Redo Log Groups page. This page shows the attributes of the redo log groups for your database. A database typically consists of three groups. Note the status attribute for the redo log groups. The CURRENT status group is the one currently being written to disk by the log writer.
When a redo log group contains only one member, it is not multiplexed. Click the number of a redo log group to view information about the redo log group members.
While small database environments might not need multiplexed redo log groups, consider adding multiplexing as your database grows in users and applications. Multiplexing provides better protection for data in the case of instance or media failure.
To multiplex your redo log, you must add members to each redo log group. It is not required that redo log groups be symmetrical, but Oracle recommends that your groups all have the same number of members. A database must have a minimum of two redo log groups.
To create a multiplexed redo log for your database:
From the Storage section of the Administration page, click Redo Log Groups.
The Redo Log Groups page appears.
Select a group and click Edit.
The Edit Redo Log Group page appears.
In the Redo Log Members section, click Add.
The Add Redo Log Member page appears.
Enter the file name for the new redo log member. For example, if your existing member file name is REDO01.log, then you might name this member REDO01a.log.
Enter the file directory or accept the default. You can create this file in the same directory, but it is recommended that you store members on separate drives. That way, if there is a drive failure, you still have access to one member.
Click Continue, then Click Apply to accept your changes.
Repeat these steps for every existing group.
To switch a log file:
Navigate to the Redo Log Groups page.
From the Actions menu, select Switch logfile.
Click Go.
You can see that the next groups status changes from INACTIVE to CURRENT.
When you archive your redo log, you write redo log files to another location prior to their being overwritten. This location is called the archive log. You can archive to multiple locations, including a standby database.
These copies of redo log files extend the amount of redo data that can be saved and used for recovery. Archiving can be either enabled or disabled for the database, but Oracle recommends that you enable archiving.
Click Archive Logs to display the Archive Logs page. This page lets you view archive log files and their attributes.
For placing the database into archive log mode, or adding archive log locations, see "Configuring Your Database for Basic Backup and Recovery".
Rollback segments were database structures used to track undo information for the database in earlier releases of Oracle. Now, the preferred way of managing undo is with the undo tablespace. For more information, see "Managing Undo for Your Database" in this chapter.
Datafiles are the operating system files that hold the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by other programs. Tempfiles are a special class of datafiles that are associated only with temporary tablespaces.
Datafiles can be broken down into the following components:
Segments and Extents
A segment contains a specific type of database object. For example, tables are stored in data segments, whereas indexes are stored in index segments.
An extent is a contiguous set of data blocks within a segment. Oracle initially allocates an extent of a specified size for a segment, but if that extent fills, then more extents can be allocated.
Data Blocks
Data blocks, also called database blocks, are the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. The database uses a default block size at database creation.
After the database has been created, it is not possible to change the default block size without re-creating the database. Nevertheless, it is possible to create tablespace with a block size different than the default block size. For details on how to create tablespaces with a nondefault or nonstandard block size, see Oracle Database Administrator's Guide.
Click Datafiles to open the Datafiles page. You can use this page to view information about your datafiles. Select a datafile and click View to display more attributes for the datafile. You can also click the name of the tablespace to display the attributes of the owning tablespace.
A database consists of one or more tablespaces. A tablespace is a logical structure, consisting of one or more datafiles or tempfiles.
Tablespaces hold data in the form of other objects such as tables and indexes. When you create an Oracle database, some tablespaces already exist, such as SYSTEM and USERS. For a complete list, see "Some Tablespaces in the Database" .
You can create new tablespaces to support your user and application data requirements. During tablespace creation, you set the following parameters:
Space management within a tablespace involves keeping track of free and used space, so that space is allocated efficiently during data insertion and deletion.
Oracle recommends creating locally-managed tablespaces over dictionary-managed tablespaces. Locally-managed tablespaces keep the space allocation information within the tablespace, not in the data-dictionary, thus offering better performance.
By default, Oracle sets all newly created tablespaces to be locally-managed with automatic segment management, a feature that further improves performance.
Tablespaces can be one of three types: permanent, undo, or temporary.
Permanent Tablespaces
Oracle uses these tablespaces to store permanent data, such as system data. You use permanent tablespaces to store your user and application data.
Undo Tablespace
A database running in automatic undo management mode transparently creates and manages undo data in the undo tablespace. Oracle uses undo data to provide read-consistency and to enable features such as Flashback query.
Even though you can create more than one undo tablespace, only one can be active. If you want to switch the undo tablespace used by the database instance, however, then you can create a new one and instruct the database to use it instead. The undo tablespace no longer in use can be dropped later.
Temporary Tablespaces
Temporary tablespaces are used for storing temporary data, as would be created when SQL statements perform sorts. After database creation, the database already has a temporary tablespace.
You would create another temporary tablespace if you were creating a temporary tablespace group. Under normal circumstances, you need not create additional temporary tablespaces.
Temporary and permanent tablespaces can be assigned at the user level. You can create a default temporary tablespace at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.
You can set tablespace read-write status and its availability as follows:
Read Write
Users can read and write to the tablespace after it is created. This is the default.
Read Only
If the tablespace is created read-only, then the tablespace cannot be written to until its status is changed to Read Write. It is unlikely that you create a read-only tablespace, but you might change it to that status after you have written data to it that you do not want modified.
Offline
If the tablespace is created offline, then no users can access it. It is unlikely that you create an offline tablespace, but you might change its status to offline later to perform maintenance on its underlying files.
You can set a tablespace to automatically extend itself by a specified amount when it reaches its limit.
If you do not enable autoextend, you will be alerted when the tablespace reaches its critical or warning threshold size. The critical and warning threshold parameters have default values which you can change at any time. You can respond to space alerts by manually increasing the tablespace.
Table 6-1 describes some of the tablespaces included in the database.
Table 6-1 Tablespaces and Descriptions
| Tablespace | Description |
|---|---|
|
This tablespace contains the sample schemas that Oracle includes with the database. The sample schemas provide a common platform for examples. Oracle documentation and educational materials contain examples based on the sample schemas. |
|
|
This is an auxiliary tablespace to the Some components and products that used the Components that use |
|
|
This tablespace is always created at database creation. Oracle uses it to manage the database. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for a particular database. It also contains various tables and views that contain administrative information about the database. These are all contained in the |
|
|
This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace would be used for query sorting. Every database should have a temporary tablespace that is assigned to users as their temporary tablespace. In the preconfigured database, the |
|
|
This is the undo tablespace used by the database server to store undo information. See "Managing Undo for Your Database" to understand how an Oracle database uses the undo tablespace. Every database must have an undo tablespace. |
|
|
This tablespace is used to store permanent user objects and data. Like the |
Other storage structures that can exist in an Oracle database include the initialization parameter file, the password file, and backup files.
Initialization parameters are used by the database server at startup to determine the runtime resources for the database. They are actively monitored by the database and can be set or modified while the database is running.
Initialization parameters and the initialization parameter file are discussed in Chapter 5, "Managing the Oracle Instance".
A database can use a password file to authenticate administrative users with SYSDBA connect privileges. SYSDBA privileges enable a DBA to start up and shut down the database and perform other high-level administrative tasks. This password file is outside of the database itself because it must sometimes be referenced when the database is not yet running.
This is not the only form of administrator authentication, so not all databases require a password file.
The password file is discussed in "Accessing the Database" in Chapter 5, "Managing the Oracle Instance".
Backup files are not technically database files, but are copies of the database in some form that can be used to recover the database if a failure causes loss of data.
Backup files are discussed in Chapter 9, "Performing Backup and Recovery".
